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.