Retention rate

Definition

Retention rate is the percentage of a cohort of users who are still active after a defined period, measured from a fixed starting event such as install.

The definition above is where the agreement ends. "Active after seven days" can mean active on day seven exactly, active at any point during the first week, or active on day seven or later — three different questions that produce three different numbers from identical data, all commonly labelled D7. Most retention arguments are definition mismatches wearing a data disguise.

Three definitions, one cohort

Take 1,000 users who installed on the same day. Every number below comes from that identical group; only the question changes.

Classic is always the lowest and rolling is always at least as high as classic, because every user counted by classic is also counted by rolling. Range sits above classic too, and can exceed rolling for short windows. A team quoting 31% and a team quoting 8.4% for the same app are both right and are not disagreeing about anything factual.

Which to use for what

The jaggedness of classic retention is not noise to be smoothed. A weekly-habit app shows peaks on days 7, 14 and 21 and troughs between them, and that pattern is the product working as designed. Choosing range retention to make the chart smooth throws away the signal that told you the habit exists.

Rolling retention has the opposite constraint: it can only be computed once enough time has passed that "or later" means something. A cohort installed nine days ago has no trustworthy rolling D7, and reporting one anyway is the most common way retention dashboards fabricate an upward trend at the right-hand edge.

The query, with the definition made explicit

The three definitions differ by one predicate. Writing all three in a single query is the cheapest way to make the choice visible to whoever reads the output.

-- One cohort table, three definitions, no ambiguity about which is which.
WITH cohort AS (
  SELECT user_id, DATE(installed_at) AS cohort_date
  FROM installs
),
activity AS (
  SELECT
    c.user_id,
    c.cohort_date,
    DATE_DIFF(DATE(s.started_at), c.cohort_date, DAY) AS day_n
  FROM cohort c
  JOIN sessions s USING (user_id)
)
SELECT
  cohort_date,
  COUNT(DISTINCT user_id) AS cohort_size,

  -- Classic: active on day 7 exactly.
  ROUND(100.0 * COUNT(DISTINCT IF(day_n = 7, user_id, NULL))
        / COUNT(DISTINCT user_id), 1) AS classic_d7,

  -- Range: active anywhere in days 1-7.
  ROUND(100.0 * COUNT(DISTINCT IF(day_n BETWEEN 1 AND 7, user_id, NULL))
        / COUNT(DISTINCT user_id), 1) AS range_d1_d7,

  -- Rolling: active on day 7 or later.
  ROUND(100.0 * COUNT(DISTINCT IF(day_n >= 7, user_id, NULL))
        / COUNT(DISTINCT user_id), 1) AS rolling_d7
FROM activity
-- Only cohorts old enough for all three to be defined.
WHERE cohort_date <= CURRENT_DATE() - 8
GROUP BY cohort_date
ORDER BY cohort_date DESC;

The WHERE clause at the bottom is the part most implementations omit. Without a maturity filter, the newest cohorts appear in the output with partial data, and every retention chart slopes reassuringly upward at the edge where it has the least information.

Retention is where attribution stops being an accounting exercise

Retention split by acquisition source is the single most useful cross-section in growth analytics, because it is where install fraud and low-quality traffic become undeniable. A source whose installs retain at a fraction of every other channel is not a source with unlucky users.

• Retention indistinguishable from organic, at implausible volume. Consistent with click spamming reassigning organic installs.
• Near-zero D1 across a whole source. Often incentivised or emulated installs rather than a product problem.
• A single sub-publisher dragging a channel's curve down. Split before you judge the channel.
• A cohort-wide cliff on one date. A release regression, not a marketing problem — check the build.

One caution on comparing sources: deferred deep linking changes retention on its own, because users who land on the content they were promised behave differently from users dropped on a home screen. When comparing channels, hold the post-install destination constant, or you are measuring routing quality and calling it traffic quality.

Whatever you choose, write the definition into the report's own title. D7 (classic, cohort ≥ 8 days old) is a number someone else can reproduce; D7: 8.4% is a number someone else will contradict.