Customer churn is the measure of how many customers you lose in a given period. It’s an important metric in subscription-based businesses, although it’s applicable to other revenue models as well. If your growth rate (the rate at which you are adding new customers) is higher than your churn rate, then your customer base is growing. If not, you’re losing customers faster than you can add them, and something needs to change.
Figure below shows a churn calculation for a company with recurring monthly revenue. You need to know the number of customers at the beginning and end of the month and the number of new customers in that month.
Subscribers Lost
=C2+C3-C4
Churn Rate
=C6/C2
To determine the number of customers lost during the month, the number of new customers is added to the number of customers at the beginning of the month. Next, the number of customers at the end of the month is subtracted from that total. Finally, the number of customers lost during the month is divided by the number of customers at the beginning of the month to get the churn rate.
In this example, the business has a churn rate of 9.21%. It is adding more customers than it is losing, so that churn rate may not be seen as a problem. However, if the churn rate is higher than expected, the company may want to investigate why it’s losing customers and change its pricing, product features, or some other aspect of its business.
EXCEL : Calculating annual churn rate
If a business has monthly recurring revenue, it means that customers sign up and pay for one month at a time. For those companies, it makes sense to calculate the churn rate on a monthly basis. Any new customers during the month will not churn in the same month because they’ve already paid for the month.
A typical magazine, however, signs up subscribers for an annual subscription. A meaningful churn rate calculation for them would be an annual churn rate. If a business wants to calculate a churn rate for a longer period than its recurring revenue model, such as calculating an annual churn for a business with monthly subscribers, the formula changes slightly.
Figure below shows an annual churn rate calculation.
Annual churn rate: =C6/AVERAGE(C2,C4)
The number of lost subscribers is divided by the average of beginning and ending subscribers. Because the period of the churn rate is different than the period of the recurring revenue, some of those 614 new subscribers canceled their subscriptions within the year, albeit in a later month than they first subscribed.
EXCEL: Calculating average customer lifetime value
Customer lifetime value (CLV) is a calculation that estimates the gross margin contributed by
one customer over that customer’s life. The churn rate calculated in Figure 15.7 is a component
of CLV.
Figure 15.9 shows a calculation of CLV using the churn rate previously calculated. The first
step is to calculate the average gross margin per customer.
Gross Margin
=F2-F3
Average Customer Margin
=F4/AVERAGE(C2,C4)
Customer Lifetime Value
=F6/C7
To calculate CLV, follow these steps:
1. Calculate the gross margin.
2. Calculate the average customer margin by dividing the gross margin by the
average number of customers for the month. Because the gross margin was
earned over the month, you have to divide by the average number of customers
instead of either the beginning or ending customer count.
3. Calculate the CLV by dividing the average customer margin by the churn rate.
In this example, each customer will contribute an estimated P828.97 over their lifetime.
No comments:
Post a Comment