Practice · hard · dataset: ecom
First-touch channel attribution
Marketing wants to know which acquisition channel brought each customer in. Attribute every customer to the channel of their first order, then report the attributed revenue from that first order only.
Return channel, customers (the number of customers whose first order used that channel), and
revenue (the sum of quantity * unit_price over the line items of those first orders), rounded to
two decimals. Sort by channel ascending.
Tables available
| Table | Columns |
|---|---|
orders |
order_id, customer_id, order_date, status, channel |
order_items |
order_id, product_id, quantity, unit_price |
“First” means earliest order_date, with order_id ascending as the tiebreaker. Revenue from
later orders is not part of first-touch attribution.
⌘/Ctrl + Enter to grade
Write a query, then grade it.
Hints
Try it first. Reveal a hint only when you are stuck.