Averaging Percentages - Semi-Annual Churn Rate Expressed as a Monthly Churn Rate

[penacho]

This business calculates churn rate using the following formulas:

Customers Serviced = Starting Customers + New Customers

Churn Rate = Customers Lost / Customers Serviced

This works well for calculating the churn rate of the period. We generally are calculating this on a monthly basis, but would like the team to be held to a standard across a 6-month period (to smooth out variance of a particular month). If we apply the above formulas to 6-months worth of data we will get a larger % than the monthly period, which is accurate, but not helpful. We need to understand what the average monthly churn rate was during the 6-month period so we can set that as the target and report on how the team is doing every month using the monthly number.

Example:

Month 1
Starting Customers (1) = 1126
New Customers (1) = 85
Customers Lost (1) = 18
Customers Serviced (1) = 1211
Churn Rate (1) = 1.49%

Month 2
Starting Customers (2) = 1193
New Customers (2) = 25
Customers Lost (2) = 11
Customers Serviced (2) = 1218
Churn Rate (2) = 0.90%

Month 3
Starting Customers (3) = 1207
New Customers (3) = 41
Customers Lost (3) = 20
Customers Serviced (3) = 1248
Churn Rate (3) = 1.60%

Month 4
Starting Customers (4) = 1228
New Customers (4) = 30
Customers Lost (4) = 13
Customers Serviced (4) = 1258
Churn Rate (4) = 1.03%

Month 5
Starting Customers (5) = 1245
New Customers (5) = 36
Customers Lost (5) = 24
Customers Serviced (5) = 1281
Churn Rate (5) = 1.87%

Month 6
Starting Customers (6) = 1257
New Customers (6) = 46
Customers Lost (6) = 23
Customers Serviced (6) = 1303
Churn Rate (6) = 1.77%

Semi-Annual
Starting Customers = 1126
New Customers = 263
Customers Lost = 109
Customers Serviced = 1389
Churn Rate = 7.85%

How can the 7.85% value be expressed in a monthly way so when the team is given their performance for the month at the end of the month they know how they're trending towards the target? Is a simple average correct, or is that going to cause an issue? Is that not considering the severity one way or another of each month? We're not necessairly seeking the average monthly rate, but more interested in the performance over the 6-months expressed in a monthly size.

Appreciate any help and let me know if there is anything I can clarify!

 

[lev888]

This business calculates churn rate using the following formulas:

Customers Serviced = Starting Customers + New Customers

Churn Rate = Customers Lost / Customers Serviced

This works well for calculating the churn rate of the period. We generally are calculating this on a monthly basis, but would like the team to be held to a standard across a 6-month period (to smooth out variance of a particular month). If we apply the above formulas to 6-months worth of data we will get a larger % than the monthly period, which is accurate, but not helpful. We need to understand what the average monthly churn rate was during the 6-month period so we can set that as the target and report on how the team is doing every month using the monthly number.

Example:

Month 1
Starting Customers (1) = 1126
New Customers (1) = 85
Customers Lost (1) = 18
Customers Serviced (1) = 1211
Churn Rate (1) = 1.49%

Month 2
Starting Customers (2) = 1193
New Customers (2) = 25
Customers Lost (2) = 11
Customers Serviced (2) = 1218
Churn Rate (2) = 0.90%

Month 3
Starting Customers (3) = 1207
New Customers (3) = 41
Customers Lost (3) = 20
Customers Serviced (3) = 1248
Churn Rate (3) = 1.60%

Month 4
Starting Customers (4) = 1228
New Customers (4) = 30
Customers Lost (4) = 13
Customers Serviced (4) = 1258
Churn Rate (4) = 1.03%

Month 5
Starting Customers (5) = 1245
New Customers (5) = 36
Customers Lost (5) = 24
Customers Serviced (5) = 1281
Churn Rate (5) = 1.87%

Month 6
Starting Customers (6) = 1257
New Customers (6) = 46
Customers Lost (6) = 23
Customers Serviced (6) = 1303
Churn Rate (6) = 1.77%

Semi-Annual
Starting Customers = 1126
New Customers = 263
Customers Lost = 109
Customers Serviced = 1389
Churn Rate = 7.85%

How can the 7.85% value be expressed in a monthly way so when the team is given their performance for the month at the end of the month they know how they're trending towards the target? Is a simple average correct, or is that going to cause an issue? Is that not considering the severity one way or another of each month? We're not necessairly seeking the average monthly rate, but more interested in the performance over the 6-months expressed in a monthly size.

Appreciate any help and let me know if there is anything I can clarify!

Averaging percentages is not a good idea. I would divide the semi-annual churn rate by 6 instead, to get the monthly performance target.

 

[penacho]

Right, I know not accounting for the size of each individual percentage can get people into trouble!

So in this example, we'd get 1.31% by dividing the semi-annual by 6 whereas if we took the simple average of each monthly churn it'd be 1.44%. Why are we seeing the difference here? What's the mathematical reasoning / explanation?

What concerns me is if we apply 1.31% to each individual month then we end up with 98 customers lost, but in actuality 109 customers were lost. If we use 1.44% and apply to each individual month then we end up with 109 customers lost, which aligns.

 

[lev888]

Right, I know not accounting for the size of each individual percentage can get people into trouble!

So in this example, we'd get 1.31% by dividing the semi-annual by 6 whereas if we took the simple average of each monthly churn it'd be 1.44%. Why are we seeing the difference here? What's the mathematical reasoning / explanation?

What concerns me is if we apply 1.31% to each individual month then we end up with 98 customers lost, but in actuality 109 customers were lost. If we use 1.44% and apply to each individual month then we end up with 109 customers lost, which aligns.

Why you should be careful when averaging percentages — Roberto Reif

A common mistake I often observe in data analysis is the averaging of percentages. To explain this concept let’s examine a simple example. Suppose we conduct a survey on whether people like or do not like chocolate. We discover that 90% of children like chocolate; however, only

www.robertoreif.com

 

[lev888]

Right, I know not accounting for the size of each individual percentage can get people into trouble!

So in this example, we'd get 1.31% by dividing the semi-annual by 6 whereas if we took the simple average of each monthly churn it'd be 1.44%. Why are we seeing the difference here? What's the mathematical reasoning / explanation?

What concerns me is if we apply 1.31% to each individual month then we end up with 98 customers lost, but in actuality 109 customers were lost. If we use 1.44% and apply to each individual month then we end up with 109 customers lost, which aligns.

You pointed out yourself that averaging percentages does not account for the size of whatever the basis for the percentage is.
E.g. you have 100 customers and you gain 20. That's an increase of 20%. Next month you lose 20 customers - a decrease of 16.7%. The average is (20-16.7)/2 = 1.65%. In reality, nothing changed.

 

[penacho]

Yes, I have read Robert Reif's post before, I know it's an issue! Yet, when we're looking at the application of the data 1.31% gives us the incorrect result and 1.44% is giving us the correct result. Something isn't making sense. Is our sample size actually 1-month here so averaging the percentages is OK since the sample size is equal? Or what's going on?

 

[lev888]

Yes, I have read Robert Reif's post before, I know it's an issue! Yet, when we're looking at the application of the data 1.31% gives us the incorrect result and 1.44% is giving us the correct result. Something isn't making sense. Is our sample size actually 1-month here so averaging the percentages is OK since the sample size is equal? Or what's going on?

The size is not 1 month. The size that matters is the number of customers you are dividing by. See my gain 20 lose 20 example. First you are dividing by 100, then by 120. So the magnitude of gain % will always by greater than the magnitude of a corresponding loss %.

 

[penacho]

So how do we reconcile 1.31% not delivering the correct value for customers lost? Becuase that answer isn't right either.

 

[lev888]

So how do we reconcile 1.31% not delivering the correct value for customers lost? Becuase that answer isn't right either.

1.44% did not produce the right result - I'm getting 108.58, which is not 109. Make a spreadsheet and change some numbers - your total based on the average % will move away from the actual total by more than 1.00. E.g. I changed the starting numbers of month 1 and 2 from 1126 and 1193 to 1333 and 1444. Applying the average rate resulted in 110.25 total lost customers, even though it should still be 109. Try it.
I don't have time to look into why the average % produces "better" results than the 1/6th of the semi-annual rate in your customer calculation exercise. But take another look at my example. When the total number of customers lost is 0, which % makes more sense as the monthly target, 0% or 1.65%?