Customer Retention Part 2: Period over Period Retention

[amitchandak]

Objective:

The period over Period Retention is a comparison of one period vs another period. These periods are heterogeneous. The definition of the new retained and lost customers is only based on 2 periods of data i.e. like the customer did not visit in the last 3 months but his last visit was 12 months before. Basically, he did not come in 9 months before the above 3-month period.

This Period: Last 3 months rolling

Last/ previous period: Last 9 Months before the above 3 months

For e.g.: Consider the current month-year as September-2020. The current period or THIS PERIOD is 3 months i.e. June 2020 –August 2020 and the LAST PERIOD or previous period will be September 2019-May 2020.

New: Arrival of a customer for the first time in this period (here, the Last 3-month is considered as one period) is a new customer. Here, we won’t get any sales data from this customer for the last period (i.e. 9-month before this period (3-month)).

Lost: If a customer had visited the store last period (in last 12-months other than last 3-month) but did not visit in this period of 3-month, then we call him a Lost customer. This means that in this period there was no sale from this customer. However, there was a sale recorded by the same customer the previous period.

Retain: The customer who visited both periods (i.e. Last Period and This period). There will be sales data of this customer for this period and last Period.

Implementation

We have 4 tables – Sales, Customer, Geography and Item

We also created a Date table in Power BI with Month, Qtr and Year information. Joined all of them.

Abbreviations: 

• This Period: Rolling 3-month Sales
• Last Period: Rolling 9 Before 3 months Sales.
• New:  IF This Period is NOT Blank and Last Period is Blank
• Lost:  IF Last Period is NOT Blank and This Period is Blank
• Retain: IF Last Period is NOT Blank  and This Period is NOT Blank

Using Time Intelligence, we will create the following:  

Rolling 12 = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-12,MONTH))  
Rolling 3 = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-3,MONTH))  
Rolling 9 before three = CALCULATE([Sales],DATESINPERIOD('Date'[Date],eomonth(max('Date'[Date]),-3),-9,MONTH))  

Lost Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Rolling 3]) && not(ISBLANK([Rolling 9 before three])) , 1,BLANK()))
New Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Rolling 9 before three]) && not(ISBLANK([Rolling 3])) , 1,BLANK()))
Retained Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(not(ISBLANK([Rolling 3])) && not(ISBLANK([Rolling 9 before three])) , 1,BLANK()))

Let us know what you think about these measures. Share your thoughts on Customer Retention Part 2.

You can get all my posts at https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403

The file can found at: https://community.powerbi.com/t5/Quick-Measures-Gallery/Customer-Retention-Part-2-Period-over-Period...

[pineville]

Hello Amit,

this worked great for me to calculate the new customers. I can't calculate lost customers correctly. I am using a "reverse sales to calculate lost customer count. 

Reverse Sales= CALCULATE (

    SUM ( 'Sales'[Revenue] ),

    FILTER (

        ALL ( 'Date' ),

        'Date'[DATE] >=MAX( ( 'Date'[DATE] )

    )

))

When I use the lost customers this period with Reverse sales instead of rolling 9 months sales, it does not give me an accurate count. Wondering if you could help identify why. 

[pineville]

running into a problem with the total year calculations, the total is correct but the year is wrong even though I only have 2020 as my year filtered. 

[Sab]

Amit is the best!