Did you know that you can visualize a pivot table’s sum or count as a percentage for the column? The benefits of using this are that you can switch back and forth at any time, so you can visualize trends, act on opportunities, and genuinely compare like metrics against each other much faster. In this blog post, I will show how to do this at the most elementary level but will expand upon this practice for different ways to slice and dice data. In this example below, I took 350k rows of data and quickly found what states drove high conversion rates and the lowest cost per acquisition. I can make this results to then bid more aggressive from these states.
Here is a post that shows how to use a pivot table if you need a refresher.
How to Show Values as a Percentage
With the data that you have in the pivot table, you can right-click on any column that you want to visualize and head over to the percentage of column total. From here, you can click on any of the settings that have the percentage mark on them. For this example, I chose to do the column total as the setting I wanted to visualize.
After clicking on that setting, I am now presented with this column as a percentage of the sum of sessions for the states that brought in traffic.
How to Take this Even Further with a Heat Map
The example above was fundamental, but I want to share how I would take data like this even further. I want to see what states convert well and have a low CPA so I can bid more aggressive for them when a visitor is searching from there. I can do all of this within the pivot table, and I can better visualize this with a heat map. As a note, I recommend doing each of these steps per column, or you will start to combine data in different columns which will throw off the heat map. Also, you do not want to select the grand totals at the end when you create the heat map or else your data will be skewed. See step 2 for what I mean by this.
If you are new to excel, you can check out PPC Hero’s post about heat maps that explains this process in more detail.
Step 1: Turn the Right Columns into a Percentage
This step is the step that we did above. We want to change the columns that we want to see as a percentage first. I did this with the clicks and the impressions columns.
Step 2: Use Data Bars
For this example, I want to see spend and conversions with a data bar set. This will help in the heat map to understand what drives the majority of the conversions but also costs the most. As a note, you need to make sure you are not selecting the total at the end, or this will throw off your data. You can remove the total from the pivot table, or you can choose everything above that amount. Here is an example of what this looks like.
Step 3: Use Color Scales
I use the color scales on the CPA and CR% columns. To do this, you need to click on the column that you want to visualize and head to the conditional format section. From there, you want to look for the color scales section. Make sure you are using the right color scale view or else you can misread your data. For the CPA example, a lower CPA should be in green while a more significant number should be in the red. The opposite will be right for the conversion rate column.
This blog post will get you caught up to speed on how to use a conditional format.
How to Read the Heat Map
At this point, you can have a completely different heat map with different values than mine, but the concept is the same. You can visualize the data trends that you want to see. For this example, I want to look at what states that are converting well for me and that I should consider bidding more aggressively on. From a high level, I know that these states makeup 2% or so of my traffic but also account for over 3% of my conversions for my site. While that number might seem low, it is more about looking for that little hanging fruit and optimizing for it. It is always easier to look at top performing metrics when the data trends are the highest, but I want to make sure I am also being budget focused too.
