Cohort Analysis in BigQuery: Measuring User Retention

What Cohort Analysis Shows

A cohort is a group of users who share a common characteristic at a specific time — typically the date of their first visit or first purchase. Cohort analysis tracks how each cohort behaves in subsequent weeks or months, revealing retention patterns: how many users who first visited in week 1 also returned in week 2, week 4, week 8?

This is more insightful than aggregate session counts because it shows the true retention trajectory of different user cohorts, enabling comparison between cohorts acquired through different channels or in different periods.

Step 1: Define First Touch Date per User

CREATE OR REPLACE TABLE `project.analytics.user_first_touch` AS
SELECT
  user_pseudo_id,
  MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) as first_touch_date
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260630'
GROUP BY user_pseudo_id

Step 2: Calculate Weeks Since First Touch

WITH first_touch AS (
  SELECT
    user_pseudo_id,
    MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) as cohort_date
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260630'
  GROUP BY user_pseudo_id
),
activity AS (
  SELECT
    e.user_pseudo_id,
    DATE(TIMESTAMP_MICROS(e.event_timestamp)) as activity_date
  FROM `project.analytics_PROPERTY.events_*` e
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260630'
)
SELECT
  ft.cohort_date,
  DATE_DIFF(a.activity_date, ft.cohort_date, WEEK) as weeks_since_first_touch,
  COUNT(DISTINCT ft.user_pseudo_id) as cohort_size,
  COUNT(DISTINCT a.user_pseudo_id) as retained_users,
  ROUND(COUNT(DISTINCT a.user_pseudo_id) * 100.0
    / COUNT(DISTINCT ft.user_pseudo_id), 1) as retention_rate
FROM first_touch ft
JOIN activity a ON ft.user_pseudo_id = a.user_pseudo_id
GROUP BY ft.cohort_date, weeks_since_first_touch
ORDER BY ft.cohort_date, weeks_since_first_touch

Reading Cohort Results

The output shows each cohort (by acquisition week), the weeks since first touch (0 = acquisition week, 1 = one week later, etc.), and the retention rate. A healthy ecommerce cohort might show 100% in week 0, 20-30% in week 1, 15-20% in week 4, and 10-15% in week 12. Lower retention rates in specific cohorts may indicate that specific acquisition channels bring lower-quality users.

Summary

BigQuery cohort analysis: define cohorts by first touch date per user_pseudo_id, then join to all activity to calculate weeks since first touch, grouping by cohort and week to get retention rates. The key metric is the percentage of each cohort that returned in each subsequent week. Compare retention curves across acquisition channels, campaigns, or time periods to identify which sources drive users with the best long-term engagement.

See our BigQuery Setup service for retention analysis development.

Need cohort analysis in BigQuery? Contact Adslytics.