First-Touch vs Last-Touch Revenue by Channel — SQL Problem
The marketing mix review compares two attribution models side by side.
- Difficulty: Hard
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #291
Problem statement
The marketing mix review compares two attribution models side by side. For a conversion, the qualifying touches are the same user's touchpoints at or before converted_at. First touch credits the conversion's whole revenue to the earliest qualifying touch (equal times: the lower touch_id); last touch credits it to the latest (equal times: the higher touch_id). Conversions with no qualifying touch are credited to nobody.
Return channel, first_touch_revenue and last_touch_revenue (the revenue credited to the channel under each model, 0 when none) for every channel credited under at least one model. Order by channel.
Tables
Table: Touchpoint
| Column | Type |
|---|---|
| touch_id | int |
| user_id | int |
| channel | enum(paid_search, paid_social, email, affiliate, organic) |
| touched_at | datetime |
Primary key: touch_id.
One row per tracked visit from a marketing channel.
Table: Conversion
| Column | Type |
|---|---|
| conversion_id | int |
| user_id | int |
| converted_at | datetime |
| revenue | int |
Primary key: conversion_id.
One row per order; revenue in rupees.
Examples
Example 1
Touchpoint
| touch_id | user_id | channel | touched_at |
|---|---|---|---|
| 1 | 7 | paid_social | 2024-11-01 09:00:00 |
| 2 | 7 | 2024-11-02 18:30:00 | |
| 3 | 7 | paid_search | 2024-11-03 10:00:00 |
| 4 | 8 | affiliate | 2024-11-02 12:00:00 |
| 5 | 8 | 2024-11-02 12:00:00 | |
| 6 | 9 | organic | 2024-11-05 08:15:00 |
| 7 | 7 | organic | 2024-11-06 08:00:00 |
Conversion
| conversion_id | user_id | converted_at | revenue |
|---|---|---|---|
| 101 | 7 | 2024-11-03 20:00:00 | 1500 |
| 102 | 8 | 2024-11-02 12:00:00 | 2600 |
| 103 | 9 | 2024-11-04 22:00:00 | 800 |
| 104 | 7 | 2024-11-02 19:00:00 | 400 |
Output
| channel | first_touch_revenue | last_touch_revenue |
|---|---|---|
| affiliate | 2600 | 0 |
| 0 | 3000 | |
| paid_search | 0 | 1500 |
| paid_social | 1900 | 0 |
How to solve First-Touch vs Last-Touch Revenue by Channel
Start by joining every conversion to its qualifying touches — same user, touched_at <= converted_at. A conversion with no such touch disappears from the inner join, which is exactly "credited to nobody". Touches after the conversion (user 7's organic visit on 6 November) never enter.
Within each conversion, ROW_NUMBER() ordered by touched_at, touch_id marks the first touch with 1, and the same window ordered descending marks the last touch. Breaking ties by touch_id in opposite directions is what the statement asks for — two touches at 12:00 on 2 November give the affiliate the first touch and email the last. A conversion with a single touch has first_rn = 1 and last_rn = 1 on the same row, so both conditional sums pick it up.
Aggregating per channel with SUM(CASE WHEN first_rn = 1 THEN revenue ELSE 0 END) (and the same for last) gives both models in one pass; filtering to the winning rows keeps channels that were merely touched from appearing with two zeros. The alternatives find each winner with an ordered scalar subquery, or with a NOT EXISTS "nobody earlier / later" anti join, and stack the two credit streams with UNION ALL. The window plan sorts the joined rows twice; the subquery plans probe an index on (user_id, touched_at) per conversion.
Reference solution (MySQL)
WITH q AS (
SELECT v.conversion_id, v.revenue, t.channel,
ROW_NUMBER() OVER (PARTITION BY v.conversion_id ORDER BY t.touched_at, t.touch_id) AS first_rn,
ROW_NUMBER() OVER (PARTITION BY v.conversion_id ORDER BY t.touched_at DESC, t.touch_id DESC) AS last_rn
FROM Conversion v
JOIN Touchpoint t ON t.user_id = v.user_id AND t.touched_at <= v.converted_at
)
SELECT channel,
SUM(CASE WHEN first_rn = 1 THEN revenue ELSE 0 END) AS first_touch_revenue,
SUM(CASE WHEN last_rn = 1 THEN revenue ELSE 0 END) AS last_touch_revenue
FROM q
WHERE first_rn = 1 OR last_rn = 1
GROUP BY channel
ORDER BY channelAnother way
WITH credit AS (
SELECT (SELECT t.channel FROM Touchpoint t WHERE t.user_id = v.user_id AND t.touched_at <= v.converted_at
ORDER BY t.touched_at, t.touch_id LIMIT 1) AS channel, v.revenue AS f, 0 AS l
FROM Conversion v
UNION ALL
SELECT (SELECT t.channel FROM Touchpoint t WHERE t.user_id = v.user_id AND t.touched_at <= v.converted_at
ORDER BY t.touched_at DESC, t.touch_id DESC LIMIT 1), 0, v.revenue
FROM Conversion v
)
SELECT channel, SUM(f) AS first_touch_revenue, SUM(l) AS last_touch_revenue
FROM credit WHERE channel IS NOT NULL
GROUP BY channel ORDER BY channelAnother way
WITH q AS (
SELECT v.conversion_id, v.revenue, t.touch_id, t.channel, t.touched_at
FROM Conversion v JOIN Touchpoint t ON t.user_id = v.user_id AND t.touched_at <= v.converted_at
), firsts AS (
SELECT a.channel, a.revenue FROM q a
WHERE NOT EXISTS (SELECT 1 FROM q b WHERE b.conversion_id = a.conversion_id
AND (b.touched_at < a.touched_at OR (b.touched_at = a.touched_at AND b.touch_id < a.touch_id)))
), lasts AS (
SELECT a.channel, a.revenue FROM q a
WHERE NOT EXISTS (SELECT 1 FROM q b WHERE b.conversion_id = a.conversion_id
AND (b.touched_at > a.touched_at OR (b.touched_at = a.touched_at AND b.touch_id > a.touch_id)))
)
SELECT channel, SUM(f) AS first_touch_revenue, SUM(l) AS last_touch_revenue FROM (
SELECT channel, revenue AS f, 0 AS l FROM firsts UNION ALL SELECT channel, 0, revenue FROM lasts
) x GROUP BY channel ORDER BY channel← Email Subscribers Who Open but Never Click · Streaks of Three or More Overspent Days →