Using Google Sheets as a backend
for AB testing

It’s often joked about by the Tech team, that I’m trying to use Google Sheets/Forms to build a new Secret Escapes.

This month, I’ve begun trying out how we can use Google Sheets to make changes on the front end that would usually need to go into the database. One example is sale titles. Usually we use sale titles which are descriptive, as opposed to inspirational. A Marriott hotel in Paris, would be called “Marriott Hotel Paris” – we wanted to try changing all sale names, so they are something more like “Luxury 4* Hotel with view of the Eiffel Tower”.

Usually, we’d make a change in our CMS and Database to include 2 sale titles, and then toggle what was shown depending on AB test conditions. This allows our editors to make changes on the fly, and not have Tech push changes. Another option would have been a static JSON or CSV provided on an occasional basis, but this requires both regular Tech updates and ensuring they are kept up to date is a task with overhead.

So – I decided to use Google Sheets as a makeshift CMS, and then use it to create a JSON feed which could then be pulled into Optimizely to manipulate the front-end using Jquery.

I set up a Google sheet with the internal sale reference ID, and the new name we wanted to call it. Then I set it to be publicly available, so we could access the JSON. Obviously as it’s publicly accessible, you don’t want any personally¬†identifiable information in there – but for this experiment it’s fine. All this is accessible on the website, anyway.

The Google Sheet, with the internal ID of the sale we wish to change and the new name

The Google Sheet, with the internal ID of the sale we wish to change and the new name

Next up was to set-up the Optimizely experiment, the JS needed to pull this information through is actually remarkably simple.

The JS written to pull through the spreadsheet as a JSON file and change the sale card.

The JS written to pull through the spreadsheet as a JSON file and change the sale card.

I spent some time setting up the firing rules, and testing it. But voilà, the new names pull through on our page

The updated sale card on our sales listing page

The updated sale card on our sales listing page

Obviously this should never be a long term solution or alternative to a DB or CMS. But as a quick way to allow editable data in an AB test for none technical users, it’s perfect.

One Comment

  1. Fantastic idea, Thomas! I’d love to connect with you about this and have some input / best practices that I can share with you on this type of testing. Shoot me an email if you’re interested.

Leave a Reply

Your email address will not be published. Required fields are marked *

*