Automation & Reporting

Automated Supply Chain Control Tower with Python

Optimize your Supply Chain Network with an automated solution to follow your shipments and evaluate the risk of late deliveries

Article originally published on medium.

A supply chain control tower is traditionally defined as a set of dashboards connected to various systems using data to monitor important events across the supply chain.

It enables a Supply Chain department to better track, understand, and resolve critical issues in real-time.

In this article, we will implement automated tools for shipment tracking that automatically send incident reports, follow open shipments and analyze lead times.

💌 New articles straight in your inbox for free: Newsletter

If you prefer, you can watch the video version of this tutorial

Context

Scenario
You are a distribution planning manager in an international clothing group that has stores all around the world.

Stores Replenishment
Distribution planners are managing the inventory in the stores to meet the demand.

Supply Chain Network for Control Tower — Samir Saci — Supply Chain Network — (Image by Author)

When the inventory level reaches the minimum level

Distribution planners create replenishment orders in the ERP with item quantities and requested delivery dates
Warehouse operational teams prepare the orders for shipment
Transportation teams organize the delivery to the stores

Shipment Tracking
On a daily basis, you need to follow the shipments and report incidents to the store and logistic managers:

How many shipments have been delivered with delay?
Where are the shipments currently in transit?
How many transit shipments are at risk?

Our Supply Chain Control Tower will be basically a set of automated python scripts that distribute reports by email answering these questions.

Automated Workflow for Supply Chain Control Tower — Samir Saci — Shipments Tracking in 3 Steps — (Image by Author)

Step 1: Perform SQL queries to extract the shipment information from the systems using pandas.read_sql
Step 2: Process the data and build visuals using pandas and matplotlib
Step 3: Create HTML reports and send them by email using smtplib

Solution

Cut-off times

There are several steps between the order creation in the system and the store delivery managed by different teams

Order transmission to the warehouse: IT Teams
Order preparation in the warehouse: Warehouse Ops
Transportation from the Warehouse to the store: Transportation Ops

Cut-off Times Examples for Distribution — Samir Saci — Keys Steps with Cut-off times — (Image by Author)

For some key steps we have cut-off times, if they are not respected +24 hours are added to the total lead time.

Shipment Records

After performing the SQL queries, you have a data set of shipment information with all the time stamps needed

Last Status: last step of the process at the time you perform the extraction
On-Time Flags: boolean variable checking if the cut-off time has been respected

Analysis of delivered shipments

How many shipments have been delivered after the requested delivery date?

Delivered Shipments with Delays — Samir Saci — Delivered Shipments (Top: On Time, Bottom: Late)— (Image by Author)

On-Time Deliveries: the actual delivery time is before the requested delivery time
Late Deliveries: the actual delivery time is after the requested delivery time

Question 1: How many shipments have been delivered with delay?

Donut Plot of Late Deliveries by Reason Code — Samir Saci — Late Deliveries — (Image by Author)

💡 ANALYSIS
1,842 shipments delivered with delay
- 696 due to order transmission issues
- 60 due to warehouse operational issues
- 630 impacted by transportation
- 456 due to multiple factors
🧮 PYTHON CALCULATION
- Filter the shipments to take only the delivered ones
- Groupby Last Status: count the number of shipments
- Pandas pie plot

Shipments Status by Day

Stacked Bar Plot Chart of Deliveries by Reason Code — Samir Saci — Stacked Bar Plot Chart of Status per Order Day — (Image by Author)

Analysis of shipments in transit

Shipments in transit are the ones that have been ordered and not yet delivered to the store.

Question 2: Where are the shipments currently in transit?

Automated Shipment Tracking for Supply Chain Control Tower — Samir Saci — Shipments in transit — (Image by Author)

💡 COMMENT
2,046 shipments in transit
- 782 in the warehouse
- 593 on flight transit- 535 in customs
- 136 on last-mile delivery
🧮 PYTHON CALCULATION
- Filter the shipments to take only the not delivered one
- Groupby Last Status: count the number of shipments
- Pandas pie plot

Question 3: How many transit shipments are at risk?

Definition
A shipment in transit is considered at risk if it missed the cut-off time or was delayed for at least one step of the whole chain.

Example of Transit Shipment at Risk for Supply Chain Control Tower — Samir Saci — Example of Shipment At Risk — (Image by Author)

In the example below, your shipment left the warehouse behind the schedule. Therefore, it is considered at risk to be delivered late.

Shipments in transit at risk — (Image by Author)

💡 COMMENT
1,099 shipments in transit at risk because of delay
- During Warehouse Operations: 534 shipments
- During the flight: 144 shipments
- Due to multiple factors: 421 shipments
🧮 PYTHON CALCULATION
- Filter the shipments to take only the not delivered one with at least on OnTime flag False
- Map the shipments with reason code: if the flag XX OnTime = False the reason code is XX
- If you have multiple reasons the reason code is Multi
- Groupby Reason code: count the number of shipments
- Pandas pie plot