GA4 retention in BigQuery
Day-N retention from the GA4 export is the share of a cohort active N days after its first day. Use user_first_touch_timestamp to find new users, and leave out cohorts too young to have reached day N.
Practice this in a free lesson on learn.querylab.io: Day-N retention.
Day-N retention is the share of a cohort that comes back exactly N days after its first day. From the GA4 export: find each user’s first day, compute the number of days from it to each later active day with DATE_DIFF, count distinct users per N, and divide by the size of the cohort. Two things go wrong often. A date window can make old users look new, and recent cohorts haven’t had N days yet, which pulls the rate down.
Day-N retention
The example starts from one row per user and active day. In a real query that is SELECT DISTINCT user_pseudo_id, PARSE_DATE('%Y%m%d', event_date) over the shards:
WITH activity AS ( SELECT * FROM UNNEST([ STRUCT('a' AS user_pseudo_id, DATE '2026-01-01' AS active_day), ('a', DATE '2026-01-02'), ('a', DATE '2026-01-08'), ('b', DATE '2026-01-01'), ('b', DATE '2026-01-02'), ('c', DATE '2026-01-01'), ('d', DATE '2026-01-01'), ('d', DATE '2026-01-08') ]) ), first_seen AS ( SELECT user_pseudo_id, MIN(active_day) AS cohort_day FROM activity GROUP BY user_pseudo_id ) SELECT DATE_DIFF(a.active_day, f.cohort_day, DAY) AS day_n, COUNT(DISTINCT a.user_pseudo_id) AS users, ROUND(COUNT(DISTINCT a.user_pseudo_id) / 4, 2) AS retention FROM activity AS a JOIN first_seen AS f USING (user_pseudo_id) GROUP BY day_n ORDER BY day_n
| Row | day_n | users | retention |
|---|---|---|---|
| 1 | 0 | 4 | 1.0 |
| 2 | 1 | 2 | 0.5 |
| 3 | 7 | 2 | 0.5 |
All four users are active on day 0, so that row is 1.0. Two come back on day 1 and two on day 7. Divide by the cohort’s size, in a real query a COUNT from the first_seen step, rather than by the previous day.
For a triangle with one row per cohort day, add cohort_day to GROUP BY and divide by each cohort’s size. Use PIVOT to turn N into columns.
Who is new
MIN(event_date) inside a query only sees the shards that query reads. A user from last June, first seen in your January window, looks new. Here a first touched the site in 2025:
WITH users AS ( SELECT * FROM UNNEST([ STRUCT('a' AS user_pseudo_id, DATE '2025-06-01' AS first_touch, DATE '2026-01-03' AS first_in_query), ('b', DATE '2026-01-02', DATE '2026-01-02'), ('c', DATE '2026-01-03', DATE '2026-01-03') ]) ) SELECT first_in_query AS day, COUNT(*) AS new_by_window, COUNTIF(first_touch = first_in_query) AS new_by_first_touch FROM users GROUP BY day ORDER BY day
| Row | day | new_by_window | new_by_first_touch |
|---|---|---|---|
| 1 | 2026-01-02 | 1 | 1 |
| 2 | 2026-01-03 | 2 | 1 |
user_first_touch_timestamp is the time the user first opened the app or visited the site. Use it for the cohort, with MIN per user, because it can be NULL: it is missing on 5,348 of 245,209 events in a week of Google’s sample. It is in microseconds and in UTC, while event_date is in the property’s time zone. Convert with the property’s zone, or a user can land on day -1:
WITH events AS (SELECT 1767238200000000 AS user_first_touch_timestamp) SELECT DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) AS utc_day, DATE(TIMESTAMP_MICROS(user_first_touch_timestamp), 'America/New_York') AS new_york_day FROM events
| Row | utc_day | new_york_day |
|---|---|---|
| 1 | 2026-01-01 | 2025-12-31 |
Young cohorts
If today is January 12, users from January 9 can’t have reached day 7. Leaving them in the denominator counts them as lost. Keep only cohorts at least N days old:
WITH users AS ( SELECT * FROM UNNEST([ STRUCT('a' AS user_pseudo_id, DATE '2026-01-01' AS cohort_day, TRUE AS back_on_day_7), ('b', DATE '2026-01-01', FALSE), ('c', DATE '2026-01-05', TRUE), ('d', DATE '2026-01-09', FALSE), ('e', DATE '2026-01-09', FALSE) ]) ) SELECT 'all cohorts' AS base, ROUND(COUNTIF(back_on_day_7) / COUNT(*), 2) AS day_7_retention FROM users UNION ALL SELECT 'only cohorts 7 days old', ROUND(COUNTIF(back_on_day_7) / COUNT(*), 2) FROM users WHERE cohort_day <= DATE_SUB(DATE '2026-01-12', INTERVAL 7 DAY) ORDER BY base
| Row | base | day_7_retention |
|---|---|---|
| 1 | all cohorts | 0.4 |
| 2 | only cohorts 7 days old | 0.67 |
The real figure here is 0.67, and the unfiltered query reports 0.4. Recent daily tables can also still change for about three days, so leave those out too if you report on a schedule.
Classic or rolling
Classic retention asks “was the user active on day 7?”. Rolling retention asks “was the user active on day 7 or later?”. User b came back on day 9, so only the rolling form keeps them:
WITH day_offsets AS ( SELECT * FROM UNNEST([ STRUCT('a' AS user_pseudo_id, 0 AS day_n), ('a', 7), ('b', 0), ('b', 9), ('c', 0), ('c', 3) ]) ) SELECT COUNT(DISTINCT IF(day_n = 7, user_pseudo_id, NULL)) AS classic_day_7, COUNT(DISTINCT IF(day_n >= 7, user_pseudo_id, NULL)) AS rolling_day_7 FROM day_offsets
| Row | classic_day_7 | rolling_day_7 |
|---|---|---|
| 1 | 1 | 2 |
Classic suits products used daily. Rolling suits ones used now and then, and it is also the number that can rise later, since a user can still come back.
What a user is
user_pseudo_id identifies a browser or an app install. The same person on a phone and a laptop is two users, and cleared cookies make a new one, so retention from this ID is a lower bound for people who switch devices. user_id is set only if your site sends it. Where you have it, COALESCE(user_id, user_pseudo_id) joins a person’s devices once they have logged in.
The optional user-data export has a row for each user whose data changed that day, which isn’t the same as active. Its count can be higher than Active users, so compute retention from events.
A retention query reads every shard in its date range. Selecting user_pseudo_id, event_date and user_first_touch_timestamp keeps it cheap, since none of them is nested. APPROX_COUNT_DISTINCT can stand in for exact counts on very large properties.