Как посчитать order fulfillment time в SQL

Зачем fulfillment time

Customer купил — ждёт shipping update. Если 5 дней нет даже отправки — заказ кажется потерянным, CSAT падает. Order fulfillment time = от order placement до shipped status. Часто confused с delivery time (включает carrier). Здесь — только internal часть.

Формула

fulfillment_time = shipped_at − ordered_at

Считают в часах для same-day, днях для standard.

Fulfillment time в SQL

WITH orders AS (
    SELECT
        order_id,
        ordered_at,
        shipped_at,
        EXTRACT(EPOCH FROM (shipped_at - ordered_at)) / 3600 AS hours_to_ship
    FROM orders_fulfilled
    WHERE shipped_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    COUNT(*) AS orders,
    AVG(hours_to_ship) AS avg_hours,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours_to_ship) AS median_hours,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY hours_to_ship) AS p95_hours,
    COUNT(*) FILTER (WHERE hours_to_ship <= 24)  AS shipped_within_1d,
    COUNT(*) FILTER (WHERE hours_to_ship <= 48)  AS shipped_within_2d
FROM orders;

p95 важнее avg — это «worst case» для customer expectations.

Декомпозиция по шагам

WITH stage_times AS (
    SELECT
        order_id,
        ordered_at,
        picked_at,
        packed_at,
        shipped_at
    FROM order_status_history
    WHERE shipped_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    AVG(EXTRACT(EPOCH FROM (picked_at - ordered_at)) / 3600) AS avg_hours_to_pick,
    AVG(EXTRACT(EPOCH FROM (packed_at - picked_at)) / 3600) AS avg_hours_pick_to_pack,
    AVG(EXTRACT(EPOCH FROM (shipped_at - packed_at)) / 3600) AS avg_hours_pack_to_ship
FROM stage_times;

Bottleneck — на каком шаге max time. Часто pack_to_ship (waiting for carrier pickup).

По warehouse

SELECT
    warehouse_id,
    COUNT(*) AS orders,
    AVG(hours_to_ship) AS avg_hours,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY hours_to_ship) AS p95_hours
FROM orders_with_warehouse
WHERE shipped_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY warehouse_id
ORDER BY p95_hours DESC;

Один warehouse 3× медленнее другого = ops issue, training или staffing.

Частые ошибки

Ошибка 1. Mean vs median. Distribution right-skewed (несколько late outliers). Median больше показывает «normal».

Ошибка 2. Включать weekends/holidays. Order Friday evening → ship Monday morning. 60-hour gap включает 48h non-business. Filter business hours.

Ошибка 3. Считать только shipped. Orders «stuck» в processing — тоже проблема. Include как «open свыше 48h».

Ошибка 4. Не сегментировать на shipping method. Same-day delivery vs standard 5-day — разные expectations и metrics.

Ошибка 5. One target для seasonal. Black Friday week — fulfillment time удваивается или утраивается. Plan для spikes.

Связанные темы

• Как посчитать on-time delivery в SQL
• Как посчитать perfect order rate в SQL
• Как посчитать fill rate в SQL
• Как посчитать time-to-resolution в SQL

FAQ

Какой fulfillment time нормален?

Same-day services: меньше 4 часов. Standard e-com: 24-48 hours. Marketplace 3rd-party: 1-3 days.

Pick vs pack vs ship — что дольше?

Часто pack_to_ship (waiting carrier). Pick / pack — controllable.

Weekend handling?

Если closed, fulfillment time для Sunday orders starts Monday morning. Track «business hours fulfillment».

Customer expectation?

Amazon стандарт — 1-2 days shipping. Industry pressure to match.

Stuck orders?

Orders свыше 72h в pending status — daily report. Manual investigation.