Movers and Shakers
Movers and Shakers

Over the past year, I have spent hours and hours diving into multiple marketing data sources. Building an overall dashboard for the marketing team. What was supposed to be a high-level view still turned into something that can be overwhelming and take more effort than I would like to identify action items. Don’t get me wrong, I believe it still has value, but I want to build an executive-level overview and a tool that immediately helps identify what channels are performing and which ones are not. The goal of this report is not to answer but, to spark leadership to ask questions of the owners of those channels.

The Final Product

Final Movers and Shakers Report in Tableau. Image by Author

This report looks at 30+ metrics across our marketing channels and identifies which ones had the biggest gains & losses compared to the previous month, the previous year, and year to date. It also pulls out the values of those metrics so you can get an understanding of the metrics outside of the percentage change.

Building this report presented a variety of challenges. The data was coming from 5+ separate data sets and the metrics are of varying types. We’re also comparing two separate time periods, by month and by year. All of these metrics needed to be merged into one data set in a format where their performance could be compared against each other.

Organizing the Data

The first hurdle was cleaning the data. I already had all of the data sets published to Tableau Server and set to auto-update. So my only real option was to build a flow in Tableau Prep.

Full Tableau Prep Flow for Movers and Shakers report. Image by Author

I approached this by creating 2 data sets. One that aggregates the data by month and one that aggregates by year. It wasn’t much extra work in Prep, but it would save me a ton of time building the reports in Tableau.

For a simple dataset where the KPIs are just aggregations of data, the process is pretty easy. For YouTube, the 2 metrics I am interested in tracking are Total Views and Hours Watched.

Youtube Tableau Prep Flow. Image by Author

I grab the Youtube data from Tableau Server, the first cleaning step removes any fields I do not need and also renames the metrics that I will be monitoring. There is also a filter because my Youtube data has more than one channel’s worth of information in it. I also created a calculated field to make aggregating by month easier. As Tableau Prep has some limitations on working with dates.

STR(MONTH([Date]))+"/01/"+STR(YEAR([Date]))

Using this new field the next step aggregates by Month/Year.

Aggregating YouTube Data. Image by Author

Then I take that data a pivot it, to create our final data format. [MM/YY], [Metric Names], [Metric Values]

Pivoting YouTube Data. Image by Author

For other data sets that include a calculated metric, there is a little more work that needs to be done. Let’s look at email data, where we need to calculate Open Rate and Click To Open Rate.

Email Data Tableau Prep Flow. Image by Author

The main difference is after we aggregate our data by month and before we pivot our data, we create calculated fields for our new KPIs.

[Email - Total Uniq Opens]/[Email - Total Mailed]

I have one other KPI that requires a little extra work. For our Comms data, we track Share of Voice. I collect data around the conversation of 4 separate brands. Share of Voice is the % of the total conversation that is about the brand I’m tracking.

Comms data flow to calculate Share of Voice. Image by Author

To do that I have to split the comms data set and aggregate the total conversation from the 4 brands. Then merge that back into the original data so I can calculate the share of voice. Before pivoting to my final data format.

[Comms - Total Buzz]/[Comms - Total Buzz All]

Finally, we take all of our different pivoted data sets and merge them into one final data set.

Image by Author

With this work and repeating the work, but aggregating by year, your data should be ready to build your report.

Building the Report

The report is made up of 18 individual metric views. Below is a breakdown of this view.

Image by Author

Let’s look at how we calculate the change in the metric.

Managing our Dates

First, we need to get a handle on our dates. I want to only ever compare full months vs. other full months. So we need to make sure if we’re in November and we’re comparing month to month we’re looking at Oct vs. Sep.

I create 5 Dimensions as calculated fields to manage the dates for our monthly comparisons, using the TODAY() calculation as the basis. Last Full Month — Month, Last Full Month — Year, Previous Month — Month, Previous Month — Year, Previous Year — Year.

Last Full Month — Month

IF MONTH(TODAY())-1 = 0 THEN 12 ELSE MONTH(TODAY())-1 END

Last Full Month — Year

IF MONTH(TODAY())-1 = 0 THEN YEAR(TODAY())-1 ELSE YEAR(TODAY()) END

Previous Month — Month

IF MONTH(TODAY())-2 = 0 THEN 11

ELSEIF MONTH(TODAY())-2 = 1 THEN 10

ELSE MONTH([TODAY())-2

END

Previous Month — Year

IF MONTH(TODAY())-2 = 0 THEN YEAR(TODAY())-1

ELSEIF MONTH(TODAY())-2 = 1 THEN YEAR(TODAY())-1

ELSE YEAR(TODAY())

END

Previous Year — Year

[TODAY()] - 1

Change in Metric

Now that we have our important dates let’s calculate our Change in Metric, we have to create metrics for each comparison time period. For example, we’ll show Month Over Month.

SUM(IF MONTH([Date]) = [Last Full Month] AND YEAR([Date]) = [Last Full Month - Year] THEN [Metric Values] END)

/

SUM(IF MONTH([Date]) = [Prev Full Month] AND YEAR([Date]) = [Prev Full Month - Year] THEN [Metric Values] END)

-1

I then do a custom format on the so the up or down arrows are added automatically.

Custom formatting Metric to add arrows. Image by Author

If you want to copy and paste, here you go. ▲ 0.00%;▼ 0.00% . You can repeat this process for calculating your Year over Year by changing the calculation for identifying your comparison time period.

Previous Period Metric Value

Next, we will calculate and display the previous period‘s’ metric value. This is actually already done, just create a new calculated field for the bottom half of your Change in Metric Calculation

SUM(IF MONTH([Date]) = [Prev Full Month] AND YEAR([Date]) = [Prev Full Month - Year] THEN [Metric Values] END)

This is great but creates a visual complication for us as all of the metrics are on varying scales of numbers. From millions to percentage points. We need a way to auto-format the data so it displays nicely in our dashboard.

Notice the variations in number values. Image by Author

So I created another calculated field to round differently based on how large the number is.

IF ([MOM Change - Comp] ) >= 1000000000 THEN ([MOM Change - Comp] ) / 1000000000

ELSEIF ([MOM Change - Comp] ) >= 1000000 THEN ([MOM Change - Comp] ) / 1000000

ELSEIF ([MOM Change - Comp] ) >= 1000 THEN ([MOM Change - Comp] ) / 1000

ELSEIF ([MOM Change - Comp] ) < 1 AND ([MOM Change - Comp] ) > 0 THEN ([MOM Change - Comp] ) * 100

ELSE ([MOM Change - Comp] )

END

Shortening our various KPIs to a similar format. Image by Author

Now that looks great, but we need to identify our units. To do that I create one more calculated field.

IF ([MOM Change - Comp] ) >= 1000000000 THEN "B"

ELSEIF ([MOM Change - Comp] ) >= 1000000 THEN "M"

ELSEIF ([MOM Change - Comp] ) >= 1000 THEN "K"

ELSEIF ([MOM Change - Comp] ) < 1 AND ([MOM Change - Comp] ) > 0 THEN "%"

ELSE ""

END

Now we just add all of these into one view, with our dates.

Image by Author

I drop the 2 metrics & 2 dimensions into the text tab. Then I pop out the text editor by selecting the 3 dots. Then just rearrange my variables into the format I would like. You can see that the % sign gets added automatically because the value is in between 1 & -1.

Titling the Card

I wanted to make sure that all of the titles for the cards were in a good clean format, starting with the channel and then the metric. The bulk of this work was done in Prep, but it still needed cleanup. I created another Calculated field to reformat the Metric Name by wrapping the dimension in REPLACE() statements.

REPLACE(

REPLACE(

REPLACE(

REPLACE(

REPLACE([Metrics Name], "Final", ""),

"Twitter", "TW"),

"Instagram", "IG"),

"Facebook", "FB"),

"SOV", "Share of Voice")

This helped to shorten and align my titles to where I was happy.

Filtering by Rank

Our last step is to filter by rank so we can see the #1 performing metric, #2, and so on. For this, we drop our Month Over Month change metric into any section of the marks box. Then we create a quick table calc for Rank, drag that pill to the filters tab, and change the filter to a one number range.

Filtering Change in Metric by Rank. Image by Author

Apply that filter to all 4 views you created and then repeat the above process for the top 3 & bottom 3 for each time period.

Putting it all together

Now we need to create our final view. I don’t imagine I'm alone with my frustrations with Tableau layout tools. Coming from a design background they leave a lot to be desired compared to Adobe’s tools. There are a few things that can make the process easier. First I nest a Horizontal Group into the bottom of a Vertical Group to help with the alignment of our views.

Grouping data in your dashboard. Image by Author

Make sure to fit your data to the width and format it to center aligned. The grouping helps a ton for your alignments and also makes it not as big of a nightmare if you have to shift everything on your layout by 15 pixels if, for example, your header image changed in size. I created the card as a .jpg in Photoshop and dragged those individually to the dashboard. Repeat this process for all 18 and you should be all set.

Job Well Done

You should now have a report, that helps you to identify KPIs that are over or underperforming for 3 different time periods. Now it’s off to find out the reasons why.
['Dwight Eppinger']
['Tableau', 'Data Science', 'Dashboard', 'Kpi Dashboard', 'Marketing']