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:

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

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] (alphanumeric), 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. Let’s break it apart.

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 ?.

Then regexp_replace is applied to that result:

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

Step 2 — The Main Normalisation Regex

This is the most complex part. It handles several distinct ID formats in a single regexp_replace pass using the | OR operator.

The full pattern

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

Targets tax codes like /1250L or 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. Targets things like /I48-TR18-T7S — identifiers that start with a letter, blend in numbers, and contain a hyphen.

Pattern C — Pure uppercase/digit tokens with a hyphen, excluding tax years

The most carefully tuned pattern. Catches things like /YPB-EMZG-IPLA and UUIDs, while deliberately not matching tax year ranges like /2021-2022.

Pattern D — Short numeric segments

The simplest pattern: catches a 1–3 digit number at the end of a path or as an interior segment.

The Referrer Version

The exact same logic is applied to the referrer URL, but without the /check-income-tax anchor since referrers can come from other GOV.UK services. After normalisation, the referrer is trimmed to a shorter form using:

This slices the referrer URL array from the 4th segment onwards (skipping protocol, empty string, and domain), joins it back into a path, then uses a regex with an atomic group (?>) to extract 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. This works precisely because the normalisation removed all user-specific tokens — the pattern .*tax-code.* now reliably matches all tax code pages regardless of the specific tax code in the original URL.

Key Design Decisions Worth Noting

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

Why [AN] as the placeholder? It’s short, visually distinctive, and survives additional regex passes without being mistaken for a real URL segment. It also documents intent — anyone reading the normalised path immediately knows a dynamic segment was here.

Why the 1–3 digit limit? Tax year ranges like /2021-2022/ contain four-digit numbers. Setting the ceiling at three digits means they’re never accidentally tokenised, preserving the ability to segment analytics by tax year.

Why a negative lookahead instead of a simpler pattern? The lookahead (?![0-9]{4}\-[0-9]{4}) in pattern C is the most surgical tool for protecting /2021-2022/. A character class approach would require excluding hyphens entirely, which would then miss UUIDs. The lookahead keeps the pattern broad while carving out exactly the shape we want to preserve.