Automate Operational Reporting with Python
Automate the email distribution of supply chain operational reports with visuals built using Python.
Do you want to optimize your warehouse operations reporting and say goodbye to manual data processing and visualization?
Explore how Python automation can help you extract data, compute KPIs, and send an HTML email with visuals and comments in this article.
Scenario
Optimizing supply chain performance for an international clothing group producing garments, bags and accessories in Asia with local warehouses and direct factory replenishment.
You are a continuous improvement engineer in the distribution centre of a fashion retail company in charge of performance reporting.
Every week, you connect to the systems, extract data and perform analysis to build operational dashboards.
You send weekly reports with operational indicators to your management via email.
This process is time-consuming because you do it manually with Excel.
Objective
In this article, we will build a fully automated Python solution to share a one-page operational report in an HTML email.
💌 New articles straight in your inbox for free: Newsletter
📘 Boost your Productivity with Data Analytics: Productivity Cheat Sheet
If you prefer the video, you can have a look at the YouTube Tutorial
I. How can you send automatic reports by email with Python?
1. 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.
2. 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
- 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
3. 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
4. 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
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
💡 Follow me on Medium for more articles related to 🏭 Supply Chain Analytics, 🌳 Sustainability and 🕜 Productivity.
You can find the source code with dummy data here: Github
II. Solution
Let us explore all the steps to generate your final report.
1. 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
2. Data Processing using Python
An important indicator is the number of lines per order.
Processing tasks will add this column to your data frame.
3. Build Charts with Python
You need a simple bar plot chart that shows the number of Lines and Orders prepared per day.
4. Save the image in an HTML Page using Python
To be embedded in your HTML page, you need to save it
5. Add insights to the report
Your report's added value is summarising 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.
6. Create the HTML Page with the 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's updated
- Your visual using a PNG image
- A comment area with updated insights
- A footer where you can put information about the author
7. 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.
8. 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.
9. Create and send the email with Python
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
Then, you can send your HTML email with updated visuals and insights.
💡 Follow me on Medium for more articles related to 🏭 Supply Chain Analytics, 🌳 Sustainability and 🕜 Productivity.
III. Conclusion & Next Steps
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
Generative AI for Email Reporting: GPT x Python
Since the introduction of ChatGPT’s advanced features by OpenAI, we have had the opportunity to enhance this solution with a smart GPT agent.
Imagine if users can ask the agent to build custom reports and send them via email.
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
Automate Graphic Design
Use Python Pillow to automate the creation of graphs, visuals or illustrations to feed your report.
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,
