Как посчитать On-Time Delivery в SQL

Зачем On-Time Delivery

В логистике / e-com OTD — главная customer experience метрика. Promised «2 days», delivered в 5 days → customer angry, refund + negative review. OTD коррелирует с retention.

Формула

On-Time Delivery Rate = on_time_orders / total_orders × 100%

«On-time» — delivered <= promised_date.

Базовый расчёт

Данные: orders(order_id, promised_date, delivered_date).

SELECT
    DATE_TRUNC('week', delivered_date) AS week,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE delivered_date <= promised_date) AS on_time,
    COUNT(*) FILTER (WHERE delivered_date <= promised_date)::NUMERIC * 100
        / NULLIF(COUNT(*), 0) AS otd_rate_pct
FROM orders
WHERE delivered_date IS NOT NULL
  AND delivered_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

По hubs

SELECT
    h.hub_name,
    h.region,
    COUNT(*) AS orders,
    COUNT(*) FILTER (WHERE o.delivered_date <= o.promised_date)::NUMERIC * 100
        / NULLIF(COUNT(*), 0) AS otd_rate
FROM orders o
JOIN hubs h ON h.hub_id = o.dispatched_from
WHERE o.delivered_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY h.hub_name, h.region
HAVING COUNT(*) >= 100
ORDER BY otd_rate DESC;

Hubs с low OTD — investigate (staffing, processes).

SLA compliance

WITH stats AS (
    SELECT
        sla_tier,
        COUNT(*) AS orders,
        COUNT(*) FILTER (WHERE delivered_date <= promised_date) AS on_time,
        AVG(EXTRACT(EPOCH FROM (delivered_date - promised_date)) / 86400)
            FILTER (WHERE delivered_date > promised_date) AS avg_delay_days
    FROM orders
    WHERE delivered_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY sla_tier
)
SELECT
    sla_tier,
    orders,
    on_time::NUMERIC * 100 / NULLIF(orders, 0) AS sla_compliance_pct,
    avg_delay_days
FROM stats
ORDER BY sla_tier;

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

Ошибка 1. Pending orders excluded. Order delivered yesterday — already in stats. Order пока in-transit — not counted. Может be late.

Ошибка 2. Timezone. Promised_date в UTC vs local. Adjust.

Ошибка 3. Cancelled orders. Cancelled — не delivered. Exclude from denominator.

Ошибка 4. Definition «on-time». Same-day delivery promised by 6pm. Delivered at 6:01pm — on-time или late?

Ошибка 5. Customer-side delays. Customer not home → re-attempt. Not delivery fault, но counts as late?

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

• Как посчитать конверсию в SQL
• Как посчитать CSAT в SQL
• Как посчитать retention в SQL
• SLA / SLO / SLI на собесе SA

FAQ

Какой OTD considered ok?

E-com same-day: 90%+. Standard delivery: 95%+. Premium services: 99%+.

OTD падает — что делать?

1. Hub analysis (which hub broken?). 2) Carrier review. 3) Promise window — может надо пересмотреть.

OTD vs FAST?

OTD — promise vs actual. FAST — short window (same-day, next-day).

Customer-caused delays?

Стандарт: exclude если customer-fault (not home, wrong address).

Multi-leg shipments?

Multi-stop delivery — каждая leg vs final. Stand-art: final destination.