Dear Sam, All Members of this community
I’m very interested in calculating (CLTV) form the Cohort analysis showcase available on youtube:
I started creating the needed formulas to calculate (CLTV), If you need more information about the formulas please refer to this (2) links:
1-All Parameters: https://blog.hubspot.com/service/how-to-calculate-customer-lifetime-value
2-For calculate customer life-span: https://www.quora.com/How-do-you-calculate-customer-lifespan
I start the Dax Formulas Needed to calculate (CLTV) as the following:
A)Average Purchase Value
Average Order Size =
AVERAGEX( Sales,
Sales[Line Total] )
B) average purchase frequency rate
Purchase Frequency = DIVIDE([Total Transactions],[Total Customers])
C) average customer’s value
Customer Value = DIVIDE([Average Order Size],[Purchase Frequency],0)
D) average customer’s lifetime span
Please Refer to link 2 (calculate customer lifespan)
First We have to calculate the number of Churn Customers
Churn Customers =
VAR CustomerDimension = VALUES( Customers[Customer Names] )
RETURN
IF( SELECTEDVALUE( 'Cohort Periods'[Period] ) = 0, [Unique Customers],
CALCULATE(
CALCULATE( [Unique Customers],
FILTER( CustomerDimension,
COUNTROWS(
FILTER( 'Cohort Periods',
[Retention Days] > 'Cohort Periods'[Min Days] && [Retention Days] <= 'Cohort Periods'[Max
Days] ) ) > 0 )
)
)
)
And
Second The Retained Customers
Retained Customers =
VAR UniqueCustomers = CALCULATE( [Unique Customers], ALL( 'Cohort Periods'[Period] ) )
VAR CustomerDimension = VALUES( Customers[Customer Names] )
RETURN
UniqueCustomers -
CALCULATE( [Unique Customers],
FILTER( CustomerDimension,
COUNTROWS(
FILTER( 'Cohort Periods',
[Retention Days] > 0 && [Retention Days] <= 'Cohort Periods'[Max Days] ) ) > 0 )
)
Third, The churn rate calculation
Churn Rate% = 1-(DIVIDE([Retained Customers],[Churn Customers]))
Fourth, Average Customer Lifespan
Avg Customer Lifespan = DIVIDE(1,[Churn Rate%],0)
E) Finally, We calculate the customer lifetime value (CLTV)
CLTV = [Customer Value]*[Avg Customer Lifespan]
I need all members to participate in this thread to make more validation for this post and get useful experience, Thanks for Sam’s efforts and contribution to all of us, Please note all the resources available in (November 2018 Learning Summit Demo) of this website.
Thank you in advance
