1. Calculated field aggregation
In this video, we’re going to look more closely at working with calculated fields, particularly aggregating them across different dimensions and calculating rates that are important to monitor for paid social marketing. For example, we can calculate an engagement rate - which represents the percent of traffic, or visits, that clicked on an interactive feature (a link, a video, an image to zoom in, etc) on the page.
So let’s calculate an engagement rate. What happens if we just divide engaged visits by visits? I'm going to name the metric "Engagement Rate - 1" and drag it into our sheet. So I'll look at engagement rate first by landing page.
Hm…..since these values are absolute numbers, and not percentages, we’re actually getting results that are greater than 1, which isn’t possible. What happens to the engagement rates if we add every dimension to the table?
So I'll drag in other features of our data set, such as ad group, creative (the visual that ran with that ad), demographic (the different target demographics), the date, and I'm going to convert this down to "DAY" since that's the level of granularity we have in the data set.
I'll drag in everything else. So that includes platform, the platform the ad ran on, and served, whether this was served in stories or in a different medium.
So this looks better - we can see that all the values are all lower than 1. Let’s check by dragging the engaged visits and visits field into our table so we can confirm the calculation is correct.
I'll rearrange everything so we have a single tabular view, which is often easier to analyze the data.
I'm going to rearrange the order of the measures, and so now we can pretty clearly see the engagement rate is correctly calculated, dividing the engaged visits by the visits.
This looks better! So now, let’s pull some of the dimensions out and see what happens.
Hmm - when we pulled some out, the calculation stops working correctly. Why is that?
The answer lies in a subtle difference that we can see when we look at the "Measure Values" in the bottom left hand portion of our screen. Here, we can see that tableau automatically summed engaged visits and visits when we drag them into the table. This is what we’d expect to see - in the example above, when we add the two measures to the landing page dimension, we want to see all the engaged visits and visits for the homepage, for example, across all the other dimensions.
But now, let’s return to our calculated field.
In this calculated field, we didn't aggregate either metric. As a result, tableau is doing the calculation at the row level - the original granularity of the data set. Then, when we drag it into the table as we have here, it sums all the values of the calculated field row by row.
This is why we see SUM(Engagement Rate) in our "Measure Values". Instead, we want to divide the values we see for engaged visits and visits - the sum of each.
So now, let’s create another calculated field for our engagement rate doing just that, and I'll name it engagement rate 2. Let's add it to our table and quickly compare.
These are the numbers we wanted to see; looking at the values in comparison to the engaged visits and visits volumes in the table shows us the calculation is working the way we want.
Notice a difference in how tableau displays the value in the "Measure Values" section; instead of using a SUM, tableau notes that the field is instead being aggregated (AGG) based on the calculation within the field. This is an important difference.
This distinction, between aggregates and sums, is an important one to keep in mind when using calculated fields in tableau. In this example, we used paid social data. Now, in the following exercises, you'll give it a try with email data.
2. Let's practice!