Author: Mahesh Katula
Domain: Supply Chain & Logistics Analytics
Amazon handles millions of shipments daily. As volume increases, so do challenges like route congestion, warehouse bottlenecks, and delivery delays.
This project utilizes Advanced SQL to analyze the delivery network, identifying root causes of delays and providing data-driven recommendations to improve the Global On-Time Delivery Percentage.
I have recorded a detailed video explaining the analysis, SQL logic, and key findings of this project.
| File Name | Description |
|---|---|
Amazon_Logistics_Analysis.sql |
The complete SQL script containing data cleaning, transformation, and analytical queries. |
Amazon_Logistics_Project.pdf |
A detailed presentation deck summarizing findings, visualizations, and strategic recommendations. |
- Database: MySQL
- Data Cleaning:
COALESCE,CAST, Duplicate Removal - Advanced Techniques: * CTEs (Common Table Expressions) for modular logic.
- Window Functions (
ROW_NUMBER,DENSE_RANK) for ranking and stratification. - Aggregations (
GROUP BY,HAVING) for summary statistics.
- Window Functions (
- Metrics: Efficiency Ratios, KPI Calculation, Time-Difference Calculations.
Before analysis, the dataset was scrubbed to ensure accuracy.
- Duplicate Removal: Utilized a CTE with
ROW_NUMBER()partitioned byOrder_IDto identify and delete duplicate records. - Null Handling: Imputed missing
Traffic_Delay_Minvalues using the average delay specific to each route (rather than a global average) to maintain data granularity. - Validation: Enforced logical consistency (e.g., ensuring Delivery Date > Order Date).
- Calculated the precise delay (
Actual_Arrival - Expected_Arrival) for every shipment. - Identified the Top 10 Routes with the highest average delay duration.
- Used
DENSE_RANK()to prioritize orders with the most severe delays within each warehouse.
- Efficiency Ratio: Engineered a custom metric: "Distance-to-Time Efficiency Ratio".
- Identified specific routes that are short in distance but take disproportionately long to travel.
- Flagged High-Risk Routes where >20% of shipments failed to meet the delivery deadline.
- Analyzed processing throughput to identify facility bottlenecks.
- Used CTEs to compare individual Warehouse Processing Times against the Global Average.
- Ranked facilities based on their contribution to total network delays.
- Ranked Delivery Agents based on On-Time Delivery Percentage.
- Identified underperforming agents (scoring <80%) for targeted retraining programs.
- Correlated Agent Speed vs. Reliability to determine if rushing deliveries led to errors.
- Analyzed shipment checkpoints to find common reasons for failure (Weather, Traffic, Operations).
- Final KPI: Calculated the Global On-Time Delivery Percentage (Found to be 56%).
Based on the SQL analysis, the following strategic actions are recommended:
- Route Re-Planning: Immediate review of the specific routes identified in Task 3 that exhibit low efficiency ratios despite short distances.
- Warehouse Operations: Deep-dive investigation into the bottom 3 warehouses (identified in Task 4) that are consistently slower than the global average.
- Agent Training: Initiate a performance improvement plan for agents identified in Task 5 with <80% on-time rates.
Mahesh Katula Connect with me on LinkedIn!
