Cohort analysis

Cohort analysis groups customers by when they first purchased, then tracks how many return in subsequent months. It is the gold standard for measuring retention and is one of the most valuable analytics you can build in SQL.

Cohort retention table

Rows = first purchase month, Columns = months since first purchase, Cells = % still active

Monthly active users mixes all customers together, hiding important patterns. Cohort analysis separates customers by when they joined, so you can see if January signups retain better than March signups. This reveals whether your product or acquisition strategy is improving over time.

cohort_step1.sql
sql
-- CTE 1: First purchase month per customer (= their cohort)
WITH customer_cohort AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(order_purchase_timestamp)) AS cohort_month
  FROM orders
  GROUP BY customer_id
)

MIN(order_purchase_timestamp) finds when each customer first ordered. DATE_TRUNC rounds to the month. This defines their cohort.

With cohorts assigned, the next step joins every order back to its customer's cohort and calculates how many months passed since their first purchase.

cohort_step2.sql
sql
-- CTE 2: Join back to all orders, compute months since first purchase
order_months AS (
  SELECT
    cc.customer_id,
    cc.cohort_month,
    DATE_TRUNC('month', o.order_purchase_timestamp) AS order_month,
    EXTRACT(YEAR FROM AGE(
      DATE_TRUNC('month', o.order_purchase_timestamp),
      cc.cohort_month
    )) * 12 + EXTRACT(MONTH FROM AGE(
      DATE_TRUNC('month', o.order_purchase_timestamp),
      cc.cohort_month
    )) AS months_since
  FROM orders o
  JOIN customer_cohort cc ON o.customer_id = cc.customer_id
)

Join every order back to its customer's cohort month. Calculate months_since as the difference in months.

Finally, we aggregate by cohort and months_since to count how many distinct customers were still active at each interval.

cohort_step3.sql
sql
-- CTE 3: Count distinct customers per cohort + months_since
SELECT
  cohort_month,
  months_since,
  COUNT(DISTINCT customer_id) AS active_customers
FROM order_months
GROUP BY cohort_month, months_since
ORDER BY cohort_month, months_since;

The final aggregation: how many unique customers from each cohort were active N months later.

MAU can mask retention problems. If you acquire 1000 new users per month but lose 800 existing ones, MAU might look flat or growing. Cohort analysis reveals the truth: each group's retention curve shows exactly how many users stick around over time.

Code playground: Build a cohort table

Loading practice…

Quiz: Quiz

Loading practice…

Fill in the blanks: Cohort assignment

Loading practice…