Customer Retention Rate


#1

Dear Sam
For the (Customer Retention And New Customers Calculations) showcases how can we calculate the customer retention rate as the following:

Customer Retention Rate = ((E-N)/S)*100

Where:

Number of the customer at the end of a period (E)
Number of new customers acquired during that period (N)
Number of customers at the start of that period (S)

Could you please share with us how can we model it? and If Possible how to calculate the Customer life time value (LTV)?


#2

Can you show me the specific example you’re working on? Where you are at with it?

If you are looking for a specific video tutorial I recommend requesting it through the ‘Content Ideas’ section and then I’ll review it and see when I can get this into the schedule

Due to the length of time it takes to work up new material I have to be practical around what’s possible to produce.

To review details around what best for the support forum. See here - How to ask support questions on the forum

Thanks
Sam


#3

Ok Sam thank you so much , here is the where I got this formula : http://customersthatstick.com/blog/customer-loyalty/how-to-calculate-customer-retention-rate/ , I’m putting
The Topic In this section because the Show Case Is already available and Not New (New Customer and customer churn Showcase), In the future I’ll consider this and Sorry if I put the topic in the wrong section.


#4

Certainly if you are working on something live and you need some help in a certain situation like with you DAX formula, your model etc, then always post that.

Training materials etc. just take a lot of time to create from scratch in some cases and I have a list of about 200 things that I could cover. It’s just about prioritizing time.

Obviously the support forum is here though to support development work and direct members to where to learn and master everything to do with Power BI.

Chrs
Sam


#5

Ok I’ll try to work with it by myself but Can I get a tip from You On How to calculate it based on any of related Videos (Customer Churn and/or attrition) ?

I mean from Lost , New and retained customers that were showed in your show cases?


#6

Currently the best material to work with this is the below


#7

Ok, Sam , I started the work now, please take look at the following screen-shot:

based on the formula in my last post, Just to remember:

CE [Total Customers] , CN[New Customers] and I created a new measure called [last Period New Customers] = CALCULATE([New Customers],DATEADD(Dates[Date],-1,MONTH)
Where [last Period New Customers] is CS, But I found the Rentintion Rate is Very High starting from Sep 2016 and later months , based on what I did do you think the Calculated Rentention Rate is correct ? if not please correct it.


#8

I’m asking Also for (customer revenue lost) measure, how it calculated?

I understand all the values In the Customer Revenue Lost Until Sep 2017 = 9,003 $, the next amounts 69797 , 5196 I didn’t really how it is calculated?


#9

Well it doesn’t look that correct as your % are in the 100s

The logic to me looks like it need to be

Steady customers / Total customers.

That to me would suggest a simple way to evaluate who you would consider retained.

Why not just use that?


#10

Don’t you already have it there?

What formula are you actually using right now?

This is the formula from the new customer analysis download file

Customer Revenue Lost = 
VAR CustomersPurchased = CALCULATETABLE( VALUES( Sales[Customer ID] ), 
                            FILTER( ALL( Dates ),
                                Dates[Date] >  MIN( Dates[Date] ) - 365 && 
                                Dates[Date] <=  MIN( Dates[Date] ) - [Churn Time Frame Value] ) )
VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ), 
                        FILTER( ALL( Dates ),
                            Dates[Date] >  MAX( Dates[Date] ) - [Churn Time Frame Value] &&
                            Dates[Date] <= MAX( Dates[Date] ) ) )
RETURN
CALCULATE( 
    CALCULATE( [Total Sales],
    DATESBETWEEN( Dates[Date], MIN( Dates[Date] ) - 365, MIN( Dates[Date] ) - [Churn Time Frame Value] ) ) ,
        EXCEPT( CustomersPurchased, PriorCustomers ) ) * -1

Are you using logic similar to this?


#11

Yes the same


#12

Ok Now I think what you said is the right thing divide the steady by total customers , so it’s over now thank you for that , Now I need Just to understand the concept of Customer Lost Revenue if you don’t mind.


#13

Are you just looking to understand the logic?

If so,

What it’s doing is saying how much that particular customer purchased in the proceeding 365 days (you could put this to any time frame obviously)

As they are lost and didn’t purchase anything, you need to look backwards and evaluate what they did buy and then use that as the value you considered lost.

This certainly is open to interpretation and could be situation dependent but is the logic that I used in this example.


#14

I understand but Just please refer to the Picture for that particular customer , I understand everything Until the line with Amount (9,003) , the other two amounts line (6,979) and (5,196) still not understand how it calculated.


#15

As per above. It’s the same logic for every row.