Аннотация

В CRM было 412 успешных сделок, в выгрузке — 397, а дашборд показывал 384. Вместо очередной ручной сверки я разобрал путь одной метрики от исходной записи до финального графика. Ошибка оказалась не в BI, а сразу в нескольких незаметных правилах обработки данных.

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

До тех пор, пока кто-нибудь не открывает CRM и не считает сделки вручную.

В моём случае разница выглядела так. За один и тот же месяц CRM показывала 412 завершённых продаж. В таблице после выгрузки было 397. На итоговом дашборде — 384.

Разница между 412 и 384 — почти 7%. Для красивого графика это мелочь. Для отчёта по выручке, конверсии менеджеров или эффективности рекламы — уже совсем нет.

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

Самая первая ошибка оказалась методологической.

В CRM успешная сделка определялась по текущему статусу. Если карточка находилась в стадии успешно реализовано, она попадала во встроенный отчёт.

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

На первый взгляд правила почти одинаковые.

Но нашлось 11 карточек со статусом успешной сделки и пустой датой закрытия. Менеджеры когда-то передвигали их в финальную стадию вручную, но поле с датой по разным причинам не заполнилось.

CRM считала их.

ETL-запрос — нет.

Получились первые 11 потерянных продаж.

До этого момента я вообще был уверен, что проблема находится где-то в выгрузке или BI. На деле две системы просто отвечали на немного разные вопросы.

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

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

После этого стало понятно, какие поля действительно нужны для расчёта:

• идентификатор сделки;
• идентификатор клиента;
• текущий статус;
• время изменения статуса;
• история переходов между стадиями;
• признак отмены;
• сумма;
• дата создания.

И вот здесь проявилась следующая проблема. Текущий снимок CRM вообще не позволял корректно ответить на вопрос, сколько продаж произошло в прошлом месяце.

Представим сделку.

28 июля она успешно закрыта.

3 августа клиент отказался, и менеджер перевёл карточку в отменённые.

Если 31 июля построить отчёт, это продажа.

Если 10 августа открыть CRM и попросить показать текущие успешные сделки за июль, её уже нет.

Получается странный эффект: историческая выручка меняется задним числом в зависимости от текущего состояния карточек.

У нас таких случаев оказалось немного, но для аналитики этого достаточно.

Я выгрузил историю изменений статусов и восстановил состояние сделки на конец отчётного периода.

После этого исходные 412 записей пришлось пересчитать.

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

В результате эталонная выборка уже состояла не из 412, а из 404 продаж.

То есть восемь записей из первоначального отчёта CRM не соответствовали определению метрики, которое мы только что зафиксировали.

Это был первый важный вывод всего разбора: прежде чем сравнивать системы, нужно построить одну эталонную выборку на уровне идентификаторов. Сравнивать только итоговые числа почти бесполезно.

После нормализации определения продажи осталось семь пропавших записей.

Здесь я уже сравнивал не агрегаты, а ID сделок.

Получились два множества:

CRM — 404 идентификатора.

Хранилище после загрузки — 397.

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

У шести из семи была одна особенность: их изменили примерно между полуночью и двумя часами ночи.

В CRM время хранилось с часовым поясом пользователя. В промежуточной базе временные метки приводились к UTC. А ETL выбирал записи по условию от начала до конца месяца, используя локальные границы периода.

В результате сделка, закрытая условно 1 августа в 01:20 по Москве, после преобразования превращалась в событие предыдущего календарного дня по UTC.

На другом конце месяца происходило обратное.

Это классическая неприятность временных данных: timestamp сам по себе почти ничего не значит без понимания timezone и бизнес-правила, по которому дата относится к периоду.

Седьмая запись потерялась по другой причине. У неё отсутствовал менеджер, а при сборке аналитической таблицы использовался INNER JOIN со справочником сотрудников.

Упрощённо логика выглядела примерно так:

сделки соединить с менеджерами по manager_id.

Для нормальной записи всё работало.

Если manager_id был пустым или сотрудника уже удалили из справочника, строка полностью исчезала из результирующей таблицы.

После замены соединения на LEFT JOIN сделка вернулась.

В итоге эталон и хранилище сравнялись: 404 против 404.

Но дашборд всё ещё показывал 384.

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

Проверил количество записей непосредственно в витрине — 404.

Запустил тот же период через SQL — 404.

Открыл дашборд — 384.

Значит, проблема находилась уже после хранилища.

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

Первая — скрытый фильтр по типу сделки.

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

Из выборки исчезали 14 повторных сделок.

Вторая причина была ещё менее заметной.

На странице стоял фильтр по менеджерам. В справочнике один сотрудник был переведён в архив, и его значение не попадало в активный набор фильтра. Шесть продаж существовали в витрине, но BI автоматически отбрасывал их при построении визуализации.

404 минус 14 минус 6.

Получилось ровно 384.

Никаких сломанных формул.

Никаких серьёзных ошибок в базе.

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

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

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

Если завтра снова появится расхождение, вручную проходить всю цепочку слишком дорого.

Поэтому я добавил контрольные значения между этапами.

Теперь логика выглядит примерно так:

• CRM по единому определению метрики — 404;
• сырая загрузка — 404;
• очищенный слой — 404;
• аналитическая витрина — 404;
• BI до пользовательских фильтров — 404;
• итоговый показатель — 404.

Это не означает, что число на каждом этапе всегда обязано совпадать.

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

Но тогда изменение количества должно быть объяснимым.

Если было 404, а стало 397, система должна уметь ответить, какие именно семь записей исчезли и по какому правилу.

Именно поэтому я перестал считать простой COUNT достаточной проверкой.

Гораздо полезнее контролировать не только количество, но и сами множества идентификаторов.

Допустим:

A — множество ID до трансформации.

B — множество ID после.

Тогда A − B показывает записи, которые потерялись.

B − A — те, которые неожиданно появились.

Для поиска ошибок такая проверка оказалась намного эффективнее сравнения двух цифр в Excel.

Когда основные расхождения были исправлены, я проверил ещё одну вещь — уникальность ключа сделки.

В витрине было 404 уникальных сделки, но строк оказалось больше.

Причина — присоединение таблицы товаров.

Одна сделка могла содержать несколько позиций. После обычного JOIN одна строка сделки превращалась в три, четыре или пять строк.

Если считать COUNT DISTINCT deal_id, всё выглядит правильно.

Если затем случайно посчитать SUM(revenue), выручка начинает дублироваться.

Например, была сделка на 120 000 рублей с четырьмя товарами.

После соединения она превращалась в четыре строки, в каждой из которых оставалась исходная сумма 120 000.

Количество продаж через DISTINCT — одна.

Выручка через SUM — уже 480 000.

Этот тип ошибки особенно неприятен тем, что разные показатели на одном дашборде могут вести себя по-разному. Количество сделок правильное, средний чек странный, а выручка завышена.

Поэтому перед каждым JOIN я теперь задаю довольно примитивный вопрос: какая кардинальность связи?

1:1.

1:N.

N:M.

Если после соединения меняется уровень детализации таблицы, агрегаты нужно пересматривать отдельно.

Сам JOIN при этом может быть абсолютно корректным.

Ошибка появляется позже, когда метрику считают так, будто одна строка по-прежнему соответствует одной сделке.

Когда начал разбирать расхождение 412 против 384, ожидал найти конкретный баг.

Например, неправильный WHERE или сбой синхронизации.

Получилась совсем другая картина.

В CRM исходная цифра была завышена из-за способа определения исторического статуса.

При выгрузке часть записей попадала в соседний период из-за часового пояса.

Одна сделка исчезала из-за INNER JOIN.

На уровне BI старый фильтр убирал повторные продажи.

Ещё один фильтр исключал сделки архивного сотрудника.

Каждая проблема по отдельности небольшая.

Вместе они давали почти семипроцентное расхождение.

Именно поэтому подобные ошибки плохо ищутся сверху вниз по итоговому числу.

Нужно раскладывать метрику на путь данных.

После этого случая перед публикацией любого важного показателя я прохожу несколько проверок.

Не доверяю названию метрики. Сначала формулирую её определение на уровне конкретной записи.

Не начинаю сверку с агрегата. Беру небольшой период и выгружаю список ID.

Проверяю количество записей после каждого существенного преобразования.

Отдельно смотрю временные зоны и границы периода.

Перед JOIN определяю кардинальность связи.

Проверяю NULL в ключевых полях.

Сравниваю результат BI с запросом непосредственно к витрине.

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

Самая полезная проверка при этом оказалась очень простой.

Берётся десять случайных сделок из итогового отчёта и вручную прослеживается их путь назад до CRM.

Затем берётся десять сделок из CRM и проверяется путь вперёд до дашборда.

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

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

Теперь смотрю на это иначе.

Неправильная цифра чаще появляется не в одной точке. Она постепенно собирается из небольших решений: как определили метрику, какую дату использовали, какой JOIN выбрали, что сделали с NULL, как обработали историю статусов, какие фильтры остались внутри BI.

Самое опасное здесь то, что каждый отдельный компонент может работать без ошибок.

CRM корректно показывает свой отчёт.

ETL корректно выполняет запрос.

База корректно хранит данные.

BI корректно применяет фильтр.

А итоговая цифра всё равно неправильная для того вопроса, который задаёт бизнес.

Поэтому теперь я считаю надёжным не тот дашборд, где цифры красиво сходятся.

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

Если этого сделать нельзя, передо мной пока не аналитика.

Просто очень аккуратно нарисованное число.