Supply Chain Shipment Pricing Analytics

End-to-end SQL + Power BI portfolio project analyzing real shipment-level supply-chain data.

Project Summary

This project uses a real supply-chain shipment pricing dataset with 10,324 shipment records and 33 original variables. The analysis focuses on freight cost, shipment modes, vendor activity, manufacturing sites, product groups, country-level demand, and delivery reliability.

The dataset is commonly published as Supply Chain Shipment Pricing Data and is based on health commodity shipment and pricing records.

Why This Project Matters

Supply-chain teams need to understand where freight costs are concentrated, which transportation modes drive cost, which vendors and manufacturing sites dominate operations, and where delivery reliability problems occur. This project converts raw shipment records into SQL-based KPIs and Power BI dashboard logic.

Tools Used

• SQLite
• SQL
• Python / Pandas for cleaning and database creation
• Power BI
• GitHub

Dataset

File used:

SCMS_Delivery_History_Dataset.csv

Dataset size:

Rows: 10,324
Original columns: 33
Cleaned analytical columns: 41

Repository Structure

supply-chain-shipment-pricing-analytics/
│
├── data/
│   ├── raw/
│   │   └── SCMS_Delivery_History_Dataset.csv
│   └── processed/
│       └── shipments_clean.csv
│
├── database/
│   └── supply_chain_shipments.db
│
├── sql/
│   ├── 01_schema.sql
│   ├── 02_data_quality_checks.sql
│   ├── 03_kpi_queries.sql
│   └── 04_dashboard_views.sql
│
├── powerbi/
│   └── dashboard_blueprint.md
│
├── scripts/
│   └── build_database.py
│
├── screenshots/
│
└── docs/

Key Metrics From the Dataset

• Total shipments: 10,324
• Total freight cost: $68,817,849.41
• Total line item value: $1,627,584,457.29
• Total shipment weight: 21,820,540.00 kg
• Late shipment rate: 11.49%

Initial Findings

Highest Freight Cost Countries

country	shipments	total_freight
Nigeria	1194	1.42686e+07
Zambia	683	6.31044e+06
Côte d'Ivoire	1083	6.09217e+06
Rwanda	430	5.89349e+06
Uganda	779	5.48429e+06

Shipment Mode Freight Profile

shipment_mode	shipments	total_freight	avg_freight
Air	6113	4.30386e+07	10459
Truck	2830	1.18657e+07	10176.4
Air Charter	650	8.92611e+06	21052.1
Ocean	371	3.59073e+06	12733.1
nan	360	1.3967e+06	6619.43

Highest Value Vendors

vendor	shipments	total_value
SCMS from RDC	5404	1.08537e+09
Orgenics, Ltd	754	1.47703e+08
Aurobindo Pharma Limited	668	9.13837e+07
MYLAN LABORATORIES LTD (FORMERLY MATRIX LABORATORIES)	317	7.22137e+07
HETERO LABS LIMITED	277	4.28816e+07

Business Questions

1. Which countries generate the highest freight cost exposure?
2. Which shipment modes are most expensive?
3. Which vendors and manufacturing sites dominate the supply chain?
4. Which countries or shipment modes show delivery reliability risk?
5. Which shipments are freight-cost outliers?
6. How does freight cost behave relative to weight?

SQL Analysis Included

The SQL query library includes:

• Executive summary KPIs
• Freight cost by country
• Shipment mode economics
• Vendor volume and value
• Manufacturing site network analysis
• Product group financial profile
• Delivery punctuality analysis
• Freight cost per kilogram
• High-cost shipment outlier detection
• Annual shipment trends
• Incoterm freight profile
• Fulfillment route analysis

Power BI Dashboard Pages

1. Executive Overview
2. Freight Efficiency
3. Vendor & Manufacturing Network
4. Delivery Reliability

Portfolio Positioning

This project demonstrates practical analyst skills in:

• SQL querying
• KPI development
• Data cleaning
• Supply-chain analytics
• Freight cost analysis
• Operational dashboard design
• Power BI reporting
• Business insight generation

Next Steps

• Build the Power BI report using shipments_clean.csv
• Add dashboard screenshots to screenshots/
• Add screenshots to this README
• Publish the repository on GitHub
• Link the repository on the CV