lesson 58 Advanced product analysis · advanced

Experiment conversion denominators

Choose the correct population for experiment conversion rates.

the idea

An experiment’s conversion rate divides converters by everyone assigned to the variant, not by users who happened to be active afterwards. A conversion counts only if it happens at or after the user’s assignment and before the measurement window ends.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Count users per variant. Run this. assigned is everyone split into A or B for experiment 6—the denominator for conversion rate.the pattern

   example.sql Run

   sql›

   SELECT x.experiment_key, a.variant, COUNT(*) AS assigned
   FROM experiment_assignments a
   JOIN experiments x USING (experiment_id)
   WHERE x.experiment_id = 6
   GROUP BY x.experiment_key, a.variant;

2. 2

   See a biased denominator. Run this. Inner-joining conversions drops users who never created a report, so both variants look almost 100% converted.

   step-2.sql Run

   sql›

   SELECT a.variant, COUNT(DISTINCT a.user_id) AS users_seen
   FROM experiment_assignments a
   JOIN events e
     ON e.user_id = a.user_id
     AND e.event_name = 'report_created'
     AND e.occurred_at >= a.assigned_at
   WHERE a.experiment_id = 6
   GROUP BY a.variant;

common mistake

Inner-joining assignments to conversion events drops assigned users who never converted, so both variants look close to 100%.