Day-N retention

Retention shows whether people come back, which often says more than raw traffic does. It depends on comparing every later day with a fixed starting group.

How it works

Fix the day-0 cohort and its size once. For each later day, find the distinct active users who are in that same cohort and divide by the original cohort size.

Common mistake

If you divide by each day’s active users, you measure how the audience is made up. Calculating beyond the last available shard reports numbers for days that were never exported.

Worked example

WITH cohort AS (SELECT 'a' AS user_id UNION ALL SELECT 'b' UNION ALL SELECT 'c'),
day2 AS (SELECT 'a' AS user_id UNION ALL SELECT 'c' UNION ALL SELECT 'outside')
SELECT SAFE_DIVIDE(
  (SELECT COUNT(*) FROM cohort WHERE user_id IN (SELECT user_id FROM day2)),
  (SELECT COUNT(*) FROM cohort)
) AS day2_retention

The denominator stays at the three users in the original cohort.

Two cohort users return on day 2 and the outside user does not count, so retention is two thirds.

All BigQuery lessons