Shipping & Delivery Performance Analysis

Live Dashboard

Having trouble interacting with the embed? Click here to open the full report in its own page →

KPIs

Project overview

The raw dataset arrived at line-item grain (multiple rows per order) with inconsistent formatting and a built-in lateness flag that needed independent verification before it could be trusted for analysis.

• Source: DataCo Smart Supply Chain for Big Data Analysis (Kaggle)
• Scope: 180,519 order line items, de-duplicated to 65,752 unique orders
• Timeframe: January 2015 – January 2018 (3 full years)

Methodology

Findings

• The first thing I ruled out was geography. Late delivery rates only vary by 7.4 percentage points across 23 regions (52.6%–60.0%), but by over 60 points across shipping modes (39.9%–100%). Shipping mode explains roughly 8x more of the variation than region does, so I stopped looking at geography and started looking at how each shipping mode was actually built.
• What I found was a broken SLA, not bad execution. First Class promises 1-day delivery but averages 2, a 100% late rate. Second Class promises 2 days but averages 4. Standard Class is the only mode actually hitting its own promise (3.99 actual vs. a 4-day SLA).
• I checked whether this was a seasonal blip. It wasn't. The late rate held steady in a narrow 57%–59% band across three full years, which rules out a one-time event.
• Cost tracks volume, not how late an order runs. Standard Class carries the biggest total cost of delay (~$225K) despite having the best on-time rate, purely because of how much volume runs through it. First Class and Second Class land at nearly identical dollar exposure (~$145–148K each) even with very different late rates, since average order value stays consistent (~$477–506) across every mode.
• I also cross-checked the dataset's own built-in lateness flag against my own calculation and found a 1,650-order mismatch, traced back to cancelled orders rather than just picking whichever number looked cleaner.

Recommendations

• Renegotiate the SLA for First Class and Second Class to something actually achievable, or invest in the process fixes needed to hit what's currently promised.
• Use Standard Class as the internal benchmark. It's the only mode meeting its own SLA, and it's already carrying the largest share of volume (57% of all orders).
• Fix this by shipping mode, not by region. A regional improvement push would be aiming at the wrong lever entirely.

What this demonstrates

• SQL applied to a real relational schema with genuine data quality problems, not toy queries.
• Independent verification of a dataset's own built-in flags rather than trusting them at face value.
• Translating an operations problem into a dollar-impact metric a business audience acts on.