Automating Executive Reporting Across Multiple Data Sources

Problem

A hospitality organization used a recurring report to track web and digital performance across multiple data sources. The analytics team reviewed the results internally and used them as part of quarterly reporting to the CTO.

Producing the report manually took dozens of hours each month. Data had to be collected from several sources, cleaned and combined, turned into custom charts and indicators, and assembled into Excel and PowerPoint deliverables.

The amount of manual work also limited how detailed the reporting could reasonably become.

Challenge

The report combined data from external APIs and CSV files with screenshots captured directly from the organization's website. The finished output needed to include the underlying data, custom charts, month-over-month performance indicators, website visuals, and a presentation suitable for executive review.

The data preparation itself was fairly straightforward. The harder part was making the entire process repeatable while still producing polished deliverables.

Website screenshots had to be captured automatically and then sized and positioned consistently within the PowerPoint presentation. The workflow also needed to handle missing data or an unavailable source without causing the rest of the report to fail.

Because the report was used over several years, it also needed to be flexible enough to accommodate changing stakeholder preferences without requiring the entire process to be rebuilt.

Approach

I built a Python workflow that handled the process from data collection through final delivery.

The workflow retrieved data from external APIs and CSV sources, then used pandas and NumPy to clean, combine, and prepare the information for reporting. A separate Selenium process captured current screenshots from the organization's website for use in the presentation.

From the prepared data, the workflow automatically generated two main deliverables:

• an Excel workbook containing the supporting data, custom charts, and color-coded indicators for positive and negative month-over-month changes
• a PowerPoint presentation containing stakeholder-defined charts, performance summaries, and automatically captured website screenshots

The finished files were distributed automatically with SendGrid. The analytics team could then review the results, add commentary where needed, and focus on interpreting the report rather than building it.

The process ran on a recurring schedule using cron and required very little manual work once report generation started.

Technical Decisions

One goal was to automate the repetitive parts of reporting without changing how the client actually wanted to work.

Excel and PowerPoint were already familiar parts of their review process, so I kept those as the final deliverables rather than introducing a new reporting platform. The automation handled the time-consuming work of collecting data, applying the reporting logic, creating visuals, and assembling the files while leaving the analytics team in control of the final review and commentary.

I also designed the workflow so that problems with one source did not necessarily stop the entire report. Missing values could be handled within the report, and an unavailable external source could be excluded from a run rather than causing the full process to fail.

The data-processing and presentation logic were kept separate enough that charts, indicators, and presentation layouts could be updated as stakeholder preferences changed.