cancel
Showing results for 
Search instead for 
Did you mean: 
amitchandak

Customer Retention Part 1: Month on Month Retention

Using PowerBI, analyze customer behavior when making a purchase.

 

Objective:

MoM Retention is only focused on Month vs Month. The definition of the new, retained, and lost customers are only based on 2 months of data i.e. This month and Last month.

 

New: Arrival of a customer for the first time this month is a new customer. Here, we won’t get any sales data from this customer for last month.

 

Lost: If a customer had visited the store last month but did not visit the preceding month (i.e. this month) then we call him a Lost customer. This means that in this month there was no sale from this customer. However, there was a sale recorded by the same customer the previous month.

 

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

 

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.

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

 

Joined all tables with sales: 1 to Many (sales)

Screenshot 2020-09-10 18.18.32.png

 

Using Time Intelligence, we will create the following:  

                Abbreviations: MTD – Month to Date, LMTD – Last Month to Date

  • MTD Sales, LMTD Sales. This month sales and last month sales
  • New:  IF MTD is NOT Blank and LMTD is Blank
  • Lost:  IF LMTD is NOT Blank and MTD is Blank
  • Retain: IF LMTD is NOT Blank  and MTD is NOT Blank

 

 

 

MTD = calculate([Sales],datesmtd('Date'[Date]))
LMTD = calculate([Sales],DATESMTD(DATEADD('Date'[Date],-1,MONTH)))	
Lost Customer This Month = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([MTD]) && not(ISBLANK([LMTD])) , 1,BLANK()))
New Customer This Month = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([LMTD]) && not(ISBLANK([MTD])) ,1,BLANK()))
Retained Customer This Month = if(not(ISBLANK([MTD])) && not(ISBLANK([LMTD])) , 1,BLANK())

 

 

Screenshot 2020-09-10 18.22.18.png

 

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

 

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

 

The file is attached to this blog.