t-test без боли на чистом SQL

В прошлый раз я поделился лайфхаком для дата-аналитика — как посчитать MDE для A/B-теста простым SQL-запросом. Пост нашел свою аудиторию: судя по статистике telegram его добавили в закладки более 500 раз.

Сегодня хочу развить тему и рассказать, как использовать обычный SQL, чтобы сделать пост-анализ AB при помощи two-sample t-test. Таким образом, вооружившись всего двумя SQL-скриптами (для расчета MDE и t-test), вы сможете проводить A/B-эксперимент от начала до конца — от дизайна до подведения итогов.

Также как и при расчете MDE, наша основная задача — оценить параметры, среднее и дисперсию метрики. Однако теперь мы будем это делать для двух выборок — control и treatment (A и B). На основе средних и дисперсий по этим выборкам считаем t-статистику — и мы в дамках.

Скрипт будет очень похож на тот, что мы использовали для MDE. Но теперь, помимо таблицы dwh.clickstream, откуда будем извлекать значение метрики, нам пригодится таблица с маппингом пользователей на группу. Допустим, эта таблица называется dwh.ab_participant, и в ней два столбца:

1. user_id. Айди пользователя, участника эксперимента;
2. is_treatment — булевый признак принадлежности user_id к тестовой группе. True — значит пользователь в группе treatment (B), False — в группе control (A).

Посмотрим, что получилось:

(Текстовая версия приложена в конце)

Чтобы воспользоваться скриптом, нужно лишь поменять параметры и на места dwh.clickstream и dwh.ab_participant встроить ваши таблички. Обратите внимание, что если вам нужна оценка по ratio-метрике, знаменатель рассчитывается из dwh.clickstream. Но если метрика обычная, то в качестве знаменателя нужно брать количество пользователей в группе: т. о. считаем среднее и избегаем смещения.

Также как и при расчете MDE, мы используем методологию бакетов. О её преимуществах я рассказывал в прошлый раз.

На выходе получаем два числа: t-статистика и количество степеней свободы. Эти параметры — всё что нам нужно для вычисления p-value. Если в синтаксисе вашей БД есть функция CDF t-распределения, то двусторонний p-value можно посчитать как-то так:

(1 - t_cdf(degrees_of_freedom, abs(t_statistic))) * 2

Но если в БД нет таких возможностей, то же самое можно легко сделать в python (scipy.stats.t.cdf). Либо, просто сравните значение t_statistic с классическими порогами стат. значимости:

1. t_statistic < 2. Изменение не стат. значимо (даже не пытайтесь себя убедить в обратном).
2. t_statistic ~ 2. Это соответствует p_value ~ 0.05. Стат. значимо, ну такое. Отвергнуть нулевую гипотезу можно, только если вы изначально целились именно в эту метрику и крутили эксперимент ровно то время, на которое закладывались. Ну и помните, что вероятность false positive 5% — это на самом деле немало.
3. t_statistic ~ 3. Тут p-value уже в районе 0.002. Можно быть более уверенным в стат. значимом результате. Однако, если вы прогнали t-test на сотне-другой метрик, и только одна дала такой результат — то, скорее всего, это тоже случайность.
4. t_statistic > 4. Вероятность false positive около нуля, можно смело отвергать нулевую гипотезу.

Если вы получили стат. значимый результат (отвергли нулевую гипотезу) — рано радоваться. Опытные аналитики знают: чтобы считать результаты валидными, должно быть выполнено ещё несколько условий. Вот самые важные:

• независимость сэмплов treatment и control;
• независимость наблюдений (экспериментальных единиц) друг от друга;
• значение метрики (mean, ratio) распределено нормально. Чтобы соблюсти это условие, размер выборок должен быть достаточно большим (ну хотя бы 30), а также в данных не должно быть сильных выбросов;
• выборки treatment, control не должны быть смещены друг относительно друга еще до начала эксперимента. Чтобы в этом убедиться, часто предварительно проводят A/A тест (или пользуются надежной системой сплитования). Наличие явного смещения также помогает идентифицировать проверка на разницу размеров сэмплов Sample Ratio Mismatch.

Проверку Sample Ratio Mismatch можно сделать с помощью того же самого скрипта, немного его модифицировав:

• исключить таблицу с метриками dwh.clickstream;
• participants_count (pts) использовать в качестве числителя;
• поставить 1 на место знаменателя (1 as den).

Т. е. с помощью t-test мы проверим нулевую гипотезу о равенстве сэмплов. В случае маленьких сэмплов для SRM лучше подходит тест хи-квадрат (который тоже можно сделать в SQL), но это тема другого поста.

Итого, я рассказал вам довольно простой способ подводить итоги экспериментов с помощью простого SQL. Если вы возьмете на вооружение скрипт для t-test и скрипт из предыдущего поста для MDE, то я гарантирую, что вы будете совершать меньше классических ошибок в аналитике экспериментов.

Напоследок, текстовая версия скрипта:

with
-- Параметры ------------------
1000 as NB, -- количество бакетов, любое достаточно большое число
'bucket_salt' as bucket_salt, -- соль для бакетирования, любая строка
date'2024-07-01' as ab_start_date, -- начало эксперимента
date '2024-07-01' as ab_end_date, -- конец эксперимента
-------------------------------
bucketed_data as (
    select
        p.bucket,
        p.is_treatment,
        m.num,
        -- если метрика ratio то в качестве знаменателя берем m.den
        -- если обычная, то знаменатель — количество пользователей
        -- m.den,
        p.pts as den
    from (
        select
            xxHash32(user_id, bucket_salt) % NB as bucket,
            is_treatment,
            count(distinct user_id) as pts
        from dwh.ab_participant
        group by bucket, is_treatment
    ) p
    left join (
        select
             -- user_id — колонка с идентификатором участника эксперимента
            xxHash32(p.user_id, bucket_salt) % NB as bucket,
            p.is_treatment,
            sum(case when event_type = 'payment' then 1 end) as num,
            sum(case when event_type = 'view' then 1 end) as den
        from dwh.clickstream t -- источник данных
        join dwh.ab_participant p on p.user_id = t.user_id
        where t.event_date between ab_start_date and ab_end_date
            and t.event_type in ('payment', 'view')
        group by bucket, is_treatment
    ) m
        on m.bucket = p.bucket
        and m.is_treatment = p.is_treatment
),
aggregated_data as (
    select
        is_treatment,
        sum(num) as num_sum, sum(den) as den_sum,
        sum(num) / NB as num_mean,
        sum(den) / NB as den_mean,
        (sum(num * num) - sum(num) * sum(num) / NB) / (NB - 1) as num_var,
        (sum(den * den) - sum(den) * sum(den) / NB) / (NB - 1) as den_var,
        (sum(num * den) - sum(num) * sum(den) / NB) / (NB - 1) as covar
    from bucketed_data
    group by is_treatment
),
mean_and_variance as (
    select
        is_treatment,
        num_sum, den_sum,
        num_mean / den_mean as mean,
              1                / pow(den_mean, 2) * num_var
            - 2 * num_mean     / pow(den_mean, 3) * covar
            + pow(num_mean, 2) / pow(den_mean, 4) * den_var as
        var -- дельта-метод для оценки дисперсии ratio-метрики
    from aggregated_data
)
select
    treatment_mean, control_mean,
    (treatment_mean - control_mean) / sqrt((treatment_var + control_var) / NB) as t_statistic,
    (NB - 1) * 2 as degrees_of_freedom
from (
    select
        max(case when     is_treatment then mean else 0 end) as treatment_mean,
        max(case when not is_treatment then mean else 0 end) as control_mean,
        max(case when     is_treatment then var else 0 end) as treatment_var,
        max(case when not is_treatment then var else 0 end) as control_var
    from mean_and_variance
) t
;