Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

 

Comments

Hi @amitchandak

 

What if I want to have the number of new customer ? 

I tried Count("New Customer This Month") but the variable is not a column so it didn't work. 

 

Thanks,

 

Don 

 

 

Anonymous

Very powerful practice but instead on retain number "1" you can use True or false it's better

 

 

thanks, 

[Deleted Post]

 

[Deleted Post]

 

[Deleted Post]

Good day The example helped me a lot, now I am trying to get the sum of the clients in the total.

 

Retained Customer This Month = if(not(ISBLANK([MTD])) && not(ISBLANK([LMTD])) , 1,BLANK())

cmejia_0-1654191585262.png

 

Hi Amit, 

 

I am new to Power Bi and DAX, i am looking for the similar parameters to get the cout of user who are using our application based on the logs from Application insights to create a trend chart for last 1year.

We are pulling the below columns from Application Insights. 

 

user_AuthenticatedId,user_Id,viewDate ,viewMonth ,operation_Name also we have a dimdate table.

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.

Hi Amit @amitchandak,

I'm trying to work on a Customer Churn Report with most of the principles you've applied here.

However I ran into an issue while trying to get the values lost for 'Customers Lost'.

When I display the values of 'Retained Customers' and 'New Customers' in both card and table visual they show the right value whereas values for 'Customers Lost' stays blank when I select a particular Month & Year from the slicer.

I've attached a link to download the pbix file which has the data and dax functions that you've specified in this article, Also Sales TM - MTD & Sales LM - LMTD.

 

Link for the pbix file : https://drive.google.com/file/d/1jdP3fIXHAvkJI1yegcVoxGgkwcqUiyjx/view?usp=drive_link

Screenshot (34).pngScreenshot (35).png