Модуль 7. Эксперименты, атрибуция, прогнозирование и ML-паттерны на ClickHouse
После того как слой витрин стабилен (Модули 0–6), появляется запрос «сделайте, чтобы помогало принимать решения»: маркетинг — про ROAS/POAS и атрибуцию, продукт — про A/B и удержание, финансы — про LTV/прогнозы, SRE — про аномалии/p95. Здесь показываю, как корректно и быстро считать эти штуки на ClickHouse, не ломая семантику.
Событийная модель как фундамент аналитики
Минимальный набор полей для событий
- event_time (UTC) и event_date_local (по TZ, если нужно);
- user_id, session_id (или можно вычислять);
- event_name (view, add_to_cart, purchase и т. п.);
- event_value (сумма, длительность), attrs (JSON — осторожно);
-
маркетинговые метки: utm_source/medium/campaign, click_id (gclid/yclid), channel, ad_account.
Риск: 100+ полей JSON → тяжёлые колонки. Митигация: денормализуйте нужное в отдельные простые столбцы.
Сессии и идентичности
- Сессии: session_id можно вычислить как «пауза > 30 минут» с помощью оконных функций (см. §6.1 Cookbook).
-
Identity graph: user_id ↔ device_id ↔ cookie_id. Делайте канонический user_id (маппинг обновляется ретро-окном 7–30 дней).
Риск: «склейка» задним числом портит вчерашние DAU. Митигация: паспорт метрики + ретро-окно и nightly пересчёт.
Когорты, удержание, LTV: базовые кирпичи
Когорта (по первой покупке/активации)
CREATE OR REPLACE VIEW vw_user_cohort AS SELECT user_id, minIf(toDate(event_time), event_name='purchase') AS cohort_day FROM events_wide GROUP BY user_id;
Удержание (Retention)
-- D7/D30 retention от даты когорты
WITH base AS (
SELECT e.user_id, toDate(e.event_time) AS day, c.cohort_day
FROM events_wide e
JOIN vw_user_cohort c USING(user_id)
WHERE e.event_name='purchase' -- или "active" событие
),
grid AS (
SELECT cohort_day, day, dateDiff('day', cohort_day, day) AS d
FROM base
)
SELECT cohort_day,
countDistinctIf(user_id, d=0) AS users_d0,
countDistinctIf(user_id, d=7) AS users_d7,
users_d7 / NULLIF(users_d0,0) AS retention_d7
FROM (SELECT g.*, b.user_id FROM grid g JOIN base b USING(cohort_day, day))
GROUP BY cohort_day
ORDER BY cohort_day;
Риск: возвраты/переносы «размывают» активность. Митигация: чётко определить «активное» событие в паспорте метрики.
LTV/CLV
Операционный LTV (накопленная выручка когорты за N дней от D0):
SELECT
c.cohort_day,
dateDiff('day', c.cohort_day, toDate(e.event_time)) AS d,
sumIf(e.amount_base, e.event_name='purchase') AS rev
FROM events_wide e
JOIN vw_user_cohort c USING(user_id)
WHERE d BETWEEN 0 AND 180
GROUP BY c.cohort_day, d
ORDER BY c.cohort_day, d;
Продвинутый CLV (дисконтирование): применяйте коэффициент 1 / (1+r)^(d/365) в SELECT.
Риск: смешение валют/календарей. Митигация: две вьюхи — «на дату операции» и «на дату отчёта», как в Модулях 1 и 4.
Атрибуция: от простого к управляемому
Last/First touch
-- last-touch: канал последнего клика за 7 дней до покупки
WITH clicks AS (
SELECT user_id, event_time, channel
FROM events_wide WHERE event_name='ad_click'
),
purch AS (
SELECT user_id, event_time AS buy_time, amount_base
FROM events_wide WHERE event_name='purchase'
)
SELECT p.user_id, p.buy_time, p.amount_base,
anyLast(c.channel) AS channel_last
FROM purch p
LEFT JOIN clicks c
ON c.user_id = p.user_id
AND c.event_time BETWEEN p.buy_time - INTERVAL 7 DAY AND p.buy_time
GROUP BY p.user_id, p.buy_time, p.amount_base;
Риск: мульти-девайс и отсутствие клик-ID. Митигация: identity graph + fallback по UTM/реф.
Position-based (40-20-40) и Time-decay
- Position-based: при наличии пути (c1, c2, ..., cn) выделяем веса w1=0.4 для первого, wn=0.4 для последнего, оставшиеся 0.2/(n-2) — промежутку.
-
Time-decay: экспоненциальный распад веса по времени до конверсии.
Реализуется агрегацией массивов путей (см. §6.2 Cookbook).
Марковская атрибуция (приблизительно)
- Строим переходную матрицу состояния каналов + «конверсия»/«потеря».
-
Считаем removal effect (снижение конверсий при удалении канала).
ClickHouse позволяет посчитать эмпирические вероятности переходов и затем «снять» вклад.
Риск: чувствительность к малым объёмам и к «шуму» идентификаций. Митигация: сглаживание, минимальный порог посещений, триммирование «редких» путей.
POAS (profit on ad spend) и ROAS
- ROAS = revenue_attributed / ad_spend.
-
POAS = gross_profit_attributed / ad_spend.
Важно: иметь таблицу затрат по каналам с тем же «уровнем» (день×канал×кампания) и одинаковым календарём.
Частые ошибки и фиксы
- Двойной учёт (путь и Last-touch одновременно). → Храните атрибуцию версированно (v1/v2) и используйте одну модель на отчёт.
- Линковка кликов и покупок через разные TZ. → Нормализуйте event_time_utc и задайте TZ метрики в паспорте.
Эксперименты (A/B): правильная постановка и быстрые расчёты
Назначение в бакеты (bucketing) — без «дыр»
Используйте детерминистическое хеширование, чтобы попадание в эксперимент не «плавало»:
-- user → bucket % 100; эксперименты с префиксом 'exp_42'
WITH
cityHash64(concat('exp_42', toString(user_id))) % 100 AS bucket
SELECT user_id,
bucket BETWEEN 0 AND 49 AS group_a, -- 50%
bucket BETWEEN 50 AND 99 AS group_b -- 50%
FROM users_dim;
Риск: SRM (Sample Ratio Mismatch) — дисбаланс групп. Митигация: SRM-тест ежедневно (χ²), алерт.
CUPED (variance reduction)
Предварительный ковариат — метрика «до эксперимента» или на раннем этапе.
-- Пример: конверсия, ковариат = конверсия за 14 дней до запуска
WITH
cov AS (
SELECT user_id, avg(conv) AS x
FROM preperiod_conv
GROUP BY user_id
),
cur AS (
SELECT user_id, conv AS y, group_flag
FROM period_conv
),
joined AS (
SELECT c.user_id, c.group_flag, c.y, coalesce(v.x, 0) AS x
FROM cur c LEFT JOIN cov v USING(user_id)
),
stats AS (
SELECT covarPop(y,x) AS cov, varPop(x) AS varx FROM joined
),
theta AS (SELECT if(varx=0,0, cov/varx) AS t FROM stats)
SELECT
group_flag,
avg(y - (SELECT t FROM theta) * x) AS cuped_mean
FROM joined
GROUP BY group_flag;
Риск: ковариат должен коррелировать и быть до воздействия. Митигация: фиксируйте в паспорте эксперимента.
Итог метрик и доверительные интервалы
- Для долей (конверсия) — Вилсон/АгRESTI–Coull.
-
Для средних (AOV) — бутстрэп: ClickHouse справляется с 10–50k бутстрэп-итераций по массивам (см. §6.3 Cookbook).
Риск: множественные проверки → инфляция ошибок. Митигация: корректировки (Bonferroni/Holm) или pre-registered primary metric.
Дополнительно
- Стратификация (по платформе/региону) и WLS для снижения дисперсии.
- Дифф-ин-дифф для поэтапных раскаток (staggered launch).
- Uplift-анализ (TRT-CTRL сегменты), если интересует «кому помогло».
Прогнозирование и план-факт
Подготовка рядов (и 4-5-4)
- Сформируйте чистые дневные/недельные ряды в отдельной таблице ts_net_sales_day(region, day, value).
-
Для ритейла создайте альтернативный ряд по финансовым неделям 4-5-4.
Риск: сравнивать YoY «календарные» недели с «финансовыми» — бессмысленно. Митигация: держите оба ряда и показывайте чётко.
Базовые модели в SQL (быстрые бенчмарк-базлайны)
- Наивный сезонный: прогноз = значение «такой же недели прошлого года».
- Экспоненциальное сглаживание (SES) — можно посчитать рекурсивно на стороне Python, но хранить параметры/результаты в CH.
Интеграция с внешними моделями
Паттерн:
- CH агрегирует и отдаёт в файл/объектку (Parquet).
- Модель (Prophet/LightGBM) вне CH считает прогнозы и пишет forecast(day, region, yhat, yhat_lo, yhat_hi).
-
Вьюха в CH даёт «план-факт»: фактическое vs прогноз + ошибки (MAPE/WAPE/SMAPE).
Риск: «незащищённые» промо/праздники. Митигация: календарь событий и регрессоры (праздники, распродажи).
Иерархическая согласованность
- Bottom-up: суммируем прогнозы низов на верх.
-
Top-down: делим верх по историческим долям (или «последним неделям»).
Риск: конфликт планов между уровнями. Митигация: хранить «до согласования» и «после», фиксировать метод в паспорте прогноза.
Cookbook: готовые SQL-наброски
Сессии (30-мин таймаут)
WITH ordered AS (
SELECT user_id, event_time,
lagInFrame(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_t
FROM events_wide
),
flags AS (
SELECT *, (prev_t IS NULL OR event_time - prev_t > 1800) AS is_new
FROM ordered
),
sess AS (
SELECT *,
sum(is_new) OVER (PARTITION BY user_id ORDER BY event_time) AS sess_num
FROM flags
)
SELECT user_id,
concat(toString(user_id), '_', toString(sess_num)) AS session_id,
min(event_time) AS session_start,
max(event_time) AS session_end,
count() AS events
FROM sess
GROUP BY user_id, sess_num;
Position-based атрибуция на массивах
-- Соберём путь каналов за 7 дней до покупки
WITH paths AS (
SELECT p.user_id, p.event_time AS buy_time, p.amount_base,
groupArray(c.channel ORDER BY c.event_time) AS ch_path
FROM purchases p
LEFT JOIN ad_clicks c
ON c.user_id = p.user_id
AND c.event_time BETWEEN p.event_time - INTERVAL 7 DAY AND p.event_time
GROUP BY p.user_id, p.event_time, p.amount_base
),
weights AS (
SELECT user_id, buy_time, amount_base, ch_path,
arrayEnumerate(ch_path) AS idx,
arraySize(ch_path) AS n,
arrayMap(i -> multiIf(n=1, 1.0,
i=1, 0.4,
i=n, 0.4,
0.2/NULLIF(n-2,0)), idx) AS w
FROM paths
)
SELECT arrayJoin(arrayZip(ch_path, w)) AS z,
z.1 AS channel,
sum(amount_base * z.2) AS revenue_attr
FROM weights
GROUP BY channel
ORDER BY revenue_attr DESC;
Бутстрэп доверительного интервала разницы средних
-- Предположим есть таблица exp_metrics(user_id, group_flag, metric)
WITH
a AS (SELECT arrayAgg(metric) arr FROM exp_metrics WHERE group_flag=0),
b AS (SELECT arrayAgg(metric) arr FROM exp_metrics WHERE group_flag=1),
iters AS (SELECT range(10000) AS i) -- 10k итераций
SELECT
quantile(0.025)(diff) AS ci_lo,
quantile(0.975)(diff) AS ci_hi,
avg(diff) AS delta_mean
FROM (
SELECT
arrayAverage(arrayMap(x -> a.arr[randUniform(1, length(a.arr))], range(length(a.arr)))) AS mean_a,
arrayAverage(arrayMap(x -> b.arr[randUniform(1, length(b.arr))], range(length(b.arr)))) AS mean_b,
mean_b - mean_a AS diff
FROM iters, a, b
);
Z-score аномалий + MAD (robust)
WITH base AS (
SELECT day, sum(net_sales) AS s
FROM vw_net_sales_daily
WHERE day >= today()-90
GROUP BY day
),
m AS (SELECT median(s) AS med FROM base),
mad AS (SELECT median(abs(s - (SELECT med FROM m))) AS mad FROM base)
SELECT day, s,
(s - (SELECT med FROM m)) / NULLIF((SELECT mad FROM mad)*1.4826,0) AS z_robust
FROM base
WHERE day = today()-1 AND abs(z_robust) > 3;
ML-фичи и выборки без утечек времени
Point-in-time правильность (as-of join)
Задача: собрать фичи так, чтобы на момент события мы не знали будущего.
-- Фича "покупки за 30 дней до момента"
SELECT e.user_id, e.event_time,
sumIf(o.amount, o.event_time BETWEEN e.event_time - INTERVAL 30 DAY AND e.event_time) AS spend_30d
FROM prediction_targets e
LEFT JOIN orders o ON o.user_id = e.user_id
GROUP BY e.user_id, e.event_time;
Риск: «глянули» на возврат, который случился после момента предсказания. Митигация: ограничивать JOIN «левее момента», хранить SCD-снапшоты атрибутов.
Хранение фичей
- Offline-store: таблицы features_* по срезам (день/час), заполнение batch/MV.
-
Nearline: агрегаты-состояния (…State) для скользящих окон (1–7 дней).
Риск: несогласованность между offline/nearline. Митигация: единая логика расчёта с разной частотой; тесты «расхождения ≤ порога».
Мониторинг дрейфа/качества модели
- PSI/JS-дивергенция распределений фичей (дневной чек).
- Стабильность целевой метрики во времени; сегменты.
- Храните predictions(score) + факты → считайте ROC/AUC и калибровку.
Риски и как их избегать
|
Область |
Риск |
Как проявляется |
Профилактика/фикс |
|---|---|---|---|
|
Атрибуция |
Двойной учёт |
ROAS «растёт» >100% |
Единственная активная модель на отчёт; версия атрибуции в паспорте; регресс-сравнение |
|
Атрибуция |
Мульти-девайс |
Клик ≠ пользователь |
Identity graph + fallback; окно связывания фиксированное |
|
Эксперименты |
SRM |
Дисбаланс A/B |
χ²-SRM ежедневно; детерминированный bucketing |
|
Эксперименты |
Перенос/перемешивание |
Пользователь меняет группу |
Хеш-бакеты, sticky assignment по user_id |
|
Эксперименты |
Ложные «победы» |
Много метрик/подсегментов |
Pre-registered primary, корректировки за множественность |
|
Прогнозы |
Неправильный календарь |
YoY «едет» |
Два ряда (григорианский и 4-5-4), чёткая маркировка |
|
Прогнозы |
Промо/праздники |
«вылеты» ошибок |
Календарь событий/регрессоров, отдельный алерт |
|
Аномалии |
Фальш-положительные |
Много шумных алертов |
Робастные метрики (MAD), исключения по событиям |
|
ML |
Утечка времени |
«волшебная» точность в offline |
As-of join; срезы «до момента»; аудит признаков |
Организация работы и артефакты
- Каталог экспериментов: YAML-паспорт (цель, метрика, bucketing, ковариаты, даты, SRM-лог).
- Каталог атрибуций: перечень моделей, формулы/веса, changelog, ретро-окно.
- Каталог прогнозов: источники рядов, метод, гиперпараметры, период ретро-оценки, метрики качества.
- Дашборды здоровья: SRM, аномалии, PSI, ошибки прогнозов (MAPE/WAPE), дельты v1→v2 атрибуции.
Итоги
Чтобы ClickHouse-витрины «кормили» решения в маркетинге/продукте/финансах, держите четыре опоры:
- Событийная дисциплина (время/TZ, identity, сессии, когорты).
- Атрибуция как код (версии, единая активная модель, контролируемые ретро-пересчёты).
- Эксперименты по науке (bucketing, SRM, CUPED, доверительные интервалы, стратификация).
- Прогнозы и аномалии (чистые ряды, календарь 4-5-4/событий, робастные детекторы, план-факт).
Добавьте ML-паттерны без утечек, и у вас получится управляемая инфраструктура продвинутой аналитики поверх уже сделанных витрин.
Arenadata QuickMarts (ADQM) — корпоративная платформа на базе ClickHouse для быстрого слоя витрин и near-real-time аналитики. Решает задачи «быстрых» дашбордов и API с низкой латентностью и высокой конкуррентностью, работает поверх вашего DWH/лейкхауса как serving-уровень. Даёт предсказуемую производительность на терабайтно-петабайтных объёмах за счёт колоночного хранения, компрессии и предагрегатов (Materialized Views, AggregatingMergeTree), подключается к Kafka/S3 и стандартным BI-инструментам по SQL/HTTP. Для корпоративных ИТ ADQM предлагает поддержку и SLA, отказоустойчивые кластеры (HA/DR), безопасность (RBAC, LDAP/OIDC, шифрование трафика и данных), мониторинг и резервное копирование. Платформа хорошо ложится на методологию курса: семантика vw_*, роллап-слои, NRT-ингест, SLO/наблюдаемость и «гвардейки» для BI/API. Итог — быстрый запуск витрин за недели, снижённые риски в проде и предсказуемая стоимость владения.
