I came across a great post earlier this week written by Chad Summerhill of PPC Prospector, detailing an Excel process to visually report Ad Copy Tests. This allowed a user to quickly sort through a fair amount of data and confidently read results based upon the charts. You can see a winner, and ensure the data is normalized. Instead of using tools to read the numbers and report algorithm produced winners, it is much more efficient to display the results in a simple and direct fashion and allows the marketer to react.
It also referenced another spreadsheet created by the author that determined ad test statistical validity based upon confidence & expected performance. This was an important factor, and allowed us to make sure the data we were looking at would be ready to be judged.
While I read through the articles and their sources I started building the Ad Copy Charts, and downloaded the Statistical Validity tool. By following the steps laid out I had a five tab workbook: Charts, Ad Report, Data, ChartData, & Validity.
With a large portion of retail clients, I had to update the charts to reflect metrics that matter to them, and give me the data I need to drive returns. The data I download from AdWords is slightly modified from the default views. It is important to drill-down to a specific ad group. I then replaced View-Through-Conversions with the three Many-Per-Conversion metrics (Count, Cost, & Rate) & Total Conversion Value.
Once you have navigated to a specific ad group select the metrics shown here.
While the early versions looked at two ads over about six week ranges, I quickly realized there was going to need to be some flexibility, but also hard caps. 90 days and four variants seemed to be an appropriate mix that encapsulated nearly all the tests I would be conducting.
You can choose a range of data including 4 different ad variants through a 90 day period and paste into the Ad Report tab.
From the downloaded report, copy all of the data – do not include the headers or totals – into the Ad Reports.
This data directly produces charts to display Click-Through-Rates, (Unique) Conversions-Per-Impressions, Unique Conversions Rates, All Conversion Rates, & Return-On-Ad-Spend.
Charts are produced with no additional effort beyond pasting the data, leaving you time to react to the data as opposed to crunching it.
Now, how it got there is where it gets interesting. Instead of pasting values from tab to tab, I used reference formulas that lead back to the original data. I had to make a leap from the downloaded Ad Report’s straight list of ads, broken out by days, so that unique ads are displayed side-by-side in the Data tab. I used formulas to read the dates to trigger when they reset (assuming that represents a new ad variant).
By creating an index, I used vertical lookups to retrieve the appropriate data and lay out unique ads side-by-side. This fed the cumulative formulas, for each ad, which was passed into the ChartData.
The Ad Report tab recognizes when the dates resets and reads this as a new ad before passing the information to the Data tab.
The ChartData tab allows a user to easily manipulate the charts by hiding columns and rows based upon how much data is being tested when it’s not maxed out at 90 days & four variants (two ads for six weeks for example, or three ads for a week). If you have three ads your chart will be off since you are reading zeros for the fourth. If the relevant data is squished to the left with half of the days being zeros, or 2/3rds of which as shown below, it is very hard to read. By hiding the rows and columns, they are not included in the charts and you can better see the trends emerge.
You can hide data to normalize charts when you are not taking advantage of the max range.
Here we see 31 days of data just after its pasted (1), which when the remaining 59 days of null values are hidden (2), the charts can be easily read (3).
The last tab, Validity is also mostly automated. This sheet was directly downloaded and is a stroke of genius – I merely added some window dressings. While the tab now displays four ads, the computations only take the first two ads as it originally performed. It automatically pulls the stats from the Data, compares them to what the sheet recommends, and provides visual tools throughout the workbook of whether the data inputted in the Ad Report is valid to test. The Validity sheet is also doing a quick ROI analysis on the ad variants. You can adjust the confidence level and expected conversion rate, which will factor into determining the statistically validity.
The Validity tab provides visual cues throughout the worksheet so you can have confidence in the data you are reading.
In essence, you can paste your data into the Ad Report tab, and simply click on the Charts tab and see which ads won and if the results are consistent. In addition you can fine tune the statistical validity of the data you are comparing, and adjust a data set to reflect its scope. This allows us to quickly download the reports see what happened and respond accordingly.
I could not have made such an important tool so quickly without all of the hard work Chad had done. By reading through his articles, and his sources, I was able to take away some new ideas in how to sift through the data. After working in PPC for over 6 years, it’s always good to get a fresh perspective.
You can download the excel file right here (.XLSX file)
This article is written by Chris Kostecki, the PPC Program Manager of Exclusive Concepts’ Profitable PPC service.
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.