How to use cohort analysis to determine what the customer wants?

How to use cohort analysis
to determine what
the customer wants?

#сonversions

#KPI

AUGUST 25, 2022 | 6 MIN

Stay tuned with our latest marketing reviews

Every day we leave a trail behind us as we surf the World Wide Web. Users generate innumerable amounts of information every minute, contributing to the big data, working with which is a necessity for most online businesses today.

The abundance of data makes it harder to determine the usefulness of this or that piece of information to make a final decision. It is necessary to get a certain set of tools to quickly navigate in arrays of data and work effectively with them. One of them is cohort analysis, the features of which will be discussed in this article.

Cohort analysis is a subset of behavioral analytics aimed at obtaining information about patterns and trends in user behavior over a certain period of time within their lifecycle. Modern cohort analysis helps not only to process the data, but also to visualize them.

How do customers interact with the site over a long period of time?
What patterns of user behavior exist and what might be their causes?
What effect do different advertising campaigns have on users?

If you’re wondering about these questions and don’t know how to answer them quickly, cohort analysis can help.

The basic principle of cohort analysis is to divide the data into related groups (cohorts) based on performance metrics or business indicators. This is different from the classical approach, where an array of data is considered and analyzed as a whole.

What is it?

Most often users are divided into cohorts based on the time of the desired action — vising the page, registering, filling out a form, downloading, purchasing, etc. In other words, a cohort is a segment of users who performed a certain action in a given time interval. A simple example of dividing customers into cohorts based on the date of first purchase:

Cohort analysis allows you to focus only on the data that are relevant to certain goals. We can identify behavioral trends faster and determine the factors influencing those trends by looking at users within cohorts at specific time intervals.

This method of analysis helps to determine the causes of user churn, assess the effectiveness of advertising campaigns in the long term and customer retention strategies, and learn their buying habits.

Cohort analysis algorithm

The client — a regional online store of professional cosmetics.

The objectives of the analysis — to determine how users interact with the online store over time and find the causes of buying behavior patterns, if any.

Data from CRM were used for the analysis: date and amount of the order, order ID and client ID, as well as the channel that lead the customer to the website.

We took the time period of interest to determine which cohort the user belongs to. The time period, in our case a year, was divided into equal intervals — months. User cohorts were based on the date of the first purchase.

Thanks to this data it was possible to determine how much profit each cohort generated during the year.

A diagram helps understand the information better.

On the basis of the data obtained, we can draw the following conclusions:

The members of each cohort bring the client the greatest profit during the first month, then the number of purchases significantly decreases;
Customers respond positively to “retention techniques,” such as remarketing campaigns or holiday promotions — this can be seen in the peaks of user activity on the chart;
Existing customers have the potential to develop. You have to work with them to increase profits and avoid the cost of attracting new customers.

There are many scenarios to build a cohort analysis. Everything is limited only by the data available and your ingenuity. You can start with a simple and quick analysis, and you can definitely make it from scratch.

5 steps:

Prepare data on users and purchases;
Import data into Google Sheets;
Put them in order;
Enter formulas and create tables;
Enjoy your splendor.

Data preparation and import
Regardless of how deep you intend to analyze and how many factors you want to address, your data set must contain:

The date on the basis of which a user will be refered to a cohort, for example, the date of registration;
The date of conversion — purchase, download, etc.

Creating a table

The easiest way to visualize a table where the rows are cohorts and the columns are the interval of time during which users do or do not perform the desired action.

The number in this cell means how many users registered between October and November performed a conversion action.

Putting data in order

The first thing to do is to format and separate the data into different sheets to make the analysis process as quick and convenient as possible.

Create a sheet where your initial data will be stored: user IDs, dates of registration and conversion actions, column with user numbering (to count the number of users in the cohort, you need to assign each a value of 1)

The table with the results of the analysis and charts is better placed on a separate sheet.

Working with formulas

The SUMIFS formula summarizes data in an interval based on different criteria.

=SUMIFS (sum range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

Put simply

sum_range = the column with the result (how many people belong to this or that cohort);

criteria_range1 = search range;

criterion1 = search criterion;

criteria_range2 & criterion2 = additional conditions that can be used to define more complex segments.

For the example above:

sum_range = the interval including all users in question;

criteria_range1 = the date range within which the user must register to belong to a cohort;

criterion1 = date range, during which the conversion action occurred (additional user selection criterion).

So the formula should look like this:

=SUMIFS(‘Data’!$F$2:$F$625,’Data’!$D$2:$D$625,”>”&$C$1,’Data’!$D$2:$D$625,”
<”&$D$1,’Data’!$E$2:$E$625,”>”&A7,’Data’!$E$2:$E$625,”<”&A8)

The formula checks the data for compliance with the specified conditions: date of registration and conversion action, and as a result outputs the sum — the number of users in the cohort. The diagram makes the dynamics of their behavior easier to understand.

Et voilà! You have just done a cohort analysis. Now you can get useful insights about your audience without spending a lot of time and effort.

The tool becomes even more useful by adding several metrics and conditions, and by exploring weekly or daily cohorts.

Visualizing the results

To get the results, you will need to fill in the table using the formula (from the example), calculate the sum of each cohort, and make a chart.