10 Replies Latest reply on Nov 6, 2017 6:31 PM by Vandana Samtani

# Churn Rate Calculation

Hi Guys, I have a data set and would like to calculate the churn rate based on the logic below -

On my dashboard I have a date range filter default to 30 days and the users are looking for churn rate to change if the date range changes.

Let's take an example below. From the above logic

Sep 11 to Oct 11 is the 30 day rolling window - I am excluding the last few days from Oct 20 - Oct 11 because these could be trial users which is already excluded in my query.

on Sep 11th - How many total subs as of Sep 11? So business is looking for total subs from July 31st to Sep 11 in this bucket. e.g. 500

Cancellation - Total Cancellations between Sep 11 to Oct 11 in this bucket. e.g.50

divide the cancelltions/total subs as of Sep 11

50/500 = 10%

the final data set should be

Churn Date          Churn Percentage

10/11/2017                   10%

10/12/2017                    9%     For this we should look data from Oct 12 to Sep 12 - How many subs as of Oct 12? How many cancellations between Sep 12th and Oct 12? then the calculations.

10/13/2017                    15%   For this we should look data from Oct 13 to Sep 13 - How many subs as of Oct 13? How many cancellations between Sep 13th and Oct 13? then the calculations.

• ###### 1. Re: Churn Rate Calculation

Hi Vandana

I am not sure I'm on the right track or not, but this might help?

Moving_sum last 30 data points including today

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Churn Rate Calculation

Shinichiro: I guess the subscriptions should be cumulative till date since Vandana mentioned as of date.

1 of 1 people found this helpful
• ###### 3. Re: Churn Rate Calculation

Hi Hari,

Thank you for the comment.

But I am not sure what you pointed.

Mine is already moving sum of 30 "points" with assuming all the dates has data (= previous 29 days + today) .

Thanks,.

Shin

1 of 1 people found this helpful
• ###### 4. Re: Churn Rate Calculation

I meant that Subscriptions should be as of the date and not the last 30 day subscriptions. Cancellations are for the last 30 days.

• ###### 5. Re: Churn Rate Calculation

Ah, I understood.

Shiin

• ###### 6. Re: Churn Rate Calculation

Hi Shin, Thanks for your help. I am also learning this process so I apologize in advance for the confusion. I like the approach you suggested. While Talking to  the business in detail here is some more info on this  -

What was the subs on Day 1?

What was the cancels on day 30?

Divide the cancels on day 30 by the subs on day 1.

This number should be called “Rolling 30 day churn”

If we take an example for the month of Oct -

As of Oct 1  - End of period subscribers.

Cancellations  - From Oct 1 to Oct 31. ---This will be a rolling 30 day.

This is the pattern the business is looking for -

So the EOP subs should besame through out the month until Oct 31st.

Nov 1st we will have a new number 7000 which is the EOP subs as of Oct 31st

and total cancellations start from Nov 1st - Nov 30th .

If the user wants to go back and check churn rates in Sep then he should be able to change the date range and look at the churn rate in Sep.

Let me know if you have any ideas.

Vandana.

• ###### 7. Re: Churn Rate Calculation

Hi Vandana,

I think this works.

One thing not clear is

Thanks,

Shin

1 of 1 people found this helpful
• ###### 8. Re: Churn Rate Calculation

Hi Shin, Thank you very much for your help. Your suggestion worked. Thanks again, Vandana

• ###### 9. Re: Churn Rate Calculation

Hi Vandana

You are very welcome.

Shin

• ###### 10. Re: Churn Rate Calculation

Hi Shin, Just posted a timing calculation that i need help with. Can you please help? Thanks, Vandana