Cohort and Retention Analytics

Cohort & Retention Analytics

Overview

Retention answers the single most important growth question: do users who join keep coming back? Acquisition without retention is a leaky bucket — you pour users in the top and they fall out the bottom, so growth stalls no matter how much you spend. This skill covers the math and methods for measuring retention across cohorts (groups of users grouped by a shared start or behavior), reading retention curves, doing growth accounting (decomposing user and revenue change into its parts), and the SaaS revenue-retention metrics (NRR/GRR). It is the methods layer beneath product-led and SaaS growth.

Scope boundary: this skill is the measurement of who stays and by how much. For funnels/activation/North-Star use da-21-product-analytics; for probabilistic CLV (BG/NBD, gamma-gamma) use da-23-customer-lifetime-value; for hazard-rate/survival modeling use da-24-survival-analysis; for experiment-driven lift use da-12-ab-testing-causal-inference.

Core Concepts

1. Acquisition vs behavioral cohorts

2. Retention-curve shapes

A retention curve plots % of a cohort still active against periods-since-start. Canonical shapes (Amplitude, Retention Curve; Churnkey, Retention Curves, 2024; Product Growth, Retention Curves Guide):

The retention floor (where the curve flattens) is your real long-term retention. Improving the floor (curve flattens higher) compounds far more than improving early-period retention that still decays to the same floor.

3. Retention definitions: N-day, unbounded, bracket, rolling

Choosing the definition changes the numbers dramatically — always state which you use (Amplitude, 3 Ways to Measure Retention, 2024; Mixpanel Docs, Retention; Amplitude, N-Day Retention for Mobile Games):

4. Retention ↔ engagement

Retention is binary (active or not in a period) and is the output; engagement depth (frequency × breadth of actions) is the leading indicator. Deeper engagement → habit → retention → sustainable growth — Reforge frames retention/engagement as “the power plant of the growth model” (Reforge, Retention is the Silent Killer; Reforge, Growth Loops are the New Funnels; Conor Dewey, Reforge Recap: Engagement + Retention). Practical move: don’t just track the retained/churned flag — track how deeply retained users engage, because depth predicts long-term value and is the lever you pull to raise the retention floor.

5. Growth accounting (users)

Decompose period-over-period active users into additive components. The fundamental identity (Social Capital / Jonathan Hsu, Diligence Part 1, 2017; Amplitude, Growth Accounting, 2024):

MAU(t) = MAU(t-1) + new(t) + resurrected(t) - churned(t)

User Quick Ratio (QR) = (new + resurrected) / churned. Users gained per user lost. QR > 1 means growing; rule of thumb QR ≥ ~1.5 is healthy (Hsu, Diligence Part 1, 2017; The SaaS CFO, SaaS Quick Ratio, 2024).

6. Growth accounting (revenue / MRR)

Same identity applied to dollars (Social Capital / Hsu, Diligence Part 2, 2017; Lenny Rachitsky, Bottom-Up SaaS Metrics):

MRR(t)   = new(t) + retained(t) + resurrected(t) + expansion(t)
MRR(t-1) = retained(t) + churned(t) + contraction(t)

SaaS Quick Ratio = (new MRR + expansion MRR) / (churned MRR + contraction MRR). Mamoon Hamid (Social Capital) popularized a target of QR ≥ 4 for early-stage SaaS — $4 of growth for every $1 lost (The SaaS CFO, 2024; Cobloom, SaaS Quick Ratio).

7. DAU/WAU/MAU and stickiness

8. Churn rate vs retention rate (and the asymmetry)

9. Revenue / dollar retention — NRR & GRR

Cohort the revenue of a customer group and measure it a year later (Drivetrain, GRR; Orb, NRR vs GRR; SaaS Capital, Good Retention Rate, 2025):

GRR = (Starting ARR − churn − contraction) / Starting ARR              # ≤ 100%, no expansion
NRR = (Starting ARR − churn − contraction + expansion) / Starting ARR  # can exceed 100%

10. Sean Ellis test & power-user curve

11. Building cohort tables in SQL

Canonical three-step pattern (Holistics, Cohort Retention with SQL; Cube, Cohort Retention Recipe; O’Reilly, SQL for Data Analysis ch.4):

  1. Assign each user a cohort (their first-activity period).
  2. Compute period offset for every activity (period − cohort_period).
  3. Pivot/aggregate counts per (cohort, offset) and divide by cohort size.
WITH first_activity AS (          -- 1. cohort assignment
  SELECT user_id,
         DATE_TRUNC('month', MIN(event_date)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (                     -- 2. period offset per active month
  SELECT e.user_id, fa.cohort_month,
         DATE_TRUNC('month', e.event_date) AS active_month,
         (DATE_PART('year',  e.event_date) - DATE_PART('year',  fa.cohort_month)) * 12
       + (DATE_PART('month', e.event_date) - DATE_PART('month', fa.cohort_month)) AS month_number
  FROM events e
  JOIN first_activity fa USING (user_id)
),
sizes AS (
  SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
  FROM first_activity GROUP BY cohort_month
)
SELECT a.cohort_month, a.month_number,           -- 3. retention table
       COUNT(DISTINCT a.user_id) AS active_users,
       ROUND(100.0 * COUNT(DISTINCT a.user_id) / s.cohort_size, 1) AS retention_pct
FROM activity a JOIN sizes s USING (cohort_month)
GROUP BY a.cohort_month, a.month_number, s.cohort_size
ORDER BY a.cohort_month, a.month_number;

Tools / Frameworks

Methodology

  1. Define the active event explicitly (login? key action? value moment?). Everything downstream depends on this.
  2. Pick the retention definition (N-day vs unbounded vs bracket) to match product usage frequency. State it on every chart.
  3. Build acquisition cohorts, plot curves, find the retention floor.
  4. Segment by behavioral cohort to find the activation behavior that lifts the floor.
  5. Run growth accounting (users and MRR) to see whether growth is new-driven or retention-driven; compute the quick ratio.
  6. Layer revenue retention (NRR/GRR) for monetized products.
  7. Validate PMF with flattening curve + power-user tail + Sean Ellis test.

Practical Patterns

Anti-Patterns

Troubleshooting

References