Unit 08.04: Cohorts: grouping by a fixed origin period

Unit ID: SQL-M08-U05 - Estimated active time: 14-17 minutes Objective: build a cohort by a fixed starting period and explain what makes cohorts comparable.

A cohort is defined by when you started, not what you did

Group customers by signup month, and each customer stays in that group forever:

SELECT DATE_TRUNC('month', signed_up) AS cohort_month,
       COUNT(*)                       AS customers
FROM customers
GROUP BY cohort_month
ORDER BY cohort_month
LIMIT 5;
-- 2025-01-01 | 309
-- 2025-02-01 | 280
-- 2025-03-01 | 310
-- 2025-04-01 | 300
-- 2025-05-01 | 310

The fixed origin is what makes the groups comparable. If customers could move between cohorts based on recent behaviour, you could not attribute a difference to when they joined.

Cohort plus outcome

The useful shape combines the cohort with something that happened later:

SELECT DATE_TRUNC('month', c.signed_up)             AS cohort_month,
       COUNT(*)                                     AS customers,
       COUNT(o.order_id)                            AS orders,
       ROUND(100.0 * COUNT(DISTINCT o.customer_id)
             / COUNT(DISTINCT c.customer_id), 1)    AS pct_ordered
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY cohort_month
ORDER BY cohort_month;

Two deliberate choices here, both from earlier modules:

• LEFT JOIN, because 3,812 customers never ordered (Module 5). An inner join would drop them and

every cohort would show 100% conversion.

• COUNT(DISTINCT …), because a customer with several orders must count once in the denominator

(Module 4).

The comparability trap

Later cohorts have had less time to act. A cohort that signed up last month cannot show twelve-month retention. Comparing "percentage who ordered" across cohorts of different ages measures elapsed time, not quality.

The fix is to compare at a fixed age - orders within 30 days of signup, for every cohort:

SELECT DATE_TRUNC('month', c.signed_up) AS cohort_month,
       COUNT(DISTINCT c.customer_id)    AS customers,
       COUNT(DISTINCT CASE
         WHEN o.placed_at < c.signed_up + INTERVAL 30 DAY
         THEN o.customer_id END)        AS ordered_within_30d
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY cohort_month
ORDER BY cohort_month;

Now every cohort is measured over the same window, and the comparison is about the cohorts rather than the calendar.

Practice

Why would an inner join make every cohort's conversion rate look identical, and what would that identical value be?

Takeaway

Fix the origin period and hold it constant. Compare cohorts at the same age, not on the same calendar date, or you are measuring how long ago they joined.

---

Mark complete