Automate Your Power Analysis Using Apps Script

Let Apps Script do The Heavy Lifting For You

Sylvain Zircher | Statistics

Before you run an AB test you really want to understand how long you should be running it for. Power Analysis offers one solution for finding the sample size required, that can then be expressed in a number of days if you know the daily traffic on your website. At the core of Power Analysis is the Minimum Detectable Effect and in this blog post I will share an Apps Script that automates the Sample Size calculations for a list of MDEs to make a Data Driven decision regarding the dimensioning of your split test.

If you want to get started right away , here is a link to a Google Sheets that already contains the Apps Script code and can be used straight away. Also a link here to my GitHub repository with the Apps Script code that you can copy and paste in your own G-Sheets. Associated with the following explanations you will be up and running in no time.

Underlying formula

This blog post will leverage the following formula:

n = f(α/2, β) × [p1 × (100 − p1) + p2 × (100 − p2)] / (p2 − p1)2

f(α, β) = [Φ-1(α) + Φ-1(β)]2

I am referring to this sample size calculator where the reference for the formula is clearly cited: Pocock SJ. Clinical Trials: A Practical Approach. Wiley; 1983.

If you want to find out how you can implement this formula in Google Sheets I have an explanation here.

Setup your Google Sheets first! (boring but important)

The script I am sharing with you assumes you have one sheets called “Automated” that is organised as follows:

Visual summary below:

Set up your Google Sheets first!

Get the code and add it to your Google Sheets

This will take you to my GitHub repository where you will find the code to be used in your Google Sheets.

Next go to your Google Sheets and click Tools > Script editor. Delete what’s in there and paste the code you have just copied.

Automate your power analysis

Hit save and give your project a name. Close the App Script window and refresh your Google Sheets.

Run the code!

You should see after a few seconds an extra menu appearing beside Help, called Sample Size Calculation. Click it and then select Compute Sample Sizes which is going to run the code and output the Sample Sizes and Durations required for each combination of Variant and MDEs you input.

Automate your sample size analysis

Click through the permissions popup to allow your Script to run.

You may see "This app isn't verified", in which case click on "Advanced" and then "Go to [name] project - unsafe" and then click "Allow". These are extra security steps introduced by Google for non-verified scripts. Since we're the author of this script though, it's safe to run.

Here you go! Wait for the script to run (Google Sheets will let you know): it might take a few minutes (it took me a bit less than a minute for around 2k comments).


Find the full code here.

Share this post: