Retention analysis sits at the center of decision-making in e-commerce and SaaS models. Yet classic cohort queries run against production event tables perform full-table scans on terabytes of data, taking minutes to complete and driving query costs into hundreds of dollars daily. When cohort calculations happen on-demand, decision cycles slow down, analyst teams fight query optimization battles, and dashboards go stale. The solution: pre-compute cohort tables as partitioned, incrementally refreshed data assets. This guide shows you how to build materialized cohort tables in BigQuery, cut query costs by 90%, reduce analysis latency to seconds, and turn retention decisions into near-real-time competitive advantages.

Why Classic Cohort Queries Don't Scale

Standard cohort analysis works like this: group users by their first transaction date, then measure what percentage return on subsequent days. The SQL query joins the events table twice—once to find the cohort assignment date, once to count retention behavior. On BigQuery, a 500-million-row events table makes this query run 10-15 seconds and cost ~$0.50 per execution. That query repeats on every dashboard refresh, every analyst iteration, every A/B test report.

The problem isn't just cost—it's speed and flexibility. When the analyst team wants to change the cohort definition (say, testing "second add-to-cart" instead of "first purchase"), rewriting, testing, and validating the query takes hours. Dashboards stay stale. When marketing asks "what was last week's cohort retention," there's no live answer—an analyst manually runs a query. This loop delays decisions by days.

Cohort calculations are also a unique data asset requiring an aggregation layer. The retention metric isn't just "user count"—it's "active users / cohort size." This ratio updates daily as new users join cohorts and existing cohorts accumulate new behavior days. Classic queries don't support this incremental logic; they recalculate everything from scratch each time.

From Query to Table: Materializing Cohorts with Views

The first step is hardcoding the cohort definition as a materialized view. BigQuery materialized views physically store query results and incrementally refresh when base tables change. But standard MVs aren't enough for cohorts because cohort definitions and retention windows are dynamic parameters. Instead, build a hybrid: cohort assignment table + retention event aggregation table.

The first table, cohort_assignments, stores when each user first entered their cohort:

CREATE TABLE `project.dataset.cohort_assignments`
PARTITION BY DATE(cohort_date)
CLUSTER BY user_id
AS
SELECT
  user_id,
  MIN(DATE(event_timestamp)) AS cohort_date,
  COUNTIF(event_name = 'purchase') AS total_purchases
FROM `project.dataset.events`
WHERE event_name IN ('first_visit', 'purchase', 'signup')
GROUP BY user_id;

This table holds each user once; cohort_date is the partition key. When new users arrive, only the relevant partition gets updates. Table size scales with users (not events)—10 million users = ~500 MB.

The second table, daily_user_activity, stores whether each user was active each day as a boolean flag:

CREATE TABLE `project.dataset.daily_user_activity`
PARTITION BY activity_date
CLUSTER BY user_id
AS
SELECT
  user_id,
  DATE(event_timestamp) AS activity_date,
  TRUE AS is_active
FROM `project.dataset.events`
WHERE event_name IN ('pageview', 'purchase', 'session_start')
GROUP BY user_id, activity_date;

Now retention queries join these two tables:

SELECT
  c.cohort_date,
  DATE_DIFF(a.activity_date, c.cohort_date, DAY) AS days_since_cohort,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  COUNT(DISTINCT a.user_id) AS active_users,
  SAFE_DIVIDE(COUNT(DISTINCT a.user_id), COUNT(DISTINCT c.user_id)) AS retention_rate
FROM `project.dataset.cohort_assignments` c
LEFT JOIN `project.dataset.daily_user_activity` a
  ON c.user_id = a.user_id
  AND a.activity_date >= c.cohort_date
WHERE c.cohort_date >= '2026-01-01'
GROUP BY c.cohort_date, days_since_cohort
ORDER BY c.cohort_date, days_since_cohort;

This query no longer scans terabytes of raw events. It joins two small tables. On BigQuery, 10 million users run in ~2 seconds, costing $0.02—a 96% cost reduction.

Partition Strategy: Which Date Goes Where

Partition strategy for cohort tables is critical because two temporal dimensions exist: cohort date and activity date. cohort_assignments partitions by cohort_date because this table captures a user's first event and the cohort assignment is fixed. New users land only in today's partition; past partitions stay immutable.

daily_user_activity partitions by activity_date because fresh activity arrives daily and past days never change. This structure suits incremental refresh: your dbt or Airflow job writes only today's partition each morning, leaving history untouched.

Retention analysis, though, joins across two dates: cohort_date to activity_date. To optimize join performance, use cluster keys. BigQuery's CLUSTER BY user_id physically collocates rows with the same user_id, enabling block-level pruning during joins and reducing disk I/O. Without clustering, a join on 10 million users takes ~8 seconds; with clustering, ~2 seconds.

Partition pruning also matters. Retention analyses usually examine the last 90 days of cohorts. A filter like WHERE c.cohort_date >= '2026-05-01' triggers partition pruning—BigQuery reads only relevant partitions. For 2 years of data, queries without pruning cost ~$0.50; with pruning, ~$0.02, because scanned data shrinks 24-fold.

One partition-strategy trade-off: daily partitions ease incremental refresh, but too many partitions (1000+) increase query-planning overhead in BigQuery. Compress older cohorts into monthly partitions or archive data beyond 2 years.

Incremental Refresh: Compute Only New Data

Cohort tables need daily updates—new users join cohorts and existing cohorts' retention behavior shifts. But full refresh (recalculating the entire table) wastes resources. Use the incremental build pattern instead.

In dbt, define an incremental model:

{{
  config(
    materialized='incremental',
    partition_by={'field': 'cohort_date', 'data_type': 'date'},
    cluster_by=['user_id'],
    incremental_strategy='insert_overwrite'
  )
}}

SELECT
  user_id,
  MIN(DATE(event_timestamp)) AS cohort_date,
  COUNTIF(event_name = 'purchase') AS total_purchases
FROM {{ source('raw', 'events') }}
WHERE DATE(event_timestamp) = CURRENT_DATE() - 1
{% if is_incremental() %}
  AND DATE(event_timestamp) > (SELECT MAX(cohort_date) FROM {{ this }})
{% endif %}
GROUP BY user_id

This model computes only yesterday's partition daily. The insert_overwrite strategy deletes the old partition and writes the new one. In BigQuery, partition-level replacement is atomic—downstream queries never read incomplete data.

For daily_user_activity, incremental logic is simpler; a new partition gets appended daily:

{{
  config(
    materialized='incremental',
    partition_by={'field': 'activity_date', 'data_type': 'date'},
    cluster_by=['user_id']
  )
}}

SELECT
  user_id,
  DATE(event_timestamp) AS activity_date,
  TRUE AS is_active
FROM {{ source('raw', 'events') }}
WHERE DATE(event_timestamp) = CURRENT_DATE() - 1
{% if is_incremental() %}
  AND DATE(event_timestamp) NOT IN (SELECT DISTINCT activity_date FROM {{ this }})
{% endif %}
GROUP BY user_id, activity_date

Incremental refresh cuts daily job runtime from 5 minutes to 30 seconds. BigQuery slot usage drops 80%, eliminating query queue wait. By 9 AM, yesterday's retention data is ready on the analyst's dashboard.

One risk: late-arriving data. If your event pipeline lags 2-3 hours, yesterday's partition holds incomplete data. Handle this two ways: (1) dbt's lookback_window parameter—recompute the last 3 days every run; (2) BigQuery _PARTITIONTIME metadata—filter by partition insertion time, reprocessing only late events. The second approach is more efficient.

Query Cost Optimization: Table Size and Scan Patterns

Cohort table costs depend on two factors: table size (GB) and query scan patterns. cohort_assignments for 10 million users is ~500 MB; daily_user_activity for a 90-day window is ~5 GB. Joining both tables makes BigQuery scan ~6 GB, costing ~$0.03. The same analysis on raw events scans 500 GB and costs ~$2.50—an 80x difference.

Push costs lower with a pre-aggregated cohort summary table:

CREATE TABLE `project.dataset.cohort_retention_summary`
PARTITION BY cohort_date
CLUSTER BY days_since_cohort
AS
SELECT
  c.cohort_date,
  DATE_DIFF(a.activity_date, c.cohort_date, DAY) AS days_since_cohort,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  COUNT(DISTINCT a.user_id) AS active_users,
  SAFE_DIVIDE(COUNT(DISTINCT a.user_id), COUNT(DISTINCT c.user_id)) AS retention_rate
FROM `project.dataset.cohort_assignments` c
LEFT JOIN `project.dataset.daily_user_activity` a
  ON c.user_id = a.user_id
  AND a.activity_date >= c.cohort_date
GROUP BY c.cohort_date, days_since_cohort;

This table pre-computes retention ratios for every cohort-day combination. It's ~100 MB (10M users × 90 days = 900M rows → ~50K rows post-aggregation). Dashboards read this table, skip joins, and return results in <1 second, costing ~$0.001.

Another cost-optimization detail: avoid SELECT *. Cohort analysis needs only user_id, cohort_date, activity_date. If daily_user_activity holds event_name, session_id, and other columns, and queries use SELECT *, you scan unnecessary data. BigQuery uses columnar storage—selecting only needed columns cuts disk I/O by 40-50%.

Finally, use BigQuery BI Engine. BI Engine caches your cohort summary table in memory, making dashboard queries return sub-second. A 100 MB table costs ~$10/month in BI Engine reservation but saves ~$30/month in query costs—net win.

The Retention Engineering Pipeline: dbt + Airflow + Alerts

In production, cohort architecture isn't SQL alone—it needs orchestration and monitoring. A retention pipeline has:

  1. Airflow DAG: Triggers daily at 06:00 AM, validates event-table freshness (late-data checks).
  2. dbt incremental models: Sequentially refreshes cohort_assignments, daily_user_activity, cohort_retention_summary.
  3. Data quality tests: dbt tests verify cohort_size > 0, retention_rate BETWEEN 0 AND 1.
  4. Alerting: If today's Day-1 retention drops 20% below last week's average, a Slack alert fires.

Building this pipeline requires CDP & Retention Engineering infrastructure—end-to-end from event collection through cohort definitions, BigQuery optimization, and dashboard integration.

Parameterize cohort definitions with dbt macros:

{% macro cohort_definition(event_name) %}
  SELECT user_id, MIN(DATE(event_timestamp)) AS cohort_date
  FROM {{ source('raw', 'events') }}
  WHERE event_name = '{{ event_name }}'
  GROUP BY user_id
{% endmacro %}

Now the same pipeline runs "first purchase cohort," "first login cohort," and "second add-to-cart cohort" in parallel. When analysts want to test a new cohort, they change a config file—no code changes needed.

Monitor using BigQuery Audit Logs to track query costs per job. If cohort refresh suddenly costs 10x more (maybe partition pruning broke), alert automatically. In production, cost-anomaly detection is part of retention-pipeline reliability.

How Cohort Architecture Changes Decision Speed

Pre-computing cohort tables does more than optimize costs—it transforms decision velocity and analytical flexibility. When marketing asks, "Did iOS cohorts retain better than Android last week?" the answer comes in 10 seconds, not 10 minutes. A/B test results update automatically each day; no manual export-import loops.

Retention becomes a daily operational metric, not a monthly report. If today's cohort's Day-1 retention drops 5%, campaign optimization triggers instantly. If a feature release improves Day-3 retention, you scale immediately. This speed only happens when cohort data stays near-real-time fresh.

Cohort architecture also enables cross-functional collaboration. Product teams use cohort tables to measure feature adoption, finance uses the same retention curve for LTV projections, and customer success derives churn-risk scores from cohort assignments. One data asset serves multiple use cases; data duplication disappears.

Finally, cohort architecture underpins incrementality measurement. Retention analysis answers not just "how many users return" but "which marketing channel's cohorts show better retention." When paired with First-Party Data & Measurement Architecture, your attribution model moves beyond first-click to lifetime-value contribution. By storing utm_source and campaign_id in cohort tables, you compare retention by channel—the fundamental metric for marketing budget allocation.