The gap every attribution project falls into
Every attribution tutorial you have ever read starts with a tidy little dataframe where each row is already a full customer journey. Reality never hands you that. Reality hands you a billion raw events - impressions, clicks, opens - scattered across Snowplow, Segment, a GA4 export, and a Meta CAPI feed, with no notion of "journey" anywhere. Session 1 builds the missing layer: the pipeline that stitches raw events into an ordered, per-customer path table the rest of the course can actually model on. We do it in SQL, on Lumen, with window functions carrying most of the weight.
touchpoints events and emits (1) an ordered touchpoint table with position_in_path and is_converting_touch, (2) one path string per customer for Markov and Shapley later, and (3) first-touch and last-touch credit in pure SQL. Every later session imports this output.
The three-table data model 6 min live
Lumen's whole world lives in three tables. Get these right and everything downstream - heuristics, Markov, Shapley, MMM - is just a query away. touchpoints is the event log (the grain is one interaction), conversions records the sale, and spend_weekly is the aggregate feed the mix models in B8 will need. Learn the keys that join them.
LiveWhy the grain matters3 min▶
The single most important word in this session is grain - the thing one row represents. In touchpoints, one row is one interaction: a single impression, click, or open. Not a session, not a customer, not a journey. Everything else is built up from that atom by grouping and ordering.
- touchpoints - grain = one interaction. This is your raw material. Millions of rows, no order imposed yet.
- conversions - grain = one sale. This is the label: did the journey pay off, and for how much (
order_value)? - spend_weekly - grain = one channel-week-geo cell. No customer key at all. It exists for mix modeling, which never sees an individual.
The classic bug: someone joins touchpoints to conversions on customer_id without deduping, so a customer with 5 touches and 1 order suddenly has 5 conversion rows and $460 of revenue instead of $92. Grain discipline - knowing exactly what one row means at every step - is what stops fan-out joins from silently 5x-ing your revenue.
LiveThe 9-channel enum, fixed everywhere2 min▶
Lumen has exactly nine channels, and the order is fixed across every session so your outputs reconcile. Treat it as an enum, validate against it on ingest, and reject anything else.
| Create demand (early) | Capture demand (late) | In between |
|---|---|---|
| display, paid_social, influencer, ctv | paid_search, organic_search, direct | email, affiliate |
Enum drift is a real production killer: a rogue "Paid Social " with a trailing space becomes a tenth channel, splits your credit, and nobody notices for a quarter. Lowercase, snake_case, validated on write.
From raw events to journeys with window functions 5 min live
Here is the data-stack pattern the whole industry converged on. Raw events land from your collectors, the warehouse holds them cheaply, dbt sessionizes and stitches, and out the other end comes a touchpoint fact table with an ordered path per customer. The magic in the middle is a small set of window functions.
LiveThe four window functions that do the work3 min▶
Attribution heuristics are not exotic - they are window functions in disguise. Once you see the mapping, first-touch and last-touch stop being "models" and become one-liners.
- ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_ts) - stamps each touch with its
position_in_path. Position 1 is the first-touch, the max is the last. - FIRST_VALUE(channel) over that same window - the first-touch channel, directly.
- LAST_VALUE(channel) - the last-touch channel, but only if you fix the frame (see the demo - the default frame bites everyone once).
- STRING_AGG / ARRAY_AGG(channel ORDER BY event_ts) - collapses the journey into one
pathvalue, which is the input format Markov (B4) and Shapley (B5) expect.
PARTITION BY customer_id ORDER BY event_ts. That partition-and-order is the single idea underneath every position-based attribution rule. Learn it once, reuse it everywhere.
Attribution windows and the path string 4 min live
Two decisions quietly shape every number downstream: how far back you look, and how you serialize a journey. Both must be stated explicitly, because both are choices - not facts.
Self-studyLookback windows - state them or regret them2 min read▶
An attribution window (or lookback) is the maximum age of a touch that is allowed to earn credit for a conversion. Lumen uses a 90-day lookback for the standard model, matching its 7-21 day consideration cycle with headroom. A touch older than 90 days before the sale is dropped from the path.
- Too short and you amputate the demand-creating channels - a display impression 40 days out vanishes, and display looks worthless (the Session A1 mistake, now in SQL).
- Too long and you glue unrelated journeys together, crediting a channel for a sale it had nothing to do with.
- Always write it in the query and the report. "Paid search drove 41%" is meaningless without "under a 90-day last-touch window". The window is part of the number.
Self-studyWhy the path string matters2 min read▶
The path string - display > paid_social > email > organic_search > paid_search - looks trivial, but it is the pivot of the entire course. Markov models (B4) build a transition matrix by counting how often one channel follows another in that string. Shapley (B5) treats the set of channels in the string as a coalition. Get the ordering or the delimiter wrong here and every learned model downstream is wrong too.
Teams often store the path with a comma delimiter, then a campaign name contains a comma, and the whole path parser shatters months later. Pick a delimiter that cannot appear in a channel name ( > is safe because channels are a fixed enum), and validate it on write.
Build the ordered touchpoint table ★ 8 min · everyone
Our first query turns raw Lumen events into a numbered journey. ROW_NUMBER() gives every touch its position; a windowed MAX flags the converting touch. This is the table every later session imports.
Open your warehouse (BigQuery or SQLite both run this with tiny dialect tweaks). Point it at Lumen's raw touchpoints.
Apply the 90-day lookback in the WHERE, then number and flag with window functions:
-- Ordered Lumen touchpoint fact table
-- One row per interaction, numbered within each customer journey
SELECT
touchpoint_id,
customer_id,
event_ts,
channel,
interaction_type,
cost,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY event_ts
) AS position_in_path,
-- the last touch in each journey = the converting touch
CASE
WHEN event_ts = MAX(event_ts) OVER (PARTITION BY customer_id)
THEN TRUE ELSE FALSE
END AS is_converting_touch
FROM touchpoints
WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
ORDER BY customer_id, position_in_path;
Run it and eyeball Lumen's canonical customer: five rows, position_in_path 1 through 5, display at position 1 and paid_search at position 5 with is_converting_touch = TRUE. That matches the $92 journey exactly.
Materialize it (CREATE TABLE lumen_fct_touchpoints AS ... or a dbt model). Every session from B2 on reads this table, so name it once and never recompute the ordering ad hoc.
ROW_NUMBER and MAX ... OVER are safe here, but LAST_VALUE is not - it uses a running frame by default. We fix that in Build-along 3. If your "last touch" ever looks like the current row instead of the real last one, that frame is why.
Build the journey path string per customer ★ 7 min · everyone
Now collapse each ordered journey into a single path string. STRING_AGG with an ORDER BY inside it is the one-liner that feeds Markov and Shapley.
Aggregate the fact table down to one row per customer, ordering channels by time inside the aggregate:
-- Collapse each customer's ordered touches into ONE path string SELECT customer_id, STRING_AGG(channel, ' > ' ORDER BY event_ts) AS path, COUNT(*) AS n_touches, MIN(event_ts) AS first_touch_ts, MAX(event_ts) AS last_touch_ts FROM lumen_fct_touchpoints GROUP BY customer_id; -- Lumen's canonical customer returns: -- display > paid_social > email > organic_search > paid_search -- n_touches = 5
On BigQuery it is STRING_AGG; on Snowflake LISTAGG; on Postgres STRING_AGG too but the ORDER BY goes WITHIN GROUP. Same idea, watch the dialect.
Prefer ARRAY_AGG(channel ORDER BY event_ts) when the next step is Python - you get a real list, no re-parsing a delimited string.
Sanity-check: COUNT(DISTINCT path) tells you how many unique journey shapes Lumen has. That number is the state space Markov will work over in B4.
This one table - customer_id, path, n_touches - is often the single most reused artifact in a whole attribution stack. Analysts build dashboards off it, data scientists featurize from it, and the Markov/Shapley jobs read it directly. Ship it clean and you have earned your keep for the quarter.
First-touch and last-touch in pure SQL ★ 7 min · everyone
Two attribution "models", zero libraries. FIRST_VALUE and LAST_VALUE read the endpoints of each journey straight out of the window - once you fix the frame.
Grab both endpoints per customer. The named WINDOW clause keeps it readable and lets both functions share one spec:
-- First-touch and last-touch credit, no library needed SELECT DISTINCT customer_id, FIRST_VALUE(channel) OVER w AS first_touch_channel, LAST_VALUE(channel) OVER w AS last_touch_channel FROM lumen_fct_touchpoints WINDOW w AS ( PARTITION BY customer_id ORDER BY event_ts -- CRITICAL: without this frame, LAST_VALUE returns the CURRENT row, -- not the real last touch of the journey ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ); -- Lumen canonical customer -> -- first_touch_channel = display, last_touch_channel = paid_search
Delete the ROWS BETWEEN ... line and rerun. Watch last_touch_channel turn into whatever row you are on. That is the default running frame - the bug that has cost more analysts a bad afternoon than any other in SQL attribution.
To turn these into dollars, join to conversions on customer_id and assign the full order_value to the first (or last) channel. First-touch hands display the whole $92; last-touch hands it to paid_search - exactly the mirror image from Leader Session 1.
Aggregate to channel level (GROUP BY last_touch_channel, SUM(order_value)) and you have just reproduced the last-click revenue report every dashboard ships - now you know precisely what rule it encodes.
This week ◐ 40 min total
- Run all three build-alongs on Lumen and materialize
lumen_fct_touchpoints. B2 assumes it exists - do not skip this. - Add an
attribution_window_daysparameter to the ordering query and try 30, 60, 90. Note how many touches drop out of the demand-creating channels at 30 days. - Write the dbt version. Turn Build-along 1 into a dbt model with a test that
position_in_pathstarts at 1 for every customer. This is the production shape. - Optional: compute
COUNT(DISTINCT path)and the top 10 most common paths on Lumen. You will recognize these journey shapes again in B4.
Three questions before you go 🎯 ◐ 90 seconds
1 · To turn a pile of raw events into ordered per-customer journeys, the window function should...
You partition by the journey key (customer_id) and order by the time key (event_ts). That one spec underlies position_in_path, first-touch, and last-touch alike.
2 · Why build the path string with STRING_AGG / ARRAY_AGG at all?
Markov counts channel-to-channel transitions in the ordered path; Shapley treats the channel set as a coalition. No path string, no learned models downstream.
3 · A colleague reports "paid search drove 41% of revenue." What is missing before you can trust it?
The window is part of the number. "41% under a 90-day last-touch window" is a claim; "41%" alone is not. Always state the lookback in the query and the report.
What this session covers
This is the coverage gap the course owns outright: no existing attribution course teaches the SQL pipeline that produces the journey table everyone else assumes you already have. We build it from the warehouse pattern the industry actually runs.