Meta: Last-Click Attributed Revenue by Campaign
Meta's ads measurement team credits each purchase on an advertiser's site to one ad campaign with a last-click, 7-day rule: the purchase goes to the…
- Difficulty: Medium
- Topics: Window Functions, Joins, Dates
- Dialect: MySQL
- Problem: #757
Problem statement
Meta's ads measurement team credits each purchase on an advertiser's site to one ad campaign with a last-click, 7-day rule: the purchase goes to the campaign of the same user's latest ad click at or before the purchase time, provided that click happened no more than 7 days (exactly 7 days still counts) before the purchase. If two of the user's clicks share the latest time, the one with the larger click_id wins. A purchase with no qualifying click is credited to nobody.
Return every campaign credited with at least one purchase, with the columns campaign_id, purchases (purchases credited to it) and revenue (their summed amount), in any order.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: AdClick
| Column | Type |
|---|---|
| click_id | int |
| user_id | int |
| campaign_id | int |
| clicked_at | datetime |
Primary key: click_id.
One row per click on an ad.
Table: Purchase
| Column | Type |
|---|---|
| purchase_id | int |
| user_id | int |
| purchased_at | datetime |
| amount | int |
Primary key: purchase_id.
One row per purchase reported by the advertiser's conversion tracking, in rupees.
Examples
Example 1
AdClick
| click_id | user_id | campaign_id | clicked_at |
|---|---|---|---|
| 1 | 1 | 701 | 2025-09-01 10:00:00 |
| 2 | 1 | 702 | 2025-09-03 12:00:00 |
| 3 | 2 | 701 | 2025-09-02 09:00:00 |
| 4 | 3 | 703 | 2025-08-20 08:00:00 |
| 5 | 4 | 702 | 2025-09-05 18:00:00 |
| 6 | 4 | 703 | 2025-09-05 18:00:00 |
| 7 | 2 | 702 | 2025-09-06 20:00:00 |
Purchase
| purchase_id | user_id | purchased_at | amount |
|---|---|---|---|
| 1 | 1 | 2025-09-04 09:00:00 | 1200 |
| 2 | 2 | 2025-09-05 10:00:00 | 800 |
| 3 | 3 | 2025-09-01 09:00:00 | 500 |
| 4 | 4 | 2025-09-12 18:00:00 | 2000 |
| 5 | 1 | 2025-09-09 11:00:00 | 300 |
| 6 | 5 | 2025-09-10 10:00:00 | 450 |
Output
| campaign_id | purchases | revenue |
|---|---|---|
| 701 | 1 | 800 |
| 702 | 2 | 1500 |
| 703 | 1 | 2000 |
How to solve Meta: Last-Click Attributed Revenue by Campaign
Attribution is a two-step problem: pick one click per purchase, then aggregate per campaign. Doing it in that order matters — summing over every candidate click would credit one purchase to several campaigns.
The join builds the candidates: clicks by the same user within the window [purchased_at − 7 days, purchased_at]. Both ends are inclusive, so a click exactly seven days earlier qualifies, and a click after the purchase never does. ROW_NUMBER() OVER (PARTITION BY purchase_id ORDER BY clicked_at DESC, click_id DESC) numbers each purchase's candidates from the latest, the click_id tie-break making the pick unique when two clicks share a timestamp; keeping rn = 1 leaves exactly one row per credited purchase, and the inner join has already dropped purchases with no candidate.
The alternative picks the click with a correlated ORDER BY … LIMIT 1 subquery and joins the campaign through its click_id. With an index on AdClick(user_id, clicked_at) both look only at each user's recent clicks.
Reference solution (MySQL)
WITH candidates AS (
SELECT p.purchase_id, p.amount, c.campaign_id,
ROW_NUMBER() OVER (PARTITION BY p.purchase_id ORDER BY c.clicked_at DESC, c.click_id DESC) AS rn
FROM Purchase p
JOIN AdClick c ON c.user_id = p.user_id
AND c.clicked_at <= p.purchased_at
AND c.clicked_at >= DATE_SUB(p.purchased_at, INTERVAL 7 DAY)
)
SELECT campaign_id, COUNT(*) AS purchases, SUM(amount) AS revenue
FROM candidates
WHERE rn = 1
GROUP BY campaign_idAnother way
SELECT c.campaign_id, COUNT(*) AS purchases, SUM(p.amount) AS revenue
FROM Purchase p
JOIN AdClick c ON c.click_id = (
SELECT c2.click_id FROM AdClick c2
WHERE c2.user_id = p.user_id AND c2.clicked_at <= p.purchased_at
AND TIMESTAMPDIFF(SECOND, c2.clicked_at, p.purchased_at) <= 7 * 86400
ORDER BY c2.clicked_at DESC, c2.click_id DESC LIMIT 1)
GROUP BY c.campaign_id← Meta: Ad Set Metrics Unpivoted Into Long Format · Meta: Facebook Groups Where Every Admin Approved a Post →