A data analytics project I built to understand why supply chains lose money — and where to find it back. Visit live Dashboard
I kept seeing "supply chain analytics" in job descriptions but struggled to find a portfolio project that went beyond basic dashboards. So I decided to build one end-to-end — from raw data to SQL to a machine learning forecast — using only SQL and no Python.
The dataset is 180,000 real orders from a global sporting goods distributor. I spent several weeks on this, running queries, finding things that didn't make sense, digging into why, and eventually building something I'm genuinely proud of.
I expected to find that certain suppliers were causing late deliveries. What I actually found was that every single supplier, region, and warehouse had almost identical late delivery rates — all clustered between 56% and 61%.
When everything fails the same way, it's not a people problem or a supplier problem. It's a system problem. The scheduled delivery days in the order management system are set too aggressively. That's a one-line configuration fix with immediate network-wide impact.
The other one that caught me off guard: AIR freight was 87% late while SEA freight arrived early on average. The business was paying more for a slower, less reliable service.
| Dashboard | Business Question |
|---|---|
| Executive Overview | Where are we losing money and why? |
| Inventory Health | What's running out vs sitting still? |
| Supplier Performance | Why is 57% of everything late? |
| Cost & Demand Forecast | Where can we save and what's coming? |
| Tool | What I used it for |
|---|---|
| Google BigQuery | Data warehouse, all SQL queries |
| BigQuery ML | ARIMA demand forecast — entirely in SQL |
| Looker Studio | 4 dashboards connected live to BigQuery |
| SQL | 12 analytics queries |
| GitHub | Version control and project documentation |
No Python. No Spark. No complicated infrastructure. Just SQL and a free BigQuery sandbox.
Source:
- DataCo SMART SUPPLY CHAIN (Kaggle)
- 180,519 orders · 3 years of history (2015–2018)
- Cleaned and transformed using BigQuery SQL
- One upload issue fixed using BigQuery character map V2
| # | File | What I was trying to answer |
|---|---|---|
| 01 | late_delivery_analysis | Which regions and modes have the worst delivery performance? |
| 02 | shipping_mode_performance | Are we using the right shipping mode for each order type? |
| 03 | inventory_turnover | How efficiently is stock converting to sales by category? |
| 04 | stockout_analysis | Which products are about to run out — and what does that cost? |
| 05 | overstock_detection | Which products are sitting on shelves wasting money? |
| 06 | supplier_scorecard | Which suppliers are reliable and which are causing problems? |
| 07 | supplier_lead_time_analysis | Are lead times predictable enough to plan around? |
| 08 | demand_trends | How does demand change by month and category? |
| 09 | revenue_by_category | Which categories drive profit vs just revenue? |
| 10 | customer_segment_analysis | Do B2B and B2C channels behave differently? |
| 11 | cost_optimization | Where are we overspending on logistics? |
| 12 | warehouse_efficiency | Which warehouses perform well and which need attention? |
Trained an ARIMA_PLUS time series model directly in SQL — no Python, no separate ML infrastructure. The model auto-detects seasonality and handles outliers. I excluded October 2017 manually because the data showed a 95% demand drop that month across every category simultaneously — not real seasonality, a data capture issue.
Fishing and Cleats forecasted at under 10% uncertainty (strong yearly seasonality detected). Cardio Equipment came back flat — which is useful to know, it means a simple constant reorder policy works fine.
Connected directly to BigQuery — no CSV exports, no manual refresh.
- Executive Overview — where is money being lost and why
- Inventory Health — stockout risk vs overstock by product
- Supplier Performance — scorecard, lead time variance, OTD rates
- Cost & Demand Forecast — shipping modal shift + 6-month forecast
1. The late delivery problem is a system issue, not a supplier issue Every supplier, region, and warehouse showed 56–61% late delivery rates. That tight clustering means the root cause is the scheduled delivery day settings — set too short across the entire system.
2. AIR freight is the worst performing mode 87% late rate vs 40% for SEA. SEA freight was arriving slightly early on average. The business is paying a premium for speed it's not getting.
3. The inventory picture is contradictory The Perfect Fitness Rip Deck was selling 70 units per day approaching stockout. At the same time, the Polar Loop Activity Tracker was selling 0.35 units per day — essentially frozen stock. Understocked on fast movers, overstocked on slow ones. That's a buying strategy problem more than a supply chain problem.
4. Golf Bags & Carts — highest margin, lowest revenue 19.1% gross margin — best in the portfolio. Ranked 49th in revenue. Either demand is genuinely low or it's an undiscovered commercial opportunity.
5. October 2017 data quality issue Every outdoor category dropped 93–97% in October 2017 while tech categories stayed normal. Not seasonality — missing data. Excluded from the forecast model. Good reminder: always visualise before modelling.
- More data sources. The dataset only has order-level data. Real inventory snapshots would make the stockout analysis much more precise — right now I'm inferring stock levels from demand velocity.
- Better supplier data. The "suppliers" here are departments, not actual vendor entities. Real supplier analysis needs vendor-level data with contract terms.
- Automated anomaly detection. I found the October 2017 issue manually. I'd build a query that flags months where any category drops more than 50% vs prior month.
- dbt for transformations. I wrote transformation SQL directly in BigQuery. For production I'd use dbt for proper lineage and testing.
- Zero return rate — returns are not captured in this dataset or recorded separately
- Suppliers are departments — the supplier scorecard is by department, not actual vendor companies
- 3 years of history — more data would improve seasonal detection in the ARIMA model
- Shipments table duplicates — built a deduplication view (
shipments_clean) to handle this
1. Download DataCoSupplyChainDataset.csv from Kaggle
Search: "DataCo SMART SUPPLY CHAIN"
2. Upload to BigQuery
— Create dataset: supply_chain
— Upload CSV as table: orders_raw
— Use Character Map V2 in Advanced options
3. Run setup scripts in order
sql/00_setup/01_create_tables.sql
sql/00_setup/03_dedup_shipments_view.sql
sql/00_setup/04_dashboard_supporting_tables.sql
4. Run analytics queries (any order)
sql/02_analytics/ — all 12 files
5. Run ML scripts in order
sql/03_ml/01_demand_forecast_model.sql (takes 2-5 mins)
sql/03_ml/02_generate_forecast.sql
sql/03_ml/03_model_evaluation.sql
6. Connect Looker Studio
lookerstudio.google.com → New Report → BigQuery
Project: your-project-id → Dataset: supply_chain
supply-chain-analytics/
├── README.md
├── sql/
│ ├── 00_setup/
│ │ ├── 01_create_tables.sql
│ │ ├── 03_dedup_shipments_view.sql
│ │ └── 04_dashboard_supporting_tables.sql
│ ├── 02_analytics/
│ │ ├── 01_late_delivery_analysis.sql
│ │ ├── 02_shipping_mode_performance.sql
│ │ ├── 03_inventory_turnover.sql
│ │ ├── 04_stockout_analysis.sql
│ │ ├── 05_overstock_detection.sql
│ │ ├── 06_supplier_scorecard.sql
│ │ ├── 07_supplier_lead_time_analysis.sql
│ │ ├── 08_demand_trends.sql
│ │ ├── 09_revenue_by_category.sql
│ │ ├── 10_customer_segment_analysis.sql
│ │ ├── 11_cost_optimization.sql
│ │ └── 12_warehouse_efficiency.sql
│ └── 03_ml/
│ ├── 01_demand_forecast_model.sql
│ ├── 02_generate_forecast.sql
│ └── 03_model_evaluation.sql
└── docs/
└── data_dictionary.md