Supplier SLA Performance — Lead Time and Reliability

Supplier performance analysis tracks whether vendors deliver on time, within promised lead times, and how their reliability affects your inventory position.

1. Actual vs Promised Lead Time per Supplier

SELECT
    sup.name                        AS supplier,
    sup.lead_time_days              AS promised_lead_days,
    sup.reliability_score,
    COUNT(po.id)                    AS total_orders,
    COUNT(po.id) FILTER (WHERE po.received_at IS NOT NULL) AS received_orders,
    ROUND((AVG(
        CASE WHEN po.received_at IS NOT NULL THEN
            DATE_PART('day', po.received_at - po.ordered_at)
        END
    ))::NUMERIC, 1)                           AS avg_actual_lead_days,
    ROUND((AVG(
        CASE WHEN po.received_at IS NOT NULL THEN
            DATE_PART('day', po.received_at - po.ordered_at) - sup.lead_time_days
        END
    ))::NUMERIC, 1)                           AS avg_delay_days
FROM lg_suppliers sup
LEFT JOIN lg_purchase_orders po ON po.supplier_id = sup.id
GROUP BY sup.id, sup.name, sup.lead_time_days, sup.reliability_score
ORDER BY avg_delay_days DESC NULLS LAST;

What This Returns

supplier                | promised_lead_days | avg_actual_lead_days | avg_delay_days
------------------------+--------------------+----------------------+----------------
Epsilon Goods           |                 25 |                 30.1 |            5.1
Delta Parts Co          |                 18 |                 21.3 |            3.3
Alpha Manufacturing     |                 21 |                 23.8 |            2.8
...

2. On-Time Delivery Rate by Supplier

SELECT
    sup.name,
    COUNT(po.id) FILTER (WHERE po.received_at IS NOT NULL) AS delivered,
    COUNT(po.id) FILTER (WHERE po.received_at IS NOT NULL
        AND po.received_at::DATE <= po.expected_at)        AS on_time,
    COUNT(po.id) FILTER (WHERE po.received_at IS NOT NULL
        AND po.received_at::DATE > po.expected_at)         AS late,
    ROUND((100.0 *
        COUNT(po.id) FILTER (WHERE po.received_at IS NOT NULL
            AND po.received_at::DATE <= po.expected_at)
        / NULLIF(COUNT(po.id) FILTER (WHERE po.received_at IS NOT NULL), 0))::NUMERIC, 1) AS otd_rate_pct
FROM lg_suppliers sup
LEFT JOIN lg_purchase_orders po ON po.supplier_id = sup.id
GROUP BY sup.name
ORDER BY otd_rate_pct DESC;

3. Supplier Performance Score Card

Combine multiple metrics into a composite score:

WITH metrics AS (
    SELECT
        sup.id,
        sup.name,
        sup.reliability_score,
        ROUND((AVG(
            CASE WHEN po.received_at IS NOT NULL THEN
                DATE_PART('day', po.received_at - po.ordered_at) - sup.lead_time_days
            END
        ))::NUMERIC, 1) AS avg_delay,
        ROUND((100.0 *
            COUNT(po.id) FILTER (WHERE po.received_at IS NOT NULL
                AND po.received_at::DATE <= po.expected_at)
            / NULLIF(COUNT(po.id) FILTER (WHERE po.received_at IS NOT NULL), 0))::NUMERIC, 1) AS otd_pct
    FROM lg_suppliers sup
    LEFT JOIN lg_purchase_orders po ON po.supplier_id = sup.id
    GROUP BY sup.id, sup.name, sup.reliability_score
)
SELECT
    name,
    reliability_score,
    avg_delay,
    otd_pct,
    ROUND(((reliability_score * 40)
        + (otd_pct / 100.0 * 40)
        + GREATEST(0, (10 - COALESCE(avg_delay, 0)) / 10.0 * 20))::NUMERIC, 1) AS composite_score
FROM metrics
ORDER BY composite_score DESC;

4. Cost of Late Deliveries

Late deliveries often force expensive spot purchases. Estimate the cost impact:

SELECT
    sup.name,
    COUNT(po.id) FILTER (WHERE po.received_at::DATE > po.expected_at) AS late_orders,
    SUM(po.quantity * po.unit_cost)
        FILTER (WHERE po.received_at::DATE > po.expected_at)           AS late_order_value_usd,
    ROUND((AVG(po.received_at::DATE - po.expected_at)
        FILTER (WHERE po.received_at::DATE > po.expected_at))::NUMERIC, 1)       AS avg_lateness_days
FROM lg_suppliers sup
JOIN lg_purchase_orders po ON po.supplier_id = sup.id
WHERE po.received_at IS NOT NULL
GROUP BY sup.name
ORDER BY late_order_value_usd DESC NULLS LAST;

Key Takeaway

Always compare actual lead time against the promised lead time rather than measuring absolute delivery speed — a supplier that always delivers in 30 days is more reliable than one that promises 5 days but regularly takes 15. The composite score pattern (weighted sum of normalised metrics) is directly reusable for vendor scorecards, employee performance reviews, and customer health scores.

← Previous Next →