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.

Table of Contents

Introduction to Using Pivot Tables for PPC Analysis

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.

Table of Contents

How to Select Your Advertising Data

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.

Remove any ‘Total’ PPC Rows

Before we select the advertising data for the pivot table we need to remove the ‘Total’ row that Google Adwords includes on all reports.

Just press:

This will take you to the last filled cell in the spreadsheet. From here just delete that row of information.

Delete Adwords Report CSV Total Row to get your data ready for the pivot table

Now press:

This will take you to the first filled cell in the spreadsheet. Total row removed!

Select the PPC Data

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:

This will span your selection across all of your data’s columns.

With the first row of data selected press:

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.

Use CTRL + Shift + Arrow Keys to quickly select all of your pivot table data.

Create a Pivot Table

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.

Select the poorly circled PivotTable icon.

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.

This is the window that will appear when you click on the Pivot Table Icon

Table of Contents

Difference between Pivot Table and Pivot Chart

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 Pivot Table

A PivotChart includes both the PivotTable and a chart or graph. Behold, both a chart as well as a graph:

This is a PivotChart. Notice. The Chart.

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.

Table of Contents

Adding Rows, Columns and Values

Selecting a Pivot Table

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.

Once you select the Pivot Table you should see this Pivot Table Field List with your PPC fields listed.

To select your pivot table just click on the image floating on the left of your worksheet.

Add Rows to your PPC Pivot Table

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’.

Adwords ‘Hour of Day’ field in Pivot Table ‘Row Labels’ areas.

To add another Row for layered information rinse and repeat.

Here is what applying multiple Excel Row Labels looks like. Fancy yes?

If you’ve added two pivot table row labels you should see something like this in your spreadsheet.

This is an example of multiple row labels in a pivot table. It can get very confusing fast.

Collapse Layered Pivot Table Labels

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.

Click the poorly circle ‘Collapse Entire Field’ Icon to make PPC data more manageable.

You should have a nice looking Pivot Table now.

Adding Values to your Pivot Table

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.

Add ‘Values’ to your Pivot Table. We pulled in Adword’s PPC Clicks.

After your Values are pulled in you should see something similar to the following in your Pivot Table:

Something like this will be your Pivot Table output after adding the ‘values’

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.

Adding Columns to your Pivot Table

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.

We added Adword’s ‘Day of Week’ field as a column for some matrix fun.

You’ll now see your data presented similar to this:

When you add PPC fields to the Pivot Table Column area you will create a matrix.

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.

How to Use Columns and Row Matrix to Find Insights

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.

Table of Contents

Adjusting Field Formats to fit your PPC data

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.

This is the Pivot Table Value Field Settings Window.

How to Use Summarize Value Field by Section

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.

How to Use Pivot Table Number Formats

Click on the ‘Number Format’ button on the open ‘Value Field Settings’ window. You’ll now see this window:

Use this window to format you Pay per Click data.

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.

Table of Contents

Using Calculated Fields to Fix Excel Pivot Table Averaging Issues

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.

What is happening to my PPC data and my Pivot Table

There are two things happening to your data right now:

How and Why Create a Calculated Field

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.

Click on the ‘Fields, Items, & Sets’ icon to reveal the Calculated Field option.

You’ll see the following window:

This is the basic formula for calculating CPA (Cost per Acquisition) with a Pivot Table.

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.

A spooky scary difference between the PPC CPA the Pivot Table created by default and what we created.

Below is an example on how to create the CTR field. You should probably be getting the hang of this by now.

Behold the very simple formula for Click Through Rate that totally redeems your Pivot Table!

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.

Another HUGE difference in PPC metrics that the Pivot Table calculated and what we calculated.

Advanced Calculated Fields

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.

Here is an Error in our PPC Pivot Table. This shall not pass.

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.

Advanced Formula for even better PPC CPA numbers in a pivot table!

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.

Table of Contents

Customizing Empty or Error filled Cells

To clean up empty or error filled pivot table cells simply select the pivot table

The Excel Pivot Table Options Icon. Click here to clean up empty & error cells.

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.

Table of Contents

Sorting Pivot Table Data

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.

Select your column and sort away!

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.

Table of Contents

Using Pivot Table Slicers

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.

Select the poorly circled ‘Insert Slicer’ Icon.

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.

Slice and dice with all the PPC data fields you pulled into the pivot table

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.

Increasing the Size of the Data Slicer

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 is where you can adjust the settings of your Excel slicers.

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.

Using Multiple Slicers

Multiple slicers to use with our PPC pivot table data! Below is an example of what that exciting premise can look like.

An Example of how to use multiple pivot table data slicers with PPC Data.

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.

Table of Contents

I’m Excited to Hear About Your PPC and Pivot Table Adventures!

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!