First-Touch Attribution with a 14-Day Lookback
Credit each conversion to the campaign of the user's earliest ad click within the 14 days before the conversion. Ignore conversions with no click in that window. Report attributed conversions and revenue per campaign.
Expected output
name, conversions, revenue (rounded to 2 decimals). Sorted by revenue descending, then name.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: Digital ads performance
A retailer's paid campaigns across search, social, display and video in February 2026: daily delivery, individual ad clicks and purchases.
campaigns
One row per campaign.
| campaign_id | INTEGER | primary key |
| name | TEXT | |
| channel | TEXT | search, social, display or video |
| start_date | TEXT | YYYY-MM-DD |
| end_date | TEXT | YYYY-MM-DD inclusive, NULL if still running |
| daily_budget | REAL | USD |
ad_daily
Delivery per campaign per day it ran, as reported by the ad platform. clicks is the platform's count, which may differ from our own click log.
| campaign_id | INTEGER | |
| day | TEXT | YYYY-MM-DD |
| impressions | INTEGER | |
| clicks | INTEGER | |
| spend | REAL | USD |
ad_clicks
Our own click log: one row per ad click that reached the site and was tracked.
| click_id | INTEGER | primary key, increases with clicked_at |
| campaign_id | INTEGER | |
| user_id | INTEGER | |
| clicked_at | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
conversions
One row per purchase. Some purchases come from users who never clicked an ad.
| conversion_id | INTEGER | primary key |
| user_id | INTEGER | |
| converted_at | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
| revenue | REAL | USD |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Monthly Revenue Trend (easy)
- Quarterly Cancellation Rate (medium)
- Top 2 Products in Each Category (medium)
- Days from Signup to First Order (medium)
- Running Revenue Total for 2025 (medium)