Measuring AB Tests: SQL for p-value graphs in Redshift

So, in deciding when an AB test is actionable using p-value graphs we saw some cool charts that looked like:

In these charts the green line is "cumulative p-value" and the red and blue track "conversions".  This example is a "definitely not significant" experiment.

So how do we get our redshift event schema to lead to something that looks like this? Let's take a look at the SQL.

First we need to find our Exposure and Conversion events.

Then we calculate the daily conversions.

Then we make them cumulative using some window functions.

Then some grouping up to make it tidy. 

Finally we run a significance function for each day.

Here's the definition of the significance function from the wonderful Periscope Blog.

And that's it! We use the heck out of Periscope for visualizing and sharing these reports with others. We just need to configure things a little bit in there so that the conversion lines end up on a second y-axis.