Automate Operational Reporting with Python

Automate the email distribution of supply chain operational reports with visuals built using Python.

(Image by Author)

You are a data scientist in a fashion retail distribution centre, responsible for performance reporting.

Every week, you connect to the systems, extract data, and perform analyses to build operational dashboards.

How can you automate the process with Python?

You send weekly reports with operational indicators to your management via email.

This process is time-consuming because you do it manually with Excel.

In this article, we will build a fully automated Python solution to share a one-page operational report in an HTML email.

I. How can you send automatic reports by email using Python?
  1. Supply Chain Performance Reporting
  Weekly report of the orders prepared and delivered from a warehouse.
  2. Optimize Performance Management with Automated Reporting
  Automate the preparation and distribution of reports
  3. Results of the automation solution
  HTML reports distributed by email
II. Solution
  1. Extraction of Logistics Data from Systems
  SQL queries and python scripts to extract and process transactional data
  2. Build Charts with Python
  Matplotlib to generate graphs with comments 
  3. Generate HTML pages 
  Include visuals and comments in HTML pages
  4. Send the report by email
  Use smtplib to send these HTML pages by email
III. Conclusion
  1. Animate your Python Graphs with Pillow
  Generate animated GIFs of your graphs to improve user experience
  2. Automate Graphic Design 
  Generate visuals and illlustrations using Python
  3. Generative AI for Email Reporting: GPT x Python
  Improve the user experience witha smart agent powered by GPT

How can you send automatic reports by email using Python?

Supply Chain Performance Reporting

Optimizing supply chain performance for an international clothing group producing garments, bags and accessories in Asia with local warehouses and direct factory replenishment.

WLogistics Network of a Fast Fashion Retailer — (Image by Author)

What do we mean by performance?

Optimize Performance Management with Automated Reporting

Learn how to streamline your warehouse operations reporting with Python automation and say goodbye to manual data processing and visualization.

Regularly, you have to share warehouse operational indicators with your management.

This is a manual process in which you need to

1. Extract data from the Warehouse Management System (WMS)
2. Process data and built visuals with Excel
3. Send a short report by email with the visuals and comments

Can we avoid wasting time with some automation?

Objective

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

Process in 4 steps — (Image by Author)

How does it look like?

Results of the automation solution

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

Final Result — (Image by Author)

The whole process will be automated to send the reports on time without your support.

If you are looking for examples of application for operational management, have a look at this short video

🏫 Discover 70+ case studies using Python to automate reporting and support business optimization 🏪 in this Cheat Sheet

Solution

Let us explore all the steps to generate your final report.

Steps to generate the automated reports sent by email using Python— (Image by Author)

It starts with extracting data …

Extraction of Logistics Data from Systems

I have shared a CSV file with dummy data because you won’t have access to my WMS databases.

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
• The results will be a pandas data frame

How to extract the right indicators from this dataset?

Data Processing using Python

An important indicator is the number of lines per order.

Processing tasks will add this column to your data frame.

Let’s visualize now

Build Charts with Python

It would be best to have a simple bar plot chart showing the number of Lines and Orders prepared per day.

Visual of the workload per day using bar plots— (Image by Author)

How do we include these results in an email?
We use HTML.

Save the image in an HTML Page using Python

To be embedded in your HTML page, you need to save it

Can we add some observations based on the data?

Add insights to the report

Your report's added value summarises the week of operations in three bullet points.

Therefore, you need to compute the right indicators to bring visibility to your top management.

These insights will be included in your HTML file for the comment area.

The objective is to have a ready-to-be-read report.

Create the HTML Page with the Report

The repository contains an HTML page template that you can use to create your email.

Structure of the HTML file for the report sent by email — (Image by Author)

The structure is simple, with

• A header with a logo in a PNG file
• A title with the current week's updated
• Your visual using a PNG image
• A comment area with updated insights
• A footer where you can put information about the author

We start by including the illustrations.

Include the images in the HTML report

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.

And we add the text …

Add the insights in the HTML code

I have put some markers where the insights will be written in the HTML code.

The idea is to use the replace function to modify them and put the values returned by the Python script.

Now that the page is ready, we can send it.

Create and send the email with Python

To send your email using Python, you can use the library smtplib.

Automated process to send an email with python — (Image by Author)

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

Then, you can send your HTML email with updated visuals and insights.

You can find the source code with dummy data here

GitHub - samirsaci/automated_report: Automate Operational Reports Distribution in HTML Emails

💡 Follow me on Medium for more articles related to 🏭 Supply Chain Analytics, 🌳 Sustainability and 🕜 Productivity.

Conclusion

This simple example gives you a template to build your reporting automation solution.

You can now,

• Add visuals or tables
• Bring more insights or enrich the text with conditions

What if you want to animate your graphs?

Animate your Python Graphs with Pillow

For some graphs, you want to provide information about a dynamic process.

In that case, static plots may not convey the message or insights expected.

Can we improve warehouse productivity with pathfinding algorithms?

As a supply chain solution manager, I have conducted reengineering studies to find ways to improve picking productivity.

Pathfinding algorithm comparative study — (Image by Author)

In another article, you can find a comparative study between two pathfinding algorithms to minimize walking distance.

Can we spot the difference with static graphs? No.

Therefore, I used Python’s Pillow library to animate these graphs and generate the visual below.

Example of Animated Visuals — (Image by Author)

The detailed tutorial is available in the link below if you want to include a similar animation in your email report.

Improve Warehouse Productivity using Pathfinding Algorithm with Python

Do you want to add nice visuals to your report?

Automate Graphic Design

Use Python Pillow to automate the creation of visuals or illustrations to feed your report.

Example of Graphic Design — (Image by Author)

For example, the labels above have been generated automatically with a Python script.

This a great way to boost user attention and improve interactions by

• Conveying ideas and concepts with animated visuals
• Animate graphs to show a trend

💡 For more information,

Automate Graphic Design using Python

Have you ever heard about generative AI?

Generative AI for Email Reporting: GPT x Python

Since OpenAI introduced ChatGPT’s advanced features, we have had the opportunity to enhance this solution with an intelligent GPT agent.

“The Supply Chain Analyst” — (Image by Author)

Imagine if users can ask the agent to build custom reports and send them via email.

Advanced Solutions Architecture with GPT — (Image by Author)

The “The Supply Chain Analyst” example shows how we can design analytics products with a user interface boosted by Large Language Models (LLMs).

The design approach is quite simple.

You need

• A core module in a Python Script: this will be the solution designed in this article
• Additional prompts to guide the agent on how to interact with users

If you want to build your own GPT, have a look at these articles

Create GPTs to Automate Supply Chain Analytics

Leveraging LLMs with LangChain for Supply Chain Analytics — A Control Tower Powered by GPT

If you prefer the video, you can have a look at the YouTube Tutorial

About Me

Let’s connect on Linkedin and Twitter. I am a Supply Chain Engineer who uses data analytics to improve logistics operations and reduce costs.

For consulting or advice on analytics and sustainable supply chain transformation, feel free to contact me via Logigreen Consulting.

If you are interested in Data Analytics and Supply Chain, look at my website.

Samir Saci | Data Science & Productivity

💌 New articles straight in your inbox for free: Newsletter
📘 Boost your Productivity with Data Analytics: Productivity Cheat Sheet