This is a guest post by Chad Summerhill, Author of the blog PPC Prospector, provider of free PPC tools & PPC tutorials, and AdWords Specialist at Moving Solutions, Inc. (UPack.com and MoveBuilder.com).
Over the past few weeks I’ve been seeing more discussion about the math behind our ad testing efforts. Can we trust our results? Is it valid? We don’t want to blindly make the wrong choice, and it’s hard to feel confident about math we don’t really understand.
I remember searching, with some difficulty, for information on how to calculate statistical significance for an ad test. Then I came across some good information on marketing experiments.
After finding this resource, I created a spreadsheet method for ad testing with confidence (95% to be exact). I guess I wasn’t the only one who could use a little confidence boost, because about 100 PPCers download this spreadsheet each month.
So, I thought that I would share one more way you can add a layer of confidence to your ad testing. By charting the cumulative totals, by day, of the metric you are trying to improve (CTR, Conv. Rate (CVR), or Conversion-Per-Impression (I2C)).
Visualizing the cumulative totals allows you to see erratic behavior from your test ads that might have otherwise gone unnoticed. I first saw cumulative totals being used in testing a few years ago when Offermatica (landing page testing software bought by Omniture) came on the scene.
You might see some wildly differing results at the beginning of a test, but as the test continues to run each ad’s performance levels out and becomes more consistent. If it doesn’t, then you probably need to let it run longer. As your sample size increases, variables outside the scope of your test (time, geographies, operating systems, etc.) will become more evenly distributed across your ad variations.
Visualizing cumulative totals is very easy to do in Excel, but you have to get the right data first. You can’t segment your ads by ‘day’ in the AdWords interface, but you can download a segmented report.
Getting your ad testing data:
- Select the ad group you want to analyze from the tree menu in the AdWords interface.
- Click on the ‘Ads’ tab.
- Choose the appropriate date range for your test.
- Select these columns at a minimum (Clicks, Impressions, & Conversions).
- Click on the download icon.
- Click on ‘Add segment’ & choose ‘Day’.
- Click on ‘Create’.
- Open your file in Excel.
One you have your ad data in Excel, you need to prepare it for analysis. You will have to move some data around and add a simple formula.
Preparing your data for visualization:
- Highlight your data and ‘Format as Table’.
- Filter for your champion ad.
- Copy your champion ad’s data (including the header row).
- Paste into a new worksheet.
- Go back and filter for your challenger ad (I’m assuming a two ad test).
- Copy your challenger ad’s data (including the header row).
- Paste into the cell directly to the right of your champion ad’s data.
- Delete the ‘Date’ column for your challenger ad as long as it lines up with the dates from your champion.
- Create your cumulative metrics (Champ_CUM_CTR, Champ_CUM_CVR, Champ_CUM_I2C, Challenger_CUM_CTR, Challenger_CUM_CVR, & Challenger_CUM_I2C) using a formula like: =(SUM($H$2:H2)/SUM($I$2:I2))
- For Champ_CUM_CTR use the formula above in the first free cell to the right of your data. $H$2 = your first Clicks value for your Champion and by using the $ you lock the first value in the formula. When you copy the formula down it will calculate a cumulative total.
- Repeat for the other cumulative metrics above.
You should now have your challenger ad’s data right next to your champion ad’s data (make sure the dates line up). You want one row of data with each ad’s performance for each day of the test.
Your data should look something like the image above.
- Copy all of your data & Paste Special > Values in a new worksheet.
- Delete all of the columns except the date and your new cumulative totals.
- Reformat your data appropriately (i.e. the date column formatted as a date).
- ‘Format as Table’ again.
Now you’re ready to visualize your cumulative test metrics using multiple Excel line charts.
Visualizing your cumulative metrics:
- Select any cell in your new table and click ‘Line Chart’ from the ‘Insert’ menu in Excel.
- Copy your new line chart and paste it twice in your worksheet, so you have three copies.
- Delete everything but CTR from the first chart.
- Delete everything but CVR from the second chart.
- Delete everything but I2C from the last chart.
As you can see from the charts above, the results at the beginning of the test were quite erratic. It would have been unwise to pick a winner within the first two weeks of the test. By the end of the third week, you start to see a stable trend and your statistical check should confirm that your sample size is big enough to pick a winner (for this particular test, some test may take longer).
By adding a visualization of your ad tests, you add another layer of reinforcement for your decision making. When you see your cumulative metrics diverge and stabilize after the first couple of weeks you can be fairly confident that the trend will not reverse.
Opinions expressed in the article are those of the guest author and not necessarily Certified Knowledge. If you would like to write for Certified Knowledge, please let us know.