Last-Touch Channel for Each Conversion — SQL Problem
Under last-touch attribution, a conversion is credited to the most recent marketing touchpoint of the same user at or before the moment of conversion.
- Difficulty: Medium
- Topics: Subqueries, Joins
- Dialect: MySQL
- Problem: #285
Problem statement
Under last-touch attribution, a conversion is credited to the most recent marketing touchpoint of the same user at or before the moment of conversion. Touches after the conversion don't count.
Return conversion_id and last_touch_channel for every conversion. When two qualifying touches share the latest timestamp, the one with the higher touch_id wins. A conversion with no qualifying touch is credited to 'direct'. Return the rows in any order.
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, 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-04 10:00:00 |
| 4 | 8 | affiliate | 2024-11-03 12:00:00 |
| 5 | 8 | 2024-11-03 12:00:00 | |
| 6 | 9 | organic | 2024-11-05 08:15:00 |
Conversion
| conversion_id | user_id | converted_at | revenue |
|---|---|---|---|
| 101 | 7 | 2024-11-03 20:00:00 | 1499 |
| 102 | 8 | 2024-11-03 12:00:00 | 2599 |
| 103 | 9 | 2024-11-04 22:00:00 | 799 |
| 104 | 10 | 2024-11-05 11:00:00 | 349 |
Output
| conversion_id | last_touch_channel |
|---|---|
| 101 | |
| 102 | |
| 103 | direct |
| 104 | direct |
How to solve Last-Touch Channel for Each Conversion
Each conversion needs one value from a set of rows: among the user's touches at or before the conversion, the latest. A correlated scalar subquery expresses that directly — filter by user and time, sort by touched_at DESC, touch_id DESC so equal timestamps are decided by the higher id, and take one row. With no qualifying touch the subquery returns NULL, which COALESCE reports as 'direct'. A touch at exactly the conversion time qualifies, because the window is "at or before".
The window alternative LEFT JOINs every qualifying touch to the conversion and keeps row number 1 per conversion; a conversion without touches survives the LEFT JOIN as one row with a NULL channel. The third form keeps only the touch that no other qualifying touch beats (later, or same time and higher id) — a classic greatest-per-group anti join.
With an index on (user_id, touched_at) each subquery is one short index range scan.
Reference solution (MySQL)
SELECT v.conversion_id,
COALESCE((
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
), 'direct') AS last_touch_channel
FROM Conversion vAnother way
SELECT conversion_id, COALESCE(channel, 'direct') AS last_touch_channel FROM (
SELECT v.conversion_id, t.channel,
ROW_NUMBER() OVER (PARTITION BY v.conversion_id ORDER BY t.touched_at DESC, t.touch_id DESC) AS rn
FROM Conversion v
LEFT JOIN Touchpoint t ON t.user_id = v.user_id AND t.touched_at <= v.converted_at
) x WHERE rn = 1Another way
SELECT v.conversion_id, COALESCE(t.channel, 'direct') AS last_touch_channel
FROM Conversion v
LEFT JOIN Touchpoint t ON t.user_id = v.user_id AND t.touched_at <= v.converted_at
AND NOT EXISTS (SELECT 1 FROM Touchpoint u WHERE u.user_id = v.user_id AND u.touched_at <= v.converted_at
AND (u.touched_at > t.touched_at OR (u.touched_at = t.touched_at AND u.touch_id > t.touch_id)))← Campaign Months That Overspent the Budget · Email Open Rate by Hour of Send →