Customer Retention Part 5: Year on Year Retention

[amitchandak]

Objective:

YoY Retention is only focused on Year vs Year. The definition of the new, retained, and lost customers are only based on 2 Years of data i.e. This Year and Last Year.

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

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

Retain: The customer who visited both Years(i.e. Last Year and This Year). 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.

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: YTD – Year to Date, LYTD – Last Year to Date

• YTD Sales, LYTD Sales. This Year sales and last Year sales
• New:  IF YTD is NOT Blank and LYTD is Blank
• Lost:  IF LYTD is NOT Blank and YTD is Blank
• Retain: IF LYTD is NOT Blank  and YTD is NOT Blank

YTD = calculate([Sales],datesytd('Date'[Date]))
LYTD = calculate([Sales],DATESYTD(DATEADD('Date'[Date],-1,MONTH)))	
Lost Customer This Month = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([YTD]) && not(ISBLANK([LYTD])) , 1,BLANK()))
New Customer This Month = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([LYTD]) && not(ISBLANK([YTD])) ,1,BLANK()))
Retained Customer This Month = if(not(ISBLANK([YTD])) && not(ISBLANK([LYTD])) , 1,BLANK())

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

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