What Is a Cohort Retention Dashboard and Why Does It Matter?

A cohort retention dashboard tracks how well you retain users over time by grouping them into cohorts—typically based on when they first visited your site—and then measuring what percentage of each cohort returns in subsequent periods. It answers the fundamental product and marketing question: are users coming back? A business that acquires 10,000 users per month but retains none of them is on a treadmill. A business that retains 30% of users after three months has compounding growth potential. Cohort retention analysis reveals which acquisition channels bring users who actually stick around, and whether product changes are improving or hurting long-term engagement.

GA4 has a built-in Retention report, but it offers limited customization—you cannot change the cohort definition, the retention metric, or the visual style. By building a cohort retention dashboard in Looker Studio connected to your GA4 BigQuery Export, you get full control over all of these parameters and can create a visualization tailored exactly to your business model.

Step 1: Build the Cohort Retention Query in BigQuery

The core of the dashboard is a BigQuery query that calculates weekly cohort retention. The query first identifies each user’s first-visit week (their cohort), then counts how many users from each cohort returned in subsequent weeks:

WITH user_first_visit AS (
  -- Get each user's first visit week
  SELECT
    user_pseudo_id,
    DATE_TRUNC(MIN(PARSE_DATE('%Y%m%d', event_date)), WEEK) AS cohort_week
  FROM
    `your_project.ga4_export.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20260101' AND '20260417'
    AND event_name = 'session_start'
  GROUP BY user_pseudo_id
),

user_activity AS (
  -- Get all weeks each user was active
  SELECT DISTINCT
    user_pseudo_id,
    DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), WEEK) AS activity_week
  FROM
    `your_project.ga4_export.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20260101' AND '20260417'
    AND event_name = 'session_start'
)

SELECT
  f.cohort_week,
  DATE_DIFF(a.activity_week, f.cohort_week, WEEK) AS weeks_since_first_visit,
  COUNT(DISTINCT a.user_pseudo_id) AS retained_users,
  COUNT(DISTINCT f.user_pseudo_id) AS cohort_size,
  ROUND(COUNT(DISTINCT a.user_pseudo_id) / COUNT(DISTINCT f.user_pseudo_id) * 100, 2) AS retention_rate
FROM
  user_first_visit f
JOIN
  user_activity a ON f.user_pseudo_id = a.user_pseudo_id
WHERE
  DATE_DIFF(a.activity_week, f.cohort_week, WEEK) BETWEEN 0 AND 12
GROUP BY
  cohort_week, weeks_since_first_visit
ORDER BY
  cohort_week, weeks_since_first_visit;

This query produces rows with three key columns: the cohort week (when users first visited), the weeks since first visit (0 = acquisition week, 1 = one week later, etc.), and the retention rate (percentage of cohort still active that week). Week 0 is always 100% by definition—all users are active in the week they first visit. Week 1 shows your one-week retention rate, which is often the most revealing early signal of product-market fit.

Step 2: Save as a BigQuery View

Save this query as a BigQuery view rather than running it ad hoc. In the BigQuery console, click “Save” → “Save as view.” Name it ga4_cohort_retention_weekly. Update the date range in the query to use dynamic dates (DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)) so the view always reflects the most recent 90 days of data without requiring manual date updates. Connect Looker Studio to this view as your data source.

Step 3: Create the Looker Studio Report

Open Looker Studio and create a new report. Add your BigQuery view as the data source (Project → Dataset → ga4_cohort_retention_weekly). You now have access to four fields: cohort_week, weeks_since_first_visit, retained_users, cohort_size, and retention_rate.

The signature visualization for cohort retention is a pivot table (also called a cohort triangle or retention matrix). In Looker Studio, add a Pivot Table chart. Set the Row Dimension to cohort_week. Set the Column Dimension to weeks_since_first_visit. Set the Metric to retention_rate. Set the sort to ascending on both dimensions. This creates a triangular matrix where each row is a cohort week and each column shows that cohort’s retention at week 0, 1, 2, and so on up to week 12.

Step 4: Apply Conditional Formatting for the Heatmap Effect

Raw numbers in a pivot table are hard to scan. Apply conditional formatting to turn the retention matrix into a color-coded heatmap. In the chart settings, find “Conditional formatting” and add a rule: color cells from red (low retention) to green (high retention) based on the retention_rate metric. Set the minimum (red) to 0% and the maximum (green) to 100%. Now the retention matrix shows at a glance which cohorts have strong retention (rows that stay green across many weeks) and which have sharp drop-offs (rows that quickly turn red after week 0). You can immediately see whether recent cohorts are retaining better or worse than older ones—revealing whether product or marketing changes are having a positive effect on long-term user behavior.

Step 5: Add a Cohort Size Column for Context

Add a second chart—a simple bar chart with cohort_week on the X axis and cohort_size as the metric. Place this bar chart directly above or below the retention heatmap. This gives critical context: a 25% Week 4 retention rate means very different things for a cohort of 100 users versus a cohort of 10,000 users. The bar chart lets viewers immediately calibrate how much weight to give each cohort’s retention figures.

Step 6: Segment Retention by Acquisition Channel

The most powerful use of a cohort retention dashboard is comparing retention across acquisition channels. Modify your BigQuery view to join the user_first_visit CTE with the traffic source of the first session. Add a first_session_medium column to the view output. In Looker Studio, add a filter control tied to this field. Now stakeholders can filter the entire retention heatmap to show only organic search users, only paid social users, or only direct visitors—revealing which channels bring users who actually engage long-term versus one-and-done visitors who never return.

This analysis often produces surprising results. Organic search traffic frequently shows dramatically higher Week 4 and Week 8 retention than paid social traffic—because organic visitors found your content by actively searching for it (high intent) while paid social visitors were interrupted by an ad (low intent). This data can make a compelling case for investing more in SEO and content marketing relative to top-of-funnel paid social.

Automating Data Freshness with Scheduled Queries

For a production dashboard that your team checks regularly, convert your BigQuery view into a materialized table updated by a daily scheduled query. The cohort retention calculation is computationally heavy—querying months of raw GA4 events every time Looker Studio refreshes would be slow and expensive. By materializing the results daily into a summary table and connecting Looker Studio to that table, your dashboard loads in seconds regardless of how much historical data you have accumulated.

Conclusion

A Looker Studio cohort retention dashboard built on GA4 BigQuery data gives your team the long-term user engagement visibility that GA4’s built-in retention report lacks. The custom BigQuery query lets you define exactly what counts as a “returning user,” the pivot table heatmap makes multi-cohort comparison instantly scannable, and the acquisition channel segmentation reveals which marketing investments build a loyal audience versus which attract visitors who never return. Build this dashboard once and it becomes one of the most referenced tools in your analytics stack.