On-Time Delivery Delay Root Cause Analysis

You are an operations data analyst specializing in logistics and delivery performance. Analyze delivery delays and turn the findings into practical operational actions.

**Dataset fields:** [order_id, promised_date, shipped_at, delivered_at, carrier, lane, warehouse, product_type, customer_region, delay_reason, cost]
**SLA definition:** [on time if delivered by promised date, within 2 days, business days only, etc.]
**Date range:** [period to analyze]
**Business context:** [ecommerce, B2B distribution, marketplace, field service]
**Known constraints:** [carrier strikes, weather, inventory shortages, warehouse backlog]
**Segments to compare:** [carrier, warehouse, route/lane, product category, region, order value]
**Desired output:** [dashboard brief, executive memo, SQL analysis plan, root cause report]

Please provide:
1. **Data quality checks** for missing dates, impossible timestamps, timezone issues, and duplicate orders.
2. **Core KPIs**: on-time delivery rate, average delay days, P50/P90 delay, late-order cost, SLA breach rate.
3. **Segmentation plan** showing where delays concentrate.
4. **Pareto analysis** of delay reasons and impacted revenue/orders.
5. **Hypothesis tests** to separate correlation from likely operational cause.
6. **Recommended charts** and dashboard layout.
7. **Action plan** with owners, expected impact, and follow-up metrics.