Pitfall
Simpson's paradox
A group can be better in every segment and worse overall, when the segments weigh differently.
A total ratio mixes the rate inside each segment with the weight of each segment. If one group has most of its members in
a low segment (mobile, a cheap category), its total falls even when it wins everywhere. Show the ratio per segment and
overall (GROUPING SETS), and look at the weights before trusting the total.
WITH results(device, variant, users, converted) AS (VALUES
('mobile', 'control', 2000, 80), ('mobile', 'treatment', 8000, 400),
('desktop', 'control', 8000, 960), ('desktop', 'treatment', 2000, 260))
SELECT COALESCE(device, 'all') AS device, variant,
ROUND(100.0 * SUM(converted) / SUM(users), 1) AS conversion_pct
FROM results
GROUP BY GROUPING SETS ((device, variant), (variant))
ORDER BY device, variant;
| device | variant | conversion_pct |
|---|---|---|
| all | control | 10.4 |
| all | treatment | 6.6 |
| desktop | control | 12.0 |
| desktop | treatment | 13.0 |
| mobile | control | 4.0 |
| mobile | treatment | 5.0 |