How to do cohort analysis using Looker Studio

If you want to go beyond surface-level metrics and really understand how user behaviour evolves, cohort analysis is the tool you need. In this post, you’ll learn how to do cohort analysis using Looker Studio, from preparing your data to building pivot tables and calculating retention percentages.

Cohort analysis helps you track user retention, understand customer lifetime value, and evaluate engagement patterns across time. By the end, you’ll be able to apply this method to optimise your product, marketing, or ecommerce strategy.

What is cohort analysis?

A cohort is a group of users who share a common trait within a specific time period. Examples include:

• Users who signed up in January
• Customers who made their first purchase in February
• App users who first installed in March

By tracking these cohorts over time, you can see:

• How long users stay engaged
• How frequently they return or purchase
• How value evolves from their first interaction

Preparing your data

To create an effective cohort analysis in Looker Studio, your dataset needs to include:

• User identifier: A unique ID for each user
• Cohort date: The user’s first interaction, like sign-up or first purchase
• Activity date: Any date where the user took a meaningful action (e.g., logging in, purchasing again)

Data sources you can use:

• Google Analytics 4 + BigQuery (best for scale and automation)
• Supermetrics connectors (fast integration with marketing data)
• Google Sheets (good as a starting point)

For ecommerce, structure your sheet with:

• Order date
• Transaction ID
• Customer ID
• First purchase date (to define Cohort Date)

Creating the calculated fields

Once your data is ready and connected (for example, via Google Sheets), it’s time to build out the calculated fields. These are essential for grouping and comparing users over time:

• Cohort month:
  Use this formula to extract the cohort’s year and month:
  FORMAT_DATE('%Y-%m', Cohort_Date)
• Activity month:
  Do the same for the activity date:
  FORMAT_DATE('%Y-%m', Activity_Date)
• Months since cohort:
  This tells you how many months have passed since the user joined:
  TIMESTAMP_DIFF(PARSE_DATE('%Y-%m', Activity_Month), PARSE_DATE('%Y-%m', Cohort_Month), MONTH)

These fields will allow you to group data meaningfully in your visualisations.

Building the cohort table

The most common (and useful) way to visualise cohorts is in a pivot table. Here’s how to structure it:

• Rows: Cohort month
• Columns: Months since cohort
• Values: Number of active users (typically done using COUNT_DISTINCT on the User Identifier)

This layout gives you a matrix showing how many users from each cohort remained active in each subsequent month. You can adjust the time granularity (monthly or weekly) depending on your goals.

Showing retention with percentages

Raw counts are great, but if your cohorts are different sizes, comparisons can be tricky. That’s where percentages come in.

Create a calculated field like this:

Active_Users / Total_Cohort_Users

This shows the percentage of users retained each month relative to the original cohort size, making trends much easier to spot. To enhance readability, consider applying conditional formatting or heat maps in your table.

Templates can help

Short on time? There are ready-made templates out there that can fast-track your setup. For example, Gaille Reports offers a cohort analysis template for Looker Studio (Google Sheets-based) that includes key metrics like Lifetime Value and Customer Retention. Templates like this are a solid starting point if you want to skip the setup and go straight to analysis.

Key takeaways on how to do cohort analysis using Looker Studio

• Cohort analysis tracks retention trends and user engagement over time.
• You’ll need User ID, Cohort Date, and Activity Date in your dataset.
• Create calculated fields to group and compare cohorts.
• Use pivot tables with retention percentages for clarity.
• Templates can fast-track your reporting.

👉 Learning how to do cohort analysis using Looker Studio gives you actionable insights into user behaviour that simple metrics cannot provide.

FAQ: How to do cohort analysis using Looker Studio