Automation · Reporting format via GA & GSC MCP

A whole team's SEO reports, from one Sheet.

Each client's monthly report used to take a specialist most of a working day. I built a Google Sheet with its own menu: pick a client and a month, and it pulls Search Console and GA4 data through MCP, splits branded from non-branded traffic, measures each site section, compares the periods and writes the insights. The analyst only reviews it.

Search Console MCPGA4 MCPGoogle Apps ScriptGoogle SheetsClaude / Gemini
~1 dayper client report, built by hand
1–2 minfirst build of a new client report
< 1 minto refresh it every month after that
0servers to host; it runs inside Google Sheets
Inside the tool

What the team actually sees.

The sidebar where a report is set up, then each tab of the finished client Sheet. Swipe or use the arrows.

1 · SidebarEverything is picked from dropdowns. Saved settings load when a client is chosen.
2 · Insights tabWritten by AI from the summary numbers, then reviewed by the analyst.
3 · GSC tabWeb and Image Search, branded vs non-branded, month over month and year over year.
4 · GA4 tabOrganic Search users, new users, sessions and revenue.
5 · Segment tabOne tab per URL group, with its share of total clicks.
6 · Run LogWho generated which report, when, how long it took, and a link to it.

Client names and figures are anonymised and scaled. The layout, tabs and formatting are the same as the live tool.

The problem

Same report, every client, every month.

Every client needed the same pack: Search Console clicks and impressions, branded against non-branded, image search, GA4 organic performance, a breakdown for product, category and brand pages, month-over-month and year-over-year, and a written summary. Done by hand it took most of a day per client. And because it was the same steps each time, small copy-paste mistakes crept in.

The flow

Eight steps, one click.

Each box is one step. Press Run workflow to watch a report being built, or click any step to see what goes in and what comes out. Switch to a monthly refresh to see how much less data is fetched.

Scroll sideways to see the whole flow →

Step 1

≈ 66MCP requests on a first build (Mar–Sep, plus Jun–Sep last year)
≈ 12on a monthly refresh (the new month and its year-ago match)
≈ 6to re-run the same month and pick up late Search Console data
Time per report, drawn to scale

From a working day to a minute.

By handmost of a working day · ~8 h
First build1–2 minutes
Monthly refreshunder a minute

Bars are drawn against an 8-hour day. Generation time only; the analyst then reviews the report before it goes to the client.

Design decisions

Built for a team, not one person.

speed

Only fetch what's new

Past months are stored in a hidden tab and never fetched again. A monthly refresh pulls just the new month and its year-ago match.

accuracy

Totals match the GSC UI

Non-branded is Total minus Branded, so anonymised queries stay in the numbers the same way Search Console counts them.

team

Regex kept in Sheets

Brand and segment patterns live in admin tabs. The team only ever sees names, and anyone can add a brand or section without code.

access

No personal GSC access

Every property is served through one MCP connection, read-only. Keys sit in script settings, never in a sheet.

When something breaks

It fails politely.

ga4

GA4 never blocks a report

If the GA4 property can't be reached, the Search Console tabs still build.

ai

No single AI provider

Insights use Claude or Gemini, whichever key is set, and fall back to rule-based summaries if the AI is busy.

mcp

A connection test

One menu item lists every MCP tool, runs six sample calls and shows a plain-English error if a reply format changes.

audit

Run log

Every run records who generated which report, when, and a link to it.

What changed

Reporting became a review, not a chore.

Every analyst builds the report the same way, in the same format, from the same saved client settings. New clients take about two minutes to set up, and the day each report used to take goes back into SEO work.