The purpose of this article is to give an in-depth and visual look into working with and modifying Excel pivot tables in preparation for Pay Per Click data analysis.
Pivot Tables make our job as marketers and data analysts much easier. However, there are many quirks in how pivot tables calculate data that need to be accounted for. This guide should be able to take a average excel user and turn him or her into a proficient pivot table ninja. With the knowledge you gain in this visual tutorial you will be able to accurately view, slice, dice, and organize PPC data to your heart’s content.
This guide assumes you have a working knowledge of excel and Google Adwords reports.
Due to the lengthy and visual nature of this excel tutorial I’ve included a Table of Contents of internal links to help you jump in where you left off.
Pivot tables are fantastic excel tools for reporting and viewing large amounts of PPC data. A pivot table will take a large chunk of organized data, like an Adwords Keyword Report, and allow you to break that data into bite sized pieces for easy information digestion.
It’s important to note that for a Pivot Table to work you MUST be using organized data. Any csv or excel download from Adwords, Bing Ads, or Facebook Ads should do fine.
Select the time frame and PPC report you want to analyze with a Pivot table. For this tutorial’s data we used an hour of day and day of the week campaign segmentation. Pivot tables work best when the data you’re working with isn’t easily visually analyzed.
Before we select the advertising data for the pivot table we need to remove the ‘Total’ row that Google Adwords includes on all reports.
Ctrl + End
This will take you to the last filled cell in the spreadsheet. From here just delete that row of information.
Ctrl + Home
This will take you to the first filled cell in the spreadsheet. Total row removed!
Select the first category label by clicking on it. This should be the very top left cell you’ll be including in your pivot table data. Make sure that you do include all column labels or using your pivot table will be very difficult.
With the first category label selection press:
Ctrl + Shift + Right Arrow
This will span your selection across all of your data’s columns.
With the first row of data selected press:
Ctr + Shift + Down Arrow
This will span your selection to include all data you will be including in your pivot table. You should have any ‘Total’ rows deleted. If they aren’t go ahead and delete those rows and follow the previous steps again.
With your Adwords data selected, click on the ‘insert’ tab. You’ll see the PivotTable icon on the far right of the Excel toolbar. Click that.
You’ll see the following window appear. All of the data you’ve selected will be included. Just click OK and Excel will create your pivot table in a new Worksheet.
A bit of a side note: If you clicked the arrow below the PivotTable icon you’ll have noticed the option for both a PivotTable and PivotChart.
What’s the difference?
A PivotTable is just the table. Behold, a table:
A PivotChart includes both the PivotTable and a chart or graph. Behold, both a chart as well as a graph:
You can edit a PivotChart almost identically to how you would edit any other chart within Excel. Charts are great for seeing trends, groupings, or just making data attractive.
To add a row to your pivot table you will need to first make sure your pivot table is selected. If you do not see the following window on your worksheet your pivot table is not selected.
To select your pivot table just click on the image floating on the left of your worksheet.
To add a row to your pivot table simply determine what you’re measuring. With our data we are measuring account performance by day of the week and hour of the day. Since each day of the week has 24 hours that can potentially perform differently than another day’s hours we want to layer our data. We’ll layer it by Hour first and then Day.
Simply click on your first categorical measurement (field) and drag it into the box labeled ‘Row Labels’.
To add another Row for layered information rinse and repeat.
If you’ve added two pivot table row labels you should see something like this in your spreadsheet.
As you can see, if your data is presented in this expanded view it will be difficult to work with. To collapse these layered row labels make sure your PivotTable is selected. You should see a purple ‘PivotTable Tools’ section on the right of your toolbar.
Now that your row labels are manageable let’s add some data. I’ve pulled in the Clicks field into my data. Select a metric in the pivot table management window you want to analyze and pull it down to the ‘Values’ section.
After your Values are pulled in you should see something similar to the following in your Pivot Table:
If you added in a metric that is an average or a percentage like Click through Rate or Cost per Conversion you’ll notice the numbers are WAY off. It’s ok, we’ll deal with that later.
By default Excel will create pivot table columns for you based on the PPC fields you add to the ‘Values’ section. To create more of a matrix view (the math kind not the movie kind) you can simply select another categorical PPC field like; Hour of Day, Day of the Week, Campaign Name, etc. and pull it down to the ‘Column Labels’ section.
You’ll now see your data presented similar to this:
Personally, I don’t find this view as user friendly but combined with the right Conditional Formatting I’m sure it could do wonders at helping you spot issues.
For example, you could create an Hour of the Day and Day of the Week matrix that is measuring profits. You apply the Conditional Formatting > Color Scales and now you can easily see both the day and hour combinations that perform best and worse.
It’s important to note that if you’re using an Adwords or Bing Ads Report what you’re analyzing is only last click conversion modeling. If these are the only reports you have access to, make sure that you understand your sales cycle first. What you will see through this analysis of your Adword’s report could be a day and hour combination that seems to be performing terribly but in reality it is responsible for 40% of sales the following day.
With the data we’re using we are combining together all of the Adword’s PLA, Display, Search, Brand, etc. campaigns by using an account level view. This data is not segmented out by type or by audience. Its use is frankly limited. Extreme performance, for good or worse, in any campaign will throw the value of the combined data off unless it is considered independently from the other parts.
This is another value of using Pivot Tables. If I were to use very segmented data, like a keyword level report with all campaigns and adgroups, I could use a Pivot Table to see the data from a high level or ground level perspective.
If you take high level data like an account and analyze it you cannot break it down further. The take away from this tucked away tangent is to use granular data if you need to see things as a whole AND things as a part.
We’re now off to format our PPC data within the pivot table. If you added an Avg. Position field into the ‘Values’ area you’re probably seeing a lot of decimals right about now. To format your data click on the small downward pointing arrow located on the ‘Sum of Clicks’ item in the ‘Values’ area.
While we’re at this screen we might as well talk about how to use this section.
The Summarize Values By section that you see before you allows you to change how the Pivot Table calculates its cells. Sum is selected by default. Sum will …sum… all the values of, let’s say, clicks.
If you wanted to do categorical analysis of Geographic Locations by campaign you would do the following:
Count will then …count…all of the instances each Geographic Location was used within each campaign.
Click on the ‘Number Format’ button on the open ‘Value Field Settings’ window. You’ll now see this window:
Number formatting options in a pivot table are identical to the options you have when formatting any other cell in Excel.
To remove the number of decimals in the Avg. Position metric just go to the ‘Number’ category and set the number of decimal places to 1 or 2 depending on your preference. When you click ‘OK’ your changes will be applied.
This is the part where a lot of Pay per Click analysis can go bad very quickly. As I previously mentioned, you’ll likely be seeing some k-r-a-z-y numbers coming from Cost per Conversion, Click Through Rate, and any other pre calculated numbers. We’re going to fix that now.
There are two things happening to your data right now:
To solve the problem of non-weighted pre-calculated metrics like CTR and Cost / Conv. we’re going to create a calculated field.
A Calculated Field allows you to use the data fields you’ve pulled into your Pivot Table to create your own custom numbers. If any of you have used a PPC platform like Aquisio this will be a familiar concept.
By using the base field values we’ve pulled in to the data we can re-create field values like CTR and Cost / Conv.
You’ll see the following window:
You can see in the image above that we have created the ‘Real CPA’ field by taking Cost and dividing it by Conv. (1-per-click). You’ll notice that field names that contain spaces need to have a single quote ( ‘ ) around them to work properly. It’s probably easier and quick to just find your field in the scroll menu on the window and insert the field.
Notice the difference in the numbers? That’s kind of a big deal. Select OK and your new field will be available.
Below is an example on how to create the CTR field. You should probably be getting the hang of this by now.
Look at the difference in Click through Rate after we fixed the pivot table with calculated fields. Again, pretty significant: from jumbled to just right.
If you’ve created any Cost per Conversion calculated field you’ll notice a big error message (#DIV/0!) for the categories that contained no conversions. This is because you set your calculated field to create Cost per Conv. as
= Cost/Conversions. Excel is freaking out because you’re trying to divide by zero.
What we could do is go to our Pivot Table options and tell Excel that every time there is an error message, just insert a 0. This is cool because we no longer have an error message but it’s ugly because now our data is dirty. The true cost per conversion of a high level category needs to include the cost of sub categories in its calculation.
To solve this problem we just write a Calculated Field that’s a bit fancier.
Get to the Calculated Field Screen
Click on the drop down menu to the right of the ‘Name:’ field
Select your CPA Calculated Field
Use the following formula:
=IF(‘Conv. (1-per-click)’=0,Cost,Cost/’Conv. (1-per-click)’)
Adapt it to your needs
What we’ve done is tell Excel that if the number of conversions in a category are zero than replace than replace the CPA( Cost per Acquisition) value with the total cost incurred by that category. If the number of conversions is not zero than calculate the CPA as normal.
To clean up empty or error filled pivot table cells simply select the pivot table
You will be able to input whatever value you want in place of an empty or error filled cell. The downside here is you cannot enter a variable or formula. You’ll just replace those instances with a static numeric or string value.
To sort your data you select the Pivot Table Column you’d like to sort by and use the Pivot Table Options tab to select the sort function.
You can sort the collapsed rows as shown above by column label values. If you collapse a row you can also sort the sub-categories by column label values.
Can I say I love these tools? Pivot Table Data Slicers not only have a very exciting name but they also add a great deal of ease to working around a pivot table.
A slicer is essentially a visually friendly and accessible ‘instant filter’. When using a slicer you’ll be able to simply click on the element and value you want to filter by without having to access any menus.To see is to believe. Follow me.
You will see the following Pivot Table Slicer Options. Notice that you only have access to the original data fields you pulled into your pivot table. These will be the fields you are able to filter your data by. You cannot use Calculated Fields in a slicer.
Select the field you’d love to slice and dice with. Categorical fields are easiest to work with but I’ll show you an easy way to get around that.
Click ‘OK’ and your slicer will appear on your spreadsheet right next to your pivot table.
If you’ve selected a field with a larger number of unique values you’ll see a scroll bar on your slicer. To work a bit easier with the slicer let’s increase the number of columns and rows it displays.
Here you can increase the number of slicer columns displayed, the size of the slicer boxes, etc. Use these options to create a slicing tool you can work with easily.
Multiple slicers to use with our PPC pivot table data! Below is an example of what that exciting premise can look like.
You’ll notice that I’ve created four columns for my Hour of the Day slicer. This was great for me because I can use the tried and true method of
click , ( Shift +click) to select multiple values extremely quickly.
For those not in the know, click on your starting value, hold shift, and now click on your ending value. All the values within that range will be selected. You can use this method to work with large slicers but I would recommend just using conditional filtering for that.
To recap, the goal of this guide is to help the average Excel and PPC data analyst learn how to use and modify pivot tables effectively to fit their needs.
This article will be built on as I create sub guides for actual PPC data analysis scenarios.
I hope this extensive guide has been useful and I’d love to see/hear how you’ve used your pivot tables. Email or tweet me what your favorite pivot table uses are and I’ll post the best on the blog!
"*" indicates required fields