Cohort analysis

Overview

Cohort analysis segments users into groups ("cohorts") based on when they shared a common event, most often the date they signed up, made their first purchase, or first used a product. Instead of looking at aggregate metrics across the whole user base on a given calendar date (which mixes long-time users with brand-new ones), cohort analysis aligns everyone to "days/weeks/months since [their] starting event," which makes it possible to compare how a cohort from January behaves in its first 30 days against how a cohort from March behaves in its first 30 days, on equal footing.

The classic retention cohort table

The most common output is a retention table: rows are signup cohorts (e.g., by signup month), columns are periods since signup (month 0, 1, 2, ...), and cells show the percentage of the original cohort still active in that period.

Building a cohort retention query in DuckDB

Copy code
WITH first_activity AS (
    SELECT user_id, date_trunc('month', min(event_date)) AS cohort_month
    FROM events
    GROUP BY ALL
),
activity AS (
    SELECT e.user_id,
           f.cohort_month,
           date_diff('month', f.cohort_month, date_trunc('month', e.event_date)) AS period
    FROM events e
    JOIN first_activity f USING (user_id)
)
SELECT cohort_month, period, count(DISTINCT user_id) AS active_users
FROM activity
GROUP BY ALL
ORDER BY ALL;

This can then be pivoted (DuckDB's PIVOT statement works well here) into the classic cohort-by-period grid, and divided by each cohort's initial size to get retention percentages.

Why it matters

Cohort analysis is one of the most common patterns in product and growth analytics because it separates two things that raw time-series metrics conflate: how well a product retains users over their lifecycle, and how the size of the overall user base is changing — a distinction that's essential for understanding whether growth is healthy or churn is being masked by new signups.