R For SEO Part 10: SEO Reporting With Google Sheets & OpenRouter

Welcome back, and we’re finally at the end of my R for SEO series, at least for now. We’ve gone through quite a lot over the course of this series, everything from the basics, to where we are today: using everything we’ve learned to build a shiny SEO report, using Google Sheets and OpenRouter in R.

If you’re new, or you’ve fallen out along the way, here’s a run-down of everything we’ve covered in the series.

The Road So Far

If you’re a fan of Supernatural, this won’t be unfamiliar, since we’re at the season end. If you’re not, you can skip the video.

Part 1 – The Basics: We looked at the basics of using R for SEO, including how to install R and RStudio, basic calculations, setting dataframes, reading & writing CSV data and subsetting a dataset
Part 2 – Using Google Analytics & Search Console: We covered how to pull data from Google Analytics and Search Console, using the Tidyverse and other packages
Part 3 – Visualisation: In part 3, we looked at how to create various charts and tables, using GGPlot2 from the Tidyverse package
Part 4 – Functions: This is where we started to bring everything together, using functions to make our work reproducible and more efficient
Part 5 – Common Excel Formulas: Part 5 was where we examined how to replicate common Excel formulae, such as IF, COUNTIF, SUMIF, various lookups & pivot tables in R
Part 6 – APIs: We looked at how to use the SERPAPI, SE Ranking and SEMRush APIs to get data directly into R
Part 7 – Loops: Here, we ran through how to use various loop methods to use functions across various datasets
Part 8 – Apply Methods: Then we looked at the apply family to add those into the mix, again using them across various datasets
Part 9 – Web Scraping: Before the finale, we looked at how to scrape websites using Rvest and the Polite package

The Series In Numbers

Claude put together a fun little infographic for me on how the series has gone.

OK, that’s enough nostalgia for what’s been a very large undertaking. I genuinely hope it’s been useful for you. It’s been fun for me, but I’m ready to wrap this up and start writing about other things, because there have been a lot of changes in the digital landscape since I started this, and I’d love to cover more of them.

It’s taken far more time than I planned due to a lot of changes in digital marketing, some shifts in my personal life and a number of equipment failures along the way, but that’s the way of things.

On to today.

What We’ll Cover Today

In today’s piece, we’re going to bring everything together and use R to build an SEO report that will cover the following:

Google Analytics data: We’re going to pull our last year’s worth of data from Google Analytics and then add our last 30 days to it every time we refresh the report
Google Search Console data: We’ll do the same with Search Console, focusing on clicks, Impressions, Average Position and CTR
SEMRush Visibility data: We’ll pull the visibility numbers for our target domain, using the SEMRush API
Automate commentary with OpenRouter: We had to get AI into this series somewhere, so we’ll use the OpenRouter API to create commentary on our report
Send all our data to Google Sheets: We’ll upload all of the data that we pull, including our AI commentary into a Google Sheet, ready for a Data Studio template

Sounds fun, right?

There will be a fair few functions and elements to our final code, so you might want to use Git to branch across those features and leverage version control. If you want to do that, I’d recommend reading up on Git from my Complete Guide to Git for Data Analysts.

OK, let’s write some R.

A Big Google Search Console And Analytics Authentication Update With R

Since I wrote Part 2, there’s been a big update with how we use the searchConsoleR package, so we’re going to need to take a few additional steps. This is going to affect how we authenticate with Google Analytics as well, so pay attention even if you’re not planning to pull Search Console data for your report.

The searchConsoleR package has been removed from CRAN and there isn’t really a full replacement yet, so we’ll need to install an older version from GitHub. That part is fine, but due to this, we’ll have to take a few extra steps to authenticate our project.

Installing SearchConsoleR From GitHub With Remotes

Firstly, we’ll need to get the old version of the package from GitHub. We’ve not really covered installing packages from GitHub too much in this series, so it’s probably as good a time as any.

Install the “remotes” package. This will enable you to install packages from GitHub.

install.packages("remotes")

library(remotes)

install_github("MarkEdmondson1234/searchConsoleR")

library(searchConsoleR)

Now we’ve got our outdated searchConsoleR package installed, we’ll need to take some additional authentication steps.

Time doesn’t permit me to build my own Search Console R package, but I might at some point.

Authenticating searchConsoleR Post-CRAN Removal

Since the package isn’t on CRAN anymore, Google tends to refer the authentication to a “general” project rather than automatically linking it to our one. As such, we’ll need to create a specific API project in our Google Cloud Console. It’s a bit of work, but it’s worth going through the process as you may encounter it again along your R journey.

Creating A Google Cloud Console Project

If you’ve been reading for a while, you may remember my Sentiment Analysis for SEO Using Google Sheets post. The authentication process is pretty similar.

First off, we need to create a Cloud Console project. If you’re using Analytics seriously, you’ll probably already have one with BigQuery. If you don’t, get one set up. For the majority of sites, it’ll be free or practically free and with how GA4 handles historic data, it’s important to keep your reports accurate. You can also use it to keep more than 16 months of Search Console data, which is handy.

Go to the Google Cloud Console and create a new project – call it something you’ll remember, like “r-seo-reporting”.

Once you’re in your new project, head to the API Library and enable both the Google Search Console API and the Google Analytics Data API. Then head over to Credentials and create an OAuth 2.0 Client ID, choosing “Desktop app” as the application type. This will give you a Client ID and Client Secret, which is what we need for R.

Since we need both googleAnalyticsR and searchConsoleR to use this new project, the easiest way to do this is to authenticate them together using the googleAuthR package, which sits underneath both.

install.packages("googleAuthR")
library(googleAuthR)

options(googleAuthR.client_id = "XXXXXXXX.apps.googleusercontent.com")

options(googleAuthR.client_secret = "XXXXXXXX")

options(googleAuthR.scopes.selected = c("https://www.googleapis.com/auth/webmasters",
                                        "https://www.googleapis.com/auth/analytics.readonly"))

gar_auth()

Replace the Client ID and Client Secret with your own from the project you just created, and don’t forget to keep the speech marks. Running gar_auth() will open a browser window asking you to authorise both scopes in one go, saving us doing this twice, which is handy given we’re about to use both APIs back to back.

Pulling GA4 Data With R

Now that’s sorted, let’s get our GA4 data into our R environment as well.

We discussed this in Part 2, but honestly, that was so long ago, we’re probably due a refresh. GA4 wasn’t the only game in town when I wrote the last piece, and there are some changes to the API that we’ll need to be aware of.

If you followed the authentication steps for Google Search Console above, you should also be authorised for Google Analytics.

Getting GA4 Accounts Lists With R

We need to find the account and property ID that we want to work with. This is a bit different to our previous piece, thanks to GA4 working differently. Fortunately, it’s not too much different, we just need to add a parameter to our original call. Our command is:

gaAccounts <- ga_account_list(type = "ga4")

This tells R that we’re looking for GA4 accounts in particular, but it will give us the following output:

You’ll see that it gives us the “propertyId” parameter, which is different from Universal Analytics.

This is what we want to work with.

Let’s create our propertyID variable.

First, run the following in your console:

gaAccounts

This will list all your accounts.

In my case, the one I want to work with is the first row of my accounts list, but you can update accordingly if yours are different.

propertyID <- gaAccounts$propertyId[1]

That will turn the target GA4 property ID into a variable, which is what we’ll need to pull our data.

Pulling GA4 Organic Data With R

Since this series is all about using R for SEO, we’ll get our data for Sessions, Pageviews and Total Users with the organic search filter. You can add any other columns that you want to with this, based on the metrics and dimensions list here.

I would add Key Events, but since I’ve not posted on the site in over a year, no one has signed up for my email list in a while (hint hint)!

I’ve set the date range for the start of last year until now, so we’ve got a good basis for the report we’ll build later on in this post. Again, you can change accordingly.

ga4Data <- ga_data(propertyId = propertyID, date_range = c("2025-01-01", "2026-03-31"),
  metrics = c("sessions", "screenPageViews", "totalUsers"),
  dimensions = c("date", "pagePath"),
  dim_filters = ga_data_filter("sessionDefaultChannelGroup" == "Organic Search"))

Here’s the most common phrase in this series again – let’s break it down:

ga4Data <-: This is our assignment. We’re telling R to create a new object called ga4Data so we can store the output of our function and use it for our analysis or visualisations later on
ga_data: This is the core function from the googleAnalyticsR package used for the GA4 Data API. If you’re used to the older google_analytics function from Part 2, this is its modern version
propertyId = propertyID: Here, we’re telling R which specific GA4 property to fetch data from. Unlike Universal Analytics which used an 8-digit “View ID”, GA4 uses a 9-digit Property ID. I’ve used our propertyID variable to keep the code clean and easy to update
date_range: The timeframe for our data. We’re passing a character vector with our start and end dates. As always, keep these in the YYYY-MM-DD format to ensure the API processes them correctly
metrics: These are our quantitative values – the “numbers” we want to report on
sessions: The number of sessions
screenPageViews: The GA4 equivalent of the old “pageviews” metric
totalUsers: The total number of unique visitors who landed on the site during our date range
dimensions: These are the attributes we use to segment our data
date: Essential for seeing our performance over time
pagePath: This gives us the URL slug (everything after the domain) so we can see which specific pages are driving our organic growth
dim_filters = ga_data_filter(: Instead of creating a complex logic gate, we use the ga_data_filter helper directly within the call. This is the “proper” way the package expects filters to be handled for the GA4 API
“sessionDefaultChannelGroup” == “Organic Search”): Finally, by quoting the dimension name, we stop R from looking for a local variable and tell it to look for a column in GA4 instead. It’s a bit more “programmer-friendly” and much less likely to break. We’re using “Organic Search” to ensure we’re getting the clean organic search traffic, skipping over the noise of organic social or video.

As always, don’t forget your closing brackets. This will pull our GA4 data for the last year, ready to import into our report.

Pulling Google Search Console Data With R

Now we’ve got our GA4 data sorted, let’s do the same thing with Search Console. Since we authenticated both packages together with googleAuthR a moment ago, this bit’s nice and quick.

Setting Up Our Search Console Site URL

First, let’s create an object for the site we want to report on.

scSiteURL <- "https://www.your-domain.com"

Replace this with your own verified property in Search Console. If you’re using a domain property rather than a URL-prefix property, you’ll need to use the “sc-domain:” format instead, like “sc-domain:your-domain.com”.

Pulling Search Console Data With R

We want the same date range as our GA4 pull, but Search Console data always lags a few days behind, so we’ll knock a few days off the end date to avoid pulling incomplete data.

gscData <- search_analytics(scSiteURL, startDate = "2025-01-01", endDate = as.character(Sys.Date() - 3),
                            searchType = "web", dimensions = c("date", "page"))

colnames(gscData) <- c("Date", "Page", "Clicks", "Impressions", "CTR", "Average Position")

Let’s break it down:

gscData <-: As always, we’re creating a new object to store our output
search_analytics(scSiteURL,: We’re calling the search_analytics function from searchConsoleR and passing in our scSiteURL object from earlier
startDate = “2025-01-01”, endDate = as.character(Sys.Date() – 3),: We’re using the same start date as our GA4 pull for consistency, but ending three days ago rather than today, since Search Console data isn’t reliable for the most recent couple of days
searchType = “web”,: We’re telling the API we want web search results, rather than image or video search
dimensions = c(“date”, “page”)): As with our GA4 pull, we’re breaking the data down by date and page, so we can match it up later on. Don’t forget your closing bracket
colnames(gscData) <- c(: Finally, we’re renaming our columns to something a bit more readable for our report, since the default column names from searchConsoleR aren’t the friendliest

Now we should have our Google Analytics and Google Search Console data in our R environment, let’s move on to getting our SEMRush visibility.

Getting SEMRush Visibility Data In R

Thankfully, this API hasn’t changed much since I wrote Part 6, so we’ll use largely the same approach. Remember to update your API key to be your own and use your own domain. We’re going to get this data out in CSV format, so we’ll be able to upload that straight to our Google Sheet.

Preparing Our SEMRush Function In R

We’re going to pull keyword visibility scores monthly over time, but if you wanted to pull the keywords you’re ranking for, it wouldn’t be too different.

First up, create a variable for your API key. You’ll find that in your SEMRush account. Copy that and do the following:

semRushAPI <- "XXXXXXXX"

Obviously replace the X’s with your API key and remember to wrap it in quotes.

We’re going to use the Domain Overview (history) parameter from the API, so we can get our keyword visibility by month for the last year. This can get expensive in API costs if we’re not careful, so we’re going to use filtering for the last twelve months and sort them by date. Then we’ll add a bit of logic to label the months, so it’s easier to put into our report.

The SEMRush Domain (History) R Function

Let’s put our function together. As always, we’ll break it down afterwards.

semRushDomainHist <- function(x, y){
apiCall <- paste("https://api.semrush.com/reports/v1/projects/0/rank_history?key=", y,
                 "&domain=", x, "&export_columns=Dt,Rk,Or,Ot,Oc,Ad,At,Ac&database=uk",
                 sep = "")

apiCall <- gsub(" ", "%20", apiCall)

semRushHist <- read.csv(apiCall, header = TRUE, sep = ";", stringsAsFactors = FALSE)

semRushHist$Dt <- as.Date(as.character(semRushHist$Dt), format = "%Y%m%d")

semRushHist <- subset(semRushHist, Dt >= Sys.Date() - 365)

semRushHist <- semRushHist[order(semRushHist$Dt),]

semRushHist$Month <- format(semRushHist$Dt, "%b %Y")

colnames(semRushHist) <- c("Date", "Rank", "Organic Keywords", "Organic Traffic", "Organic Cost",
                           "Adwords Keywords", "Adwords Traffic", "Adwords Cost", "Month")

output <- semRushHist

}

How The SEMRush Domain History Function Works

Let’s break it down like we always do:

semRushDomainHist <- function(x, y){: We’re creating our function, using x for the domain we want to check and y for our API key
apiCall <- paste(“https://api.semrush.com/reports/v1/projects/0/rank_history?key=”, y,: As with our other functions, we’re pasting our URL together, starting with the rank history endpoint and our API key
“&domain=”, x, “&export_columns=Dt,Rk,Or,Ot,Oc,Ad,At,Ac&database=uk”, sep = “”): We’re adding our domain variable and telling SEMRush which columns we want: date, rank, organic keywords, organic traffic, organic cost and the AdWords equivalents. I’ve set the database to “uk” here, but you can change it based on your target region
apiCall <- gsub(” “, “%20”, apiCall): As always, we’re replacing any spaces in our call with the URL encoding for a space
semRushHist <- read.csv(apiCall, header = TRUE, sep = “;”, stringsAsFactors = FALSE): As with our earlier SEMRush function, we’re using read.csv rather than fromJSON, since this endpoint also returns semicolon-separated data
semRushHist$Dt <- as.Date(as.character(semRushHist$Dt), format = “%Y%m%d”): SEMRush sends its dates back in a slightly awkward YYYYMMDD format, so we’re converting that into a proper R date so we can filter and sort by it
semRushHist <- subset(semRushHist, Dt >= Sys.Date() – 365): Since this endpoint can return your entire history if you let it, we’re subsetting to just the last twelve months to keep our API costs down, as I mentioned earlier
semRushHist <- semRushHist[order(semRushHist$Dt),]: We’re sorting our data by date, oldest to newest, ready for our report
semRushHist$Month <- format(semRushHist$Dt, “%b %Y”): This is our bit of labelling logic – we’re creating a new column with a nicely formatted month and year, like “Jan 2025”, which will make things much easier to read on our final report
colnames(semRushHist) <- c(: As always, we’re renaming our columns to be a bit friendlier
output <- semRushHist}: And finally, we return our completed dataframe

To run it, we just need to do the following:

semRushVisibility <- semRushDomainHist("your-domain.com", semRushAPI)

Replace “your-domain.com” with your own target domain.

And there we have it, our SEMRush visibility trend for the last year, ready to go into our report.

Now let’s get AI involved and automate some commentary on our data.

Automating Commentary With OpenRouter

I said we’d get AI into this series somewhere, and here it is. We’re going to use OpenRouter, since it gives us access to a huge range of models through a single, simple API, rather than having to sign up with each provider separately. I’m using Claude for this, since it’s rather good at writing plain English commentary, but you can point this at whichever model you prefer.

Setting Up Our OpenRouter Function In R

First, we need a couple of new packages: httr for making our API call and jsonlite for handling the JSON we’ll be sending and receiving.

install.packages("httr")
library(httr)

install.packages("jsonlite")
library(jsonlite)

Now let’s create an object for our API key, just like we have for all our other APIs today.

openRouterAPI <- "XXXXXXXX"

You’ll find your key in your OpenRouter account. Replace the X’s with your own key, and remember to keep the speech marks.

The OpenRouter Commentary Function In R

Now let’s put our function together.

openRouterCommentary <- function(x){

  requestBody <- list(
    model = "anthropic/claude-sonnet-4.5",
    messages = list(
      list(role = "system", content = "You are an SEO analyst writing a short, plain English
           commentary on a website's performance data. Keep it to two or three sentences and
           focus on the most significant trends."),
      list(role = "user", content = x)
    )
  )

  openRouterCall <- POST(
    url = "https://openrouter.ai/api/v1/chat/completions",
    add_headers(Authorization = paste("Bearer", openRouterAPI)),
    content_type_json(),
    body = toJSON(requestBody, auto_unbox = TRUE)
  )

  openRouterResponse <- content(openRouterCall, as = "parsed", simplifyVector = TRUE)

  output <- openRouterResponse$choices$message$content

}

How The OpenRouter Commentary Function Works

As always, let’s break it down:

openRouterCommentary <- function(x){: We’re creating our function with a single x variable, which will be the text summary we want commentary on
requestBody <- list(: Rather than pasting a URL together like our other APIs, OpenRouter (like most modern AI APIs) expects a JSON body, so we’re building that up as a list first
model = “anthropic/claude-sonnet-4.5”,: This is where we choose our model. OpenRouter supports a huge range, so it’s worth having a look through the documentation to see which one suits your budget and needs
messages = list(: As with most chat-based AI APIs, we need to send a list of messages
list(role = “system”, content = “You are an SEO analyst…”): Our system message, which tells the model how to behave. I’ve kept mine fairly tightly scoped so it doesn’t go off and write us an essay
list(role = “user”, content = x): Our user message, which is just our x variable, the summary text we’re passing in
openRouterCall <- POST(: We’re using the POST function from httr to send our request
add_headers(Authorization = paste(“Bearer”, openRouterAPI)),: This is our authentication, using our openRouterAPI object from earlier
content_type_json(),: We’re telling the API that we’re sending JSON
body = toJSON(requestBody, auto_unbox = TRUE)): We’re converting our requestBody list into JSON using jsonlite. The auto_unbox parameter stops jsonlite wrapping single values in unnecessary square brackets, which OpenRouter doesn’t like
openRouterResponse <- content(openRouterCall, as = “parsed”, simplifyVector = TRUE): We’re parsing the response back into an R object
output <- openRouterResponse$choices$message$content}: Finally, we’re pulling just the commentary text out of the response and returning it

Running Our OpenRouter Commentary Function

To use this, we need to give it something to comment on. Let’s build a quick summary of our GA4 sessions data to pass in.

gaSummary <- paste("Sessions over the last 30 days totalled", sum(tail(ga4Data$sessions, 30)),
                   "compared to", sum(tail(ga4Data$sessions, 60)) - sum(tail(ga4Data$sessions, 30)),
                   "in the previous 30 days.", sep = " ")

gaCommentary <- openRouterCommentary(gaSummary)

Run that, and typing gaCommentary into your console should give you a couple of sentences of plain English commentary on your GA4 performance, ready to drop straight into your report.

You could easily repeat this same pattern for your Search Console and SEMRush data too, just by building a different summary string and passing it through the same function. That’s the beauty of writing it as a function in the first place.

Now, let’s bring everything together and send it all to Google Sheets.

We’re on the home stretch now. We’ve got our GA4 data, our Search Console data, our SEMRush visibility and our AI-generated commentary all sitting in our R environment. Let’s get it all into a Google Sheet, ready for a Data Studio template.

Installing And Authenticating Googlesheets4

First, we need to install the Googlesheets4 package.

install.packages("googlesheets4")

library(googlesheets4)

gs4_auth()

As with our other Google authentications today, this will open a browser window asking you to authorise access to your Google account. Since googlesheets4 uses the same underlying authentication as the rest of the Tidyverse’s Google packages, this should feel very familiar by now.

Creating Our Report Sheet In R

Now let’s create a new Google Sheet and send each of our datasets to its own tab.

reportSheet <- gs4_create("SEO Report", sheets = list("GA4" = ga4Data, "GSC" = gscData,
                                                       "SEMRush" = semRushVisibility))

sheet_write(data.frame(Commentary = gaCommentary), ss = reportSheet, sheet = "Commentary")

Let’s break it down:

reportSheet <-: We’re creating an object to store the reference to our new Google Sheet, which we’ll need for our next command
gs4_create(“SEO Report”,: We’re using the gs4_create function to create a brand new Sheet, called “SEO Report”. If you’re updating an existing report rather than creating a new one each time, you’d use gs4_get with your Sheet’s ID instead
sheets = list(“GA4” = ga4Data, “GSC” = gscData, “SEMRush” = semRushVisibility)): This is the handy bit – we can pass a named list of dataframes directly into gs4_create, and it’ll create a tab for each one, named after the list item. Don’t forget your closing brackets
sheet_write(data.frame(Commentary = gaCommentary), ss = reportSheet, sheet = “Commentary”): Since our commentary is just a single piece of text rather than a dataframe, we wrap it in data.frame() first, then use sheet_write to add it as its own tab on our existing reportSheet object

And that’s it. Every time you run this script, you’ll get a fresh Google Sheet with a full year of GA4 and Search Console data, your latest SEMRush visibility trend and some AI commentary to help explain what’s going on, all ready to plug into a Data Studio template.

Wrapping Up

And that, for now at least, is the end of my R for SEO series. We’ve gone from installing R for the very first time in part one, all the way through to building a fully automated SEO report with AI commentary baked in. I hope you’ve enjoyed the journey and, more importantly, that you’re using at least some of this in your own work by now.

As always, if you build something interesting with any of this, or if you get stuck, let me know on LinkedIN or BlueSky. And if you want to be kept up to date with whatever I write about next, sign up for my email list.

Thanks for reading and I’ll see you next time. I’ve got something cool coming up, so you’ll want to be checking in. I promise it won’t take so long, next time!

Our Code From Today

# Authenticate GA4 And Search Console Together

install.packages("googleAuthR")
library(googleAuthR)

options(googleAuthR.client_id = "XXXXXXXX.apps.googleusercontent.com")
options(googleAuthR.client_secret = "XXXXXXXX")
options(googleAuthR.scopes.selected = c("https://www.googleapis.com/auth/webmasters",
                                        "https://www.googleapis.com/auth/analytics.readonly"))

gar_auth()

# GA4 Data

install.packages("remotes")
library(remotes)

install_github("MarkEdmondson1234/searchConsoleR")
library(searchConsoleR)

gaAccounts <- ga_account_list(type = "ga4")

propertyID <- gaAccounts$propertyId[1]

ga4Data <- ga_data(propertyId = propertyID, date_range = c("2025-01-01", "2026-03-31"),
                   metrics = c("sessions", "screenPageViews", "totalUsers"),
                   dimensions = c("date", "pagePath"),
                   dim_filters = ga_data_filter("sessionDefaultChannelGroup" == "Organic Search"))

# Search Console Data

scSiteURL <- "https://www.your-domain.com"

gscData <- search_analytics(scSiteURL, startDate = "2025-01-01", endDate = as.character(Sys.Date() - 3),
                            searchType = "web", dimensions = c("date", "page"))

colnames(gscData) <- c("Date", "Page", "Clicks", "Impressions", "CTR", "Average Position")

# SEMRush Visibility History

semRushAPI <- "XXXXXXXX"

semRushDomainHist <- function(x, y){

  apiCall <- paste("https://api.semrush.com/reports/v1/projects/0/rank_history?key=", y,
                   "&domain=", x, "&export_columns=Dt,Rk,Or,Ot,Oc,Ad,At,Ac&database=uk",
                   sep = "")

  apiCall <- gsub(" ", "%20", apiCall)

  semRushHist <- read.csv(apiCall, header = TRUE, sep = ";", stringsAsFactors = FALSE)

  semRushHist$Dt <- as.Date(as.character(semRushHist$Dt), format = "%Y%m%d")

  semRushHist <- subset(semRushHist, Dt >= Sys.Date() - 365)

  semRushHist <- semRushHist[order(semRushHist$Dt),]

  semRushHist$Month <- format(semRushHist$Dt, "%b %Y")

  colnames(semRushHist) <- c("Date", "Rank", "Organic Keywords", "Organic Traffic", "Organic Cost",
                             "Adwords Keywords", "Adwords Traffic", "Adwords Cost", "Month")

  output <- semRushHist

}

semRushVisibility <- semRushDomainHist("your-domain.com", semRushAPI)

# OpenRouter Commentary

install.packages("httr")
library(httr)

install.packages("jsonlite")
library(jsonlite)

openRouterAPI <- "XXXXXXXX"

openRouterCommentary <- function(x){

  requestBody <- list(
    model = "anthropic/claude-sonnet-4.5",
    messages = list(
      list(role = "system", content = "You are an SEO analyst writing a short, plain English
           commentary on a website's performance data. Keep it to two or three sentences and
           focus on the most significant trends."),
      list(role = "user", content = x)
    )
  )

  openRouterCall <- POST(
    url = "https://openrouter.ai/api/v1/chat/completions",
    add_headers(Authorization = paste("Bearer", openRouterAPI)),
    content_type_json(),
    body = toJSON(requestBody, auto_unbox = TRUE)
  )

  openRouterResponse <- content(openRouterCall, as = "parsed", simplifyVector = TRUE)

  output <- openRouterResponse$choices$message$content

}

gaSummary <- paste("Sessions over the last 30 days totalled", sum(tail(ga4Data$sessions, 30)),
                   "compared to", sum(tail(ga4Data$sessions, 60)) - sum(tail(ga4Data$sessions, 30)),
                   "in the previous 30 days.", sep = " ")

gaCommentary <- openRouterCommentary(gaSummary)

# Send Everything To Google Sheets

install.packages("googlesheets4")
library(googlesheets4)

gs4_auth()

reportSheet <- gs4_create("SEO Report", sheets = list("GA4" = ga4Data, "GSC" = gscData,
                                                       "SEMRush" = semRushVisibility))

sheet_write(data.frame(Commentary = gaCommentary), ss = reportSheet, sheet = "Commentary")

R For SEO Part 10: SEO Reporting With Google Sheets & OpenRouter

The Road So Far

The Series In Numbers

What We’ll Cover Today

A Big Google Search Console And Analytics Authentication Update With R

Installing SearchConsoleR From GitHub With Remotes

Authenticating searchConsoleR Post-CRAN Removal

Creating A Google Cloud Console Project

Pulling GA4 Data With R

Getting GA4 Accounts Lists With R

Pulling GA4 Organic Data With R

Pulling Google Search Console Data With R

Setting Up Our Search Console Site URL

Pulling Search Console Data With R

Getting SEMRush Visibility Data In R

Preparing Our SEMRush Function In R

The SEMRush Domain (History) R Function

How The SEMRush Domain History Function Works

Automating Commentary With OpenRouter

Setting Up Our OpenRouter Function In R

The OpenRouter Commentary Function In R

How The OpenRouter Commentary Function Works

Running Our OpenRouter Commentary Function

Installing And Authenticating Googlesheets4

Creating Our Report Sheet In R

Wrapping Up

Our Code From Today

Ben Johnston

Pages

Newsletter

The Road So Far

The Series In Numbers

What We’ll Cover Today

A Big Google Search Console And Analytics Authentication Update With R

Installing SearchConsoleR From GitHub With Remotes

Authenticating searchConsoleR Post-CRAN Removal

Creating A Google Cloud Console Project

Pulling GA4 Data With R

Getting GA4 Accounts Lists With R

Pulling GA4 Organic Data With R

Pulling Google Search Console Data With R

Setting Up Our Search Console Site URL

Pulling Search Console Data With R

Getting SEMRush Visibility Data In R

Preparing Our SEMRush Function In R

The SEMRush Domain (History) R Function

How The SEMRush Domain History Function Works

Automating Commentary With OpenRouter

Setting Up Our OpenRouter Function In R

The OpenRouter Commentary Function In R

How The OpenRouter Commentary Function Works

Running Our OpenRouter Commentary Function

Installing And Authenticating Googlesheets4

Creating Our Report Sheet In R

Wrapping Up

Our Code From Today

Related Posts

Ben Johnston

Pages

Newsletter

Follow Me