The Complete Query

Here’s the full SQL in context. The sections below break down each regex component in detail.

select DATE, generated_at, referrer,
regexp_extract(concat('/', try(array_join(slice(short_referrer_array, 4, cardinality(short_referrer_array) - 1), '/'))),
'(?>/check-income-tax|^)(personal-account|.*)', 1) AS "short_referrer",
short_page, user_agent_string, event_id, request_id,
session_id, transaction_name, detail_method, page_path, Subject
from (
SELECT
DATE, generated_at,
detail.referrer AS "referrer",
split(coalesce(
regexp_replace(regexp_extract(detail.referrer, '(.*)\?.*$', 1),'\/\d{1,3}$', '/[AN]/'),
regexp_replace(detail.referrer,'\/[0-9]+[A-Za-z].+[\-].+|\/[A-Za-z]+[0-9].+[\-].+|\/(?![0-9]{4}\-[0-9]{4})(?:[A-Z]|[0-9])+[\-].+\/|\/[0-9]{1,3}(?:$|\/)', '/[AN]/'),
regexp_extract(detail.referrer, '(.*)\?.*$', 1),
regexp_extract(detail.referrer, '(.*)', 1)),
'/') AS "short_referrer_array",
detail.user_agent_string AS "user_agent_string",
event_id, tags.x_request_id AS "request_id",
tags.x_session_id AS "session_id",
tags.transaction_name AS "transaction_name",
detail.method AS "detail_method",
tags.path AS "page_path",
coalesce(
regexp_replace(regexp_extract(tags.path, '/check-income-tax(.*)\?.*$', 1),'\/\d{1,3}$', '/[AN]/'),
regexp_replace(regexp_extract(tags.path, '/check-income-tax(.*)', 1),'\/[0-9]+[A-Za-z].+[\-].+|\/[A-Za-z]+[0-9].+[\-].+|\/(?![0-9]{4}\-[0-9]{4})(?:[A-Z]|[0-9])+[\-].+\/|\/[0-9]{1,3}(?:$|\/)', '/[AN]/'),
regexp_extract(tags.path, '/check-income-tax(.*)\?.*$', 1),
regexp_extract(tags.path, '/check-income-tax(.*)', 1)) AS "short_page",
CASE
WHEN regexp_like(tags.path, '.*print-your.*') THEN 'Printing'
WHEN regexp_like(tags.path, '.*current-year.*') THEN 'Current Year'
WHEN regexp_like(tags.path, '.*tax-code.*') THEN 'Tax Code'
WHEN regexp_like(tags.path, '.*employment.*') THEN 'Employment'
WHEN regexp_like(tags.path, '.*pension.*') THEN 'Pension'
WHEN regexp_like(tags.path, '.*underpayment.*') THEN 'Underpayment'
WHEN regexp_like(tags.path, '.*state-benefits.*') THEN 'State Benefits'
WHEN regexp_like(tags.path, '.*income-tax-history.*') THEN 'Income Tax History'
WHEN regexp_like(tags.path, '.*signout.*') THEN 'Signout page'
WHEN regexp_like(tags.path, '.*what-do-you-want-to-do.*') THEN 'Home Dashboard'
ELSE 'Other'
END AS "Subject"
FROM {{time_aware_table("[YOUR TABLE NAME]", timerange = "7 days")}}
WHERE tags.path NOT LIKE '%/assets/%'
AND tags.path NOT LIKE '%/images/%'
AND tags.path NOT LIKE '%/fonts/%'
AND detail.status_code = '200'
)

The Problem

When you’re doing analytics on a government tax service, your page paths look deceptively clean at first glance — until you realise that URLs like these all represent the same page:

/check-income-tax/tax-code/1250L/employment
/check-income-tax/tax-code/K497/employment
/check-income-tax/tax-code/BR/employment

And paths like these each represent different users visiting the same underpayment summary page:

/check-income-tax/underpayment/I48-TR18-T7S
/check-income-tax/underpayment/YPB-EMZG-IPLA
/check-income-tax/underpayment/dd6c6129-9956-42c3-ab19-8184669c04ec

To produce meaningful page-level analytics — counting sessions per page, tracking journeys, building funnels — you need to strip those dynamic segments out and replace them with a stable placeholder like [AN], while not stripping segments that are actually meaningful, such as the tax year range /2021-2022/.

That’s what the regex chain in this SQL does.

The Overall Strategy: COALESCE as a Waterfall

Rather than writing one enormous regex that tries to handle every case, the query uses Presto/Athena’s COALESCE function as a waterfall of increasingly general patterns. The first expression that returns a non-null value wins.

Step 1 — Handling URLs with Query Strings

regexp_extract(path, ‘/check-income-tax(.*)\?.*$’, 1)

This strips the query string from a URL, capturing only the path segment after /check-income-tax and before the ?.

regexp_replace(…, ‘\/\d{1,3}$’, ‘/[AN]/’)

Step 2 — The Main Normalisation Regex

This handles several distinct ID formats in a single regexp_replace pass using the | OR operator.

\/[0-9]+[A-Za-z].+[\-].+          -- Pattern A
|\/[A-Za-z]+[0-9].+[\-].+         -- Pattern B
|\/(?![0-9]{4}\-[0-9]{4})(?:[A-Z]|[0-9])+[\-].+\/  -- Pattern C
|\/[0-9]{1,3}(?:$|\/)             -- Pattern D

Pattern A — Numbers first, then letters, then a hyphen

Targets identifiers that start with digits, mix in letters, and contain a hyphen.

Pattern B — Letters first, then numbers, then a hyphen

The mirror of pattern A. It targets identifiers such as /I48-TR18-T7S.

Pattern C — Uppercase/digit hyphenated tokens, excluding tax years

This catches uppercase/digit hyphenated tokens such as /YPB-EMZG-IPLA/, while deliberately not matching tax year ranges like /2021-2022/. Lowercase UUID-like values may be caught elsewhere in the wider regex chain, especially by the mixed letter/number patterns.

Pattern D — Short numeric segments

This catches a 1–3 digit number at the end of a path or as an interior segment.

The Referrer Version

The same logic is applied to the referrer URL, but without the /check-income-tax anchor since referrers can come from other GOV.UK services.

regexp_extract(
  concat('/', try(array_join(slice(short_referrer_array, 4, cardinality(short_referrer_array) - 1), '/'))),
  '(?>/check-income-tax|^)(personal-account|.*)',
  1
)

This slices the referrer URL array from the fourth segment onwards, joins it back into a path, then extracts either a personal-account path or anything else following the service prefix.

Putting It Together: The Subject Classification

Once the path is normalised into short_page, a CASE statement maps it to a human-readable subject area.

WHEN regexp_like(tags.path, '.*tax-code.*') THEN 'Tax Code'
WHEN regexp_like(tags.path, '.*employment.*') THEN 'Employment'
WHEN regexp_like(tags.path, '.*underpayment.*') THEN 'Underpayment'
WHEN regexp_like(tags.path, '.*pension.*') THEN 'Pension'

Key Design Decisions Worth Noting

Why not just one big regex? A single pattern trying to handle query strings, short numerics, mixed alphanumerics, and UUID-like identifiers simultaneously becomes difficult to reason about and maintain. The COALESCE waterfall makes each case explicit and independently testable.

Why [AN] as the placeholder? It is short, visually distinctive, and documents that a dynamic alphanumeric segment was present.

Why the 1–3 digit limit? Tax year ranges such as /2021-2022/ contain four-digit numbers. Setting the ceiling at three digits helps avoid accidentally tokenising tax years.

Why a negative lookahead? The lookahead (?![0-9]{4}\-[0-9]{4}) protects /2021-2022/ while still allowing other hyphenated token patterns to be normalised.