Query of the Week — AMC SQL explained in context

1 · Business question

Does a test campaign actually lift conversion rate vs. a control group (incrementality)?

2 · Query (SQL)

SELECT
  test_group,
  COUNT(DISTINCT user_id) AS users,
  COUNT(DISTINCT CASE WHEN purchased THEN user_id END) AS converters,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN purchased THEN user_id END)
        / NULLIF(COUNT(DISTINCT user_id), 0), 2) AS cvr_pct
FROM amazon_sponsored_ads
GROUP BY test_group;
-- Lift % = cvr(test) - cvr(control) over the two test_group rows.

3 · Interpretation

Lift near zero means conversions would have happened anyway (not incremental). Only significantly positive lift justifies scaling.