Напишите SQL-запрос, который посчитает конверсию по группам A/B-теста и lift между группами (ClickHouse)?

Короткий ответ

Конверсия считается как отношение числа пользователей с целевым событием к общему числу пользователей в группе; lift — это относительная разница конверсий тестовой группы относительно контрольной.

Как это работает подробнее

сначала агрегируем число уникальных пользователей и число сконвертировавшихся по каждой группе, затем делим одно на другое и сравниваем результат между группами.

Пример кода

example.sqlКопировать

SELECT
    group_name,
    uniqExact(user_id) AS users,
    uniqExactIf(user_id, converted = 1) AS converters,
    uniqExactIf(user_id, converted = 1) / uniqExact(user_id) AS cr
FROM ab_assignments AS a
LEFT JOIN ab_conversions AS c USING (user_id)
GROUP BY group_name
ORDER BY group_name;

Что использовать на практике

• важно считать конверсию на уровне пользователя (DISTINCT user_id), а не на уровне событий, иначе активные пользователи с повторными действиями искажают метрику.
• Ожидаемый результат: строки A и B с cr_A и cr_B; lift = (cr_B - cr_A) / cr_A. Проверка: users в сумме по группам должен совпадать с общим числом назначений в таблице ab_assignments.

Подводные камни

• забывают учитывать пользователей, которые попали в группу, но не совершили ни одного события вообще (их нет в таблице событий) — тогда знаменатель конверсии считается неверно.