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 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 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' tabletDimDate = 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 membersActiveMemberPreviousMonth = 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 userARPU =
var DistCountCust = DISTINCTCOUNT('Supermarket Data'[CUST_CODE])
var TotalRev = SUM('Supermarket Data'[SPEND])return
- Create a new measure for ALT.
-- create new measure to calculate average lifespan of a customerALT = 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 with 10%
-- create new measure to calculate customer lifetime valueCLV = IF([ARPU]*0.1*[ALT], [ARPU]*0.1*[ALT],0)
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.
- 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.
- 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.
Data P (Tubsamon Dusitsopittawong)