Automate the distribution of supply chain operational reports with visuals built in HTML emails with Python
Article originally published on: Medium.
You are a continuous improvement engineer in the distribution centre of a fashion retail company in charge of performance reporting.
On a weekly basis, you connect to the systems, extract data and perform analysis to build operational dashboards.
You send via email to your management weekly reports with operational indicators.
This process is time-consuming because you do it manually with Excel.
In this article, we will build a fully automated solution to share a one-page operational report in an HTML email using Python.
💌 New articles straight in your inbox for free: Newsletter
You are a continuous improvement manager in an international clothing group that has stores all around the world.
The company is producing garments, bags and accessories in factories located in Asia.
Stores are delivered from local warehouses that are directly replenished by factories.
On a regular basis, you have to share warehouse operational indicators with your management.
This is a manual process in which you need to
- Extract data from the Warehouse Management System (WMS)
- Process data and built visuals with Excel
- Send a short report by email with the visuals and comments
To be efficient, you would like to automate this process using Python.
Your solution will be a simple python script, deployed on the cloud, that will automatically perform these four steps:
- Extract prepared order lines of last week from the WMS SQL database
- Process the data and compute KPIs with key insights
- Automatically send an HTML email with visuals and comments
The final report will look like the image below:
- The title will be adapted to the current week
- A bar plot visual will be included
- A comment area will provide insights based on the visual
The full process will be automated so the reports can be sent on time without your support.
If you are looking for examples of application for operational management, have a look at this short video
You can find the source code with dummy data here: Github
Let us explore all the steps to generate your final report.
As you won’t have access to my WMS databases, I have shared a CSV file with dummy data.
But your solution will be connected to your WMS
- Create your SQL Query to extract shipment records
- Use pandas.read_sql_query to do the query
- Results will be a pandas data frame
An important indicator is the number of lines per order, your processing task will add this column to your data frame.
You need a simple bar plot chart that shows the number of Lines and Orders prepared per day.
Save the image
In order to be embedded in your HTML page, you need to save it
An added value of your report is to summarize the week of operations in three bullet points.
Therefore, you need to compute the right indicators that will bring visibility to your top management.
These insights will be included in your HTML file for the comment area.
Create the HTML report
In the repository, you can find a template of a very simple HTML page that will be used to create your email.
The structure is simple with
- A header with a logo in a png file
- A title with the current week updated
- Your visual using a png image
- A comment area with updated insights
- A footer where you can put information about the author
Include the images
To include the header and your visual in the HTML you’ll create MIMEImage objects with a content-id that will be put in the HTML code.
Add the insights
In the HTML code, I have put some markers where the insights will be written. The idea is to use the replace function to modify them and put the values returned by the python script.
Create and send the email
To send your email using python you can use the library smtplib.
You need to add the following information (example FYI)
- SMTP server, port: for instance ‘smtp.google.com’, 465
- Your email address and the delivery address
- Your mailbox password
And then you can send your HTML email with updated visuals and insights.
Conclusion & Next Steps
With this very simple example, you have a template to build your own reporting automation solution.
You can now,
- Add visuals or tables
- Bring more insights or enrich the text with conditions
This python script can be launched locally on your computer with one click.
However, as the initial objective is to fully automate the process, you can deploy this code on Heroku and schedule the launch every Monday at 9:00 am.