The initial problem
Our client consolidated data monthly from 4 sources: an ERP, a CRM, Excel exports and a BI tool. The process involved 3 people, took 2 full days and regularly generated input errors.
The architecture
Step 1: collection and normalisation
A Python pipeline with Pandas and SQLAlchemy retrieves data automatically from 4 sources. Data normalised and stored in PostgreSQL. Execution time: 12 minutes.
Step 2: KPI calculation
A calculation module generates the 23 board KPIs. Each compared to previous month and annual target. Significant variations automatically flagged.
Step 3: report generation
A Jinja2 template integrated in a Python workflow generates the Word report then converts it to PDF. Charts produced with Matplotlib and automatically injected at the right places.
Key to success: we spent 40% of project time validating calculation formulas with the client before writing any code.
Lessons learned
Start with the 5 most consulted indicators, deliver in week 1, validate, then extend. Always plan for edge cases: automatic retry and failure email alert are non-negotiable.
Automating a report end to end, from source to delivered document, is one of the projects in the Data Analyst Bootcamp.
