Retention is the metric that quietly predicts whether a product lives or dies, and the GA4 export holds everything you need to measure it honestly. The trick is grouping users by when they arrived. Here is how to build a cohort.

What a retention cohort is

A cohort groups users by the period they first appeared, then tracks how many return in each later period. Read down a cohort and you see how a given week's users decay over time; read across and you compare weeks. It answers the survival question a single retention number hides.

Step 1: find each user's first week

Everything hinges on a first-seen date per user, which you derive from the minimum event timestamp:

WITH first_seen AS (
  SELECT
    user_pseudo_id,
    MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) AS cohort_date
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260331'
  GROUP BY user_pseudo_id
)

Step 2: join activity back to the cohort

Next, pair every active day a user has with their cohort date, and measure the gap in weeks:

, activity AS (
  SELECT
    e.user_pseudo_id,
    DATE_DIFF(DATE(TIMESTAMP_MICROS(e.event_timestamp)), f.cohort_date, WEEK) AS week_number
  FROM `project.analytics_123456789.events_*` e
  JOIN first_seen f USING (user_pseudo_id)
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260331'
)
SELECT
  DATE_TRUNC(f.cohort_date, WEEK) AS cohort_week,
  a.week_number,
  COUNT(DISTINCT a.user_pseudo_id) AS users
FROM activity a
JOIN first_seen f USING (user_pseudo_id)
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number

Week 0 is the cohort's size, and each later week is how many came back, so:

retention rate (week N) = users active in week N รท users in week 0

Read it as a triangle

The output forms a triangle: newer cohorts have fewer observed weeks. That shape is normal, and the diagonal edge is just the present catching up. Compare the same week number across cohorts to see whether retention is improving.

Common pitfalls

A few things quietly distort a cohort:

  • Using event_date as a string instead of a real date, which breaks the week math.
  • Forgetting the suffix filter, so you scan and pay for months you do not need.
  • Counting events instead of distinct users, which inflates every cell.

Retention is the metric that predicts survival, and a cohort is how you see it instead of guessing. Pin each user's first week, measure the gap to every later active week, and read the triangle across cohorts. Do that and the number that decides whether a product lives stops being a vague worry and becomes something you can watch and move.

Want a stronger data analyst role or a raise? Grab the FREE Product Analyst Playbook and get the exact roadmap to your next offer.