Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

amitchandak
Super User
‎09-17-2020 03:59 AM

Customer Retention Part 2: Period over Period Retention

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

Screenshot 2020-09-10 18.17.56.pngScreenshot 2020-09-10 18.17.59.pngScreenshot 2020-09-10 18.18.01.pngScreenshot 2020-09-10 18.18.09.png

 

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

 

Screenshot 2020-09-10 18.18.32.png

 

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()))

 

Screenshot 2020-09-17 15.59.33.pngScreenshot 2020-09-17 15.59.40.png

 

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...

 

Comments