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:

P-Value graphIn 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. 

Screenshot of Periscope

 

Tags: experiments, data warehousing, redshift, sql

Work With Us

We’re always looking for highly skilled full stack engineers to help execute our technology goals while riding this rocket ship of growth. Our people are terrifically talented, friendly people who love what they do. And everyone is generous and kind, too — we have a strict no jerk policy.

View ezCater Opportunities

About ezCater

We're the #1 online – and the only nationwide – marketplace for business catering in the United States. We make it easy to order food online for your office. From routine office lunches to offsite client meetings, from 5 to 2,000 people, we have a solution for you. ezCater connects business people with over 50,000 reliable local caterers and restaurants across the U.S.

ezCater is hiring!

We’re always looking for highly skilled full stack and iOS engineers to help execute our technology goals while riding this rocket ship of growth. Our people are terrifically talented straight-shooters who love what they do. And everyone is generous and kind, too — we have a strict no jerk policy.

View ezCater Opportunities

Recent Posts

Subscribe to Email Updates