First-Touch Attribution — Solution & ExplanationProblems / First-Touch Attribution / EditorialApproach

Arg-min per user. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time), keep rn = 1. This returns the *whole row* (and thus the channel) of the earliest touch — which MIN(touch_time) alone cannot do.

Revenue per user. Plain SUM(amount) GROUP BY user_id. Doing this before any join means later steps move one row per customer, so nothing can double-count.

Attribute and aggregate. Inner join revenue to first-touch (non-purchasers vanish; the data's touch-only user never appears), then per channel: COUNT(*) customers, SUM revenue, sort DESC with the channel tie-breaker.

Key InsightAttribution problems decompose into one fact per entity decisions: each customer needs exactly one channel (arg-min via ROW_NUMBER) and exactly one revenue number (pre-aggregation). Both CTEs exist to enforce that one-row-per-user invariant before the final join — collapse first, join after, and the per-channel rollup becomes trivially correct. Skip either collapse and the join grain silently multiplies revenue.Hints

'The channel of the earliest touch' is an arg-min: you need the row that minimizes touch_time, not the minimum itself. ROW_NUMBER ordered by touch_time, filtered to rn = 1, is the standard idiom.

MIN(touch_time) + GROUP BY channel is the classic wrong answer — a user first touched by ads and later by email would be attributed to both. Arg-min picks exactly one row per user.

Collapse purchases to per-user totals before joining the attribution on; joining raw purchases to touches invites fan-out.

The final sort is by attributed_revenue DESC with channel as tie-breaker — the data plants two channels with identical revenue to make a missing tie-breaker visible.

Solve First-Touch Attribution yourself →