Customer Retention Part 1: Month on Month Retention

[amitchandak]

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.

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

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

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.

CustomerThisMonthVs LastMonth.pbix