Розробка звітів з вирви замовлень 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 тижні. Результат — дашборд, який показує, де теряються замовлення та скільки це коштує бізнесу.