Everything was manual. Nothing connected.
The Operations and Finance teams at Task Source were spending hours each week pulling data from over 7 separate sources — Excel sheets, CRM exports, and SQL databases — and manually copying figures into reports. Every reporting cycle meant the same repetitive work: open a file, copy a number, paste it elsewhere, format it, repeat.
There was no single source of truth. Numbers in one report didn't match another. By the time a report landed in someone's inbox, the data was already hours old. Decisions were being made on stale information.
Hours of manual copy-paste every reporting cycle across 2 teams, with no live visibility into business performance.
Map everything. Automate the pipeline. Build once.
Before writing a single line of Python, I mapped every data source and understood what each team actually needed to see. The goal wasn't just to build a dashboard — it was to eliminate the manual work entirely so the data could speak for itself, automatically.
Audit & Map Data Sources
Identified all 7+ sources: Excel workbooks, CRM export files, and SQL database tables. Documented field names, update frequency, and ownership for each source.
Python Data Scraping & Extraction
Wrote Python scripts to scrape and extract data from multiple sources — Excel files, CRM exports, and SQL databases — automating what was previously done by hand every reporting cycle.
Clean & Transform with Python
Used pandas to clean and transform the scraped data — standardising date formats, column names, and handling nulls across every feed before consolidating into a unified output.
Design the Data Model
Structured the cleaned data into a unified model in Excel — fact tables for transactions with dimension columns for time, teams, and categories — enabling fast, flexible filtering.
Build KPIs & Visuals
Built charts, KPI summaries, and heatmaps in Excel tailored to what each team — Operations and Finance — needed to act on daily, auto-populated by the Python pipeline.
Automate & Hand Off
Scheduled the Python script to run automatically so reports updated without manual intervention. Ran a walkthrough with both teams and documented the workflow for self-service use.
Clean data. Two destinations. Zero manual steps.
The Python pipeline wrote final, structured output to two destinations depending on the use case — an Excel file for team-level review, and SQL Server for centralised storage and querying. Both updated automatically on each run.
Excel Output
Structured Excel workbook auto-generated by Python (openpyxl) — formatted sheets per team with KPI summaries, charts, and heatmaps ready to open and share.
SQL Server Storage
Cleaned, unified data written directly to SQL Server tables — enabling ad-hoc querying, historical comparisons, and future reporting on top of a reliable data store.
KPI Summaries
At-a-glance metrics for Operations and Finance — revenue, volume, and targets — computed by Python and written directly into the Excel output.
Scheduled Automation
The entire pipeline — scrape, clean, transform, write — ran on a schedule with no human trigger required. Reports were always up to date at the start of each day.
40% less time spent. Zero manual reports.
Within the first week of deployment, the manual reporting process was fully replaced. Both teams adopted the dashboard as their primary source of truth for daily and weekly reviews.
The dashboard became the default reporting tool for both teams — replacing all manually compiled Excel reports permanently.
What I'd do differently.
Involve end users earlier
Running stakeholder interviews before designing the layout would have saved one revision cycle. I now start every dashboard project with a requirements session.
Document the data model from day one
Keeping a living data dictionary alongside the build made handoff much cleaner and reduced questions from both teams post-launch.
Automate refresh monitoring
Adding an alert for failed scheduled refreshes would prevent stale data from going unnoticed — something I've built into every project since.