Построение воронки продаж в BI сводится к трём вещам: корректно зафиксировать события по каждому этапу, стабильно посчитать конверсию по этапам воронки продаж (без двойного счёта и смещения по времени) и настроить диагностику drop-off, чтобы находить узкие места. Дальше вы упаковываете это в дашборд воронки продаж BI и мониторите отклонения.
Краткая карта важных выводов и решений
- Начните с выбора гранулярности (лид/сделка/контакт) и строгих определений этапов: это снимает 80% спорных расхождений между отчётами.
- Фиксируйте этапы как события с timestamp и ключом сущности; сессии используйте только для digital-этапов, не для CRM-стадий.
- Соберите минимальный набор полей (id, stage, time, owner, source) и встройте проверки качества до загрузки в BI.
- Считайте конверсию на когорте входа в этап и с единым правилом дедупликации (первое/последнее событие этапа).
- Для узких мест смотрите не только drop-off, но и время до перехода, долю возвратов назад и долю "зависших".
- Для витрины и визуализации разделяйте: слой фактов (события), слой агрегатов (по дням/неделям), слой отчёта (Power BI/другой BI).
Архитектура воронки в BI: события, сессии и точность гранулярности
Кому подходит. Если у вас есть повторяемые этапы (лид → квалификация → предложение → сделка) и нужно единообразно сравнивать каналы/менеджеров/продукты, аналитика воронки продаж в BI даёт контролируемую методику и автоматический мониторинг.
Когда лучше не делать воронку в BI в лоб. Если этапы не определены и меняются "по ощущениям", нет уникальных идентификаторов сущностей, или CRM не хранит историю стадий (только текущее значение) - сначала исправьте процесс и сбор истории, иначе отчёт будет "красивым, но неверным".
Что выбрать в качестве "сущности воронки"
- Лид - для верхнего уровня (маркетинг/SDR), где важна скорость обработки и первичная квалификация.
- Сделка/Opportunity - для классической продажи, где этапы и сумма привязаны к коммерческому циклу.
- Контакт/аккаунт - для B2B с несколькими сделками на клиента, но требуется аккуратная логика многократных входов.
События vs сессии: не смешивайте модели
- События этапов: "сделка переведена в этап X" с точным временем и автором изменения. Это базовый формат для построения воронки продаж в BI.
- Сессии: нужны, если воронка начинается на сайте/в приложении (просмотр → форма → отправка), но дальше должна "склеиваться" с CRM по идентификаторам (client_id, lead_id, phone/email hash).
Сбор и валидация событий: источники, схемы и гарантии качества данных
Чтобы "воронка продаж в Power BI" была воспроизводимой, сначала подготовьте источники, схему событий и набор проверок. Цель - чтобы одно и то же событие не появлялось дважды, не терялось при загрузке и не ломало историчность стадий.
Что понадобится (доступы и инструменты)
- Источники: CRM (история стадий/аудит), телефония/чат (если этапы завязаны на контакты), веб-аналитика (если есть digital-этапы).
- Доступы: чтение таблиц/эндпоинтов истории стадий, справочников менеджеров/каналов, прав на выгрузку изменений (incremental).
- Хранилище: DWH/ODS (PostgreSQL/ClickHouse/BigQuery/SQL Server) или хотя бы слой витрин, куда выгружаются события.
- ETL/ELT: любой инструмент, поддерживающий инкрементальные загрузки и контроль дублей (по бизнес-ключу события).
- BI: Power BI или аналог для визуализации и контроля (для большинства команд удобна связка DWH + Power BI).
Минимальная схема события этапа
- entity_id (id лида/сделки)
- event_time (timestamp изменения этапа)
- stage_from, stage_to
- actor_id (кто изменил)
- source_system (CRM/сайт/импорт)
- ingested_at (время загрузки в DWH, для контроля задержек)
Валидация: примеры SQL/псевдокода для контроля качества
1) Поиск дублей событий (одинаковая сущность, время и целевой этап).
SELECT entity_id, event_time, stage_to, COUNT(*) AS cnt
FROM fact_stage_events
GROUP BY entity_id, event_time, stage_to
HAVING COUNT(*) > 1;2) Контроль "времени назад" (когда событие пришло позже, чем следующее по времени).
WITH s AS (
SELECT
entity_id,
event_time,
stage_to,
LAG(event_time) OVER (PARTITION BY entity_id ORDER BY event_time) AS prev_time
FROM fact_stage_events
)
SELECT *
FROM s
WHERE prev_time IS NOT NULL AND event_time < prev_time;3) Проверка пропусков ключевых полей.
SELECT
SUM(CASE WHEN entity_id IS NULL THEN 1 ELSE 0 END) AS null_entity_id,
SUM(CASE WHEN event_time IS NULL THEN 1 ELSE 0 END) AS null_event_time,
SUM(CASE WHEN stage_to IS NULL THEN 1 ELSE 0 END) AS null_stage_to
FROM fact_stage_events;Практика безопасных гарантий. Заведите "карантин" для некорректных строк (missing id/time/stage), логируйте аномалии и не допускайте их в витрину воронки, иначе разъедутся конверсии и периоды.
Моделирование этапов воронки: определение шагов, условия перехода и сегментация
Перед расчётами зафиксируйте правила: что считается входом в этап, как обрабатываются повторные входы и что делать с откатами. Это основа для стабильной конверсии по этапам воронки продаж и сопоставимости отчётов.
Риски и ограничения (учтите до внедрения)
- Смена этапов задним числом (ручные правки в CRM) ломает тренды по дням; нужен аудит и правило пересчёта.
- Повторные входы в этап без политики дедупликации дают завышение объёма на шагах.
- Смешение сущностей (лиды и сделки в одной воронке) усложняет интерпретацию; разделяйте или нормализуйте.
- Неединые определения этапов между командами превращают воронку в "спорный дашборд", а не в инструмент управления.
- Задержки загрузки создают ложные "провалы" сегодня; нужен мониторинг свежести данных.
-
Зафиксируйте список этапов и их порядок. Определите конечный набор стадий (без "прочее" внутри воронки) и порядок прохождения. Если в процессе есть развилки (несколько продуктов/каналов), делайте отдельные воронки или сегменты, а не "ветки" в одном шаге.
- Документируйте этап: бизнес-смысл, критерий попадания, владелец процесса.
- Согласуйте, где начинается воронка: первый лид, первая сделка или первый контакт.
-
Определите правило входа в этап (event definition). Для каждого шага задайте условие: какое событие считается входом (stage_to = X), и какой timestamp является "истиной" (event_time из CRM-аудита, а не время выгрузки).
- Если CRM хранит только текущий этап - сначала включите историю изменений или снимайте снапшоты и восстанавливайте историю.
-
Выберите дедупликацию и обработку повторных входов. Самый устойчивый вариант: для конверсии использовать первое попадание в этап в рамках сущности, а для времени прохождения - первое попадание и ближайший следующий этап.
- Политика A: "first touch по этапу" - стабильна для конверсий.
- Политика B: "last touch по этапу" - полезна, если этап часто корректируют и важен финальный статус перед переходом.
- Опишите правила для откатов (stage rollback). Решите, считается ли возврат назад ошибкой данных, особенностью процесса или отдельным сценарием. Отразите это отдельными метриками (доля откатов), а не "чините" конверсию вручную.
-
Соберите витрину фактов этапов и витрину входов. В фактах храните все события, а для расчётов сделайте производную таблицу "первый вход в этап" на сущность.
- Факт: все перемещения стадий.
- Входы: минимальный event_time по (entity_id, stage).
- Добавьте сегментацию, но только после стабилизации ядра. Подключите канал, менеджера, продукт, регион, тип клиента. Сегменты должны быть справочниками (dimension tables), чтобы не ломать модель при изменениях.
- Проверьте логику на контрольной выборке. Возьмите несколько десятков сущностей и вручную сравните последовательность этапов в BI с CRM-аудитом. Это быстрее, чем искать расхождения по всей базе.
Метрики конверсии и устойчивые способы их расчёта по этапам
Для управляемости нужны метрики, которые не "прыгают" из-за дублей, перезагрузок и смены логики. Используйте расчёты от витрины входов в этап, а не от "всех событий", и фиксируйте единое окно анализа.
Как считать конверсию по шагам (логика)
- Step conversion: доля сущностей, вошедших в этап N+1 среди вошедших в этап N.
- Cumulative conversion: доля дошедших до этапа N среди вошедших в первый этап.
- Time to next stage: медиана/перцентили времени между входами в этапы (при необходимости - с отсечением выбросов по бизнес-правилу).
Чек-лист проверки результата перед публикацией в BI
- Воронка строится на одной сущности (лид или сделка), ключ не меняется по ходу этапов.
- Каждый шаг использует одинаковое правило дедупликации (first/last) и оно задокументировано.
- Период в отчёте относится к дате входа в первый этап (когорта), либо явно подписано другое правило.
- Есть контроль "свежести" данных: максимальный event_time и ingested_at отображаются на дашборде.
- Сумма по сегментам не даёт "магического" роста объёма (признак дублей или многозначных атрибутов).
- Конверсия не превышает 100% на переходах (иначе дубли/разные сущности/неверные связи).
- Повторные входы и откаты отражены отдельными показателями, а не замаскированы фильтрами.
- Есть сверка по контрольной выборке: несколько сущностей совпадают с CRM по порядку и датам этапов.
Диагностика узких мест: показатели drop-off, приоритеты исправлений и тестирование гипотез
Узкое место - это не просто низкая конверсия. Это участок, где потери велики, исправление контролируемо, а эффект можно проверить. В аналитике воронки продаж в BI делайте диагностику через сочетание drop-off, времени прохождения и качества данных.
Типовые ошибки и как их распознать (risk-aware)
- Ложный drop-off из-за задержки загрузки. Провал "сегодня" при норме "вчера" - часто проблема свежести; лечится мониторингом ingested_at и отсечением неполного дня.
- Завышение конверсии из-за дублей событий. Если конверсия на шаге > 100% или объём на шаге растёт - ищите дубли по бизнес-ключу события.
- Смещение периода (cohort mismatch). Считаете по дате закрытия, а фильтруете по дате создания - получаете несопоставимые тренды; закрепите правило "дата входа в первый этап" или явно маркируйте "по дате перехода".
- Смешение лидов и сделок. Переход "лид → сделка" как смена сущности ломает шаги; нужен мост (lead_id → opportunity_id) и отдельный шаг "создана сделка".
- Откаты этапов скрыты фильтрами. Визуально воронка выглядит лучше, но процесс деградирует; добавьте метрику "доля rollback" и список причин.
- Неоднозначные сегменты (несколько источников/каналов на одну сущность). Суммы по сегментам больше общего итога - признак M2M; решайте правилом приоритета или отдельной моделью атрибуции.
- Смена справочников без версионирования. Переименования этапов/каналов меняют историю; используйте surrogate key и таблицу соответствий.
- Тесты гипотез без контроля "качества входа". Улучшили конверсию, но снизили качество лидов; добавьте downstream-метрику (например, доля выигранных/маржа), чтобы не оптимизировать "верх" в ущерб "низу".
Приоритизация исправлений (быстрое правило)
- Сначала исправляйте то, что влияет на достоверность: дубли, пропуски, неверные связи сущностей.
- Потом - то, что даёт большой вклад в потери: шаги с высоким drop-off и большим объёмом входа.
- Затем - оптимизация скорости: этапы с большой длительностью и высокой долей "зависших".
Визуализация и отчётность: дашборды, таблицы сравнения и автоматизация мониторинга
Дашборд нужен не "для красоты", а чтобы быстро ответить: где просадка, в каком сегменте и это данные или процесс. Дашборд воронки продаж BI обычно собирают в Power BI, но структура отчётности важнее инструмента.
Что показывать на дашборде
- Объём входа в воронку (когорта) и переходы по этапам.
- Step conversion и cumulative conversion по сегментам.
- Drop-off (потери) и время до следующего этапа.
- Индикаторы качества данных: свежесть, доля дублей (если считаете), доля неизвестных значений.
Таблица для сравнения подходов к построению и обновлению
| Подход | Когда уместен | Риски | Как снизить риск |
|---|---|---|---|
| Воронка на витрине событий (DWH) + Power BI | Нужна единая логика для нескольких отчётов, много сегментов и контроль качества | Дольше старт, нужна дисциплина схемы и тестов | Сначала MVP на 3-5 этапов, затем расширение; автоматические проверки дублей/пропусков |
| Расчёт в Power BI (DAX) поверх "сырых" таблиц | Быстрый прототип, ограниченный объём данных и один владелец отчёта | Легко получить разные трактовки, тяжёлые меры, сложнее отлаживать | Вынести дедупликацию и "первый вход в этап" в Power Query/SQL, оставить DAX для агрегаций |
| Готовая CRM-воронка (встроенные отчёты) | Нужна базовая операционная картинка без кастомной интеграции | Ограниченная сегментация, слабый контроль качества, трудно склеить с digital | Использовать как контрольный источник, параллельно готовить события в DWH |
Альтернативы и когда они лучше классической воронки
- Когортный отчёт по неделям/месяцам - если цикл длинный и важна динамика созревания (сколько дошло до этапов через N недель).
- Скоринговая модель качества входа - если проблема в том, что конверсия падает из-за "дешёвых" лидов, а не из-за работы этапа.
- Процесс-майнинг/граф переходов - если много откатов и нелинейных маршрутов, и нужно увидеть реальные траектории.
- Отчёт по SLA этапов - если узкое место в скорости реакции (например, "не обработали за X часов" важнее конверсии).
Ответы на типовые затруднения при построении и анализе воронки
Почему конверсия на шаге получается больше 100%?
Обычно это дубли событий или разные сущности на соседних шагах (лид → сделка). Проверьте дедупликацию и наличие мостика между lead_id и opportunity_id.
Какой датой фильтровать воронку: созданием, входом в этап или закрытием?
Для сопоставимости используйте когорту по дате входа в первый этап. Если фильтруете по дате перехода/закрытия, явно подпишите это в отчёте и не сравнивайте напрямую с когортным вариантом.
Что делать, если CRM хранит только текущую стадию без истории?
Без истории вы не построите корректные переходы. Включите аудит изменений или снимайте регулярные снапшоты и восстанавливайте события переходов.
Как учитывать возвраты на предыдущие этапы?
Не "вычищайте" откаты фильтрами. Считайте отдельную метрику rollback-rate и анализируйте причины возврата, параллельно оставляя основную конверсию по первому прохождению.
Можно ли всё сделать только в Power BI без DWH?
Для прототипа - да, но риск расхождений и тяжёлых расчётов выше. Устойчивее вынести слой событий и дедупликации в SQL/витрину, а Power BI оставить для визуализации и сегментов.
Как понять, что просадка - это проблема данных, а не продаж?
Сравните свежесть загрузки, долю пропусков ключевых полей и аномалии дублей с предыдущими периодами. Если одновременно "поехали" техметрики качества, сначала чините пайплайн.
Как связать digital-воронку сайта и CRM-этапы в одну цепочку?
Нужен единый идентификатор или правило склейки (например, lead_id, client_id, устойчивый hash контакта) и явный шаг "создан лид/сделка". Без этого объединённая воронка будет давать некорректные переходы.
