R-bloggers

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

Ben Johnston
[This article was first published on R | Ben Johnston, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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.

< !-- Mailchimp for WordPress v4.14.0 - https://wordpress.org/plugins/mailchimp-for-wp/ -->

< !-- / Mailchimp for WordPress Plugin -->

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:

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:

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:

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:

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:

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:

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!

< !-- Mailchimp for WordPress v4.14.0 - https://wordpress.org/plugins/mailchimp-for-wp/ -->

< !-- / Mailchimp for WordPress Plugin -->

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")

This post was written by Ben Johnston on Ben Johnston

To leave a comment for the author, please follow the link and comment on their blog: R | Ben Johnston.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Exit mobile version