During my professional training at Swiss Re, I worked on improving the way our team manages and tracks workload. Because our team’s work is primarily assistant support, each email we receive represents an action to be taken. Emails are categorized both by the assistant responsible and by the market or business area they support. These categories form the backbone of our workload tracking system, but until recently, the way they were collected and prepared for reporting was largely manual.
Previously, each month’s data had to be exported from Outlook manually via a Search Folder for the given period. The emails were then copied into a new Excel file every month, sorted, cleaned, with unnecessary items deleted and categories adjusted one by one. This manual process took a long time and often introduced errors or inconsistencies, making it harder to rely on the results. Another disadvantage of the old workflow is that they were multiple source files in Power BI, new for each month which was messy and difficult to maintain.
To solve these issues, I developed an automated workflow using Python. The script connects directly to Outlook, searches for all relevant emails from the previous month, and downloads them automatically. It runs in the middle of current month for the previous one because I wanted to introduce a two week buffer period, so all the tasks have time to get finalized and properly sorted. The data is then cleaned and prepared by removing automatic replies, standardizing categories and folders, and tracking actions based on the number of relevant emails. The cleaned results are appended to a single master Excel file, which provides a smooth foundation for Power BI reporting in comparison to the many individual files before. A scheduled email reminder is also sent to the team’s mailbox a few days prior to the email extraction, encouraging colleagues to finish categorizing their emails so that the report is always accurate.
The workflow relies on several tools working together. Outlook serves as the source of data, Excel as the storage and editing tool for combining all records, and Power BI as the visualization and analysis tool. Python automates extraction, cleaning, and transformation of the emails, while Windows Task Scheduler ensures the entire process runs on time each month without requiring manual input. Together, these tools create a fully automated reporting pipeline.
The Power BI dashboard has also been updated with clearer visuals, making it easier to track workload trends by assistant, market, and month. Automatic refresh ensures that the data from source file uploaded in OneDrive is always up to date, meaning the team can focus on their work instead of managing spreadsheets.
0 Comments