Case 5 — Marketing Attribution: First-Touch and Last-Touch

# Case 5 — Marketing Attribution: First-Touch and Last-Touch > *"From the `touchpoints(user_id, touch_time, channel)` table and `conversions(user_id, conversion_time, revenue)`, build first-touch and last-touch attribution. Show revenue attributed to each channel."* This question tests window function fluency AND business judgment. The SQL is straightforward; the closing comment about *why both models are wrong* is what separates senior candidates from junior ones. --- ## Schema ``` touchpoints(user_id, touch_time, channel) -- e.g., 'google', 'facebook', 'email', 'direct' conversions(user_id, conversion_time, revenue) ``` A touchpoint is any time a user interacts with a marketing channel before converting. A conversion is the purchase event. ## First-touch attribution The first channel that ever touched the user, before they converted. ```sql WITH first_touch AS ( SELECT user_id, FIRST_VALUE(channel) OVER (PARTITION BY user_id ORDER BY touch_time) AS first_channel FROM touchpoints ) SELECT ft.first_channel AS channel, SUM(c.revenue) AS attributed_revenue, COUNT(DISTINCT c.user_id) AS attributed_conversions FROM conversions c JOIN (SELECT DISTINCT user_id, first_channel FROM first_touch) ft ON ft.user_id = c.user_id GROUP BY ft.first_channel ORDER BY attributed_revenue DESC; ``` Simpler alternative without `FIRST_VALUE`: ```sql WITH first_touch AS ( SELECT user_id, channel, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time) AS rn FROM touchpoints ) SELECT ft.channel, SUM(c.revenue) AS attributed_revenue FROM conversions c JOIN first_touch ft ON ft.user_id = c.user_id AND ft.rn = 1 GROUP BY ft.channel; ``` Same answer. Some interviewers prefer FIRST_VALUE; others prefer ROW_NUMBER. Either is fine. ## Last-touch attribution The channel that touched them *before* the conversion (within some attribution window — typically 7 or 30 days). Without a window, "last touch before conversion" is the simplest: ```sql WITH last_touch AS ( SELECT c.user_id, c.conversion_time, c.revenue, ( SELECT t.channel FROM touchpoints t WHERE t.user_id = c.user_id AND t.touch_time <= c.conversion_time ORDER BY t.touch_time DESC LIMIT 1 ) AS last_channel FROM conversions c ) SELECT last_channel AS channel, SUM(revenue) AS attributed_revenue FROM last_touch GROUP BY last_channel ORDER BY attributed_revenue DESC; ``` The correlated subquery picks the most recent touchpoint per conversion. Alternative without correlation: ```sql WITH ranked_touches AS ( SELECT t.user_id, t.channel, t.touch_time, c.conversion_time, c.revenue, ROW_NUMBER() OVER ( PARTITION BY c.user_id, c.conversion_time ORDER BY t.touch_time DESC ) AS rn FROM conversions c JOIN touchpoints t ON t.user_id = c.user_id AND t.touch_time = c.conversion_time - INTERVAL '30 days' ``` Now only touchpoints in the 30 days before conversion are eligible. The cutoff depends on the business (B2C: 7-14 days; B2B: 30-90 days). ## Why both first-touch and last-touch are wrong This is the senior-analyst close. After producing the SQL: > **You**: "Both models have known problems. First-touch overweights awareness channels — people often discover via SEO/social months before converting, but those channels can't directly drive the purchase decision. Last-touch overweights conversion-driving channels — if someone always searches `'company name'` before checkout, search gets disproportionate credit but isn't doing the real persuasion work. The honest answer for the business is multi-touch or data-driven attribution — weight each touchpoint by its incremental contribution. That's harder to compute and usually lives in a dedicated attribution tool (Heap, AppsFlyer, in-house ML model), not in pure SQL." If the interviewer nods, follow up with: *"For a SQL-only solution that's better than first/last, equal-weight attribution (divide revenue equally across all touchpoints in the window) is one step up — easy to compute and at least acknowledges the multi-touch nature."* ```sql WITH touches_per_conv AS ( SELECT c.user_id, c.conversion_time, c.revenue, t.channel, COUNT(*) OVER (PARTITION BY c.user_id, c.conversion_time) AS total_touches FROM conversions c JOIN touchpoints t ON t.user_id = c.user_id AND t.touch_time <= c.conversion_time