How often do you use Pivot tables created from AdWords data? Several times a day, every day? Even if you create a Pivot table from AdWords reports only once a week you probably get as annoyed as me to always have to create the necessary calculated fields for CTR, Conversion Rate, CPC etc.

Let’s say you like to view performance of Google Search vs. Search Partners on a daily basis for all of your campaigns. Sure, you could use the new Report Editor for this task, but you will get into trouble if you manage large scale PPC accounts that spread over more than one account in an MCC environment. Unfortunately the Report Editor is not available for the MCC level, yet. We need to use the MCC reports in that case. But these can only be downloaded as an Excel or CSV-file. Aggregation of the data needs to be done in a Pivot table to actutally see trends.

Manual Pivot Creation

Usually you might create the Pivot table with the following steps all manually:

Download the report and open it in Excel. The report might look like this:

AdWords Campaign Report

For position to be calculated correctly (weighted by Impressions instead of taking the mean) in a Pivot table you need to add an additional helper column to your original data:

AdWords Campaign Report: Position Helper Column

Then you select the table and create a privot table:
AdWords Campaign Report Pivot Table

Create all necessary calculated fields:

Pivot Calculated Field: CTR

Then you see that Excel does not format numbers in a reasonable way:

AdWords Pivot Table: Number Formatting

Now you need to click on all of your current metrics and change their formatting:

AdWords Pivot Table: Number Formatting Percentage

If your are fast, you can do all of the above steps in around five minutes. But why not use a VBA macro to full automatize these steps? The idea here is not only to save you some time but to also enable you to do more quick analysis.

The Code for doing it automatically:

Install the code

  1. Copy the code.
  2. In Excel press “Alt + F11” to open the VBA editor.
  3. Create a new Module in your current workbook.
  4. Paste the code into the module (If you already set up Excel’s highly recommended PERSONAL.XSLB file paste the code into a module in that file to have the macro available in all of your Worksheets. See Microsoft’s Support page on how to set up PERSONAL.XLSB)

How to use the code

  1. Download a report from AdWords with at least these metrics:
    • Impressions
    • Clicks
    • Conversions
    • Cost
    • Average Position
  2. Your report’s column names need to be English.
  3. All column headlines need to be in the first row: Delete all rows above the column headlines (usually that is one row in a report directly downloaded from the interface and five rows if you got the report from MCC report center).
  4. Delete the “Total columns” at the end of the report.
  5. Press “Alt + F8” to open the Macro overview.
  6. Run the macro.
  7. The result will be a new Sheet with a pivot table fully formatted and with all necessary columns and calculated fields:
    Adwords Pivot Macro Result

From this point on you can easily put Dimensions or other useful metrics into your Pivot table.