This is the second in a series of delicious analytics “recipes” that will make any data-driven professional’s mouth water.
Recipe: The Churn Rate Margarita
For SaaS businesses, churn rate is a critical metric, right up there with revenue. Your churn rate impacts your customer lifetime value, the profitability of your marketing channels, and the long-term sustainability of your entire business. It also affects your ability to attract funding: whether you’re looking for VC or public market funding, expect churn to be one of the primary metrics investors look at. In this Churn Rate Margarita recipe, you’ll learn how to calculate churn for your SaaS business.
There are multiple ways of expressing churn, depending on the specific question you’re asking. Let’s take a quick look at your options.
Prep Work
Customer vs. Revenue Churn
Churn is a way of expressing recurring customer attrition. If you had 100 customers at the beginning of the month and during the month 5 of them cancelled their subscriptions, that equates to a 5% customer churn rate (5/100). Here is a simple formula for calculating customer churn:
In the same scenario, let’s say that you were earning $10,000 / month from those 100 customers. But the five customers you lost were actually some of your best customers and were paying more than your average customers. If those 5 customers were worth 1,000 of revenue, then your revenue churn rate is 10% ($1,000 / $10,000). Here is a simple formula for calculating revenue churn:
Viewing churn on a customer count basis is valuable as an indication of customer satisfaction with your product or service, whereas viewing churn on a revenue basis gives a better indication of its impact on your financials. These two ways of measuring churn can reveal very different information, so we will be calculating both in this recipe. As a general rule, we recommend that SaaS companies do the same.
Choose your Measurement Period
Choose a measurement period that makes sense for your business. If you typically have less than 10 customers cancel per month, don’t try to measure daily churn. Looking at churn on a monthly or quarterly basis would make more sense in this case. You want to make sure that you have a reasonable sample size of cancellations so that you can easily visualize and draw conclusions from your data. In this recipe, we’ll be doing monthly churn calculations.
Ingredients 
Transaction Data
Within your transaction data, make sure that you have the following:
- Transaction Date
- Amount
- Customer ID
- Event Type
Basic Preparation: The Classic Margarita
Step 1: Gather Your Ingredients
Churn is fairly tricky to calculate, and most of the difficulty lies in gathering the data you need from your transactions table. In raw form, your data will look something like this:
This transaction data lives either in your application or your billing platform. If it’s in your application, you’ll need to grab an engineer and ask them to dump the data to a spreadsheet for you. If it lives in a billing platform, you should be able to export it on your own.
Step 2: Measure Your Ingredients
Once you have all the data in the format you need, you can start setting up your spreadsheet for your customer churn rate calculation. It should look like this:
Step 3: Count Your Monthly Cancellations
To get the total count of cancellations for each month from your transactions table, you will need to use the following “countif” summary formula:
=countifs(transactions!$C$2:$C$200,”>=”&$A2,transactions!$C$2:$C$200,”<="&$B2,transactions!$D$2:$D$200,"cancellation")
Step 4: Count Your Monthly Renewals
To get the total count of renewals for each month from your transactions table, you will need to use the following “countif” summary formula:
=countifs(transactions!$C$2:$C$200,”>=”&$A2,transactions!$C$2:$C$200,”<="&$B2,transactions!$D$2:$D$200,"renewal")
Step 5: Calculate Your Customer Churn Rate
You can now use this simple formula to calculate your customer churn rate by month:
=C2/(D2+C2)
Step 6: Calculate Your Revenue Churn Rate
But don’t stop there! Calculating customer churn rate is just the first step—you need to calculate revenue churn in order to control for the size of customers you’re losing. Rather than walking you through that process step-by-step, here’s a pre-mixed spreadsheet. Save a copy and get to work!
Serve It Up
How can you tell if your churn rate is good? Churn rates vary widely from industry to industry, and within that, different business models can also have different churn rates. It also depends on how your particular business defines and measures churn. This is why it is so important to shake up your own churn margarita: until you know the baseline for your business, you won’t be able to start improving it in targeted ways.
How can you tell if your churn rate is good? http://ow.ly/Ax56e
The Master Mixologist’s Margarita
If you’re interested in trying other variations of this cocktail, it’s worth adding some top-shelf-worthy mixology tools to your kitchen. SaaS businesses who want to analyze their churn on an ongoing basis and understand the drivers of that churn use RJMetrics. We connect directly to your source data and analyze your churn rate automatically.
Calculating your churn rate is doable without RJMetrics, but it’s complicated and time-consuming to update the necessary spreadsheets every month. With RJMetrics, you set up your churn calculations just one time, and they automatically update. Even better, you can easily slice your churn data in different ways and run cohort analyses on top of it to better understand your customers.
Check out the video below to see just how easy it is:
