1. DATEDIFF and DATEPARSE
In this video we’ll be reviewing date-specific functions in Tableau. We’ll be using these functions throughout the upcoming chapter to calculate values related to LTV. An important part of the LTV calculation involves knowing how long the customer has been a customer; in other words, quantifying the customer’s lifetime. This involves date-based calculations.
Let’s start with a data set we used earlier in this course, that includes cookie ID, email, landing page type, origin channel, purchase date, session ID, and the purchase value:
Let’s start by finding the first purchase date for a customer using a level of detail calculation as we’ve done for other fields in this course. We’ll fix on the Cookie ID field, since we want the field to be unique for each Cookie ID, and since we want the first purchase date, we’ll find the MIN of purchase date.
If we wanted to know the amount of time that’s passed since each purchase date, we could use the DATEDIFF function. Let’s create a calculated field, days since purchase, that does just that.
You can see when I type the function, I’m told I need to specify the date part first; that’s the unit of time I want the field to be in. In this case, we want days, So I’ll write, ‘day’, between single quotes.
We’ll then specify a start date, in this case, the purchase date.
Next, we include an end date - I’ll use the TODAY function for demonstrative purposes, and then we can specify the day of the week that starts the week. That’s an optional field, as its in square brackets, and I’ll choose to leave it blank for the purposes of this demo:
From dragging the field into the table, we can see that we seem to have written the formula correctly. One quick way to check date-related fields is to order the date in ascending or descending order and make sure the field changes appropriately:
What if we had wanted to calculate the days that had passed since a specific date, not today? Let’s edit our calculated field and try a different end date, say, January 31, 2023:
When we just input the date alone, we get an error: Tableau interprets the number as an integer. We need to clarify that it is a date field. We can use the DATEPARSE function, which instructs Tableau to parse, or read, the included values as a date:
We can see that in addition to including the date itself, we also need to specify the format the date is in and include it in string form.
I’m going to use the format “MM-DD-YYYY” and adjust the month portion of January 31, 2023 by adding a 0 to mirror the format we’re using with two characters for month. I’ll then add quotes around the date to make it a Tableau-recognized string:
Great. It looks like our calculation is now valid. Let’s see if the calculation adjusts as we’d expect in the table:
And sure enough it looks like it is working correctly.
Now that you’re familiar with DATEDIFF and DATEPARSE, let’s see how they can be used in marketing analytics.
2. Let's practice!