How we automated our monthly client SEO reporting

By June of this year we had twelve clients on SEO retainers. Each client expected a monthly report. The report for each client took between 90 minutes and three hours to produce, depending on how complex the situation was. That is a floor of 18 hours a month, every month, on report production. Not strategy, not execution. Just formatting data into slides.

I started automating this because the hours were unsustainable. What I discovered in the process of automating it is that most of the hours were not just unsustainable, they were wasted.

What the reports contained before

Each report had: organic traffic trend for the month versus the previous month versus the same month last year. Keyword ranking movements. A technical health summary. A list of what we had done that month. A list of what we planned to do next month.

Every client got roughly the same structure. The data changed, the words changed, the conclusions changed. The format was identical.

Clients mostly read the traffic number at the top and the "what we did" section. The keyword table, which took the most time to format, was rarely discussed in review calls.

What we built

We set up a system using the Semrush API and Google Search Console API feeding into a Google Sheet, with a script that pulls data on the first day of every month and populates a pre-formatted Google Slides template. The output is not perfect. It still needs a ten-minute human review to check for anomalies and add the commentary in the "key observations" section. But the process that used to take two hours now takes twenty minutes.

The investment in setting this up was probably forty hours across one month. It has already paid that back.

The unexpected benefit

When I cut the report production time from two hours to twenty minutes, I had a genuine question: what do I do with the rest of the time? The obvious answer was strategy and execution. But it also prompted a harder question: if most clients only read two sections of a five-section report, why does the report have five sections?

We stripped back the template. Current format: executive summary in three bullet points, organic traffic trend, top five ranking movements, what we did, what we are doing next. One page, no more. We tested this with three clients for two months before rolling it out. Not one asked where the keyword ranking table went. Two said the new format was easier to share with their teams.

The automation forced us to confront a process we had never questioned. That is probably the better outcome than the time savings.