🔥Give Excel SUPERPOWERS with Minty Tools for Excel

An Excel add-in to help you save time and enhance your modeling and analysis.

🔥Give Excel SUPERPOWERS with Minty Tools for Excel

An Excel add-in to help you save time and enhance your modeling and analysis.
🔥Give Excel SUPERPOWERS with Minty Tools for Excel

Customer Churn Analysis in Excel

Customer Churn is one of the most essential metrics for any company with a subscription-based model. It shows the rate at which customers are leaving and switching their subscriptions to someone else.

It’s paramount to understand and analyze churn, as even a slight increase in the churn rate can have devastating effects on our business.

Customer Churn Rate

We can also refer to Customer Churn as ‘customer attrition rate.’

Churn is very costly for the business. It results in the need to onboard new customers at a higher rate to compensate. Usually, it’s more expensive to find new prospects and convert them than keep existing clients happy. Analysts estimate that acquiring new customers can cost up to 5x more than retaining existing ones. That’s why a high churn rate can seriously hinder the growth potential of the company.

We should pay attention to our business’s churn rate for a few reasons:

  • Reduces profitability through revenue loss;
  • Results in more significant marketing and selling costs;
  • It’s easier to sell to existing clients than to convert new prospects;
  • Churn rate helps with the calculation of customer lifetime value (LTV);
  • Shows the development of customer retention;
  • It helps identify which customers are the best fit.

Calculate Churn Rate

Customer churn is the proportion of customers we lost over a period.

We calculate it as:

customer churn rate

We can also calculate churn in terms of lost revenue:

This is the gross calculation of revenue churn, but we can also calculate the net rate by taking into account the newly acquired revenue over the period:

The Net Revenue Churn rate can be negative whenever we have gained more new revenue than we have lost from our pre-existing customers. If we lost $1m from our existing clients’ sales of $10m but managed to acquire new clients that brought in $2m, then the calculation would be:

Types of Churn

There are various types of customer churn, and not all are necessarily bad.

Lost Subscriptions

That’s the most common way to lose clients. One reason is a poor customer fit. If we sell to the wrong audience, people will churn fast, and we will lose the resources invested in onboarding them.

If our customers express the need for new features and we neglect to meet their needs, they will end up looking for these features elsewhere.

If our product doesn’t deliver the outcomes and features our customers expect, they will likely churn. Poor onboarding practice and bad UX can quickly push people away.

Switching Providers

We need to constantly look at the offerings of our competitors and at what price they sell. It’s crucial to make sure they don’t provide more value for the same price or similar products and services at a lower cost. This may lead to our customers churning over to them.

That’s why we need to analyze our customers’ requirements constantly. If our competitors are more dynamic than us, they may take clients from us.

Not Renewing Subscriptions

Most subscription-based offerings need renewal once in a while. We may lose customers who have not engaged actively with our products and services.

We need to ensure we have a good relationship with our customer base and keep them engaged and using our product. If they feel they are getting the value, they’re most likely to stick around and renew their subscription when the time comes.

In an online subscription business, non-renewals can account for more than 30% of customer churn.

Good Customer Churn

Sometimes clients churn because our product solved their issue and they completed their project. Such users leave satisfied and happy and are very likely to renew their license when they have another project.

We can work to reduce this churn by expanding our portfolio and offering more products and services.

Downgrade Churn

In some instances, customers may choose to switch down to a lower-tier plan. This may be due to sensitivity towards the cost, or our lower programs offer better value for the price and are therefore more attractive to our client base.

Active vs. Passive Churn

Most churn is active or voluntary, where people choose to leave our offerings. This can be due to many reasons and represents the most considerable portion of lost revenue.

On the other hand, there is involuntary or passive churn, where subscriptions are not renewed for some reason. These can be a failed payment, expired or maxed out credit card, or poor payment system integration.

What is Churn Analysis?

Customer churn is a vital metric for SaaS companies. If we see that our churn rate is creeping up, it can be hard to identify the underlying reasons. This is where churn analysis comes into play. We leverage historical data to attempt to answer various questions:

  1. Which users are leaving?
  2. Why are they leaving?
  3. Who will leave shortly?
  4. How can we prevent this from happening?

A higher churn rate can lead to many problems. It will result in higher Customer Acquisition Cost (CAC) and lower revenue. It costs money to convert potential prospects into paying customers. The more churn we have, the more new clients we need to onboard. This will significantly impact our CAC and drive down profitability, which will work against the business growth.

The analysis process involves evaluating the rate at which the business loses customers. We do this to assess ways we can reduce the churn rate.

It’s the only way to address high churn rates. We can try to compensate by finding more new clients, but this will only delay the issue and the impact of the customer churn.

Understanding the reasons behind customer churn is a crucial part of analyzing ways to reduce the rate.

To properly analyze our business’s churn, we must set the right Key Performance Indicators (KPI’s). We can track various metrics around churn that can help:

  • Number of support requests;
  • Level of customer engagement;
  • Competitor pricing;
  • Percentage of plan upgrades.

Customer Behavior

It is doubtful that our business will only serve one type of customers. Our client base is bound to exhibit different patterns of behavior.

Here we can benefit from applying the Cohort Analysis approach. The key is to separate the customer base into cohorts that make sense and help us analyze customer behavior to anticipate and potentially prevent churn.

The end goal is to find ways of re-engaging customers who are no longer getting enough value out of our products and services.

We can answer important questions by segmenting our clients:

  • Which customers are likely to disengage after three or six months?
  • What are the most popular features of our products?
  • Are there features that only a few people use?
  • Is the churn rate different between low-tier and high-tier customers?

When analyzing customer behavior to understand the underlying drivers of our customer churn rate, we may also take a look at the Pareto Principle (the 80/20 rule). This technique assumes that 80% of results come from 20% of efforts. If we can identify the actions that would reduce our churn, we can operate more cost-effectively.

Cost of Customer Churn

When we look at customer churn, we should keep in mind that its total cost includes both the lost revenue and the marketing costs to replace those clients.

If we can use our analysis to estimate which customers are at risk of churning soon, we still have time to do something to prevent that. This can be a huge potential revenue source for the business. It is always harder and costlier to find and sign a new customer than to retain an already paying one.

It’s essential to get the customer churn analysis right. If our work on this is flawed, we will make retention efforts where these are unnecessary. In some instances, those might be ineffective and can also increase churn by sending the wrong message to some customers.

Additionally, if our analysis fails to identify the clients we are at risk of losing, we won’t be able to take any action to prevent them from churning.

Currently, most churn models rely on historical data and statistical methods like regression analysis to forecast the future development of the churn rate. More often than not, these models show significant deviation from reality, as some of their underlying assumptions are inherently flawed.

Churn analysis doesn’t only look at the overall rate at which customers are leaving us. It goes into detail to understand the underlying causes and to find ways to mitigate the identified issues.

Sign Up on Substack

Benefits of Customer Churn Analysis

Churn Analysis helps us identify areas for improvement for our products and services, customer support, and satisfaction.

It can help identify the strengths and weaknesses of our offerings. Additionally, analyzing churn is fundamental for improving the communication with our customers and their satisfaction and loyalty.

By analyzing customer churn, we can build models to estimate which customers are about to leave us or downgrade and take proactive measures. We can also identify at-risk customers to engage with.

Example Churn Analysis in Excel

If you want to follow along with the example, you can download the free sample dataset from here:


Bring the data in Excel, and it should look something like that.

You don’t need to follow along in Excel to benefit from the example. I will outlay what we can do with this particular dataset, but you can still read on to get the gist of the analysis.

Here are all the fields we have in the sample data. The easiest way to see those is to create a pivot table.

Let’s take a look at some of the fields we will use.

  • customerID – we will use this to ensure all lines are unique and we don’t have multiple lines per customer;
  • gender – we will split the users into cohorts based on gender to see whether churn has any correlation to gender;
  • Partner – we expect customers that are signed as full partners to have less churn, and our analysis will either accept or reject this hypothesis;
  • Tenure – this shows the number of months we billed per each customer. We will use it to split them into cohorts and see how loyalty corresponds to churn;
  • Contract – we will take a look at how the churn rate changes based on the type of contract (month-to-month, 1-year, 2-year);
  • PaperlessBilling – some clients have opted for paperless account management, and we want to see if this has any effect on their churn;
  • MonthlyCharges – we will use the monthly charge per customer to estimate an average MRR (Monthly Recurring Revenue) per client;
  • Churn – this is a Yes/No column showing if the customer has churned or not, which we will convert into 1 for Yes and 0 for No, to facilitate more straightforward analysis.

As part of preparing our data, we will introduce three new columns to the data table, as shown below:

  • Tenure in Years – we calculate this with the formula “=ROUNDUP(tenure/12,0)”, giving us the number of years for the customer. For example, a result of “6” will mean they have been with us for 5 to 6 years;
  • Churn Counter – this is calculated as “=IF(Churn=”Yes”,1,0)” and converts the Churn parameter to a number that we can use to count the churned customers;
  • Total Counter – this is calculated as “=COUNTIF(customerID, current_customerID)” and shows us if the customer is unique in the data set and utilizes an easy way to count total customers.

We can now create our Pivot table. We will need a Calculated Field equal to the Churn Counter divided over the Total Counter we made above.

Let’s look at the Churn Rate and the Average Monthly Charges, or MRR (Monthly Recurring Revenue), in our pivot table. To get this, we will change the Value Field Settings and set Summarize value field by to Average.

We can then start to split our customer base into different cohorts, as we outlined above.

Contract Type

If we look at our churn rate per contract type, we notice that monthly subscriptions are the highest source of churned customers. Clients that sign one or two-year contracts tend to stick with us longer and leave less frequently.

What we can do is aim our marketing efforts to convert more customers to longer-term subscriptions. One way to achieve this is by lowering the subscription cost for one and two-year contracts, as currently, we are not charging month-to-month subscribers at a meaningfully higher rate.


Our marketing efforts work with both women and men with the same effectiveness. Churn rates are almost identical. However, I might dive a bit deeper into the data and look for some outliers, as it seems, on average, we charge women more. This might be due to a single large contract or the customer distribution per plans and services. The difference is not that material to warrant a more detailed analysis at this point.

Overall, it appears our customer base is very balanced based on this parameter, so we don’t need to adjust our strategy.


Some of our customers have signed a full partnership agreement with us, and it is evident that such tend to churn less than regular clients. They also have a higher MRR per customer.

We should strive to improve our conversion rates for regular customers switching over to full partners. Doing so will decrease the overall churn rate and will have a positive impact on revenue and profitability.

Paperless Billing

Customers who haven’t opted for paperless billing are churning much less than those who have. However, they also generate less MRR.

We will need to obtain additional data and ensure the lower MRR is sufficient to generate profit. I would expect that most of the customers who prefer paper bills are older, more traditional citizens. They may sign up for lower-tier subscriptions, which may explain the lower MRR and their higher loyalty.

Tenure in Years

If we take a look at our customers per their tenure, we notice a few things. The first one is that the churn rate percentage drops with tenure, which makes sense. The clients that have stuck longer with us tend to be more loyal, which is why the churn rate drops so significantly.

The second point to note is that the average MRR significantly increases. It may be due to a few reasons, and we need to perform a more detailed analysis to figure out the underlying cause. One possible answer may be that the cost of our services has significantly decreased over the years.


Customer churn is a problem many businesses face, especially in the subscription-based online business space.

It’s critical to analyze our churn rate properly. We can then get a much clearer picture of why we lose customers.

By understanding the underlying reasons that cause customer churn, we can improve the situation by increasing customer engagement and satisfaction.

Show your support by sharing the article with colleagues and friends. Also, don’t forget to download the free Excel model below.

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: