🔥$39 Lifetime Deal on Magnimetrics Tools for Excel

Cohort Analysis Explained with an Excel example

It’s relatively easy for most businesses to analyze the day-to-day operational marketing and sales metrics like conversion rates, cost of sale, and others. However, when it comes to customer retention, it proves to be a more challenging task. Many companies struggle to define customer retention within their business model, let alone calculate and analyze it.

Cohort Analysis is a form of behavior analysis on groups of users with similar characteristics in a given time frame. We refer to these groups as cohorts, hence the name of this analytics tool. For example, people who purchased the Pro version of online software can be one cohort, while those who only got the Basic version can be another cohort.

Cohort Analysis’s power comes from the fact it does not look at the whole data set as a single unit but instead splits and groups customers into related cohorts.

What is Cohort Analysis

If you took a dictionary, a ‘cohort’ is a group of individuals with a common statistical trait or characteristic, usually within a demographic study’s premises. These can be a similar level of education, age, political views, and others.

Cohort Analysis is a popular way for companies to gain a more in-depth insight into their customers’ behavior. It gives invaluable insight into customer behavior that we can leverage to set up successful growth strategies and improve the decision-making process.

When we perform a Cohort Analysis, we don’t look at individual users or the user base as a whole but instead split those into groups (cohorts). This is done based on similarity in properties. One of the most common properties to differentiate on is the user acquisition period. We can split our customer base into those onboarded in Q1, those onboarded in Q2, Q3, and Q4.

Companies can analyze how users consume products by separating them into cohorts and making the analysis more meaningful and relevant. That way, we can improve our marketing strategy and how we communicate with our customer base.

With Cohort Analysis, we can answer questions like:

  • Do customers acquired in one period behave differently than those in another period?
  • Do customers who bought at promotions behave differently than those paying at full price?
  • Do large companies use our services longer than small companies?

We generally identify Cohort Analysis by breaking down customers into groups based on similar traits. In terms of this analysis, a cohort is the group of customers we focus our analysis on. These clients have something in common during a specific time frame. Based on the question we want to answer with our analysis, we pick the common characteristics to look into.

Cohort Analysis gives us effectiveness and improved accuracy when we drill down large data sets. In business, we use it to classify customers, support the marketing and sales teams’ efforts, and improve the company’s decision-making process.

Sign Up on Substack

Types of Cohort Analysis

Time-based Cohorts

In this type of cohort, we separate customers into groups based on a particular time frame. We can get a lot out of analyzing time-based cohorts. It is helpful when we look into the churn rate of the business. For example, imagine customers on-boarded in Q1 of the year leave on average in 18 months. At the same time, those onboarded in Q2 cancel on average in 6 months. This deviation indicates there may have been some issues with our onboarding process in Q2. We need to investigate further what happened. The case may be that in Q2, a competitor started to offer better offerings to our customers – the improved quality or reduced cost.

Segment-based Cohorts

We can group customers by various other characteristics. For example, we can split them into cohorts based on their subscription plan. Clients using the Basic version of our product might have different needs than our Pro version users. Understanding our customers’ needs can help us tremendously in providing them more value and tailor-made services. We can also look at the churn rate or lifetime value (LTV) of customers based on different cohorts and identify subscription plans that work better. We can then focus on improving our other offerings or pushing the working plans more in our marketing campaigns.

Size-based Cohorts

We can also assign our customer base to cohorts per their size – small, medium, large, enterprise. Comparing how much clients in different groups spend will help us identify where our business generates cash. Small companies and freelancers may struggle with small budgets, so we may look at offering them limited versions of our services at a more affordable price.

Basic and Segmented Cohort Analysis

As types go, we can perform a basic or a segmented version of the Cohort Analysis.

If we are now starting to implement the concept within our business analysis, we split our customer base into cohorts by acquisition month. This will already provide a lot more value to our business. However, we can bring the analytics to a whole new level by introducing segments based on additional characteristics specific to the customers.

Segmented Cohort Analysis gives us much more detailed insights than the basic one. For example, we can compare segmented cohorts’ retention rate and arrive at more actionable intel on our customer base.

Steps of a Cohort Analysis

When we perform this form of behavior analysis, we mostly follow these steps.

  1. Identify the objective – what is the question we want to answer?

The purpose of any analysis is to arrive at actionable insights to improve our business performance, products, user experience, and others. To achieve this, it’s essential to ask the right questions. Otherwise, the whole analysis will yield useless and potentially harmful results.

  1. Which metrics will help answer the question?

To perform a Cohort Analysis, we need events we can measure. Such can be contract start and end date, and the average monthly value of each contract.

The metrics we need to consider are:

  • The characteristics of the cohort –what defines it for our analysis;
  • The inclusion metric – the action customers, perform to land into the particular cohort;
  • The return metric – the item we want to analyze.
  1. Define relevant cohorts

Each person in a cohort needs to share a similar yet unique characteristic. Such traits must be different between cohorts. One that we use commonly is the contract start date. We can then further segment by contract type, subscription plan, customer industry, and others.

  1. Carry out the analysis

We can apply various techniques and software solutions to perform a Cohort Analysis. In Excel, for example, we can use the pivot table functionality coupled with data visualization options like conditional formatting.

Performing Cohort Analysis

When we analyze our company’s performance, we may get tremendous value out of customer groups comparison. Looking at various cohorts’ behavior, we can identify patterns and use this knowledge to improve our operations. This can lead to better identification and assessment of risks and problems.

In business analytics, we usually group customers that perform specific actions in a defined period. We can group people who found our software through social media advertising in a particular month and compare them to those who saw it via google search.

It is critical to look at a defined time frame when we perform Cohort Analysis. Otherwise, if we split customers by behavior only, these are not cohorts but rather segments.

Usually, it’s best to combine two or more cohort analysis variants to gain better insights into improving our business, customer experience, and products.

Benefits of Cohort Analysis

There are many advantages to introducing Cohort Analysis within our business.

The high specificity of the analysis generally results in highly valuable actionable insights that we can employ to improve the business’s performance.

Cohort Analysis also shows how action, or inaction for that matter, affects the business metrics, like customer acquisition, churn rate, and others.

This analytics method allows us to look into cause and effect in customer behavior. For example, we can see whether sign-ups from a specific promotion campaign have a higher churn rate than another campaign.

We can also analyze the customer lifetime value, which is a crucial metric for the business. For example, we can split our customers into cohorts based on acquisition time. That way, we can see how much a customer spends on our products and services over time. We can then further group by the clients’ size and by their industry segment, and then analyze which acquisition channels work best.

Splitting into cohorts allows us to perform a more detailed analysis of how various groups interact with our company and products. This will enable us to help our customers use our product more effectively, thus improving the overall customer experience.


It is essential to note the method has some drawbacks as well. One of the major drawbacks is that the analysis requires that we keep a sizeable detailed dataset within the business, quickly becoming costly and time-consuming.

The Cohort Analysis can also be subject to biases of the analyst performing it. This may result in a loss of objectivity and useless results.

Example Cohort Analysis in Excel

Let’s look at a simple Cohort Analysis example to analyze our retention rate. Here’s a list showing all our customers. It lists their subscription and cancelation date and some details on the plans they have subscribed for.

To facilitate our analysis, first, we need to add some more details. Let’s split our customers into cohorts based on the month we onboarded them. The reason behind this is that the business does one promotional campaign every month. We want to evaluate if some campaigns performed better or worse than the average for the company.

We use the DATE formula to calculate the first of the month. We will also add a column where we will calculate the number of months each client remained subscribed. If they don’t have a cancel date, we assume the client is still active.

Next, we create a pivot table. We will place the Cohort (Group) field in the pivot table’s Rows, the Months field will go into the Columns of the pivot table, and we will add the Count of Customer in the Values. This will show us how many clients onboarded in each cohort have left in the consecutive month of subscription.

We can now copy the data from the pivot and start to make some calculations. We will duplicate the table and move the Subscribed (Total per Cohort) at the front. We will then calculate the remaining after each month by subtracting the matching number of customers who canceled their subscriptions.

In another table on the top, we can show the percentage of customers remaining after each month, which effectively gives us the retention rate up to this month.

To ensure the Cohort names look a bit better, let’s add some custom formatting.

This already gives us a much better overview of our performance. However, adding some basic conditional formatting paints a much better picture.

cohort analysis

It is immediately evident that our “2018 : Jun” cohort performs noticeably worse in terms of customer retention rate.

The next steps will be to look into our promotion campaign in June 2018, figure out what went wrong, and why customers who subscribed during the month have a lower average life cycle.

Even at such a simple cohort analysis, we are getting an insight that we never would’ve gotten by merely analyzing the raw data.


Cohort Analysis is most beneficial when we look into customer behavior and the best way to acquire and retain clients. As an analytics framework, Cohort Analysis gives us a more granular view of our client base. It helps answer the why, when, and how questions of customer behavior. We can provide more value to our customers and improve our company’s retention rate and customers’ lifetime value.

The method allows us to visualize patterns clearly across the life-cycle of our customers. Seeing these patterns helps us to adapt better and serve the ever-changing needs of our customer base.

Please, show your support by sharing this article with colleagues and friends.

Dobromir Dikov


Hi! I am a finance professional with 10+ years of experience in audit, controlling, reporting, financial analysis and modeling. I am excited to delve deep into specifics of various industries, where I can identify the best solutions for clients I work with.

In my spare time, I am into skiing, hiking and running. I am also active on Instagram and YouTube, where I try different ways to express my creative side.

The information and views set out in this publication are those of the author(s) and do not necessarily reflect the official opinion of Magnimetrics. Neither Magnimetrics nor any person acting on their behalf may be held responsible for the use which may be made of the information contained herein. The information in this article is for educational purposes only and should not be treated as professional advice. Magnimetrics and the author of this publication accept no responsibility for any damages or losses sustained as a result of using the information presented in the publication. Some of the content shared above may have been written with the assistance of generative AI. We ask the author(s) to review, fact-check, and correct any generated text. Authors submitting content on Magnimetrics retain their copyright over said content and are responsible for obtaining appropriate licenses for using any copyrighted materials.

You might also like one of the following articles: