Shipment On-Time Delivery Rate

Business Use Case

Executives use this KPI to identify bottlenecks in the delivery process and improve logistics planning to enhance customer satisfaction.

Business Criticality

High

Journey / Navigation Area

Generic

Owning Function

Data Architecture Team

Scope

Global

Formula

(On-Time Shipments / Total Shipments) × 100

Calculation Notes

1️⃣ Exclude shipments with customer-requested delays. 2️⃣ Account for timezone differences in international deliveries. 3️⃣ Ensure accurate delivery scheduling. 4️⃣ Communicate potential delays to customers. 5️⃣ Monitor shipment status regularly.

Technical Details

SQL Queries

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
SELECT order_id, 
       COUNT(CASE WHEN delivery_date <= promised_date THEN 1 END) / COUNT(order_id) AS on_time_delivery_rate
FROM `your-project.erp.shipment_logs`
WHERE delivery_status = 'Delivered'
GROUP BY order_id

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
SELECT order_id, 
       COUNT(CASE WHEN delivery_date <= promised_date THEN 1 END) / COUNT(order_id) AS on_time_delivery_rate
FROM aep_backend_dataset.shipment_logs
WHERE delivery_status = 'Delivered'
GROUP BY order_id

Source Data

Dependencies

Report Attributes

Recommended Reporting Views

Segment Eligibility

Governance

GitHub

Community Discussion