R For SEO Part 10: Build an automated SEO report with Google Sheets and OpenRouter

This final part of the R for SEO series ties together earlier topics into a repeatable SEO reporting workflow built in R. The goal is a report that combines a year of Google Analytics data (updated with the last 30 days on refresh), Search Console metrics (clicks, impressions, average position, CTR), SEMRush visibility for a target domain, and automated narrative commentary. All outputs are written to Google Sheets so a Data Studio template or other consumer can visualise them.

• Google Analytics: pull the previous 12 months and append the most recent 30 days on each refresh. This keeps a rolling dataset without re-fetching everything every run.
• Google Search Console: collect clicks, impressions, average position and CTR for the same windows as Analytics.
• SEMRush: fetch visibility numbers for the monitored domain using the SEMRush API.
• Commentary: use OpenRouter to convert metric changes into plain-language commentary and write that text back to the sheet alongside metrics.
• Output: upload all datasets and the generated commentary into a single Google Sheet ready for downstream reporting.

Practical authentication note for Search Console and Analytics

• install the remotes package and use install_github("MarkEdmondson1234/searchConsoleR").

If your workflow also pulls Google Analytics via the same auth flow, these extra steps will affect Analytics authentication too, so apply the updated process to both services.

Google Sheets is the destination for both raw data and the generated narrative. It's quick to connect to reporting tools such as Data Studio and makes the final report accessible to non-technical stakeholders. Writing both metrics and commentary into the same sheet reduces the friction of building a presentation layer.

Where this guide fits with the series

The series previously covered: R basics, pulling GA and GSC data (earlier approach), ggplot2 visualisation, writing functions, replicating Excel formulas, using APIs (SERPAPI, SE Ranking, SEMRush), loops and apply methods, and web scraping. This final piece demonstrates how to combine those skills into an automated, reproducible report that finishes the sequence of practical tutorials.

Follow Ben's code structure: install required packages (including the remotes-installed searchConsoleR), create concise functions for each data source, build and test the OpenRouter commentary step, and wire up Google Sheets export. Use Git branches for each major change so you can revert or iterate safely.