(Semi) Automating A/B Testing with Google Sheets and Supermetrics
A key tenet of digital marketing is testing. Indeed, in many respects digital marketing (when done right) is one giant, ongoing experiment. Keeping track of that experiment, however, can be overwhelming, in particular for marketers who manage multiple clients/accounts.
For the AdWords marketer, continuous testing of ad copy is one of the most important experiments that can be run for improving account performance. Below I detail one approach to keep track of all those A/B tests you are running. You are running A/B tests, right?
A/B Testing Dashboard
Let’s start at the end by taking a look at the finished product and breaking down it’s functionality:
- Our dashboard will provide an overview of all A/B tests we wish to track.
- Our dashboard will report on the statistical validity of each test.
- Our dashboard will update automatically at an interval of our choosing.
Step 1: Purchase and Install Supermetrics for Google Drive
Supermetrics is a data grabber that allows you to fetch data from a growing number of APIs including AdWords, Google Analytics, Bing Ads & Facebook. There is a free version but functionality is limited.
Step 2: Label Your Ads
The second, and most important, step is to label the ads you wish to track. The labels will allow you to use Supermetrics to extract a variety of AdWords metrics for each ad that has the label applied to it.
Step 3: Create Supermetrics Query in Google Sheets
Once your ads are labeled you are ready to create your Supermetrics query in Google Sheets. Below is a breakdown of the various query parameters that need to be filled out:
- Data source: Select Google AdWords
- Select accounts: Choose the AdWords account you wish to pull data from.
- Select dates: Select the time period during which the test is/was active.
- Select metrics: Select Clicks and Impressions
- Split by: Select Ad labels
Once the required parameters have been selected, click the blue Get Data to Table button. The result is a data dump, which will be the raw input to your dashboard (by default Supermetrics will fetch data for all the labels in our account).
Step 4: Create Dashboard
On a separate sheet create the outline for your dashboard.
We will use vlookup formulas to populate the dashboard with data from the Supermetrics data dump. The main benefit of using vlookup formulas is that it will allow you to update your dashboard on the fly. For example, if you want to include another test in your dashboard, simply add the label names in the Labels column and drag the formula down. Provided you have defined your data range correctly, your dashboard will update with the appropriate data.
Step 5: Create Custom Formula to Calculate Statistical Significance
In order to facilitate the statistical significance calculation, we will define a custom function using Google Sheets’ built-in script editor.
The statistical test we will be using is called a chi-squared test. We will set the confidence interval at 95%. I won’t go into details about the test, but if you are interested in learning more about calculating statistical significance see the following blog post by Avinash Kaushik.
Once you have defined the function in the script editor you will be able to reference it in Google Sheets.
Step 6: Schedule Automatic Updates
With your dashboard now in place, the final step is to create a refresh schedule. Depending on your needs, this can be anywhere from hourly to monthly.
Hopefully you now have your very own A/B Testing Dashboard in place. You can access the file I used for this post here. The file is view only, but you can create a copy which will allow you to make edits.