How To Excel At Excel For SEM Applications, Part 5: Master Pivot Tables

In the previous posts of this Excel series for Search Engine Marketers (Part 1, Part 2, Part 3, Part 4), I’ve shared various tips and tricks for the tool. However, today’s post is just going to cover pivot tables, as I feel they are such an important piece for online marketers to not just grasp, but master. Excel itself can be intimidating and pivot tables are doubly so. I hope this will be the definitive guide to pivot tables for anyone in this industry who has yet to feel one hundred percent comfortable with this crucial Excel feature.

By the time you finish this post, you will be a Pivot Table champ (or your money back! LOL.)

The first thing to understand is why you would need to put your data in a pivot table. Well, not all data needs to be pivoted. However, when you have a table of data that you’re trying to analyze, pivot tables allow you to quickly extract meaningful information by easily changing columns, rows, and filters to get those useful insights.

Here’s an example Google Adwords Placement/Keyword Performance Report for a fictitious company called MapWiseTech:

Ten questions you might want the data to answer could include:

• What keywords get the most impressions?
• Which engines get the most clicks?
• Which campaigns generated the most conversions?
• Which ad groups had the highest click-thru Rates?
• What was my average position for my top spending terms?
• Which keywords spent more than $100?
• Which keywords in my General Terms campaign spent the most yet converting the least?
• Which ad group had the lowest Cost Per Conversion?
• How did Search perform vs. Content?
• How did different match types affect my performance?

These are all very common questions that any paid search professional has to answer in order to optimize their accounts. For those of you who aren’t consistently using pivot tables to collect this data, how long would you say it would take you to answer these questions for your accounts? Let me tell you, with pivot tables, these ten questions will take you less than five minutes to answer. That’s right. Less than five minutes.

Are you ready to be a pivot table convert now?

Building a pivot table

Converting a regular data table into a pivot table is much easier than you think. Simply highlight your table, then go to the INSERT ribbon and choose Pivot Table. A Create PivotTable dialogue box will open to confirm your data source and where you want the Pivot Table generated. The default option is in a New Worksheet, but you can choose the Existing Worksheet option and pick a cell to begin the insertion.

That’s it. You now have a pivot table from that source data. From here, you just have to choose your columns, rows, and values from the Field List. Notice you now have two extra ribbons for PivotTable tools, Options and Design as well.

Drag/Drop fields to build reports you want to see

The first question was, “What Keywords get the most impressions?”. This is child’s play in a pivot table. Drag the Placement/Keyword field into the Row Labels box and the Impressions field into the Values box. Then, drag the Ad Distribution field into the Report Filter box. The pivot table is built. The last thing to do is to choose Search Only from the report filter (so you see keywords, not placements) and click on the first impression value listed and click the ZA down arrow to sort from largest to smallest.

I know you can say that you could have done the same thing by filtering the source table and sorting the impression column. However, let’s go to the next level. What if you wanted to find out which ad groups had the most impressions? An account with even just 10-30 campaigns might easily have 100-200 ad groups. You could tell me that you could go back to Adwords and pull an ad group report and then filter and sort. But that would take time. With the current pivot table, just drag the Ad Group field into the Row Labels box and you have the data you need.

It’s important that you understand there is always a lot of experimentation in pivot tables. Even for advanced users, trying out new ways to drag and drop fields into the various row, column, filter, and value boxes provide infinite ways to look at the data. It’s important for you not to get frustrated while experimenting with your pivot table. It will take time to learn, but you may actually uncover interesting ways of looking at the data that you’ve may have never considered. So, go ahead, drag and drop fields until you find the data you need.

Collapse/Expand fields

When you have multiple fields in the Row Labels box, they will be grouped in the order which they appear. So, in the example below, I now have both the Ad Groups and Keywords showing. If I just want to see everything at the ad group level, I just select one of the ad group cells and click Collapse Entire Field from the Options ribbon.

Normally, I may have a Row Label configuration such as: Engine -> Campaign -> Ad Group -> Keyword. By using Collapse and Expand, you can quickly go in and out of granularity. Each time you do, the Grand Totals will change based on what’s selected.

Why you should use the most granular data you can find

Understand that Pivot Tables will aggregate values if there’s a common connection between the data you’re looking at. For example, the pivot table knows that these 100 keywords are all in the “Branded Terms” ad group. So, even though the source table is at the keyword level, they all have “Branded Terms” listed as the ad group. The pivot table will recognize that and aggregate them together. Therefore, a best practice is always to get the most granular data into a pivot table as it will be able to calculate the higher level groups as long as these connections exist. You can even pull the source data at the day level and group them into weeks, months, and years so you can see that information quickly in your pivot table.

Calculated Fields

There’s an inherent problem when you have calculated metrics already in a source table. For SEM, the two big ones are CPC and Click Thru Rate. You can’t sum up these metrics together in a pivot table, right? So, you have to use Excel’s CalculatedFields feature.

HOW TO: Options ribbon -> Formulas -> Calculated Fields. A dialogue box opens up where you can name a new field and then create the formula to generate this new calculated field. In the example below, I’ve created a new CTR field with the formula Clicks/Impressions. You can type the formula in or just double click each field to add it to the formula box. Now you’ve got a CTR field that’s being generated at every level of the pivot table.

Naming trick: You cannot duplicate names in a pivot table. Since “CTR” already exists, my trick is to just add a single “invisible” space after each name. That way, it fools the system into thinking it’s a unique name.

Filtering

If you know how to use filters already in Excel, you can apply the same techniques to pivot tables. Click the little box with the filter icon in any row or report filter cell and you can filter the following four ways:

• Report Filter. This is for filtering the entire pivot table, not just individual columns. You can drag/drop any field from the Field List into the Report Filter box. Using a combination of the Report Filters and the filters below can really help you zoom in on data. For example, you could use the report filter to choose a certain month and engine and then the Standard Filter to pick just those values you want to analyze.
• Standard Filter. Every value in that group will appear in the lower box. You can chose to Select All or individually check the boxes next to the values you want to see. A quick tip – If you want to exclude only a few values, use Select All and uncheck the ones you don’t want. If you only want to see a few of the values, click Select All until all of the boxes become unchecked. Then it’s easier to select those few.
• Label Filter. This is for filtering text. You choose the standard excel options such as contains, begins with, etc.
• Value Filter. This is for filtering numbers. Once again, you have the standard Excel options such as greater than, Top 10, etc

Formatting the table

You can format each field as you would with any column in Excel. The easiest way to choose the column and click Field Settings in the right click menu or the Options ribbon. From there you can choose whether you want the data to be summed, Counted, Averaged, etc. You can change the number format to percentage, dollar, include decimal levels or commas, as well. You can also explore the Design ribbon for setting a color scheme, banded rows/columns, when you want subtotals to appear, etc. Basically, all of the same style options you have for regular tables in Excel can be applied to pivot tables.

Pivot Charts

Pivot charts are just charts based on pivot tables. If you begin with the source data, you can choose to insert a PivotChart instead of a PivotTable. All this does is build a pivot table next to your chart. Pivot around the table to affect the chart. As well, you can simply create a chart from an existing pivot table (as you would with any data table in Excel).

Quick Tip: Select any cell in a pivot table and click F11. It will instantly create a column chart for you.

Miscellaneous Pivot Table stuff

• You can make multiple pivot tables from the same source table.
• When the source table changes, you can import that new data into the pivot by choosing Refresh from the Options ribbon.
• “Blanks” may appear in rows as data. Easily filter them out by clicking the down arrow on any header and un-checking the box next to that value.
• Move row labels up or down with Right Click -> Move. This can be helpful if you want to a row to appear at the top even if it’s not alphabetically going to happen if you sort. You can also just grab the row labels themselves and put them in the order you’d like (thanks for that, Sean!)
• If you pivot a table into data you want to copy and paste, make sure to Paste Special -> Values or the entire pivot will be inserted.
• You can make multiple pivot charts from the same pivot table. However, every time you manipulate the data, the chart will change. The best practice is to make a new pivot table to be the source data for each pivot chart.

Final Pivot Table trick: the Average Position calculated field

Unlike CTR or CPC, you can’t create a simple formula for the Average Position calculated field. Since each keyword has its own Average Position value, if you were to simple create an average to see it at a higher level such as the Ad Group or Campaign level, your numbers would be skewed. Why? Because you have to take into account the weight that each keyword has on it’s grouping level. In a simple example, if you had two keywords, one with Avg Pos of 1 and the other at 3, a simple formula would tell you their average position overall was 2. However, what if I was to tell you the first keyword had 100 impressions and the second keyword had 1,000,000? The average position for that group would be more like 2.99 than 2, right? So, how do you do get around this in a pivot table?

The actual formula is (keyword 1 Avg Pos x impressions)+(keyword 2 Avg Pos x impression) etc / Total Impressions. So, to start, you have to create a new column in the source table called AvgPosXImpsFormula. Then, create the formula of Avg Position x Impressions. Drag that formula down for the entire report. Then, copy and paste values that same column so you have a column of just the values, not the formulas. I call this column just AvgPosXImps. Then, when you convert this source table into a pivot table, you can create a calculated field with AvgPosxImps/Impressions. That will create the proper metric for any level you want to see the average position (i.e. campaign level, engine level, ad group level, etc).

I’ll be wrapping up this Excel series in my next post with some final tips/tricks and a Online Marketer Excel Skills test–20 questions to see just how good you and your crew are at the tool.