"Are we keeping customers better than before?" Overall repeat rates cannot answer this, because they mix old and new customers. Cohort analysis can. It groups customers by when they started and tracks each group over time.
The example uses an orders table from a fictional meal-kit company, CookBox: order_id, customer_id, order_date, amount.
What we want
| cohort_month | month 0 | month 1 | month 2 | month 3 |
|---|---|---|---|---|
| 2026-06 | 100% | share active | share active | share active |
| 2026-07 | 100% | share active | share active | |
| 2026-08 | 100% | share active |
Each row is a cohort (customers whose first order was in that month). Each column is the number of months since their first order.
Step 1: each customer's first month
WITH first_order AS (
SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY customer_id
),Step 2: each customer's active months
activity AS (
SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS active_month
FROM orders
),DISTINCT makes several orders in one month count once.
Step 3: months since the first order
joined AS (
SELECT f.cohort_month,
a.customer_id,
(EXTRACT(YEAR FROM a.active_month) - EXTRACT(YEAR FROM f.cohort_month)) * 12
+ (EXTRACT(MONTH FROM a.active_month) - EXTRACT(MONTH FROM f.cohort_month)) AS month_number
FROM activity a
JOIN first_order f ON f.customer_id = a.customer_id
),Step 4: count and divide by cohort size
counts AS (
SELECT cohort_month, month_number, COUNT(DISTINCT customer_id) AS active_customers
FROM joined
GROUP BY cohort_month, month_number
)
SELECT c.cohort_month,
c.month_number,
c.active_customers,
ROUND(100.0 * c.active_customers
/ FIRST_VALUE(c.active_customers) OVER (PARTITION BY c.cohort_month ORDER BY c.month_number), 1)
AS retention_pct
FROM counts c
ORDER BY c.cohort_month, c.month_number;Month 0 always includes everyone in the cohort, so it is the denominator.
Step 5: pivot for reading
Either pivot with SUM(CASE WHEN month_number = 1 THEN retention_pct END) per column, or export the long result and pivot in Excel, pandas or Power BI.
How to read the triangle
- Read across a row: how one cohort decays over time. Steep early drops point to onboarding or first-experience problems.
- Read down a column: whether newer cohorts retain better or worse at the same age. This is the key question for "are we improving?".
- Look for a diagonal pattern: if every cohort dips in the same calendar month, something happened that month (an outage, a price change, a competitor's sale).
Variations
- Weekly cohorts for fast-moving apps.
- Revenue retention instead of customer counts.
- Cohorts by acquisition channel or first-order discount, to see which sources bring loyal customers.
Practise
This query combines CTEs, date functions and window functions. Then apply it in the quick commerce retention project, where finding why repeat customers drop is the whole brief.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: