Funnels
Harry’s getting more comfortable with “one number” questions. Today’s is bigger: “Where in the funnel are we losing people?”
A funnel is usually like: visited the site, added to cart, checked out, right? We don't have carts or site visits anywhere in here.
Right, no cart events. But you already found a real funnel last lesson without realizing it — customers who exist, customers who've ever ordered, and customers who've ordered more than once. That's three honest stages, straight from data we actually have.
A funnel is just a sequence of stages where each one is a subset of the one before it — everyone at stage 3 was also at stage 2, and everyone at stage 2 was also at stage 1. NOVA’s honest version:
- Signed up (a row in
customers) - Placed a first order
- Placed a second, repeat order
Each funnel stage answers the same question: how many of the people from the stage before made it this far?
Each stage is its own count. Stack them with UNION ALL so they land as rows in one result instead of three separate queries:
SELECT 'Signed up' AS stage, 1 AS stage_order, COUNT(*) AS customers
FROM customers
UNION ALL
SELECT 'Placed 1st order', 2, COUNT(DISTINCT customer_id)
FROM orders
UNION ALL
SELECT 'Placed 2nd order (repeat)', 3, COUNT(*)
FROM (
SELECT customer_id FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
) repeat_customers
ORDER BY stage_order;| stage | stage_order | customers |
|---|---|---|
| Signed up | 1 | 12 |
| Placed 1st order | 2 | 10 |
| Placed 2nd order (repeat) | 3 | 6 |
The third stage is the interesting bit: a subquery groups orders by customer and keeps only the ones with HAVING COUNT(*) >= 2 — customers who ordered more than once. That’s the same GROUP BY / HAVING combo from filtering groups, just repurposed to define a funnel stage instead of a report row.
Raw counts tell you the shape, but percentages tell you where the damage is. LAG pulls the previous stage’s count into the same row, so you can compute the drop-off right there:
WITH funnel AS (
SELECT 'Signed up' AS stage, 1 AS stage_order, COUNT(*) AS customers
FROM customers
UNION ALL
SELECT 'Placed 1st order', 2, COUNT(DISTINCT customer_id)
FROM orders
UNION ALL
SELECT 'Placed 2nd order (repeat)', 3, COUNT(*)
FROM (
SELECT customer_id FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
) repeat_customers
)
SELECT
stage,
customers,
ROUND(customers * 100.0 / (SELECT COUNT(*) FROM customers), 1) AS pct_of_signups,
ROUND(customers * 100.0 / LAG(customers) OVER (ORDER BY stage_order), 1) AS pct_of_previous_stage
FROM funnel
ORDER BY stage_order;Running…
83% of signups place a first order, but only 60% of those come back for a second. The bigger drop, by far, is stage 2 to stage 3 — getting someone to buy once isn’t NOVA’s hardest problem. Getting them to buy again is.
Try it: What if you added a 4th stage — 'placed a third order'?
Run your query to see results here.
Just three customers have placed three or more orders. A funnel can have as many stages as the data supports — the pattern is always the same HAVING COUNT(*) >= n subquery, just with a bigger n. At some point a stage gets so small (one or two people) that a percentage off it stops meaning anything, same as the country conversion numbers from last lesson.
Rebuild the same 3-stage funnel — signed up, placed a first order, placed a repeat order — but restricted to USA customers only. Just the stage name and customer count for each stage is fine.
Hint 1
Each of the three stages needs its own country filter — for the order-based stages, that means joining to customers to find country.
Hint 2
Stage 1 filters customers directly. Stages 2 and 3 need JOIN customers ON customers.id = orders.customer_id, then WHERE country = 'USA'.
Hint 3
Same UNION ALL / GROUP BY / HAVING shape as the main funnel, with WHERE c.country = 'USA' added to each of the three branches.
Solution
Running…
USA’s funnel doesn’t leak at all — all three of NOVA’s USA customers signed up, ordered once, and ordered again. The overall “60% of first-time buyers come back” number was hiding a market that’s actually performing perfectly. Same lesson as conversion: a funnel computed across everyone can hide a segment that’s either much better, or much worse, than the average suggests.
A funnel is a sequence of nested subsets, each one a HAVING-filtered count of the stage before it. LAG lets you compute stage-to-stage drop-off in the same query — and just like a conversion rate, it’s worth checking whether a segment is behaving differently than the overall funnel suggests.