lesson 58 Advanced product analysis · advanced

Experiment conversion denominators

Choose the correct population for experiment conversion rates.

the idea

Compare assigned members, including people who do nothing. A conversion is at least one purchase on or after assignment, through Jan 31. Counting purchases instead of members or using only active members biases the denominator. This query estimates rates, not statistical significance.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

social_events6 columns · 22 rows

idinteger · key	event_keytext	member_idinteger	event_typetext	event_attext	ingested_attext
1	a	1	view	2025-01-01 10:00:00	2025-01-01 10:00:01
2	b	1	signup	2025-01-01 10:03:00	2025-01-01 10:03:01
3	c	1	purchase	2025-01-01 10:05:00	2025-01-01 10:05:01
4	a	1	view	2025-01-01 10:00:00	2025-01-01 10:10:00
5	d	2	view	2025-01-02 09:00:00	2025-01-02 09:00:01

showing 5 of 22 rows

experiment_assignments3 columns · 8 rows

member_idinteger · key	varianttext	assigned_attext
1	control	2025-01-01 09:00:00
2	control	2025-01-02 08:00:00
3	treatment	2025-01-03 08:30:00
4	treatment	2025-01-04 09:00:00
5	control	2025-01-05 10:00:00

showing 5 of 8 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

WITH results AS(
  SELECT
    a.member_id,
    a.variant,
    CASE
      WHEN EXISTS(
        SELECT 1
        FROM social_events e
        WHERE e.member_id=a.member_id
          AND e.event_type='purchase'
          AND e.event_at>=a.assigned_at
          AND e.event_at<'2025-02-01'
      ) THEN 1
      ELSE 0
    END AS converted
  FROM experiment_assignments a
)
SELECT
  variant,
  COUNT(*) AS assigned,
  SUM(converted) AS converted,
  ROUND(100.0*SUM(converted)/COUNT(*),2) AS conversion_pct
FROM results
GROUP BY variant;

Watch for: A higher observed conversion rate alone does not establish a significant treatment effect.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
See assignment and conversion pieces. Experiment math needs a clear denominator: assigned members per variant. Conversion checks for a later success event.
step-1.sql
sql›
```
SELECT member_id, variant, assigned_at
FROM experiment_assignments
ORDER BY variant, member_id;
```

Keep the assignment denominator. Count assigned members first, then mark who converted. Filtering to converters before counting shrinks the denominator incorrectly.

step-2.sql

WITH results AS(
  SELECT
    a.member_id,
    a.variant,
    CASE
      WHEN EXISTS(
        SELECT 1
        FROM social_events e
        WHERE e.member_id=a.member_id
          AND e.event_type='purchase'
          AND e.event_at>=a.assigned_at
          AND e.event_at<'2025-02-01'
      ) THEN 1
      ELSE 0
    END AS converted
  FROM experiment_assignments a
)
SELECT
  variant,
  COUNT(*) AS assigned,
  SUM(converted) AS converted,
  ROUND(100.0*SUM(converted)/COUNT(*),2) AS conversion_pct
FROM results
GROUP BY variant;