Meta: Last-Click Attributed Revenue by Campaign

Medium
757.

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 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

ColumnType
click_idint
user_idint
campaign_idint
clicked_atdatetime

Primary key: click_id.

One row per click on an ad.

Table: Purchase

ColumnType
purchase_idint
user_idint
purchased_atdatetime
amountint

Primary key: purchase_id.

One row per purchase reported by the advertiser's conversion tracking, in rupees.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

AdClick

click_iduser_idcampaign_idclicked_at
117012025-09-01 10:00:00
217022025-09-03 12:00:00
327012025-09-02 09:00:00
437032025-08-20 08:00:00
547022025-09-05 18:00:00
647032025-09-05 18:00:00
727022025-09-06 20:00:00

Purchase

purchase_iduser_idpurchased_atamount
112025-09-04 09:00:001200
222025-09-05 10:00:00800
332025-09-01 09:00:00500
442025-09-12 18:00:002000
512025-09-09 11:00:00300
652025-09-10 10:00:00450

Output

campaign_idpurchasesrevenue
7011800
70221500
70312000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
click_idint
user_idint
campaign_idint
clicked_atdatetime

Primary key: click_id.

One row per click on an ad.

Table: Purchase

ColumnType
purchase_idint
user_idint
purchased_atdatetime
amountint

Primary key: purchase_id.

One row per purchase reported by the advertiser's conversion tracking, in rupees.

Examples

Example 1

AdClick

click_iduser_idcampaign_idclicked_at
117012025-09-01 10:00:00
217022025-09-03 12:00:00
327012025-09-02 09:00:00
437032025-08-20 08:00:00
547022025-09-05 18:00:00
647032025-09-05 18:00:00
727022025-09-06 20:00:00

Purchase

purchase_iduser_idpurchased_atamount
112025-09-04 09:00:001200
222025-09-05 10:00:00800
332025-09-01 09:00:00500
442025-09-12 18:00:002000
512025-09-09 11:00:00300
652025-09-10 10:00:00450

Output

campaign_idpurchasesrevenue
7011800
70221500
70312000

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_id

Another 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 →