Introduction
Welcome to the first part of our comprehensive series on creating a powerful Supply Chain Management App!
In this article, we will dive into the exciting world of data analysis and dashboard creation, setting the stage for an app that will enhance the supply chain optimization.
As a budding developer, you’re about to embark on a transformative journey that will not only enhance your skills but also contribute significantly to the world of logistics and operations.
So, let’s roll up our sleeves and get started!
Setting the Foundation for Your Supply Chain App
Our ambitious project aims to create a supply chain management desktop app that will streamline logistics processes, and boost customer satisfaction. The journey ahead encompasses multiple crucial phases: Exploratory Data Analysis (EDA), database development, app creation, and more…
In this inaugural part of our series, we’ll delve into the EDA phase, where you’ll harness the power of real-world data to extract invaluable insights. These insights will lay the groundwork for an intuitive and effective dashboard, which will be the cornerstone of your app’s success.
Understanding the Data: Navigating Your Dataset
Before we dive into the fascinating realm of data analysis, let’s take a closer look at the dataset that will fuel your app’s functionality. The dataset you’ll be working with is a goldmine of information, containing details about customers, product orders, delivery dates, and much more. This data will serve as the raw material from which you’ll craft a seamless user experience within your app.
Challenge #2 : Generate Insights to Solve a Supply Chain Issue in the FMCG domain.
Download Problem Files:
https://codebasics.io/challenge/codebasics-resume-project-challenge
Cleaning and Preprocessing: A Prerequisite for Analysis
First things first, let’s ensure the integrity of your data. Cleaning and preprocessing are essential steps to guarantee accuracy and reliability. This involves tasks like removing duplicates, handling missing values, and converting data types. Your dataset comprises a wealth of customer information, product records, order specifics, and delivery performance statistics.
We will not cover this part for the length of the article and the quality of our data that was in a good state and clean.
Please refer to this repo for more code : https://github.com/mehdy28/Supply_Chain_Desktop_App_1
Visualizing Product Categories: Unveiling Insights
A great starting point for your EDA journey is to visualize the distribution of products across various categories. By doing so, you can uncover trends that will shape your app’s design and features. Python’s powerful data visualization libraries, such as Matplotlib and Seaborn, will be your allies in creating informative bar charts showcasing product distribution within each category.
# Sample Python code snippet for visualizing product categories
import matplotlib.pyplot as plt
import seaborn as sns
# Create a bar plot of product categories
plt.figure(figsize=(10, 6))
sns.countplot(x='product_category', data=data)
plt.title('Product Distribution by Category')
plt.xticks(rotation=45)
plt.show()
Uncovering Key Insights: Embarking on Exploratory Data Analysis
With your data polished and ready, it’s time to embark on your EDA journey. The goal? Extracting insights that will guide your app’s development and enhance its effectiveness. Let’s delve into some essential aspects of EDA that will steer your project in the right direction.
Customer Analysis: Deciphering Customer Behavior
To offer tailor-made solutions and impeccable service, you need to understand your customers inside out. Analyzing customer data will unveil ordering patterns, delivery preferences, and overall satisfaction levels. Visualizing metrics like on-time delivery rates, in-full delivery rates, and OTIF (On Time In Full) percentages will provide a comprehensive snapshot of customer behavior.
# Sample Python code snippet for customer analysis visualization
plt.figure(figsize=(10, 6))
sns.histplot(data['on_time_delivery'], bins=20, kde=True)
plt.title('Distribution of On-Time Delivery Rates')
plt.xlabel('On-Time Delivery Rate')
plt.ylabel('Frequency')
plt.show()
Product Analysis: Spotting High Performers
Products are the backbone of the supply chain, and analyzing their performance can drive data-backed decisions. Metrics like line fill rates and volume fill rates are your compass for assessing product ordering and delivery efficiency. Identifying top-performing products can fine-tune inventory management and ensure swift response to customer demands.
# Sample Python code snippet for product analysis visualization
plt.figure(figsize=(10, 6))
sns.boxplot(x='line_fill_rate', y='product_category', data=data)
plt.title('Line Fill Rates by Product Category')
plt.xlabel('Line Fill Rate')
plt.ylabel('Product Category')
plt.show()
Delivery Performance: Tracking Punctuality and Precision
At the heart of seamless supply chain management lies punctual and precise deliveries. Scrutinizing delivery metrics such as on-time delivery percentages and in-full delivery percentages exposes areas ripe for enhancement. Crafting visualizations that track these metrics over time unveils trends and guides proactive measures.
# Sample Python code snippet for delivery performance visualization
plt.figure(figsize=(10, 6))
sns.lineplot(x='order_date', y='on_time_delivery', data=data)
sns.lineplot(x='order_date', y='in_full_delivery', data=data)
plt.title('Delivery Performance Over Time')
plt.xlabel('Order Date')
plt.ylabel('Percentage')
plt.legend(['On-Time Delivery', 'In-Full Delivery'])
plt.show()
Building Your Dashboard: From Insights to Action
With insights gleaned from EDA, it’s time to transform data into a dynamic dashboard that empowers businesses to make informed choices. A well-crafted dashboard offers a visual panorama of KPIs, trends, and performance metrics, equipping users to swiftly grasp critical information.
| No. | Metric | Abbreviation | Description |
|---|---|---|---|
| 1 | Total Order Lines | Count of all order lines in the fact_orders table | |
| 2 | Line Fill Rate | LIFR % | Number of order lines shipped In Full Quantity / Total Order Lines |
| 3 | Volume Fill Rate | VOFR % | Total Quantity shipped / Total Quantity Ordered |
| 4 | Total Orders | ||
| 5 | On Time Delivery % | OT % | Number of orders delivered On Time / Total Number of Orders |
| 6 | In Full Delivery % | IF % | Number of orders delivered in Full quantity / Total Number of Orders |
| 7 | On Time In Full % | OTIF % | Number of orders delivered both IN Full & On Time / Total Number of Orders |
| 8 | On Time Target | Average of On-Time Target | |
| 9 | In Full Target | Average of In-Full Target | |
| 10 | On Time In Full Target | Average of OTIF Target |
This is an example of code on how we can achieve these KPIs:
# 1. Total Order Lines
def total_order_lines(order_lines_df):
return len(order_lines_df)
# 2. Line Fill Rate (LIFR %)
def line_fill_rate(order_lines_df):
in_full_lines = order_lines_df['In Full'].sum()
total_lines = total_order_lines(order_lines_df)
if total_lines == 0:
return 0
return (in_full_lines / total_lines) * 100
# 3. Volume Fill Rate (VOFR %)
def volume_fill_rate(order_lines_df):
total_qty_shipped = order_lines_df['delivery_qty'].sum()
total_qty_ordered = order_lines_df['order_qty'].sum()
if total_qty_ordered == 0:
return 0
return (total_qty_shipped / total_qty_ordered) * 100
# 4. Total Orders
def total_orders(orders_aggregate_df):
return len(orders_aggregate_df)
# 5. On Time Delivery % (OT %)
def on_time_delivery_rate(orders_aggregate_df):
on_time_orders = orders_aggregate_df['on_time'].sum()
total_orders_count = total_orders(orders_aggregate_df)
if total_orders_count == 0:
return 0
return (on_time_orders / total_orders_count) * 100
# 6. In Full Delivery % (IF %)
def in_full_delivery_rate(orders_aggregate_df):
in_full_orders = orders_aggregate_df['in_full'].sum()
total_orders_count = total_orders(orders_aggregate_df)
if total_orders_count == 0:
return 0
return (in_full_orders / total_orders_count) * 100
# 7. On Time In Full % (OTIF %)
def on_time_in_full_rate(orders_aggregate_df):
otif_orders = orders_aggregate_df['otif'].sum()
total_orders_count = total_orders(orders_aggregate_df)
if total_orders_count == 0:
return 0
return (otif_orders / total_orders_count) * 100
# 8. On Time Target
def avg_on_time_target(target_orders_df):
return target_orders_df['ontime_target%'].mean()
# 9. In Full Target
def avg_in_full_target(target_orders_df):
return target_orders_df['infull_target%'].mean()
# 10. On Time In Full Target
def avg_otif_target(target_orders_df):
return target_orders_df['otif_target%'].mean()
# Example usage:
total_order_lines_count = total_order_lines(order_lines_df)
line_fill_rate_percent = line_fill_rate(order_lines_df)
volume_fill_rate_percent = volume_fill_rate(order_lines_df)
total_orders_count = total_orders(orders_aggregate_df)
on_time_delivery_percent = on_time_delivery_rate(orders_aggregate_df)
in_full_delivery_percent = in_full_delivery_rate(orders_aggregate_df)
on_time_in_full_percent = on_time_in_full_rate(orders_aggregate_df)
avg_on_time_target_value = avg_on_time_target(target_orders_df)
avg_in_full_target_value = avg_in_full_target(target_orders_df)
avg_otif_target_value = avg_otif_target(target_orders_df)
print("Total Order Lines:", total_order_lines_count)
print("Line Fill Rate (LIFR %):", line_fill_rate_percent)
print("Volume Fill Rate (VOFR %):", volume_fill_rate_percent)
print("Total Orders:", total_orders_count)
print("On Time Delivery % (OT %):", on_time_delivery_percent)
print("In Full Delivery % (IF %):", in_full_delivery_percent)
print("On Time In Full % (OTIF %):", on_time_in_full_percent)
print("On Time Target:", avg_on_time_target_value)
print("In Full Target:", avg_in_full_target_value)
print("On Time In Full Target:", avg_otif_target_value)
Total Order Lines: 57096
Line Fill Rate (LIFR %): 65.96083788706738
Volume Fill Rate (VOFR %): 96.59059222446581
Total Orders: 31729
On Time Delivery % (OT %): 59.03117022282455
In Full Delivery % (IF %): 52.78136720350468
On Time In Full % (OTIF %): 29.020769642913425
On Time Target: 86.08571428571429
In Full Target: 76.51428571428572
On Time In Full Target: 65.91428571428571
Dashboard Components: Assembling the Puzzle
Your dashboard should encompass diverse components that collectively paint a vivid picture of supply chain performance. These components may include:
Order Overview:
Summarizing total orders, on-time delivery rates, and in-full delivery rates.
def total_orders_analysis(df):
total_orders = len(df)
total_delivered = df['In Full'].sum()
total_on_time = df['On Time'].sum()
avg_delivery_qty = df['delivery_qty'].mean()
return {
'Total Orders': total_orders,
'Total Delivered': total_delivered,
'Total On Time': total_on_time,
'Avg Delivery Quantity': avg_delivery_qty
}
total_orders_analysis(order_lines_df)
{'Total Orders': 57096,
'Total Delivered': 37661,
'Total On Time': 40605,
'Avg Delivery Quantity': 227.14650763626173}
Customer Insights:
Unveiling customer distribution.
# Function to visualize the count of customers in each city
def plot_city_counts(data_frame):
city_counts = data_frame['city'].value_counts()
plt.figure(figsize=(8, 5))
plt.bar(city_counts.index, city_counts.values, color='skyblue')
plt.xlabel('City')
plt.ylabel('Number of Customers')
plt.title('Customer Counts by City')
plt.xticks(rotation=45)
plt.show()
Delivery Metrics:
Visualizing delivery performance metrics such as on-time and in-full delivery percentages.
def calculate_metrics_and_plot(data_frames):
# Calculate Total Orders
total_orders = orders_aggregate_df.shape[0]
# Calculate On Time Delivery %
on_time_delivery_percentage = (orders_aggregate_df['on_time'].sum() / total_orders) * 100
# Calculate In Full Delivery %
in_full_delivery_percentage = (orders_aggregate_df['in_full'].sum() / total_orders) * 100
# Calculate On Time In Full %
otif_delivery_percentage = (orders_aggregate_df['otif'].sum() / total_orders) * 100
# Create a time-based index from date_df
date_df['date'] = pd.to_datetime(date_df['mmm_yy']) # Correct the column name
date_df.set_index('date', inplace=True)
# Aggregate delivery metrics by date
aggregated_metrics = orders_aggregate_df.groupby('order_placement_date').mean()
# Plot On Time Delivery %, In Full Delivery %, and On Time In Full % over time
plt.figure(figsize=(10, 6))
plt.plot(aggregated_metrics.index, aggregated_metrics['on_time'], label='On Time Delivery %')
plt.plot(aggregated_metrics.index, aggregated_metrics['in_full'], label='In Full Delivery %')
plt.plot(aggregated_metrics.index, aggregated_metrics['otif'], label='On Time In Full %')
plt.xlabel('Date')
plt.ylabel('Percentage')
plt.title('Delivery Metrics Over Time')
plt.legend()
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()
# Call the function with your data frames
data_frames = [ date_df, products_df, order_lines_df, orders_aggregate_df]
calculate_metrics_and_plot(data_frames)
These are some of the possible Visualizations and KPIs to start with. You can find an in depth analysis in the github repo:
https://github.com/mehdy28/Supply_Chain_Desktop_App_1
Data Visualization Tools: Bringing Data to Life
To craft an engaging dashboard, leverage data visualization tools like Matplotlib and Seaborn. These tools empower you to create visually appealing charts and graphs that effectively communicate crucial insights. Thoughtful selection of chart types, colors, and labels ensures your dashboard strikes the perfect balance between information and aesthetics.
User Interactivity: Enhancing User Experience
Interactivity is the lifeblood of any effective dashboard. Implement features enabling users to filter and drill down into specific data points. Users should seamlessly select date ranges or customer segments to access pertinent insights. User-friendly interactivity elevates your dashboard’s usability and value.
Conclusion: Paving the Path to Supply Chain Excellence
In this inaugural part of our series, we’ve embarked on a journey to elevate your supply chain management project. The fusion of data analysis, visualization, and dashboard creation is setting the stage for a groundbreaking app. Remember, this is just the beginning – the next article will delve into the creation of a robust database to store your valuable data. By mastering these foundational aspects, you’re on track to craft a supply chain app that optimizes logistics, empowers decision-makers, and propels businesses towards excellence.
Stay tuned for Part 2, where we’ll guide you through the intricate process of database development. Your dream of creating a transformative supply chain management app is well within reach. Keep the momentum going, and let’s collectively shape the future of logistics and operations!
Disclaimer: The code snippets provided are illustrative examples. Actual implementation may vary based on your specific requirements and programming environment.