Using Excel Pivot Tables For Efficient PPC Ad Testing

Error: Unable to create directory D:\Hosting\4482607\html/wp-content/uploads/2018/12. Is its parent directory writable by the server?

harryhuxford | Thursday, October 14th, 2010

Filed under: PPC Tips & Advice

Follow me on Twitter

Add to Mixx!

Managing and optimizing large sets of text ads can be cumbersome and tedious, especially if you’re conducting campaign wide A/B tests. Excel Pivot Tables can help simplify the process by automatically aggregating performance statistics across a large number of ads to make ad copy optimization easier and more insightful.

Creating The Pivot Table

The first step is to run an Ad Performance Report on the campaigns and ad groups whose ads you want to optimize and open it up in Excel. For the purpose of this exercise, I’m running ads for an online candy vendor that promotes caramel apples. I want to test if varying the headlines of the ads leads to better performance in four different ad copy variations. To keep things simple and controlled, description line 1, the display URL and the destination URL are uniform across all ads. The only variances are in the headlines and description line 2.

Create an ad performance report and open it in Excel.

Create an ad performance report and open it in Excel.

The second step is to highlight all your data with Control + A, select ‘Insert’ from the tool bar and choose ‘PivotTable’. You will then need to check off the columns in the ‘PivotTable Field List’ window to be included in your Pivot Table.

Highlight all your data and select 'Insert' from the toolbar and choose 'PivotTable'.

Highlight all your data and select 'Insert' from the toolbar and choose 'PivotTable'.

The end result will look something like this:

A polished ad performance report in a Pivot Table.

A polished ad performance report in a Pivot Table.

Note that any derived statistic like CTR, Cost/Conversion and Conversion Rate will need to be corrected as the Pivot Table will merely sum these stats over all your ads (ie, two ads with CTRs of 1.50% and 1.60% will be incorrectly summed to total 3.10% instead of being correctly averaged to 1.55%). You can easily correct this problem by inputting simple formulas in the first row of each column and ‘dragging’ the formula down the whole column (the formula to calculate CTR in cell E2 is “C2/D2”; the formula to calculate Cost/Conversion in cell H2 is “F2/G2”; the formula to calculate Conversion Rate in cell I2 is “G2/C2”, then drag the formula down the columns to populate subsequent cells).


Looking at the final data, I can see clearly that “Creamy Caramel Apples” is the most converting headline across my 4 ad variations with a conversion rate of 7.04% while “Fresh Caramel Apples” has the lowest conversion rate of 4.22%. In addition, I’m able to see an “interaction effect” for each description line 2. The description line “Get A Pound For $20. Free Shipping!” converts most when coupled with the headline “Creamy Caramel Apples” (9.88% conversion rate) and converts the least when coupled with the headline “Crunchy Caramel Apples” (4.94% conversion rate).

Want advice from MySEMexperts
about your PPC, SEO or Social Media Marketing?
Contact us for a

Add This To Your PPC Toolbox

While my example above was small scale, using this method becomes more useful the larger your data set becomes and is invaluable when analyzing the performance of hundreds of ads simultaneously. You can also play around with which ad copy elements you want to test against each other. For this exercise I tested headlines against description line 2’s but you can also choose to test description line 1 against line 2 or headline against display URL among other options. Take a look at your text ads and see where this analysis can fit in and provide insight.

Do you have any other insightful methods for ad A/B testing and analysis? Please let me know in the comments!

Tags: , , , , ,

Daily Tips & Advice

Sign up to get our FREE PPC, SEO & Social Media tip of the day delivered directly to your inbox.


Leave a Reply