Cohort Analysis Guide

Cohort analysis groups users by a shared starting point, usually the month they signed up, and tracks each group's behavior (such as retention or revenue) over the same periods since that start. Comparing cohorts at the same age shows whether newer users behave better or worse than older ones, a trend that overall averages hide.

What is cohort analysis used for?

Cohort analysis tells you whether a product, onboarding flow, or acquisition channel is getting better or worse over time. Overall metrics mix old and new users together, so a flat active-user count can hide newer cohorts retaining much better (or worse) than older ones; splitting by cohort separates the effect of recent changes from the mix of who happens to be active.

What are the main types of cohort analysis?

The three common types are time-based cohorts (grouped by when users started, such as sign-up month), behavior-based cohorts (grouped by the first action they took), and segment-based cohorts (grouped by an attribute such as acquisition channel or pricing tier). Start with time-based cohorts; the other two help explain why one month's cohort retains better than another.

1. Time-Based Cohorts

Group by: Sign-up month (Jan 2025, Feb 2025...)

Track: Retention, engagement, revenue per cohort

Use case: "Are newer users more engaged than older users?"

2. Behavior-Based Cohorts

Group by: First action taken (watched video, read article, purchased)

Track: Conversion paths, feature adoption

Use case: "Which onboarding action leads to best retention?"

3. Segment-Based Cohorts

Group by: Acquisition channel, pricing tier, geography

Track: Channel effectiveness, LTV by segment

Use case: "Do paid ads bring better users than organic?"

How do you read a cohort retention table?

Each row is a cohort, each column is the number of periods since that cohort started, and each cell is the percentage of the original cohort still active in that period. Read across a row to see how one cohort decays as it ages, and read down a column to compare cohorts at the same age; Month 0 is always 100%.

Cohort	Users	Month 0	Month 1	Month 2	Month 3
Jan 2025	1,200	100%	60%	45%	40%
Feb 2025	1,000	100%	70%	55%	50%
Mar 2025	900	100%	72%	58%	—

Reading down the Month 1 column, retention rises from 60% to 70% to 72%, so each newer cohort keeps more of its users. March has no Month 3 value because that month has not finished; a blank means "not measured yet", not 0%. Shade the cells by value and you have a cohort retention heat map.

How do you calculate cohort retention rate?

Divide the number of cohort members active in a period by the cohort's starting size and multiply by 100. In the January cohort above, 540 of the 1,200 users were active in Month 2, so Month 2 retention is 540 ÷ 1,200 = 45%.

Always divide by the starting size, not by the previous period: 540 ÷ 720 (January's Month 1 active users) = 75% is a month-over-month ratio, and calling it retention overstates how many users you keep. Define "active" (logged in, made a purchase) once and use it for every cohort.

How do you build a cohort table in SQL?

Assign each user to the month of their first activity, count the distinct users active in each month since then, and divide by the cohort's Month 0 count. This SQLite query expects an activity table with one row per user action and activity_date stored as YYYY-MM-DD text.

WITH firsts AS (            -- cohort = month of each user's first activity
    SELECT user_id, MIN(activity_date) AS first_date
    FROM activity
    GROUP BY user_id
),
active_months AS (          -- one row per user per month they were active
    SELECT DISTINCT
        a.user_id,
        strftime('%Y-%m', f.first_date) AS cohort,
        (CAST(strftime('%Y', a.activity_date) AS INTEGER)
           - CAST(strftime('%Y', f.first_date) AS INTEGER)) * 12
        + CAST(strftime('%m', a.activity_date) AS INTEGER)
        - CAST(strftime('%m', f.first_date) AS INTEGER) AS month_number
    FROM activity a
    JOIN firsts f ON f.user_id = a.user_id
)
SELECT
    cohort,
    month_number,
    COUNT(*) AS active_users,
    ROUND(100.0 * COUNT(*) / FIRST_VALUE(COUNT(*)) OVER (
        PARTITION BY cohort ORDER BY month_number), 1) AS retention_pct
FROM active_months
GROUP BY cohort, month_number
ORDER BY cohort, month_number;

Each result row is one cell of the table. On the data behind the January cohort above, the first four rows are:

cohort   month_number  active_users  retention_pct
2025-01  0             1200          100.0
2025-01  1             720           60.0
2025-01  2             540           45.0
2025-01  3             480           40.0

Pivot month_number into columns to get the grid; the window function FIRST_VALUE supplies each cohort's Month 0 denominator. In PostgreSQL, replace strftime with DATE_TRUNC('month', d) for the cohort and EXTRACT(YEAR FROM d) and EXTRACT(MONTH FROM d) for the date parts.

Key Metrics to Track

Retention rate is the core cohort metric; the others show why it moves and what each cohort is worth.

• Retention rate: % of the original cohort active N days, weeks, or months after starting
• Churn rate: % who leave in each period
• Cohort LTV: Cumulative revenue from the cohort divided by its starting size
• Time to value: How long until first key action

Common Mistakes

Most misleading cohort reports come from comparing raw counts, judging periods that have not finished, or reading seasonal swings as trends.

• Comparing raw counts across cohorts of different sizes: January had more Month 1 users than February (720 vs 700) but lower retention (60% vs 70%). Compare percentages.
• Not waiting for cohorts to mature: a period still in progress shows artificially low retention.
• Ignoring seasonal effects: December sign-ups may behave differently from June ones (see seasonality in metrics).
• Using only current users as the base: it hides everyone who left, a form of survivorship bias.