What is Cohort Analysis and How to Improve Retention? Cohort Report in Google Analytics

You’ve achieved your first thousand customers. Congratulations! What do you know about them? What do they need now? Moreover, what will you offer them (and when) to tie them closely to your brand?

To answer these and other important questions, we suggest you try cohort analysis. It will be best for your business if you learn how to apply it as soon as possible to make your retention measures more data-driven.

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 users who are visiting your site for the first time in December or make their first purchase between November 1–7. This action distinguishes the cohort from all your other customers.

Cohort analysis is the process of dividing users into groups based on certain criteria and examining how the behavior of these groups changes over time. This type of analysis helps you understand how your digital marketing campaigns affect key performance indicators: conversion and retention rates, churn rate, revenue per user, CLV, ROI, CAC, etc.

Now, we’ll take a look at several cohort analysis tools, explain the meaning of cohort reports on examples and how to read such reports to improve your digital marketing strategy.

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:

Here you can set the four parameters mentioned above: the cohort type (which is also the attribute that defines the cohort), its 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 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.

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:

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’ meaning is how the cohort retention rate has changed for all users and for each group 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 report by weeks, you may notice that sales in all groups fall sharply on the sixth week. Now you know when to launch remarketing.

You can perform cohort analysis using 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, size, and reporting period:

An example of a 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 such a report in Smart Data:

If you still have any questions, take advantage of a free trial or discuss how cohort reports and other OWOX BI features can help your marketing strategy with our specialists.

If you lack the cohort analysis capabilities you need in Google Analytics and you don’t use other analytics systems, you can build this type of 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:

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 incorporating Client ID, we select the necessary information:

In the first part of the request, we select users depending on the week in which they registered. So the cohort formation attribute meaning 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:

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:

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 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.

• =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.

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 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:

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.

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.

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.

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.

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:

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:

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.

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 size to exclude cohorts with too few users.

Cohort reports in 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.

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 group 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 such a report in Kissmetrics:

All methods of behavioral research are aimed at improving customer engagement and retention metrics. Cohort analysis gives you hints on when it’s the best time to remind customers about your company or product with a good-looking offer, who exactly is more likely to buy, and who the best buyers are based on their sequence of actions.

Analyzing your data from the acquisition point of view, you can see how long customers stay interested and continue to buy from you or continue to use your app or service. Segments based on behavioral characteristics let you understand if sending a particular offer changes the behavior of the whole cohort and enlarges its CLV.

To improve retention, you can apply the results of analysis in your bid management, personalized emails and offers, and ad targeting.

Cohort analysis helps you to correlate user cohorts and see how exactly behavior of various cohorts differs. You can divide users into cohorts for analysis based on their first sighup for your product (acquisition cohorts) or their behavior within a selected period (behavioral cohorts).

This approach presumes that you divide users by when they first signed up for your product. You can check what day, week, or month your users first accessed your website or app, and track daily, weekly or monthly cohorts.

Acquisition cohorts help you figure out how long customers continue to use your app from their starting point.

In contrast with the approach above, according to this approach you should track users by actions they take or don’t take in your app or website within a particular time period. You can select any actions that are important for you to track — registration, click a Play button, adding items to cart, etc.

For example, a group of users who performed specific actions within a selected timeframe can form one cohort. You can monitor it and compare to other ones to discover for how long different users stay active after they do specific actions.

Predict and increase your CLV

Customer lifetime value (CLV) is the revenue a company receives from a customer over the course of their relationship. You’ll clearly see when your customers stop buying from you and will be able to calculate CLV for each customer segment to make precise predictions. With this information, you can better plan your advertising expenses, tuning your campaigns for each segment based on acquisition channel.

Retain your best customers

You can use cohort analysis to find out who your most loyal customers are and then encourage them to stay with your company longer. It will be cheaper than acquiring new customers, in any case.

Improve A/B testing

Standard A/B tests won’t tell you how a new design or any other change may affect conversions in the long run. To find out, create a cohort based on interactions with a new design. Then compare its conversion rate with cohorts that didn’t interact with the new design. In this way, you’ll see how the new design affects conversions.

Research app performance

Cohort analysis is a favorite method of mobile app analytics, showing user interest in an app, activity inside an app, etc. It helps app marketers understand bottlenecks and places where users have difficulty using an app so developers can improve it.

Consider OWOX BI as both an advanced solution for cohort analysis and part of your business analytics (BI) toolbox.

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, but they allow you to dig deeper.

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:

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