DataCo Supply Chain Analytics

Introduction

In today’s competitive business landscape, supply chain analytics plays a pivotal role in driving efficiency, optimizing operations, and enhancing customer satisfaction. By leveraging data-driven insights, companies can make informed decisions that lead to improved performance and growth. In this article, we’ll explore how I analyzed a comprehensive supply chain dataset to uncover key trends, identify areas for improvement, and provide actionable recommendations.

My goal was to transform raw data into meaningful insights that could guide strategic decision-making at DataCo Logistics, a leading supply chain management company. Through careful data transformation, visualization, and analysis, I aimed to answer critical questions such as:

• How is revenue performing compared to previous years?
• What are the most profitable product categories?
• How efficient is our delivery process?
• Which customer segments contribute the most to revenue?

By the end of this article, you’ll have a clear understanding of how data analytics can drive tangible improvements in supply chain operations.

About the Dataset

The dataset provided for this project was initially structured as a single flat table containing 54 columns and 102,580 records. While this format made it easy to load into Power BI, it lacked the structure needed for efficient analysis. To address this, I transformed the dataset by splitting it into four distinct tables: Customers, Products, Orders, and Order Locations.

By restructuring the dataset into these four tables, I was able to create meaningful relationships between them, enabling cross-table analysis and deeper insights.

Data Transformation

To make the dataset more usable for analysis, I performed several transformations. Here’s how I approached the process:

a. Splitting the Single Table: Creating Structure

To improve the dataset’s usability, I reorganized it into four logical tables:

• Orders Table:
  I extracted all fields related to orders, such as Order Id, Order Date Dateorders, Shippint Date Dateorders, Sales, and Order Item Quantity. This table became my Facts Table, my central hub.
• Customers Table:
  I isolated customer-related fields like Order Customer Id, Repeat Customer, Segment, and State.
• Products Table:
  I grouped product-related fields such as Product Name, Product Category Id, Product Price, and Product Status.
• Order Locations Table:
  I created a separate table for geographic data, including City, State, and Country. Even added a Continent column.

b. Cleaning the Data: Addressing Gaps and Inconsistencies

Once the dataset was split, I focused on cleaning each table. Here’s what I did:

• Removing Unnecessary Columns:
  I reviewed all 45 columns in the original dataset and removed fields that were irrelevant to the analysis. For example, internal IDs and redundant metadata were excluded to reduce clutter and improve performance.
• Translating Geographic Names:
  The dataset originally contained city, state, and country names in Spanish. To ensure consistency and usability, I translated these names into English.
• Normalizing Column Data Types:
  I ensured that each column had the correct data type. For example: Dates (Order Date Dateorders, Shippint Date Dateorders) were converted to the Date type. Numeric fields like Sales and Order Item Profit were formatted as Decimal Numbers. Text fields like Product Name and City were set to Text.
• Fixing Text Cases to Proper Case:
  Many text fields (e.g., City, State, Product Name) had inconsistent capitalization. To standardize the data, I converted all text fields to proper case.

c. Building Relationships: Connecting the Tables

With the data cleaned and split into four tables, I established relationships between them to enable cross-table analysis:

• Orders and Customers:
  I linked the Orders table to the Customers table using the Order Customer Id field.
• Orders and Products:
  I connected the Orders table to the Products table via the Order Item Cardprod Id field.
• Orders and Order Locations:
  I linked the Orders table to the Order Locations table using a unique identifier (Order Location Id). (which I created from merging the City, Stateand Country into one column).

d. Creating Calculated Columns and Measures: Deriving New Insights

To enhance the dataset, I created the following calculated columns:

• Orders s Table:
  I added a calculated column called Fulfilment Time to measure how long it took to fulfill each order:

• Customers Table:
  I added a calculated column called Repeat Customer to identify whether a customer had placed more than one order:

e. Creating a Date Table: Enabling Time-Based Analysis

To facilitate time-based analysis (e.g., monthly trends, year-over-year comparisons), I created a dedicated Date Table using DAX.

e. Validating the Data: Ensuring Accuracy

Finally, I validated the transformed data to ensure everything was accurate and consistent:

• I cross-checked calculated columns like Fulfilment Time and Repeat Customer against raw data to confirm their correctness.
• I tested relationships between tables (e.g., Orders → Customers, Orders → Date) to ensure they worked seamlessly in Power BI.

Key Performance Indicators

Revenue: $21.47M

• What It Means: Total revenue generated over the analysis period.
• Why It Matters: Indicates financial health and growth potential.

Total Orders: 102,580

• What It Means: Total number of orders processed.
• Why It Matters: Reflects consistent demand and operational efficiency.

Sales Growth Rate: 0.96%

• What It Means: Year-over-year revenue growth is minimal.
• Why It Matters: Highlights stagnation in sales growth.

Average Fulfillment Time: 3.6 Days

• What It Means: Average time taken to fulfill an order.
• Why It Matters: Impacts customer satisfaction and delivery efficiency.

Repeat Customer Rate: 76.2%

• What It Means: Percentage of customers who made repeat purchases.
• Why It Matters: Reflects strong customer loyalty and retention.

Profit Margin: 11.9%

• What It Means: Percentage of revenue that has translated into profit.
• Why It Matters: Indicates healthy profitability.

2017 Insights

1. Key Insights from KPI Cards

The top section provides high-level metrics for 2017, comparing them to the previous year (2016). Here’s what we can infer:

a. Revenue: $6.72M (-15.4% vs. Last Year)

• Insight: Revenue decreased by 15.4% compared to 2016.

b. Total Orders: 29,209 (+33.4% vs. Last Year)

• The number of orders increased significantly (33.4%) despite the revenue decline.

c. Sales Growth Rate: -5.83%

• Sales growth was negative, indicating a decline in year-over-year revenue.

d. Average Fulfillment Time: 3.6 Days (+1.4% vs. Last Year)

• Fulfillment time increased slightly compared to 2016.

e. Repeat Customer Rate: 70.1% (-28.1% vs. Last Year)

• There was a significant drop in repeat customers, indicating potential issues with customer retention.

f. Profit Margin: 12.3% (+4.3% vs. Last Year)

• Profit margins improved compared to 2016, despite the revenue decline.

2. Monthly Revenue Trends

• Revenue in 2017 declined by -15.4% compared to 2016, with most months underperforming relative to the previous year. From January to August, revenue remained relatively stable but consistently below 2016 levels. September was an exception, peaking at $671K, which slightly exceeded its 2016 counterpart.
• However, from October to December, revenue dropped sharply, culminating in December’s lowest figure of $205K.

3. Average Fulfillment Time by Shipping Mode

• Same-day shipping is the fastest (average of 12 hours, or 0.5 day), while Standard Class takes the longest (4.02 days). Interestingly, there is no significant difference between Standard Class and Second Class (4.02 days), suggesting inefficiencies or overlaps in how these modes are managed.

4. Repeat Customers

• Repeat customers account for 70% of total customers, but this rate has declined by 28.1% compared to last year (which was 98%).

5. Geographic Distribution of Revenue

• California stands out as the highest-revenue state, followed by other states like Texas and New York.

6. Most Profitable Product Categories

• 4 out of the top 5 profitable product categories are sports-related , including Tennis & Racquet (35.11%) and Baseball & Softball (20.18%). This highlights the importance of sports items as a key driver of profitability.

7. Revenue by Segment

• The Consumer segment contributes the most revenue (55.5%), followed by Home Office (23.0%) and Corporate (21.6%).

Summary of Insights

The 2017 data shows a -15.4% revenue decline, with a sharp drop from September to December. Delivery times reveal inefficiencies, as Second Class and Standard Class are nearly identical. Repeat customer rates fell by 28.1%, while sports products dominate profitability, with 4 out of the top 5 categories being sports-related. Profit margins improved by 4.3%, showcasing effective cost management.

Recommendations

Based on the 2017 analysis, here are actionable recommendations to drive growth and improve performance:

Revenue Growth

Focus on boosting sales during underperforming months, especially Q4 (October to December), by launching holiday-specific promotions or discounts. Encourage higher-value purchases through product bundling or upselling to increase average order value.

Delivery Efficiency

Streamline Second Class and Standard Class shipping by identifying inefficiencies and differentiating these modes more clearly. Promote faster shipping options like Same-day and First Class to attract customers who prioritize speed while optimizing slower modes for cost-efficiency.

Customer Retention

Strengthen loyalty programs with personalized offers or post-purchase follow-ups to re-engage repeat customers. Analyze feedback to address potential pain points, such as delivery delays or product availability, which may be contributing to the decline in repeat customer rates.

Product Strategy

Prioritize sports-related products, which dominate profitability, by focusing on inventory management and targeted marketing campaigns. Explore opportunities to expand high-margin categories like Tennis & Racquet and Baseball & Softball into untapped markets or customer segments.

Cost Optimization

Maintain strategies that improved profit margins by 4.3% and explore additional cost-saving measures without compromising customer satisfaction. Regularly monitor costs to ensure sustained profitability.

Explore the Insights Further: Power BI Dashboard

To dive deeper into the DataCo Supply Chain analysis, you can interact with the Power BI dashboard I created for this project.

Dashboard Images

---

Open the live dashboard

Originally published on Medium.