Setting up Cohort Analysis Excel: 6 Easy Steps

The best way to understand customers is by analyzing customer data. This can give a business some insights that can spearhead its growth. Businesses can also use such insights to come up with successful growth strategies. They can learn more about their Customer Retention Rate as well as the Average Lifetime Value (LTV) for their customers. That is exactly what the Cohort Analysis does. 

Cohort Analysis helps marketers and businesses separate growth metrics from engagement metrics since it’s easy for growth to mask engagement problems. The lack of activity of old users can be hidden by the high number of new users. In this article, you will be learning about Cohort Analysis and the steps for setting up Cohort Analysis Excel. 

Steps to Perform Cohort Analysis in Excel

In this section, you will be learning how to build a Cohort Analysis and calculate the Average Lifetime Value (LTV) of users in Excel. 

The following are the general Cohort Analysis steps:

• Cohort Analysis Excel Step 1: Understand and Clean the Data Set
• Cohort Analysis Excel Step 2: Add New Columns to the Data
• Cohort Analysis Excel Step 3: Data Visualization
• Cohort Analysis Excel Step 4: Perform Cohort Churn Analysis

Cohort Analysis Excel Step 1: Understand and Clean the Data Set

Before doing anything with the dataset, make sure that you understand it. Look for any errors and abnormalities in the dataset and deal with them. A good example of an abnormality, in this case, is “cancel dates” that begin earlier than the start dates. You should identify and look for ways of handling them. 

The dataset to be used shows user details like their id, the starting date of their plan, the date they cancelled their plan, their monthly payment, and their plan Id. 

Cohort Analysis Excel Step 2: Define the cohorts

After gathering the data, you must classify it according to the cohorts you wish to examine. Groups of clients or users with comparable traits are called cohorts. You must make a table with the unique values for the feature you wish to investigate in order to define the groups.

Cohort Analysis Excel Step 2: Add New Columns to the Data

The current data gives you the foundation for Cohort Analysis. To perform Cohort Analysis on the data, you should first add new columns to help you calculate new information. 

Examples of such information include Cohorts, number of active months, and customer LTV. There are different ways of defining and calculating Cohorts in Excel. In this case, a Cohort will represent the month in which a customer was acquired. 

The format should be the same for all customers in a Cohort and it can be calculated using the “End of Month” function that finds the end of the previous month and adds 1 to get the start of the current Cohort as shown below:

=EOMONTH(Start Month,-1)+1

After getting values for the Cohort column, you can proceed to the number of active months. The number of active months is the Average Lifetime Value (LTV) of a customer, that is, from when the customer was acquired to when he stopped using the product or service. 

You can calculate this using the DATEIF function, which determines the number of days, months, or years between two dates. The active months are inclusive since customers pay during their cancel months. The values for this column can be calculated using the following formula:

=DATEIF(Start Date,End Date,"m")+1

There are different ways of calculating the Average Lifetime Value (LTV) of a customer. The simplest approach is by taking the monthly payment of a customer and multiplying it by the total number of active months to get the total revenue. 

It will be good for you to understand the syntax and the logic of each function before trying it. You can press the F1 key (PC) to access the Excel Help menu and get information about each function. 

Cohort Analysis Excel Step 3: Data Visualization

You can now use your expanded data set to group your individual customer data into Cohorts. 

You can then start to generate charts that visualize your data and aid in the Data Analysis process. 

More Information about generating charts in Microsoft Excel can be found here.

Cohort Analysis Excel Step 4: Perform Cohort Churn Analysis

A Cohort Churn Analysis determines how well you’ve retained customers over the lifetime of each Cohort. You can perform this Analysis using the COUNTIFS function, which counts the number of cells in a particular range that meet a particular criterion. 

The function takes the following syntax:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)

In Cohort Analysis, this function can be used to count the number of active users per Cohort. The current active customers can be divided by the total number of users in the Cohort so as to get the percentage of active customers per month. 

=COUNTIFS('MY Data'!$F$2:$F$515,"="&J$1,'MY Data'!$C$2:$C$515,">"&EOMONTH(J$1,$B22))/J$33

Note, that you must format the cells correctly so as to get a percentage. The final product should be a visualized analysis of Customer Churn for every Cohort, which can help one understand the Retention strategies which were not effective and the ones that should be replicated in the future. More information regarding Churn Analysis.

Cohort Analysis Excel Step 6: Analyze the outcomes

Lastly, you must interpret the Cohort Analysis’s findings. Examine the data for trends and patterns, such as if some groups have retention rates that are higher or lower than others. Utilize this data to generate data-driven decisions that improve client retention and maximize the customer experience.  

Benefits of using Cohort Analysis Excel

The following are five advantages of utilizing Excel’s cohort analysis feature:

a) Recognise patterns and trends:

Businesses may use it to monitor how certain client groups behave over time and spot trends and patterns in user behavior. By using this data, marketing efforts that are more specifically targeted, product offers that are optimized, and customer retention rates are raised.

b) Measure customer lifetime value:

By monitoring their purchasing habits over time, it can assist companies in understanding the lifetime worth of their clients. This makes it easier for companies to identify their most valued clients and figure out how to keep them interested in their offerings.   

c) Improve customer retention:

It can assist companies in creating proactive strategies and identifying the causes of client attrition. This would enable companies to enhance client retention by taking proactive measures. Businesses may uncover common trends and solve underlying issues by examining groups of people who have ceased using their product or service.

d) Optimize marketing campaigns:

By determining which channels and campaigns work best for attracting and keeping consumers, it may assist businesses in optimizing their marketing efforts. Businesses may also determine which initiatives are generating the most engagement and income by monitoring user behaviour over time.  

e) Improved decision-making:

Cohort analysis, like any analytics tool, helps the company make better decisions. It gives companies insightful information on the behavior of their customers, enabling them to make wise choices regarding things like product development, marketing, and customer support. Businesses may find areas for development and make data-driven choices that spur growth and profitability by monitoring user behavior over time. 

Limitations of Cohort Analysis

The following are the drawbacks of Cohort Analysis:

• Cohort Analysis requires you to keep a sizeable and detailed dataset within your business, which makes it costly and time-consuming. 
• It is subject to bias by the person performing the analysis. This can result in useless results. 

Example of How to Create Cohort Analysis in Excel

Here’s an example of how an e-commerce website can conduct a Cohort Analysis:

Step 1: Establish the metric and cohort

You must first specify the cohort that you wish to examine. Assume for the purposes of this example that we wish to examine the customer cohort whose first purchase occurred in January 2022. The overall revenue this stratum generates will be the parameter we analyze.

Step 2: Create the cohort table

The next step is to make a table that plots this cohort’s monthly revenue over time. This is an illustration of a cohort table: 

This table displays the monthly revenue that the Jan 2022 group produced for the six months following their initial purchase. The total revenue that cohort brought in for that particular month is indicated by the numbers in each cell. 

Step 3: Calculate Cohort Retention Analysis Excel Rate

You will now need to figure out the Retention Rate for every month using the Cohort Analysis Formula. The percentage of first-group consumers who made a purchase in each month afterward is known as the retention rate. The retention rate table may resemble this:

The percentage of clients from the first cohort that made a purchase in each month that followed is displayed in this table. For instance, in February 2022, 50% of the Jan 2022 cohort made a purchase. 

Step 4: Examine the outcomes

Lastly, you will make some inferences on the behavior of your customers using the cohort and retention rate data. As an illustration:

a) The Jan 2022 cohort made the highest money in their initial month of business, but over time, that income progressively decreased.

b) Each succeeding month’s retention rate is comparatively low, suggesting that many consumers do not ultimately favor the brand.   

Step 5: Taking action

Taking action based on the knowledge acquired from the cohort analysis is the last phase. The marketing plan will need to be changed to concentrate on drawing in more clients in January, for instance, if the example indicates that consumers who signed up in January had a higher retention rate. 

Conclusion

In this article, you learned in detail about the process of Cohort Analysis and the steps that need to be followed for setting up Cohort Analysis Excel. Automated integration with your Data Warehouses/multiple data sources and the analytics database can make your choice much simpler as a lot of necessary features can be integrated readily. Integrating and analyzing data from a huge set of diverse sources can be challenging, this is where Hevo comes into the picture.

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline helps you transfer data from a source of your choice in a fully-automated and secure manner without having to write the code repeatedly. Hevo with its strong integration with 150+ data sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a spin?Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable Hevo pricing that will help you choose the right plan for your business needs!

Share your experience of learning about Cohort Analysis Excel! Let us know in the comments section below!

References

1. Cohort analysis steps