March 19, 2021
#WorkoutWednesday 2021 - Week 2: Customer Lifetime Value (CLTV) Matrix
CLTV
,
cohort analysis
,
customer lifetime value
,
first order date
,
level of detail
,
LOD
,
matrix
,
running total
,
table calc
,
table calc filter
,
table calculation
,
tableau
,
Workout Wednesday
No comments
If you like a table calc challenge, this Workout Wednesday is for you. Get Ann's requirements here. On the surface it seems pretty simple:
- Get the first order date for each customer.
- Determine the number of quarters that elapsed since then.
- Calculate the cumulative value of each cohort.
Steps 1 & 2 are pretty simple with an LOD and a calculated field. Step 3 is an aggregate calculation that Ann gives a big hint for and then it's cumulative across the view.
The tricky part comes when you try to get rid of any future quarters. The cumulative calc forces each cell to be filled in. The requirements say that you can't show any quarters after the cohort's latest quarter.
This is where the table calc magic happens. When I create complex calculations, I nearly always split them into multiple calcs because (1) they're easier to debug and (2) I can see my progress along the way and see where I am going wrong.
First, calculate the average lifetime value.
Next, calculate each cohort's cumulative lifetime value.
You should now have a view like this with the marks are filled in across the whole table
We need to figure out how to get rid of the marks when they start to repeat after each cohort's last quarter since birth. This is where the complex table calc comes into play.
Add this calculation to the Filters shelf, choose true and you're done! Click on the image below to view my version on Tableau Public.
Subscribe to:
Post Comments
(
Atom
)
No comments
Post a Comment