比較實驗轉化和收入提升

WITH participant_outcomes AS (
  SELECT
    experiment_name,
    variant,
    user_id,
    MAX(CASE WHEN event_name = 'experiment_assigned' THEN 1 ELSE 0 END)
      AS was_assigned,
    MAX(CASE WHEN event_name = 'purchase_completed' THEN 1 ELSE 0 END)
      AS converted,
    SUM(CASE
      WHEN event_name = 'purchase_completed' THEN revenue_usd
      ELSE 0.0
    END) AS revenue_usd
  FROM experiment_events
  WHERE timestamp_utc >= now() - INTERVAL '60 days'
  GROUP BY experiment_name, variant, user_id
),
variant_summary AS (
  SELECT
    experiment_name,
    variant,
    COUNT(*) AS participants,
    SUM(converted) AS converted_participants,
    100.0 * SUM(converted) / NULLIF(COUNT(*), 0) AS conversion_rate_pct,
    SUM(revenue_usd) / NULLIF(COUNT(*), 0) AS revenue_per_participant_usd
  FROM participant_outcomes
  WHERE was_assigned = 1
  GROUP BY experiment_name, variant
),
control AS (
  SELECT
    experiment_name,
    conversion_rate_pct AS control_conversion_rate_pct
  FROM variant_summary
  WHERE variant = 'control'
)
SELECT
  summary.experiment_name,
  summary.variant,
  summary.participants,
  summary.converted_participants,
  summary.conversion_rate_pct,
  summary.revenue_per_participant_usd,
  100.0 * (
    summary.conversion_rate_pct - control.control_conversion_rate_pct
  ) / NULLIF(control.control_conversion_rate_pct, 0) AS relative_lift_pct
FROM variant_summary AS summary
JOIN control
  ON control.experiment_name = summary.experiment_name
ORDER BY summary.experiment_name, summary.variant;