Identifying content to a/b test using Google Sheets Google Analytics api

There are plenty good calculators to work out test duration for a/b split and multi-variant tests  (optimizely, VWO…) but these tools are based on calculating a single page duration, but what if you are new to a/b testing and are just scoping out what pages you can test on?

Nonlinear Digital, a Sitecore Partner, provided a great excel based a/b testing  duration calculator to throw in your monthly top page/ pageviews (from any analytical software; GA, Adobe or the Sitecore XP analytics). After adding your existing site conversion rate, and your minimum improvement in conversion rate, numbers are calculated and instantly you are shown what pages you can test on.*

*Just because you can test, doesn’t mean you should. Think about what you can optimise, hypothesise it, then validate it.

*The nonlinear calculations are based on 80% significance and identifies possible tests with a duration of less than 30 days. Duration is a topic of debate in CRO land. For me you have to run tests against your business cycles. This might be 30 days, but it might be 100 days.

Using google sheets

I thought it would be a good idea to replicate the excel test duration calculator in google sheets. Utilising the Google Sheets and the Google Analytics api, very simply you can just link your view ID, hit run and the report will be available. No more cutting and pasting just fast free automation.

Even better you can schedule the report too, so you and colleagues can always have access to the latest list of possible pages to test on.

A copy of my Google Sheet is here. Simply open it and make a copy. Also note the data in the final tab is fictional and just there for visual purposes.

Step 1 – get your view ID

In Google Analytics, just select your site name in the hierarchy menu (top left). Expand the menu based on your required account/ property and look at the view you want. The number is below the wording.

Google Analytics – selecting a view ID

In the Google Sheet add this ID to the “Report Configuration” tab (cell B4). Remember to precede the number with “ga:” eg ga:123456789

Adding a view ID in Google Sheets Google Analytics api

Step 2 – run the report

Running a report in Google Sheet with Google Analytics api

Very easy this, click “add-ons” in the menu, then “Google Analytics”, then “run report”. You will also notice schedule is here to in case you want to run this every hour, day, week, month.

Step 3 – configure your test variables

Nonlinear test duration questions

Go to the “Answer Questions on Conversion” tab. Here you have 3 questions to answer:

• Current Conversion Rate
• Expected Improvement from a Test
• Validity (choose 80% or 95%)
• Duration length benchmark

Validity is a new question. Previously it was set at 80%, but now I give you that option to switch it to 95% for a greater test significance. Equally I have added duration length benchmark which will aid the colour coding in the final tab.

Step 4 – see your pages

Nonlinear Sitecore content testing duration calculator results using google sheets

Once you configure your variables, head over to the “Pages where AB testing works” tab. Using conditional formatting you can see the pages that can be ran within your duration benchmark (green) and those that will take longer (red).

Notes

To give this report some further granularity, rather than all pages and the sitewide conversion rate, focus on pages relating to certain goals. For where I work (LJMU, a leading UK University) one key goal is course application conversion rate. What I can do with this report is utilise the filter feature on the “Report Configuration” tab. If I add:

• ga:pagePath=~^/study/courses/

This will just shows pages starting with /study/courses/ in the url (all course pages). I can then alter the conversion rate on the “Answer Questions on Conversion” tab to match the goal in question not just the site wide conversion rate.

I’m very interested to hear peoples thoughts on this a/b test content duration calculator. Thanks of course go to Nonlinear and to Paras Chopra/ VWO for their ground work with the spread sheets and calculation respectively.