(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:

  1. Our dashboard will provide an overview of all A/B tests we wish to track.
  2. Our dashboard will report on the statistical validity of each test.
  3. Our dashboard will update automatically at an interval of our choosing.
A/B Testing Dashboard

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:

  1. Data source: Select Google AdWords
  2. Select accounts: Choose the AdWords account you wish to pull data from.
  3. Select dates: Select the time period during which the test is/was active.
  4. Select metrics: Select Clicks and Impressions
  5. Split by: Select Ad labels
Supermetrics Query in Google Sheets

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).

Supermetrics Data Dump

Step 4: Create Dashboard

On a separate sheet create the outline for your dashboard.

Dashboard Outline

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.

Dashboard Formulas
Dashboard with Data from Supermetrics Data Dump

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.

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.

Chi Squared Function

Once you have defined the function in the script editor you will be able to reference it in Google Sheets.

Using the chiSquared Formula

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.

Supermetrics Scheduling

Wrapping Up

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.


About the author

Lars C. L. Larsen is an Online Marketing Specialist & Partner at AdNudging.com. You can connect with him on LinkedIn.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.