milestone 4/4 Experiment conversion denominators

Choose the correct population for experiment conversion rates.

Social Product Analytics · Clean event retries, reconstruct ordered funnels, sessionize activity, and compare experiment conversions.

approach

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.

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;

data for this milestone