topic Re: Negative churn rate?? in DAX Commands and Tips

Negative churn rate??

Anonymous — Sat, 01 Oct 2022 19:59:36 GMT

Hi there. I am in the process of building a Customer Lifetime Value for my organisation. I started by calculating the Churn Rate {(customers at the beginning of the period - Customers at the end of the period)/ Customers at the beginning of the period}. My problem is that in other periods the customers at the end of the period are more than customers at the beginning resulting in a negative churn rate e.g (100-120)/100= -20%. I also use this churn rate to calculate the Average Life Time using the following formula:

1/ Churn rate and CLV = Average Revenue Per Customer x Gross Profit x ALT. The above logic will result in a negative CLV. Is this normal and if so, what could be the explanation?

Summarised Questions:

1. Can the churn rate be negative?

2. Can the CLV be negative?

3. What is the explanation for the negative churn?

Re: Negative churn rate??

Greg_Deckler — Sat, 01 Oct 2022 22:22:47 GMT

Anonymous So customer churn rate should not be negative as that violates the premise of the metric. What you need to do is to calculate the number of customers at the beginning of a period and then you need to see how many of those customers you have at the end of the period. If you do your calculation that way, then your churn rate can never be negative.

Re: Negative churn rate??

Anonymous — Sun, 02 Oct 2022 18:25:26 GMT

Greg_Deckler Thanks for the insights. I calculated the churn by doing the following:

(Customers at the beginning of the period - Customers at the end of the period)/ Customers at the beginning of the period

I am getting a negative churn because the number of customers at the end of the period was more meaning there was growth in the number of customers. The customers didn't churn. My confusion is on how best to handle such a scenario in CLV calculation. Should I use revenue churn instead of customer churn?

Thank you in advance!

Re: Negative churn rate??

Greg_Deckler — Sun, 02 Oct 2022 18:40:05 GMT

Anonymous But that's not customer churn. You have to get the ID's of your customers at the beginning of the period and the ID's of your customers at the end of your period and then do:

VAR __ChurnedCustomers = COUNTROWS(EXCEPT( CustomersAtBegin, CustomersAtEnd))

This is your numerator. Then you don't have to worry about negative customer churn numbers in your CLV calculation.

Re: Negative churn rate??

Anonymous — Mon, 03 Oct 2022 10:15:26 GMT

Greg_Deckler My problem is that the Customers at the end are higher than those at the Beginning. This is my formula:

Re: Negative churn rate??

Greg_Deckler — Mon, 03 Oct 2022 12:08:00 GMT

Anonymous But that is not churn rate. Churn rate is the number of customers at the beginning of the period minus the count of those same customers that you have at the end of period divided by the number of customers you had at the beginning of the period. If you post sample data that includes customer identifiers, I can get you the correct formula but it would look something like:

Churn = VAR __Table = SUMMARIZE('Table',[CustomerKey],"__Prev",[ActiveMemberPreviousMonth],"__Current",[ActiveMember]) VAR __Prev = DISTINCT(SELECTCOLUMNS(FILTER(__Table,[__Prev] = 1),"__Cust",[CustomerKey])) VAR __Current = DISTINCT(SELECTCOLUMNS(FILTER(__Table,[__Current] = 1),"__Cust",[CustomerKey])) VAR __Lost = COUNTROWS(EXCEPT(__Prev, __Current)) RETURN DIVIDE(__Lost, COUNTROWS(__Prev), 0)

Re: Negative churn rate??

Anonymous — Mon, 03 Oct 2022 12:39:18 GMT

Re: Negative churn rate??

Anonymous — Tue, 04 Oct 2022 06:23:56 GMT

Greg_Deckler

Re: Negative churn rate??

Greg_Deckler — Tue, 04 Oct 2022 06:58:17 GMT

Anonymous I perfectly understand that you have more customers in a subsequent month versus a previous month. What you seem to fail to understand is that has zero bearing on customer churn rate. It means absolutely, 100% nothing. Read the definition of customer churn rate:

Customer Churn Rate | KPI example | Geckoboard

Customer churn rate is the % of customers that cancel/don't renew their subscription/whatever. It has absolutely nothing to do with adding customers. Hence, you are complaining about negative churn rates when you are doing the calculation 100% incorrect.

I don't think there is anything else I can say on the matter without blowing a gasket.