A detailed guide to cohort analysis in Google Analytics and Google Sheets

4
1193
Downloads

Obviously, first-time buyers and repeat customers behave differently on your website and bring in different amounts of revenue. With the right marketing strategy, these differences can be very beneficial for your business. But how can you identify patterns and understand what distinguishes people who first visited your site a month ago from those who first visited a year ago? Cohort analysis lets you do this.                

In this article, we’ll tell you what cohort analysis is and what it’s for. We’ll talk about the possibilities and limitations of cohort analysis in Google Analytics and describe in detail four ways of conducting a cohort analysis in Google Sheets.

Table of contents

What cohort analysis is and where to apply it

A cohort, in marketing terms, is a group of people who perform a certain action in a given period of time: for example, a group of people who first visit your site in December or make their first purchase between November 1–7. This action distinguishes the cohort from all your other customers.

The whole idea of cohort analysis is to divide users into groups based on certain criteria and examine how the behavior of these groups changes over time. Cohort analysis helps you understand how your marketing efforts affect key performance indicators: conversion and retention rates, LTV, ROI, CAC, etc.

Let’s review a few examples of what you can do using cohort analysis.

1. More accurately evaluate the ROI of ad campaigns

Not all users who visit your site for the first time immediately complete a conversion action. Some people need time to study your offer, review the pros and cons, compare it with competitors, and then decide whether to buy, subscribe, or register. This is especially true for businesses with delayed conversions, long sales cycles, and expensive products.

The longer the period from the first visit to conversion, the higher the chance of incorrectly evaluating the effectiveness of your advertising channels. Cohort analysis helps solve this problem. Let’s say that in November you launch a paid ad campaign in Google Ads. A month later, you review the results and see that ROI is below 100%. Your first thought is to turn off the ad. But let’s not jump to conclusions. Instead, let’s wait and see for a couple more months, just as an experiment. If you analyse the cohort of users who were attracted by this ad campaign in November and users at the same ROI report for January, you might see that ROI has grown significantly. This means people were thinking for more than a month but eventually made a purchase.

Cohort example

2. Find and retain loyal customers

With the help of cohort analysis, you can find where your most loyal customers come from. For example, you can create a cohort of visitors who logged in to your site for the first time between January and June. Then you can segment them by attraction channel and compare the retention rate or repeat purchase rate by month for each channel. It’s clear that channels with higher rates are more worthy of your attention and investment.

By analyzing cohort behavior, you can better plan your marketing campaigns. For example, you may notice that the user activity in a cohort falls sharply in the fifth month. You can then send an email with a personal discount to warm up their interest.

3. Predict and increase LTV

Lifetime value (LTV) is the revenue that a company receives from a customer over the course of their relationship. This metric is difficult to calculate until someone ceases to be your customer. However, it’s possible to calculate the revenue from a single cohort over one month and predict how much money this cohort will bring in over a longer period with the help of LTV calculations.

What’s more, you can segment cohorts across ad channels to compare LTV and customer acquisition cost (CAC). This will help you to understand the ROI period for each channel and redistribute your marketing budget more effectively.

cohort analysis and CAC

4. Conduct A/B testing

Cohort analysis can also be used to evaluate the results of A/B tests. Let’s say you’ve updated the button design on your site. To find out whether this has affected the conversion rate, you create two variants of one page. Half of your users see the old button (part A), the other half see the new one (part B). Next, you consider the A/B conversion ratio based on button clicks.

However, a usual A/B test doesn’t answer the question of how a new design will affect conversions in the long run. You can use cohort analysis to find this out. Starting three to four weeks after the release, build a cohort report that considers users who first came to the site from a page with the new button. Then, compare the conversion rate of these users against a cohort who didn’t see the new design. This difference will clearly show you how the new design has affected conversions.

5. Analyze the performance of mobile apps

Cohort analysis takes a special place in mobile app analytics, where it helps to assess the user retention rate based on app version. Using cohort analysis, you also can determine which sources bring the most active users:

cohort analysis for mobile app analytics

What do you need for cohort analysis?

No matter in which system you plan to build a cohort report, you’ll need to consider four key parameters:

  1. The attribute that defines the cohort: date of the first visit to the site, date of purchase or registration, etc.
  2. Cohort size: the time interval over which to define your cohort — days, weeks, or months
  3. Report period: the time during which you’ll analyze the cohort’s behavior
  4. The key metric for analysis: ROI, retention rate, LTV, etc.

The choice of an attribute to define a cohort with depends on what indicator you want to analyze and improve. Need to calculate the retention rate and stop the outflow of users? Explore the behavior of people who first visited, registered, or installed the app at the same time. Want to calculate and increase LTV? Form cohorts by date of first purchase.

This doesn’t mean that there should be only one attribute that defines a cohort. You can use any conditions to create a cohort according to your needs. For example, in a cohort, you can identify users who both registered in July and made their first purchase that same month.

Cohort analysis in Google Analytics

To create a report on cohorts in Google Analytics, go to the Audience —> Cohort Analysis menu. At the very top, you’ll see these settings:

cohort analysis in Google Analytics

Here you can set the four parameters mentioned above: the cohort type (which is also the attribute that defines the cohort), cohort size, an indicator to analyze, and a reporting period.

Since cohort analysis in Google Analytics is in beta testing, there are certain limitations:

You can create cohorts only by date of the first visit. 

  • You can analyze only one indicator in one cohort report. In total, there are 14 indicators available in the settings. 
  • There are three options available in the Cohort size field: day, week, and month.
  • You can’t set your own reporting period, since the date range is limited and depends on the cohort size. For example, the maximum reporting period for cohorts created by day is 30 days; by week, 12 weeks; and by month, 3 months.
  • You can’t filter data in the standard settings, for example by traffic type, device type, or another parameter. For this, you need to use segments. 

There’s a graph under the settings that shows the dynamics of an analyzed indicator for all users and offers three cohorts to choose from.

acquisition date cohorts by user retention

In the screenshot above, we see that the customer retention rate for all users during the third week is 4.83%, and for the cohort of users who first visited the site from February 3–9 the retention rate is 0.99%.

Under the graph, you’ll see a table with data for each cohort for the entire reporting period. Let’s figure out how to interpret it. For example, let’s choose:

  • Indicator — customer retention rate​
  • Cohort size — by week
  • Data range — the last 6 weeks

As a result, we get this table:

cohort analysis table Google Analytics

In the first column, we see how many people visited the site during the reporting period as well as the number of users in each weekly cohort. The remaining columns show how the retention rate has changed for all users and for each cohort from week to week. The higher the retention rate, the darker the color of the cell.

From the screenshot above, it’s clear that from December 30 through January 5, 6,604 new users logged on to the site. A week later, only 4.47% of them returned to the site; after two weeks, 2.32% returned, and so on.

In general, cohort analysis in Google Analytics helps you understand your data in more detail. For example, you might see that sales per quarter are growing due to the inflow of new customers. However, if you dig deeper and look at the cohort report by weeks, you may notice that sales in all cohorts fall sharply on the sixth week. Now you know when to launch remarketing.

Cohort reports in other services

In addition to Google Analytics, built-in cohort reports are available in many advertising and analytics services. They are found in almost all analytics systems for mobile applications.

AppsFlyer has more flexible settings than Google Analytics, allowing you to add multiple filters to a cohort report. You can also set a minimum cohort size to exclude cohorts with too few users.

Cohort reports in AppMetrica (Yandex.Metrica for mobile applications) and Adjust are used mainly to track retained users. In Adjust, you can add a second indicator for analysis, for example the number of sessions per user:

An example of a cohort report in AppMetrica:

an example of a cohort report in AppMetrica

Mixpanel and Kissmetrics also support cohort analysis. These are also analytics systems for websites and mobile applications that work with data at the scale of users, not visits and page views. The report settings in these services are very similar to the settings in Google Analytics, but there are some differences.

In Kissmetrics, you can form a cohort by two attributes at a time. For example, you can build a cohort of users who visited the site and converted to a subscription. You can also group people not only by time, as is commonly done in cohort reports, but also by any other attribute such as purchase amount, location, and traffic source.

An example of a cohort report in Kissmetrics:

an example of a cohort report in Kissmetrics

Cohort reports in OWOX BI Smart Data

Recently, we added cohort analysis to our own OWOX BI Smart Data service for data visualization. As a data source, OWOX BI Smart Data uses your Google BigQuery project, to which you can upload information from Google Analytics, advertising services, and CRM and ERP systems.

To get a cohort report in Smart Data, simply type the word «cohort» in the search line and the system will show you a drop-down menu in which you can select your desired metric, cohort size, and reporting period:

An example of a cohort analysis request in OWOX BI Smart Data

As of now, OWOX BI Smart Data can only create cohorts by user acquisition date. But in the near future, we’ll add more attributes.

An example of a cohort report in Smart Data:

An example of a cohort report in OWOX BI Smart Data

By the way, we provide a 14-day trial period so you can try cohort reports and other OWOX BI features for free.

TRY OWOX BI FOR FREE

Cohort reports in Google Sheets

If you lack the cohort analysis capabilities you need in Google Analytics and you don’t use other analytics systems, you can build a cohort report manually in Google Sheets. You can do this in one of four ways, which we’ll discuss in more detail below — namely pivot tables, formulas, filter formulas, and Apps Script.

To build a cohort report using the first three methods, you should prepare and upload the source data to Google Sheets. This data should be structured in the following way:

Cohort reports in Google Sheets

It’s important that in the first column you have a start month/week/day for cohort formation (registration_week), in the second column you have the transaction month/week/day (transaction_week), and then any columns (purchases, in this case) with data on the metric you’re analyzing.

You can upload data to Google Sheets in any convenient way. In our example, we use raw data that we export from Google Analytics to Google BigQuery with the help of OWOX BI Pipeline. Then, using a SQL query, we select the necessary information:

    
SELECT
registration_week,
transaction_week,
SUM ( purchases ) AS purchases
FROM (
SELECT
IFNULL ( user.id, clientId ) AS userID,
STRING ( YEAR ( date )) + ’-’ + RIGHT ( ’0′ + STRING ( WEEK ( date )), 2 ) AS registration_week
FROM TABLE_DATE_RANGE ( [projectID:datasetID.session_streaming_], TIMESTAMP ( ’2017-08-01′ ), CURRENT_TIMESTAMP ())
WHERE hits.eventInfo.eventCategory = ’registration’ ) AS t1
LEFT JOIN EACH (
SELECT
IFNULL ( user.id, clientId ) AS userID,
EXACT_COUNT_DISTINCT ( hits.transaction.transactionId ) AS purchases,
STRING ( YEAR ( date )) + ’-’ + RIGHT ( ’0′ + STRING ( MONTH ( date )), 2 ) AS transaction_week
FROM TABLE_DATE_RANGE ( [projectID:datasetID.session_streaming_], TIMESTAMP ( ’2017-08-01′ ), CURRENT_TIMESTAMP())
WHERE hits.eCommerceAction.action_type = ’purchase’
GROUP BY userID, transaction_week) AS t2
ON t1.userID = t2.userID
WHERE REPLACE ( registration_week, ’-’, ’’ ) <= REPLACE ( transaction_week, ’-’, ’’ )
GROUP BY registration_week, transaction_week,
ORDER BY registration_week, transaction_week,
    

In the first part of the request, we select users depending on the week in which they registered. So the cohort formation attribute is registration, and the cohort size is a week.

In the second part, we choose the number of purchases per week. This is the metric we want to analyze. We link all parts of the request by User ID.

Then we upload the data to Google Sheets using the OWOX BI BigQuery Reports Add-on. One of the advantages of this add-on is that you can set calculations to happen at whatever frequency you need and the report will be updated automatically.

1. Cohort report using pivot tables

The easiest way to build a cohort report is with a pivot table. Remember that in Google Sheets, we already have data in the necessary structure: column A is the week of registration, column B is the week of transactions, and column C is the number of purchases. Now select the relevant date range, open the Data tab, and select Pivot Table. You’ll see a panel with settings on the right:

Cohort report using pivot tables

In the Rows block, select the week of registration; in the Columns block, select the transaction week (additional columns show the following weeks); and in the Values block, select the number of purchases. Then apply conditional formatting to make the report more convenient and you’ll get this table:

If you’ve analyzed several metrics, the report may look like this:

cohort analysis with few metrics

You can use filters in the pivot table, but it’s not convenient. Every time you want to filter the data, you’ll have to open the table settings and look for the value you need.

2. Cohort report using formulas

The same cohort report can be built using three simple formulas. For this, we create a new sheet in Google

Sheets and apply these formulas: 

  • =UNIQUE(Example!A:A) 

We insert this formula into cell A2. It pulls all weeks of cohort formation from the source data table into column A.

  • =TRANSPOSE(unique(Example!B2:B)) ​

This formula is used in cell B2. It pulls all transaction weeks from the column in the source data table and converts them into a row.

transpose formula in cohort table
  • =SUMIF(Example!$C:$C,Example!$A:$A,$A3,Example!$B:$B,B$2) ​

We insert this formula into cell B3 at the intersection of the week of registration and the week of the transaction. This formula summarizes all purchases from column C of the source data table.

sumif formula in cohort table

Note that Example in these formulas is the name of our spreadsheet in Google Sheets with the source data. Next, we need to stretch these formulas over the entire sheet and apply conditional formatting. Then the report is ready.

3. Cohort report using formulas and filters

If you want to conveniently filter data in the report by any parameter, the third formula should be made a bit more complicated. Let’s figure out how to do it.

First, you need to add source data parameters to the table to filter the data for the cohort report. In our example, we have a traffic source, a channel, and a campaign name. You can add any parameter you need: for example, location data (continent, country, city), mobile application version, product category, or something else.

To make the most lifelike example, we add more analyzed metrics to the report and also change the cohort size from week to month. As a result, we get a table with the source data:

a table with the source data

After that, create an additional sheet and pull all registration (cohort formation) months from the source data table using the first formula: = UNIQUE (Example! A: A). This will make it easier to access the data. Let’s name this sheet «Month.»

Later on, we’ll add a new sheet that will contain our report and create two filters by Source/Medium and Campaign. To do this, open the Data —> Data Validation tab. In the Cell range field, enter the cell address to specify which cell will display the filter drop-down list. In the Criteria field, leave the List from a range condition and specify the column address with the necessary parameters from the source data table. Click Save.

data validation

As a result, we get this filter:

Then, using the formula =Month!A$2, we pull up (in column A) the months of registration from the Month sheet. Each value must be repeated as many times as there are analyzed metrics (in our example, we have three metrics). That’s why we pulled up months of cohort formation in a separate sheet.

month sheet of cohort report with few metrics

Since we have only three parameters in our example (Users, Revenue, Cost) and they’re the same for all cohorts, we enter the names manually in order not to use formulas to pull them from another sheet.

In cell C2, we use the formula =TRANSPOSE(UNIQUE (Example2! B2:B)), which pulls up months of transactions from the source data table and converts them into a row:

And finally, at the intersection of the month of registration, the subsequent month, and a specific metric, we insert our third formula:
=IF(AND($B$1="",$D$1=""),SUMIF(Example2!$E:$E,Example2!$A:$A,$A3,Example2!$B:$B,C$2),IF(AND($B$1=""),SUMIF(Example2!$E:$E,Example2!$A:$A,$A3,Example2!$B:$B,C$2,Example2!$D:$D,$D$1),IF(AND($D$1=""),SUMIF(Example2!$E:$E,Example2!$A:$A,$A3,Example2!$B:$B,C$2,Example2!$C:$C,$B$1),SUMIF(Example2!$E:$E,Example2!$A:$A,$A3,Example2!$B:$B,C$2,Example2!$C:$C,$B$1,Example2!$D:$D,$D$1))))

This formula calculates the required metric in the desired cell based on filters.

desired metric calculations based on filters

Since we have two filters, we need to include all possible combinations in the formula:​

  • Both filters are empty
  • Both filters are filled
  • Only the first filter is filled
  • Only the second filter is filled

For each metric, the formula in the cell remains unchanged; only the column by which the sum of a specific metric is calculated changes. For example, for the Users metric, we calculate the sum of the column Example2!$E:$E; for the revenue metric, the column Example2!$H:$H; and for the Cost metric, the column Example2!$I:$I.

If you want to have more than two filters in your report, you’ll need to increase the number of conditions and the size of the formula. You can learn how to do this from the webinar «Cohort Analysis 101.» In this webinar, you’ll hear our analyst Anastasiya Chausova explain in detail what part of the formula is responsible for what. Anastasiya shows how to build all the reports described in this article in Google Sheets. Fill out the form below and we’ll email you a link to the webinar, a presentation, and sample reports.

ACCESS THE WEBINAR

4. Cohort report using Google Apps Script

Google Apps Script is a programming language that allows you to add features and process data in Sheets and other Google services. To build a cohort report using Apps Script, you don’t need to transfer data to Google Sheets. The table with the source data is created in Google BigQuery, and from there the script exports it to the report.

Using this script, you can:​
  • Create a menu on the Google Sheets toolbar to run your own calculations in a click.
  • Execute data queries in BigQuery and access the results of these queries. 
  • Create cells that will be used as filters. Parameters in such cells must be entered manually. This means the filter won’t have a drop-down list, as in a report built using formulas. You specify the value by which you want to filter the data in the cell yourself, and this value is used in the SQL query to the data in Google BigQuery. 
  • Change the way the report is displayed: apply conditional formatting, make the necessary breaks and indents, change the order of items, flip tables, and much more.

To connect Apps Script to Google Sheets, open the Tools —> Script editor tab. In the opened editor, select Resources —> Additional functions of Google Services and activate the BigQuery API and the Google Sheets API:

Apps Script connection to Google Sheets

Then go to Google Cloud Platform to activate these APIs there as well. After that, you can run scripts in the Script editor that will take data from Google BigQuery and generate cohort reports in Google Sheets.

We wrote a script template for a report with one parameter and two filters. You can create a data table in Google BigQuery with the structure described above. Then you can insert a project name into the script template as well as tables in BigQuery and get a ready-made report:

the ready-made report from script template

In addition, you can easily modify the script template to build a report with the fields and number of metrics you need. Here’s an example of how Boodmo did it. Fill out the form above and we’ll email you the script template for cohort analysis.

Conclusions

If we compare the methods of cohort analysis by complexity, then the simplest option is Google Analytics. However, its capabilities and settings are limited. The second easiest method is pivot tables in Google Sheets. Here you can form cohorts by any parameter, not just by the date of the first visit as in Google Analytics. Options with formulas and Apps Script are a bit more complicated, as they require additional knowledge.

If you need to use filters often, the most convenient option is to create a report using formulas: then all filters will be in a drop-down list. Sure, you can use filters in all Google Sheets reports; but in the pivot table, you need to go to the table settings. With the Apps Script method, you have to specify the filter values manually in each cell. If you make a mistake, your SQL query will return nothing.

We’ve prepared a table comparing the pros and cons of all the cohort analysis methods described in this article:

comparison of cohort analysis methods

If you have any questions, leave a comment below and we’ll be happy to answer!

You might also like