Rolling retention

Definition

Rolling retention is the percentage of a cohort that is active on day N or on any day after it, treating a user as retained if they ever returned at or beyond that point.

Where classic retention asks whether a user showed up on a particular day, rolling asks whether they had left for good by then. That inversion makes it the natural definition for churn, because its complement is a clean statement: the users who never returned on or after day N have, by this measure, churned before it.

Why it is always higher than classic

Every user counted by classic retention at day N is also counted by rolling retention at day N, because being active on day N satisfies "day N or later". The reverse is not true. Rolling therefore sits above classic at every point on the curve, and the gap between them is itself informative — it is the population that returns irregularly.

A wide and persistent gap means your product is used irregularly but genuinely. A gap that collapses toward zero means the users who remain are daily users and everyone else has gone — which is healthy for a habit product and alarming for one that expects occasional use.

Rolling retention is also monotonically non-increasing in N by construction, so it never produces the sawtooth pattern classic does. That is convenient for a chart and is precisely why it is the wrong tool for detecting weekly habits.

The maturity problem, which is worse here

"Or any day after" has no upper bound, so rolling retention at day N can only increase as more time passes. A cohort measured today at D7 may gain retained users tomorrow when a dormant user returns on day 40. This is not a data error; it is the definition. It does mean recent cohorts are systematically understated.

The capped variant is worth adopting as the default. Rolling D7 with a 30-day cap is comparable across cohorts as soon as each is 37 days old, and it loses almost nothing, because the users who return after a month were rarely going to change a decision.

The query

-- Rolling: active on day N or later. The uncapped version keeps
-- rising as cohorts age, so the capped column is the comparable one.
WITH cohort AS (
  SELECT user_id, DATE(installed_at) AS cohort_date
  FROM installs
),
last_seen AS (
  SELECT
    c.user_id,
    c.cohort_date,
    DATE_DIFF(MAX(DATE(s.started_at)), c.cohort_date, DAY) AS last_active_day
  FROM cohort c
  JOIN sessions s USING (user_id)
  WHERE s.is_foreground
  GROUP BY c.user_id, c.cohort_date
),
params AS (SELECT 7 AS n, 30 AS lookahead_cap)
SELECT
  p.n AS day_n,
  COUNT(*) AS cohort_size,

  -- Uncapped: ever active on day N or after.
  ROUND(100.0 * COUNTIF(l.last_active_day >= p.n) / COUNT(*), 2) AS rolling_pct,

  -- Capped: active between day N and day N + cap. Comparable across cohorts.
  ROUND(100.0 * COUNTIF(l.last_active_day BETWEEN p.n AND p.n + p.lookahead_cap)
        / COUNT(*), 2) AS rolling_capped_pct
FROM last_seen l
CROSS JOIN params p
-- Only cohorts old enough to have been observed for the full cap.
WHERE l.cohort_date <= CURRENT_DATE() - p.n - p.lookahead_cap
GROUP BY p.n;

Because rolling retention depends only on the last active day, it is far cheaper to compute than classic — one aggregate per user rather than a day-by-day expansion. On large event tables that difference is substantial, and it is a reasonable argument for materialising last_active_day per user per cohort as a daily job.

What it is genuinely good for

• Churn. One minus rolling retention at day N is a defensible churn definition, because it means "never came back at or after day N".
• Lifetime estimates. The rolling curve is closer to a survival function than the classic curve is.
• Irregular-cadence products. Booking, delivery and finance apps score fairly under rolling and unfairly under classic.
• Executive reporting. It is monotonic, which makes trend claims harder to accidentally overstate.

Its weaknesses follow from the same properties. It cannot detect weekly rhythm, it responds slowly to a release regression because a single bad day is absorbed by "or later", and it flatters recent product changes only after a long wait. Most teams end up reporting rolling for churn and lifetime while keeping classic as the operational chart — the retention rate overview sets out how the three fit together.