1.5 миллиона событий в день и ни одной таблицы events: как мы считали продуктовую аналитику 300K-бота по голому проду / Хабр

Маркетолог прислал мне скрин дашборда и одно слово: «пусто».

На скрине — панель «Количество пришедших по реферальной ссылке за период». Ноль. Под ней — график регистраций. Тоже ноль. Ссылка, по которой он пришёл, вела на дашборд с фильтром по конкретному slug — тому самому, который он неделю крутил в закупке.

Я открыл ту же ссылку, поменял в урле from=now-6h на from=now-30d и увидел 16 регистраций.

Дефолтный интервал в Grafana — 6 часов. У бота ночью в этой гео никого нет. Аналитика работала идеально и показывала абсолютно честный ноль.

Это самая безобидная из историй, которые тут будут. Дальше — про то, как мы строили продуктовую аналитику для Telegram-бота с AI-персонажами на ~300K MAU, ~75 RPS в пике и ~1.5M событий в день: без ClickHouse, без Amplitude, без единой event-таблицы. Двенадцать дашбордов в Grafana поверх боевой MySQL. Три из них какое-то время врали, и это выяснилось не сразу.

Главный парадокс всего проекта помещается в одну строку: через систему проходит полтора миллиона событий в день, и ни одно из них не записано как событие. Есть только строки в продуктовых таблицах и их created_at. Всё, что вы прочитаете ниже, — следствие этого факта.

Статья — не туториал «подключите MySQL к Grafana за 10 шагов». Это разбор того, что происходит, когда продуктовые метрики приходится доставать из схемы, которую проектировали под продукт, а не под аналитику, — и когда по этим метрикам прямо сейчас решают, лить трафик дальше или нет.

Дисклеймер: проект под NDA, поэтому названия, точные объёмы и часть деталей изменены. Порядок величин, схема и сами проблемы — настоящие.

Что за система

Telegram-бот с AI-персонажами. Пользователь открывает ссылку, попадает в бота, выбирает персонажа, переписывается. Часть переписки — текст, часть — генерация картинок. Есть платежи, есть реферальные ссылки под закупку трафика, есть локали (бот многоязычный).

Порядок величин, чтобы дальше было с чем соотносить цифры:

~300K MAU, из них заметная дневная активность;
~75 RPS в пике — это живой OLTP, по которому мы теперь ещё и гоняем тяжёлые GROUP BY;
~1.5M событий в день — сообщения, коллбэки, генерации, платежи. Всё это ложится строками в продуктовые таблицы и нигде не агрегируется.

Стек до аналитики:

Telegram  ──►  Laravel-бэкенд  ──►  MySQL 8
                     │
                     ├──►  очередь (jobs / failed_jobs)
                     │        └──►  воркеры генерации: text / image
                     │
                     └──►  внешние LLM- и image-провайдеры

Схема — обычная продуктовая. Никакого event sourcing, никакой таблицы events, никаких user_id в привычном смысле. То, что есть:

Таблица	Что это на самом деле
`telegram_chats`	диалог человека с ботом. Ближайшее, что есть к «пользователю»
`chats`	диалог с конкретным персонажем внутри одного `telegram_chat`
`messages`	сообщения внутри `chats`, с `role` = `user` / `assistant`
`telegram_messages`	сырые сообщения из Telegram, привязаны к `telegram_chat_id`
`characters` / `character_versions`	персонажи и версии их промптов, `active_version_id`
`telegram_links`	реферальные ссылки, поле `slug` и счётчик `visited`
`payments`	платежи, `amount`, привязка к `telegram_chat_id`
`generation_states`	состояния генераций, `type` = `text` / `image`
`jobs` / `failed_jobs`	стандартная очередь Laravel

Всё. Из этого нужно было получить воронку, retention, метрики по каналам, топ персонажей и техничку — на объёме, который растёт на полтора миллиона строк в сутки.

Почему Grafana поверх прода, а не нормальный аналитический контур

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

Длинный ответ — список того, что мы сознательно приняли как долг:

Читаем боевую базу. Отдельной реплики под аналитику сначала не было. Все запросы ниже — с прода, который в этот момент держит 75 RPS живой нагрузки.
Нет слоя агрегатов. Каждое открытие дашборда — это полный пересчёт по messages, а она прибавляет по миллиону с лишним строк в день.
Нет событий. Всё, что мы можем измерить, — это состояния строк и их created_at.
Нет истории изменений. Если строку обновили, прошлое значение мы уже не узнаем.

Первые два пункта решаются деньгами и временем. Третий и четвёртый — нет, и именно из-за них дальше начинается интересное.

Grafana тут выиграла у Metabase/Redash по одной причине: она уже стояла для инфраструктурных метрик, у неё есть переменные дашборда ($ref_id, $character) и макросы времени. Продуктовые дашборды просто приехали в ту же инсталляцию.

Проблема 1: в базе нет пользователей

Первый же вопрос — «сколько у нас пользователей» — не имеет ответа в схеме. Есть telegram_chats, есть chats. Разница между ними — множитель.

Один человек, который поговорил с пятью персонажами, — это:

1 строка в telegram_chats
5 строк в chats
N строк в messages

Если считать «пользователей» по chats, вы завышаете аудиторию ровно во столько раз, сколько персонажей в среднем пробует человек. У нас это было далеко не 1.0 — то есть на 300K реальных людей приходилось ощутимо больше «пользователей», если считать наивно.

Поэтому во всех продуктовых панелях «пользователь» — это строго telegram_chats.id, и считается он только так:

SELECT COUNT(DISTINCT tc.id) AS `Пользователи`
FROM messages m
JOIN chats ch          ON ch.id = m.chat_id
JOIN telegram_chats tc ON tc.id = ch.telegram_chat_id
JOIN characters c      ON c.id  = ch.character_id
WHERE m.role = 'user'
  AND $__timeFilter(m.created_at);

COUNT(DISTINCT tc.id), а не COUNT(*) и не COUNT(ch.id). Выглядит очевидно ровно до того момента, пока не появится вторая панель, где кто-то (я) написал иначе.

Правило, которое стоило дороже всего: определение сущности «пользователь» должно быть записано словами до того, как написан первый SQL. У нас оно появилось после третьего расхождения в цифрах.

Проблема 2: две таблицы сообщений и два разных топа персонажей

В базе две ветки сообщений:

telegram_messages — сырьё из Telegram. Знает telegram_chat_id, не знает персонажа.
messages — сообщения внутри диалога с персонажем. Знают chat_id, role, через chats знают character_id.

Панель «Топ персонажей» была написана по первой:

WITH base AS (
  SELECT DISTINCT m.telegram_chat_id, ch.character_id
  FROM telegram_messages m
  JOIN chats ch ON ch.telegram_chat_id = m.telegram_chat_id
  WHERE $__timeFilter(m.created_at)
)
SELECT c.code, COUNT(base.telegram_chat_id) AS "Количество пользователей"
FROM base JOIN characters c ON c.id = base.character_id
GROUP BY c.code
ORDER BY 2 DESC;

Найдите ошибку. Она в JOIN.

telegram_messages не знает, какому персонажу адресовано сообщение. Джойн идёт по telegram_chat_id — то есть каждое сырое сообщение пользователя склеивается со всеми его диалогами. DISTINCT спасает от размножения строк, но не от смысла: если человек написал персонажу A, а с персонажем B у него просто открыт пустой диалог, — B получает этого человека в свой топ.

Персонажи, которых показывали первыми в списке выбора, собирали пустые диалоги и уезжали наверх в топе. Топ мерил не популярность, а позицию в UI.

Правильная версия джойнит через messages и фильтрует роль:

FROM messages m
JOIN chats ch ON ch.id = m.chat_id          -- ключ диалога, не пользователя
WHERE m.role = 'user'                       -- считаем людей, а не бота

Две панели в двух дашбордах отвечали на один вопрос и давали разные ответы. Заметили это, когда пришёл вопрос, почему персонаж из топа-3 не приносит ни одного платежа.

Вывод: если в схеме две таблицы про одно и то же — выберите каноническую для аналитики и запретите себе вторую. У нас канон — messages, telegram_messages осталась только там, где нужны сырые тайминги (о них ниже).

Проблема 3: воронка без событий

Классическая воронка требует событий: bot_opened, character_selected, first_message_sent. Событий нет — хотя их полтора миллиона в день. Есть только строки и их created_at.

Пришлось собирать воронку из косвенных признаков существования:

-- Этап 1: новые (появилась строка в telegram_chats)
SELECT DATE(tc.created_at) AS time, COUNT(*) AS new_users
FROM telegram_chats tc
WHERE $__timeFilter(tc.created_at)
GROUP BY DATE(tc.created_at);

-- Этап 2: активированные (появился хотя бы один диалог с персонажем)
SELECT DATE(tc.created_at) AS time, COUNT(DISTINCT tc.id) AS activated_users
FROM telegram_chats tc
JOIN chats ch ON ch.telegram_chat_id = tc.id
WHERE $__timeFilter(tc.created_at)
  AND $__timeFilter(ch.created_at)
GROUP BY DATE(tc.created_at);

-- Этап 3: стартовавшие
SELECT DATE(tc.created_at) AS time, COUNT(DISTINCT tc.id) AS started_users
FROM telegram_chats tc
JOIN chats ch   ON ch.telegram_chat_id = tc.id
JOIN messages m ON m.chat_id = ch.id
WHERE $__timeFilter(tc.created_at)
  AND m.role = 'user'
  AND m.text LIKE '/start%'
GROUP BY DATE(tc.created_at);

Да, третий этап — это LIKE '/start%' по тексту сообщения. Скан по тексту вместо поля event_type. Мне не нравится, вам не нравится, но другого следа от команды /start в базе не осталось.

Два неприятных свойства такой воронки:

Этапы считаются по дате регистрации, а не по дате события. $__timeFilter(tc.created_at) во всех трёх запросах. Человек, зарегистрировавшийся 1-го и написавший 3-го, попадёт в оба этапа на 1-е число. Для когорты это правильно, для «что было вчера» — нет. Мы выбрали когорту, и это надо помнить, читая график.
Порядок этапов — предположение. Мы не знаем, что chats создаётся после telegram_chats по бизнес-логике, — мы это просто знаем из кода. Схема этого не гарантирует.

Если бы у меня был один голос за одно изменение в бэкенде — я бы потратил его на таблицу events(telegram_chat_id, type, payload, created_at). Всё остальное в этой статье — следствие её отсутствия.

Проблема 4: главная метрика оказалась не воронкой, а кривой дожития

Самая полезная панель во всём проекте называется «Вовлечённость» и выглядит как таблица:

Этап (X)	Пользователи (остались)	Доля (%)
0 сообщение(й) и более	‹все›	100.0
1 сообщение(й) и более	…	…
2 сообщение(й) и более	…	…
5 сообщение(й) и более	…	…

Это не воронка по этапам продукта. Это кумулятивное распределение: сколько людей дошли до N-го сообщения. Считается так:

WITH msg_counts AS (
  SELECT ch.telegram_chat_id AS chat_id, COUNT(*) AS msg_cnt
  FROM messages m
  JOIN chats ch ON ch.id = m.chat_id
  WHERE m.role = 'user' AND $__timeFilter(m.created_at)
  GROUP BY ch.telegram_chat_id
),
eligible_chats AS (
  SELECT DISTINCT tc.id AS chat_id
  FROM telegram_chats tc
  WHERE $__timeFilter(tc.created_at)
),
msg_distribution AS (
  SELECT COALESCE(mc.msg_cnt, 0) AS msg_cnt, COUNT(*) AS users_count
  FROM eligible_chats ec
  LEFT JOIN msg_counts mc ON mc.chat_id = ec.chat_id   -- нули не теряем
  GROUP BY COALESCE(mc.msg_cnt, 0)
),
cumulative AS (
  SELECT md.msg_cnt,
         md.users_count,
         SUM(md.users_count) OVER (ORDER BY md.msg_cnt) AS cum_users,
         SUM(md.users_count) OVER ()                    AS total_users
  FROM msg_distribution md
)
SELECT
  CONCAT(msg_cnt, ' сообщение(й) и более')                               AS "Этап (X)",
  (total_users - cum_users) + users_count                                AS "Пользователи (остались)",
  ROUND(100 * ((total_users - cum_users) + users_count) / total_users, 1) AS "Доля (%)"
FROM cumulative
ORDER BY msg_cnt;

Ключевая деталь — LEFT JOIN в msg_distribution. Без него из знаменателя пропадают все, кто не написал ни одного сообщения, — то есть ровно те, чей отвал вы и пытаетесь измерить. Метрика становится красивой и бесполезной: конверсия «из написавших в написавших ещё раз».

Именно эта таблица дала самый прикладной результат всего проекта: обрыв стоит не там, где его искали. Основная масса отваливается не на десятом сообщении (усталость от продукта), а между первым и вторым (не понял, что происходит). Это меняет приоритет: не «улучшать персонажей», а «чинить онбординг». На аудитории в 300K это разница не косметическая — это разные статьи бюджета.

Отдельная история — как это писалось. Первая версия считала кумулятиву коррелированным подзапросом:

(SELECT SUM(d2.users_count) FROM msg_distribution d2 WHERE d2.msg_cnt <= d1.msg_cnt) AS cum_users

Работает, но это квадрат по числу различных длин чата — а их со временем становится больше. MySQL 8 умеет оконные функции, и SUM(...) OVER (ORDER BY msg_cnt) делает то же самое за один проход. В репозитории до сих пор живут обе версии в разных дашбордах — идеальная иллюстрация того, как копипаста расползается быстрее, чем правки.

Проблема 5: сессии, которых нет в базе

«Сколько раз в день человек возвращается в бота» — вопрос, на который в схеме нет ни одного поля. Сессий никто не пишет.

Зато есть тайминги сообщений, и работает старый приём из веб-аналитики: новая сессия начинается там, где разрыв между соседними сообщениями больше 30 минут.

SELECT ROUND(AVG(sess_cnt), 2) AS value
FROM (
  SELECT chat_id, SUM(is_new_session) AS sess_cnt
  FROM (
    SELECT
      m.telegram_chat_id AS chat_id,
      CASE
        WHEN LAG(m.created_at) OVER (PARTITION BY m.telegram_chat_id ORDER BY m.created_at) IS NULL
          OR TIMESTAMPDIFF(
               MINUTE,
               LAG(m.created_at) OVER (PARTITION BY m.telegram_chat_id ORDER BY m.created_at),
               m.created_at
             ) > 30
        THEN 1 ELSE 0
      END AS is_new_session
    FROM telegram_messages m
    JOIN telegram_chats tc ON tc.id = m.telegram_chat_id
    WHERE $__timeFilter(m.created_at)
  ) t
  GROUP BY chat_id
) s;

LAG(...) IS NULL в первом условии — это не защита от NULL, это и есть первая сессия: у самого первого сообщения предыдущего нет, значит оно открывает сессию.

Здесь мы сознательно взяли telegram_messages, а не messages: сессия — это про присутствие человека в боте вообще, а не про конкретного персонажа. Переключение между персонажами внутри получаса не должно порождать вторую сессию.

Порог в 30 минут — не истина, а соглашение, унаследованное из GA. Для бота с AI-персонажами он, скорее всего, неправильный: диалоговая сессия тут длиннее веб-сессии. Правильный порог достаётся из гистограммы межсообщенческих интервалов — по «локтю» распределения. Мы этого не сделали и честно живём с числом, которое сравнимо само с собой во времени, но не имеет абсолютного смысла.

Проблема 6: мультиселект в Grafana + MySQL = REGEXP и дыра

Дашборд по рефералам должен уметь фильтроваться по одной ссылке, по нескольким или по всем сразу. Переменная:

SELECT slug FROM telegram_links ORDER BY slug;

Дальше начинается специфика. У Grafana для SQL-источников нет честного биндинга списка в IN (...) — переменная разворачивается текстовой подстановкой до отправки запроса. Общепринятый костыль выглядит так:

WHERE tc.telegram_link_id IS NOT NULL
  AND $__timeFilter(tc.created_at)
  AND (
    '${ref_id:raw}' = ''                                    -- ничего не выбрано → без фильтра
    OR l.slug REGEXP CONCAT('^(', '${ref_id:pipe}', ')$')   -- выбрано → em0311|abc123|...
  );

Форматтер :pipe превращает мультиселект в a|b|c, а ^(...)$ не даёт REGEXP матчить подстроки: без якорей slug em03 поймал бы em0311, и отчёт по маленькому каналу тихо включил бы в себя большой.

Это работает и решает задачу. Но признаем прямо, что тут происходит: значение переменной подставляется в текст SQL как есть. Это конкатенация строк в запрос — то самое, за что бьют по рукам в любом другом месте кодовой базы.

Здесь это терпимо ровно потому, что:

значения переменной не вводятся руками, а приходят из SELECT slug FROM telegram_links;
дашборд закрыт и доступен только своим;
у datasource-пользователя Grafana права только на SELECT.

Убери любое из трёх — и получишь SQL-инъекцию через URL дашборда, потому что var-ref_id= живёт в адресной строке и подставляется в запрос. Проверьте прямо сейчас, что ваш аналитический пользователь в MySQL — read-only. Это тридцать секунд и один GRANT.

Проблема 7: $__timeFilter по двум таблицам через OR

В части панелей нужно было брать «пользователей, которые либо зарегистрировались в периоде, либо были активны в периоде». Написалось это буквально:

eligible_chats AS (
  SELECT DISTINCT tc.id AS chat_id
  FROM telegram_chats tc
  LEFT JOIN chats ch    ON ch.telegram_chat_id = tc.id
  LEFT JOIN messages m  ON m.chat_id = ch.id
  WHERE $__timeFilter(tc.created_at)
     OR $__timeFilter(m.created_at)
)

Две проблемы, и обе не про SQL как язык.

Производительность. OR между условиями на разные таблицы через LEFT JOIN не даёт оптимизатору использовать индекс ни по одной из них: нельзя сузить telegram_chats по дате, потому что строка может пройти по второй ветке условия. На проде это разворачивается в полный проход по messages — самой большой таблице в базе, которая растёт на миллион с лишним строк в сутки. Дашборд с большим интервалом открывался долго и держал соединение к базе, у которой в этот момент 75 RPS боевой нагрузки. Один открытый на месяц дашборд — и OLTP чувствует. Лечится разделением на UNION двух дешёвых запросов, каждый со своим индексом.

Смысл. Знаменатель метрики теперь — «новые ИЛИ активные», и это разные когорты, склеенные в одну. Retention по такой когорте нельзя сравнить с retention по когорте «только новые», а на соседней панели — как раз она. Две таблицы рядом, обе называются «вовлечённость», знаменатели разные.

Оба варианта — WHERE $__timeFilter(tc.created_at) и WHERE ... OR ... — живут в репозитории до сих пор. Разница между ними нигде не подписана, и это самая настоящая мина: цифры расходятся, обе панели правы.

Проблема 8: час пик, которого не было

Панель «Временные паттерны активности» показывает почасовую активность. Бакеты в ней считаются руками:

SELECT
  FROM_UNIXTIME(UNIX_TIMESTAMP(m.created_at) - (UNIX_TIMESTAMP(m.created_at) % 3600)) AS time,
  COUNT(DISTINCT ch.telegram_chat_id) AS value
FROM messages m ...
GROUP BY time

Приём рабочий: округление unix-времени вниз до часа. Но он обходит стороной $__timeGroupAlias(...), а вместе с ним — и всю работу Grafana с таймзонами. Бакет получается в таймзоне сервера MySQL, а дашборд открыт с timezone=browser.

Для одного часового пояса это косметика. Для бота, у которого локали размазаны от Европы до Азии, — это метрика, которая отвечает на вопрос «когда активны наши пользователи» временем моего сервера. «Пик в 23:00» не значит ничего, пока не сказано, чьи это 23:00.

Правильно — либо честный $__timeGroupAlias(m.created_at, 1h) и один явный часовой пояс на весь дашборд, либо (что для «когда людям удобно писать боту» куда полезнее) считать час в локальном времени пользователя, раз уж locale в telegram_chats есть.

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

Проблема 9: реферальная атрибуция, которой хватает ровно на один вопрос

Реферальные ссылки — это telegram_links.slug и счётчик visited, который инкрементит приложение. Плюс telegram_chats.telegram_link_id, который проставляется при создании чата.

Отсюда получается ровно одна честная панель:

SELECT
  l.slug                 AS ref_slug,
  l.visited              AS visits,       -- клики, счётчик приложения
  COUNT(DISTINCT tc.id)  AS chats_count   -- реально дошли до бота
FROM telegram_links l
LEFT JOIN telegram_chats tc ON tc.telegram_link_id = l.id
GROUP BY l.id, l.slug
ORDER BY l.visited DESC;

Разрыв между visits и chats_count — это и есть верхняя часть воронки канала, и по нему каналы отлично сортируются: одинаковый объём кликов при разной конверсии в старт видно сразу.

Дальше — стена, и стена архитектурная:

visited — счётчик, а не события. У него нет истории. Нельзя посмотреть клики за прошлый вторник, нельзя построить их динамику, нельзя нормально сравнить с регистрациями в том же интервале. $__timeFilter к нему неприменим в принципе: у числа нет даты. На панели «Визиты по ссылке» его и нет — это единственная панель в проекте, которая игнорирует таймпикер, и в этом её главная ловушка для читателя.
Атрибуция только first touch, и только навсегда. telegram_link_id пишется один раз при создании telegram_chats. Человек, пришедший по ссылке A, потом по B, потом купивший, — до конца жизни числится за A. Ни last touch, ни multi-touch на этих данных не построить, и никакой SQL этого не исправит: данных просто нет.
Мёртвые джойны. В доброй половине запросов живёт LEFT JOIN telegram_links l ON l.id = tc.telegram_link_id, где l дальше нигде не используется. Наследство копипасты из реферальных дашбордов в персонажные. Оптимизатор такой джойн обычно выкидывает, но читателю запроса он врёт: выглядит как «тут учтены рефералы», а не учтено ничего.

Вот эта строчка про first touch — самая дорогая в статье. Пока трафика мало, кажется, что и так сойдёт. Когда каналов становится пять и они начинают пересекаться, выясняется, что ретаргет вообще не может быть оценён: он по определению приводит людей, у которых telegram_link_id уже занят.

Проблема 10: A/B персонажей, который невозможно посчитать

У персонажей есть версии: characters → character_versions, актуальная лежит в characters.active_version_id. Промпты правятся, версии обновляются.

В аналитике это выглядит красиво:

names AS (
  SELECT c.id AS character_id,
         c.code AS character_code,
         COALESCE(chv.name, c.name) AS character_name
  FROM characters c
  LEFT JOIN character_versions chv ON chv.id = c.active_version_id
)

COALESCE тут — потому что имя может жить и в версии, и в самом персонаже, а актуальной версии может не быть вовсе.

Проблема глубже: в messages нет character_version_id. Сообщение знает персонажа, но не знает, какой промпт на него отвечал. Из этого следует, что:

нельзя сравнить вовлечённость до и после правки промпта;
любая метрика персонажа — это среднее по всем его версиям за период;
откатили неудачную версию — метрика «размазалась» и выглядит просто как шум;
вся панель «Уникальность и релевантность персонажей» с её relevance_score считает популярность персонажа-как-идеи, а не персонажа-как-текущего-промпта.

Это ровно тот случай, когда одно поле в одной таблице отделяет «у нас есть аналитика персонажей» от «у нас есть A/B-платформа для контента». Поле стоит одну миграцию. Мы его не добавили, потому что в момент, когда это стало понятно, версии уже жили без него достаточно долго, и половина истории всё равно была бы пустой.

Проблема 11: техничка, которая измеряет не то, что написано на панели

Дашборд «Техничка» — про инфраструктуру: очередь, падения, время генерации. Самая невинная на вид панель:

SELECT
  DATE(gs.created_at)                                      AS "Дата",
  AVG(TIMESTAMPDIFF(SECOND, gs.created_at, gs.updated_at)) AS "Среднее время (сек)",
  COUNT(*)                                                 AS "Количество"
FROM generation_states gs
WHERE gs.type = 'text'
  AND gs.updated_at IS NOT NULL
  AND $__timeFilter(gs.created_at)
GROUP BY DATE(gs.created_at);

«Среднее время генерации текста». Считается как updated_at - created_at.

updated_at — это ORM-поле. Оно обновляется при любом апдейте строки. Ретрай, смена статуса, дозапись метаданных постфактум — всё это уезжает в «время генерации». Метрика меряет не длительность генерации, а время жизни строки до последнего касания.

Что это значит на практике: чем хуже работает провайдер, тем сильнее метрика врёт — причём в какую сторону, предсказать нельзя. Прошёл ретрай — время выросло вдвое, хотя пользователь ждал столько же. Дописали статус через час — в среднем появился час.

И ровно на этой панели держится решение «менять провайдера или нет».

Чинится не в SQL, а в схеме: started_at / finished_at, выставляемые воркером явно. До тех пор надо хотя бы смотреть не на AVG, а на медиану и p95 — среднее по такому распределению не значит вообще ничего.

Две соседние панели, кстати, честные и полезные:

-- сколько задач ждёт прямо сейчас
SELECT COUNT(*) AS `Текущий размер очереди`
FROM jobs WHERE (reserved_at IS NULL OR reserved_at = 0);

-- где именно падает
SELECT COALESCE(queue,'default') AS `Очередь`, COUNT(*) AS `Ошибок`
FROM failed_jobs WHERE $__timeFilter(failed_at)
GROUP BY 1 ORDER BY 2 DESC;

Мораль в том, что на одном дашборде рядом стоят метрики с совершенно разной степенью доверия, и внешне они неотличимы. Размер очереди — факт. Среднее время генерации — оценка с неизвестной ошибкой. Обе нарисованы одинаковым шрифтом.

Где мы наврали сами себе

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

Из одиннадцати проблем выше в продакшн уехали три реально неверных числа. Два из них уже разобраны (топ персонажей, мерящий позицию в UI, и дефолтный now-6h из начала статьи). А вот третье не всплывало нигде — и оно худшее:

«Платящие активные пользователи по персонажам» — декартово произведение.

WITH base AS (
  SELECT ch.telegram_chat_id, ch.character_id
  FROM messages m
  JOIN chats ch ON ch.id = m.chat_id
  WHERE m.role = 'user' AND $__timeFilter(m.created_at)   -- ← строка на КАЖДОЕ сообщение
)
SELECT
  c.code,
  COUNT(DISTINCT b.telegram_chat_id) AS "Платные пользователи",
  COUNT(m.id)                        AS "Сообщений",
  ROUND(COUNT(m.id) / COUNT(DISTINCT b.telegram_chat_id), 2) AS "Среднее сообщений на пользователя"
FROM base b
JOIN paid_users p ON p.telegram_chat_id = b.telegram_chat_id
JOIN characters c ON c.id = b.character_id
JOIN messages m   ON m.role = 'user'
                 AND m.chat_id IN (SELECT ch.id FROM chats ch
                                   WHERE ch.telegram_chat_id = b.telegram_chat_id)
                 AND m.created_at BETWEEN $__timeFrom() AND $__timeTo()
GROUP BY c.code;

В base нет DISTINCT — там по строке на каждое сообщение. Затем messages джойнится второй раз, по всем диалогам пользователя, без привязки к персонажу. Результат: COUNT(m.id) — это примерно (сообщения персонажу) × (все сообщения пользователя), и колонка «Среднее сообщений на пользователя» показывает величину, у которой нет физического смысла.

COUNT(DISTINCT b.telegram_chat_id) при этом остаётся правильным. То есть панель одновременно содержит корректную колонку и колонку-выдумку. Это худший из возможных вариантов: если бы врало всё, заметили бы сразу.

Общее у всех трёх багов: ни одна цифра на дашборде не была ни разу сверена с ручным пересчётом по базе. Панель написана, панель показывает правдоподобное число, панель уходит в продакшн. Правдоподобность — не проверка.

Что бы я сделал иначе

По убыванию отношения пользы к затратам:

Таблица events с первого дня. (telegram_chat_id, type, payload JSON, created_at). Одна миграция и один хелпер в коде. Снимает разделы 3, 9 и 10 этой статьи целиком. На 1.5M событий в день она же станет и главным кандидатом на отдельное хранилище — но это уже следующий шаг.
Словарь метрик до первого SQL. Один markdown-файл: что такое пользователь, что такое активный, что такое сессия, какая таблица каноническая. Не «архитектурная документация», а десять строк, которые не дают двум панелям разойтись.
Явные тайминги вместо updated_at. started_at / finished_at в generation_states. Одна миграция превращает выдуманную метрику в настоящую.
character_version_id в messages. Одно поле, которое превращает контент-аналитику в A/B.
Read-only реплика + read-only пользователь. Второе — прямо сегодня, даже без первого. Особенно если у вас в дашборде живёт REGEXP CONCAT('^(', '${var:pipe}', ')$'). При 75 RPS живой нагрузки реплика перестаёт быть роскошью примерно сразу.
Сверка каждой новой панели руками. Один раз, на маленьком интервале, ручным SELECT. Ловит ровно те баги, которые нашлись сильно позже.
Подпись на каждой панели. Что именно считает знаменатель и в какой таймзоне бакеты. Grafana умеет описания панелей — все их игнорируют, включая меня.

Чеклист для тех, кто собирается делать так же

[ ] Определение «пользователя» записано словами и одинаково во всех запросах
[ ] Выбрана каноническая таблица событий/сообщений, вторая под запретом
[ ] LEFT JOIN там, где в знаменатель должны попадать нули (иначе retention врёт в плюс)
[ ] Кумулятивы — оконными функциями, не коррелированными подзапросами
[ ] Нет $__timeFilter(a.x) OR $__timeFilter(b.y) — вместо этого UNION двух запросов
[ ] Все временные бакеты — через $__timeGroup*, таймзона дашборда зафиксирована
[ ] Мультиселект-переменные — только через :pipe и с якорями ^(...)$
[ ] Пользователь datasource в MySQL имеет только SELECT
[ ] Дефолтный интервал дашборда соответствует реальному циклу данных, а не now-6h
[ ] Метрики, построенные на ORM-полях (updated_at), помечены как приблизительные
[ ] Каждая панель хотя бы раз сверена с ручным пересчётом

Аналитика на голом проде — это нормальное инженерное решение, когда решения нужно принимать сейчас, а бюджета на отдельный контур нет. Она даёт большую часть пользы за малую часть стоимости, и я бы сделал так же ещё раз даже на 300K MAU.

Только у неё есть свойство, о котором не пишут: она не падает. Сломанный запрос не бросает исключение — он рисует красивое число не той величины, и это число неделями ходит по чатам как факт, пока по нему закупают трафик. Единственная защита — руками пересчитать каждую цифру ровно один раз, в самом начале.

Ну и поменять дефолтный таймрейндж.

1.5 миллиона событий в день и ни одной таблицы events: как мы считали продуктовую аналитику 300K-бота по голому проду