[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 & OpenRouterWelcome 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 FarIf 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 datasetPart 2 – Using Google Analytics & Search Console: We covered how to pull data from Google Analytics and Search Console, using the Tidyverse and other packagesPart 3 – Visualisation: In part 3, we looked at how to create various charts and tables, using GGPlot2 from the Tidyverse packagePart 4 – Functions: This is where we started to bring everything together, using functions to make our work reproducible and more efficientPart 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 RPart 6 – APIs: We looked at how to use the SERPAPI, SE Ranking and SEMRush APIs to get data directly into RPart 7 – Loops: Here, we ran through how to use various loop methods to use functions across various datasetsPart 8 – Apply Methods: Then we looked at the apply family to add those into the mix, again using them across various datasetsPart 9 – Web Scraping: Before the finale, we looked at how to scrape websites using Rvest and the Polite package(function() {window.mc4wp = window.mc4wp || {listeners: [],forms: {on: function(evt, cb) {window.mc4wp.listeners.push({event : evt,callback: cb});}}}})(); Leave this field empty if you're human: The Series In NumbersClaude 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 TodayIn 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 reportGoogle Search Console data: We’ll do the same with Search Console, focusing on clicks, Impressions, Average Position and CTRSEMRush Visibility data: We’ll pull the visibility numbers for our target domain, using the SEMRush APIAutomate commentary with OpenRouter: We had to get AI into this series somewhere, so we’ll use the OpenRouter API to create commentary on our reportSend 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 templateSounds 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 RSince 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 RemotesFirstly, 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 RemovalSince 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 ProjectIf 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 RNow 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 RWe 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