Docs/Google Analytics 4/Retention
Google Analytics 4

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:

retention by days since first seenOpen in Querylab.io
12345678910111213141516171819
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
Rowday_nusersretention
1041.0
2120.5
3720.5
3 rows

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:

first seen in the window against first touchOpen in Querylab.io
1234567891011121314
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
Rowdaynew_by_windownew_by_first_touch
12026-01-0211
22026-01-0321
2 rows

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:

first touch in UTC and in the property's zoneOpen in Querylab.io
12345
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
Rowutc_daynew_york_day
12026-01-012025-12-31
1 row

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 cohortsOpen in Querylab.io
123456789101112131415161718
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
Rowbaseday_7_retention
1all cohorts0.4
2only cohorts 7 days old0.67
2 rows

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:

exactly day 7 against day 7 or laterOpen in Querylab.io
1234567891011
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
Rowclassic_day_7rolling_day_7
112
1 row

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.

Open the playground. It is the real app on public datasets and needs no sign-in.