You can automate an entire weekly SEO report with two Google APIs, one AI agent, and a single scheduled job. Once Google Search Console is connected through Google Cloud Console, Hermes writes its own Python script, pulls live search data, builds a color coded Excel workbook, mirrors it into Google Sheets, and emails you the summary every Monday morning.
No dashboard clicking. No manual exports. No pasting screenshots into a client deck late on a Sunday night.
This build runs on GLM 5.3 set to max inside Hermes, though the same setup works with whatever agentic model you prefer. Here is exactly how to build it.
Core Concepts
- Connecting the Google Search Console API and Google Cloud Console so an agent can pull live search data.
- Why queries sitting in positions 10 through 20 are the fastest content wins in your account.
- Turning a one time data pull into a repeating skill that runs on a cron job.
- Using a planning mode skill so the agent proposes scope before it takes action.
- Why a dedicated project folder keeps every report landing in a predictable place.
- What the finished deliverable looks like across email, Excel, and Google Sheets.
- How to catch and fix a reporting gap on the first run.
Who does this apply to
Marketers, SEO leads, agency account managers, and business owners who already have Search Console data and are tired of rebuilding the same report by hand every week. Manage two sites and this saves a few hours a month. Manage ten and it changes how your Monday starts.
What This Automation Actually Delivers
The finished system runs on a schedule and produces three things at once. An email lands in your inbox with headline numbers, a short wins list, a short fixes list, the top 10 search queries, and new post ideas. An Excel workbook arrives attached to that email with color coordinated tabs for every data cut. A matching Google Sheet gets written into Drive so a team or a client can open it without downloading anything.
Here is the tab structure I asked for.
| Workbook tab | What it answers |
|---|---|
| Summary | Clicks, impressions, and average position with a comparison against the prior 28 days |
| Top 5 blog pages | Which posts are actually carrying organic traffic |
| Top regular pages | Which non blog pages earn impressions |
| Bottom 5 regular pages | Which pages need new copy, meta titles, and descriptions |
| Top 10 queries | How people find the site overall |
| Queries 10 through 20 | Mid range terms with the most upside |
| Per page queries | How people find one specific page |
| Page improvement ideas | Recommendations tied to the weakest pages |
| New blog ideas | Topics pulled from real query demand |
The per page query tab is the one worth protecting. Site wide queries tell you how people find your brand. Per page queries tell you what someone typed before landing on your pricing page or your about page, which is the difference between a vanity report and a report you can act on.
The Two APIs You Need Before Anything Else
Two connections do all the heavy lifting. The Google Search Console API gives the agent access to the same click, impression, and position data you see in the interface through the Search Analytics query endpoint. Google Cloud Console is where you generate OAuth 2.0 credentials for API access so the agent can authenticate as you.
Budget about an hour for the Cloud Console setup the first time. It is the least fun part of this build and the only part you do once. If you get stuck, ask Hermes or any AI assistant to walk you through project creation, API enablement, the consent screen, and the credential download step by step.
After that, verification is one prompt. I asked Hermes to confirm the connection and it came back with a valid OAuth token and no refresh needed, which is the green light to start pulling data.
One more thing worth knowing. Once Cloud Console is wired up, Google Drive, Google Sheets, and Google Analytics are a checkbox away rather than a new project. That is why this report can write itself into a shared Sheet with no extra plumbing.
Why an Agentic Model Beats a Chat Model Here
I ran this on GLM 5.3 through the Z.AI coding plan, and the model choice matters more than usual. This is not a summarize the dashboard task. The agent writes its own Python script, executes terminal commands, calls the API, parses the response, and formats a workbook. A model that can only talk about data cannot do that.
Two practical reasons GLM 5.3 held up well. Speed, because marketing work happens in a flow state and waiting 40 seconds between steps breaks it. Context, because the window runs up to a million tokens, which means one long session can hold a full build, a QA pass, and three rounds of edits without losing the thread. I keep a Codex subscription as a fallback runtime, and for a multi step build like this the agentic coding plan was faster and smoother.
Testing the Connection With Live Queries
Start small before you automate anything. My first prompt simply asked for the top 10 search queries in chat. That confirms the pipe works and tells you something useful in the same breath. Mine came back loaded with model comparisons and Hermes walkthroughs, which lines up exactly with what I publish, so the data was clearly real rather than invented.
That two minute test is worth repeating every time you add a new property. If the top queries look nothing like your content, the connection is pointed at the wrong site.
Mid Range Queries Are Where the Next Content Ideas Live
Now change one number. Instead of the top 10 queries, ask for queries 10 through 20.
The top 10 tells you what already works. The 10 through 20 band tells you what is one improvement away from working. My mid range pull surfaced DeepSeek questions, people looking for how to use Fable 5 inside Notion, and more demand for model comparisons. Those are not guesses about what to write next. Those are people already searching and landing just short of the click.
Then I chained it. I asked the agent to pull search data for those terms and find ranking opportunities based on clicks, impressions, and average position. It wrote a script, ran it, and returned five pages that needed work in seconds. That is where an agentic model earns its keep, because the same analysis by hand is 20 minutes of filtering and sorting.
Expert tip: When you review the mid range list, prioritize pages with high impressions, a click through rate under 2 percent, and an average position between 8 and 20. Google already trusts those pages enough to show them. A stronger title, a rewritten meta description, and a tighter opening paragraph often move them further than a brand new post would.
Turning a Manual Pull Into an Automated Skill
Once the manual version works, the automation prompt is just a spec. I asked for an automated email alert and an Excel spreadsheet automation that creates a new report on a schedule, then listed every tab from the table above.
Three details in that prompt did most of the work. Asking for color coordinated tabs by query type, which makes the workbook readable at a glance. Asking for a summary tab so the top of the report answers the question before you scroll. Asking the agent to suggest the best email layout rather than dictating one, because it proposed a cleaner one screen layout than what I had in mind.
I also asked for the same report to be written into a Google Sheet. Now the data lives in three places. On my machine, in Drive for anyone who needs to see it, and in my inbox as a summary.
Add a Planning Skill So the Agent Proposes Before It Acts
Before giving the build command, I loaded a small skill I call Q Mode, short for Question Mode. It does one thing. Before taking any action, present the plan.
Hermes can do something similar through prompt snippets buried in the settings, but those are genuinely hard to find, and a named skill is easier to invoke mid conversation. Call it Q Mode, P Mode, or plan mode. The name does not matter. The behavior does.
The payoff showed up immediately. The agent laid out the script it intended to write, the workbook tabs, the Google Sheet copy, a cron schedule set to Monday at 8am, a failure alert, and a proposed email layout. Reading that plan is what made me add the top 10 queries to the email body before anything got built. Catching that in the plan cost one sentence. Catching it after the build would have cost a rewrite.
Planning mode also stayed active through the whole session, so later fixes ran as scope edits instead of surprise actions. If you like this pattern, it pairs well with the way I build a Notion content library with GLM for AI blog writing, where an approval step keeps the database clean.
Work Out of a Dedicated Project Folder
Create a folder for this project and tell the agent to work inside it. Mine is a Google Search Console folder that collects nothing but these reports.
This sounds like housekeeping and it is actually a reliability feature. The agent knows where to write output, it has a consistent reference point across sessions, and every Monday the new workbook lands in the same place next to the last twelve. When you want to move it later, Hermes updates the path in the script.
Launch Sub Agents to Run the Build in Parallel
One prompt made the build noticeably faster. I told Hermes to launch sub agents as needed to complete tasks quicker and in parallel.
It spun up a sub agent whose only job was to verify the environment and monitor the run, so if a step failed there was something watching for it rather than a silent error at the end. Environment check first, then script execution, then delivery. Think of it as giving the main agent a friend to watch the process.
The Cron Job Is the Actual Product
Everything up to here is a nice one time analysis. The cron job is what turns it into a system. Mine runs Monday at 8am with an alert if it fails, and the same pattern works daily, weekly, or every two weeks.
If you have never set one of these up inside an agent, the mechanics match what I walk through for setting up daily news brief cron jobs in Hermes. And if you are wondering what a stack of scheduled jobs actually costs to run, I broke down what a 21 job AI automation pipeline costs per day, and it lands under the price of a coffee.
What the First Report Delivered
The automated email arrived exactly as specced. Date range at the top, totals underneath, a comparison against the prior 28 days, a short wins section, a short fixes section, the top 10 search queries, and new post ideas specific enough to shoot. The Excel workbook came attached with every tab populated. The Google Sheet matched.
The per page query tab was the standout. It pairs each page with the queries that led people to it, which is the report I would normally spend half an hour assembling by hand.
Fixing the Top Pages Gap on the First Run
The first run had one real gap. The top regular pages and bottom regular pages tabs only pulled the home page.
The likely cause is that my blog absorbs most of the organic traffic, so the non blog pages had almost no clicks to rank against each other. The fix was a scope edit. Include regular pages even when clicks are low or zero, attach recommendations to them every week, and also save the workbook into the project folder rather than only sending it by email.
Treat the first run of any reporting automation as a QA pass, not a finished product. Budget one iteration. Nothing here is set in stone, and the agent will rewrite its own script or adjust the reporting logic without starting the build over.
Because planning mode was still on, that correction came back as a proposal I approved rather than a blind rewrite. That is the whole argument for keeping a plan first skill loaded on long running builds.
Manual Search Console Reporting Versus an Automated Agent Report
| Task | Manual Search Console reporting | Automated agent report |
|---|---|---|
| Time per site each week | 45 to 90 minutes | About 2 minutes to read the email |
| Data pulls | Filter and export one view at a time | One script pulls every cut at once |
| Formatting | Rebuild the sheet every week | Color coded tabs generated automatically |
| Query analysis | Manual sorting and eyeballing | Opportunities flagged by clicks, impressions, and position |
| Content ideas | Whatever you remember to look for | New post ideas pulled from live query demand |
| Per page queries | Rarely pulled because it is tedious | Included on its own tab every run |
| Delivery | Only when you remember to send it | Email, Excel attachment, and Google Sheet on schedule |
| Team access | A screenshot in Slack | A shared Google Sheet in Drive |
| Failure handling | You notice weeks later | An alert fires when the job fails |
Frequently Asked Questions
Do I need to know Python to build this?
No. The agent writes and runs the script. Your job is to describe the report you want, review the plan, and approve it. Being able to read a script helps when you want to change something, but it is not a requirement.
How long does the whole setup take?
Roughly an hour for the Google Cloud Console project and OAuth credentials, then about 20 minutes for the report spec, the plan review, and the first build. Every week after that is zero minutes.
Can I use a different model instead of GLM 5.3?
Yes. Any model with strong agentic tool use and terminal access will run this. GLM 5.3 was fast and handled the long session well, and Codex works as a fallback. The requirement is an agent that can execute scripts, not just describe them.
Does this replace Google Search Console?
No, and it should not. Search Console stays the source of truth for indexing issues, manual actions, and deep debugging. This automation removes the repetitive weekly reporting that sits on top of that data.
Can I run it across multiple client sites?
Yes. Give each property its own project folder and its own scheduled job, then keep the report spec identical so every client gets the same structure. That consistency is what makes reports comparable month over month.
The Takeaway
Weekly SEO reporting is a solved problem. With two API connections, a planning skill, a project folder, and one cron job, the report writes itself and you spend Monday morning acting on the data instead of assembling it. Start with the manual query pull, confirm the numbers look like your business, then automate the version you already trust.
If you want help wiring this up across a client roster, you can read more about how I approach marketing systems or book a call to map out your reporting automation.
About Jason Pollak
Jason Pollak is a marketing strategist with over 10 years of experience building campaigns for entertainment brands, artists, and businesses across music, film, television, eCommerce, and B2B SaaS. As Director of Marketing at Young Money Entertainment, he grew Lil Wayne’s Facebook following from 10 million to 50 million and managed over 60 million followers across the roster. He also served as Paid Media Director at Horizon Media, launching major TV shows for History Channel, A&E, WWE, and Lifetime, and led film marketing for Utopia Distribution, generating over $10 million in revenue on a $200K media spend. Jason specializes in paid media, organic social strategy, email automation, SEO, content development, and AI-driven marketing systems. He holds a BA in English Literature from Binghamton University and a Masters in Media Studies from Brooklyn College. Learn more at jasonpollakmarketing.com.