# 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. Source: https://docs.querylab.io/bigquery/ga4-retention/ Updated: 2026-11-04 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: **retention by days since first seen** ```sql 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 ``` Result in BigQuery (3 rows): | day_n | users | retention | | --- | --- | --- | | 0 | 4 | 1.0 | | 1 | 2 | 0.5 | | 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`](https://docs.querylab.io/bigquery/pivot-and-unpivot/) 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: **first seen in the window against first touch** ```sql 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 ``` Result in BigQuery (2 rows): | day | new_by_window | new_by_first_touch | | --- | --- | --- | | 2026-01-02 | 1 | 1 | | 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](https://docs.querylab.io/bigquery/ga4-export/#dates-and-time-zones). Convert with the property's zone, or a user can land on day -1: **first touch in UTC and in the property's zone** ```sql 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 ``` Result in BigQuery (1 row): | utc_day | new_york_day | | --- | --- | | 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: **day-7 retention with and without young cohorts** ```sql 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 ``` Result in BigQuery (2 rows): | base | day_7_retention | | --- | --- | | all cohorts | 0.4 | | 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](https://docs.querylab.io/bigquery/ga4-export/#which-tables-you-get), 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: **exactly day 7 against day 7 or later** ```sql 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 ``` Result in BigQuery (1 row): | classic_day_7 | rolling_day_7 | | --- | --- | | 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](https://docs.querylab.io/bigquery/ga4-export/#which-tables-you-get) 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`](https://docs.querylab.io/bigquery/approximate-aggregation/) can stand in for exact counts on very large properties. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [BigQuery Export schema (Analytics Help)](https://support.google.com/analytics/answer/7029846) - [BigQuery Export user-data schema (Analytics Help)](https://support.google.com/analytics/answer/12769371)