1. Web data example
In this video, we’re going to review some key tableau calculations that are used frequently in marketing analytics, specifically in web traffic data. Let’s start by looking more closely at the fields in this example data set, from an online jewelry store.
In this case, our data-set consists entirely of descriptive fields, including:
Channel: the medium used to run an advertisement.
Landing Page Type: the general type of page of the user's first page in that session.
Final Page Type: the general type of page of the user's final page in that session.
Cookie ID: the unique identifier stored on a user's device
User ID: a unique identifier for that user if they've created an account, which is typically when a user ID is assigned
Email: the email address the user created an account with, if applicable
Geography: the region from where the user is accessing the website
Session Date: the date the user accessed the website
Session ID: the unique identifier for that user's session exploring the website
The first question is the level of granularity of our data set. We can see in the data set tab that there are 2,000 rows in this sample; in order to determine which field serves as the level of granularity, we can do a distinct count of each of our identifiers. This includes user ID, as well as a distinct count of Cookie ID, and last but not least, a distinct count of Session ID. Sure enough, we can see that there are 2,000 unique session IDs; therefore, our data is at the level of granularity of session ID.
As I mentioned earlier, not all Cookie ID's will have a User ID, since User ID is assigned at account creation. That's why when we look at the two fields, we see a lot of User ID values are null.
Given that we know Session ID is the level of granularity of our data set, we can see that some Cookie IDs also have multiple Session IDs.
And this makes sense; we’d expect that some individuals would visit multiple times. And if we do a distinct count of sessions by Cookie ID, we can see how many times each individual Cookie ID visited.
But when I remove the Cookie ID, the distinct count aggregates back up to the 2,000 sessions in the table.
What if I wanted to keep that value to be the distinct count of sessions for each cookie ID? And keep the calculation at the level of cookie ID? I can use a level of detail expression for that in tableau. I'll call the FIXED function and specify I want the following calculation to be fixed on Cookie ID. I'll then specify my calculation: a distinct count of session ID, and close the curly brackets corresponding to "FIXED". I'll name my level of detail expression "Session ID CNT". and then convert it to a dimension.
When we look at this value, we see that it stays fixed; it doesn't sum to 2,000. When I look by Cookie ID, its the same value as a distinct count of session ID.
Now we can use this field to perform other analytics. For example, we can examine the count of distinct Cookie IDs based on how many sessions they had. I'll convert this quickly to a bar chart, and now we can see the number of cookie IDs based on the number of times each individual visited.
We can also examine whether this trend is different for individuals who made an account on the website and therefore have a User ID. I’m going to make a calculated field that indicates if a User ID exists or is null. Here I'll use an IF statement with the "ISNULL" function, and say, if it is true, that User ID is null, then return "No User ID". Otherwise, if it is false that User ID is null, return "User ID" - so here I'll write else "User ID" and then END to close the IF statement.
Then I'll use this calculated field to make a separate bar chart based on whether or not the cookie ID has a user ID associated with it.
As a final step, I’m going to display the cookie ID counts as a percent of the total. That way, comparison between traffic volumes isn’t skewed by how many unique cookie IDs have user IDs and how many do not.
From this chart we can see that the general trend of visit frequency is not impacted by whether an individual has created an account on a website. This may be something we’d want to share with our broader marketing team as it seems to represent a missed opportunity.
Now that you're more familiar with web traffic data, its your turn.
2. Let's practice!