Mathew K AnalyticsLesson 15 · Supply Chain Operations Analytics
Mastering Core Supply Chain Views for Enhanced Operations Analytics
In this lesson, we explore how to build fundamental supply chain and operations analytics views using real-world data. Building these views helps…
- CourseSupply Chain Operations Analytics
- Lesson15 of 27
- Video24 min
- FormatJupyter notebook · 18 code cells
What you'll learn
- Understanding Core Supply Chain Data
- Example 1: Loading Retail Sales Data
- Example 2: Inspecting an Inventory and Operations Log
- Example 3: Loading a Supplier Performance Dataset
- Example 4: Viewing Order Fulfillment Data
- Example 5: Exploring Sales Forecasting Data
- Intermediate 1: Counting Stock Keeping Units (SKUs)
- Intermediate 2: Calculating Daily Total Demand
Data
No separate download needed — the notebook creates or downloads everything it uses.
📓 Full notebook
Download .ipynbBuilding Core Supply Chain Views#
- In this lesson, we explore how to build fundamental supply chain and operations analytics views using real-world data.
- Building these views helps organizations monitor inventory, track sales, measure supplier performance, and optimize fulfillment.
- You will learn to organize operational data, clean and analyze it, and build key dashboards or analytical tables.
- By the end, you will know how to transform raw supply chain data into actionable business insights.
import pandas as pd
import numpy as np
import openml
import warnings
warnings.filterwarnings('ignore')
Understanding Core Supply Chain Data#
- Supply chain data includes sales, inventory, supplier, and operational performance records.
- Each dataset usually contains time, location, product/part, and quantity information.
- Beginners often confuse transactional records (orders) with static records (inventory balance).
- It is important to recognize what your data describes and its time granularity.
Example 1: Loading Retail Sales Data#
- Our first example uses historical sales data from a real online retail store.
- We will preview the data and discuss what each column means.
# Load retail sales dataset from UCI repository
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df_sales = pd.read_excel(url, sheet_name='Year 2010-2011')
df_sales['InvoiceDate'] = pd.to_datetime(df_sales['InvoiceDate'])
print(df_sales.shape)
print(df_sales.head(3))
Example 2: Inspecting an Inventory and Operations Log#
- Next, we examine manufacturing operations data with inventory actions.
- Operations logs help us trace resource usage and bottlenecks.
dataset = openml.datasets.get_dataset(43900)
X_ops, y_ops, _, _ = dataset.get_data(dataset_format='dataframe')
df_ops = X_ops.copy() if y_ops is None else pd.concat([X_ops, y_ops], axis=1)
print(df_ops.shape)
print(df_ops.head(3))
Example 3: Loading a Supplier Performance Dataset#
- Supplier performance metrics are critical to procurement and logistics.
- Let us load and review a real supplier-focused operations dataset.
dataset = openml.datasets.get_dataset(42125)
df_suppliers, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df_suppliers.shape)
print(df_suppliers.head(3))
Example 4: Viewing Order Fulfillment Data#
- Order fulfillment data tracks each stage of the customer order lifecycle.
- These datasets allow us to study lead times and delivery performance.
url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
df_orders = pd.read_csv(url)
df_orders['order_purchase_timestamp'] = pd.to_datetime(df_orders['order_purchase_timestamp'])
df_orders['order_delivered_customer_date'] = pd.to_datetime(df_orders['order_delivered_customer_date'])
print(df_orders.shape)
print(df_orders.head(3))
Example 5: Exploring Sales Forecasting Data#
- Sales forecasting datasets help predict future demand based on historic trends.
- Using real-world data, we can practice time-series aggregations.
dataset = openml.datasets.get_dataset(4549)
X_sales, y_sales, _, _ = dataset.get_data(dataset_format='dataframe')
df_forecast = pd.concat([X_sales, y_sales], axis=1)
print(df_forecast.shape)
print(df_forecast.head(3))
Intermediate 1: Counting Stock Keeping Units (SKUs)#
- Understanding how many unique products you stock is a basic but crucial supply chain view.
- Let us calculate the number of unique SKUs in the sales data.
sku_count = df_sales['StockCode'].nunique()
print(f'Total unique SKUs in sales data: {sku_count}')
Intermediate 2: Calculating Daily Total Demand#
- Summing up sales quantities by day gives a core view of demand trends.
- Let us aggregate the retail data by invoice date.
df_sales['date'] = df_sales['InvoiceDate'].dt.date
daily_demand = df_sales.groupby('date')['Quantity'].sum()
print(daily_demand.head())
Intermediate 3: Measuring Average Lead Time for Fulfillment#
- Lead time is the delay between receiving an order and fulfilling it.
- We will measure average delivery lead time using our order fulfillment data.
mask = (~df_orders['order_purchase_timestamp'].isnull()) & (~df_orders['order_delivered_customer_date'].isnull())
df_orders['lead_time'] = (df_orders['order_delivered_customer_date'] - df_orders['order_purchase_timestamp']).dt.days
avg_lead_time = df_orders.loc[mask, 'lead_time'].mean()
print(f'Average delivery lead time: {avg_lead_time:.2f} days')
Advanced 1: Building a Pivot Table Across Key Supply Chain Dimensions#
- A pivot table helps summarize by multiple dimensions, such as country and product.
- Let us create a summary of total quantities sold by country and product.
pivot = df_sales.pivot_table(index='Country', columns='StockCode', values='Quantity', aggfunc='sum', fill_value=0)
print(pivot.iloc[:5, :5])
Advanced 2: Generating a Supplier Performance Ranking#
- Ranking suppliers by performance metrics lets you target improvements.
- Here, let us rank suppliers by gross pay received.
if '2016_gross_pay_received' in df_suppliers.columns:
ranking = df_suppliers[['full_name', '2016_gross_pay_received']].sort_values(by='2016_gross_pay_received', ascending=False)
print(ranking.head(5))
else:
print('Metric not available in this dataset.')
Advanced 3: Visualizing Seasonality in Sales (with pandas plotting)#
- Visualizing time trends helps identify seasonality in sales demand.
- Let us plot monthly retail demand using a built-in pandas plot.
import matplotlib.pyplot as plt
df_sales['month'] = df_sales['InvoiceDate'].dt.to_period('M')
monthly_demand = df_sales.groupby('month')['Quantity'].sum()
monthly_demand.plot(kind='line', marker='o', title='Monthly Retail Demand')
plt.xlabel('Month')
plt.ylabel('Total Quantity Sold')
plt.tight_layout()
plt.show()
Error Handling: Missing Dates in Order Data#
- Sometimes, delivery dates or purchase timestamps are missing in real order records.
- We should always check for missing or invalid date fields before analysis.
missing_dates = df_orders['order_delivered_customer_date'].isnull().sum()
print(f'Number of orders missing delivered date: {missing_dates}')
Error Handling: Incorrect Joins Produce NaN Values#
- Common joining mistakes in supply chain views lead to missing connections (NaN).
- We will illustrate a join between sales and supplier data on an incorrect key.
# This join is purposely incorrect for illustration
df_bad_join = pd.merge(df_sales, df_suppliers, left_on='StockCode', right_on='full_name', how='left')
nan_count = df_bad_join['full_name'].isnull().sum()
print(f'Rows with missing supplier after join: {nan_count}')
Error Handling: Misinterpreting Lead Times or Quantities#
- Sometimes, negative lead times or quantities signal data entry errors.
- Let us check for negative or zero values in these fields.
num_negative_qty = (df_sales['Quantity'] <= 0).sum()
num_negative_lt = (df_orders.get('lead_time', pd.Series()) < 0).sum()
print(f'Negative or zero sales quantities: {num_negative_qty}')
print(f'Negative lead times detected: {num_negative_lt}')
Best Practices: Using Group-By for Supply Chain Dashboards#
- Group-by is a workhorse operation for summarizing all supply chain data.
- Let us group demand by country and month for higher-level insight.
agg = df_sales.groupby(['Country', 'month'])['Quantity'].sum().reset_index()
print(agg.head())
Best Practices: Calculating Fill Rate as an Operations KPI#
- Fill rate measures the percentage of demand satisfied by on-time supply.
- Let us demonstrate a simplified fill rate calculation with sales data.
# For demo, define delivered as Quantity > 0; calculate percent of positive quantity lines
filled_orders = (df_sales['Quantity'] > 0).sum()
total_orders = df_sales.shape[0]
fill_rate = 100 * filled_orders / total_orders
print(f'Fill rate (proxy): {fill_rate:.2f}%')
End-to-End Problem: From Raw Sales Data to a 3-Level Summary Table#
- Let us solve a full supply chain analytics case: summarize sales by country, month, and product.
- Our final table will help understand demand patterns at several levels.
summary = df_sales.groupby(['Country', 'month', 'StockCode'])['Quantity'].sum().reset_index()
summary = summary.sort_values(['Country', 'month', 'Quantity'], ascending=[True, True, False])
print(summary.head(10))
Recap: Building Core Views in Supply Chain & Operations Analytics#
- We used real datasets to learn how to load, clean, aggregate, and summarize supply chain data.
- You now know how to build operational reports, debug common errors, and answer core business questions.
- Practice these skills on your own datasetsand subscribe to our YouTube channel for more advanced tutorials!
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
19 minLesson 11
Mastering Survey & Market Research Dataset Structures in Python | Analytics Training
22 minLesson 15
How to Build Core Banking Views with Python for Financial Data Management
29 minLesson 47
Analyzing Growth in Views and Subscribers
27 minLesson 47