Как посчитать Perfect Order Rate в SQL

Зачем Perfect Order Rate

Perfect Order — order, который right on every dimension: time, completeness, condition, paperwork. Holy grail metric. World-class logistics — 95%+.

Criteria

Perfect Order Rate = orders_perfect / total_orders × 100%

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

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

SELECT
    DATE_TRUNC('week', delivered_date) AS week,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (
        WHERE delivered_date <= promised_date
          AND complete = TRUE
          AND damaged = FALSE
          AND invoice_correct = TRUE
    ) AS perfect_orders,
    COUNT(*) FILTER (
        WHERE delivered_date <= promised_date
          AND complete = TRUE
          AND damaged = FALSE
          AND invoice_correct = TRUE
    )::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS perfect_order_rate_pct
FROM orders
WHERE delivered_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

По criteria

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE delivered_date <= promised_date) AS on_time,
    COUNT(*) FILTER (WHERE complete) AS complete,
    COUNT(*) FILTER (WHERE NOT damaged) AS no_damage,
    COUNT(*) FILTER (WHERE invoice_correct) AS correct_invoice,
    -- Each criterion %
    COUNT(*) FILTER (WHERE delivered_date <= promised_date)::NUMERIC * 100 / COUNT(*) AS on_time_pct,
    COUNT(*) FILTER (WHERE complete)::NUMERIC * 100 / COUNT(*) AS complete_pct
FROM orders
WHERE delivered_date >= CURRENT_DATE - INTERVAL '30 days';

Декомпозиция

Если Perfect Order Rate 85%, какой criterion main culprit?

WITH stats AS (
    SELECT
        order_id,
        delivered_date <= promised_date AS on_time,
        complete,
        NOT damaged AS no_damage,
        invoice_correct
    FROM orders
    WHERE delivered_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    'on_time' AS criterion,
    COUNT(*) FILTER (WHERE NOT on_time) AS failures,
    COUNT(*) FILTER (WHERE NOT on_time)::NUMERIC * 100 / COUNT(*) AS failure_pct
FROM stats
UNION ALL
SELECT 'complete', COUNT(*) FILTER (WHERE NOT complete), COUNT(*) FILTER (WHERE NOT complete)::NUMERIC * 100 / COUNT(*)
FROM stats
UNION ALL
SELECT 'no_damage', COUNT(*) FILTER (WHERE NOT no_damage), COUNT(*) FILTER (WHERE NOT no_damage)::NUMERIC * 100 / COUNT(*)
FROM stats
UNION ALL
SELECT 'invoice_correct', COUNT(*) FILTER (WHERE NOT invoice_correct), COUNT(*) FILTER (WHERE NOT invoice_correct)::NUMERIC * 100 / COUNT(*)
FROM stats;

Top failure mode — focus first.

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

Ошибка 1. Multiplicative trap. 4 criteria × 95% each → 0.95^4 = 81% Perfect Order. High individual scores ≠ high Perfect Order.

Ошибка 2. Subjective measures. «Damaged» — кто определяет? Customer feedback, returns data? Договоритесь.

Ошибка 3. Invoice correctness. Часто not tracked в systems. Need integration with finance.

Ошибка 4. Time-zone for on-time. Promise date in customer's tz vs delivery date в operations tz.

Ошибка 5. Window choice. Daily fluctuations — use rolling.

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

• Как посчитать on-time delivery в SQL
• Как посчитать fill rate в SQL
• Как посчитать CSAT в SQL
• SLA / SLO / SLI на собесе SA

FAQ

Какой Perfect Order Rate ok?

World-class: 95%+. Average: 85-90%. <80% — major operational issues.

Multiplicative effect?

Yes. Чтобы Perfect Order 95%, каждый criterion ~99%.

Damaged orders track?

Через returns + customer complaints + visual inspection.

Invoice correctness?

ERP system + accounts receivable. Track «invoice disputes».

Perfect Order vs CSAT?

Связаны. Perfect Order — operational. CSAT — perception.