Cohorts
Harry’s manager has a hunch: “Do customers from March behave differently than customers from January?”
How would January and March customers even be different? They're all just... customers.
That's the question a cohort analysis answers. Group customers by when they showed up, then compare what each group did afterward. Sometimes a signup month really is just a label. Sometimes it means something happened that month — a promotion, a bad week, a slow one.
A cohort is just a group of customers who share a starting point in time — here, the month they signed up. Once you have cohorts, you can compare them on anything: did they ever order, how much did they spend, did they come back. It’s the same GROUP BY you already know — the group is just defined by a date instead of a country or a category.
A cohort analysis is GROUP BY signup period, then compare what each group did next.
Cohort the customers first — same date-truncation trick, this time on customers.signup_date:
SELECT SUBSTR(CAST(signup_date AS TEXT), 1, 7) AS cohort_month, COUNT(*) AS cohort_size
FROM customers
GROUP BY cohort_month
ORDER BY cohort_month;Now bring in orders with a LEFT JOIN — some cohorts, like March, have a customer who never ordered at all, and a LEFT JOIN is what keeps that cohort in the results instead of dropping it. Conditional CASE WHEN inside SUM/COUNT keeps only the completed orders in the money and conversion numbers, same guardrail as the revenue lesson:
SELECT
SUBSTR(CAST(c.signup_date AS TEXT), 1, 7) AS cohort_month,
COUNT(DISTINCT c.id) AS cohort_size,
COUNT(DISTINCT CASE WHEN o.status = 'completed' THEN o.customer_id END) AS customers_who_bought,
ROUND(
COUNT(DISTINCT CASE WHEN o.status = 'completed' THEN o.customer_id END) * 100.0
/ COUNT(DISTINCT c.id),
1
) AS conversion_rate_pct,
ROUND(COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.amount END), 0), 2) AS cohort_revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY cohort_month
ORDER BY cohort_month;| cohort_month | cohort_size | customers_who_bought | conversion_rate_pct | cohort_revenue |
|---|---|---|---|---|
| 2022-11 | 1 | 1 | 100 | 199.99 |
| 2022-12 | 1 | 1 | 100 | 34.5 |
| 2023-01 | 2 | 2 | 100 | 268.96 |
| 2023-02 | 2 | 2 | 100 | 254.97 |
| 2023-03 | 1 | 0 | 0 | 0 |
| 2023-04 | 1 | 1 | 100 | 223.46 |
| 2023-05 | 1 | 1 | 100 | 59.99 |
| 2023-06 | 1 | 0 | 0 | 0 |
| 2023-07 | 1 | 1 | 100 | 177.99 |
| 2023-08 | 1 | 1 | 100 | 79.99 |
There's your answer. The March 2023 cohort converted at 0% — every other cohort but June is at 100%.
So March customers really are different. Or... is that just one person?
Exactly the right question. Check the cohort_size column before you get excited about a percentage.
Running…
Every cohort here has just one or two customers — NOVA isn’t big enough yet for a single month’s cohort to be a reliable sample. March converting at 0% is one real customer (Chloe) who hasn’t ordered, not a March-specific problem. The technique is exactly right; it just needs more customers per cohort before the percentages mean much on their own.
Try it: What if you used every order instead of only completed ones for cohort_revenue?
Run your query to see results here.
A couple of cohorts get a bump — the 2023-01 cohort, for instance, includes George’s pending order from that stretch. It’s the exact same trap from the revenue lesson: dropping the status = 'completed' filter makes every cohort look a little richer than it really is.
The VP wants to know which single signup cohort has generated the most revenue for NOVA so far. Show cohort_month and total completed revenue for each cohort, highest revenue first.
Hint 1
You don't need the conversion or cohort_size columns for this one — just the month and the revenue.
Hint 2
Same LEFT JOIN + SUBSTR(CAST(...)) cohorting as above, but sort by the revenue column instead of the month.
Hint 3
GROUP BY cohort_month, then ORDER BY cohort_revenue DESC.
Solution
Running…
January 2023’s two customers — Alice and Diego — have brought in more revenue than any other single cohort, ahead even of cohorts that have had more time to accumulate orders. That’s a genuinely useful, specific finding a single overall revenue number could never surface: not just how much NOVA made, but which group of customers is responsible for it.
Cohort analysis is GROUP BY signup period plus a LEFT JOIN to whatever behavior you want to compare — and just like conversion rates, always check cohort size before trusting a cohort’s percentage.