Automating client reporting with Python
How a few scripts replaced hours of manual reporting work every week.
Client reporting is the work that quietly eats an agency. It is not hard, it is not interesting, and it has to happen every single month regardless of what else is on. For a while I was spending the better part of two days a month pulling numbers into decks.
Here is the shape of what replaced it, and — more usefully — what I got wrong first.
The pipeline
Three stages, deliberately kept separate:
Pull. One script per data source, each writing raw responses to disk before anything touches them. Search Console and GA4 both have Python client libraries; rank data comes from whichever API the client’s tooling uses. Every response gets cached with the date in the filename.
Transform. A second stage reads the cached files and produces one tidy table per client per month — sessions, conversions, clicks, impressions, average position, plus month-over-month and year-over-year deltas. This is where segment definitions live, and it is the only place they live.
Render. The tidy table goes into a template. I moved from slide decks to a simple HTML report, which meant charts could be generated inline and the whole thing could be emailed as a link rather than a 30MB attachment.
The mistake I made first
I built it as one script. Pull, transform, and render all in a single run, no intermediate files.
It worked until an API changed a field name. Then every re-run to debug the transform also re-hit the API, which meant rate limits, which meant waiting, which meant a fifteen-minute feedback loop for a one-line fix. Splitting the stages and caching the raw pulls turned that into fifteen seconds.
Cache the raw response. Always. Storage is free and API calls are not.
The part that actually saved the time
Not the data pulling — that was maybe 40% of the work. The real saving was the commentary scaffold.
A report is not numbers, it is an explanation. The slow part was writing “organic sessions are up 12% month over month, driven mostly by the new comparison pages” thirty times over. So the transform stage now emits a set of flagged observations: which segments moved more than a threshold, which pages gained or lost the most clicks, which queries newly entered the top ten.
Those observations get passed to a model with the client’s context to draft the commentary, and I edit it. Drafting from a blank page took twenty minutes per client. Editing a draft that already has the right numbers in it takes three.
That is the honest version of “AI automation” in this workflow: it is not writing the report. It is removing the blank page, while the numbers underneath stay deterministic — computed by code, not generated by a model. I would not let a model near the arithmetic.
Scheduling and failure
Everything runs on a cron on a small VPS. Two rules that matter more than they sound:
- Fail loudly. A reporting job that silently produces last month’s numbers is worse than one that crashes. Every stage validates that the data it loaded covers the window it expected, and refuses to continue if not.
- Make it re-runnable. Any stage can be run again for any month without side effects. When something does go wrong at 6am on the first, you want to fix and re-run, not untangle partial state.
Was it worth building?
For one client, no. Two days a month across a book of clients, yes — it paid for itself in about six weeks and it has not needed real attention since.
The test I’d apply: if the task is repetitive, rule-based, and the inputs arrive in a predictable format, automate it. If it needs judgement about what matters to a specific client, keep the human in it — but let the machine do everything up to the point where the judgement starts.