Как настроить ежедневный алертинг по маркетинговым метрикам с помощью SQL

Привет, Хабр! На связи Антон Прыгин, аналитик данных в Garage Eight. Расскажу, как с помощью простых SQL-запросов и базовых математических методов получилось построить систему ежедневного мониторинга и алертинга маркетинговых метрик, которая работает в связке с таск-трекером. 

Как возник запрос от бизнеса

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

В результате выбрали мониторинг метрик с алертингом в Slack — в нем каждый день появляется информация о том, есть ли аномалии в метриках. 

Сбор исходного датасета

Для начала я проанализировал существующие данные и построил графики, чтобы выявить закономерности всплесков и провалов. 

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

Z-score

С точки зрения контроля метрик z-score помогает обнаруживать аномалии на ранних стадиях, например резкий рост отказов или падение трафика. 

Как я действовал при работе с z-score:

1. Импортировал данные из базы данных и форматировал: добавил определение квартала и дня недели, классификацию на будни и выходные. 

2. Определил период, для которого будет рассчитываться отклонение параметра. В нашем случае это две недели — оптимальный баланс между чувствительностью и устойчивостью. Меньшее количество времени добавило бы больше шума и реакции на случайные пики, а большее — отсутствие реакции на короткие изменения.

3. Задал необходимые для анализа переменные: 

• 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%'

В результате мы получаем уведомление с большим, но емким и понятным объемом информации: 

• Количество регистраций за день и соответствие ежедневному плану.

• Изменение в количестве регистраций по сравнению с прошлым периодом. 

• Соответствие количества регистраций квартальному плану.

• Количество активаций за день и соответствие ежедневному плану.

• Изменение в количестве активаций по сравнению с прошлым периодом. 

• Соответствие количества активаций квартальному плану.

Заключение

Стоит отметить, что решение не идеальное, но качественное — я стремился сделать простой и работающий инструмент, чтобы вовремя следить за изменениями. Теперь им пользуется не только наша команда маркетинга, но и коллеги из смежных отделов. Все просят добавить собственные метрики, поэтому эта статья появилась в том числе для того, чтобы каждый самостоятельно мог это сделать =)

Пользуйтесь и задавайте вопросы в комментариях!