Business case queries · Hard
How would you measure customer retention by acquisition cohort?
Short answer
Assign each customer to a cohort by the month or quarter of their first order, then check whether they ordered again within a fixed window, such as 90 days. Retention is returning customers divided by cohort size. Use a fixed window so older cohorts are not favoured simply because they have had more time, and exclude cohorts too recent to have completed the window.
Detailed answer
With a small dataset, quarterly cohorts and a 90-day window keep the numbers meaningful.
WITH valid_orders AS (
SELECT customer_id, order_date
FROM orders
WHERE status <> 'cancelled'
),
firsts AS (
SELECT customer_id, MIN(order_date) AS first_order
FROM valid_orders
GROUP BY customer_id
),
flags AS (
SELECT f.customer_id,
strftime('%Y', f.first_order) || '-Q' ||
((CAST(strftime('%m', f.first_order) AS INTEGER) + 2) / 3) AS cohort,
MAX(CASE WHEN v.order_date > f.first_order
AND v.order_date <= date(f.first_order, '+90 days')
THEN 1 ELSE 0 END) AS returned_90d
FROM firsts f
JOIN valid_orders v ON v.customer_id = f.customer_id
WHERE f.first_order <= date((SELECT MAX(order_date) FROM orders), '-90 days')
GROUP BY f.customer_id, cohort
)
SELECT cohort,
COUNT(*) AS cohort_customers,
SUM(returned_90d) AS repeat_within_90d,
ROUND(100.0 * SUM(returned_90d) / COUNT(*), 1) AS retention_90d_pct
FROM flags
GROUP BY cohort
ORDER BY cohort;
Walk the interviewer through the decisions:
- Definition of "active" – any non-cancelled order. Returns could arguably be excluded too; state the choice.
- Fixed window – "ordered again within 90 days" is comparable across cohorts; "ever ordered again" is not.
- Censoring – the WHERE on
first_orderdrops customers whose 90-day window has not finished yet. - Small numbers – a cohort of four customers gives noisy percentages; say you would report the counts alongside the rate.
For a classic cohort grid, compute month_number (months since first order) per order and pivot with conditional aggregation.