How to Do Cohort Analysis Using Looker

Understanding user retention is one of the most powerful ways to gauge the health of your product or marketing efforts. Cohort analysis is the best tool for the job, letting you see exactly how groups of users behave over time. This guide breaks down how to build a clear, insightful cohort analysis report directly within Looker, step-by-step.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Cohort Analysis?

Before jumping into Looker, let's quickly refresh on the core concept. A "cohort" is simply a group of people who share a common characteristic over a specific time period. In most digital products and e-commerce stores, the most common cohort is based on an acquisition date.

• An acquisition cohort might be all users who signed up in January.
• Another cohort could be all customers who made their first purchase in the first week of March.
• A different one might be users who came from a specific Facebook Ads campaign in Q2.

Cohort analysis is the process of tracking these groups over time to see how their behavior changes. For most businesses, the key questions cohort analysis helps answer are related to retention and engagement:

• How long do our new users stick around? Do January signups behave differently than February signups?
• Did our new onboarding flow actually improve user engagement? We can compare the cohort from before the change to the one after.
• Which marketing channels bring in the most loyal customers? Analyze cohorts based on their acquisition source to see which one has the best long-term retention.

Instead of looking at a single, blended "user retention rate," cohort analysis gives you a much clearer, more actionable picture of user behavior patterns.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Pre-Analysis Checklist: What You'll Need in Looker

To successfully build a cohort chart, your data needs to be structured in a way Looker can understand. Before you open a new Explore, confirm with your data team — or check yourself if you have access to your LookML model — that you have these three essential pieces of information available as dimensions:

1. A User Identifier: A unique ID for each user (e.g., User ID or Email Address). This is necessary to count distinct users.
2. A Cohort Date: The timestamp when a user joined a cohort. This is usually their sign-up date or first purchase date (e.g., User Created Date or First Order Date).
3. An Activity Date: The timestamp of the actions you want to track over time. This could be any repeat activity like a login, a purchase, or a page view (e.g., Order Created Date or Login Date).

Having these three fields readily available as separate dimensions will make the rest of the process straightforward. If they aren't, you may need to ask a developer to expose them in your LookML model first.

Building Your Cohort Analysis in Looker: A Step-by-Step Guide

Once you’ve confirmed you have the necessary data, you can build the analysis in a Looker Explore. We'll build a classic retention table showing the percentage of users from each monthly cohort who returned in the subsequent months.

Step 1: Select Your Cohort Dimension and Metric

First, you need to define your cohorts. In your Looker Explore, find your cohort date dimension. For this example, we’ll use User Created Date.

• From the field picker on the left, find your User Created Date dimension.
• Click on it to add it to your query.
• By default, it may show the raw date. You'll want to group this by month. In Looker, you can typically do this by selecting the "Month" timeframe from the dimension's dropdown or by choosing a pre-built "Created Month" dimension if one exists.
• Next, add your primary metric. Find a measure that counts your unique users, like Unique User Count or Count of Distinct IDs, and add it to your query.

At this point, if you hit "Run," you'd have a simple table showing how many unique users you acquired each month. This is the base of our cohort chart.

Step 2: Create a Dimension for "Months Since Sign-Up"

Now for the most important part: calculating the time difference between when a user signed up (their cohort) and when they performed a subsequent action. This tells us if a user returned in "Month 1," "Month 2," etc.

In most Looker setups, you'll need to create a custom dimension to handle this calculation. Let's imagine we're tracking repeat orders, using Order Created Date as our activity date.

• In the dimensions list, click the Add button next to "Custom Fields".
• Select Dimension to open the custom dimension editor.
• We need to write a Looker expression that calculates the difference in months between the activity date (Order Created Date) and the cohort date (User Created Date).

The formula will look something like this:

diff_months(${orders.created_date}, ${users.created_date})

• Give your new dimension a clear name, like "Months Since Sign-Up," and a format (Integer).
• Click Save.

This new dimension now calculates, for every activity, how many months have passed since that user first signed up.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3: Pivot the Data by Time Since Sign-Up

The goal is to see a grid with your cohorts as rows and the "Months Since Sign-Up" as columns. A pivot is the perfect tool for this.

• Find your newly created custom dimension, "Months Since Sign-Up".
• Instead of just clicking it, click the Pivot icon next to it.

This tells Looker to use the values from this dimension as the column headers in your results table, which gives us the classic cohort grid format.

Step 4: Filter and Run

Before you run the query, it’s good practice to add a filter to limit the timeframe of your cohorts. Find your User Created Date dimension again, but this time click the Filter icon.

• Set a reasonable date range, like "is in the last 12 months."

Now, click the Run button. You should see a table with your cohorts (e.g., "January 2023," "February 2023") as rows and the "Months Since Sign-Up" (0, 1, 2, 3...) as columns. The cells will be filled with the raw count of unique users who were active in that specific period.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 5: Visualize as a Percentage

While raw numbers are useful, cohort analysis is typically viewed in terms of percentages to make retention comparable across cohorts of different sizes. We need a table calculation to do this.

• Click the Add button next to "Custom Fields" in the Data bar, and select Table Calculation.
• The formula will divide the count of users in each cell by the total number of users in that cohort's first month (Month 0).

The formula will look something like this. Remember to replace ${users.count} with the actual name of your user count measure:

${users.count} / pivot_index(${users.count}, 1)

• In the editor, set the Format to "Percent (2)."
• Give it a name like "Retention %". Click Save.
• Finally, you can hide the raw count measure from the visualization by clicking the gear icon next to its column name and selecting "Hide from Visualization".

You now have a clean retention table showing the percentage of users from each cohort who returned over time.

From Numbers to Insights: How to Interpret Your Cohort Chart

With your table built, the fun part begins. Here’s how you can read it to pull out valuable insights:

• Read Across a Row: Follow a single row (e.g., the "January 2023" cohort) from left to right. This shows you the retention journey for that specific group of users. You'll see how quickly the percentage of active users drops off month after month.
• Read Down a Column: Follow a single column (e.g., "Month 3") from top to bottom. This compares retention at the same point in lifecycle across different cohorts. For example, you can quickly see if newer cohorts have better or worse Month 3 retention than older ones.
• Look for Trend Changes: Is there a sudden improvement in retention for all cohorts that started after a certain date? For example, if your May cohorts showed much stronger Month 1 retention than previous months, you can ask, "What significant product change did we launch or what marketing campaign did we run in May?"
• Use the Heatmap Visualization: Go to the "Visualization" tab and select the "Table" option. Within its settings, you should find a "Cell Visualization" option. Enabling this creates a heatmap, which uses color to instantly highlight strong and weak retention periods without you having to read every single number.

Final Thoughts

Building a cohort analysis in Looker gives you a powerful, granular view of user behavior and retention over time. By defining your cohorts, calculating the time lapsed since acquisition, and pivoting the results, you can move beyond simple, averaged metrics and see the true impact of your product and marketing efforts.

The process, while powerful, often involves a learning curve and multiple steps to get right, from finding the right fields to writing custom expressions. We built Graphed to skip past that complexity entirely. Instead of configuring dashboards and table calculations manually, you can just ask in plain language: "Create a cohort analysis showing user retention by sign-up month." We connect to your data sources, generate the live dashboard, and help you get answers in seconds, not hours — no custom expressions required.