Developing reports on the 1C-Bitrix order funnel

Developing order funnel reports for 1C-Bitrix

Store gets 500 orders per day, but only 320 are fulfilled. Where do the other 180 go? At which stage — "New", "Confirmed", "Assembled", "Sent to delivery" — does the biggest dropout happen? Without order funnel report, you can answer this only manually, browsing orders in admin. This is unacceptable at scale of hundreds of orders per day.

Order funnel report visualizes the path from order creation to completion, shows conversion between statuses and reveals bottlenecks in processing.

Order status model in Bitrix

Statuses stored in b_sale_status table. Each order has current status (STATUS_ID in b_sale_order), and transition history fixed in b_sale_order_change — table with fields ORDER_ID, TYPE, DATA, DATE_CREATE, USER_ID.

Problem: b_sale_order_change stores ALL order changes in formalized form, not just status change. Records with status change have TYPE = 'ORDER_STATUS_CHANGED'. Extract old and new status from DATA field (JSON).

Typical e-commerce status chain:

N (New) → P (Confirmed) → A (Assembled) → G (Sent to courier) → F (Completed)
                                                              → D (Cancelled)

Atypical transitions (returns, reopening) also recorded and important for analysis.

Deep-dive: building funnel by statuses

Funnel — is counting orders that passed through each status and conversion between adjacent stages.

SQL query for funnel calculation per period:

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;

Stage conversion = orders_reached / prev_count * 100%. For example, if 450 out of 500 reached "Confirmed" status — conversion 90%, loss 10% — these are orders cancelled before confirmation.

Alternative approach — via current statuses. Simpler, but less accurate: SELECT STATUS_ID, COUNT(*) FROM b_sale_order WHERE DATE_INSERT >= ... GROUP BY STATUS_ID. Shows order distribution by current status, but doesn't account for dynamics — order that passed all stages and completed isn't visible in intermediate statuses.

Processing time at each stage

Second most important funnel metric — how much time order spends in each status. Calculated as difference DATE_CREATE between adjacent b_sale_order_change records.

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;

Median is more important than average. One order stuck for a week skews the average. P95 shows "tail" — orders with anomalously long processing.

Analysis of cancellation reasons

Cancellations — funnel leakage. Report shows: from which status cancellation happened, who cancelled (customer/manager), reason (if filled in order properties).

Grouping cancellations by stage reveals system problems:

• Cancellation at "New" stage — customer changed mind, duplicate order, test orders
• Cancellation after confirmation — product out of stock (stock issue)
• Cancellation after assembly — address error, customer unreachable

Funnel visualization

Funnel displayed as horizontal or vertical diagram with decreasing sections. Implementation via Chart.js with chartjs-plugin-funnel plugin or via SVG generation on backend.

Place on dashboard:

• Funnel chart — visual funnel with conversion percentages
• Table — details: count, conversion, average time per stage
• Line chart — conversion dynamics by weeks (trend: improving or declining)
• Filters — period, manager, payment system, delivery method

Excel export via PhpSpreadsheet with separate sheets: funnel summary, manager breakdown, list of cancelled orders with reasons.

Automation and alerts

Order funnel report useful not just retrospectively. Set up Bitrix agent checking hourly:

• Conversion "New → Confirmed" in last hour < 70% → notification to manager
• Average time in "New" status > 1 hour → notification to senior manager
• Cancellations count per day > 20% of orders → alert

Development timeline

Total timeline — 1-2 weeks. Result — dashboard showing where orders are lost and what it costs the business.