For Data Teams: UTM Data in the Warehouse

For data engineers, analytics engineers, and BI developers who meet UTM data downstream: schema design, normalization patterns, parsing structured campaign values, and taxonomy version handling. Chapter 10 introduced this pipeline at a high level. This is the implementation detail.

Where UTM Data Lives in the Warehouse

UTM data arrives in your warehouse from multiple sources, each with its own schema, latency, and reliability characteristics:

Source	What it provides	How it arrives	Key fields
Web analytics (GA4 BigQuery export, Adobe Data Feeds)	Event-level UTM values, session-scoped last-click attribution, engagement metrics	Daily/streaming export	collected_traffic_source.manual_* (per event), session_traffic_source_last_click (per session), traffic_source.* (user first touch)
CRM (Salesforce, HubSpot)	Lead/contact-level UTM fields + pipeline outcomes	API extract or CDC	utm_source__c, utm_medium__c, utm_campaign__c (custom fields)
Ad platforms (Google Ads, Meta, LinkedIn)	Campaign/ad-level spend, impressions, clicks	API extract	campaign_name, campaign_id, ad_group_name, ad_id
UTM management platform (Terminus, etc.)	Link-level metadata, taxonomy rules, approved values	API extract	link_id, campaign_id, governed field values

The central challenge: joining these sources into a unified view where a single campaign can be traced from ad spend through website visit through lead creation through closed revenue. None of these systems were designed with the others in mind. The joins are yours to build.

Schema Design: The Campaign Dimension Table

Model UTM data as a star schema with one central campaign dimension table that every fact table references.

-- Campaign dimension table
CREATE TABLE dim_campaign (
    campaign_key        INT PRIMARY KEY,        -- surrogate key
    utm_source          VARCHAR(255),
    utm_medium          VARCHAR(255),
    utm_campaign        VARCHAR(255),
    utm_term            VARCHAR(255),
    utm_content         VARCHAR(255),

    -- Parsed dimensions (from structured campaign values)
    campaign_initiative VARCHAR(255),           -- e.g., 'spring_sale'
    campaign_product    VARCHAR(255),           -- e.g., 'shoes'
    campaign_region     VARCHAR(255),           -- e.g., 'us'
    campaign_date       VARCHAR(20),            -- e.g., '2025_q2'
    campaign_objective  VARCHAR(100),           -- e.g., 'conversion'

    -- Channel grouping (derived)
    channel_group       VARCHAR(100),           -- e.g., 'Paid Social'

    -- Metadata
    taxonomy_version    INT,
    first_seen_date     DATE,
    last_seen_date      DATE,
    is_governed         BOOLEAN                 -- matched approved values?
);

Skip the dimension table and every analyst writes their own parsing logic, channel grouping rules, and normalization queries. You end up with three definitions of “Paid Social” across three dashboards, each defended in a different meeting. The dimension table is the single source of truth for “what does this campaign combination mean?”

Fact tables (sessions, conversions, leads, revenue) reference campaign_key via foreign key, keeping them lean and joinable.

Normalizing Messy UTM Data

Real-world UTM data is messy. The marketing chapters of this guide exist to make it arrive clean; assume they were read selectively. Your transformation layer handles the rest:

Casing normalization:

-- dbt model: stg_utm_normalized.sql
SELECT
    LOWER(TRIM(utm_source))   AS utm_source,
    LOWER(TRIM(utm_medium))   AS utm_medium,
    LOWER(TRIM(utm_campaign)) AS utm_campaign,
    LOWER(TRIM(utm_term))     AS utm_term,
    LOWER(TRIM(utm_content))  AS utm_content
FROM {{ source('analytics', 'raw_sessions') }}

Value consolidation (legacy cleanup):

-- dbt model: int_utm_consolidated.sql
SELECT
    *,
    CASE utm_source
        WHEN 'fb'        THEN 'facebook'
        WHEN 'ig'        THEN 'instagram'
        WHEN 'li'        THEN 'linkedin'
        WHEN 'linked-in' THEN 'linkedin'
        WHEN 'yt'        THEN 'youtube'
        ELSE utm_source
    END AS utm_source_clean,

    CASE
        WHEN utm_medium IN ('paid-social', 'paidsocial', 'social-paid')
            THEN 'paid_social'
        WHEN utm_medium IN ('cpc', 'ppc', 'paid-search', 'sem')
            THEN 'cpc'
        WHEN utm_medium IN ('email', 'e-mail', 'email-marketing')
            THEN 'email'
        ELSE utm_medium
    END AS utm_medium_clean
FROM {{ ref('stg_utm_normalized') }}

Maintain these mappings in a seed file (utm_value_mappings.csv), not hardcoded SQL. The marketing team can then update a mapping without an engineering ticket, and you never become the human API between a spreadsheet and a CASE statement:

raw_value,field,clean_value
fb,utm_source,facebook
ig,utm_source,instagram
paid-social,utm_medium,paid_social
paidsocial,utm_medium,paid_social

-- Use the seed as a lookup table
SELECT
    s.*,
    COALESCE(m.clean_value, s.utm_source) AS utm_source_clean
FROM {{ ref('stg_utm_normalized') }} s
LEFT JOIN {{ ref('utm_value_mappings') }} m
    ON s.utm_source = m.raw_value
    AND m.field = 'utm_source'

Channel Grouping Logic

Analytics platforms apply channel grouping rules automatically. Your warehouse needs its own implementation, and it’s one of the most valuable transformations you can build: it turns raw source/medium combinations into the high-level channel categories that business stakeholders actually think in.

-- dbt model: int_channel_grouping.sql
SELECT
    *,
    CASE
        -- Paid Search
        WHEN utm_medium_clean IN ('cpc', 'ppc')
            AND utm_source_clean IN ('google', 'bing', 'yahoo')
            THEN 'Paid Search'

        -- Paid Social
        WHEN utm_medium_clean = 'paid_social'
            THEN 'Paid Social'

        -- Organic Social
        WHEN utm_medium_clean = 'social'
            THEN 'Organic Social'

        -- Email
        WHEN utm_medium_clean = 'email'
            THEN 'Email'

        -- Display
        WHEN utm_medium_clean = 'display'
            THEN 'Display'

        -- Affiliate
        WHEN utm_medium_clean = 'affiliate'
            THEN 'Affiliate'

        -- Referral
        WHEN utm_medium_clean = 'referral'
            THEN 'Referral'

        -- Organic Search (no UTMs: from analytics source data)
        WHEN utm_medium_clean = 'organic'
            THEN 'Organic Search'

        -- Direct
        WHEN utm_source_clean IS NULL
            OR utm_source_clean IN ('(direct)', '(none)', '')
            THEN 'Direct'

        -- Catch-all
        ELSE 'Other'
    END AS channel_group
FROM {{ ref('int_utm_consolidated') }}

Keep your warehouse channel grouping rules aligned with your analytics platform’s rules (GA4, Adobe, etc.). When the dashboard and the warehouse disagree, nobody audits the pipeline; they just stop trusting the warehouse. Document any intentional differences.

Parsing Structured Campaign Values

If your organization uses structured utm_campaign values (Chapter 6), parse them into individual dimensions for filtering and aggregation.

Positional (hyphen-delimited) parsing:

For values like us-cpc-google-spring_sale-awareness-2025_q2:

-- dbt model: int_campaign_parsed.sql
SELECT
    utm_campaign,
    SPLIT_PART(utm_campaign, '-', 1) AS campaign_region,
    SPLIT_PART(utm_campaign, '-', 2) AS campaign_medium,
    SPLIT_PART(utm_campaign, '-', 3) AS campaign_platform,
    SPLIT_PART(utm_campaign, '-', 4) AS campaign_initiative,
    SPLIT_PART(utm_campaign, '-', 5) AS campaign_objective,
    SPLIT_PART(utm_campaign, '-', 6) AS campaign_date
FROM {{ ref('int_utm_consolidated') }}
WHERE utm_campaign LIKE '%-%-%-%'  -- only parse structured values

Key-Value parsing:

For values like geo:us-obj:awareness-prd:shoes-q:q2:

-- dbt model: int_campaign_kv_parsed.sql
SELECT
    utm_campaign,
    REGEXP_SUBSTR(utm_campaign, 'geo:([^-]+)', 1, 1, 'e')  AS campaign_region,
    REGEXP_SUBSTR(utm_campaign, 'obj:([^-]+)', 1, 1, 'e')  AS campaign_objective,
    REGEXP_SUBSTR(utm_campaign, 'prd:([^-]+)', 1, 1, 'e')  AS campaign_product,
    REGEXP_SUBSTR(utm_campaign, 'q:([^-]+)', 1, 1, 'e')    AS campaign_quarter
FROM {{ ref('int_utm_consolidated') }}
WHERE utm_campaign LIKE '%:%'  -- only parse key-value format

Handling mixed formats: During taxonomy transitions you’ll have flat values (spring_sale) and structured values (us-cpc-google-spring_sale-awareness-2025_q2) coexisting. Use a CASE statement or a format-detection flag to route each value to the appropriate parser:

CASE
    WHEN utm_campaign LIKE '%:%'   THEN 'key_value'
    WHEN utm_campaign LIKE '%-%-%' THEN 'structured'
    ELSE 'flat'
END AS campaign_format

Handling Taxonomy Version Changes (Slowly-Changing Dimensions)

The marketing team will update their taxonomy: split social into paid_social and organic_social, add a new region dimension, rename a campaign. When they do, you face a classic SCD (slowly-changing dimension) problem. You’ve solved this before for product catalogs and sales territories. Same problem, same toolkit.

Type 2 SCD for taxonomy versions:

Track the full history of taxonomy changes by versioning the dimension table:

CREATE TABLE dim_campaign_history (
    campaign_key         INT PRIMARY KEY,
    campaign_natural_key VARCHAR(500),          -- source + medium + campaign composite
    utm_source           VARCHAR(255),
    utm_medium           VARCHAR(255),
    utm_campaign         VARCHAR(255),
    channel_group        VARCHAR(100),

    -- SCD Type 2 fields
    taxonomy_version     INT,
    effective_from       DATE,
    effective_to         DATE,                  -- NULL = current
    is_current           BOOLEAN
);

This answers both “what channel group was this campaign in when it ran?” and “what channel group would it be in under today’s rules?” Two very different questions, and analysts conflate them routinely.

Practical approach for most teams:

Full SCD Type 2 is overkill for many organizations. A simpler alternative:

1. Document taxonomy versions with effective dates (the guide recommends this in Chapter 7).
2. Tag each record with its taxonomy version at ingestion time.
3. Build separate “as-reported” and “as-normalized” views:

-- as-reported: uses the original values, no normalization
CREATE VIEW v_campaigns_as_reported AS
SELECT
    f.*,
    d.utm_source,
    d.utm_medium,
    d.utm_campaign,
    d.channel_group
FROM fact_sessions f
JOIN dim_campaign d ON f.campaign_key = d.campaign_key;

-- as-normalized: applies current taxonomy rules to all historical data
CREATE VIEW v_campaigns_normalized AS
SELECT
    f.*,
    COALESCE(m.clean_value, d.utm_medium) AS utm_medium_current
FROM fact_sessions f
JOIN dim_campaign d ON f.campaign_key = d.campaign_key
LEFT JOIN {{ ref('utm_value_mappings') }} m
    ON d.utm_medium = m.raw_value AND m.field = 'utm_medium';

The “as-reported” view preserves history exactly as it happened. The “as-normalized” view gives you apples-to-apples comparisons across taxonomy versions. Build both; the first question after any taxonomy change is a comparison across the change.

Joining UTM Data Across Sources

The highest-value join in marketing analytics: connecting ad spend (from ad platforms) to website behavior (from analytics) to revenue (from CRM).

Ad Platform Data          Web Analytics             CRM
(spend, impressions)      (sessions, events)        (leads, opportunities, revenue)
       │                        │                          │
       └──── campaign_name ─────┼──── utm_campaign ────────┘
             ad_group_name      │    utm_source
             ad_name            │    utm_medium
                                │
                          ┌─────┴─────┐
                          │           │
                     dim_campaign   dim_campaign
                     (campaign_key)

The join challenge: Ad platform data uses campaign/ad group/ad names (or IDs). Web analytics uses UTM values. CRM uses custom fields populated from form submissions. These don’t always match exactly, and they especially don’t match when ad platform naming and UTM naming aren’t governed by the same taxonomy (see Chapter 7b).

Practical join strategies:

-- Strategy 1: Direct join on campaign name
-- Works when ad platform campaign names match utm_campaign values
SELECT
    a.campaign_name,
    a.spend,
    a.impressions,
    w.sessions,
    w.conversions,
    c.pipeline_value,
    c.closed_revenue,
    -- Derived metrics
    a.spend / NULLIF(w.sessions, 0)     AS cost_per_session,
    a.spend / NULLIF(c.closed_revenue, 0) AS cost_per_revenue_dollar
FROM mart_ad_platform_daily a
JOIN mart_web_sessions_daily w
    ON a.campaign_name = w.utm_campaign
    AND a.date = w.date
LEFT JOIN mart_crm_attribution c
    ON w.utm_campaign = c.utm_campaign;

-- Strategy 2: Lookup table for mismatched names
-- When ad platform names don't match UTM values
SELECT a.*, w.*, c.*
FROM mart_ad_platform_daily a
JOIN dim_campaign_crosswalk x
    ON a.campaign_name = x.ad_platform_campaign_name
JOIN mart_web_sessions_daily w
    ON x.utm_campaign = w.utm_campaign
    AND a.date = w.date
LEFT JOIN mart_crm_attribution c
    ON w.utm_campaign = c.utm_campaign;

The crosswalk table (dim_campaign_crosswalk) maps ad platform campaign names to UTM campaign values. You need it whenever auto-tagging (e.g., Google Ads’ gclid) means the campaign name in analytics comes from the ad platform rather than from manual UTMs.

Data Quality Monitoring

Build automated checks that flag UTM data quality issues before they reach dashboards:

-- dbt test: check for ungoverned values
-- This query finds utm_source values not in the approved list
SELECT
    utm_source_clean,
    COUNT(*) AS session_count,
    MIN(session_date) AS first_seen,
    MAX(session_date) AS last_seen
FROM {{ ref('int_utm_consolidated') }}
WHERE utm_source_clean NOT IN (
    SELECT approved_value
    FROM {{ ref('approved_utm_values') }}
    WHERE field = 'utm_source'
)
AND utm_source_clean IS NOT NULL
AND session_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY utm_source_clean
ORDER BY session_count DESC;

Key quality checks to automate:

Check	What it catches	Frequency
Ungoverned values	New source/medium values not in approved list	Daily
Fragmentation rate	Unique values / true entities ratio trending up	Weekly
NULL rate	Percentage of sessions missing source, medium, or campaign	Daily
Format violations	Uppercase, spaces, special characters in UTM values	Daily
Orphaned campaigns	UTM campaign values with no matching ad platform campaign	Weekly
Cross-source consistency	Same campaign showing different source/medium in analytics vs. CRM	Weekly

Wire fragmentation rate up first; it compresses the whole problem into one number. Say marketing runs 8 real traffic sources and COUNT(DISTINCT utm_source_clean) comes back 31. That’s 31/8 = 3.9x: nearly four warehouse values for every real-world source, and every extra value is a GROUP BY row quietly splitting a channel’s performance. Clean is 1.0x. The trend line matters more than the level.

Hook the rest into your data pipeline alerts (dbt tests, Great Expectations, Monte Carlo, etc.) so the marketing team learns about taxonomy violations within days, not months.

Recommended dbt Project Structure

models/
├── staging/
│   ├── stg_ga4_sessions.sql          -- Raw GA4 BigQuery export
│   ├── stg_crm_leads.sql            -- Raw CRM lead extract
│   └── stg_ad_platform_campaigns.sql -- Raw ad platform data
├── intermediate/
│   ├── int_utm_normalized.sql        -- Lowercase, trim
│   ├── int_utm_consolidated.sql      -- Legacy value mapping
│   ├── int_channel_grouping.sql      -- Channel group derivation
│   └── int_campaign_parsed.sql       -- Structured value parsing
├── marts/
│   ├── dim_campaign.sql              -- Campaign dimension table
│   ├── mart_campaign_performance.sql -- Spend + sessions + conversions
│   └── mart_attribution.sql          -- Multi-touch attribution
└── seeds/
    ├── utm_value_mappings.csv        -- Legacy → clean value mappings
    ├── approved_utm_values.csv       -- Current approved values
    └── campaign_crosswalk.csv        -- Ad platform → UTM name mapping

This structure separates concerns: staging handles extraction, intermediate handles transformation and normalization, marts handle business logic and aggregation. Seeds keep marketing-owned data (approved values, mappings) version-controlled alongside the code that uses it.

---

Key Points

• Model UTM data as a star schema with a central campaign dimension table, or watch every analyst write a private definition of “Paid Social”
• Keep normalization mappings in seed files, not hardcoded SQL, so the marketing team can update them without an engineering ticket
• Build both “as-reported” and “as-normalized” views; taxonomy versions change, and history shouldn’t need rewriting when they do
• The highest-value join runs ad spend to web sessions to CRM revenue, and it only works when ad platform names and UTM values share one taxonomy (Chapter 7b)
• Automate checks for ungoverned values, fragmentation rate, and NULL rates, and taxonomy drift surfaces in days instead of months

Action Item: Count the distinct utm_source values in your warehouse for the last 90 days. If the number is more than 2x the traffic sources your team actually uses, you have a normalization problem, and a seed file plus one dbt model fixes it this week.