Start by writing down how the report is made
Before automating anything, follow the report once from start to finish and write down each step: which systems you export from, which filters you apply, which columns you rename, which rows you delete and why, and which numbers you check by eye. The manual fixes matter most. They are the rules the automation has to know.
Keep a copy of last week's finished report. It is the test: the automated version should produce the same numbers from the same exports.
Step 1: get the exports into one place
Many tools can send an export on a schedule by email, save it to a shared folder, or offer a link that always returns the latest data. Use whatever your tools provide, and agree on one folder or one inbox where the files land.
If a tool has no scheduled export, someone may still need to click export once a week. That is fine to start with: the slow part is usually what happens after the download, not the download itself.
Step 2: combine them with something repeatable
In Excel, Power Query can load every file in a folder, apply the same cleaning steps each time and refresh with one click. In Google Sheets, IMPORTDATA can read a CSV file from a link, and QUERY can filter and summarise it. For anything larger, a short script does the same job and can run on a schedule.
Whichever you use, the steps you wrote down become saved steps: the same filters, the same renamed columns, the same rules for the rows you used to delete by hand.
Step 3: check the numbers before anyone sees them
An automated report that is wrong is worse than a late one, because people trust it. Add checks that run every time:
- Every expected export arrived, and none of them is last week's.
- Row counts are in their usual range, so a half-empty export stands out.
- Totals match the source system wherever you can compare them.
- Anything that could not be matched or categorised is listed, not dropped.
Step 4: send it, with the exceptions on top
Once the checks pass, the report can go out on its own: as an email, a shared sheet or a dashboard in a tool such as Looker Studio or Power BI. Put whatever the checks flagged at the top. The person who used to build the report now reviews it instead.
When it is worth having it built
If the report draws on several systems, needs cleaning rules that are awkward in a spreadsheet, or has to be right every week, a small purpose-built tool is the steadier option. Our Digital Ops Cockpit demonstration produces its weekly digest from the same records the team works in, so the report cannot drift from the data.
A report like this is a typical Workflow Fix: one task automated end to end, with a check step and handover notes, for a fixed price agreed before any work starts.