Привет, Хабр! На связи Антон Прыгин, аналитик данных в Garage Eight. Расскажу, как с помощью простых SQL-запросов и базовых математических методов получилось построить систему ежедневного мониторинга и алертинга маркетинговых метрик, которая работает в связке с таск-трекером.
Как возник запрос от бизнеса
Команда маркетинга ежедневно следит за метриками регистраций и активаций продуктов, и раньше изменения контролировались через дашборды. Чтобы упростить процесс и анализировать сами графики только во время резких пиков и спадов, мы решили найти более быстрый и удобный способ.
В результате выбрали мониторинг метрик с алертингом в Slack — в нем каждый день появляется информация о том, есть ли аномалии в метриках.
Сбор исходного датасета
Для начала я проанализировал существующие данные и построил графики, чтобы выявить закономерности всплесков и провалов.
Мы хотели создать достаточно простой инструмент для алертинга, поэтому при поиске подходящих методов я опирался на базовые математические приемы, которые впоследствии и использовал в алгоритме.
Z-score
|
Немного теории: z-score — стандартизированная оценка, показывает значение отклонения параметра от его среднего значения. Определяется по следующей формуле: где X — среднее значение, Sx — стандартное отклонение для набора данных. |
С точки зрения контроля метрик z-score помогает обнаруживать аномалии на ранних стадиях, например резкий рост отказов или падение трафика.
Как я действовал при работе с z-score:
Импортировал данные из базы данных и форматировал: добавил определение квартала и дня недели, классификацию на будни и выходные.
Определил период, для которого будет рассчитываться отклонение параметра. В нашем случае это две недели — оптимальный баланс между чувствительностью и устойчивостью. Меньшее количество времени добавило бы больше шума и реакции на случайные пики, а большее — отсутствие реакции на короткие изменения.
Задал необходимые для анализа переменные:
prev_day_registrations — количество регистраций на предыдущий день.
cumulative_quarter — суммарное количество регистраций за квартал.
AVG_registrations — среднее значение регистраций за последние 13 дней (исключая текущий).
daily_plan и cumulative_plan — целевые значения для ежедневных и кумулятивных регистраций.
lower_bound и upper_bound — границы целевых значений.
delta — изменение регистраций по сравнению с предыдущим днем.
STDDEV_registrations — стандартное отклонение регистраций за те же 13 дней.
positive_limit и negative_limit — границы, определяющие, что является нормальным уровнем регистраций с учетом стандартного отклонения. При расчете границ использовали среднее значение регистраций с разбросом в два стандартных отклонения количества регистраций.
И сам z-score =)
В SQL это выглядит следующим образом:
SELECT
day,
'registrations' AS metric,
day_of_week,
country,
registrations,
prev_day_registrations,
delta,
AVG_registrations + k * STDDEV_registrations AS positive_limit,
AVG_registrations - k * STDDEV_registrations AS negative_limit,
(registrations - AVG_registrations) / STDDEV_registrations AS z_score,
cumulative_quarter,
daily_plan AS daily_registrations,
cumulative_plan AS cumulative_registrations,
lower_bound,
upper_bound
FROM (
SELECT
day,
day_of_week,
country,
registrations,
LAG(registrations) OVER (ORDER BY day) AS prev_day_registrations,
(registrations / LAG(registrations) OVER (ORDER BY day)) - 1 AS delta,
AVG(registrations) OVER (PARTITION BY day_type ORDER BY day ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING) AS AVG_registrations,
STDDEV(registrations) OVER (PARTITION BY day_type ORDER BY day ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING) AS STDDEV_registrations,
2 AS k,
SUM(registrations) OVER (PARTITION BY quarter) AS cumulative_quarter,
plan.daily_target AS daily_plan,
plan.cumulative_target AS cumulative_plan,
plan.lower_bound,
plan.upper_bound
FROM (
SELECT
DATE_TRUNC(registered_at, QUARTER) AS quarter,
DATE(registered_at) AS day,
FORMAT_TIMESTAMP('%A', registered_at) AS day_of_week,
IF(FORMAT_TIMESTAMP('%A', registered_at) IN ('Monday','Tuesday','Wednesday','Thursday','Friday'), 'workday', 'weekend') AS day_type,
country,
COUNT(DISTINCT user_id) AS registrations
FROM `project.dataset.user_events`
WHERE registered_at >= '2024-01-01'
GROUP BY 1, 2, 3, 4, 5
) base
LEFT JOIN `project.dataset.registration_plans` plan
ON base.day = plan.date
)Если значение выходит за позитивную или негативную границу, то это аномалия. Я прописал это условие в запросе и стал накладывать на графики, чтобы выяснить, насколько качественно только z-score определяет алерты.
По визуализациям стало понятно, что одного метода недостаточно, и тогда я обратился к еще одному базовому приему.
Перцентили
Перцентиль — это значение, которое заданная случайная величина не превышает с фиксированной вероятностью, заданной в процентах. |
В нашем алгоритме если 90% перцентиль по регистрациям составляет десять тысяч, то в 90% случаев за день регистрируется не более этого количества пользователей.
Я начал рассчитывать перцентили для каждого показателя, сравнивать на графиках и подбирать наиболее подходящий. После проверки 90 и 95% наиболее точным оказался 99%.
Для этой метрики я брал период в 28 дней. В отличие от z-score, перцентильный подход не зависит от нормальности распределения, и 99 и 1% перцентили помогают зафиксировать крайние значения — отсечь «хвосты» графика. Период в 28 дней позволяет собрать больше данных и использовать метод для стратегического мониторинга: он менее чувствителен к шуму и лучше подходит для метрик с редкими, но сильными всплесками, такими как инциденты на выходных.
Запрос выглядел так:
registrations_percentile_data AS (
SELECT
r1.day,
PERCENTILE_CONT(r2.registrations, 0.99) OVER (PARTITION BY r1.day) AS p99_rate,
PERCENTILE_CONT(r2.registrations, 0.01) OVER (PARTITION BY r1.day) AS p01_rate
FROM registrations r1
JOIN registrations r2
ON r2.day BETWEEN r1.day - INTERVAL 28 DAY AND r1.day
),До этого отдельно z-score был точен на 65–80% в зависимости от метрики — в периоде 14 дней появлялись ошибки. Отдельно перцентили были точны в 50–60% случаев в зависимости от показателя — в более длинном периоде 28 дней небольшие пики и провалы терялись. Когда мы объединили методы, точность определения алертов составила уже около 99%. Я понял, что подход корректный, и написал аналогичные запросы для метрик активации.
activations AS (
SELECT
day,
'activations' AS metric,
day_of_week,
country,
activations,
prev_day_activations,
delta,
AVG_activations + k * STDDEV_activations AS positive_limit,
AVG_activations - k * STDDEV_activations AS negative_limit,
(activations - AVG_activations) / STDDEV_activations AS z_score,
cumulative_quarter,
daily_plan AS daily_activations,
cumulative_plan AS cumulative_activations,
lower_bound,
upper_bound
FROM (
SELECT
day,
day_of_week,
country,
activations,
LAG(activations) OVER (ORDER BY day) AS prev_day_activations,
(activations / LAG(activations) OVER (ORDER BY day)) - 1 AS delta,
AVG(activations) OVER (PARTITION BY day_type ORDER BY day ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING) AS AVG_activations,
STDDEV(activations) OVER (PARTITION BY day_type ORDER BY day ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING) AS STDDEV_activations,
2 AS k,
SUM(activations) OVER (PARTITION BY quarter) AS cumulative_quarter,
plan.daily_target AS daily_plan,
plan.cumulative_target AS cumulative_plan,
plan.lower_bound,
plan.upper_bound
FROM (
SELECT
DATE_TRUNC(activated_at, QUARTER) AS quarter,
DATE(activated_at) AS day,
FORMAT_TIMESTAMP('%A', activated_at) AS day_of_week,
IF(FORMAT_TIMESTAMP('%A', activated_at) IN ('Monday','Tuesday','Wednesday','Thursday','Friday'), 'workday', 'weekend') AS day_type,
country,
COUNT(DISTINCT user_id) AS activations
FROM `project.dataset.activation_events`
WHERE activated_at >= '2024-01-01'
AND activation_index = 0
GROUP BY 1, 2, 3, 4, 5
) base
LEFT JOIN `project.dataset.activation_plans` plan
ON base.day = plan.date
)
),
activations_percentile_data AS (
SELECT
a1.day,
PERCENTILE_CONT(a2.activations, 0.99) OVER (PARTITION BY a1.day) AS p99_rate,
PERCENTILE_CONT(a2.activations, 0.01) OVER (PARTITION BY a1.day) AS p01_rate
FROM activations a1
JOIN activations a2
ON a2.day BETWEEN a1.day - INTERVAL 28 DAY AND a1.day
)Далее нужно было решить, использовать ли оба метода или остановиться только на перцентилях. Я выбрал применять оба, так как они работают комплексно: z-score — для быстрого, чувствительного алертинга, когда метрики просели в текущий момент, а перцентили — для устойчивого контроля и понимания границ нормы за длительный период.
Вместе они дают и широту, и глубину — возможность отслеживать и резкие скачки, и выбросы, которых в целом не должно быть.
Настроил мониторинг так, что, если появляется алерт по одному из методов, это некритичная аномалия. Если же срабатывают оба алерта, то изменения значимы, и команде маркетинга стоит разобраться, с чем они связаны.
SELECT
DISTINCT
r.day,
r.metric,
r.registrations AS daily_fact,
ROUND((r.registrations / r.daily_registrations) * 100, 2) AS daily_percent_to_plan,
r.daily_registrations AS daily_plan,
ROUND(r.delta * 100, 2) AS percent_to_prev_day,
CASE WHEN r.registrations > r.positive_limit AND r.z_score > 2 THEN 1 ELSE 0 END AS stat_positive_alert,
CASE WHEN r.registrations < r.negative_limit AND r.z_score < -2 THEN 1 ELSE 0 END AS stat_negative_alert,
CASE WHEN r.registrations > p99_rate THEN 1 ELSE 0 END AS p99_alert,
CASE WHEN r.registrations < p01_rate THEN 1 ELSE 0 END AS p01_alert,
r.cumulative_quarter AS quarter_fact,
ROUND(r.cumulative_quarter / r.cumulative_registrations * 100, 2) AS quarter_percent_to_plan,
r.cumulative_registrations AS quarter_plan,
CAST(ROUND(reg_to_act, 2) AS STRING) AS reg_to_act,
CAST(ROUND(((reg_to_act / prev_day_cr) - 1) * 100, 2) AS STRING) AS percent_to_prev_cr
FROM registrations r
JOIN `project.dataset.reg_to_act_cr` cr USING(day)
JOIN registrations_percentile_data p USING(day)
UNION ALL
SELECT
DISTINCT
a.day,
a.metric,
a.activations AS daily_fact,
ROUND((a.activations / a.daily_activations) * 100, 2) AS daily_percent_to_plan,
a.daily_activations AS daily_plan,
ROUND(a.delta * 100, 2) AS percent_to_prev_day,
CASE WHEN a.activations > a.positive_limit AND a.z_score > 2 THEN 1 ELSE 0 END AS stat_positive_alert,
CASE WHEN a.activations < a.negative_limit AND a.z_score < -2 THEN 1 ELSE 0 END AS stat_negative_alert,
CASE WHEN a.activations > p99_rate THEN 1 ELSE 0 END AS p99_alert,
CASE WHEN a.activations < p01_rate THEN 1 ELSE 0 END AS p01_alert,
a.cumulative_quarter AS quarter_fact,
ROUND(a.cumulative_quarter / a.cumulative_activations * 100, 2) AS quarter_percent_to_plan,
a.cumulative_activations AS quarter_plan,
'' AS reg_to_act,
'' AS percent_to_prev_cr
FROM activations a
JOIN activations_percentile_data p USING(day)
ORDER BY day DESCТак с помощью достаточно небольших запросов мы собрали таблицу дневных метрик и их статус аномалии.
Интеграция с таск-трекером
Алгоритм можно интегрировать с разными трекерами и мессенджерами. Чтобы связать сервисы у нас, мы обратились к data-инженерам, которые настроили взаимосвязь по API, я же написал запрос для корректного отображения результатов.
WITH data_preparation AS (
SELECT
-- Символы статуса по дневному плану
CASE
WHEN D_percent_to_Plan < 60 THEN ':red_circle:'
WHEN D_percent_to_Plan < 90 THEN ':yellow_circle:'
ELSE ':green_circle:'
END AS D_plan_symbol,
-- Символы статуса по квартальному плану
CASE
WHEN Q_percent_to_Plan < 60 THEN ':red_circle:'
WHEN Q_percent_to_Plan < 90 THEN ':large_yellow_circle:'
ELSE ':large_green_circle:'
END AS Q_plan_symbol,
day,
UPPER(metric) AS metric,
D_fact,
D_percent_to_plan,
D_Plan,
percent_to_prev_day,
Q_FACT,
Q_percent_to_plan,
Q_Plan,
REG_to_ACT,
percent_to_prev_CR_Reg_to_Act,
-- Определение текста алерта
CASE
WHEN P99_alert = 1 AND stat_possitive_alert = 1 THEN ":alert: Обнаружена статистическая аномалия - значительное превышение объема для данного дня недели :alert:"
WHEN P01_alert = 1 AND stat_negative_alert = 1 THEN ":alert: Обнаружена статистическая аномалия - значительное снижение объема для данного дня недели :alert:"
WHEN stat_possitive_alert = 1 THEN ":alert: Объем для текущего дня недели значительно выше нормы за последние 2 недели"
WHEN stat_negative_alert = 1 THEN ":alert: Объем для текущего дня недели значительно ниже нормы за последние 2 недели"
WHEN P99_alert = 1 THEN ":alert: Объем превышает 99% значений за последние 28 дней"
WHEN P01_alert = 1 THEN ":alert: Объем ниже 1% значений за последние 28 дней"
ELSE "Отклонений не выявлено"
END AS ALERT
FROM project_id.analytics_metrics_summary
WHERE day = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
ORDER BY metric
)
),
registrations_D AS (
SELECT
'FFFFFF' AS color,
'' AS text,
metric AS title,
D_plan_symbol || " Day plan/fact: " || D_percent_to_Plan || "%" ||
"\n - Day current fact: " || D_fact ||
"\n - Day plan: " || D_Plan ||
"\n - % To previous Day: " || percent_to_prev_day || "%" ||
"\n - CR Reg to Act: " || REG_to_ACT || "% (" || percent_to_prev_CR_Reg_to_Act || ")" ||
"\n - " || ALERT AS value
FROM data_preparation
WHERE LOWER(metric) = 'registrations'
),
registrations_Q AS (
SELECT
'FFFFFF' AS color,
'' AS text,
'' AS title,
Q_plan_symbol || " Quarter plan/fact: " || Q_percent_to_Plan || "%" ||
"\n - Quarter current fact: " || Q_FACT ||
"\n - Quarter plan: " || Q_Plan AS value
FROM data_preparation
WHERE LOWER(metric) = 'registrations'
),
activations_D AS (
SELECT
'FFFFFF' AS color,
'' AS text,
metric AS title,
D_plan_symbol || " Day plan/fact: " || D_percent_to_Plan || "%" ||
"\n - Day current fact: " || D_fact ||
"\n - Day plan: " || D_Plan ||
"\n - % To previous Day: " || percent_to_prev_day || "%" ||
"\n - " || ALERT AS value
FROM data_preparation
WHERE LOWER(metric) = 'activations'
),
activations_Q AS (
SELECT
'FFFFFF' AS color,
'' AS text,
'' AS title,
Q_plan_symbol || " Quarter plan/fact: " || Q_percent_to_Plan || "%" ||
"\n - Quarter current fact: " || Q_FACT ||
"\n - Quarter plan: " || Q_Plan AS value
FROM data_preparation
WHERE LOWER(metric) = 'activations'
)
-- Основной блок формирования итогового сообщения (для мессенджера)
SELECT *
FROM (
SELECT
'CHANNEL_ID_PLACEHOLDER' AS channel_field,
'DATE: ' || DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS message_field,
TO_JSON_STRING(ARRAY_AGG(STRUCT(color, text, title, value))) AS attachments
FROM (
SELECT * FROM (
SELECT 1 AS rn, color, text, ARRAY_AGG(title) AS title, ARRAY_AGG(value) AS value FROM registrations_D GROUP BY 1,2,3
UNION ALL
SELECT 2, color, text, ARRAY_AGG(title), ARRAY_AGG(value) FROM registrations_Q GROUP BY 1,2,3
UNION ALL
SELECT 3, color, text, ARRAY_AGG(title), ARRAY_AGG(value) FROM activations_D GROUP BY 1,2,3
UNION ALL
SELECT 4, color, text, ARRAY_AGG(title), ARRAY_AGG(value) FROM activations_Q GROUP BY 1,2,3
-- Места для добавления других метрик
)
ORDER BY rn
)
)
WHERE attachments NOT LIKE '%null%'В результате мы получаем уведомление с большим, но емким и понятным объемом информации:
Количество регистраций за день и соответствие ежедневному плану.
Изменение в количестве регистраций по сравнению с прошлым периодом.
Соответствие количества регистраций квартальному плану.
Количество активаций за день и соответствие ежедневному плану.
Изменение в количестве активаций по сравнению с прошлым периодом.
Соответствие количества активаций квартальному плану.
Заключение
Стоит отметить, что решение не идеальное, но качественное — я стремился сделать простой и работающий инструмент, чтобы вовремя следить за изменениями. Теперь им пользуется не только наша команда маркетинга, но и коллеги из смежных отделов. Все просят добавить собственные метрики, поэтому эта статья появилась в том числе для того, чтобы каждый самостоятельно мог это сделать =)
Пользуйтесь и задавайте вопросы в комментариях!