Advanced
Case 36 of 50
Case 036: Revenue by Channel
customersmarketing_touchesorderspayments
Marketing tracks every touchpoint a customer has before they buy — social, email, search, referral — and the same customer can show up in several of them.
The manager wants to know which one deserves the credit: "When a customer buys something, credit that revenue to whichever channel reached them FIRST, not whatever they happened to click last. Which channel is actually driving revenue?"
The data Alex is looking at
customers
| id | name | country | signup_date |
|---|---|---|---|
| 1 | Sam | Germany | 2026-01-02 |
| 2 | Nina | Canada | 2026-01-05 |
| 3 | David | Germany | 2026-01-10 |
| 4 | Maya | Japan | 2026-01-15 |
| 5 | Daniel | Canada | 2026-01-20 |
| … 5 more rows | |||
marketing_touches
| id | customer_id | channel | touch_date |
|---|---|---|---|
| 1 | 1 | Social | 2026-01-01 |
| 2 | 1 | 2026-01-03 | |
| 3 | 2 | Search | 2026-01-04 |
| 4 | 2 | 2026-01-06 | |
| 5 | 3 | Referral | 2026-01-09 |
| … 8 more rows | |||
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
payments
| id | order_id | amount |
|---|---|---|
| 601 | 101 | 6200 |
| 602 | 102 | 3500 |
| 603 | 103 | 3600 |
| 604 | 104 | 60000 |
| 605 | 105 | 12000 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“Attribute each customer's total revenue to whichever marketing channel touched them first, then show the total revenue per channel.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
WITH first_touch AS (
SELECT
customer_id,
channel,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touch_date) AS rn
FROM marketing_touches
),
customer_revenue AS (
SELECT orders.customer_id, SUM(payments.amount) AS revenue
FROM orders
JOIN payments ON payments.order_id = orders.id
GROUP BY orders.customer_id
)
SELECT
first_touch.channel,
SUM(customer_revenue.revenue) AS attributed_revenue
FROM first_touch
JOIN customer_revenue ON customer_revenue.customer_id = first_touch.customer_id
WHERE first_touch.rn = 1
GROUP BY first_touch.channel
ORDER BY attributed_revenue DESC;
What that query returns
Case Debrief
First-touch attribution
- "Attribution" just means deciding which signal gets credit for an outcome — here, whichever channel touched a customer FIRST gets credit for all of their later revenue, even if other channels reached them afterward.
- The technique itself is nothing new — ROW_NUMBER() finds each customer's first touch, SUM() totals their revenue. Attribution is just combining tools you already know to answer a new kind of business question.