Delivery Route Delay Analysis Builder

You are a logistics data analyst. Build a delivery route delay analysis for the operation below.

Business type: [grocery, furniture, parcel, pharmacy, field service, B2B supplies, food delivery]
Delivery model: [in-house fleet, third-party carrier, hybrid, gig drivers, scheduled appointments]
Dataset available: [route ID, stop sequence, promised window, actual arrival, driver, vehicle, zone, customer notes]
Time period: [date range]
Granularity: [stop-level, route-level, driver-day, carrier-day]
Delay definition: [minutes late, missed window, failed attempt, customer reschedule]
Operational context: [dispatch cutoff, route planning tool, warehouse loading, traffic, weather, service time]
Known issues: [late departures, access problems, long dwell time, stock delays, driver shortages]
Segments to compare: [zone, driver, carrier, product type, appointment window, day of week, route length]
Data quality concerns: [missing timestamps, manual status updates, timezone errors, GPS gaps]
Decision audience: [dispatch, ops leadership, carrier manager, warehouse, customer support]
Tools: [SQL, spreadsheet, Python, BI tool, route planning export]
Target outcome: [improve on-time rate, reduce failed attempts, rebalance routes, fix loading delays]

Create:
1. Data cleaning and validation checklist for delivery timestamps and route fields.
2. Metric definitions: on-time rate, average delay, p90 delay, dwell time, departure lag, failed attempt rate.
3. Segment analysis plan by route, zone, driver, carrier, time window, product, and stop sequence.
4. Root-cause hypothesis tree separating warehouse, dispatch, driver, customer, carrier, and external factors.
5. SQL or spreadsheet calculation outline for delay metrics.
6. Dashboard layout with filters, trend charts, heatmaps, exception tables, and drilldowns.
7. Statistical or practical tests to distinguish noisy routes from real patterns.
8. Recommended actions by delay driver with owner and expected impact.
9. Executive summary template for weekly operations review.
10. Caveats for weather, holidays, route changes, and incomplete GPS data.

Keep the analysis tied to operational decisions, not just charts.