View your Google Ads creatives as a Google Optimize website experiment

The idea

Doing A/B experiments with text ads and other creatives in Google Ads is getting less important with the introduction of Responsive Search Ads. But that doesn’t mean that it’s gone completely. It’s still relevant to optimise text ads based on data, just as we should never stop optimising our websites. At Adwise, we usually do this with a Google Ads script that is an improved version of the A/B testing script by Russ Savage from or by simply calculating for a statistical significant difference manually. This works, but I feel it lacks a graphical component to clarify the outcome.

The Google Optimize interface on the other hand contains a beautiful graph to visualise the results of an experiment.

The Google Optimize interface, image by Google.

The graph shows the cumulative conversion rate of a variant together with a confidence interval over time. It provides a clear overview of the experiment and makes it easy to compare and interpret the statistical differences between each variant.

Wouldn’t this graph be perfect for other tests and experiments as well? What about using this graph to compare the results of different Google Ads text ads? I think it’s worth trying out, so I created a little “proof of concept” that looks like the image below. It’s also interactive! Try for yourself here!

An example graph made with Bokeh in Google Colab

I created this graph using Bokeh. I found a Bokeh example that provided a good starting point for building my own graph in a (Jupyter) Notebook. I also opted to use Google Colaboratory, because I could create my notebook online and have easy access to Google Drive. This is convenient, because this would open the possibility to use Google Sheets with the Google Analytics plugin to retrieve data about Google Ads text ads. If you’ve lost me here, I understand! I’ll go over the steps one by one below. The important thing to note here is that I use only freely available tools so you can recreate (and probably improve) my idea.

The math

Statistics was not my favourite subject in high school, but I ended up in an analytical job anyway. 😉 If I’m not mistaken, we should calculate a binomial confidence interval. A user can either click or not click. Or a user can either convert or not convert. So there are always two possible outcomes, which brings us to a binomial distribution.

Build your own version of this graph

Step 1: Copy the example spreadsheet

Make a copy of this spreadsheet:
The copy should also include the Google Analytics plugin, which you can use to retrieve data about your Google Ads text ads from your Google Analytics account. You should change the value in cell B3 to your own Analytics profile ID and change the filter in cell B9 on the sheet named ‘Report Configuration’ to the name of an ad group in your Google Ads account. You can also make other changes if needed. Then run the report to refresh the data. If you’re not familiar using the Analytics plugin, you can read about it at the Google Developers website.

Step 2: Copy the Colab notebook

Then make copy of this Google Colaboratory notebook:
After making a copy check if the first cell contains the name of the spreasheet you created in step 1 and edit if necessary. Then you can run all the cells. At cell 3 you’ll be presented with a URL to authenticate and connect Google Drive. Click the URL and follow the steps. You’ll receive a code that you can paste in the new field that appeared in your notebook.

Step 3: check out your awesome graph.

If everything worked the way it should, you should see an interactive graph at the bottom of the notebook that looks similar to the graph in the screenshot above. There are probably easier ways to get the same kind of graph, but I chose to use free tools that require some extra steps. It’s not perfect, but I guess it’s a MVP that can be improved upon. 🙂

Anyway, I’m eager to hear what you think about using this type of graph to analyse the results of Google Ads text ads!

Pushing Google Sheets content to BigQuery

I’ve decided to dust off my blog. After several years of neglect, I thought it would be nice to try to do something useful with it again. For my little coding projects, I combine and use lots of code from others. I guess the least I can do is try to share some of my code and ideas in return.

But first I should probably warn you: I’m not an educated developer. Most of my coding skills are self-taught through websites such as CodeCademy, Udemy, and DataCamp, among others. So if you have any remarks, improvements or tips, please feel free to reach out.

How to push data from Google Sheets to Google BigQuery

The problem: At work, we have access to a Google Sheets spreadsheet with data that is refreshed daily (our agency Google Ads HI Score spreadsheet for those who are familiar with it). Unfortunately, we are not in control of the spreadsheet. So if we want to save each day’s content we must copy it to another sheet. However, if you do this daily, you quickly reach limits such as the maximum number of cells a spreadsheet can contain. That’s where Google BigQuery comes in.

The solution: With Google Apps Scripts we can access the BigQuery API to push data to BigQuery. We can also use Google Apps Scripts to access the content of Google Sheets. By combining these two it’s possible to push data from Google Sheets directly to Google BigQuery.

There are several examples to be found on how to push a CSV file to Big Query using Google Apps Scripts. There are also several blogs about downloading BigQuery results to a Google Sheets spreadsheet. We want to do the opposite though, and blogs and examples about pushing the content of a Google Sheets spreadsheet to Google BigQuery are more sparse. At least, I didn’t find any (free) useful examples. That’s why I wrote something that is based on two examples that sort-of do what I was looking for, but I adapted them to my needs.

I used these examples:

This script can be used as a Google Apps Script. The easiest way is to open the Google Sheet and go to the script editor through the menu bar.

In the script editor, you must enable the BigQuery API through the Advanced Google Services. You can then set a schedule for the Google Apps Script to fetch the content of the Spreadsheet and push it to Google BigQuery.

You can find the code below or check the repository at GitHub.