Shipment Tracking — On-Time Delivery & Carrier Performance

Once orders leave the warehouse, carrier performance drives customer experience. We measure on-time delivery rate, average transit time, and late shipments by carrier and service level.

-- Carrier performance dashboard
SELECT
    carrier,
    service_level,
    COUNT(*)                                                    AS total_shipments,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL)            AS delivered,
    COUNT(*) FILTER (WHERE delivered_at IS NULL
                       AND promised_at < NOW())                 AS overdue,
    ROUND((100.0 * COUNT(*) FILTER (
            WHERE delivered_at IS NOT NULL
              AND delivered_at <= promised_at
        ) / NULLIF(COUNT(*) FILTER (WHERE delivered_at IS NOT NULL), 0))::NUMERIC, 1)                                                          AS otd_pct,
    ROUND((AVG(EXTRACT(EPOCH FROM (delivered_at - shipped_at)) / 3600)
        FILTER (WHERE delivered_at IS NOT NULL))::NUMERIC, 1)                                                          AS avg_transit_hours,
    ROUND((AVG(freight_cents) / 100.0)::NUMERIC, 2)                       AS avg_freight_usd
FROM lg_shipments
GROUP BY carrier, service_level
ORDER BY carrier, service_level;