Разработка отчетов по воронке заказов 1С-Битрикс

Разработка отчетов по воронке заказов 1С-Битрикс

Магазин получает 500 заказов в день, но выполняется только 320. Куда деваются остальные 180? На каком этапе — «Новый», «Подтверждён», «Собран», «Передан в доставку» — происходит наибольший отсев? Без отчёта по воронке заказов ответить на этот вопрос можно только вручную, перебирая заказы в админке. Это неприемлемо при масштабе от нескольких сотен заказов в день.

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

Модель статусов заказа в Битриксе

Статусы хранятся в таблице b_sale_status. Каждый заказ имеет текущий статус (STATUS_ID в b_sale_order), а история переходов фиксируется в b_sale_order_change — таблице с полями ORDER_ID, TYPE, DATA, DATE_CREATE, USER_ID.

Проблема: b_sale_order_change хранит ВСЕ изменения заказа в формализованном виде, не только смену статуса. Записи со сменой статуса имеют TYPE = 'ORDER_STATUS_CHANGED'. Из поля DATA (JSON) извлекаем старый и новый статус.

Типичная цепочка статусов e-commerce:

N (Новый) → P (Подтверждён) → A (Собран) → G (Передан курьеру) → F (Выполнен)
                                                                  → D (Отменён)

Нетипичные переходы (возвраты, повторное открытие) тоже фиксируются и важны для анализа.

Deep-dive: построение воронки по статусам

Воронка — это подсчёт количества заказов, прошедших через каждый статус, и конверсии перехода между соседними этапами.

SQL-запрос для расчёта воронки за период:

WITH status_transitions AS (
    SELECT
        o.ID AS order_id,
        o.DATE_INSERT,
        s.SORT AS status_sort,
        s.ID AS status_id,
        ROW_NUMBER() OVER (PARTITION BY o.ID ORDER BY oc.DATE_CREATE) AS transition_num
    FROM b_sale_order o
    JOIN b_sale_order_change oc ON oc.ORDER_ID = o.ID
    JOIN b_sale_status s ON s.ID = JSON_EXTRACT(oc.DATA, '$.STATUS_ID')
    WHERE oc.TYPE = 'ORDER_STATUS_CHANGED'
      AND o.DATE_INSERT >= '2024-01-01'
),
max_status AS (
    SELECT
        order_id,
        MAX(status_sort) AS max_reached_sort
    FROM status_transitions
    GROUP BY order_id
)
SELECT
    s.ID AS status_id,
    s.SORT,
    (SELECT COUNT(*) FROM max_status ms WHERE ms.max_reached_sort >= s.SORT) AS orders_reached,
    LAG((SELECT COUNT(*) FROM max_status ms WHERE ms.max_reached_sort >= s.SORT))
        OVER (ORDER BY s.SORT) AS prev_count
FROM b_sale_status s
WHERE s.TYPE = 'O'
ORDER BY s.SORT;

Конверсия каждого этапа = orders_reached / prev_count * 100%. Например, если до статуса «Подтверждён» дошли 450 из 500 — конверсия 90%, потеря 10% — это заказы, отменённые до подтверждения.

Альтернативный подход — через текущие статусы. Проще, но менее точен: SELECT STATUS_ID, COUNT(*) FROM b_sale_order WHERE DATE_INSERT >= ... GROUP BY STATUS_ID. Показывает распределение заказов по текущему статусу, но не учитывает динамику — заказ, прошедший все этапы и выполненный, не виден в промежуточных статусах.

Время обработки на каждом этапе

Вторая важнейшая метрика воронки — сколько времени заказ проводит в каждом статусе. Считается как разница DATE_CREATE между соседними записями в b_sale_order_change.

SELECT
    status_id,
    AVG(time_in_status) AS avg_minutes,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY time_in_status) AS median_minutes,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY time_in_status) AS p95_minutes
FROM (
    SELECT
        order_id,
        status_id,
        EXTRACT(EPOCH FROM (next_transition - transition_time)) / 60 AS time_in_status
    FROM transitions_with_next
) sub
GROUP BY status_id;

Медиана важнее среднего. Один заказ, зависший на неделю, перекашивает среднее. P95 показывает «хвост» — заказы с аномально долгой обработкой.

Анализ причин отмен

Отмены — это утечка из воронки. Отчёт показывает: с какого статуса произошла отмена, кто отменил (клиент/менеджер), причина (если заполняется в свойствах заказа).

Группировка отмен по этапу, на котором они произошли, выявляет системные проблемы:

• Отмена на этапе «Новый» — клиент передумал, дубль заказа, тестовые заказы
• Отмена после подтверждения — товар закончился на складе (проблема с остатками)
• Отмена после сборки — ошибка в адресе, клиент не выходит на связь

Визуализация воронки

Воронка отображается как горизонтальная или вертикальная диаграмма с уменьшающимися секциями. Реализация через Chart.js с плагином chartjs-plugin-funnel или через SVG-генерацию на бэкенде.

На дашборде размещаем:

• Funnel chart — визуальная воронка с процентами конверсии
• Таблица — детализация: количество, конверсия, среднее время на этапе
• Line chart — динамика конверсии по неделям (тренд: улучшается или ухудшается)
• Фильтры — период, менеджер, платёжная система, способ доставки

Экспорт в Excel через PhpSpreadsheet с отдельными листами: сводка воронки, детализация по менеджерам, список отменённых заказов с причинами.

Автоматизация и алерты

Отчёт по воронке полезен не только ретроспективно. Настраиваем агент Битрикса, который ежечасно проверяет:

• Конверсия «Новый → Подтверждён» за последний час < 70% → уведомление руководителю
• Среднее время в статусе «Новый» > 1 час → уведомление старшему менеджеру
• Количество отмен за день > 20% от заказов → алерт

Сроки разработки

Общий срок — 1-2 недели. Результат — дашборд, который показывает, где теряются заказы и сколько денег это стоит бизнесу.