PPC Management and Data Analysis Made Easier in 4 Steps

This article was originally published by Sam James, a former employee of Point It.  Due to its popularity, we’ve assigned it to someone new to ensure that we keep this information up to date.  If you have any questions, please reach out to brennat@pointit.com

One of my favorite toys for effective PPC management is Excel, which may be linked to my general enthusiasm for math and marketing. In my years of managing accounts, I’ve found several tricks that make it much simpler to pull out actions from large data sets, and cut through a lot of “analysis paralysis”, while still feeling confident in the numbers.

Many of these tips are faster to implement, if you are comfortable with pivot tables. Else, the handy SUMIFS function can be used in its place. The use of these won’t be covered here, though I’m always willing to create demonstrations, so please feel free to reach out.


 Step 1 – Make your campaign and ad group names standardized and useful.

Ideally, a campaign or ad group name should be easy to read by the marketer and those the marketer is reporting to, while also using characters which make breaking the bits of descriptive in the name into columns for aggregating and slicing data.

For example, let us say we have an account that sells clothing. Our store sells 4 major brands, a smattering of others, with various types and colors, etc… We may choose to have each clothing type as a campaign, and then each ad group signifies the brand, style, etc…

Note how I’ve used one character to indicate the spaces, while using another character to separate the elements of brand from style. This second character is what I will use to make slicing the data in a moment, much easier.

PPC Management Campaign and Ad Group Naming ConvetionsDepending on the complexity of the account, one could also choose something like:

PPC Management Campaign and Ad Group Naming Convetions

Step 2 – Establish series of ad variations by value propositions or language choices.

 When you run ad copy testing, especially in accounts with low volume or where time is limited, choose a few, easy to identify value propositions or language style choices to test, and run them in as many places as make sense. These will later have an indicator column of their own in your spreadsheet, which you can use to aggregate across ad groups or campaigns, for faster analysis.

For example, let us say that our fictional store has free shipping for purchases over $50, express delivery options, and a sale every Tuesday. Some ad variations across the campaigns could look like this:

PPC Management Ad Copy Testing TemplatingNote that every ad has a template that could be used across clothing styles and brands, and that each ends with a value proposition that would be easy to identify. Sometimes, one can’t fit such a clear distinction into an ad and remain relevant, but as long as the difference you are testing can be easily spotted, then the next step will still be fast to implement.


Step 3 – Prep your data for analysis.

 This step is going to be a bit clearer with some extra visuals. After you export your data, open it in Excel. I recommend exporting as few calculated metrics as possible, with the exception of average position. The calculated metrics will be handled by the pivot table setup.

A sample export might look like this:

PPC Management Importing Data

Now, we take a few “Text-to-Columns” actions. While this step may seem the longest, if done wisely, we can set many of these steps up once, save our file as a template, and take far fewer actions in the future.

1. Label a new column in the first empty column after the data import (here, it would be column M), indicating it as an average position helper metric, in any way you prefer to call it. This will be used to calculate average positions, as we are aggregating data. The formula under that label should be the impressions of that row multiplied by the average position of that row.

PPC Management Recalculating Average Position(In the pivot table, the average position will be recalculated as a new field, and the formula for that will be that average position helper metric divided by the impressions.)

2. Label the next two (or more) columns (here, columns N and O) in the data import sheet, to match the elements that are described in the campaign naming structure. In our example, those two columns would be something like “Clothing Type” and “Brand Name”.

3. Similarly to the above point, label the next couple of columns (columns P and Q) to indicate the elements in the ad group name. In this case, “Style” and “Color”.

4. Copy the contents, without the header, of the campaign names into the first column header for the column elements (here, that would be column N, labeled “Clothing Type”). With the pasted campaign names selected, choose to do a Text-to-Columns action, delimiting the data by the character you’ve chosen to use to separate elements in the naming convention. In this case, we are delimiting the data using the hyphen (-). This will leave only the clothing types in the “Clothing Type” column, and place the brands in the “Brand Name” column.

5. As a last import data prep step, if the ad copy variations are not clearly distinguishable in a single description line, you’ll want an extra column where you indicate that. Label the next empty column to indicate ad variations, and then use filters to help fill in that information.

When completed, the import sheet might look like this:

PPC Management Text to Columns

6. Now, we pivot this data, and set our calculated metrics. Start by highlighting all of the columns, and then inserting a pivot table off of this data.

7. Create the average position metric, and any other metrics that matter to your decision-making process.

PPC Management Pivot Table Calculated Fields8. Save the file. You can reuse this, and just paste over the data in the import sheet, being sure to use the same export columns.

Step 4 – Slice the data and take action

Analyzing the data become a joy, at this point. You can evaluate performance metrics by any element or testing aspect you like, quickly, just by dragging and dropping those elements into the pivot table’s “Row Labels” section. In the below example, I am taking a look at how the various value propositions were performed by color of the clothing item.

PPC Management Pivot Table Data Analysis

Thank you for your time, and I hope you find this as helpful as I do, on a daily basis.

Brenna Teichen About the author
  • Great post Sam! I think I need to do some renaming and cleanup of Campaign and Ad Group names to really put this into action. But now I have a plan… 🙂

    April 27, 2012 at 8:15 am

Leave a Comment: