Using PowerBI, analyze customer behavior when making a purchase.
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.
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.