Cohorts and retention: how a month of new customers behaves afterwards

The question a total hides

Revenue went up. Was it because you are keeping customers, or because you are acquiring new ones fast enough to hide that the old ones leave? A total cannot say. A cohort analysis can: take everyone who started in the same month, and watch what share of them is still active one, two, three months later.

Step one: assign each customer to a cohort

A cohort is defined by a first event, and the first event is a min():

WITH first_order AS (
  SELECT customer_id, date_trunc('month', min(placed_at)) AS cohort_month
  FROM orders
  GROUP BY customer_id
)

One row per customer, with the month they first bought. This is a decision dressed as a query: “first order” could equally be “first sign-up” or “first paid order”, and a customer who bought once in 2023 and returned in 2026 belongs to the 2023 cohort forever. Say which event defines the cohort in the report title.

Step two: each customer’s active months, as an age

activity AS (
  SELECT o.customer_id,
         f.cohort_month,
         date_trunc('month', o.placed_at) AS active_month
  FROM orders o
  JOIN first_order f USING (customer_id)
  GROUP BY 1, 2, 3
),
aged AS (
  SELECT cohort_month,
         customer_id,
         (extract(year  FROM active_month) - extract(year  FROM cohort_month)) * 12
       + (extract(month FROM active_month) - extract(month FROM cohort_month)) AS months_since
  FROM activity
)

The GROUP BY in activity collapses several orders in one month to one row, because retention counts customers, not orders. months_since is the customer’s age in months at the time of that activity: 0 for the cohort month itself, 1 for the following month, and so on. The arithmetic with extract is the portable way; DuckDB has date_diff('month', cohort_month, active_month) and does it in one call.

Step three: the matrix

SELECT cohort_month,
       months_since,
       count(DISTINCT customer_id) AS active,
       count(DISTINCT customer_id)::numeric
         / first_value(count(DISTINCT customer_id))
             OVER (PARTITION BY cohort_month ORDER BY months_since) AS retention
FROM aged
GROUP BY cohort_month, months_since
ORDER BY cohort_month, months_since;

Two grains in one query, and worth reading slowly. The GROUP BY produces one row per cohort per age, with the number of customers active at that age. The window then runs over those grouped rows, and first_value ordered by age within each cohort picks the age-0 count, which is the cohort’s size. Divide by it and the retention column reads “of the customers who started in January, 41% bought something in month 3”.

Pivoting months_since into columns, with the earlier lesson’s technique, gives the triangle-shaped grid that cohort reports are known for.

The triangle, and the comparison it forbids

The grid is a triangle because a cohort that started three months ago has ages 0 to 3 and nothing further. That shape carries the most common misreading: comparing month-6 retention across cohorts when only the older cohorts have reached month 6. The newer cohorts are not worse; they are absent. Read the grid down a column, comparing the same age across cohorts, and only as far as every cohort in the comparison has reached. A “retention is falling” headline is often a triangle read along the wrong edge.

What “retained” means here

This query counts a customer as retained in a month if they ordered in that month. A customer who orders in months 0, 1 and 3 is retained at age 3 and not at age 2, and the curve can rise from month 2 to month 3. That is correct for purchase behaviour and different from subscription retention, where “retained” means “had not cancelled by then” and the curve can only fall. Both are called retention; a chart that does not say which will be compared with one of the other kind.

The habit

A cohort report should state four things in its caption: the event that defines the cohort, the event that counts as active, whether the measure can rise, and the age beyond which cohorts are not comparable. Every one of them is a decision in the query above, and every one changes the shape of the triangle.