Skip to content

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_order drops 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.