For Data Teams: UTM Data in the Warehouse

For the analytics engineer turning the chapters’ rules into the model every report reads. Each section is one operation in words, with its input and output in Stride’s values, and a pointer to the chapter that teaches the idea.

The campaign dimension

One row per distinct set of recorded values, one column per fact a report groups by. Sessions, orders and leads point at it; spend joins on its campaign and platform columns. Every report gets the same cleaning, mapping and channel rule. The row for a click on Stride’s spring sale Facebook ad:

Column: from	Stride example
raw: the click	as recorded
source: clean, map	facebook
medium: clean, map	paid_social
campaign: clean, map	spring_sale-runners-2025_03
content: clean	video-testimonial-15sec
channel: rule	Paid Social
version: shape	1
region: lookup	us
initiative: parse	spring_sale
product: parse	runners
launch: parse	2025_03
platform: rule	Meta

Keep the raw values beside the clean ones. The checks read them, and a cleaned column can’t show what arrived.

Cleaning: lowercase and trim

The first step on every slot: lowercase the value and cut spaces from both ends. It can’t damage an approved value, since those are lowercase with no spaces already (Chapter 3).

Recorded	Cleaned
Facebook	facebook
FB	fb
fb	fb
Spring Sale Runners	spring sale runners

Cleaning fixes case, never spelling. It fixes Marcus’s Facebook (Chapter 8) on its own, but FB and fb come out as fb, which still isn’t facebook, and the spaces inside Marcus’s campaign stay. Both go to the mapping table.

The mapping table

Every spelling that differs in more than case gets a row. It’s Chapter 1’s remap and Chapter 10’s mapping table, kept where the model joins to it.

Cleaned value, then approved	Slot
fb facebook	source
spring sale runners spring_sale-runners-2025_03	campaign
spring-sale-runners-2025 spring_sale-runners-2025_03	campaign
mailchimp none: two meanings	source
any three-segment name us, by lookup	region

A value maps only if it meant one thing, so mailchimp, written on newsletters and promotions alike, gets no approved value. Its row keeps the approved value empty and a reason column says why, so reports show it as its own labeled row. The last row is a lookup, filling a column old names never had: before Chapter 10’s change, Stride sold only in the US.

Two choices shape the table:

• What it keys on. Keyed on cleaned values, as above, the map runs after cleaning and one row covers every casing. Keyed on raw values, the map runs before cleaning, and every casing needs its own row (FB and fb), a full record of what arrived.
• Where it’s edited. A sheet the taxonomy owner edits takes a row without an engineer or a review. A file kept with the model’s code is reviewed and versioned, and each row waits for someone who can commit it.

Channel grouping

Channel comes from the cleaned, mapped source and medium. Matching GA4’s default reports means GA4’s rules (Google, checked 2026-10-02): any paid medium makes a visit paid, and the source decides which paid channel.

Source	Medium	Channel
search (google)	paid (cpc)	Paid Search
social (facebook)	paid (paid_social)	Paid Social
video (youtube)	paid (paid_video)	Paid Video
any other	paid	Paid Other
any	social	Organic Social
any	email	Email
any	offline	Unassigned

Google’s source-category list, linked from that page, says which sources count as search, social or video. So facebook / cpc lands in Paid Social. video, display, affiliate, referral and audio map by medium to Organic Video, Display, Affiliates, Referral and Audio. The full rules are in GA4 Field Notes.

Where offline lands is a decision. Under GA4’s defaults, Stride’s retail_display / offline lands in Unassigned:

• Mirror GA4, and the warehouse agrees with it row for row, while the QR code shares Unassigned with traffic nobody can explain.
• Give offline its own channel, and the QR code reads as itself, but the warehouse disagrees with GA4’s default report unless GA4 gets the same custom channel group.

GA4 ignores case; an exact-string rule doesn’t, so Facebook lands in Paid Other unless you group after cleaning.

Parsing names into columns

Parsing splits the campaign into its facts (Chapter 4). Structured: split on the hyphen; position names each part. Key-Value: split on the hyphen into pairs, split each pair at the colon, and read each value by its key; a key the name doesn’t carry reads empty.

Campaign	Shape: reads as
spring_sale-runners-2025_03	3: initiative, product, launch
us-fall_sale-runners-2025_09	4: region, then those three
init:spring_sale-prd:runners-launch:2025_03	KV: by key; no geo, no region

Detect the format by shape before choosing a parser:

1. A : anywhere: Key-Value.
2. Three segments: version 1, [initiative]-[product]-[yyyy_mm].
3. Four segments: version 2, [region]-[initiative]-[product]-[yyyy_mm].
4. Anything else: no parser. It goes to the checks.

Count a segment for every place between hyphens, empty ones included: us--shoes has three (Chapter 4). A split that drops empty pieces counts two, and every value after the gap shifts.

Shape alone reads Marcus’s four-segment spring-sale-runners-2025 as version 2: region spring, initiative sale, product runners, launch 2025. Map before you parse, and it arrives as spring_sale-runners-2025_03. Hold each parsed segment against its own list (regions us and europe; a launch is a year and a month), and anything that slips past the map still fails.

Versioning by shape, not date

After a cutover, a click’s date no longer says which rules made its link. Stride’s change (Chapter 10) applies to campaigns launching from 2025_09, running campaigns finish as they are, and the Fleet Feet NYC QR code (stride.co/nyc25) keeps sending spring_sale-runners-2025_03 long after its sale.

Clicks arriving after the cutover:

Campaign	Version, region
europe-fall_sale-runners-2025_09	2, europe
us-fall_sale-runners-2025_09	2, us
spring_sale-runners-2025_03	1, us by lookup

The version comes from the shape; the first two regions are parsed. A rule by date would hand the last row to the version 2 parser: region spring_sale, initiative runners, product 2025_03, launch empty. By shape it’s version 1, and the lookup gives it a region. That code is the tail Dana remaps instead of reprinting.

The July relabel: one value, two meanings

On July 1, a team starts tagging paid posts paid_social instead of social (Chapter 10; example numbers, in sessions):

Medium	Last July	This July
social	4,000	1,600
paid_social		2,800
Both	4,000	4,400

Read row by row, organic social fell 60%. Read with the record, only the sum compares, and it rose 10%.

Last July’s social held both kinds of post, so it can’t be remapped (Chapter 10), and the mapping table records a break: social before July 1, no approved value, compare the sum. The model carries it as a combined grouping compared across the date, or as a flag on the rows before it.

The spend join, at a stated grain

Spend and revenue meet on two keys, the campaign and the platform (Chapter 6). Meta’s spend pairs only with facebook and instagram rows of paid_social; the newsletter’s rows for the same campaign stay out. In Chapter 6’s example the ad ran on Facebook only, so its row is facebook / paid_social. Then state the grain and sum both sides to it first: here, one row per campaign, platform and week. Chapter 6’s worked row, in its example numbers:

Column	The sale’s first week
campaign	spring_sale-runners-2025_03
platform	Meta (facebook / paid_social)
spend	$2,400
revenue	$8,400, from 60 orders
ROAS	3.5

Meta's spend

summed per campaign, platform and week

• week
• platform
• campaign_name (join key)
• spend

same value in bothspring_sale-runners-2025_03

campaign_name and utm_campaign both hold

spring_sale-runners-2025_03

Analytics revenue

facebook / paid_social, summed the same way

• week
• utm_source
• utm_medium
• utm_campaign (join key)
• revenue

Skip the summing and the grains collide. Spend arrives a row per campaign per day, orders a row per order, so every order repeats its day’s spend, and the spend added up afterwards counts once per order.

Leads join the same way, on campaign and platform (Chapter 7): Meta’s rows meet leads whose source is facebook or instagram. Which of a lead’s two campaigns you use is a crediting choice: the first-touch campaign reads spend against first touch (Chapter 11).

Touch history and cross-device

First-touch and last-touch fields keep the ends of a journey (Chapter 7). Crediting the middle (Chapter 11) needs a touch history, one row per tagged visit, tied to the lead when the form is sent. It lives in one of two places:

	In the browser	On the server
Keeps	a list of touches, sent with the form	each tagged visit as an event
Ties visits by	the browser itself	an anonymous ID, then a user ID
Loses	other devices; cleared storage	other devices, until the person signs in there
Costs	a script, and a field for the list	an event pipeline and storage

A what-if: had Maya tapped the Facebook ad on her phone and signed up from the Instagram ad on a laptop, both touch fields would read instagram / paid_social. The laptop never saw the Facebook visit. The phone’s visit sits under an anonymous ID until she signs in there and it’s stitched to her user ID; until then, she is two paths.

A lead captured before the touch history existed keeps only what its two fields kept, so the choice comes before the first form ships.

The automated checks

Chapter 8’s audit keeps two numbers, which the model can compute on every load. They read raw values since the last audit. Computed on clean ones, they’d hide Marcus’s rows: cleaning fixes his Facebook, and the map folds his campaigns into the spring sale.

On Dana’s pull, three weeks in:

Check	On Dana’s pull	Should move
unapproved values per slot	2 campaign values	toward zero
campaign names on the pattern	1 of 3	toward all

Counting unapproved values, not all of them, keeps launches out of the number. A value counts even when cleaning or the map already fixes it, so Marcus’s Facebook counts in the source slot. Values with a mapping row are known; values without one are new.

During a change of format, a third joins them: the old format’s share of new clicks, which should fall to the NYC code. Dana’s change closes when every old-format click comes from a known, remapped link (Chapter 10).

Each find goes to the taxonomy owner, in Chapter 8’s order: fix the link where it started, then add the mapping row.