DATACO SUPPLY CHAIN INTELLIGENCE SYSTEM
- Cosmas Ashibeshi
- Jun 24
- 10 min read
INTRODUCTION
Supply chains run on timing. When delivery promises break down at scale, the damage shows up everywhere at once: customer trust erodes, profit margins absorb hidden costs, and operations teams are left reacting to problems they can't see coming. This challenge sits at the center of global e-commerce and logistics, where a single late shipment is a minor inconvenience, but a systemic pattern of late shipments is a business crisis.
This Power BI analysis examines DataCo Global, a multinational e-commerce and retail distribution company operating across five international markets: Africa, Europe, LATAM, Pacific Asia, and USCA. The investigation uncovers a severe and persistent delivery performance problem, with 57.42% of all fulfilled orders arriving late, and traces that problem through to its financial consequences, including $9.2 million in revenue tied to late orders and a total loss amount of $3.88 million across the analysis period.
The dashboard transforms 65,750 supply chain orders into a structured intelligence system that helps operations leadership understand not just that deliveries are failing, but where, why, and what it is costing the business.
PROJECT OVERVIEW
The primary objective of this project was to design and build a full enterprise-grade supply chain analytics system, structured around the SCOR (Supply Chain Operations Reference) framework, to diagnose the root causes of DataCo's delivery performance crisis and quantify its impact on profitability.
The analysis addresses the need for operations leadership to move from anecdotal awareness of delivery problems to a data-driven understanding of exactly where the supply chain is breaking down and what interventions would have the greatest impact.
Problem Statement
This project tackles a central operational crisis: more than half of DataCo's orders are delivered late, and the business has no structured way to understand whether this is a regional issue, a shipping mode issue, a seasonal issue, or something deeper.
Key questions driving this analysis include:
What percentage of orders are delivered late, on time, or early across DataCo's global operations?
Is the delivery problem concentrated in specific markets, regions, shipping modes, or product categories, or is it systemic?
How large is the gap between scheduled and actual shipping times, and which shipping modes are most responsible?
What demand patterns exist across markets and product categories, and how do they relate to delivery performance?
How much revenue and profit is at risk because of late deliveries, and where is the business losing money outright?
Which markets and regions require the most urgent operational intervention?
Answering these questions required not just visual dashboards but a properly modeled data architecture, calculated business metrics, and a governance layer that reflects how a real enterprise BI system would be deployed.
DATASET OVERVIEW
This analysis is built on the DataCo Smart Supply Chain dataset, a publicly available order-level dataset covering DataCo's global operations from 2015 to 2018.
Primary Data Source
DataCo Smart Supply Chain Dataset (Kaggle): A single flat CSV file containing 180,519 rows and 53 columns of order, shipping, customer, product, and financial data. After data cleaning and deduplication to the unique order level, the dataset reduced to 65,750 distinct orders spanning five global markets.
Key Data Dimensions Analyzed
Delivery Performance Metrics: Scheduled versus actual shipping days, delivery status, late delivery risk flags, and shipping mode classifications
Demand and Order Behavior: Order volume trends by month and year, product category contribution, customer segment distribution, and product order frequency.
Financial Metrics: Sales, profit per order, discount rates, item profit ratios, and benefit per order
Geographic Distribution: Market, region, country, state, and city level order distribution across all five global markets
Governance Structure: Customer segment, order region, and market dimensions used to support row-level security design
TOOLS AND TECHNOLOGIES
Microsoft Power BI served as the primary platform for building the data model, DAX calculation layer, and interactive dashboard. Microsoft Excel was used as a critical supporting tool during data preparation.
Power BI and Excel Features Utilized
Power Query for Data Transformation: Used extensively across both Excel and Power BI to clean, rename, and restructure 53 raw columns into a proper star schema. This included building five derived fields, including a Delay Days calculation and a five-category Delivery Timing Status classification that distinguishes between late, on time, early, cancelled, and pending orders.
Star Schema Data Modeling: Designed a fact table (FactOrders) connected to seven dimension tables, including DimCustomer, DimProduct, DimCategory, DimRegion, DimGeography, DimShippingMode, and DimDate, with relationships built directly in Power BI's model view rather than through Power Query merges.
DAX Calculations and Measures: Built 27 measures across four categories to support the analysis, including:
Late Delivery Rate %, On Time Delivery Rate %, and Early Delivery Rate %, calculated at the unique order level
Shipping Efficiency Index, comparing scheduled shipping days against actual shipping days
Revenue at Risk, isolating the sales value tied specifically to late orders
Total Loss Amount, identifying orders with negative profit
Product Velocity Score, measuring average unique orders per product
Row-Level Security: Implemented 12 governance roles, including 1 Executive role with full access, 5 Regional Manager roles scoped to individual regions, and 6 Country Manager roles scoped to individual countries, simulating how access would be controlled in a real enterprise deployment.
Interactive Visualizations and Navigation: Developed seven dashboard pages connected through custom navigation, a collapsible slicer panel controlled by bookmarks, and a dynamic drillthrough page that adjusts its title based on whichever market, region, segment, category, or product the user investigates.
METHODOLOGY
The analysis followed a structured pre-build phase, a deliberate data architecture decision process, and an iterative correction cycle once initial DAX measures revealed inconsistencies that needed to be resolved before the findings could be trusted.
Data Integration and Transformation Challenges
Resolving a Critical Data Loading Failure: Early in the project, Power BI Desktop repeatedly failed to load the dataset, with row counts multiplying from 65,750 to over 3 million during table loading. After extensive troubleshooting, including buffering attempts, disabling background refresh, and relocating the file off OneDrive, I identified the actual cause: merge operations in Power Query were creating a many-to-many join because duplicate values existed in dimension tables before deduplication was applied. The fix was to remove the merges entirely and instead build all relationships directly in Power BI's model view, which is the correct approach for a star schema and ultimately resolved the loading failure completely.
Rebuilding the Data Pipeline in Excel: To recover from the loading failure, I rebuilt the entire Power Query transformation pipeline in Excel, validated the M code there, and then imported the corrected queries into Power BI. This decision, while unconventional, proved to be the most reliable path forward.
Correcting the Delivery Timing Classification Logic: My initial Delivery Timing Status column classified orders as late, early, or on time using only the Delay Days field, which incorrectly applied those labels to cancelled and unprocessed orders. I rebuilt the logic to first check Order Status for cancelled, suspected fraud, and pending states before applying the timing classification, which changed the late delivery rate from an initial 54.82% to a more accurate 57.42% once the denominator correctly reflected fulfilled orders only.
Resolving Measure-Level Counting Inconsistencies: Several early measures, including Total Orders and Product Velocity Score, mixed row-level counting (COUNTROWS) with order-level counting (DISTINCTCOUNT), which inflated key metrics. Standardizing every measure to count at the unique order level was necessary before any finding in this report could be considered reliable.
PRE-ANALYSIS
Before opening Power BI, I completed a formal project scoping phase to ensure the analysis would be structured, traceable, and aligned to a real business framework rather than an ad hoc collection of charts.
Project Structure and Business Framework
I structured the entire project around the SCOR (Supply Chain Operations Reference) model, mapping delivery performance analysis to the Deliver process, demand and order behavior to Plan, and profitability and cost impact to Enable. This gave the project a recognized industry framework rather than an arbitrary page structure.
Requirements Traceability and Process Documentation
I built a Requirements Traceability Matrix connecting each business question (Why) to a specific analytical requirement (What) and its Power BI implementation (How), along with a context diagram mapping the system's external actors, a data flow diagram tracing how data moves from the source file through the star schema into the dashboard, and an As-Is process map of DataCo's order fulfillment process that identified two specific operational bottlenecks before any dashboard was built.
Dashboard Design Strategy
I structured the analysis into six analytical pages, beginning with an Executive Overview and progressing through Delivery Overview, Root Cause Analysis, Demand and Product Analysis, and Profitability and Cost Impact, supported by a hidden drillthrough page for order-level investigation. This sequencing was intentional: it mirrors how a supply chain manager would naturally move from noticing a problem to understanding its cause to quantifying its cost.
Stakeholder Consideration
I designed the row-level security structure around three distinct stakeholder tiers, an Executive role requiring full visibility, Regional Managers requiring scoped access to their assigned region, and Country Managers requiring the most granular access, reflecting how access governance would realistically be structured inside a multinational organization.
KEY OBSERVATIONS
Delivery Performance Is Systemic, Not Isolated
57.42% of all fulfilled orders were delivered late, and this rate holds remarkably steady across every dimension examined. All five global markets cluster between 56% and 59% late delivery regardless of order volume, and late delivery rate variance across product categories spans only 61.59% to 62.58%. This narrow variance is one of the most important findings in the analysis: it confirms the delivery failure is not caused by any specific product, region, or market, but by a structural weakness in the scheduling and logistics process itself.
The Scheduling System Is Fundamentally Miscalibrated
Average actual shipping days (3.50) consistently exceed scheduled shipping days (2.93) by 0.57 days, producing a Shipping Efficiency Index of 83.82%, meaning the supply chain operates 16% below its own planned targets. Compounding this, only 18.58% of orders arrive exactly on time, while 24% arrive early, indicating that scheduled delivery windows are poorly calibrated in both directions rather than simply being too aggressive.
First Class Shipping Is the Worst Performing Mode
Despite being positioned as a premium shipping tier, First Class recorded a 100% late delivery rate across every fulfilled order in the dataset. Second Class shipping showed the highest average delay at 2.50 days, while Same Day shipping consistently outperformed all other modes.
Revenue Exposure Is Concentrated, Not Proportional
While 57.42% of orders are late, only approximately 25% of total revenue ($9.2 million of $36.78 million) is tied to those late orders, indicating that lower-value orders are disproportionately affected by delivery failures while higher-value orders are more reliably fulfilled.
Profitability Is Under Significant Pressure
DataCo's total profit margin sits at just 10.78%, and a total loss amount of $3.88 million was recorded across the analysis period, meaning losses are nearly equal in size to total profit. LATAM emerged as the highest-risk market, combining the highest revenue at risk ($3.4 million) with a below-average profit margin.
Demand Follows a Predictable Seasonal Pattern
January consistently recorded the highest order volume at 7.4K orders, roughly 50% higher than February's 4.8K, pointing to a predictable post-holiday demand spike that the business could plan capacity around in advance.
STRATEGIC RECOMMENDATIONS
Immediate Delivery Performance Priorities
Restructure or Discontinue First Class Shipping in Its Current Form: A 100% late delivery rate on a premium tier is commercially indefensible. DataCo should renegotiate carrier contracts, recalibrate scheduled delivery windows for this mode specifically, or suspend it as a customer-facing option until reliability can be restored.
Recalibrate Scheduled Delivery Windows Across All Modes: The 0.57-day average gap between scheduled and actual shipping is present across every mode and every market without exception. Extending scheduled windows by 0.6 to 1.0 days would immediately reduce the late delivery rate without requiring any change to actual logistics performance.
Prioritize Intervention in the Five Worst-Performing Regions: South of USA, Central Asia, South Asia, West Asia, and Eastern Europe all exceed a 59% late delivery rate and should be the first targets for carrier audits and SLA renegotiation.
Demand and Operational Planning
Pre-Position Logistics Capacity Ahead of the January Demand Spike: Since the seasonal pattern is consistent and predictable, capacity, staffing, and inventory buffers should be increased in October and November each year to absorb the spike without degrading delivery performance.
Differentiate Fulfillment Strategy by Product Value Profile: Fishing generates the highest revenue with the lowest order frequency, while Cleats drives the highest order volume with proportionally lower revenue. These two product profiles warrant different fulfillment priorities, with high-value, low-frequency products receiving expedited handling.
Profitability and Risk Management
Conduct an Urgent Review of Loss-Making Orders: With total losses ($3.88 million) nearly equal to total profit ($3.97 million), identifying and correcting the specific orders, products, or regions driving these losses should be treated as a priority business action, not a future initiative.
Treat LATAM as a Priority Restructuring Market: The combination of the highest revenue at risk and a below-average profit margin makes LATAM the single highest-priority market for an operational and pricing review.
Reassess Discounting Strategy Given Thin Margins: With an average discount rate of approximately 10.2% sitting almost exactly at the level of the company's 10.78% profit margin, there is effectively no room for discounting that does not directly erode profitability.
System and Governance Recommendations
Deploy the Dashboard as a Permanent Operational Tool: This system was built to support ongoing monthly performance reviews, not as a one-time analysis, and should be connected to a live data source for continued use.
Extend Row-Level Security to Dynamic Role Assignment: The 12 roles built for this project use static filters for demonstration purposes. A production deployment should extend this to dynamic assignment using user login credentials mapped to a region and country table.
CONCLUSION
This Power BI analysis of DataCo Global's supply chain reveals a delivery performance crisis that is structural rather than incidental. Across five global markets and sixty-five thousand orders, the data tells a consistent story: 57.42% of fulfilled orders arrive late, the scheduling system underestimates delivery time by more than half a day on every order, and a supposedly premium shipping tier fails to deliver on time without exception.
What makes this finding significant is its consistency. A problem that varied by region or product category would point toward a localized fix. A problem that holds steady across every market, every category, and nearly every shipping mode points toward something deeper: a scheduling and logistics infrastructure that was never calibrated to the operational reality of the business it serves.
The financial consequences of this gap are equally clear. A 10.78% profit margin leaves almost no room to absorb the cost of late deliveries, and a total loss amount nearly equal to total profit suggests that a meaningful share of DataCo's order volume is being fulfilled at a net cost to the business rather than a benefit.
Building this system required more than charts. It required resolving a serious data loading failure, correcting flawed measure logic discovered only after the numbers stopped making sense, and designing a governance structure that reflects how access control actually works inside a multinational organization. The result is a dashboard that does not just describe DataCo's delivery problem, but traces it to its root causes and quantifies exactly what it is costing the business, giving operations leadership a foundation for the specific, prioritized interventions outlined above.
Comments