How Excel Pivot Tables Can Help in Your SEM Campaign

Excel is probably one of the most powerful applications that we use day in and day out, often without realizing its full potential. For all search marketing professionals and particularly the ones that specialize in Paid search campaigns on platforms like Adwords, Yahoo Sponsored Search and Adcenter – Excel is probably one of the most used tool.

While there are too many ways that MS Excel is used in SEM campaigns right from the keyword research phase to campaign optimization and reporting, on this article I will focus on just one extremely useful feature of MS Excel that is used / should be used by all search marketing consultants and professionals.

Pivot Tables as per MS Excel’s definition is “A Pivot Table report is an interactive way to quickly summarize large amounts of data. Use a Pivot Table report to analyze numerical data in depth and to answer unanticipated questions about your data”. If you have not used this tool before and have managed any search account with more than 50,000 keywords, you must be super excited by now on reading this definition. Now let’s look at how Pivot table can help with search marketers in managing paid search campaigns.

The first thing you should keep in mind is that you don’t need to Pivot all data. Use Pivot tables only for data that you need to analyze and get meaningful information. This particularly effective when you need to shift/ change columns, rows, add / remove filters in the same report to get your required information. An ideal example would be if we try to analyze a keyword / placement report from Adwords.

Now from this report we can see a lot of information in terms performance of each keyword or placements and using the sort and filter functions we can also get some more information like the top performing keywords in terms of CTR / clicks/ cpc etc and few other information. Now what if we try to get a few additional information like..

Yes, you can answer all of these by going back to the report console in Adwords and pulling separate Network level, ad group level and Campaign level reports for each, but a simple Pivot Table can help yo to get answers to all these questions right from the Keyword / Placement Report you downloaded initially. Let’s see how..

Create a Pivot Table

Once you have the Excel data table with you, on the “Insert” ribbon, to your extreme left notice the icon that says “pivot table” – click on that. This should select the entire data range in the table and also pop up a dialogue box that shows the cell reference for the selected area and asks you where to create the pivot table ( in the same sheet or in new sheet). Make sure that the last row containing the “Total” data is not included. Click “Ok” on the box and your Pivot table gets created in the next sheet by default. ( you can choose to create it on the same sheet also but that is not advisable).

Now that you have your pivot table created, you can see the basic skeleton of the table on the page and to your right hand side you can see the list of all the column headers from your data table. All you will need to do is to select the respective column headers and put them in columns or rows as per your requirement.

Now let’s try to answer the questions…

Which are the best performing Ad groups ?

Just Drag and drop the following..

AdGroup field to Row Label Box;
Impression, Clicks and Cost field to Values box;

You will see that the Pivot has nicely summarized the impressions, clicks and cost by Adgroups.

You might be wondering why I did not include CTR and average CPC in this because that could have given an even better picture. I left them out intentionally because those would have to be calculated fields, that we will come to at a later part of this article.

Which Campaigns Generate Most Clicks ?

Same process as above, just add the Campaign label to the “Row label” box. Excel is intelligent enough to club all the Ad group data under their respective Campaigns and give you a summarized picture by campaign as well as by ad group.

You can change the order of the fields ( campaign and ad group) by drag and drop in the “Row level” box or by doing a right click on any campaign or ad group name and then “Move” > select the option that you want to use.

Which Placements are registering most Impressions and clicks ?

To the same Pivot that you had just add the “Placement / Keyword” label to the “Rows” box and the “Match Type” label into “Report Filter” Box. Now on the Pivot table Filter for Match Type, click the down arrow and select “Placement Targeted” – it will show you only the placement targeted data. You can make it further easy to analyze by sorting the data based on Impression, clicks or cost. For this, just click on any data from the column based on which you want to sort and then click on the “Sort & Filter” option to your top right. You will get a dialogue box where you can specify how you want the data to be sorted.

Search network or content network – which was more effective?

In the same settings as above from the Match type drop down on top ( Filter), select “Content” and it would show you the data for your Content Network at Campaign and ad Group level.

These are few very simple examples of how Pivot Table can be used. If you play around with the data for some time and have a logical bend of mind you can easily find numerous ways in which you can manipulate simple data tables to effective Pivot Tables that give out tons of useful analytical information.

Now let’s discuss talk about the “Calculated Fields” like CTR and Average CPC. If you put them in similar way as other fields Pivot table would tend to sum it up which is definitely not the right thing to do. So to add these fields to your Pivot table you will need to use the calculated field option.

How to use Calculated Fields ?

“Option” Ribbon > Formulas > Calculated Fields

In the Dialogue box, put the name of the field you want to calculate ( CTR) in the “Name” box and in the “Formula” field, write the formula – ensure that you use the exact headings used in your data table to create the formula, you can also do this by selecting the field names from the box below and clicking “Insert Field”.

Also, while naming the fields make small changes to it ( like adding an extra space, or adding any prefix or suffix) because Excel would not let you create a calculated field for exactly the same heading that you already have in your data table. Because you have CTR field in your data table, you will need to name the field in your Pivot as CTR1 or something similar.

You can use the same process for adding CPC to your pivot table or any other calculated field for that matter.

Calculating Average Position in a Pivot Table

To calculate Average position in a Pivot Table you will need to do a little more work. First in the data table add an extra column and put the formula “Impressions X Average position “ and drag it to have the value for all keywords. Once you have these values, copy them and then do a Paste Special > Values. Let’s name this column as AvgPosTot.

Now in your pivot table add a calculated field for average position and add the formula Avg.PosTot / Impressions . This will show the actual average position at keyword, ad group as well as campaign level.

There are too many things that can be done with Pivot tables and pivot charts and the more you play around with the data the more you will learn and develop on how best this tool can be used to analyze data and get useful information for your search campaign management. Now that I have explained the basics on how a Pivot table can help you with your search marketing data try it yourself and feel free to post any query you might have.