Automated Optimizely Reporting Using Google Sheets

Automation is more than just a buzz-word. It’s a gold mine for efficiency and accuracy. It’s critically important for Conversion Optimisation Specialists, especially at the enterprise level where you’d rather spend more time on anything but pulling data from Optimizely to do your reporting. So here’s a spreadsheet that will save you a huge amount of time by letting you pull in data in a vlookup friendly format. This leads to faster and more accurate reporting by allowing you to:

1. Cut reporting time by getting your experiment data without sifting through each experiment.
2. Build a dashboard and never have to worry about collecting data ever again.
3. Eliminate the possibility of human error when collecting experiment data.

But first, I have to give credit where it’s due. Thanks to Trevor Fox of Swell Path for creating the original spreadsheet report and scripts that mine are based upon and Krishan Munthree for his wisdom and for basically teaching me everything I know and for putting up with me.

Want to skip straight to the spreadsheet? Download Now

Quick Start Guide

If you’re a whiz with Google Sheets, Apps Scripts and the Optimizley API then feel free to skip the next few paragraphs. However, what you should know is:

1. 1. By default, the spreadsheet pulls data for all experiments that are “Running” and from one particular project ID.
   2. To get your data, click on the “Optimizely Auto Report Menu” in the nav and hit “Pull Data” – the numbers will start printing in the tab labelled “Data”. It’s that easy.
   3. To automate – refer to use case 3.

Main Improvements

Here are the main improvements that I’ve made on Trevor’s original spreadsheet:

Enterprise Level Friendly

The previous version of the script would time out if you were pulling data for a large number of experiments. Also, the script would previously create one tab per experiment. This would quickly get out of hand for enterprise level clients. The data is now printed in a single tab for your OCD-like convenience.

Excel & Vlookup Friendly

The way the printed data was structured didn’t allow for efficient use of vlookups – which lead to efficient report/dashboard building. As either a spreadsheet, data or reporting nerd – this was a big no no. Another issue pointed out by a user was that this method would quickly lead to the spreadsheet hitting a cell or row limit.
The newer version prints the data with unique identifiers in each row based on the experiment ID, goal name and variation name. That means you can use VLookups, which means faster reporting and easier dashboard building. Hurray!

The above is an example of data from one experiment printed in the “Data” tab. The red circle is an example of a unique reference made from the ID of an experiment, the particular goal and the particular variation. 

Faster Script

Previously, the script was making several external calls to Optimizely (within for loops) and this increased the time taken for the script, often leading to the script timing out. Similarly, the makeSheetsReport function was looking through every experiment in the user’s account, making the script run slower (most of the time, we’ll only need to see active or running experiments).
This version cut down several unnecessary steps from the previous version and also reduced the amount of external calls to Optimizely, making the script run faster and not time out.

Extra Use Cases

Use Case 1: Different reports for different projects – change the project ID

To change which project that data is printed from, change cell C4 in the Sheet “Report Configuration” to whichever Project ID you desire.

Use Case 2: Only pull data for specific group of tests

Let’s face it, it’s no secret that half of the time we use Optimizely as a secondary CMS to keep site changes live even after a test has run it’s course. So, these experiments would fall under “Running”, but you wouldn’t necessarily need the results for ALL “running” experiments. To print data for only a certain group of experiments, here are the changes you need to make in the script:
First, add your list of experiment ID’s as a variable and uncomment the variable definition.


Then, uncomment the IF statement.

Use Case 3: Get real time data for your tests

This is probably one of the most useful use cases for this spreadsheet. Imaging you’re running a really important test that is critical to the business and you need real time data. Examples include:
– A site migration where you’re testing the old site vs a new variation
– A test on your E-commerce site’s cart or checkout page, where every little % is important to the value of your business
Step 1: Set the experiment(s) that you’re pulling data from
Refer to use case 2.
Step 2: Automate the “makeSheetsReport” Function
So that the script will run periodically (I would recommend not doing this more frequently than every 10 minutes). Google has a limit on Computer Time per user and you could very well exceed this if you’re running other scripts or automation magic. I’ll attach the instructions that Trevor originally provided, which were very succinct and simple to follow.
Trevor’s instructions for installing triggers:


Step 3: Build a dashboard
I’ll let you worry about the dashboard and data visualization (which should be the easy part since the data is vlookup friendly!)

Download The Spreadsheet 

What’s Next?

What I’ve provided is still far from perfect – I’m sure one of you reading this can bring it to the next level. Let us know if you happen to do so!
There are some many other use cases and small points that I could’ve covered, but it just wouldn’t have made sense to cram it all into this blog post. Post any questions you have below in the comments and I’ll try to answer them to the best of my ability.