LOD #10 - Return Purchase by Cohort - #Tableau Level of Detail Expressions #Calculations

for use case 10 we are going to revisit cohort analysis which we introduced in part one of this series the next use case is about cohort analysis let's start by understanding the term cohort and what it means is simply a group of people this definition from appq covers good ground of cohort analysis the key components here are first it is a type of Behavioral analytics and we use it to track behavior of groups of people because we want to understand their actions one of the main reasons we do this in business is because we want to reduce churn is a term we use when customers stop doing business with us for example customers that don't come back for additional goods and services are customers that terminate their subscriptions we want to understand why they're unhappy and improve what we can from our business operations so that we can reduce churn and the reverse of churn is what we call retention we want to retain our customers this time around let's try to identify how long it took for our customers to come back and purchase again from us was it less than 6 months did they wait more than a year more than 2 years and how many of them did not come back if you need an LOD Expressions refresher please check out another comprehensive tutorial I have on this topic it starts from the basics and goes through the different types of lods and the implications of the order of operations with lods I've also provided a link in the card above and description down below let's first look at the details of our customer purchases let's drag over customer ID let's also display all the dates of their purchases so right click drag order date right beside customer ID and we're simply going to display the discreete date click okay let's also calculate the cohort they belong to this should be the first year that they bought from us we have discussed this in more detail in part one of the series so if you need a refresher please check that out so on the drop down create calculated field let's call this cohort year and this is going to be a fixed LOD we're going to fix this to the customer ID and this should be the minimum year of their purchase date or order date so Min of year of order date let's click okay let's move this to Dimensions because we want this to be simply a descriptor so drag that over and let's display this let's create one more fixed LOD that captures the first date of purchase the expression is going to be very similar to our cohort expression so in this case create calculated field let's call this first purchase date this is going to be fixed to a customer ID so fixed drag over customer ID colon and this is going to be the minimum of the order date min order date click okay let's display this as well so right click drag first purchase date let's display the discrete date click okay so in here we can see that we've captured the first purchase year as well as the first purchase date now what we need to explore is when was the next time they purchased from us so for this particular customer the next time was September 15 we want to be able to capture that into a different column so that we can calculate how long it took for this customer to come back we can use a fixed level of detail expression to calculate this what we need to do is to scan all these dates per customer and get the next date after the first purchase date let's create a calculated field let's call this second purchase date so this is going to be a fixed LOD expression but let's work through the logic first how do we know the second purchase date well we will if the date is greater than our first purchase date so at the core of it is really an if statement if the order date is greater than our first purchase date and if this is true we simply want to Output the order date because we know it is greater than or later than our first purchase date let's take a look at this for this particular customer that's the first purchase date therefore this expression will give us null this second purchase date is greater than the first purchase date therefore we are going to display this value but this seems problematic because this is also going to Output October 4 March 3 and June 29 when we get a group of these records all we need to do is to Simply pass that to another aggregation which is required for our level of detail expression anyway so we're simply going to pass that to another aggregation function called Min so let's complete our expression we're going to fix this to a customer ID again so it's still going to be fixed to a customer ID so drag that

over colon and what we want is simply the minimum of this group of dates so we can pass this to a Min function let's pass the whole thing over make this a little bit bigger so we can see everything and then let's close our parenthesis actually I'm just going to move that over so it's a little bit more readable and let's close our curly brace let's click okay and let's see if this is right so right click drag second purchase date let's display that so right now we have our cohort here first purchase date is the first one that we're seeing in our records the second purchase date is 9:15 which is correct so all that's left for us to do is to calculate how long it took for this customer to come back to calculate the difference between two dates we can use a function called Date diff and we can pass in many different units we can calculate the number of years the number of quarters number of months number of days for this example let's just use number of months so another calculated field let's create a calculated field here let's call this number of months between purchase and this is simply going to be a date diff we are looking for the unit month our start date is the first purchase date our end date is the second purchase date let's close this parenthesis and let's click on apply let's click okay I'm going to move this to Dimensions again because we simply want this to be a tag or a descriptor so let's take a look at this that's display so it looks like this customer took 6 months to purchase another customer same thing but we have some customers that did not come back right away this particular customer took 26 months to come back one more thing I want to do is to group the number of months into specific periods perhaps we want to capture less than 6 months 6 to 12 months 12 to 24 months and then 24 plus months there are many different ways of doing this but I'm going to use the group functionality in Tableau for this example so let's right click on number of months between purchase create group and in here I'm going to capture 0 to 5 let's group that let's call this less than 6 months let us capture 6 to 12 let's group that and call that 6 to 12 months let's capture 13 to 24 let's group that let's call that 13 to 24 months anything that's over we're simply just going to say it's 24 plus months and for customers who did not come back it is going to be null because there is not a second purchase date we're simply going to call this lapsed let's click okay so let's create our viz in a blank worksheet let's drag over our cohort let's display our duration group rearrange this a little bit let's rearrange the duration as well so less than 6 months has to be the first one and then let's count the number of customers right click drag customer ID onto our view and let's select count distinct let us also add our totals so under analytics double click on totals so we can see in here from our 2018 cohort there's one customer who did not come back majority of them did come back in the less than 6 months range for our 20 21 cohort maybe it's a little bit too early but six of them haven't come back yet if we wanted to add the percentage we can simply add our table calculation so in this case let's take our measure on the drop- down quick table calculation perhaps we want percent of total let's change the Precision on the drop- down format and in here just zero decimal places for percentage let's close the formatting Pane and let's add the original numbers back I'm going to right click drag customer ID again onto our view select count this again and I'm just going to rearrange our Fields so I'm going to move measure names before our cohort year if we want to convert this to a highlight table we can simply take our measure values copy that to color and change our Mark to square control drag on to color change the mark to a square and there you go this answers the question which customers came back how long it took for them to come back and how many customers did not come back at all