Cohort and Retention Queries

A cohort groups users by when they started. Retention tracks what fraction of each cohort is still active some period later. This is the single most requested, and most commonly botched, product analytics query there is.

The conceptual shape: bucket users by signup month, then for each subsequent month, count what fraction of that original cohort did something (logged in, made a purchase, whatever "active" means for your product).

SELECT

DATE_TRUNC('month', u.signup_date) AS cohort_month,

DATE_TRUNC('month', e.event_date) AS activity_month,

COUNT(DISTINCT u.id) AS active_users

FROM users u

JOIN events e ON u.id = e.user_id

GROUP BY 1, 2

ORDER BY 1, 2;

This gives you a table of cohort month, activity month, and how many of that cohort's users were active that month. From there, dividing each activity month's count by the cohort's original size gives you a retention percentage, typically visualized as the classic triangular retention grid.

THE DEFINITION THAT DECIDES EVERYTHING

"Active" is a product decision, not a technical one, and it changes the entire shape of your retention curve. Logging in is a much weaker bar than completing a core action. Before writing a single line of cohort SQL, write down, as a PM, exactly what "active" means for this specific analysis, the same way you'd define "correct" in a data requirements doc. Two retention analyses using different definitions of active are not comparable, even if every line of SQL between them is otherwise identical, and presenting them side by side without flagging that difference is a common way well-meaning people mislead a room.

YOUR MOVE THIS WEEK

Find an existing retention chart your company already has. Ask, specifically, what event defines "active" in it. If nobody can answer immediately, that chart has been trusted without being understood, and it's worth five minutes to pin the definition down.