How to create a CLV (Customer Lifetime Value) dashboard in Power BI

Power BI series

Power BI is more than just a click-and-paste tool; it can generate visuals that help you gain insight.

View a sample dashboard by clicking here.

Table of Contents.

• Understanding parameters for generating Customer Lifetime Value dashboard.
• Start to create ‘Customer Lifetime Value dashboard’ using Power BI (step-by-step).

Let’s start !!

First and foremost, I’d like to point out that some of the formulas in this post may differ from what you’ve seen before because I adapted them based on the raw data. Nonetheless, the meaning is the same. (If you have any suggestions, please let me know)

You can access the formula via this link.

If you want to see it in Thai, you can click on this link. (Created by Dr.Thanachart, my lecturer at NIDA)

Data for use

You can download raw data from the link. This data has been published by Dunnhumby.

The data shown below is an example.

Parameters that you should know before creating a CLV (Customer Lifetime Value) dashboard

• Churn Rate: Churn Rate is a metric for calculating the number of customers who leave a group over a specified time period.
• Customer Lifetime Value (CLV) : CLV is the customer’s overall income throughout the relationship.
• Average Lifespan (ALT) : ALT = 1/ Churn Rate
• Average Gross Margin (AGM) : A good margin will vary substantially in industry, but a 10% net profit margin is considered an average in general terms.
• Average Revenue Per Month (ARPU) : ARPU is the average revenue by a customer. Actually, this word is widely used in the communication business. And in this post, meaning is average spending per member.
• Ticket Size (or Average Order Value, AOV)
• Transaction Per Month

Start with DAX

1. Create a date dimension

• First, create a new table with the name ‘tDimDate’ for the dimension date. This table is generated from the column date in table ‘Supermarket data’.

-- create a new table to get 'tDimDate' table
tDimDate = CALENDAR(minx(‘Supermarket Data’,[Date]),MAXX(‘Supermarket Data’,[Date]))

• On the ‘tDimDate’ table, add new columns with the names ‘Month Number,’ ‘Year’, and ‘YearMonth.’

-- create a new column to get Month Number
MonthNumber = FORMAT(tDimDate[Date], "MM")
-- create a new column to get Year
Year = FORMAT(tDimDate[Date], "YYYY")
-- create a new column to get YearMonth
YearMonth = tDimDate[Year]&tDimDate[MonthNumber]

2. Create DAX formula for CLV dashboard

• All formulas must be entered into the ‘Supermarket Data’ table.
• We must use two parameters to calculate the churn rate: active members and previous active members. As a result, we will create two new measures, as shown below.

-- create new measure to count the number of distinct current members 
ActiveMember = DISTINCTCOUNT('Supermarket Data'[CUST_CODE])
-- create previous member to count the number of distinct pervious members
ActiveMemberPreviousMonth = CALCULATE([ActiveMember], PREVIOUSMONTH(tDimDate[Date]))

• Create a new measure for churn rate.

-- create new measure to calculate churn rate of customers 
Churn Rate =         
   var BeginningCust = [ActiveMemberPreviousMonth]    
   var EndingCust = [ActiveMember]    
   var DiffCust = BeginningCust-EndingCust 
return    
   IF(DIVIDE(DiffCust,BeginningCust) <> blank (),   DIVIDE(DiffCust,BeginningCust),0)
-- We use the if-function because if the calculation is blank, it will be displayed in the zero instant.

• Create a new measure for ARPU.

-- create new measure to calculate average revenue per user
ARPU =
   var DistCountCust = DISTINCTCOUNT('Supermarket Data'[CUST_CODE])
   var TotalRev = SUM('Supermarket Data'[SPEND])
return
   DIVIDE(TotalRev,DistCountCust)

• Create a new measure for ALT.

-- create new measure to calculate average lifespan of a customer
ALT = IF(DIVIDE(1,[Churn Rate]), DIVIDE(1,[Churn Rate]), 0)

• Create a new measure for CLV.
• The Formula of CLV is “CLV = (T x AOV x AGM x ALT) / number of customers for the specific period”. Moreover, I have modified it to “CLV = ARPU * AGM * ALT”. By getting AGM equal to 10%

-- create new measure to calculate customer lifetime value
CLV = [ARPU]*0.1*[ALT]

3.To analyze data from a sales perspective.

• On the ‘Supermarket Data’ table, add 3 new measures with the names ‘Total Spending’, ‘Transactions Per Month’, and ‘Ticket Size’.

Total Spending = sum('Supermarket Data'[SPEND])
TransPerMth = DIVIDE(DISTINCTCOUNT('Supermarket Data'[BASKET_ID]),DISTINCTCOUNT(tDimDate[YearMonth]))
AOV_TS = DIVIDE(sum('Supermarket Data'[SPEND]),DISTINCTCOUNT('Supermarket Data'[BASKET_ID]))

Let’s take a look at the sample dashboard.

See more by clicking here.

Conclusions

• We can use DAX in Power BI to see some insight such as Churn Rate, ARPR CLV, and so on.
• Furthermore, Power BI can create more visually appealing graphics.
• PS: This is the first draft of the dashboard. It will be improved in order to get a better dashboard the next time.
• You can, however, use this pattern dashboard for your work.

Other analytics dashboards using Power BI.

• RFM Analysis for Customer Segmentation with Power BI
• Create a Simple Dashboard for Next Purchase Prediction with Power BI

Thanks.

• BADS7105 CRM Customer Relationship Management Analytics and Intelligence (M.S. (Business Analytics and Data Science), (Data Science: DS), NIDA, Thailand) and Dr.Thanachart Ritbumroong (He is a very cool lecturer.)
• Many thanks to Power BI for letting us improve our skills.
• Please let me know if you require a Power BI file.

See you in the next post.

Knowledge Is Endless To Learn. Let’s study together.

Tubsamon Dusitsopittawong

I’m available on LinkedIn.