How do you track retention?
It's a matrix
We get to flex both arms...table calc AND LoDs
To introduce the CLTV matrix, I’ll share what Ann wrote on the introduction to this week’s #WOW2021…simply because I have no clue what this chart is or how it’s used.
“This week, you’ll be constructing a Customer Lifetime Value Matrix (CLTV). This viz is frequently used in marketing and retail industries to understand the value of each customer. Specifically, the interesting insights come when you scan vertically and see that your initiatives are paying off and customers of the same relative age are spending more money.”
So that’s what it’s used for…but how do you build it? Well you’ve come to the right place!
Let's start with our matrix dimensions - A lesson in Level of Detail
So the first thing we need to do is determine, the first purchase quarter for each customer.
//ACQUISITION QUARTER
DATETRUNC( ‘quarter’, { FIXED [Customer ID]: MIN( [Order Date] ) } )
In english, “For each Customer ID, find the minimum Order Date then truncate the date to the corresponding quarter.”
The next thing to do is calculate the number of customer who made their first purchase during the acquisition quarter from above. This will take an additional level of detail calculations
//CUSTOMERS
{ FIXED [ACQUISITION QUARTER]: COUNTD( [Customer ID] ) }
In english, “For each Acquisition quarter, this many customers made at least 1 order.”
These two fields will serve as the row headers for our final matrix table. Let’s create our first view with these two fields
Now we need to create our column header. We basically need to calculate the number of quarters since the acquisition quarter.
//QUARTERS SINCE BIRTH
DATEDIFF( ‘quarter’, [Acquisition Quarter], DATETRUNC( ‘quarter’, [Order Date] ) )
You may be wondering why I truncated each individual order date to quarter before doing the date difference. Well different database systems may have different quarter thresholds depending on when the day lands. So by truncating order date to the level that I’m calculating, I’ll be sure that my math is correct. Let’s add this to columns
Now we build our measures - time to flex our table calculation muscles
Now this looks good and it satisfies the requirements of the challenge. We could quickly copy this field onto color, do some formatting and tooltips and call it a day! But Ann did give us an additional challenge. Do you see those nulls in the matrix? Let’s fix those holes.
What we need to do is carry forward the previous values where those nulls are. There are certainly many ways to do that; my approach is just one of them. So I’m going to use the LOOKUP() function.
//Customer Lifetime Value
IFNULL( [Avg. Lifetime Value], LOOKUP( [Avg. Lifetime Value], -1 ))
The IFNULL() function translates to a COALESCE function in SQL. So this field is going to look at every Avg Lifetime Value and if it comes across a null value, will return the value on previous row in the partition.
Let’s replace with this field on the text shelf
As you can see it worked great to replace those two holes in our matrix. However, when we look even closer we can see this calculation added duplicative values for each quarter
Well shoot…that’s not good. but then again, it’s working as designed right. But with table calcs, we can fix this too. So let’s do that. Let’s break it down. We want to:
- Remove the last value on each row
- Keep everything in the first row
I’m going to add a couple new table calc functions to the mix
//Row number for values
IF NOT ISNULL( [Customer Lifetime Value] ) then INDEX() end
//Acquisition Index
INDEX()
Okay, let’s talk about these for a second. The first is going to simple count each value in each row where there’s a value. It will stop counting at the first null value.
The second one, I’m going to use in a nested calc to have it count the number of Acquisition Quarters
These two fields will be used to determine which values are duplicates
//Remove Duplicates
[Acquisition Index] >1
AND
[Row number for values] = WINDOW_MAX( [Row number for values] )
Now, every cell in our table will return a TRUE or FALSE based on these conditions. This is where nesting table calcs can be so very powerful. You have the ability to calculate each table calculation at different partitions within a single calculated field. Check this out
So now each of these calculations in the drop down are different table calculations. And I have the ability to calculate them on different levels. Thankfully, all except the [Acquisition Index] can be calculated across the table. However the [Acquisition Index] needs to be calculated down the table.
Now we can put this into our filter card and select to only keep FALSE rows. And you’re all set! Copy the measure on to the color shelf, edit your colors, do some formatting, edit tooltips and publish! You did it!
So that’s it! Feel free to download a copy of my workbook and reverse engineer what I did while reading through this blog. Hopefully everything clicks for you but please reach out and let me know if you have any questions.
And with that I say to you…
GO FORTH AND VIZ!
Check out the interactive visualization below
