Where is a global retailer losing time and money — and what would actually fix it?
An end-to-end analysis of 180,519 order-items from a global supply chain operation, built to turn a vague sense of "things are going wrong" into specific, quantified findings and business recommendations. Data was loaded and modeled in MySQL, analyzed with SQL, and visualized in an interactive Power BI dashboard.
A global retailer ships products to customers across five world markets. Management suspected two problems but couldn't quantify them: deliveries were often late, and some orders seemed to lose money. This project set out to answer three operational questions:
- What drives late deliveries? Which shipping methods, regions, or products are the real problem?
- Which orders actually lose money — and why?
- Where is the single highest-leverage fix?
54.8% of all deliveries are late — but the cause isn't a bad region or a bad product. Late rates are nearly flat across all 23 regions (49%–58%) and all 50 categories (55%–58%). The entire story lives in shipping method:
| Shipping mode | Late rate |
|---|---|
| First Class | 95.3% |
| Second Class | 76.6% |
| Same Day | 45.7% |
| Standard Class | 38.1% |
The mechanism: the warehouse ships at a roughly constant real pace (~4 days), but the premium tiers promise faster than the operation can deliver. First Class promises 1 day and takes 2 — so it's late 95% of the time. This is a service-level-agreement (SLA) calibration problem, not a logistics breakdown.
Recommendation: Recalibrate delivery promises for First and Second Class to realistic windows. This alone converts most "late" deliveries into "on-time" — with zero change to operations.
18.7% of order-items lose money, totaling ~$3.88M — nearly wiping out the ~$3.97M in net profit. The intuitive suspects don't explain it:
- By department: loss rate 17.7%–23.5% (flat)
- By discount level: 18.4%–19.0% (flat) — discounts do not drive losses
- Deep-loss orders are statistically identical to profitable ones on discount, price, and quantity
Instead, the loss is concentrated in magnitude: a small "deep-loss" tail of ~8.7% of orders accounts for 85.3% of all losses.
Recommendation: Since no product or segment is the culprit, the fix isn't cutting a category — it's a transaction-level margin guardrail that flags any order falling below a margin threshold before it ships. The fact that losses aren't explained by any available field also signals a data gap: transaction-level cost data should be connected for deeper root-cause analysis.
A two-page interactive Power BI dashboard, one story per page:
KPI cards, late rate by shipping mode, promised-vs-actual days, and a market slicer for interactivity.
KPI cards, the flat loss-rate-by-discount chart (proving no structural driver), and the concentration of losses in the deep-loss tail.
- MySQL — loaded and structured the raw 180K-row dataset (
LOAD DATA INFILE), with a typed schema. - SQL — delay and profitability analysis: aggregation,
CASEsegmentation,GROUP BY/HAVING, ratio and concentration analysis. - Power BI + DAX — data model and measures (
AVERAGE,DIVIDE,FILTER,SUMX,SWITCH), conditional formatting, interactive slicers. - Data cleaning — removed PII columns (passwords, emails, names), resolved a latin-1/UTF-8 encoding mismatch, and handled null markers.
supply-chain-analysis/
├── README.md
├── sql/
│ ├── load_dataco.sql # schema + data load
│ ├── 01_delay_analysis.sql # what drives late delivery
│ └── 02_profit_analysis.sql # where losses hide
├── powerbi/
│ └── dataco_powerbiDashBoard.pbix
└── images/
├── page1_delivery.png
└── page2_profitability.png
DataCo Smart Supply Chain dataset (Constante, Silva & Pereira, 2019), published on Kaggle. 180,519 order-items across 65,752 orders and 20,652 customers, spanning five global markets.
I came to data analytics from the business side: I co-founded Freshuk, an online fresh-produce business, and helped run and scale it from its early stages through to its sale. At its peak, Freshuk handled around 5,000 orders a month across roughly 400 products, with a team of about 35. Running it meant looking at every issue from several angles at once — what's happening operationally, how it affects the customer, and what it ultimately means for profitability.
That's exactly how I approach data. I don't stop at a pattern or a KPI; I ask what's driving it, which decision or process sits behind it, and what action should follow. In this analysis, the delays turned out to be a promise-setting problem rather than an execution one, and the losses were concentrated in a small tail of extreme cases rather than a whole failing category. Those distinctions matter because they lead to very different business decisions — and my operational background is what helps me connect the numbers to how a business actually works.