Just In Time: A Comprehensive Analysis of Supply Chain Data

14 min readMay 31, 2024

INTRODUCTION:

Maintaining a competitive advantage in today’s fast-paced business environment relies heavily on supply chain efficiency. Effective supply chain management ensures timely deliveries, optimal inventory levels, and satisfied customers. As a newly employed data analyst at Just In Time, my main responsibility is to address key shipment and inventory management challenges. This case study emphasizes the significance of supply chain efficiency and the influence of data analytics. By identifying supply chain inefficiencies and generating informative dashboards, my goal is to educate business stakeholders and showcase the potential of data in resolving these challenges.

PROJECT BACKGROUND

Company Overview:

Just In Time is a major player in the logistics and supply chain industry, specializing in providing efficient shipment and inventory management solutions. The company operates globally, serving diverse markets and regions.

Challenges Faced:

The company faces challenges with shipment delays, inconsistent inventory management, and balancing supply and demand. There is also a lack of detailed understanding of customer behaviour and segmentation.

Goals of the Analysis:

This analysis uses data analytics to improve supply chain operations for Just In Time which are:

Improve Shipment Performance:

Identify and address the root causes of shipment delays.
Optimize shipment processing times to enhance delivery efficiency.

Optimize Supply-Demand Levels:

Analyze inventory levels to reduce overstock and stockout situations.
Implement strategies for better supply-demand balance.

Enhance Customer Insights:

Segment customers by market, region, and purchasing behaviour.
Analyze orders, revenue, and profitability by customer segment.
Identify trends and patterns in customer behaviour to inform marketing and sales strategies.

The tool used for this analysis was Microsoft Power BI

Data Overview

Data Description:

The data for this analysis was spread across three different tables, each containing crucial information for understanding shipment and inventory management at Just In Time. The key columns from each dataset include:

orders_and_shipments.csv:

Customer ID: Unique customer identification.
Customer Market: Geographic grouping of customer countries (e.g., Europe, LATAM, Pacific Asia).
Customer Region: Geographic grouping of customer countries (e.g., Northern Europe, Western Europe).
Customer Country: Customer’s country.
Order ID: Unique order identification, grouping one or multiple order items.
Order Item ID: Unique order item identification, belonging to just one order.
Order Year/Month/Day: The year, month, and day of the order.
Order Time: Timestamp of the order in UTC.
Order Quantity: The number of items ordered within a given order item.
Product Department: Product grouping into categories such as Fitness, Golf, Pet Shop, etc.
Product Category: Product grouping into categories such as Sporting Goods, Women’s Apparel, etc.
Product Name: The name of the purchased product.
Gross Sales: Revenue before discounts generated by the sales of the ordered item.
Discount %: Discount percentage applied to the catalogue price.
Profit: Profit generated by the sales of the order item.
Shipment Year/Month/Day: The year, month, and day of the shipment.
Shipment Mode: Information on how the shipment has been dispatched (e.g., First Class, Same Day, Second Class).
Shipment Days — Scheduled: Typical number of days needed to dispatch the goods from when the order is placed.
Warehouse Country: The country of the warehouse that has fulfilled the order, either Puerto Rico or the USA.

inventory.csv:

Warehouse Inventory: The monthly product inventory level (e.g., 930 units).
Inventory cost per unit: The monthly storage cost per unit (e.g., $2.07).

fulfillment.csv:

Warehouse Order fulfilment (days): The average number of days to refill stock if inventory drops below zero.

The dataset used for this project can be sourced from this Github repository here

DATA CLEANING AND TRANSFORMATION

After a thorough examination of the available data concerning fulfillment, orders, shipments, and inventory, several data issues were identified and addressed as follows:

Negative Shipment Time: Records of transactions with negative shipment times were removed.
Orders Taking Too Long to Ship: Considering the shipment modes and reorder fulfillment times, orders that took more than 14 days to ship were deemed highly unlikely and thus filtered out.
Correction of Country Names: Some country names contained special characters, which were corrected to ensure accuracy.
Data Integrity Check: No missing values or duplicated rows were found in any of the datasets.
Column Names Standardization: Examination of column names revealed trailing spaces, which were removed to maintain consistency.
Uniformity in Data Format: Non-alphanumeric characters were replaced to ensure uniformity across the datasets.
Date Columns Conversion: Date columns were converted to the DateTime data type to facilitate accurate temporal analysis.

To evaluate shipment punctuality, two new calculated columns were derived from the ‘orders_and_shipments.csv’ dataset:

Shipment Processing Days: This metric quantifies the time taken to process an order, measured in days.
Shipment Delay: Indicates whether an order was delivered on time by comparing the scheduled days for dispatch with the actual days taken.

MEASURES CREATED

Shipments:

Average Shipment Processing Days
On Time Shipments
Delayed Shipments
On Time Delivery Rate
Delayed Delivery Rate

Sales:

Total Revenue
Total Profit
Profit Margin
Number of Orders
Quantity Sold
Number of Customers
Total Discount Given

Products:

Stock Status(In stock, Stockouts, Overstock)

BUSINESS ANALYSIS AND INSIGHTS

Following the analysis, the following insights were uncovered from the data:

Supply Chain Overview:

Revenue by Product Category

The bar chart reveals the revenue distribution across various product categories. High-performing categories such as “Fishing” , “Camping & Hiking”, “Cleats” , “Water Sports” and “Women’s Apparel” generate significant revenue, indicating strong market demand. Conversely, lower-performing categories like “ Garden”, “Crafts” “Golf Gloves” and so on might require strategic interventions to boost sales.

Revenue by Region and Shipment Delay

Regions like “Central America” and “Western Europe” exhibit substantial revenue but also face notable shipment delays.

Shipment Mode Distribution

The predominance of modes like “Standard Class” and “Second Class” indicates a focus on fast delivery, crucial for maintaining high customer satisfaction levels

Profit by Customer Market

Markets such as “Latin America” ,“Europe” and “Pacific Asia” stand out as key profit drivers generating as much profit as $975.6K, $908.5K, $648.5K respectively While was “Africa” was the market generating the least Profit, $209.2K.

Shipment Analysis:

Trends in Shipment Processing Days Over Time

The line charts reveal fluctuations in shipment processing days over time, indicating potential periods of operational inefficiencies. Notably, shipment processing days peaked around Q4 2017, suggesting a need for enhanced logistical planning during peak seasons.

Delayed Shipments by Market

Latin America had the highest number of delayed shipments, totaling approximately 3,300 delayed shipments. Conversely, Africa recorded the least on-time shipments, with only 1,000 on-time shipments. This highlights significant regional disparities in shipment efficiency.

Shipment Mode Performance

Same Day Shipment Mode: This mode had the least average shipment processing days at 1.5 days, the highest on-time delivery rate of 82.9%, and the lowest delayed delivery rate of 17.1%.

First Class Shipment Mode: Contrarily, this mode exhibited the highest delayed delivery rate at 97.4%, indicating significant performance issues that need addressing.

Total Shipments by Shipment Delay

The bar chart shows that total on-time shipments were 14,694, while delayed shipments were 10,898. This indicates a considerable portion of shipments are delayed, suggesting the need for process optimization.

Shipments Delays by Product Category

Top Product Categories with On-Time Shipments: Men’s Footwear (1,886 on-time shipments), Cleats (1,856 on-time shipments), and Women’s Apparel (1,740 on-time shipments) also had the highest delayed shipments.

Categories with Least On-Time Shipments: Men’s Clothing, Music, and Children’s Clothing had the least on-time and delayed shipments, indicating these categories may be less affected by shipment delays or have lower demand.

Sales Analysis: Supply vs Demand

Product Department Ranked by Supply-Demand Ratio

The bar chart reveals that the Golf department has the highest supply-demand ratio of about 21.77%, indicating a strong balance between inventory levels and customer demand. Conversely, the Health and Beauty department has the lowest ratio of 0.09%, suggesting potential overstock issues or lower demand.

Sales Performance by Customer Market

Latin America Leads in net sales with $1.4M and total profit of $1.0M, showcasing strong market performance.

Europe Follows with net sales of $1.3M and total profit of $0.9M.

Pacific Asia Has net sales of $0.9M and a total profit of $0.6M, indicating a significant market but with room for improvement.

Africa Registers the lowest with net sales of $0.3M and total profit of $0.2M, suggesting opportunities for market development.

Stock Status Distribution

The pie chart indicates that 60.14% of the stock is stockout, 20% is in stock.

Sales Performance by Product Department

Fan Shop Department Tops the chart with net sales of $2.3M, total profit of $1.4M, and a profit margin of 61.5%.

Golf Department Records net sales of $0.6M, total profit of $566K, and a profit margin of 88.3%.

Book Shop Department Has the lowest performance with net sales of $1.3K, total profit of $0, and a profit margin of 53.2%.

The line chart indicates an average profit margin across departments at approximately 70.9%, showing a healthy profit level overall.

Total Sales Quantity by Product Category

The column chart shows that the Cleats category has the highest sales quantity at 9.8K units, followed by Women’s Apparel at 9.2K units, and Indoor/Outdoor Games at 7.7K units. Music and Children’s Clothing categories have the lowest sales quantities at 6and 5 units respectively.

Product Performance Insights

Sales, Profit, and Discount by Product

Field & Stream Sportman 16 Gunfire Safe: Gross sales of $1.07M, total profit of $0.54M, and a discount given of $110K. This indicates a high-performing product with strong sales and profit margins despite the discounts.

Perfect Fitness Perfect Rip Deck: Gross sales of $590K, total profit of $479K, and a discount given of $59K. Also a high performer but with slightly lower sales and profits than Field & Stream Sportman 16 Gunfire Safe.

Lawn Mower: Gross sales of $27K, total profit of $13K, and a discount given of $2.4K. Indicates lower performance and profit margins, potentially needing a strategic review.

Product Analysis by Category and Department

Fishing (Fan Shop): Gross sales of $1.1M, total profit of $536K, total discount given of $110K, and average shipment processing time of 3.2 days. High sales and profit with manageable shipment times.

Camping & Hiking (Fan Shop): Gross sales of $610K, total profit of $305K, total discount given of $62K, and average shipment processing time of 3.2days. Good performance but similiar shipment processing times.

Children’s Clothing (Apparel): Gross sales of $1.8K, total profit of $845, total discount given of $257, and average shipment processing time of 5 days. Lower performance with longer shipment times, indicating areas for improvement.

Top and Underperforming Products

Top-Performing Products:

Field & Stream Sportman 16 Gunfire Safe: Total profit of $540K and gross sales of $1.07M.
Perfect Fitness Perfect Rip Deck: Total profit of $480K and gross sales of $590K.

Underperforming Products:

adidas Youth Germany Black/Red Away Match Soc: Total profit of $6.5K and gross sales of $11.3K.
LIJA Women’s Eyelet Sleeveless Golf Polo: Total profit of $6K and gross sales of $9.7K.

The chart clearly distinguishes the top performers, such as Field & Stream Sportman 16 Gunfire Safe, from underperformers like LIJA Women’s Eyelet Sleeveless Golf Polo, helping identify where to focus improvement efforts.

Shipment Processing Time by Product

adidas Brazuca 2017 Official Match Ball: Average shipment processing time of 1.7 days, indicating highly efficient processing.
Sports Books: Average shipment processing time of 3 days, showing good efficiency.
Children’s heaters: Average shipment processing time of 5 days, highlighting a need for process optimization to reduce delays.

Customer Insights

Trends in Customer Orders, Revenue, and Profit

Over the analysis period, total profit peaked at $1.16M in 2016, showing a steady increase from $1.12M in 2015 and a slight decline to $0.94M in 2017.
Net sales followed a similar trend, reaching $1.63M in 2016, up from $1.57M in 2015, but dropping to $1.36M in 2017.
The number of orders saw significant growth, with 9,235 orders in 2016, increasing from 8,907 in 2015, and declining to 7,450 in 2017. This indicates that while 2016 was a peak year, there was a noticeable drop in customer engagement in 2017.
Customer Purchasing Behavior Over Time

In 2016, customer purchasing behavior peaked with an order quantity of 19,786, making it the highest year for orders.
2015 recorded the second-highest order quantity with 19,044 orders, showing a solid growth trend leading into 2016.
2017 had the least order quantity with 14,773 orders, indicating a decline in purchasing activity compared to the previous two years. This decline warrants an investigation into potential causes, such as market saturation or increased competition.
Customer Segmentation Analysis by Country

USA: The USA had the highest number of customers, totaling 1,321 and the highest number of orders at 1,406. This indicates a strong customer base and high engagement in this region.
Mexico: Mexico followed with 754 customers and 768 orders, showing significant market penetration and customer loyalty.
UK: The UK had 446 customers with 450 orders, indicating a solid customer base but lower engagement compared to the USA and Mexico.
Countries like Iran and Netherlands had lower numbers below 100, with 90 and 89 customers, and 90 and 89 orders respectively. This suggests potential growth opportunities in these markets.

KEY TAKEAWAYS:

High-performing product categories include “Fishing,” “Camping & Hiking,” “Cleats,” “Water Sports,” and “Women’s Apparel,” indicating strong market demand.
Lower-performing categories like “Garden,” “Crafts,” and “Golf Gloves” may require strategic interventions to boost sales.
Regions like “Central America” and “Western Europe” generate substantial revenue but face notable shipment delays.
Preferred shipment modes are “Standard Class” and “Second Class,” which are crucial for maintaining high customer satisfaction.
Key profit-driving markets include “Latin America” ($975.6K), “Europe” ($908.5K), and “Pacific Asia” ($648.5K), while “Africa” generates the least profit ($209.2K).
Shipment processing days peaked around Q4 2017, indicating a need for enhanced logistical planning during peak seasons.
Latin America had the highest number of delayed shipments (~3,300), while Africa had the least on-time shipments (~1,000), highlighting regional disparities in shipment efficiency.
“Same Day Shipment Mode” had the least average shipment processing days (1.5 days), highest on-time delivery rate (82.9%), and lowest delayed delivery rate (17.1%).
“First Class Shipment Mode” exhibited the highest delayed delivery rate (97.4%), indicating significant performance issues.
Total on-time shipments were 14,694, while delayed shipments were 10,898, suggesting the need for process optimization.
Top product categories with on-time shipments include Men’s Footwear (1,886), Cleats (1,856), and Women’s Apparel (1,740).
Men’s Clothing, Music, and Children’s Clothing had the least on-time and delayed shipments, indicating lower demand or less impact from shipment delays.
The “Golf” department has the highest supply-demand ratio (21.77%), while “Health and Beauty” has the lowest (0.09%), indicating potential overstock or low demand issues.
“Latin America” leads in net sales ($1.4M) and total profit ($1.0M), followed by “Europe” ($1.3M net sales, $0.9M profit) and “Pacific Asia” ($0.9M net sales, $0.6M profit).
“Africa” has the lowest net sales ($0.3M) and total profit ($0.2M), suggesting opportunities for market development.
60.14% of the stock is stockout, with only 20% in stock, indicating a potential inventory management issue.
The “Fan Shop” department tops sales performance with $2.3M net sales, $1.4M profit, and a 61.5% profit margin.
The “Golf” department has a high profit margin (88.3%) despite lower sales ($0.6M net sales, $566K profit).
The “Book Shop” department has the lowest performance ($1.3K net sales, $0 profit).
The Cleats category has the highest sales quantity (9.8K units), followed by Women’s Apparel (9.2K units), and Indoor/Outdoor Games (7.7K units).
High-performing products include “Field & Stream Sportman 16 Gunfire Safe” ($1.07M gross sales, $0.54M profit) and “Perfect Fitness Perfect Rip Deck” ($590K gross sales, $479K profit).
Underperforming products include “adidas Youth Germany Black/Red Away Match Soc” ($11.3K gross sales, $6.5K profit) and “LIJA Women’s Eyelet Sleeveless Golf Polo” ($9.7K gross sales, $6K profit).
“Adidas Brazuca 2017 Official Match Ball” has the most efficient shipment processing time (1.7 days).
Total profit peaked at $1.16M in 2016, showing a steady increase from $1.12M in 2015 and a slight decline to $0.94M in 2017.
Net sales followed a similar trend, reaching $1.63M in 2016, up from $1.57M in 2015, but dropping to $1.36M in 2017.
Customer orders peaked in 2016 with 9,235 orders, increasing from 8,907 in 2015, and declining to 7,450 in 2017.
The USA had the highest number of customers (1,321) and orders (1,406), followed by Mexico (754 customers, 768 orders) and the UK (446 customers, 450 orders).
Markets like Iran and Netherlands have potential for growth with customer numbers below 100 and equal order quantities.

RECOMMENDATION:

Boost Low-Performing Categories: Implement targeted marketing strategies and promotions to increase sales in low-performing categories such as “Garden,” “Crafts,” and “Golf Gloves.”
Improve Shipment Efficiency: Focus on reducing shipment delays, particularly in regions like Latin America and Africa, by optimizing logistical operations and enhancing shipment planning.
Address Shipment Mode Issues: Improve the performance of the First Class shipment mode, which has a high delayed delivery rate, and promote the efficient Same Day shipment mode.
Optimize Inventory Management: Address the high stockout rate (60.14%) by improving inventory forecasting and management to balance supply and demand more effectively.
Enhance Product Offerings: Review and improve underperforming products like “adidas Youth Germany Black/Red Away Match Soc” and “LIJA Women’s Eyelet Sleeveless Golf Polo” through product innovation or better marketing.
Leverage Top-Performing Products: Capitalize on high-performing products like the “Field & Stream Sportman 16 Gunfire Safe” and “Perfect Fitness Perfect Rip Deck” by ensuring sufficient stock and continued promotion.
Expand Market Penetration: Explore growth opportunities in underperforming regions such as Africa and low-customer countries like Iran and the Netherlands through targeted marketing and sales efforts.
Optimize Shipment Processing: Aim to reduce the average shipment processing time for products with longer processing times, such as “Children’s heaters,” to improve customer satisfaction.

CONCLUSION:

I truly enjoyed working on this project because it simulated real-life business scenarios and greatly improved my Power BI skills through data visualization and report creation. The insights gained from analyzing Just In Time’s supply chain, sales, and customer data provided valuable recommendations for optimizing operations and boosting performance.

Take a peek at the dashboard below:

Just In Time Supply Chain Analytics Dashboard

Click Here for Full Interaction with the Dashboard

Stay connected with me through Twitter and LinkedIn.

Be sure to follow me here and explore my Medium and GitHub profiles for additional intriguing projects.

Thank you for reading and God bless!!!

Just In Time: A Comprehensive Analysis of Supply Chain Data

Written by Favour Igwezeke