How I automated 40 monthly reporting hours at a retail company

The initial situation

When a retail company with 12 stores contacted me, their reporting process was as follows: every Monday morning, someone from the administration team connected to SAP, exported the previous week's sales in six different Excel files (one per product category), copied them to a shared folder, and then a colleague took those six files, manually unified them in a master Excel with complex formulas, and sent the final report by email to management.

The complete process took between 8 and 10 hours each week. 40 hours a month. And even then, the report arrived with the previous week's data.

The diagnosis

Before writing a line of code, I analyzed the complete process to understand the friction points:

• SAP exported in .xlsx format with inconsistent structure between weeks (sometimes columns changed or field order shifted)
• The master Excel had raw cell references to fixed cells, so if anything changed in SAP, the report broke
• No data validation: nobody detected if a store was missing or if a figure was abnormally high until someone saw it in the meeting
• The final report was static: it didn't allow filtering by store, product, or period

The solution: an ETL pipeline with Python + Power BI

The redesign was done in three layers:

Layer 1: Automatic extraction from SAP

Using the SAP API and a Python script scheduled as a task on the company's Windows server, every night at 2:00 AM the previous day's sales data is automatically extracted for all stores and categories. No human intervention.

The script includes validations: if any store doesn't report data or if there are out-of-range values, it generates an email alert before anyone arrives at the office.

Layer 2: Transformation and loading into database

The extracted data is cleaned, normalized, and loaded into a SQL Server database. This step eliminates the inconsistent structure problem: the data model is fixed, regardless of how SAP exports.

Transformations include: product name cleaning, store code normalization, gross margin calculation, and duplicate detection.

Layer 3: Power BI dashboard

Connected directly to SQL Server, the Power BI dashboard automatically updates every morning with the latest data. Management logs in any day, at any time, and sees:

• Monthly accumulated sales vs. target, by store and category
• Daily evolution with comparison to previous week and previous year
• Top 10 products by margin, with drill-down by store
• Visual alerts when a store is below 80% of its target

The result

Three weeks after implementation, the manual process had disappeared. Concrete results:

• 40 hours/month freed from the administration team for higher-value tasks
• Real-time data instead of one week delayed
• 0 errors in reports since implementation (vs. 2-3 usual manual corrections per month)
• Daily visibility that didn't exist before: they detected in the first week that a store had a stock problem that was costing sales

What do you need to implement it?

This type of project doesn't require changing ERP or large infrastructure investments. Typical requirements are:

1. Access to data (ERP, Excel, existing database) 2. A server or machine that can run the script (even an office PC) 3. A Power BI Pro license (~€10/user/month)

Implementation time for a project of this complexity is 3 to 6 weeks.

If your company has a reporting process that consumes hours of manual work every week, let's talk and I'll tell you if it has a solution.