Hello, and welcome back. We’re (finally) in the home stretch of our R for SEO series with part nine, where we’re talking about scraping the web using R, particularly using the rvest package.

Today, we’re going to discuss the rvest package, look at the different scraping methods available, pull data from multiple pages and then look at how we can do so without bringing our target sites down. This is going to be fairly important for our final piece, so it’s worth paying attention today.

As always, this is a long piece, so feel free to use the table of contents below and do please sign up for my email list, where you’ll get updates of fresh content for free.

The Rvest Package

The rvest package for R – another Hadley Wickham creation – is the most commonly used web scraping package for the R language, and it’s easy to see why. It brings a lot of the Tidyverse’s tidy data outputs and notation functionality to what can be a complex element of data analysis and SEO. I’m a fan.

It’s not included in the Tidyverse package, so you’ll need to install it separately. You can do that like so:

install.packages("rvest")

library(rvest)

This will install the rvest package and get it initialised. It’s also worth installing the Tidyverse as always.

library(tidyverse)

Now we have rvest installed, we can get scraping. But how do we find what to scrape from a page? That’s where we start needing to understand a bit about how scraping works and how to identify our data points.

XPath & CSS Selectors

XPath and CSS selectors are the most widely used ways of identifying elements on a page, and are both crucial to understand when it comes to scraping the web.

Personally, since I’m a little bit more old-school, I tend to default to XPath, but that’s not to say CSS selectors aren’t brilliant – they certainly help you write much more efficient code.

Let’s look at the differences between the two.

What Is XPath?

XPath – or XML Path Language – is a syntax used for selecting nodes in a document. Think of it like a way to pinpoint specific parts of an XML tree structure, like finding particular branches or leaves. XPath has been around a long time and is very powerful, and rvest has great support for it.

There are a number of ways that you can find the relevant XPath query that you’ll need to scrape your chosen elements from your pages, and we’ll cover that very shortly.

What Are CSS Selectors?

CSS selectors are the visual language of the web, used to style and structure web pages, but they can do much more than just create pretty sites. In R, you can leverage CSS selectors to extract the almost any data you need from web pages.

Again, rvest has a lot of native support for CSS selectors, and they tend to be a lot more efficient to write queries with, as well as needing less debugging. Both methods are completely valid and, truthfully, while I tend to default to XPath, CSS selectors are finding their way into my work a lot more due to having to write less code. But what are the key differences?

XPath Vs CSS Selectors

Using Vs is a bit of a misnomer here – it’s not a fight, because they’re both worth using, but it’s worth understanding a little bit more about the differences between the two and when to use them.

In general terms – there are always exceptions – CSS selectors are:

• Easier & more efficient to write: A CSS selector is much shorter than an XPath query, and usually more intuitive, especially if you’ve been doing a lot of technical SEO work over your career
• Faster to run: Especially if you’re using browser-native CSS, which can make large-scale scraping projects much quicker
• Primarily designed for HTML pages: They’re not great at navigating up the DOM tree, and they’re not always so good for specific text elements

Conversely, XPath tends to be:

• More powerful: You can do very complex queries with XPath, which can allow you to navigate the DOM tree in both directions, as well as scraping elements from parts of the site that don’t have CSS attached – more on that shortly
• More complex: Due to the power, an XPath query will generally be longer and more complex to write and maintain – but don’t let that put you off

So now we know the two key methods we’re going to be using to identify the elements we’ll be scraping today, let’s talk about how to find them.

Finding CSS Selectors & XPath Queries

Obviously, it’s all well and good saying you want to use R to scrape a certain part of a page, such as the H1 or image alt text, but how do you actually find the elements you need? How do you identify the CSS selector without spending ages digging through the code and how do you go about putting an XPath query together?

Fortunately, there are a few very quick and easy ways to do that using Chrome. Let’s take a look at a couple of them.

The SelectorGadget Chrome Extension

SelectorGadget is a handy, free Chrome extension that I use a lot. It’s very quick and easy to use and can help you find your CSS selector or XPath at the click of a button. It’s not perfect – few things are – but I find it hits more than it misses.

Install it into your Chrome browser and then go to the page you want to scrape an element from. Let’s look at my last post in this series.

Now let’s say we want to scrape the article title.

Click the SelectorGadget extension and hover over the article title like so:

You’ll see that it’s highlighted the selector.

If you look into the SelectorGadget bar and click the title, it’ll put the following in there:

And there we go, we have our CSS selector.

Told you it was easy.

Now if you click on the XPath button to the right of the extension, like so:

You’ll get a popup with the XPath query you need.

Again, these aren’t always perfect and sometimes won’t work the way they should, but they’ll give you a good starting point.

But what if, for some reason, you can’t install the extension, or you find it doesn’t work the way it should on certain sites? Fortunately, there’s another option that’s built right into Chrome and most other browsers.

Finding CSS Selectors & XPath Queries With Chrome Developer Tools

I’m sure if you’ve been working in SEO for a while, you’ve become very familiar with Chrome or other browsers’ Developer Tools window. I don’t think I go a day without it, but did you know that you can also use it to find the CSS Selectors or create an XPath query based on a specific element? You probably did, but let’s talk through it anyway.

Personally, I tend to use this more when I’m trying to build a scraper for an area that either doesn’t have CSS to it, such as something in the head, or when something stops SelectorGadget working effectively, but it’s definitely worth learning how to use it to help you as you get more familiar with scraping in R.

Go back to the page we discussed in the last section. Now highlight a part of the article title and right-click. Now click “Inspect”, like so:

This will bring up the familiar “Elements” window:

Now if we right click on the element we care about – our article title, in this case, you’ll see the following dialogue. If you hover over “Copy”, it’ll pop out with a few very handy options:

You can copy your CSS selector or your XPath query directly from here, ready to paste in your code. I told you it was handy!

Again, it’s also not perfect, but between developer tools and SelectorGadget, I can pretty much always find the right element notation for whatever I’m trying to scrape.

I’m conscious that we’re getting scarily close to 1,500 words and we haven’t written a single line of actual code yet! Let’s fix that by using R to scrape the article title with the CSS selector.

Scraping Article Titles With R & CSS Selectors

One of the things that is always worth remembering about using CSS selectors is that they will typically differ between websites, due to the fact that CSS styling tends to be individual to the site in question. That’s why tools like SelectorGadget or Chrome Dev Tools are so useful. Let’s write our first scraper using R’s rvest package to scrape the article title from my last post.

First, create an object of the URL to that post, like so:

scrapeURL <- "https://www.ben-johnston.co.uk/r-for-seo-part-8-apply-methods-in-r/"

Now we want to find our CSS selector. Do that with SelectorGadget, and our selector will be as follows:

.entry-title

Alright, we’re ready. Let’s create a very simple scraping call:

articleTitle <- read_html(scrapeURL) %>% html_element(".entry-title") %>% 
  html_text()

Run that in your console, now type articleTitle and you should see the following output:

Easy, right? Let’s take a look at how it works.

Our First Scraper Command

Rvest was created by the same brain behind the tidyverse (you may have guessed that I’m a fan of Mr Wickham’s work by now), and you can see certain similarities in other commands that we’ve written throughout this series.

As always, let’s break it down:

• articleTitle <-: We’re giving our object name the incredibly inventive name of “articleTitle”
• read_html(scrapeURL): Now we’re calling the read_html() function from rvest to download the html of our article that we put into scrapeURL
• %>%: We’re using the tidyverse’s pipe parameter to create multiple commands in one – you’ll have seen me use this throughout this series, but it’s always good to remind ourselves
• html_element(“.entry-title”): We’re telling rvest that we want to look specifically for the element (our CSS selector in this case) “.entry-title”
• %>% html_text(): Finally, we’re chaining in the html_text() command to only show us the text from the element we’ve selected

And there you have it – we’ve built our very first scraper. Now let’s look at how we can use XPath from R’s rvest package to scrape different elements.

Scraping Meta Titles With R & XPath

Now let’s look at how we can use XPath with R and the rvest package to pull the meta title from the same page. This is a nice, simple command, but hopefully it’ll give you an idea of how the power of XPath can be added to your web scraping work.

As before, we’re going to use my last post as the target page, so our scrapeURL object is still valid.

Since we’re going for the meta title in this part rather than something visible on the page, SelectorGadget isn’t going to help us. Fortunately, we know how to use Chrome Dev Tools to find this.

Go to your target URL and right click anywhere on the page to bring up the elements window. Now find and expand the <head> element.

We want to focus on the <title> element here, so find that and right click on it, like so:

Select “Copy XPath” and paste it somewhere.

Now we want to use the following command:

articleMetaTitle <- read_html(scrapeURL) %>% html_element(, "//title") %>%
  html_text()

Not too dissimilar to our previous scraping command, is it? But there are a couple of key differences, which we’ll break down shortly.

After this runs, type articleMetaTitle into your console, and you should see the following:

And there we have it – our meta title pulled into our R environment. As always, let’s investigate the command.

Our Meta Title Scraping Command Broken Down

As you can see, using XPath on elements isn’t too dissimilar to CSS selectors when we’re using R to scrape the web – albeit, I’ve used the simplest XPath command I could have for this example, but hopefully you’re starting to see how this can be used for your SEO work.

Let’s break this command down:

• read_html(scrapeURL) %>%: As before, we’ve created our articleMetaTitle object and used read_html on our scrapeURL page object and then used the pipe command to link our command up with the next
• html_element(, “//title”) %>%: This is where the key difference between using XPath and CSS selectors comes in – the comma. The comma tells R that we’re not using a CSS selector, but rather XPath. In this case, we’re using a very basic XPath command to scrape the title element and then we’re chaining to our next command
• html_text(): As before, we’re using html_text() to only show us the text of our scraped data

This is quite easy, right? Building scrapers in R isn’t actually as complicated as it sounds, but obviously all we’ve done so far is pull one element at a time from a specific page.

Now let’s look at how we can pull multiple elements from a page.

Scraping Titles, Descriptions & H1s From A Page Using R

Obviously, when we think about scraping the web, and using a programming language like R to do it, we’re thinking about scraping more than one thing at a time and getting them to a useful dataframe. So far, we’ve looked at using specific elements, but the point is scale – so now, let’s take a look at how we can scrape multiple elements from a page using R and rvest.

We’re going to build a function to scrape the meta title, meta description and the H1 from my last post. After that, we’ll look at scraping multiple elements from multiple pages.

Firstly, we need to get our elements. You can do that with a combination of Chrome Dev Tools and SelectorGadget, and with the power of regular expressions, we can end up with the following list of elements:

• //title: The meta title XPath
• “meta[name=’description’]”), “content”: The attribute for the meta description
• .entry-title: The selector for gathering the article title, the H1

Now we’ve got our list of elements, let’s get to work on our function. It’ll look a little something like this:

pageScrape <- function(x){
  
  pageContent <- read_html(x)
  
  metaTitle <- html_element(pageContent,, "//title") %>% html_text()
  
  metaDescription <- html_attr(html_element(pageContent, "meta[name='description']"), 
                               "content")
  
  heading <- html_element(pageContent, ".entry-title") %>% html_text()
  
  output <- data.frame(metaTitle, metaDescription, heading)
  
}

You can run this function like so:

pageElements <- pageScrape(scrapeURL)

And it’ll give you the following output:

Still pretty simple, right? As always, let’s break it down.

Our Multiple-Element Scraper Broken Down

As you can see, we’ve used a function to pull some different elements from the page using the various nodes we’ve identified, and it’s given us some useful SEO data. Let’s dig in to how this function works.

• pageScrape <- function(x){: We’re creating our function called pageScrape with the x variable
• pageContent <- read_html(x): Here, we’re getting the html of our target page into our environment using rvests read_html() function
• metaTitle <- html_element(pageContent,, “//title”) %>% html_text(): As we saw with our earlier meta title scraping command, we’re using XPath to pull the title with “//title” and using html_text() to just get the text
• metaDescription <- html_attr(html_element(pageContent, “meta[name=’description’]”), “content”): Meta descriptions can often be one of the most annoying parts to scrape, and they sometimes differ between sites. In this case, we’re using html_attr() instead of html_text() because the meta description is stored within the content attribute
• heading <- html_element(pageContent, “.entry-title”) %>% html_text(): We’re re-using our H1 scraper from earlier, pulling the H1 using the .entry-title CSS selector
• output <- data.frame(metaTitle, metaDescription, heading): Finally, we’re creating our output dataframe that puts the meta title, meta description and H1 into separate columns

Now let’s think about how we can run this across multiple pages.

Applying R Scrapers To Multiple Pages

If you’ve read my previous posts on using loops and apply methods in R, you’ll have an idea of how we can run this across multiple pages.

For consistency’s sake, let’s run it across all the pages on my site, since the elements will all be the same.

Firstly, we want to get all of our target URLs into an object. The easiest way to do this is to scrape my pages sitemap.

We can do that like so:

pagesSitemap <- read_html("https://www.ben-johnston.co.uk/page-sitemap.xml") %>% 
  html_elements(, "//loc") %>% html_text()

Now if we use the reduce() function from the tidyverse as we did in part 8, we can scrape all of the elements we discussed previously from all of my pages like so:

pagesElements <- reduce(lapply(pagesSitemap, pageScrape), bind_rows)

I won’t break this particular one down, as it’s covered in depth in part 8.

You’ll see a few NAs in there because featured images are included in the sitemap, but you get the idea. For a more robust method, you could subset using the methods we learned all the way back in part 1, like so:

pagesSitemap <- subset(pagesSitemap, str_detect(pagesSitemap, "wp-content") == FALSE)

Now we’ll only have the html pages. If we run our scraper again, we’ll get the following:

So now we know how to scrape multiple elements from multiple pages, let’s talk about how we do that politely.

Using The Polite R Package To Reduce Scrape Load

Scraping websites isn’t always the most popular thing with site owners. Sometimes they don’t want their content being used that way (I personally block ChatGPT for precisely that reason), and also scraping multiple pages can put a large amount of load on a server, sometimes costing them money or even making them think that their site is under attack.

The Polite package for R ensures that your scraper respects robots.txt and also helps you reduce the amount of load you’re putting on a server. I’m giving you the techniques to scrape websites with R here, but it would be remiss of me not to tell you that you should do so politely and respect the owners of the sites.

Lecture over, let’s get the Polite package installed.

install.packages("polite")

library(polite)

Using The Polite R Package’s Bow Function

The Polite package has two key functions: bow and scrape. It is very polite. Bow introduces your R environment to the server and asks permission to scrape, looking at the robots.txt file and scrape runs the scraper.

The three tenets of a polite R scraping session are defined by the authors as “seeking permission, taking slowly and never asking twice” and that’s as good a definition of scraping the web as we’ll find.

Let’s introduce ourselves to the target site using bow:

session <- bow("https://www.ben-johnston.co.uk")

If we inspect our session object now, we’ll see the following:

This has introduced us to the website and checked the robots.txt. Now let’s update our previous multiple page scraper to do so politely.

Using The Polite R Package’s Scrape Function On Multiple URLs

Now we understand about scraping politely, let’s update our previous pageScrape function to scrape our multiple URLs politely.

This isn’t overly complicated and our updated function is as follows:

politeScrape <- function(x){
  
  session <- bow(x)
  
  pageContent <- scrape(session)
  
  metaTitle <- html_element(pageContent,, "//title") %>% html_text()
  
  metaDescription <- html_attr(html_element(pageContent, "meta[name='description']"), 
                               "content")
  
  heading <- html_element(pageContent, ".entry-title") %>% 
    html_text()
  
  output <- data.frame(metaTitle, metaDescription, heading)
  
}

It’s similar to our previous scraper function, isn’t it? But there’s one key difference: rather than using read_html(), we’re using the polite package’s scrape() function and creating a new bow() for every page, ensuring that we are introducing ourselves to each page and making sure that we’re respecting robots.txt and not hitting the site too hard.

We can run it like so:

politeElements <- reduce(lapply(pagesSitemap, politeScrape), bind_rows)

And if we inspect our politeElements object in the console, we should see the following:

So there we have it, that’s how you can use R’s rvest and polite packages to scrape multiple pages while being a good internet user.

Wrapping Up

This was quite a long piece and we’re reaching the end of my R for SEO series (although there will definitely be a couple of bonus entries). I hope you’ve enjoyed today’s article on using R to scrape the web, you’ve learned to do so politely and you’re seeing some applications for using this in your SEO work.

Until next time, where I’ll be talking about how we can build a reporting dashboard with R, Google Sheets and Google Looker Studio, using everything we’ve learned throughout this series.

Our Code From Today

# Install Packages

install.packages("rvest")

library(rvest)

library(tidyverse)

# Scrape Article Title With CSS Selector

scrapeURL <- "https://www.ben-johnston.co.uk/r-for-seo-part-8-apply-methods-in-r/"

articleTitle <- read_html(scrapeURL) %>% html_element(".entry-title") %>% 
  html_text()

# Scrape Meta Titles With XPath

articleMetaTitle <- read_html(scrapeURL) %>% html_element(, "//title") %>%
  html_text()

#Scrape Multiple Elements

pageScrape <- function(x){
  
  pageContent <- read_html(x)
  
  metaTitle <- html_element(pageContent,, "//title") %>% html_text()
  
  metaDescription <- html_attr(html_element(pageContent, "meta[name='description']"), 
                               "content")
  
  heading <- html_element(pageContent, ".entry-title") %>% html_text()
  
  output <- data.frame(metaTitle, metaDescription, heading)
  
}

pageElements <- pageScrape(scrapeURL)

# Scrape Multiple Pages

pagesSitemap <- read_html("https://www.ben-johnston.co.uk/page-sitemap.xml") %>% 
  html_elements(, "//loc") %>% html_text()

pagesSitemap <- subset(pagesSitemap, str_detect(pagesSitemap, "wp-content") == FALSE)

pagesElements <- reduce(lapply(pagesSitemap, pageScrape), bind_rows)

# Scraping Politely

install.packages("polite")

library(polite)

session <- bow("https://www.ben-johnston.co.uk")

politeScrape <- function(x){
  
  session <- bow(x)
  
  pageContent <- scrape(session)
  
  metaTitle <- html_element(pageContent,, "//title") %>% html_text()
  
  metaDescription <- html_attr(html_element(pageContent, "meta[name='description']"), 
                               "content")
  
  heading <- html_element(pageContent, ".entry-title") %>% 
    html_text()
  
  output <- data.frame(metaTitle, metaDescription, heading)
  
}

politeElements <- reduce(lapply(pagesSitemap, politeScrape), bind_rows)

This post was written by Ben Johnston on Ben Johnston

Welcome back again. Now we’re at part eight of my series on R for SEO and we’ve been on quite a journey so far, haven’t we? What was originally meant to take me eight weeks has taken several years! Between a pandemic, several jobs, two house-moves, some big shakeups in my personal life and a lot of changes in the marketing ecosystem, it’s been a lot. But today, we’re in the home stretch and we’ll be talking about using the apply family in R.

Applys are a more “R-centric” way of running functions across a range of data. They’re not entirely dissimilar to loops, but tend to be a more efficient way to write them and they do come with additional functionality which can be very useful in a number of situations.

You’ll have seen some of them being used already through this series, particularly when we talked about using APIs in R. So let’s take a look at the components of the apply family and what they can be used for.

As always, this is quite a long piece, so do feel free to skip around using the table of contents below and please do sign up for my email list to keep up to date when I publish new content.

What Are Apply Commands In R?

R’s apply family essentially allows you to apply a command or function across a range of data. The clue’s in the name, right? While not dissimilar to a loop in concept, there are a number of differences that do often make them a better or simpler choice.

As with loops, there are a few different variations that we can use. Let’s look at what they are before we get started on how we can use these different R apply methods in SEO work.

The Different Kinds Of Apply Methods In R

There are four key applies in the apply family

• Apply: The most basic one. Apply runs across a column in a matrix or dataframe. I generally use this when I want to find the maximum or minimum value of a column or if I’m trying to find the number of times a term is mentioned in a text corpus
• Lapply: You’ll have seen me use this a couple of times throughout this series and, truthfully, it’s the one I use the most. Lapply applies a function across a list or vector and is great for using functions and API calls
• Sapply: Similar to lapply, and another one I use quite a bit. Sapply means “simple apply” and tries to simplify the output compared to lapply
• Mapply: Mapply means “map apply” and is great when you need to use your apply method across multiple datasets or elements

So now we know what they are, let’s look at how to apply (Ha! I’m a comedic genius!) these to SEO work.

Using R’s Apply Method To Count Keywords On Google Search Console Data

The easiest way to show the difference between loops and applys is probably to replicate our loops from the last piece using the different apply methods.

To use the basic R apply method, let’s rework what we did with our first for loop to count the number of keywords that have 20 or more impressions from our Google Search Console dataset – you can follow the steps from part 2 to get that data, or just export from Google Search Console and import it using the read.csv function from part 1.

Assuming you’ve named your dataframe gsc, use the following command:

kwCount <- sum(apply(gsc["Impressions"], 1, function(x) x >= 20))

Now if we investigate our kwCount object in the console, using

kwCount

We should see the following – your data will be different, and probably much higher than mine.

But we did that in one line, compared to the multiple lines of the loop we’d previously used, didn’t we?

As always, let’s break it down.

Our Apply Command Broken Down

Our apply command did what the loop achieved in one line rather than five. That’s interesting. Let’s see how it works.

• kwCount <-: As with all the commands we’ve used throughout the series, we’ve named our object and used <- to tell R we want to keep it and use this particular name
• sum(apply: We’re invoking the sum command that we’re well familiar with, and then we use apply to apply it to each row or column in our dataset
• gsc[“Impressions”],: This tells R that we want to use this on our gsc dataset and that we want to focus on the Impressions column
• 1: The number 1 specifies that we want to use this on rows. If we wanted it to be across columns, we’d use 2
• function(x) x >= 20)): Finally, we’re using a very simple function that we’re applying across the rows to see if the value is greater than or equal to 20

Pretty handy, right? It gives the same output as our first loop from part 7, but a lot quicker and more efficiently.

Now let’s take a look at lapply.

Using R’s Lapply On A List or Dataframe

Lapply is one of R’s more popular apply methods and one that I use much more heavily than the regular apply command. Lapply runs across a list or vector and is particularly good for using across multiple API calls or URLs, which is naturally, very handy for SEO analysis.

Let’s take a look at how we can use lapply to replicate our “list” loop from part 7, where we use it to subset our Google Search Console dataset to show data for keywords with 20 or more impressions.

Again, obviously, we’d use subset from part 1 for this most of the time, but it’s a good first example for using lapply.

kw20 <- reduce(lapply(seq_along(gsc$Impressions), function(x) if (gsc$Impressions[x] >= 20) gsc[x, ] else NULL), bind_rows)

Here, we’re using the reduce function from the tidyverse to cover do.call(rbind in less code.

This will give you a dataframe called kw20 which only includes keywords, impressions, clicks and CTR where the impressions are 20 or more.

Run that in your console and now, as always, let’s break it down.

Our Lapply Command Broken Down

Again, we’ve replicated our loop in a smaller amount of code using an apply method – lapply in this case – than we used with the loop.

Hopefully you’re starting to see the power of the apply family for writing less code but getting the same results. We’ve trimmed it down even further by using the Tidyverse’s reduce function. You’re probably seeing why I never start R without the Tidyverse.

Let’s take a look at how it works:

• kw20 <- reduce(lapply(: We’re creating an object called kw20 and invoking the Tidyverse’s reduce command before calling lapply. Reduce allows us to bind the output for each row into our output
• seq_along(gsc$Impressions: As with our previous loop, we’re using seq_along on the Impressions column of our gsc dataset to apply our function across every row
• function(x): We’re creating our very simple function with the variable x, similar to how we’ve done all the way through this series
• if (gsc$Impressions[x] >= 20) gsc[x, ] else NULL),: If you cast your mind back to part 5 where we talked about if statements in R, this won’t be too unfamiliar. Our function is a very simple if statement, where we’re seeing if our Impressions value in the specific row of the apply is greater than or equal to 20. If it’s not, the else is returning NULL, which means nothing
• bind_rows): Finally, we’re using dplyr from the Tidyverse’s bind_rows command to add the column values to the next row in our output dataset

So there we have it – a really simple lapply command to run a simple function across multiple rows of a dataset.

Now let’s look at sapply and how it’s similar, but also a little different.

Using R’s Sapply On A List or Dataframe

Sapply – meaning “Simple apply” can work very similarly to lapply in a lot of instances, but it is focused on simplifying the output into a dataframe or vector, rather than a list. I find that when I use reduce and bind_rows from the Tidyverse, I get better results from lapply than sapply.

Either way, let’s run through how we can do the same command that we just did on lapply using sapply instead.

kw20 <- reduce(sapply(seq_along(gsc$Impressions), 
                                function(x) if (gsc$Impressions[x] >= 20) gsc[x, ], 
                                simplify = FALSE), bind_rows)

If this runs the way it should, it’ll give you the same output in kw20 as the lapply command did, but there are some differences in the command that we want to look at.

Our Sapply Command

As you’ll see from looking at the code, the command we’ve used here is pretty much exactly the same as out lapply command, with one key difference – we have the following parameter in there:

simplify = FALSE

Since the core tenet of sapply is to “simply” apply, it tries to simplify outputs to a dataframe. Since we’re already working with a dataframe, we can use simplify = FALSE here, as we don’t need it.

However, if you’re trying to coerce a list or matrix into something a bit easier to work with, sapply is a great choice. I find myself mostly using it when I’m working across JSON outputs, but for this example, you can see that it works quite nicely in the same way our lapply method did.

I’m sure you’re seeing plenty of ways that R’s apply family can be used for SEO by now. Let’s take a look at the most complex of the apply methods now – the powerful mapply().

Using R’s Mapply Method

Mapply allows you to run apply commands across multiple dataframe elements or vectors and is really handy when you need to do this. It’s a very powerful function and allows you to do some very complex analysis work with very little code. Let’s use a very simple example to identify a difference in our click through rates against a target, using our Google Search Console data.

Firstly, if you’ve imported your Google Search Console dataset from a CSV export rather than using the API, you’ll need to do a little preparation to remove the percentage character, set the column as a number and divide it by 100. This simple function below will do that:

ctrCleanup <- function(x){
  
  x <- gsub("%", "", x)
  
  x <- as.numeric(x) 
  
  x <- x/100
  
}

You’ll have seen all of this throughout the series, but let’s break it down anyway:

• ctrCleanup <- function(x){: Our function is called ctrCleanup (catchy, right?) and has an x variable
• x <- gsub(“%”, “”, x): On x, (our CTR column), we’re running gsub to find and replace the % character with nothing. Removing it, effectively
• x <- as.numeric(x): Now we’re setting that column as a number rather than the character it was previously since we’ve removed the % character
• x <- x/100: Finally, we’re dividing it by 100 so we can get our percentage as a decimal

Run this in your console with the following command:

gsc$CTR <- ctrCleanup(gsc$CTR)

and you’ll have your CTR column as decimals, ready to work with our mapply command.

Now we want to define our target CTR. Let’s take 5%.

targetCTR <- 0.05

Now we’re prepared, let’s run our mapply command.

gsc$ctrDiff <- mapply(function(x, y) abs(x - y), gsc$CTR, targetCTR)

This will create a new column in our dataset called ctrDiff with the difference between our actual click through rate and our target.

Our Mapply Command Broken Down

As always, let’s break it down:

• gsc$ctrDiff <- mapply(: We’re creating a new column called ctrDiff in our gsc dataframe and using mapply to run our function
• function(x, y) abs(x – y): Our function uses x and y parameters (our two data elements) and uses abs to find the absolute difference between them
• gsc$CTR, targetCTR): Finally, we’re defining our x and y variables – our actual click through rate from Google Search Console and our target CTR

Running this will create a new column in gsc and will give you the difference by query from your target CTR. Hopefully it’ll give you some idea of where you need to work on with your SEO efforts. Or maybe not, but at least it gives you an idea of how mapply works!

So that’s the basics of how the different apply methods in R can work. There’s a common thread to the anatomy of them, isn’t there?

Let’s look at that now.

The Anatomy of R’s Apply Methods

In general, the apply family has a common thread of how they work – a common anatomy. You’ll find yourself working around this as you go further in your R journey, but you’ll generally look at it like so:

dataframe <- applyMethod(data, function, extra parameters)

It seems a little backwards, doesn’t it? Usually we call our function before the data. Truthfully, I don’t fully know why it’s done this way, but I suspect it’s to do with the fact that we’re defining the data to apply the function to before we start actually applying it. All programming languages have these fun little elements to them, and I think this is one of R’s.

I think this might actually be the shortest of the posts I’ve written in this series, but don’t let that be an indication of the power of R’s apply family – they are absolutely vital in R programming and an essential element in using R for SEO.

Until next time, where we’ll be talking about web scraping in R.

Our Code From Today

# Install Packages

install.packages("tidyverse")

library(tidyverse)

# Read In Data

gsc <- read.csv("Queries.csv", stringsAsFactors = FALSE)

# Apply Methods

## Apply()

kwCount <- sum(apply(gsc["Impressions"], 1, function(x) x >= 20))

## Lapply()

kw20 <- reduce(lapply(seq_along(gsc$Impressions), function(x) if 
                      (gsc$Impressions[x] >= 20) gsc[x, ] else NULL), bind_rows)

## Sapply()

kw20 <- reduce(sapply(seq_along(gsc$Impressions), 
                                function(x) if (gsc$Impressions[x] >= 20) gsc[x, ], 
                                simplify = FALSE), bind_rows)

## Mapply

ctrCleanup <- function(x){
  
  x <- gsub("%", "", x)
  
  x <- as.numeric(x) 
  
  x <- x/100
  
}

gsc$CTR <- ctrCleanup(gsc$CTR)

targetCTR <- 0.05 

gsc$ctrDiff <- mapply(function(x, y) abs(x - y), gsc$CTR, targetCTR)

This post was written by Ben Johnston on Ben Johnston

Welcome back to my R for SEO series. We’re in the home stretch now, with part seven. Today, we’re going to be looking at different ways that we can run functions or commands over a series of elements using the various kinds of loops that exist in R.

If you’ve followed along so far, or you’ve tried some experimentation of your own, you’ve probably encountered loops and applys along the way. I know early on in my R journey, it very much seemed like pot luck as to which apply I should use, or whether a loop was easier, so hopefully today’s piece will start to clear that up for you a little.

I know that most programming courses cover these elements earlier, but for me, it really didn’t click until I’d learned more about the other areas we’ve covered in this series, so that’s why I’ve placed it here.

As always, if you’ve found this useful, please give it a share on your social networks and please sign up to my free email updates to be alerted when I drop my next article.

What Is A Loop?

A loop in R is more or less what it sounds like – a command that keeps running some code until a certain condition stops it.

There are two main types that we’ll look at today: the for loop and the while loop.

Before we jump into how they work, let’s look at what the two different loops do and are used for.

• The for loop: The for loop is the most commonly used one in R and works great if you have a defined vector or dataset that you want your commands to run over, or you know how many times you want to run it
• The while loop: The while loop keeps running as long as a certain condition is met, whether it is a certain value, loop length or even timeframe. They’re very useful

The For Loop In R

If you’re familiar with other programming languages like Python, the humble for loop will be something in your arsenal, and they’re no less powerful in R.

Let’s put a really simple for loop together below, running through our Google Search Console data from the last few pieces (the tutorial is in part 2). We’re going to use this loop to count the number of keywords which have 20 or more impressions.

First we want to create an object called kwCount and we’re going to set its value to zero, like so:

kwCount <- 0

Now to create our loop:

for (val in gsc$Impressions){
  if(val >= 20)  kwCount = kwCount+1
}

OK, let’s break it down.

The For Loop In R Explained

There is a lot that you can do with loops, and this is only a really basic example, but they all follow the same general process.

• for (val in gsc$Impressions){: We’re starting our loop with “for” and saying that for every value in gsc$Impressions, to run the command within our braces. There are a number of different ways that loops can be used, and I’ll show a couple of more along the way, but the anatomy is always similar
• if(val >= 20)  kwCount = kwCount+1}: As we saw in our R if statements tutorial, our braces incorporate our commands. In this simple example, we’re using a small if statement, saying that if the value is greater than or equal to 20, to add it to our kwCount vector as a numerical value of plus 1

So as you can see, this is a very simple for loop using R. As in our R functions piece, we don’t strictly need a loop for this, but it’s a simple way to show you the anatomy.

If you now type kwCount in your console, you’ll see the total number of your Google Search Console queries that are greater than or equal to 20 impressions. In my case, you get the following:

Other For Loop Methods

In our previous example, we used a simple val in method for our for loop, but there are many others.

Personally, I find myself using loops on lists or vectors a lot more, so I find myself using some alternatives along the way. Let’s take a look at some of them:

Using For Loops On A List

Let’s use a for loop to create a subset of our Google Search Console that only incorporates rows which have 20 or more impressions. Again, a loop is overkill here, but it’s a good example.

First, we want to create our dataframe to host our data. Let’s call that kw20.

kw20 <- data.frame()

Now our for loop is as follows:

for (i in seq_along(gsc)) {
  if (gsc$Impressions[i] >= 20) {
    kw20 <- rbind(kw20, gsc[i, ])
  }
}

Fairly self-explanatory, isn’t it? But, as always, let’s break it down:

• for (i in seq_along(gsc)){: As before, we’re invoking our for loop, but there’s a key difference here. We’re using “i in seq_along(gsc)”, which means “for this value (i) in the sequence along our list of our gsc object, we want to do what is in our braces”
• if (gsc$Impressions[i] >= 20){: We’re using an if statement to see that if the value in our list we are looping to (i) is greater than or equal to 20, to do our next action in the braces
• kw20 <- rbind(kw20, gsc[i, ])}}: Let’s bring it home with our action. Our loop will take a row (i) that matches the conditions we set in the previous command and using rbind, will make it part of our kw20 frame

Obviously, if we were just looking to subset our dataset according to these conditions, we’d just use subset as we saw in part 1, and using this loop with rbind within would be very inefficient, but I hope it gives you a good example of how you can use the for loop across a list.

To see it in action, you can see my Bulk Resizing Images in R post, which features loops quite a bit.

Using For Loops On A Dataframe

Similar to our list above, let’s create a loop that subsets a dataframe if impressions are equal to or less than 20.

It’s not too dissimilar, but for the sake of this exercise, I’m going to use seq_len instead of seq_along. They’re not really very different, but it’s a more commonly-used iteration for dataframes.

kwL20 <- data.frame()

for (i in seq_len(nrow(gsc))) {
  if (gsc$Impressions[i] <= 20) {
    kwL20 <- rbind(kwL20, gsc[i, ])
  }
}

As you can see, it’s exactly the same as on our list, aside from that I’ve changed our output dataframe to kwL20 (keywords less than 20) and used seq_len(nrow for our frame. This works more or less the same as seq_along, but is a little more explicit. I’ve also set the impressions volume to be less than or equal to 20 for this exercise.

So there we have it. An introduction to the for loop in R. While these are simple examples, I hope it’s given you an idea of how they can work and be used in your SEO work. Next up, lets have a look at the while loop.

While Loops In R

Where we saw the for loop, which executes our code across every item in our dataset, the while loop is a little more steady. A while loop in R will keep executing its command all the time a condition is met, and will stop when that condition is no longer true.

While loops are great for automation. I’ve used them in the past to run real-time API data from Salesforce into my environment during a specific timeframe, for example. Again though, very simple to create and execute.

Let’s do a very simple while loop in R, looking at our Google Search Console dataset once again.

A While Loop To Find Keywords With More Than 20 Impressions

Again, this is a bit of a case of a sledgehammer to crack a nut, but hopefully this simple example will give you some ideas of where and how you can use a while loop in your day to day SEO work with R.

kw_df <- data.frame(Query = character())

index <- 1

while (index <= nrow(gsc)) {
  if (gsc$Impressions[index] >= 20) {
    kw_df <- rbind(kw_df, data.frame(Query = gsc$Top.queries[index]))
  }
  index <- index + 1
}

Again, by this point in your R journey, this might look pretty simple. But let’s break it down anyway.

The While Loop Explained

Let’s dig into how this while loop works.

• kw_df <- data.frame(Query = character()): This will be familiar to you by now, but we’re creating a new dataframe called kw_df and setting the Query column to be character
• index <- 1: We’re creating a numerical object called index to match our loop against, starting it with 1
• while (index <= nrow(gsc)) {: Now onto our loop. We’re starting with a while command rather than for, and then instructing R that while the value of our index object is lower than the number of rows in our gsc dataframe, to execute our code
• if (gsc$Impressions[index] >= 20) {: There’s our if statement again. In this case, our condition is that our Impressions value at the row number defined in our index is greater than, or equal to 20, to execute our code
• kw_df <- rbind(kw_df, data.frame(Query = gsc$Top.queries[index]))}: As with our for loop, we’re rbinding our query that matches the relevant row from our index if our if statement is true, and adding it to kw_df
• index <- index + 1}: This is the important part to keep our loop running. At the end of our loop, we add 1 to the value in our index, which will keep our while loop going – once the number in index is larger than the number of rows in our dataset, the loop will stop

That’s a very simple introduction to while loops. There’s an awful lot that you can do with these, and I’m sometimes a little guilty of using them when I should use a more elegant solution because I’m in a hurry. Try them yourself – while loops in R have a lot of applications to SEO work.

Break & Next Conditions In R Loops

The break and next conditions in loops are commands that either stop the loop dead once that condition is met or simply move to the next iteration based on the output.

I don’t generally use these too much, if I’m honest, aside from using them as a crude form of error handling if I’m in a hurry, but they’re worth knowing. Let’s take a look at the break condition within a repeat loop.

Break Conditions

Break conditions are more or less exactly what they sound like – a condition under which, a loop will break, or stop.

I don’t really use break conditions too much, largely because it’s quite rare I use repeat loops, unless they’re within a specific function and the loop is required for something. However, repeat loops are the best way to demonstrate the break condition in action.

Here’s a really simple repeat loop with a break condition that will take an object called repVal with a value of one, repeatedly printing that value and then adding 1 to the object each time. And then once we hit 10 repetitions of that loop, the break condition comes in and stops it.

Let’s have a look at the code and then we’ll break it down.

repVal <- 1

repeat{
  print(repVal)
  repVal <- repVal + 1
  
  if(repVal > 10){
    break
  }
}

If this runs properly, you’ll get the following output in your console:

Simple, right? Let’s see how it works.

The Repeat Loop & Break Condition Explained

Here’s that phrase again: let’s break it down.

• repVal <- 1: We’re creating our repVal object and assigning it a value of 1
• repeat{: This is the type of loop that we’re using, similar to the for and while loops
• print(repVal): Our loop will print the value of repVal on a continual cycle as long as our loop runs
• repVal <- repVal + 1: Now we’re saying to add 1 to our repVal object every time the loop runs
• if(repVal > 10){: There’s our if statement again. Nice and simple here, we’re just seeing if the value of repVal is greater than ten
• break}}: And finally, our break condition. Essentially, once our if statement becomes true (repVal has become greater than ten), it triggers our break condition and stops the loop

And that’s how a break condition can be used in a repeat loop. It can be used in any of the other types of loops as well, and it can be a handy way to stop a loop once a certain condition is met.

Now let’s take a look at next conditions.

Next Conditions In R Loops

The next condition is one I do use a little bit more regularly. It essentially skips to the next iteration of our command if a certain condition is not met. For example, I sometimes use it for skipping empty outputs from APIs, if there’s no data returned for a certain keyword or page, I don’t want an error, I just want it to skip to the next one.

Let’s use another simple example, with a for loop this time.

for (val in 1:10){

  if (val == 5){

    next
  }
  print(val)
}

Again, nice and simple and if you’ve followed along so far, you should be able to figure out what’s happening here, but let’s run through it anyway.

The Next Condition Explained

Shall we see how this example works?

• for (val in 1:10){: In this example, we’re going to use a for loop to cycle our commands through the values of one to ten
• if (val == 5){: You’re seeing why if statements are so fundamental to programming now, right? In this case, our if statement is checking to see if we’ve looped to a point where our val is exactly equal to five
• next{: If our if statement turns out to be true, and our val does exactly equal five, we skip to the next number in our val series
• print(val): And finally, as long as we’ve not triggered our next condition, we will see a list of the numbers in our val

If this all runs correctly, we should see the following in our R console:

So as you can see, loops in R are quite simple and give you a really good way to iterate a command over a dataset, but there’ssome controversy about when, or indeed if you should ever use them in R.

The Loop Vs Apply Debate

The word is that using loops in R is dirty code. That they’re slow, that you’re using more code than you should need, that using the apply family is just better.

Personally, as someone that came to R from learning bits of a bunch of different languages and who is doing more with Python and Julia these days, loops have always made sense to me and been something of a go-to (as you’ll see in my other R posts), but I have come to appreciate the various apply methods available as well.

In my experience and through researching, it seems that loops in R being slow is something of a fallacy. That said, you do often end up writing more code than you would with one of the apply methods. Conversely, I’ve always found loops to be very reliable, whereas I sometimes have to take a couple of extra steps to get an apply working.

Still, apply methods are great, and you’ll be using them a lot as you go through your R journey, and they’ll be the subject of my next post.

Wrapping Up

I promise, I did try to make this one a bit shorter than other pieces, but there’s what you need to know about using loops in R, covering the for loop, while loop, repeat loop and the break, jump and next conditions. Try them yourself and I hope you find them useful.

Join me in the next piece, where I’ll be covering the various apply methods that you can use.

Until next time.

Our Code From Today

# For Loop

kwCount <- 0

for (val in gsc$Impressions){
  if(val >= 20)  kwCount = kwCount+1
}

## On A List

kw20 <- data.frame()

for (i in seq_along(gsc)) {
  if (gsc$Impressions[i] >= 20) {
    kw20 <- rbind(kw20, gsc[i, ])
  }
}

## On A Dataframe

kwL20 <- data.frame()

for (i in seq_len(nrow(gsc))) {
  if (gsc$Impressions[i] <= 20) {
    kwL20 <- rbind(kwL20, gsc[i, ])
  }
}

# While Loop

kw_df <- data.frame(Query = character())

index <- 1

while (index <= nrow(gsc)) {
  if (gsc$Impressions[index] >= 20) {
    kw_df <- rbind(kw_df, data.frame(Query = gsc$Top.queries[index]))
  }
  index <- index + 1
}

# Break & Next Conditions

## Repeat Loop With Break Condition

repVal <- 1

repeat{
  print(repVal)
  repVal <- repVal + 1
  
  if(repVal > 10){
    break
  }
}

## For Loop With Jump Condition

for (val in 1:10){

  if (val == 5){

    next
  }
  print(val)
}

This post was written by Ben Johnston on Ben Johnston

Wow, we’re at part 6 of my R for SEO series. Welcome back. I really hope you’re finding this useful, and by now have started to use R in your work. Today we’re going to look at one of my favourite topics: using APIs in R.

There are, obviously, millions of APIs available, so today I will just look at a couple of my favourite SEO-specific ones which will give you a basis for using the different types. We’re going to cover SERPAPI, SEMRush and SE Ranking, how to get data out of them, how to authenticate with them and how to work with the data they give you.

Now that’s out the way, let’s get started.

What Is An API?

An API – or Application Programming Interface – is a way of getting data out of, or pushing data into, another program or application from your own program or application.

I promise you, this is nowhere near as complicated as it sounds. In fact, we’ve already used two APIs fairly extensively in this series – the Google Analytics and Search Console APIs. These came from pre-built packages, but using your own API of choice isn’t that difficult either.

Let’s take a look at how to do this.

Read The Documentation

Every API does, or should, come with extensive documentation which tells you how to create your queries and run your API calls. Sadly, since R isn’t as popular as Python in the SEO space, there is a lack of R-specific documentation for most APIs, but once you have the basics of creating your calls down, you’ll be able to work most of them out.

The Anatomy Of HTTP API Requests

In R, and most other languages, the majority of APIs are called via http requests – essentially creating a URL and downloading the content of that URL into a data frame. The outputs come in a variety of formats, but JSON is the most common, and also my preferred output.

In their most common form, a http API request, or API call, has the following core elements:

• The endpoint: The core URL that the API is hosted on
• The authentication: Usually a text string called the API key. Some platforms such as the SE Ranking API do this differently and I’ll cover that later in this article
• The query: The parameters in the URL that tell the API what data we want out of it

There are obviously different elements in every API, but I’ve found that if you think of the call in that format, it helps. Again, read the documentation.

Now we’ve got that down, let’s think about how JSON works in R.

Working With JSON In R

JSON, or JavaScript Object Notation, is a standard way for data to be exported from APIs and many other systems. It’s great, but it’s not always “tidy” when it comes to working with it in R. Due to the amount of data available and how R handles nesting of data, it can be a little challenging.

Fortunately, there are packages and conventions within R to make working with this data an easier experience.

The jsonlite Package

There are lots of JSON-related packages available on CRAN – over 60, last time I checked – but jsonlite has always served me well, so it’s the one I’m using for this series. As you go further into your R journey, you may well find a package that you prefer, and that’s fine, but I hope this gives you a grounding in using JSON APIs in R.

You can install the jsonlite package with the usual commands:

install.packages("jsonlite")

library(jsonlite)

And you can read the jsonlite documentation here.

Now that’s installed, let’s put together our first API call using SERPAPI.

Using SERPAPI In R

There are numerous search engine scraping APIs available, but SERPAPI is my favourite. It offers a fantastic amount of data from almost any geographical area for a very low cost and it works brilliantly in R. I’m a fan.

I’ve built a number of tools with SERPAPI over the last couple of years, but here’s a really simple one for our first call.

Let’s see what the SERP looks like for the term “TV Units” in London.

Building Our First SERPAPI Call In R

As I mentioned earlier, there are components to every API call, one of which being the authentication, or the API key. Fortunately, SERPAPI is very easy to sign up for and even offers a free plan of 100 calls a month, which will be more than enough for all the tutorials in this series, but if you do feel like you’ll need more calls, you won’t find many better platforms for the price.

You will need API keys to go through these tutorials as I can’t share mine, but the key focus for later pieces will be ones you can use for free, like SERPAPI. Firstly, go to this link and sign up for free.

Now you’ve signed up, let’s create an object using your API key. This is easy and can be done like so:

apiKey <- "XXXXXXXXXXXX"

Replace the X’s with your SERPAPI key, but remember to keep the speech marks.

Now we need to create variables for the rest of the API call.

Creating A SERPAPI Endpoint Object In R

We’re going to use SERPAPI as an example here, but to use most APIs in R, you’re going to need to create an endpoint object. Here’s how to do it.

serpApiEndpoint <- "https://serpapi.com/search.json?engine=google"

This a simple object that just brings the SERPAPI endpoint into our R environment. You can recreate this with most APIs that use http requests, just change the endpoint URL accordingly.

Now we have our endpoint and API key variables created, we need to put our actual query together.

Creating Our SERPAPI Query In R

We now have our endpoint and API key variables created, so now we have to create our actual query. This is the third part of an API call and this is where that documentation becomes really important. What we’re going to do here is tell SERPAPI what we want from its API.

Now we’ve got our endpoint and API key variables, let’s create another variable for our query, remembering that our goal is to see the SERP for “TV units” in the UK. Here’s how to do that.

serpApiCall <- paste(serpApiEndpoint, "&q=tv%20units", "&location=United+Kingdom&google_domain=google.co.uk", "&api_key=", serpApiKey, sep = "")

Let’s break that query parameter down.

• paste(: We’re using the paste function from base R to create a string of our various parameters
• serpApiEndpoint, “&q=tv%20units”: We’re calling the API endpoint we created earlier, invoking the Google search engine
• “&q=tv%20units”: The “&q=” parameter is adding the query to our URL – API calls are generally constructed using & to define different parameters. In this case our query is “tv units”. You’ll notice that we’ve used %20 in the query here – this is the URL encoding for a space
• &location=United+Kingdom&google_domain=google.co.uk: We’re looking at the UK version of Google
• “&api_key=”, serpApiKey: This is the parameter for the API key and in this case, rather than put the whole thing into our call, we’re using our serpApiKey object
• sep = “”): As with previous pieces, the sep parameter tells R what separator we want to use in our final pasted output. As we don’t want there to be a separator, the speech marks are empty. Don’t forget your closing bracket!

Now we’ve created our full API Query, let’s run it in our R console.

Running A SERPAPI Query In R

Now we’ve built our first SERPAPI http request, we need to call it into our R environment using the jsonlite package.

Here’s how to do that:

serpAPI1 <- fromJSON(serpApiCall, simplifyDataFrame = TRUE)

This will take a couple of seconds to run, but once it does, you’ll have the entire SERP for the keyword “TV units” based in London in your R environment.

When you run this command, you’ll see the following in your RStudio environment explorer.

Now we have our SERPAPI data in JSON format in our R environment. Let’s see how to explore that.

Examining JSON Data In R

As you’ll have seen from the above, there’s not really that much involved in getting JSON data from an API, but it’s all nested, which can make the initial exploration a little challenging as it’s a series of lists. Fortunately, the SERPAPI output is cleanly formatted and denoted, so it’s not too difficult, and it’s way better than having it in separate CSV files, the way some APIs do.

If we start by using the str() command, we’ll see the following output:

str(serpApi1)

As you can see, what we have here is a series of lists with data frames nested within them. It looks a little ugly at first, but it’s actually simple once you get to grips with it.

Essentially, we need to think of each header we found in our str command as a different data frame, so we’ll be using the $ parameter in our exploration more than once.

Let’s take a look at the organic results from our SERPAPI query:

serpAPI1$organic_results

This will give you the following output:

We can do the same with every frame within our export. At the time of writing, we can see that ikea.com had the number 10 position in London for the TV units term. We can add extra parameters to explore each column within the dataframes in the list, for example:

serpAPI1$organic_results$title

And that’s how we explore JSON data in R.

Now let’s scale our API collection.

Running Multiple JSON API Calls In R

I’ll be honest here – I’ve run tons of JSON APIs with R over the years, and I have found very few that work the same way. It very much depends on the API and the output, so always budget some time and API queries for trial and error, especially if your API charges by the request.

Now we know how to run singular SERPAPI queries in R, but we’ve just got the raw JSON data, which means we’ve got to do more work to get the information we want into a data frame. Why don’t we create a function that can dynamically create our API calls and extract the information we want into a data frame all in one go?

Here’s how.

Multiple SERPAPI Queries In R

Firstly, you’ll want a data frame with a few keywords that you’d like to check. You can either put them into a CSV file and read them in using the read.csv command, or you can create it directly in R like so:

keywords <- data.frame(keyword = c("sliding wardrobes", "bookcases", "tv units", "dining tables"))

Alright, so we’ve got out data frame (call it “keywords” to follow along). Now we need to create a function to dynamically build our API calls and to pull the ranking URLs, domains, shown title and description and the position it’s in.

Firstly, we’ll want to re-visit our domainNames function from Part 4, which will strip a URL to a domain name. The code is below:

domainNames <- function(x){
  
  strsplit(gsub("http://|https://|www\\.", "", x), "/")[[c(1, 1)]]
  
}

Now let’s look at our function.

A SERPAPI Function In R

This is a fairly simple R function, and if you’ve been following the series along, there shouldn’t be anything surprising.

serpAPIFun <- function(x){
  
  require(tidyverse)
  
  require(jsonlite)
  
  apiCall <- paste(serpApiEndpoint, "&q=", x, "&location=United+Kingdom&google_domain=google.co.uk", 
                   "&api_key=", serpApiKey, sep = "")
  
  apiCall <- gsub(" ", "%20", apiCall)
  
  serpData <- fromJSON(apiCall, simplifyDataFrame = TRUE)
  
  serpData$organic_results$domain <- domainNames(serpData$organic_results$link)
  
  output <- data.frame(serpData$search_parameters$q, serpData$organic_results$link, 
                       serpData$organic_results$title, serpData$organic_results$snippet, 
                       serpData$organic_results$domain, serpData$organic_results$position)
  
  colnames(output) <- c("Keyword", "URL", "Title", "Description", "Domain", "Position")
  
  return(output)
  
}

As always, let’s break it down:

How The Function Works

This looks a lot more complicated than it actually is.

• serpAPIFun <- function(x){: Our function is called serpAPIFun and uses an x variable. You can, obviously, call it whatever you like. I’ve simplified it for this piece and just used one variable, but you can add others
• require(): We’re adding in the packages that we need for this function. In this case, we need the tidyverse and jsonlite
• apiCall <- paste(serpApiEndpoint, “&q=”, x, &location=United+Kingdom&google_domain=google.co.uk”,  “&api_key=”, serpApiKey, sep = “”): As in the single example above, we’re pasting our API URL together, with x being our keyword from our dataframe and using the UK region. If you wanted to use different regions, this could be a y variable
• apiCall <- gsub(” “, “%20”, apiCall): As before, we’re using gsub to replace spaces with %20 so it doesn’t break our API call
• serpData <- fromJSON(apiCall, simplifyDataFrame = TRUE): Now our API call is completed, we can use jsonlite’s fromJSON function to pull the data into our serpData object
• serpData$organic_results$domain <- domainNames(serpData$organic_results$link): This will run our domainNames function on the URL and create a new column of the domain name
• output <- data.frame(: We’re creating our output dataframe, with the columns chosen above (keyword, URL, title, description,  domain, position), but you can use whichever you would like from the dataset
• colnames(output) <- c(: This uses the colnames command to rewrite the column headers in our output dataframe
• return(output): The finale of our function is to return our completed data frame

Now to run it, we need to use the following command, which will run through every API call:

serpAPI2 <- reduce(lapply(keywords$keyword, serpAPIFun), bind_rows)

And it will return the following:

Great, right? I’ll be running through the different kinds of loops and apply commands in R in the next couple of articles, but for now, that’s how you can run multiple SERPAPI queries in R.

Now let’s look at the SE Ranking API.

Using The SE Ranking API In R

SE Ranking is one of my favourite SEO tools – so much so, I chose to bring it to my team at Harvest Digital and I am also an affiliate. It’s a truly fantastic platform that offers everything a number of other tools do at a cheaper price, and you can’t fault the speed, functionality and support that the team give. Check it out from my link and tell them I sent you!

Now when it comes to the API, it’s a very full featured one, but it can be a little tricky to get some of your IDs working. So first, we want to find out all the available databases.

Let’s take a look at how we can construct a function in R to gather all our search engine IDs from the SE Ranking API and, as always, we’ll break down how it works.

Finding Search Engine IDs From The SE Ranking API In R

We’ll start with a really simple function to get all the available search engine IDs from the SE Ranking API into our R environment.

First, we want to create an object of our API key. We’ll call this seRankingAPI. Obviously you’ll need your own API key for this.

seRankingAPI <- "XXXXXXXX"

Replace the X’s with your own API key.

Now let’s put our function together.

seRankingDBs <- function(x){
  
  apiCall <- paste("https://api4.seranking.com/system/search-engines?token=", x, 
                   sep = "")
  
  databases <- fromJSON(apiCall, simplifyDataFrame = TRUE)
  
  output <- data.frame(databases)
}

Let’s see how it works.

How The SE Ranking Database Function Works

Here’s that phrase again: let’s break it down.

• seRankingDBs <- function(x){: We’re creating a function called seRankingDBs, with a single x variable
• apiCall <- paste(“https://api4.seranking.com/system/search-engines: As before, we’re constructing our API call using the paste command. You can see that we’ve used the api4.seranking.com domain and the /system/search-engines endpoint
• ?token=”, x,  sep = “”): Here, we’re adding our API key as a query string. In this case, the API key query is called “token” and we’re using the seRankingAPI object that we just created. Again, using the sep = “” parameter means there is nothing to separate the objects that we’re pasting together.That’s our URL constructed
• databases <- fromJSON(apiCall, simplifyDataFrame = TRUE): As with SERPAPI, we’re using jsonlite’s fromJSON command to download the JSON data into an object called databases
• output <- data.frame(databases)}: Finally, we’re creating our output – a dataframe of the databases in this case

Told you it was simple.

To run it, simply type:

seRankingDatabases <- seRankingDBs(seRankingAPI)

And that will give you the following output:

Now let’s create another function to get search volume on our keywords from SE Ranking in R.

Search Volume From The SE Ranking API In R

Before we get going, we’ll want to know our search engine region ID. Fortunately, we’ve already got all our databases into our R environment, so it’s not difficult to find the ID we want.

Let’s say we want to look at Google UK, the ID is 180 from our list.

Now let’s create our function.

seRankingKeywords <- function(x, y, z){
  
  apiCall <- paste("https://api4.seranking.com/system/volume?region_id=", y, 
                   "&keyword=", x, "&token=", z, sep = "")
  
  apiCall <- gsub(" ", "%20", apiCall)
  
  seRankingData <- fromJSON(apiCall, simplifyDataFrame = TRUE)
  
  seRankingData <- data.frame(x, seRankingData)
  
  colnames(seRankingData) <- c("Keyword", "Search Volume")
  
  output <- seRankingData
}

I’ve gone a little backwards here on the placement of the y and x variables, but that’ll make sense in a second.

How The SE Ranking Keyword Volume Function Works

As always, let’s break it down.

• seRankingKeywords <- function(x, y, z){: Our function is called seRankingKeywords and we’re using x, y and z variables
• apiCall <- paste(“https://api4.seranking.com/system/volume?region_id=”, y, “&keyword=”, x, “&token=”, z, sep = “”): As previously, we’re constructing our URL. Here, we’re using y to be our region ID, x for our keywords and z for our API key
• apiCall <- gsub(” “, “%20”, apiCall): We’re again using the gsub command to replace spaces with the URL encoding of space – %20
• seRankingData <- fromJSON(apiCall, simplifyDataFrame = TRUE): As before, we’re using jsonlite’s fromJSON command to download our data
• seRankingData <- data.frame(x, seRankingData): We’re turning that JSON data into a dataframe with the columns x (our keyword) and the search volume output from the API
• colnames(seRankingData) <- c(“Keyword”, “Search Volume”): We’re naming our columns “Keyword” and “Search Volume”
• output <- seRankingData}: Finally, we want to return our full dataframe

Again, it’s nice and simple.

To run it, let’s use our keywords dataframe from earlier.

seRankingRun <- reduce(lapply(keywords$keyword, seRankingKeywords, "180", seRankingAPI), 
                       bind_rows)

Here, we’re using our keywords dataframe as the keywords (x), our function name (seRankingKeywords), our Google UK region ID (“180”) and our seRanking API. Nice and simple. This command will loop through all our keywords and merge the data into a single dataframe.

If you run it in your console and then type “seRankingRun”, you’ll see the following:

And there we go. That’s how you can use the SE Ranking API in R. There’s a lot that you can do with this API and I may well go into further depth on it in a later post when I’ve finished this series as it’s very impressive.

Now let’s look at arguably the most popular SEO tool on the market: SEMRush.

The SEMRush API In R

The SEMRush API is another great tool and is essential for a lot of SEO work, and I use it a lot with R. Disclaimer again – I’ve been a customer of SEMRush for more years than I care to remember, and I am also an affiliate, because it’s so awesome.

The SEMRush API also has the option to export to CSV or JSON. Since we’ve covered JSON in a fair amount of depth today, I’m going to show you the CSV export option in R, which is actually its standard operating procedure.

Due to the way the SEMRush API handles error reporting when a keyword isn’t in its database, this is also a really good opportunity to incorporate some error handling in our function. So, let’s get going.

Gathering Keyword Data From The SEMRush API In R

Authenticating with the SEMRush API in R is very similar to how we did it with SERPAPI – just adding our API key to the query string, so that’s handy.

Let’s get going.

Firstly, we want to create an object with our API key. This works in the same way as other APIs, so very simple:

semRushAPI <- "XXXXXXXXX"

Replace the X’s with your own API key again and don’t forget the speech marks.

Now we want to start building our function and gathering our data. Here’s how.

Gathering Keyword Data From The SEMRush API In R

Our function isn’t too different to our others, but we’ve got a new data source in there, which is read.csv compared to our usual fromjson command. You’ll also notice that we’re using a different separator than usual and we’re also going to use a different command to run it.

Let’s take a look at our function, and then we’ll break it down.

semRushKeywordData <- function(x, y){
  
  apiCall <- paste("https://api.semrush.com/?type=phrase_this&key=", semRushAPI, 
                   "&phrase=", x, "&export_columns=Ph,Nq,Cp,Co,Nr,Td,In&database=", 
                   y, sep = "")
  
  apiCall <- gsub(" ", "%20", apiCall)
  
  semRushData <- read.csv(apiCall, header = TRUE, sep = ";", stringsAsFactors = FALSE)

}

How The SEMRush Keyword Research Function Works

As you can see, it’s not a million miles away from our other functions, so you can see that using APIs in R follows fairly similar rules. Let’s see what goes into this one.

• semRushKeywordData <- function(x, y){: We’re creating our semRushKeywordData function and using x and y variables
• apiCall <- paste(https://api.semrush.com/?type=phrase_this&key=: As previously, we’ve got our endpoint and other elements that we want to paste together to build our API call URL. The ?type=phrase_this query says that we’re using the keywordn research function of the API
• semRushAPI,  “&phrase=”, x,: We’re adding the semRushAPI key object we created earlier and our x variable for the phrase, or our keyword from our dataset
• “&export_columns=Ph,Nq,Cp,Co,Nr,Td,In: We’re looking to extract the following columns from SEMRush with this data: Keyword, Search Volume , CPC, Competition Number of Results, Trends and Intent. I told you there was a lot of information you could get from this API!
• &database=”, y, sep = “”): SEMRush has databases all over the world, so you can pull data relevant to your geographic region. The documentation has the full list, and for this function, I’ve set the database to our y variable, so you can change it based on your location. We’re finishing the paste command up with sep=”” as usual, so there are no spaces in the URL
• apiCall <- gsub(” “, “%20”, apiCall): As with our other API calls in our R environment, we’re using gsub to replace any spaces in our keywords with %20 to encode them
• semRushData <- read.csv(apiCall, header = TRUE, sep = “;”, stringsAsFactors = FALSE)}: Now to finish the function with our trusty read.csv command. You’ll notice that there are a couple of extra parameters in there for this one, namely header = TRUE (which does exactly what you’d think it would) and sep = “;” – this is there because the SEMRush API sends data separated by a semicolon rather than your traditional commas, largely because it nests trend data with commas

And there we go, a nice simple function to get SEMRush data from the API in R. Now to run it, we do the following:

semRushOutput <- do.call(rbind, lapply(keywords$keyword, semRushKeywordData, "uk"))

You’ll notice that this is slightly different to the other commands we’ve used to run our API calls before. This is because of the way the SEMRush API sends out CSV data, the usual “reduce” command tends to give an error. By using do.call(rbind we’re essentially doing the same thing, but it tends to work a bit better with CSV data. You may still get some warnings, but not errors.

Now if you run your command on the keywords frame we created earlier, you’ll get the following:

Handy, right? And this is just the beginning of what you can do with APIs in R.

Wrapping Up

So there we have it – now you know how to use different types of APIs in R, ones with JSON and CSV outputs, and how to use them for SEO purposes.

Obviously there are millions of APIs out there, and I haven’t even scratched the surface of what’s possible, but hopefully this crash course will be enough to get you started.

As always, if you have any questions, please hit me up on Twitter/ X and if you’d like to get an alert of when the next post drops, please sign up for my email list below.

Until next time, where we’ll be looking at different ways of running functions with loops and applys. I hope you’ll join me.

Our Code From Today

# Install Packages

install.packages("jsonlite")

library(jsonlite)

install.packages("tidyverse")

library(tidyverse)

# SERPAPI

serpApiKey <- "XXXXXXXXXXXXX"

## Endpoint

serpApiEndpoint <- "https://serpapi.com/search.json?engine=google"

## TV Units Call

serpApiCall <- paste(serpApiEndpoint, "&q=tv%20units", "location=United+Kingdom&google_domain=google.co.uk", 
                     "&api_key=", serpApiKey, sep = "")

serpAPI1 <- fromJSON(serpApiCall, simplifyDataFrame = TRUE)

#Exploring JSON

str(serpAPI1)

serpAPI1$organic_results

serpAPI1$organic_results$title

## Create Keywords Dataframe

keywords <- data.frame(keyword = c("sliding wardrobes", "bookcases", "tv units",
                                   "dining tables"))

## Running Multiple Keywords In SERPAPI

## Domain Names Function

domainNames <- function(x){
  
  strsplit(gsub("http://|https://|www\\.", "", x), "/")[[c(1, 1)]]
  
}

## SERPAPI Function

serpAPIFun <- function(x){
  
  require(tidyverse)
  
  require(jsonlite)
  
  apiCall <- paste(serpApiEndpoint, "&q=", x, "&location=United+Kingdom&google_domain=google.co.uk", 
                   "&api_key=", serpApiKey, sep = "")
  
  apiCall <- gsub(" ", "%20", apiCall)
  
  serpData <- fromJSON(apiCall, simplifyDataFrame = TRUE)
  
  serpData$organic_results$domain <- domainNames(serpData$organic_results$link)
  
  output <- data.frame(serpData$search_parameters$q, serpData$organic_results$link, 
                       serpData$organic_results$title, serpData$organic_results$snippet, 
                       serpData$organic_results$domain, serpData$organic_results$position)
  
  colnames(output) <- c("Keyword", "URL", "Title", "Description", "Domain", "Position")
  
  return(output)
  
}

serpAPI2 <- reduce(lapply(keywords$keyword, serpAPIFun), bind_rows)

## SE Ranking

seRankingAPI <- "XXXXXXXXXXXXX"

seRankingDBs <- function(x){
  
  apiCall <- paste("https://api4.seranking.com/system/search-engines?token=", x, 
                   sep = "")
  
  databases <- fromJSON(apiCall, simplifyDataFrame = TRUE)
  
  output <- data.frame(databases)
}

seRankingDatabases <- seRankingDBs(seRankingAPI)

## SE Ranking Keyword Volume

seRankingKeywords <- function(x, y, z){
  
  apiCall <- paste("https://api4.seranking.com/system/volume?region_id=", y, 
                   "&keyword=", x, "&token=", z, sep = "")
  
  apiCall <- gsub(" ", "%20", apiCall)
  
  seRankingData <- fromJSON(apiCall, simplifyDataFrame = TRUE)
  
  seRankingData <- data.frame(x, seRankingData)
  
  colnames(seRankingData) <- c("Keyword", "Search Volume")
  
  output <- seRankingData
}

seRankingRun <- reduce(lapply(keywords$keyword, seRankingKeywords, "180", seRankingAPI), 
                       bind_rows)

# SEMRush

semRushAPI <- "XXXXXXXXXX"

semRushKeywordData <- function(x, y){
  
  apiCall <- paste("https://api.semrush.com/?type=phrase_this&key=", semRushAPI, 
                   "&phrase=", x, "&export_columns=Ph,Nq,Cp,Co,Nr,Td,In&database=", 
                   y, sep = "")
  
  apiCall <- gsub(" ", "%20", apiCall)
  
  semRushData <- read.csv(apiCall, header = TRUE, sep = ";", stringsAsFactors = FALSE)

}

semRushOutput <- do.call(rbind, lapply(keywords$keyword, semRushKeywordData, "uk"))

This post was written by Ben Johnston on Ben Johnston

Welcome back. It’s part 5 of my R for SEO series and I hope you’re all finding it useful so far. Up to now, we’ve covered the basics, using packages and Google Analytics & Search Console, data visualisation with GGPlot2 and wordcloud and in our last piece, we looked at R functions for SEO. Now let’s start seeing how the power of R can help us replicate the common Excel formulas we use in our day-to-day, but faster and on larger datasets.

We’re going to use some of the datasets we’ve already created over the course of this series today for examples, but my goal is to make these commands and functions at least mostly reproducible based on what we’ve already learned to this point.

As always, if you have questions, please feel free to hit me up on Twitter or drop me a line through the contact form, and I hope you’ll consider signing up to my free mailing list. No spam, no sales pitches, just emails when I release new content.

The Ifelse Statement In R

The If statement is one of the most common queries used in programming and as SEOs, we use it a lot in Excel, when we’re trying to find data that matches our specific criteria or does not match our criteria. Here’s how we can run a similar if statement in R:

gscData$Fifty.Or.More.Impressions <- ifelse(gscData$Impressions >=50, "YES", "NO")

Here, we’re looking at our Google Search Console query dataset that we created a couple of weeks ago (or you can just download your own data from Search Console and read it in using read.csv) and adding another column to it to say if a query has fifty or more impressions.

Again, a fairly simplistic usage, but hopefully it gives you an idea of how the command works.

It’ll give the following output by saying “YES” or “NO” against queries with fifty impressions or more. Obviously, your data will vary.

Let’s break it down.

The Anatomy Of An Ifelse Command In R

Breaking down our R ifelse command, we can see it works in the following way:

• gscData$Fifty.Or.More.Impressions <-: We’re adding the snappily-named Fifty.Or.More.Impressions column to our gscData object
• ifelse(gscData$Impressions >=50: This calls the ifelse function from base R and tells it the condition for the function is that our gscData Impressions column is greater than or equal to 50
• “YES”, “NO”): Much like its Excel counterpart, IF, our ifelse function needs responses for if our condition is or is not met.To keep this one simple, we’re just saying “YES” if there are 50 or more impressions and “NO” if not

Now we’ve got the basics of the ifelse down, let’s use a slightly longer version which we can add more conditions to and make it a little more complicated.

If Else In R With Multiple Conditions

While we can use ifelse with multiple criteria, there’s a whole world of if statements that are possible in R if we go into slightly more complex statements.

If and else can be broken down into separate commands, all with their own statements attached, making it incredibly flexible. Almost like replicating Excel’s IFS function in R.

Let’s create another fairly simple one using the same Google Search Console dataset and, as we saw in part 4, turn it into a flexible and reproducible function.

gscIFELSEFun <- function(x, y){
  
  if(x >= y){
    return("Greater or Equal")
  }else{
    return("Less")
  }
  
}

Now to run it, paste the following command into your console:

gscData$Function <- sapply(gscData$Impressions, gscIFELSEFun, 50)

As you can see, it’s slightly more complex, but runs very fast and, if you’ve got a decently-sized dataset, will take much less time than doing it in Excel.

How Our If Else R Function Works

As always, let’s break it down:

• gscIFELSEFun <- function(x, y){: Another very snappy name for today. This function is called gscIFELSEFun and uses x and y variables
• if(x >= y){: We’re calling the if function and using our variables for the conditions for the output beyond the braces. In this case, X is our dataset and y is our value. For the purposes of this piece, we want to see if our data is greater or equal to our y condition
• return(“Greater or Equal”)}: Should the condition from our if statement (x being greater or equal to y) be met, it should return the specified value – “Greater or Equal” in this case
• else{return(“Less”)}: Now to the else phase of our function. If the conditions of our statement are not met, we want it to return the value “Less”

Now this looks remarkably similar to our previous command, doesn’t it? So why have we added extra steps and turned it into a function?

For this particular example, it’s a learning exercise, obviously. But this is how we would go about building up increasingly complex if else statements in R, with multiple conditions, multiple outputs and truly replicating Excels IFS function, or nested IF statements.

Now let’s use what we learned and replicate Excel’s IFS formula with a nested if else function in R.

Nesting If Else Statements To Replicate Excel IFS Formula In R

IFS is one of the main formulas I find myself using in Excel. It’s very handy to find matches across multiple conditions, and we can easily replicate it in R by nesting our if else commands and make them fast and reproducible in a function.

Let’s take our above example and create a function that tells us if our Search Console impressions are greater than, equal to or less than 50.

gscNestedFun <- function(x, y){
  
  if(x > y){
    return("Greater")
  }else{
    if(x < y){
      return("Less")
    }else{
      if(x == y){
        return("Equal")
      }
    }
  }
}

Wow, that’s a lot of closing braces, right? But it should be fairly self-explanatory if you’ve been following along.

Let’s break it down.

• gscNestedFun <- function(x, y){: As always, we’ve got the name of our function, our x and y variables and our opening braces to start our function
• if(x > y){ return(“Greater”)}: Again, we’ve got our starting if statement. In this part, we’re looking to see if our Search Console impressions are greater than 50. If they are, the function returns “Greater”
• else{if(x < y){return(“Less”)}: Our first else command says that if the previous condition isn’t met, to create another if statement to see if our impressions are less than 50. If they are, return “Less” in our output
• else{if(x == y){return(“Equal”)}: And for our final else-if commands, we’re seeing if our impressions are equal to 50 and returning “Equal” if they are. As mentioned in part 1, if we want exact matches, we need to double up on the equals symbol in R. Don’t forget all those closing braces!

To run it, as before, we need to use sapply:

gscData$NestedFunction <- sapply(gscData$Impressions, gscNestedFun, 50)

And if we look at a table of our output, you’ll see something like this (although your numbers will be different).

That’s a good primer to using the if, else and ifelse commands and how we can use it to replicate a couple of common Excel formulas in R. Most of programming comes down to if and else to varying degrees, so there’s a lot that we can do here.

Excel Countif In R

When I use Excel, particularly on larger datasets, I find myself using countif quite a lot. Obviously, the whole point of using R for SEO is to work with larger datasets quickly and efficiently, so the countif is definitely something that should be in your arsenal.

Fortunately, this is very easy to do. While you can use the basic if/ else statement above and work through it accordingly, you can actually do this all in one line using some base R syntax.

Again, let’s run it on our Google Search Console dataset to see how many queries have 50 impressions or more:

sum(gscData$Impressions >= 50, na.rm=TRUE) 

And if we run this command, we’ll get the following output (again, your data will be different):

How does this work? Like so:

• sum(: The sum command is simply telling us to add up the number of results in our chosen criteria
• gscData$Impressions: Our dataset that we’re running the operation on (if this were a function, it’d be x)
• >=50,: The criteria for our sum (the “if” if you will). Here I’ve used >=50 (greater than or equal to 50) for an example, but it can be pretty much whatever you want it to be
• na.rm=TRUE): Numbers which don’t match our criteria will not be counted

Nice and simple, right? That’s how we can replicate Excel’s countif formula in R.

Now let’s look at sumif.

Excel Sumif In R

Excel’s Sumif – adding together the numbers that match a certain criteria – is similarly simple in R and, again, if you’ve got quite a lot of data, a lot faster than running in Excel. Here’s how we can do that.

As above, we’re going to focus on our Search Console dataset.

This is another very simple Excel formula that we can replicate with base R. We can run a Sumif like so:

sum(gscData$Impressions[gscData$Impressions >= 50])

If you run that in your console, you’ll see something like the following:

Shall we see how it works?

How Sumif Replication In R Works

As always, let’s break it down.

• sum(gscData$Impressions: Like we did before, we’re running the sum command on our gscData Impressions column, but there’s one very important difference that makes this a sumif rather than a countif
• [gscData$Impressions >= 50]): As an added condition to our previous sum() command, we’re creating a subset of all impressions that are greater than or equal to 50 and feeding back that these figures should be added together rather than just counted

Very simple, isn’t it? And much faster than Excel. I’m hoping by this point, you’ll be seeing the advantages of using R for much of your SEO analysis work rather than relying on spreadsheets.

Index Match Or Vlookup In R

Index match in Excel (or Vlookup, if you’re a dirty heathen) is a great way of matching datasets with a common criteria. Honestly, it’s something I find I have to do quite a lot due to working across ranges of datasets, but fortunately R makes it very easy and quick.

There are two key ways I’m going to show you how to do this today – one using the left_join function from Dplyr in the Tidyverse package (the quickest and most efficient way), and also a function to let you do it without the Tidyverse if you can’t use it for whatever reason.

Firstly, let’s look at emulating an index match using left_join.

Preparing Our Dataset For Index Match/ Vlookup Emulation With R

Here, we’re going to return to our TV Units dataset from SE Ranking that we used in part 4 and create a separate frame for our domains, using the domainNames function we created there. To save you clicking around into the previous article, you’ll find the dataset here, and the function is as follows:

domainNames <- function(x){

  strsplit(gsub("http://|https://|www\\.", "", x), "/")[[c(1, 1)]]
  
}

If you’ve been following along, it shouldn’t be too scary to import that dataset using read.csv as follows:

tvUnits <- read.csv("tvUnits.csv", stringsAsFactors = FALSE)

And to create our secondary datasets, we run the following commands:

tvUnits2 <- data.frame(tvUnits$URL)

names(tvUnits2) <- names(tvUnits["URL"])

That’s our datasets ready to go. Now let’s get back to it.

Index Match Or Vlookup In R With Left_Join

Running an index match/ vlookup emulation in R is a really quick and easy command, no matter how large your dataset is. It’s certainly faster than doing it in Excel – especially if you use vlookup.

You may have gathered that I look down upon vlookups. You would be correct in that assumption.

Anyway, here’s what a left_join-based index match/ vlookup command would look like in R, using the datasets that we just created. Remember that you will need dplyr or the Tidyverse packages installed to make this work.

tvUnits$Domain <- tvUnits %>% 
  left_join(tvUnits2, by = "URL")

As you can see, the two datasets have merged using the URL as the anchor, which is why it’s important to make sure there’s a consistent anchor value.

Let’s take a look at how this works:

• tvUnits$Domain <- tvUnits %>%: We’re adding our “Domain” column to our tvUnits dataset, using the Tidyverse’s “chain” command
• left_join(tvUnits2, by = “URL”): The dplyr/ Tidyverse left_join command is how we replicate the Excel index match or vlookup function – it joins our dataframes by our chosen column (“URL” in this case) with all columns to the right of that parameter

Left_join is by far the quickest and easiest way to emulate Excel’s index match or vlookup function, however, it can sometimes end up causing issues if your two dataframes are of different sizes.

An Index Match Function In R Without Dplyr

Sometimes, there might be occasions where you can’t use a certain R package, such as the Tidyverse. You may have a project that’s dependent on a package that conflicts with it, your IT department might block the installation of packages (I’ve been there and it’s infuriating), there are lots of possible reasons, so it’s worth learning ways around the problem.

In the case of replicating Excel’s index match or vlookup without Dplyr (meaning we can’t use left_join), here’s a simple function that you can use. In some cases, when you only want a specific column from a dataset, this can actually be a little smoother than left_join.

indexMatch <- function(x, y, z){
  
  row <- match(x, y)
  
  targetVal <- z[row]
  
}

To run this function, you need to have the following parameters to hand:

• x: The range we’re searching
• y: The value we’re matching against
• z: Our target data range

Now that we’ve got those, let’s run our index match R function.

We can do this like so, using our two tvUnits dataframes.

tvUnits$Domain2 <- indexMatch(tvUnits$URL, tvUnits2$URL, tvUnits2$Domain)

Now you’ll see a new column in your dataset called “Domains2”, which, as before, matches the domain from our tvUnits2 frame against the URL in the tvUnits domain, similar to the left_join command.

How The IndexMatch Function Works

Let’s break the function down:

• indexMatch <- function(x, y, z){: Our function is called indexMatch and we’ve got x, y and z variables as we covered above
• row <- match(x, y): We’re creating an object called row, using base R’s match function to find the row number where the data we’re searching through (x) matches our target value (y) in the same way a vlookup or index match works in Excel
• targetVal <- z[row]}: Now we know which row we’re targeting based on our match function, we want to get our target value by extracting our row number from the target range

And there we go. Excel’s index match/ vlookup formulas emulated in R, using the Dplyr package from the Tidyverse and also with a function using base R.

Pivot Tables In R

Pivot tables are something many SEOs use a lot in Excel, and it’s easy to see why. They’re a brilliant way to present and group large amounts of data in an easy-to-digest format.

But again, the problems of too much data can cause serious issues with Excel, which is why we’re using R in the first place, right?

So here’s how you can create the super-useful pivot table in R using the pivottabler package and export them in a number of different client-friendly formats, rather than sending a 200mb Excel file that will kill their computer.

First, we need to install the package. As usual, it’s the common commands:

install.packages("pivottabler")

library(pivottabler)

Now we’ve got to get our data ready for a pivot table, which means we need a consistent point. For this example, we’re going to use a Google Analytics export from GA4 with landing page and channel as our dimensions.

If you’ve followed the series thus far, this will be fairly simple. You can refresh your knowledge with Part 2, or you can just export from the GA4 site.

Now we have our data, we need to create our pivot table.

Our First Pivot Table With Pivottabler

We’re going to create our first pivot table using the landing page as the key and the session channel as the second dimension in the summary.

Here’s how to create a very simple one and then we’ll start expanding later.

pt1 <- PivotTable$new()
pt1$addData(gaData)
pt1$defineCalculation(calculationName = "Total Sessions", summariseExpression = "sum(Sessions)")
pt1$addRowDataGroups("Landing.page")
pt1$addRowDataGroups("Session.default.channel.group")
pt1$renderPivot()

This command will create the following output in your RStudio viewer window:

As you can see, it gives us a simple html output, which looks quite pretty and can be exported as an image or HTML like so.

Now, let’s have a look at how our R pivot table works.

How The R Pivot Table Works

Here’s that phrase again: let’s break it down:

• pt1 <- PivotTable$new(): We’re creating an object called pt1, using a PivotTable function and creating a “new” column
• pt1$addData(gaData): We’re invoking our dataset with our pivot table. The gaData frame in this case
• pt1$defineCalculation(calculationName = “Total Sessions”, summariseExpression = “sum(Sessions)”): This tells R that the calculation we want to use for our pivot table is called “Total Sessions” and we’re using the sum calculation on “Sessions” from our dataset
• pt1$addRowDataGroups(“Landing.page”): The first row of our pivot table, the “key”, is our landing page
• pt1$addRowDataGroups(“Session.default.channel.group”): Our second column, our drilldown dimension, is our traffic channel
• pt1$renderPivot(): Now we’re using pivottabler’s renderPivot command to turn this to HTML and render it in our viewer window

Adding Extra Columns To Our R Pivot Table

If you export directly from GA4, or you add extra metrics to your Google Analytics call (I promise I’ll update part 2 to use the GA4 API soon), you’ll find that you have metrics other than sessions, such as Users and Engagement Time.

Let’s add those to our pivot table as well, summing Users and calculating the Average Engagement Time, so we can see how many users our sessions are driving and how long they’re spending with our content from each channel.

We can do that like so:

pt2 <- PivotTable$new()
pt2$addData(gaData)
pt2$defineCalculation(calculationName = "Total Sessions", summariseExpression = "sum(Sessions)")
pt2$defineCalculation(calculationName = "Total Users", summariseExpression = "sum(Users)")
pt2$defineCalculation(calculationName = "Average Engagement Time", summariseExpression = "round(mean(Average.engagement.time.per.session), 2)")
pt2$addRowDataGroups("Landing.page")
pt2$addRowDataGroups("Session.default.channel.group")
pt2$renderPivot()

And that will give us the following output:

How This Expanded Pivot Table Works

I won’t re-use all the explanations from the previous section, but let’s take a look at the extra rows we’ve added. For clarity’s sake in the code, I’ve called this one “pt2” rather than “pt1”.

• pt2$defineCalculation(calculationName = “Total Users”, summariseExpression = “sum(Users)”): We’ve added an extra column to our pivot table called “Total Users”, which is adding up “Users” from our gaData dataset
• pt2$defineCalculation(calculationName = “Average Engagement Time”, summariseExpression = “round(mean(Average.engagement.time.per.session), 2)”): This line might look a little intimidating, but all it’s doing is calculating the Average Engagement Time, and then using the round function to take it to two decimal places, which is why it’s wrapped in brackets

So there we go. Hopefully that gives you a solid basis to create pivot tables in R.

Let’s look at styling them up so that you can include them in presentations and share with your clients.

Adding Styling To Pivot Tables With R

Finally, let’s create another pivot table using our R code from earlier, but naming it p3 instead of p2. From here, we’ll add another few lines to style our cell backgrounds in dark green (the accent colour from my site).

Here’s how to do that:

pt3 <- PivotTable$new()
pt3$addData(gaData)
pt3$defineCalculation(calculationName = "Total Sessions", summariseExpression = "sum(Sessions)")
pt3$defineCalculation(calculationName = "Total Users", summariseExpression = "sum(Users)")
pt3$defineCalculation(calculationName = "Average Engagement Time", summariseExpression = "round(mean(Average.engagement.time.per.session), 2)")
pt3$addRowDataGroups("Landing.page")
pt3$addRowDataGroups("Session.default.channel.group")
pt3$evaluatePivot()

### Add Styling

pt3$setStyling(
  rowNumbers = c(1),  # Target the header row
  columnNumbers = c(4, 5, 6),  # Target the specific columns
  declarations = list("text-align" = "centre", "color" = "#008285")
) 
pt3$renderPivot()

Let’s take a look at the extra rows and how the styling works.

How Styling In Pivottabler Works

For the last time today, let’s break it down:

• pt3$setStyling(: We’re telling R to apply styling rules
• rowNumbers = c(1): We’re looking at the first row, the header row containing the column names
• columnNumbers = c(4, 5, 6): These are the columns for “Total Users”, “Average Engagement Time”, and “Total Sessions”
• declarations = list(“text-align” = “center”): Here, we’re setting the above columns to be centre aligned
• “color” = “#F46239”)): Finally, we’re setting the accent colour to the same colour as my site’s accent colour – change as appropriate

And there we go, a whistlestop tour of using the SEO’s old favourite  – pivot tables – in R with Google Analytics data and adding some styling to it. Try it yourself, there’s a lot you can do with them.

Wrapping Up

That’s a few common Excel formulas replicated with R, how to create pivot tables and to style them up according to your branding.

I hope you’ll join me next time, where we’ll take a look at using APIs in R. I’m really excited about that piece, and I hope you’ll enjoy reading it and using it as much as I enjoyed writing it.

As always, our R script is below.

Our Code From Today

# Install Packages

library(tidyverse)

library(rvest)

library(pivottabler)

# Read Search Console Queries

gscData <- read.csv("Queries.csv", stringsAsFactors = FALSE)

# If Statement for >= 50 Impressions

gscData$Fifty.Or.More.Impressions <- ifelse(gscData$Impressions >=50, "YES", 
                                            "NO")

# If Else Statement With Multiple Conditions

gscIFELSE1 <- if(gscData$Impressions[1] >=50){
  return("Greater or Equal")
}else{
  return("Less")
}

## If Else Statement Function

gscIFELSEFun <- function(x, y){
  
  if(x >= y){
    return("Greater or Equal")
  }else{
    return("Less")
  }
  
}

gscData$Function <- sapply(gscData$Impressions, gscIFELSEFun, 50)

## Nested If Else Statement Function

gscNestedFun <- function(x, y){
  
  if(x > y){
    return("Greater")
  }else{
    if(x < y){
      return("Less")
    }else{
      if(x == y){
        return("Equal")
      }
    }
  }
}

gscData$NestedFunction <- sapply(gscData$Impressions, gscNestedFun, 50)

# Excel Countif In R

sum(gscData$Impressions >= 50, na.rm=TRUE)

# Excel Sumif In R

sum(gscData$Impressions[gscData$Impressions >= 50])

# Excel Index Match In R

## TV Unit Data & Domain Function From Part 4

tvUnits <- read.csv("tvUnits.csv", stringsAsFactors = FALSE)

domainNames <- function(x){
  
  strsplit(gsub("http://|https://|www\\.", "", x), "/")[[c(1, 1)]]
  
}

tvUnits2 <- data.frame(tvUnits$URL)

names(tvUnits2) <- names(tvUnits["URL"])

tvUnits2$Domain <- sapply(tvUnits2$URL, domainNames)

## Index Match Emulation With Left_Join

tvUnits$Domain <- tvUnits %>% 
  left_join(tvUnits2, by = "URL")

## Index Match Emulation Function Without Dplyr

indexMatch <- function(x, y, z){
  
  row <- match(x, y)
  
  targetVal <- z[row]
  
}

tvUnits$Domain2 <- indexMatch(tvUnits$URL, tvUnits2$URL, tvUnits2$Domain)

# Pivot Tables In R

gaData <- read.csv("gaData.csv", stringsAsFactors = FALSE)

## First Pivot Table

pt1 <- PivotTable$new()
pt1$addData(gaData)
pt1$defineCalculation(calculationName = "Total Sessions", summariseExpression = "sum(Sessions)")
pt1$addRowDataGroups("Landing.page")
pt1$addRowDataGroups("Session.default.channel.group")
pt1$renderPivot()

## Second Pivot Table With Extra Metrics 

pt2 <- PivotTable$new()
pt2$addData(gaData)
pt2$defineCalculation(calculationName = "Total Sessions", summariseExpression = "sum(Sessions)")
pt2$defineCalculation(calculationName = "Total Users", summariseExpression = "sum(Users)")
pt2$defineCalculation(calculationName = "Average Engagement Time", summariseExpression = "round(mean(Average.engagement.time.per.session), 2)")
pt2$addRowDataGroups("Landing.page")
pt2$addRowDataGroups("Session.default.channel.group")#pt2$renderPivot()

## Styling Pivot Table

pt3 <- PivotTable$new()
pt3$addData(gaData)
pt3$defineCalculation(calculationName = "Total Sessions", summariseExpression = "sum(Sessions)")
pt3$defineCalculation(calculationName = "Total Users", summariseExpression = "sum(Users)")
pt3$defineCalculation(calculationName = "Average Engagement Time", summariseExpression = "round(mean(Average.engagement.time.per.session), 2)")
pt3$addRowDataGroups("Landing.page")
pt3$addRowDataGroups("Session.default.channel.group")
pt3$evaluatePivot()
###Add Styling
pt3$setStyling(
  rowNumbers = c(1),  # Target the header row
  columnNumbers = c(4, 5, 6),  # Target the specific columns
  declarations = list("text-align" = "centre", "color" = "#008285")
) 
pt3$renderPivot()

This post was written by Ben Johnston on Ben Johnston

Welcome back to part four of my series on using R for SEO. We’re at the halfway point now and hopefully you’re starting to see the power that the R language can bring to your optimisation and analysis.

Today we’re going to start making it feel like we’re programming – we’re going to be writing our own R functions, looking at the anatomy of a function and creating a few of the most common ones that I use in my SEO work.

I know, again, that this piece is super late. The last eighteen months has been a lot, but hopefully the next few months will give me more time.

As always, shares of this piece are highly appreciated, and if you’d like updates on when I drop new content, please sign up for my FREE email list using the form below.

Ready to go?

R Functions

Functions in R or any other programming language are basically commands. Every command we’ve used up until this point has included a function of some description. Think of them like Excel formulae.

But the advantage of programming is that we’re not limited to the ones that come in the box – we can create our own. This is where using R starts to feel like it’s real programming rather than just running a series of commands in a terminal, and when I started using it, functions were where it all really started to click.

The beauty of a function is that you can make it variable and, consequently, use it over and over again rather than writing the same commands constantly over the course of your analysis, and you can copy and paste them from one .R file into another and they’ll still work.

An R function can be as simple or as complex as you need it to be. You can make an entire piece of analysis run from one function if you need it to, but there are a few core elements that you should always be aware of.

Our First R Function

Again, an R function can be as simple or as complex as you need it to be, but the anatomy of that function will always have similarities.

Let’s create a really simple function first, and then we’ll break it down.

firstFun <- function(x,y){
x*y
}

This is a very basic function that will multiply one value (x) by another value (y). Truth be told, we don’t need an R function for this, but it’s an easy illustration for our first one.

Paste this function into your console and now you’ll see “firstFun” in your environment explorer under “Functions”, like so:

Now to run it, we can simply type the following in our console:

firstFun(5,2)

Hit return and you’ll see the following output in your console:

Nice and simple.

And if we wanted to turn it into an object, we could use the following:

firstFunRun <- firstFun(5,2)

Paste that into your console and you’ll see that firstFunRun is now in your environment.

And again, if you type firstFunRun into your console and hit return, you’ll see the same output as above.

The beauty of using this as a function is the variables (x and y, in this case), meaning we can put any value we like in their place when running the function. Try it yourself.

Now we’ve made our first function, let’s expand it a little.

Expanding Our First Function

Here’s how we could make this function a little bit more varied and incorporate some additional values:

secondFun <- function(x,y){
  
  val1 <- x*y
  
  val2 <- x+y
  
  val3 <- x-y
  
  val4 <- x/y
  
  output <- data.frame(val1,val2,val3,val4)

}

So now we’ve made our function ever so slightly more complex and included a few more values, with the idea of exporting all these permutations of our original calculation in one go. This is where a function would be a bit more useful.

As before, paste this into your console. Now use the same command as before:

secFunRun <- secondFun(5,2)

And when we type secFunRun into our console and hit return, we’ll get the following:

Editing Column Names In An R Function

It’s starting to come together. Now let’s edit our secondFun function to add column headers so we know which value is which.

secondFun <- function(x,y){
  
  val1 <- x*y
  
  val2 <- x+y
  
  val3 <- x-y
  
  val4 <- x/y
  
  output <- data.frame(val1,val2,val3,val4)
  
  colnames(output) <- c("multiply", "add", "subtract", "divide")
  
  return(output)
  
}

Paste that into your console and run the same command again, and your output will be updated to show the following:

As you may have guessed here, we don’t really need that “names” section there, we could’ve just named the elements that way in the first place, but it was just an example to show you how you can rename columns in a data frame in R.

Again, try it yourself a little. Now let’s look at the anatomy of that final R function and how we build them.

The Anatomy Of An R Function

Again, your functions can be as simple or as complex as you need them to be, but there are some common elements:

• secondFun <- function: Here, we’re telling R that we’re creating something called “secondFun” and it’s a function
• (x,y): The number of variables required by the function. You can add as many as you want, but it’s standard to start with “x”. If it goes beyond “z”, I typically start again with “a”
• {}: When creating an R function, we use the braces to envelop everything contained within that function, all our commands
• val1–4: The commands that we’re including in our function. In this case, we’re multiplying, adding, subtracting and dividing x and y across different commands and creating different datasets in our function’s environment. You can call them whatever you want and run pretty much any command you like
• colnames(output): We’re telling R what the column names in our output frame should be. This isn’t always necessary, but can certainly be handy if you want specific column headers and your data doesn’t automatically support them
• output: An R function always returns the last value it creates, rather than everything in the function, so you have to be specific about what you want the output to be. Typically, you’d use “return” or “output” as your denotation, just for convention

You can have a lot of flexibility with your R functions, but following this general anatomy, your functions should work fine.

As we go through the rest of this series, we’ll be using functions quite a lot, and you’ll see that there are a number of different ways they can be invoked, but they’ll all follow this basic layout, so this should be a good grounding.

Now let’s look at certain commands that make our functions reproducible in different projects, and then we’ll start running through a couple of my favourite functions.

Using “Require” For Packages To Make R Functions Reproducible

As you go further on your R journey, you’ll inevitably find yourself re-using functions that you’ve written in other projects. But what if they depend on certain packages?

You can, of course, install those packages at the start of your project, but sometimes, it can be tough to remember which ones you used, which is why we can call them from our function directly. That’s where the “require” command comes in.

The require command does depend on you having installed the package in the past, so it’s not flawless if you’re sharing code, but at least with this notation in your function, you’ll know which packages you need.

Let’s do a function with our Google Analytics and Search Console commands from part 2, turning them into one dataframe with variable dates so we can re-use it easily.

If we authorised everything properly in the previous sessions, we shouldn’t need to go through that again, but you will still need your Google Analytics View ID to hand.

A Google Analytics & Search Console R Function With “Require”

Here’s how the function looks:

gaSCR <- function(x, y, z, a){
  
  require(googleAnalyticsR)
  
  require(searchConsoleR)
  
  require(tidyverse)
  
  ga_data <- google_analytics(viewId = x,
                              date_range = c(z, a),
                              metrics = c("sessions", "users", "pageviews", "bouncerate"),
                              dimensions = "date")
  
  sc_data <- search_analytics(site = y,
                              start_date = z,
                              end_date = a,
                              dimensions = c("date"),
                              metrics = c("impressions", "clicks"))
  
  merged_data <- merge(ga_data, sc_data, by = "date", all = TRUE)
  
  merged_data$ctr <- (merged_data$clicks / merged_data$impressions) * 100
  
  return(merged_data)
  
}

You’ll see at the start, that we’ve put “require” and named the packages that we’ll need for this piece. That means that, if they’re not already loaded in, R will load them whenever you run your function. However, they will need to have been installed previously.

Now we need to set our variables for x, y, z and a – our Google Analytics View ID, our Search Console website URL, our start date and our end date. If you go back to part 2, you’ll find how to do that.

For this example, I’ve used the following:

• x: view_id <- “GOOGLE-ANALYTICS-VIEW-ID”
• y: site_url <- “YOUR-WEBSITE-URL”
• z: start_date <- “YOUR-START-DATE”
• a: end_date <- “YOUR-END-DATE”

Now paste that into your console and follow it up with:

googleData <- gaSCR(view_id, site_url, start_date, end_date)

And there we go, a really handy R function for pulling Google Analytics and Search Console data into one dataframe with variable dates. Try it yourself and feel free to change it to include different metrics and dimensions.

How The Merged Google Analytics & Google Search Console Function Works

Let’s break it down:

• gaSCR <- function(x, y, z, a){: As always, we’re creating our function, naming it gaSCR and our variables are x, y, z and a
• require: Here, we’re telling R that there are packages required for this function to run, googleAnalyticsR, searchConsoleR and tidyverse, in this case. These will be packages you’ve installed before, if you’ve been following along, but if not, you’ll need to use the install.packages command for them
• ga_data <- google_analytics(viewId = x: We’re telling R to create an object called ga_data and it’s coming from the google_analytics function in googleAnalyticsR, focusing on our viewID variable which is x in this function
• date_range = c(z, a): As we saw in part 2, we’re invoking the date_range element of our Google Analytics call. We are combining our dates into our start date (z) and our end date (a)
• metrics = c(“sessions”, “users”, “pageviews”, “bouncerate”): We’re telling R that the metrics we want from Google Analytics are Sessions, Users, Pageviews and Bounce Rate. You can change these to your own requirements
• dimensions = “date”): The last part of our Google Analytics call is to split our data by dimension. Date, in this case
• sc_data <- search_analytics(site = y,: Now we’re starting our Google Search Console data pull, and our site URL is being invoked with y
• start_date = z, end_date = a: As with our Google Analytics call, we’re invoking our date range variables in our Google Search Console data
• dimensions = c(“date”): We’re splitting our Google Search Console data by date, in the same way we did our Google Analytics data
• metrics = c(“impressions”, “clicks”)): The metrics that we want from Google Search Console are impressions and clicks. That ends our Google Search Console call
• merged_data <- merge(ga_data, sc_data, by = “date”, all = TRUE): We’re using the merge function from the dply package in the tidyverse to combine our Google Analytics and Search Console datasets with the date being the anchor
• merged_data$CTR <- (merged_data$clicks / merged_data$impressions) * 100: Now we’re creating another column in our data which calculates click-through rate percentage
• output <- merged_data: Finally, we’re sending all this data to our output dataframe

Now we can see how to merge a Google Analytics and Google Search Console dataset within a singular dataframe. Pretty handy, right?

Note: This is still using Universal Analytics. I will be updating all these posts to utilise the GA4 API in the near future.

Now let’s look at a couple of my other favourite R functions that I find myself using a lot.

Merging Multiple CSV Files In R

This was one of the first functions I worked with outside of a course. It’s pretty old now, and I’m sure there are better ways to do it, but it still works reliably for merging multiple CSV files and getting them into your environment in R.

First, make sure your CSV files are all in one dedicated folder, preferably a subfolder of your project directory. You can navigate to them in RStudio through the “Files” pane, like so:

Now we need to tell R to switch our working directory to that, which we can do like so, using RStudio:

Alright, now we’re where we need to be, here’s the function I use to merge multiple CSV files in R and create a dataframe from them.

Before we continue, it’s vital that the CSV files all have the same headers. There can be different amounts of data in there, but if they’ve got different headers, this function won’t work. That said, I’ve found it really handy over the years for dealing with multiple exports and so on. Assuming all that’s true, here’s how to run it.

Paste the following into your console:

csvMerge <- function(x){
  
  require(plyr)
  
  csvFiles <- dir(pattern = x, full.names=TRUE)
  
  output <- ldply(csvFiles, read.csv)
  
}

Now run it like so:

csvFiles <- csvMerge("csv$")

Depending on how many files and how big they are, it may take a few seconds, but it’s a lot quicker than copying and pasting in Excel! Now you should have a dataframe called “csvFiles” in your R environment like so:

Let’s break down how it works:

• csvMerge <- function (x){: We’re telling R that we’re creating a new object called csvMerge, it’s a function and there is one variable utilised called x. The opening brace is what the function’s commands will be contained in
• require(plyr): Our function has dependencies from the plyr package. If you don’t already have it installed, you’ll want to use the install.packages(“plyr”) command to get it installed. If you do already have it installed, this command will initialise it
• csvFiles <- dir(pattern = x, full.names=TRUE): Here, we’re telling R to look through the current working directory for files that match the pattern of our x variable, and that it should look at the full name of it. It should then create a csvFiles object with all of these names. In this case, our x variable is “csv$”, which means we’re looking for the end of the file to be called “csv”
• output <- ldply(csvFiles, read.csv)}: Our output is to use the ldply function from the plyr package to apply our chosen function (read.csv, in this case) across our list (csvFiles) and merge the results into a single dataframe. The closing brace says our function is complete

And there you have it: a quick and easy way to merge multiple CSV files in R. Even if you didn’t need them for analysis and just needed them all in one file, you can still use this function and then export it like so:

write.csv(csvFiles, “merged csvs.csv”)

This will export it all into one CSV file. I can’t tell you how handy this function has been to me over the years, so hopefully it’ll help you too.

Download XML Sitemaps & Check Status Codes In R

Obviously, there are better ways to do this, using tools like Sitebulb or Screaming Frog, but as SEOs, I’m sure we’ve all experienced the delays and challenges of software or getting IT departments to actually let us install them, which is why I created this little piece a while back.

This function downloads the XML sitemap from the target URL, scrapes the URLs from it using the Rvest package (another Hadley Wickham creation), and checks the HTTP status code of them using the httr package. Although sitemaps aren’t the be-all and end-all from an SEO perspective, it’s always worthwhile to have a clean one, free of redirects or broken links. Here’s how you can check your XML sitemap with R:

sitemapTestR <- function(x){
  
  require(rvest)
  
  require(httr)
  
  sitemap_html <- content(GET(x), "text")
  
  sitemap <- read_html(sitemap_html)
  
  urls <- sitemap %>% html_nodes("loc") %>% 
    html_text()
  
  results <- data.frame(url = character(), status_code = integer(),
                        stringsAsFactors = FALSE)
  
  for (url in urls) {
    response <- GET(url)
    status_code <- status_code(response)    
    results <- rbind(results, data.frame(url = url, status_code = status_code, 
                                         stringsAsFactors = FALSE))
  }
  
  output <- results
  
}

It’s not the smallest of functions, but it works well.

How The SitemapTestR Function Works

Since this function is a bit of a beast and has a couple of elements we’ve not covered yet, I thought it was worth giving it its own section. As always, let’s break it down:

• sitemapTestR <- function(x){: As before, we’re naming our function – sitemapTestR in this case (see what I did there?), it’s got a single variable called “x” and we’re opening our braces to contain our commands
• require(: Again, we’re telling R that this function depends on the httr and rvest packages
• sitemap_html <- content(GET(x), “text”): We’re using functions from the httr package to run a GET request to request data from the server – the contents of the sitemap in this case, and we’re storing it as a text object
• sitemap <- read_html(sitemap_html): This part uses the read_html function of the rvest package to take the text we’ve just downloaded and turn it into html
• urls <- sitemap %>% html_nodes(“loc”) %>%  html_text(): One of the great things about the Tidyverse and its associates is the ability to chain commands using %>%. In this example, we’re using it to say we want to create an object called “urls” using our “sitemap” object as the dataset, we’re using rvests html_nodes function (a command that pulls from the “nodes” of our sitemap – the markers called “loc”, which are the URLs in the XML sitemap) and take only the text within, which is what the html_text command does
• results <- data.frame(url = character(), status_code = integer(), stringsAsFactors = FALSE): This creates an empty dataframe called “results”, with the headers “url” and “status_code”, which are a character and integer respectively. We don’t want our strings to be seen as factors
• for (url in urls){: Here’s where we start to have some fun. We’re nesting a for loop inside our function. I’ve used loops in a couple of my other R language posts, but this is the first time we’ve used one in this series. There will be more to follow on loops in a later post, but here, we’re starting our loop with for, saying “for each url in the urls object, perform the commands below”. Similar to a function, these commands are contained in braces
• response <- GET(url): Using the httr packages’ GET request function, we’re putting the URL into an object called “response”
• status_code <- status_code(response): With our trusty httr package, our URLs status code is tested and added to an object called “status_code”
• results <- rbind(results, data.frame(url = url, status_code = status_code, stringsAsFactors = FALSE))}: Finally, the result of this test is added to our results dataframe with the URL entering the url column and the status code going into the status_code column, with no strings being factors. The rbind command says to merge them into a single frame as our loop runs these commands over every URL in our urls object and our closing brace finishes the loop
• output <- results}: And we’re finally finished with our function. Our output is the output frame from our results element of the loop and our closing brace finishes the function

Phew! I told you it was a beast of a function, but this is part of why we would use a function rather than interactive code. If you had a situation where you had to test several sitemaps, this could amount to hundreds of lines of code and hours of work, whereas this function allows you to run the same commands in a lot less time and with a lot less code.

Running The SitemapTestR Function

To run it, we need to create our x variable, which is the URL of our sitemap. Let’s use my posts sitemap as an example:

sitemap_url <- "https://www.ben-johnston.co.uk/post-sitemap.xml"

And to run it, we would use the following command:

sitemapStatus <- sitemapTestR(sitemap_url)

Give it a few seconds to run, and you’ll see the following in your environment:

Explore it with the str(sitemapStatus) command, and you should see the below:

And of course, you can export it to csv, with our trusty write.csv command.

write.csv(sitemapStatus, "sitemapStatus.csv")

And there we have it. Your XML sitemap tested in an R function. It’s certainly saved me some hassle over the years, and I hope it helps you too.

Strip URLs To Domains In R

This is a function that I use quite a lot, especially when doing link or competitor analysis using APIs in R (which we’ll cover in a couple of pieces time).

With this function, we take a URL from another column and strip it down to the domain using a very simple regular expression. This function has served me well for many years and uses an sapply method to run through a series of URLs. The function is as follows:

domainNames <- function(x){
  
  strsplit(gsub("http://|https://|www\\.", "", x), "/")[[c(1, 1)]]
  
}

But we need some test data to run it on. If you go here, you’ll find a spreadsheet with the top-ranking URLs for the term “TV Units” from SE Ranking, which you can use for a test.

Stripping URLs To Domains With Our Test Data

Firstly, download the test data above and place it in your project folder.

Now read it in using the read.csv command as follows:

tvUnits <- read.csv(“tvUnits.csv”, stringsAsFactors = FALSE)

As I say, we’ll be covering how to pull this in directly from SE Ranking in a couple of weeks, but for now, this will work.

Now, after you’ve read your function into your R console, you can use the following command to strip the ranking URLs to domains in a new column in your dataset:

tvUnits$Domain <- sapply(tvUnits$URL, domainNames)

If we look at it using the str() command, we’ll see the following:

And there we have it. URLs stripped to domains using a fairly simple R function with no additional packages.

We’ll be talking about sapply and other apply methods in R in a few weeks, but for now, this means a “simple apply” and isn’t too dissimilar to a loop, in that it applies our function to every element in our list of URLs.

How It Works

While this function isn’t as large as our sitemap tester, there are still a couple of areas that we’ve not covered yet, so again, worth having its own section.

Let’s break it down.

• domainNames <- function(x){: As always, we’re creating a new function called domainNames and it has a variable called x
• strsplit(gsub(: We’re using the strsplit and gsub functions from base R. Strsplit – or “string split” means to break up a part of a string of text or numbers (our URL in this case) and gsub is effectively R’s way of invoking the classic find and replace
• “http://|https://|www\\.”, “”, x), “/”)[[c(1, 1)]]}: This intimidating-looking piece is actually a relatively simple regular expression which looks for everything before or after the domain name, including the http(s)://, the optional www. and everything after the domain name. Regular expressions are a great addition to any SEO’s skillset, either in your R journey or in general as they can be used for so many different elements. If you’d like to learn more about them, I love using the Slash\Escape game by Robin Lord as a training exercise

And that’s how you strip URLs to domains using R. Simple, elegant and very quick to run. It’s been very useful for me over the years and I hope it helps you too.

Wrapping Up

We’ve been on quite a journey today, haven’t we? Hopefully this has given you a good introduction to using R functions, particularly for SEO. There’re going to be quite a lot of functions in the next half of this series, so it’s worth becoming familiar with them.

I hope you’ve found this useful and that you’ll join me next time for part 5 where we’ll start replicating common Excel formulae in R. I promise this one won’t take me as long to post!

Our Code From Today

# First Function

firstFun <- function(x,y){
  
  x*y

}

firstFun(5,2)

## First Function As An Object

firstFunRun <- firstFun(5,2)

# Second Function

secondFun <- function(x,y){
  
  val1 <- x*y
  
  val2 <- x+y
  
  val3 <- x-y
  
  val4 <- x/y
  
  output <- data.frame(val1,val2,val3,val4)

}

secFunRun <- secondFun(5,2)

## Add Column Names To Second Function

secondFun <- function(x,y){
  
  val1 <- x*y
  
  val2 <- x+y
  
  val3 <- x-y
  
  val4 <- x/y
  
  output <- data.frame(val1,val2,val3,val4)
  
  colnames(output) <- c("multiply", "add", "subtract", "divide")
  
  return(output)
  
}

secFunRun <- secondFun(5,2)

# Google Analytics & Search 

ga_auth()

scr_auth()

gaSCR <- function(x, y, z, a){
  
  require(googleAnalyticsR)
  
  require(searchConsoleR)
  
  require(tidyverse)
  
  ga_data <- google_analytics(viewId = x,
                              date_range = c(z, a),
                              metrics = c("sessions", "users", "pageviews", "bouncerate"),
                              dimensions = "date")
  
  sc_data <- search_analytics(site = y,
                              start_date = z,
                              end_date = a,
                              dimensions = c("date"),
                              metrics = c("impressions", "clicks"))
  
  merged_data <- merge(ga_data, sc_data, by = "date", all = TRUE)
  
  merged_data$ctr <- (merged_data$clicks / merged_data$impressions) * 100
  
  return(merged_data)
  
}

## Using GA/ GSC Function

view_id <- "GOOGLE-ANALYTICS-VIEW-ID"
site_url <- "YOUR-WEBSITE-URL"
start_date <- "YOUR-START-DATE"
end_date <- "YOUR-END-DATE"

googleData <- gaSCR(view_id, site_url, start_date, end_date)

# Merge Multiple CSV Files

csvMerge <- function(x){
  
  require(plyr)
  
  csvFiles <- dir(pattern = x, full.names=TRUE)
  
  output <- ldply(csvFiles, read.csv)
  
}

csvFiles <- csvMerge("csv$")

mergedCSV <- write.csv(csvFiles, "merged csvs.csv")

# Scrape & Test XML Sitemaps

sitemapTestR <- function(x){
  
  require(rvest)
  
  require(httr)
  
  sitemap_html <- content(GET(x), "text")
  
  sitemap <- read_html(sitemap_html)
  
  urls <- sitemap %>% html_nodes("loc") %>% 
    html_text()
  
  results <- data.frame(url = character(), status_code = integer(),
                        stringsAsFactors = FALSE)
  
  for (url in urls) {
    response <- GET(url)
    status_code <- status_code(response)    
    results <- rbind(results, data.frame(url = url, status_code = status_code, 
                                         stringsAsFactors = FALSE))
  }
  
  output <- results
  
}

sitemap_url <- "https://www.ben-johnston.co.uk/post-sitemap.xml"

sitemapStatus <- sitemapTestR(sitemap_url)

write.csv(sitemapStatus, "sitemapStatus.csv")

# Strip URLs To Domain Names

domainNames <- function(x){
  
  strsplit(gsub("http://|https://|www\\.", "", x), "/")[[c(1, 1)]]
  
}

tvUnits <- read.csv("tvUnits.csv", stringsAsFactors = FALSE)

tvUnits$domain <- sapply(tvUnits$URL, domainNames)

write.csv(tvUnits, "tvUnitsDomain.csv")

This post was written by Ben Johnston on Ben Johnston

Welcome back. You’ve made it to part three, where we’re going to start having a bit of fun with the R language and SEO data. Hopefully the first couple of parts gave you a bit of a grounding in the basics, how to use R packages and how to get Google Analytics and Search Console data in R. Today, we’re going to do some simple visualisation work on that data using the GGPlot2 package. 

In today’s piece, we’re going to use the GGPlot2 package and some Google Analytics and Search Console data to make basic bar charts, line graphs, a combo plot and finally a really cool wordcloud with our search queries.

I know today’s piece is super late. Work has been crazy and I’ve been moving house, which comes with its own fun challenges.

Ready to get started? Alright, let’s do this.

The GGPlot2 Package And The Tidyverse

In part 2, we discussed the Tidyverse series of packages and how great it is. GGPlot2 is part of that package, so we’ll be initialising it that way. 

GGPlot2 is the industry standard for visualising data using R and, truthfully, I’ve not come across a better package for this purpose. There are debates between GGPlot2 and MatPlotLib for Python, but personally, I tend to skew towards GGPlot2’s Python equivalent plotnine since it uses the same Grammar of Graphics approach and it’s just familiar when I use Python. 

If you feel you’re going to be doing a lot of data visualisation during your R journey, it’s really worth getting familiar with the concepts behind the Grammar of Graphics, so there’s some further reading for you. I’m not going to go into it here, today’s all about the mechanics of using GGPlot2 with Google Analytics and Search Console data so we can make some SEO-specific graphs. 

First, let’s start by installing the packages we’re going to use today.

Installing Our Packages 

Today, we’re going to use the following packages: 

• Tidyverse: As mentioned before, GGPlot2 is included in this, but it’s always worth having the other packages installed as well 
• googleAnalyticsR: We ran through this in the last part, but this is the standard package for pulling Google Analtyics data directly into R 
• googleSearchConsoleR: Again, we covered this last time, but this is how we get Google Search Console data into our R environment 
• wordcloud: The standard package for creating wordclouds using R 
• tm: My favourite package for text mining and cleaning  

Now we know which packages we’re going to use for this exercise, let’s try and be efficient and install them all at once.

instPacks <- c("tidyverse", "googleAnalyticsR", "searchConsoleR", "googleAuthR", "tm", "wordcloud")

lapply(instPacks, require, character.only = TRUE)

Now we’ve got our packages installed, follow the authorisation steps from the previous piece and we can start getting some SEO data to visualise.

Our Datasets 

We’re going to create four initial datasets for our first graphs: 

• Organic sessions over last 30 days: We’ll use this to build our very first line graph 
• Organic sessions over last 30 days compared to previous period: Our second graph will have two lines, comparing the last 30 days to the previous 30 days 
• Impressions over last 30 days: We’ll use this for our first bar chart 
• Impressions & clicks over last 30 days: This will build our first combo chart with dual axes 

Let’s get our Google Analytics datasets for our initial visualisations.

Google Analytics Datasets For Our Visualisations

As we covered in the last part, let’s pull some basic Google Analytics data using the organic segment and breaking it down by date. Remember to go through the authorisation steps and to replicate the View ID/ segment identification stages for your own data. I’ve replaced my IDs with X for the purposes of this piece.

ga_auth()

gaAccounts <- ga_account_list()

viewID <- gaAccounts$viewId[X]

orgSegment <- segment_ga4("orgSegment", segment_id = "gaid::-X")

ga30Days <- google_analytics(viewID, date_range =c("30DaysAgo","yesterday"), metrics = c("sessions"), dimensions = "date",segment= orgSegment)

This is the basic one that we’ll use for our very first visualisation, but we’ll also want to use a comparison range for our second, so let’s get that data as well.

GADataComparison <- google_analytics(viewID, date_range =c("60DaysAgo", "31DaysAgo", "30DaysAgo","yesterday"), metrics = c("sessions"), dimensions = "date", segment= orgSegment)

Now we’ve got everything we need from Google Analytics, let’s get the datasets we need from Google Search Console to visualise.

Google Search Console Datasets

First, we want to get our data split out over the last 30 days. With the way googleSearchConsoleR works, we can get it all in one call, which is pretty handy, considering the graphs we want to make from it. We just need the following commands, including the authorisation steps we talked about in the last piece:

scr_auth()

scDataByDate <- search_analytics("YOUR SITE", startDate = Sys.Date() -30, Sys.Date() -1, searchType = "web", dimensions = "date")

This call will actually give use what we need for both the Google Search Console graphs we’re going to build, so this is nice and easy. Obviously replace “YOUR SITE” with your site, but don’t forget the speech marks.

OK, now we’ve finally got our data, let’s start building some graphs.

GGPlot2 For SEO 

GGPlot2 is a very powerful and flexible graphing library, and you can make pretty much any data visualisation you can imagine with it, once you learn how it works. Data Visualisation With R by Thomas Rahlf is a fantastic resource to expand on this, if data visualisation is your jam.

A Basic GGPlot2 Line Graph With Google Analytics Data 

Firstly, we want to use our Google Analytics dataframe to give us a simple line graph of the last 30 days of organic search data. This will be our first GGPlot2 visualisation command in R, so I’ll break it down afterwards.

ggplot(data=ga30Days, aes(x=date, y=sessions)) +
  geom_line(colour="darkgreen", stat="identity") + xlab("Date") + ylab("Sessions")

This gives us the following graph:

We’ll talk about how to export GGPlot2 graphs from RStudio later, but for now, let’s break that command down so you can see how we put this graph together.

• ggplot(data=ga30Days, aes(x=date, y=sessions)): We’re invoking the ggplot command, telling it what data we want to use (our ga30Days set in this case) and telling it which axes it should use
• +: This is how we break up a command across multiple lines in R so that we can get it all on one screen
• geom_line(colour=”darkgreen”: We’re using the line graph method of visualising this chart and we’re using dark green for the colour. In the EN-US language, you’d be using “color”
• , stat=”identity”): We’re basing the scale on the numbers of the dataset
• + xlab(“Date”) + ylab(“Sessions”): We’re labelling the X and Y axes according to our datasets. “Date” and “Sessions” in this case

Now we’ve got a bit of an understanding of how to put a chart together in GGPlot2, let’s start expanding them with a secondary series. 

Creating Comparison Line Graphs With Google Analytics Data In R

Now we know how to create a single line graph in R with GGPlot2, we should think about adding comparison lines so our SEO reports can show changes. Here’s how we can do that. 

First we need to do a little bit of preparation on our data so the two lines can be layered against the same date range. We’ll do that with subsetting and creating a new frame so the two date ranges are a clear comparison.

GADataComparisonLast30 <- GADataComparison$sessions.d2[31:60]

GADataComparisonPrev30 <- GADataComparison$sessions.d1[1:30]

GADataComparisonVis <- data.frame(GADataComparison$date[31:60], GADataComparisonLast30, GADataComparisonPrev30)

colnames(GADataComparisonVis) <- c("Date", "Sessions Last 30 Days", "Sessions Previous 30 Days")

Now we can add a second line to our chart like so:

ggplot(data=GADataComparisonVis) +
  geom_line(aes(y=GADataComparisonLast30, x=Date), colour="darkgreen") +
  geom_line(aes(y=GADataComparisondPrev30, x=Date), colour="black")

And that’ll give us the following chart:

You can see that we’ve added the colours parameter to our original command which adds the secondary line and I’ve used the “darkgreen” colour for it, because it matches my site’s theme.

And that’s a really simple introduction to line charts using Google Analytics data in GGPlot2.

Building Bar Charts With Search Console Data In GGPlot2 

Now we’ve learned how to build basic line graphs, let’s use our Google Search Console dataset to build up a bar chart of impressions over the last 30 days with GGPlot2.

Here’s how to do that:

ggplot(data=scDataByDate) +
 geom_col(aes(date, impressions), size = 1, colour = "black", fill = "darkgrey")

This will give us the following graph (again, your numbers will be different and almost certainly higher):

As before, let’s break the command down:

• ggplot(data=scDataByDate): As before, we’re calling the ggplot command and saying what dataset we’re focusing on
• geom_col(aes(date, impressions): In this case, we’re using the geom_col command to tell ggplot that we’re using a column graph, and the axes are date and impressions
• size = 1, colour = “black”, fill = “darkgrey”): These are styling commands, where we’re saying that we want our bar size to be standard (you can play with this a lot when you have different size datasets) and we want the outline colour to be black and the fill colour to be dark grey

As you can see, there’s not much difference between geom_line and geom_bar. The main changes are that we’re adding a fill parameter for the columns. 

So that’s how we can build a basic bar chart in GGPlot2 using Google Search Console impression data. Now let’s build up a combo chart of clicks and impressions.

Building A Combo Chart Of Google Search Console Impressions & Clicks In GGPlot2

We covered this a bit in my post about Keyword Clustering for SEO, but I thought it was worth including and expanding in today’s piece.

What we’re going to do here is create a bar chart of impressions as above, but also add a line chart of clicks on a secondary axis. A quick disclaimer is that us analysts generally hate combo charts like this because they make it very easy for the end user to misinterpret the data, but it’s something that people ask a lot, so it’s worth covering for this specific use case.

Use the following command:

ggplot(scDataByDate) +
  geom_col(aes(date, impressions), size = 1, colour = "black", fill = "darkgrey") +
  geom_line(aes(date, 50*clicks), size = 1, colour = "darkgreen", group =1) +
  scale_y_continuous(sec.axis = sec_axis(~./50, name = "clicks"))

And this will give the following chart using my dataset: 

Let’s break it down:

• ggplot(scDataByDate): As always, we’re calling the ggplot command and defining the dataset
• geom_col(aes(date, impressions), size = 1, colour = “black”, fill = “darkgrey”): As before, we’re building our bar chart
• geom_line(aes(date, 50*clicks), size = 1, colour = “darkgreen”, group =1) + scale_y_continuous(sec.axis = sec_axis(~./50, name = “clicks”)): Here, we’re building our secondary axis for clicks. Excel or other tools will usually scale this for you automatically, but with R, we have to do that manually. Since my CTR over this time period wasn’t great compared to my visibility, I’ve scaled it to 50*. Assuming you spend more time on your own site, you can adapt accordingly

There we have it: a quick guide to building combo charts in R using Google Search Console’s impressions and clicks data.

Now we’re going to do our final visualisation of this piece: a wordcloud of our search queries using Google Search Console data.

Creating Wordclouds In R Using Google Search Console Data

We’re going to use the wordcloud R package for this part, rather than GGPlot2, but it’s still fairly simple. What we’re going to do here is create a wordcloud using our Google Search Console queries over the last 30 days.

Firstly, as always, we’ve got to prepare our data:

Google Search Console Query Data In R

As before when we added the date dimension to our googleSearchConsoleR query, we’re going to break it down with the query dimension. 

Here’s how to do that:

scDataByQuery <- search_analytics("YOUR SITE", startDate = Sys.Date() -30, Sys.Date() -1, searchType = "web", dimensions = "query")

As you can see, the only difference between this query and the one we used in part 2 is that we changed the dimension parameter to “query” instead of “date”, meaning it’s broken down by search term instead of by date. 

Now, similar to the Keyword Clustering post, we’ve got to clean up our dataset so it can be easily interpreted and remove potential duplicates, which means turning it into a corpus – a “bag of words”, essentially, and then making sure it’s tidied up.

Creating & Cleaning A Text Corpus In R

You’re figuring out that there’s always some data-prep work to do before making your visualisations now, right?

Firstly, we’ve got to use the tm (text mining) package to create a corpus from our Search Console queries. We already installed tm at the start of this piece, so now we just need to run the following command:

queryCorpus <- Corpus(VectorSource(scDataByQuery$query))

Now if you explore that using the “queryCorpus” command, you’ll get the following:

Not especially helpful at this point, right? And certainly not ready for a wordcloud.

That’s why we have to clean it up.

Why We Clean Text Corpuses

Text analysis is one of the main things I do with R, and something I’ve done for a lot of my SEO clients over the years. There’s a lot to go into with it and I’m not going to run through it all in this series, but hopefully this very brief introduction will whet your appetite to explore it further. 

If it does, the book Text Mining With R by Julia Silge and David Robinson is a fantastic resource and well worth a read. 

Essentially, the reason we clean a corpus is to avoid duplication and miscounting. In a pure corpus, a word beginning with a capital and fully lowercase will be counted separately, as will plurals and the many variations that can come along with that. You’ll also find that “stopwords” such as “and”, “the” and so on will be counted, and they’ll probably outnumber the words we want to focus on. When we clean a corpus, we eliminate this duplication by doing the following: 

• Make everything lowercase: This ensures consistency of our words, eliminating duplicates caused by capitalisation
• Remove punctuation: Punctuation marks can cause further duplication or variations of words which we don’t want 
• “Stem” our words: This removes extensions, thus removing duplicates caused by pluralisation. Some of the words may look a little strange, but it’s still easy to figure them out 
• Remove “stopwords”: We want to keep our key terms here rather than including words like “the” and “and” and so on. These will skew our text numbers 

OK, now we know what we’re going to do, let’s run it. We’re going to have a few commands to use here. I showed you how to combine it into a single R function in my Keyword Clustering post, but for now, let’s break it down:

Making A Corpus Lowercase In R

We’re going to use our Google Search Console query corpus that we created in the last step to run the tolower command from the tm R package, like so. You’ll see that we’re rewriting our initial queryCorpus dataset to do this.

queryCorpus <- tm_map(queryCorpus, tolower)

Simple as that. Now our entire Search Console query corpus is in lowercase.

Removing Punctuation In An R Corpus

Removing punctuation is an important step in text analysis. Punctuation can cause unwanted variants, such as hyphenated words being counted as one and in general, we don’t want it around when we’re analysing text. Fortunately, removing it is as simple as one single command, thanks to the tm package:

queryCorpus <- tm_map(queryCorpus, removePunctuation)

And now we’ve gotten rid of any punctuation elements from our Search Console queries.

Removing Stopwords In An R Corpus

For the next stage of our data preparation, we need to remove the stopwords from our corpus, words like “if”, “and”, “it”, “the” and so on; words that will interfere with our analysis of our query data.

We can do that with the following command:

queryCorpus <- tm_map(queryCorpus, removeWords,(stopwords("english")))

We’re almost there with cleaning our corpus. Now we just need to take the words down to their stems to remove pluralisation duplication.

Stemming Words In An R Corpus

Finally, we need to remove all the possible duplications caused by pluralisation and the many different ways that people can pluralise search terms. Again, using the tm package, we can use the following command:

queryCorpus <- tm_map(queryCorpus, stemDocument)

We’re still rewriting our original corpus, but now all the words will be in lowercase, punctuation and stopwords are gone and all the possible extensions have been removed through taking the words down to their stems.

If you’ve followed all these steps, you’ll have a clean corpus of your Google Search Console query data and we’re ready to move on to create our wordcloud.

Our Wordcloud With Google Search Console Query Data In R 

I know it can feel like a lot of heavy lifting to get to this point, with all the data cleaning and preparation, but I promise it’ll get easier when we follow the steps in the next few sessions, particularly the piece when we discuss functions. But now, we’re ready to create our wordcloud with our Google Search Console query data. 

We can do that with the following command using the wordcloud R package, which we installed earlier:

wordcloud(queryCorpus, scale=c(3,0.4), max.words=350, random.order=FALSE, 
          rot.per=0.35, use.r.layout=FALSE, colors=brewer.pal(8,"Dark2"))

Obviously your output will be different, since your site will have different queries, but you’ll get something similar to this:

Now we know how to create a wordcloud in R, let’s break down the command so you can edit it in the future:

• wordcloud(queryCorpus: We’re calling the wordcloud command from our wordcloud package and using the queryCorpus dataset
• scale=c(3,0.4), max.words=350, random.order=FALSE: We’re putting a small scale on the image (you can adapt as required), putting a limit on the maximum words of 350 and saying that we don’t want to randominse the order of words
• rot.per=0.35, use.r.layout=FALSE, colors=brewer.pal(8,”Dark2″)): We’re putting a 0.35 level of rotation on the visualisation and using the RColorBrewer’s Dark2 colour scheme

Now we’ve had a bit of fun with SEO data visualisation in R, let’s get those graphics exported to use in reports or presentations.

Exporting Data Visualisations In RStudio

Saving your R plots to graphics files like JPG or PNG to use in your reports or presentations is really easy with RStudio. You just go to your plot window and click the “export” button, like so:

You’ll get a few options around the file type. I typically go for PNG so the filesize is small, but you can use whatever works for you.

Now you’ll have a download of your plot in your chosen file type in your R project directory.

Wrapping Up

And there we have it. A simple introduction to using GGPlot2 and wordcloud to visualise your SEO data using R. As always, if you’ve got any questions, hit me up on Twitter and be sure to sign up to my mailing list to get notified of my next piece of content. 

Next time, we’re going to start taking R to the next level and look at functions. I hope you’ll join me.

Our Code From Today

# Install Packages

instPacks <- c("tidyverse", "googleAnalyticsR", "searchConsoleR", "googleAuthR", "tm", "wordcloud")

lapply(instPacks, require, character.only = TRUE)

# Authorise Google Analytics

ga_auth()

gaAccounts <- ga_account_list()

viewID <- gaAccounts$viewId[7]

# Get Google Analtyics Data

orgSegment <- segment_ga4("orgSegment", segment_id = "gaid::-5")

ga30Days <- google_analytics(viewID, date_range =c("30DaysAgo","yesterday"), metrics = "sessions",
                             dimensions = "date",segment= orgSegment)

GADataComparison <- google_analytics(viewID, date_range =c("60DaysAgo", "31DaysAgo", "30DaysAgo","yesterday"), metrics = "sessions",
                                     dimensions = "date",segment= orgSegment)

# Get Search Console Data

scr_auth()

scDataByDate <- search_analytics("YOUR SITE", startDate = Sys.Date() -30, Sys.Date() -1, searchType = "web", dimensions = "date")

# Basic Line Graph With 30 Days Data

ggplot(data=ga30Days, aes(x=date, y=sessions)) +
  geom_line(colour="darkgreen", stat="identity") + xlab("Date") + ylab("Sessions")

## Comparison Data

GADataComparisonLast30 <- GADataComparison$sessions.d2[31:60]

GADataComparisonPrev30 <- GADataComparison$sessions.d1[1:30]

GADataComparisonVis <- data.frame(GADataComparison$date[31:60], GADataComparisonLast30, GADataComparisonPrev30)

colnames(GADataComparisonVis) <- c("Date", "Sessions Last 30 Days", "Sessions Previous 30 Days")

## Comparison Line Chart

ggplot(data=GADataComparisonVis) +
  geom_line(aes(y=GADataComparisonLast30, x=Date), colour="darkgreen") +
  geom_line(aes(y=GADataComparisondPrev30, x=Date), colour="black")

# Bar Chart From SC Impressions

ggplot(data=scDataByDate) +
 geom_col(aes(date, impressions), size = 1, colour = "black", fill = "darkgrey")

## Combo Chart With SC Impressions & Clicks

ggplot(scDataByDate)+
  geom_col(aes(date, impressions), size = 1, colour = "black", fill = "darkgrey")+
  geom_line(aes(date, 50*clicks), size = 1, colour = "darkgreen", group =1)+
  scale_y_continuous(sec.axis = sec_axis(~./50, name = "clicks"))

# Wordcloud With Search Console Queries

## Get Query Data

scDataByQuery <- search_analytics("YOUR SITE", startDate = Sys.Date() -30, Sys.Date() -1, searchType = "web", dimensions = "query")

## Convert Queries To Corpus

queryCorpus <- Corpus(VectorSource(scDataByQuery$query))

## Convert Corpus To Lower Case

queryCorpus <- tm_map(queryCorpus, tolower)

## Remove Punctuation

queryCorpus <- tm_map(queryCorpus removePunctuation)

## Remove Stopwords

queryCorpus <- tm_map(queryCorpus, removeWords,(stopwords("english")))

## Stem Words

queryCorpus <- tm_map(queryCorpus, stemDocument)

## Create Wordcloud

wordcloud(queryCorpus, scale=c(3,0.4), max.words=350, random.order=FALSE, 
          rot.per=0.35, use.r.layout=FALSE, colors=brewer.pal(8,"Dark2"))

This post was written by Ben Johnston on Ben Johnston

Hello and welcome to part two of my series on using the R programming language for SEO. Hopefully you enjoyed part one and are raring to go with today’s piece. Today, we’re going to be looking at R packages and as an example, we’re going to be using packages to directly pull in data from Google Analytics and Google Search Console. That’s right, no more downloading CSV exports and importing them.

If you find this useful, please consider giving it a share on your social networks and if you’d like to be kept up to date with my latest posts on this site, sign up for my free mailing list. No spam, no sales pitches, just updated content whenever it’s ready.

What’s Your Email Address? *

If you are human, leave this field blank.

Δ

R Packages

I’ve mentioned this before in some of my other R blog posts, but packages are essentially plugins or extensions for the language. They’re a series of custom-built functions that are wrapped up and shared into one easily installable bundle and they’re often the quickest way to solve problems. 

There are over 18,000 packages available on CRAN (the official R repository) and thousands more on GitHub and they’re a fantastic way to share or reuse your code. Although I won’t be covering how to build one in this series, it may be something I go back to later. It’s actually easy and I recommend creating a personal R package if you find you’ve got a lot of functions that you re-use. I have one myself and it’s saved me so much time. 

As I say, I won’t be covering building your own R package in this piece, but there are several excellent resources below that you may want to check out as you get further on in your R journey:

• The Rstudio Package Dev Guide
• Hilary Parker’s Guide To Building A Personal R Package
• Hadley Wickham’s Advanced R Programming Guide To Packages

OK, now we know what an R package is, let’s install one.

The Tidyverse Package

With over 18,000 official R packages available, it can be a bit tricky to know where to start. As you get further into your R journey and start facing specific problems, you’ll become very familiar with Google and Stack Overflow. Nine times out of ten, you’ll find the answer there. 

But for today, let’s install one package (or more accurately, a group of packages) that I almost never start an R project without: the incredible Hadley Wickham’s Tidyverse package. 

I’ve long been an admirer of Mr Wickham’s work, particularly his dedication to making the R universe a better, tidier place and the Tidyverse package is essential. It incorporates the following other packages, which we’ll be becoming very familiar with the further we go through this series: 

• ggpot2: The essential graphics and visualisation package for R
• dplyr: One of the most common and easiest to use data manipulation packages. We’ll be using functions from this one a lot
• tidyr: A fantastic series of functions for tidying up data sets. The more you do with R, the more familiar you’ll become with having to tidy up data, so this will become something you’ll use quite regularly
• readr: A package for reading rectangular data like CSVs, TSVs and other similar files
• purrr: A series of functions to help making working with functions and vectors easier
• tibble: Possibly the most annoying package ever created for people who don’t write clean code! Tibble is a new way of working with dataframes which, in Wickham’s own words “Does less and complains more” forcing you to write cleaner, more expressive code
• stringr: Another essential package for SEOs since we work with text data (strings) quite a bit and need quick, easy functions to deal with that
• forcats: This package offers a series of functions to make dealing with factors better or, frankly, tolerable

As you can see, there’s a lot in the Tidyverse that we’ll be using quite a bit as we go on through this series. So let’s get this installed for our first package installation.

Installing R Packages 

Installing R packages is really simple once you know the name of the one you want to install. 

As mentioned in the previous post on the basics of R, it’s always best practice to put your package installations into your .R file at the top of RStudio just so you can run the whole code back without trying to remember what packages you used. There are other options like Packrat, but since I work out of my Dropbox most of the time, this causes more problems than it solves for me, so I tend to just keep them at the top of my .R file. 

In your text area, type the following: 

install.packages("tidyverse")

Remember that R is case sensitive, so you’ve got to be accurate or else you’ll get an error. 

Now add the following: 

library(tidyverse)

Now paste these two commands into your RStudio console. You’ll see the following:

That’s it. Your Tidyverse package is installed and activated. The “install.packages” command downloads the package to your R environment and the “library” command activates it.  

Unless you update your R installation, the installed package will stay installed and you’ll only need the “library” command to initialise it, but I recommend reinstalling regularly in case there have been updates. 

Installing Multiple R Packages At Once

It’s nice to install our R packages as and when we need them, but if we know what packages we’ll be using at the start of our project, wouldn’t it be nice to cut down the lines we need to write? Here’s how you can install several R packages at once rather than numerous separate lines of code. 

Firstly, we need to create a data frame containing the names of all the packages that we want to install. We’ll talk through the c( command in more detail shortly, but for now, just know it means “combine” and we’re going to create a list of the packages we want to install for this piece. 

Rather than typing install.packages(“package”), library(package) like we did for the Tidyverse for every single package we want to use, if we know the packages we’re going to work with for our project, we can create a list like so:

instPacks <- c("tidyverse", "googleAnalyticsR", "searchConsoleR", "googleAuthR")

And then we can initalise them by calling the list with the Require command like so:

lapply(instPacks, require, character.only = TRUE)

If you know what packages you’re going to use at the start of your R project, or if you’re going to be running the same analysis for multiple clients, this is a great way to save time and make your code more efficient. It doesn’t work with every package, especially if you’re using them for the first time and there are authorisation requirements, or they’re coming from GitHub, but it’s always worth a try.

Now we’ve got our packages installed, let’s start getting some Google Analytics data directly into R using the googleAnalyticsR package.

Using Google Analytics In R 

Now we’ve got our packages installed, we have to authenticate the googleAnalyticsR package with our Google Analytics account.

Here’s how to do that:

Authenticating GoogleAnalyticsR 

First, type: 

ga_auth()

This will open a browser window with the following dialogue:

I’ve had some issues with this when the R version isn’t up to date, so make sure you’re always using the latest version of the language. 

Make the following selections: 

It will give you an authorisation code. Paste that into your R console 

There you go, your R session is linked up with Google Analytics. Let’s get some data.

Querying Google Analytics Data In R With GoogleAnalyticsR 

First, we need to create a dataset with our list of Google Analytics accounts and views. You can do that with the following command: 

gaAccounts <- ga_account_list()

Now if you type

gaAccounts

in your console, you’ll get the full list of Google Analtyics accounts and views that are associated with the verified login you used. It’ll look something like this:

If you have multiple views and accounts in your Google Analytics account the way I do, you’re going to want to create an object with the view you want to work with. By looking at the table above (your own Google Analytics data will differ, obviously), we want to use the skills we learned in the previous post to explore our data and create an object in our R environment with it. 

When you look through the output below, you’ll be able to identify the row number of the Google Analytics View you want to focus on. Once you know the row number, type the following: 

viewID <- gaAccounts$viewId[ROW NUMBER]

Here, we’re creating a new R dataset with the specific row that we want to focus on from the viewID variable. When you look at the gaAccounts dataset, you should be able to identify the row number from the farthest left column. Put that number in the square brackets and your viewID data will be created, meaning you can use that instead of putting the specific number in with every API call. 

Now we have that sorted, let’s make our first Google Analytics query in R.

Our First GoogleAnalyticsR Query

For a test, let’s just get sessions over the last seven days.

testData <- google_analytics(viewID, date_range = c("2022-02-01","2022-02-08"), metrics = "sessions")

At the time of writing this piece, that was the seven-day date range. Your mileage may well vary, but you can hopefully see how this works. Let’s break it down: 

• testData <-: This is the name of our dataset. Since it’s just a test to make sure our connection is working properly, we’re just calling it “testData” for now 
• google_analytics(: We’re telling R that we want to use the google_analytics function from the googleAnalyticsR package, we’re going to be downloading data from Google Analytics
• viewID: We’re invoking the viewID data that we created in the previous section, telling googleAnalyticsR what Google Analytics view we want to query 
• date_range =c(: The date range we want to query. The c( command means “Combine”, something we’ll be using a lot more as we go. In this case, we’re going to combine our start and end dates 
• metrics=: The metrics from Google Analytics that we want to get our data from. In the case of our first query, we just want to get “sessions”, but in the next section, we’ll be gathering more 

If everything’s gone to plan, typing the following into your console: 

testData

Will bring up the following:

Great, so we know it works. Now let’s get some more data and make it SEO-specific.

Multiple Metrics In GoogleAnalyticsR 

First, we want to start building our query to include multiple metrics to look at our website’s performance. 

Let’s get Sessions, Users, Pageviews and Bounce Rate.

GAData <- google_analytics(viewID, date_range =c("2022-02-01","2022-02-08"), metrics = c("sessions", "users", "pageviews","bouncerate"))

Again, there’s that c( command again. In this case, we’re telling Google Analytics that we want to combine all these metrics into a single dataframe. 

If we run it using GAData, we’ll see the following:

Nice to see, but not particularly useful. Let’s start breaking it down a bit with dimensions and segments. 

Adding Segments In GoogleAnalyticsR 

Since this whole series is about using R for SEO, it makes sense that we’d start pulling our Google Analytics data using the organic search segment. Here’s how we can do that. 

There are a couple of steps to this. First, since every Google Analytics account may well be different, we need to find the ID of our organic segment. On top of that, going forwards, you may want to get the IDs of other segments you have, so it makes sense to create a dataframe of them all. You can do that like so:

GASegments <- ga_segment_list()

Now our gaSegments dataset has all the IDs of the segments in our Google Analytics account. Look through that dataset using the gaSegments command in the console to find your ID, or search for it using the following command:

which(GASegments$name == "Organic Traffic")

From here, whichever route we go, we know the ID of our organic search segment. In my case, it’s 5. Now we need to define that segment for googleAnalyticsR. You can do that like so:

orgSegment <- segment_ga4("orgSegment", segment_id = "gaid::-5")

This has been a bit of a mission for the first run, but now we’ve got our organic search segment defined in R, we can work that into our original query like so:

GADataOrg <- google_analytics(viewID, date_range =c("2022-02-01","2022-02-08"), metrics = c("sessions", "users", "pageviews","bouncerate"), segment= orgSegment)

Take a look at it by typing GAData into the console. 

And there we go. We’ve got the same dataframe as before, but using just organic search data. You’ll notice the “Segment” header added to the frame. 

Now let’s break it down by the date dimension so we can see some performance trends.

Adding Dimensions To GoogleAnalyticsR

To break your Google Analytics data down by dimension in R, we just need to add the dimension parameter to our Google Analytics query, like so:

GADataOrgDates <- google_analytics(viewID, date_range =c("2022-02-01","2022-02-08"), metrics = c("sessions", "users", "pageviews","bouncerate"), dimensions = "date", segment= orgSegment)

Now you can see your performance over that date range using the organic segment, but we don’t want to figure out the date ranges manually every time, do we? Let’s see how to make the date ranges dynamic for every time we call it.

This will be important for later pieces in this series, and we’ll be covering other dimensions as well, so if you’re not familiar, it’s worth taking a look at the Google Analytics dimensions list.

Using Dynamic Date Ranges In GoogleAnalyticsR

This is actually quite easy, but wasn’t immediately obvious to me from the documentation when I first started using googleAnalyticsR, so hopefully this helps. 

First, we need to edit our query using the c( parameter, but saying how many days we want to cover using the API shortcuts, like so:

GADataDynamicDates <- google_analytics(viewID, date_range =c("7DaysAgo","yesterday"), metrics = c("sessions", "users", "pageviews","bouncerate"),dimensions = "date", segment= orgSegment)

As you can see, there are options here. There’s a lot of flexibility available if you use the following API shortcuts.

And there we have it, a rolling date dimension in googleAnalyticsR, using the organic search segment. Again, this is something that we’re going to be using a lot more as we go through this series, so it’s worth getting familiar with it now. And be sure to save this dataset, because we’re going to be using this as a basis for the next few posts.

Now let’s take what we’ve learned about using R packages and Google APIs to the Google Search Console R package, searchConsoleR.

Using Google Search Console In R 

The searchConsoleR package has a lot of great features and it’s pretty easy to use as well, so that’s what we’ll use to work with Google Search Console in R. 

As with the googleAnalyticsR package, we need to authorise the R environment with our account. We can do that with the following command:

scr_auth()

As with googleAnalyticsR, it’ll give you an authorisation option in your console window and then open a new browser window. I don’t generally install the httpuv package as I change accounts so often with this, but your mileage may vary.

Allow access to your chosen Google account and you’ll see an authorisation code. Paste this in and you’ll see a message in your console saying you’re authorised. 

OK, now we’re linked up, let’s get some data.

Getting Google Search Console Data With SearchConsoleR 

The searchConsoleR package gives you a wide range of functions, including rewriting your data, deleting sites and much more, which are a bit beyond the scope of this series, since we’re just looking at how we can use R for SEO-specific data analysis, but there’s lots covered in the documentation. 

As an example, let’s get our Google Search Console data for the last seven days. We can do that like so: 

scData <- search_analytics("https://www.ben-johnston.co.uk", startDate = Sys.Date() -7, Sys.Date() -1, searchType = "web")

You’ll get a warning that Search Console data isn’t accurate for the last three days, but it’ll still give you something to work with. If you’ve been working in SEO for more than fifteen minutes, you’ll know that Search Console data is not what you’d describe as reliable at the best of times, so this isn’t a huge shock. That aside, let’s break the query down: 

• scData <-: What we’re naming our dataset
• search_analytics(: We’re calling the search analytics part of the API, allowing us to get Google Search Console performance data 
• “https://www.ben-johnston.co.uk”,: The name of the website we want to work with. You’ll need to put the full URL in there. I’ve used this site for this example
• startDate = Sys.Date() -7,: The Search Console API doesn’t have the same shortcuts as the Google Analytics API, so we need to be a little bit sneaky to get our rolling date ranges. Here, we’re using the Sys.Date() command to get the computer’s date and -7 to tell it to start seven days ago 
• Sys.Date()-1,: As above, we’re telling the API call that the end of our date range is the computer’s date to yesterday 
• searchType = “web”): We’re ending our API call by telling Search Console which type of search we want to look at. Obviously, Search Console breaks the different searches down by search type, so there are options here. I’m just using “web” for this example 

If this runs correctly, you’ll get a dataframe like so:

Handy, right? Much quicker than downloading it manually. But not that helpful if we want to look at performance trends. Let’s look at how we can add dimensions to it.

Using Dimensions In searchConsoleR 

As with the Google Analytics API, we can use dimensions to break our Google Search Console queries down.

As we all know, Google Search Console doesn’t have quite as much flexibility as Google Analytics, but hopefully by using dimensions, you’ll get some ideas of how you can use Google Search Console with R.

The dimensions available in the searchConsoleR package are:

• date
• country
• device
• page
• query
• searchAppearance

Now we have our list, let’s apply a date dimension to our data.

Using Date Dimensions In searchConsoleR 

As with our googleAnalyticsR queries, if we want to add a dimension to our Google Search Console R query, we need to add the “dimension” parameter to our command. We can do that like so: 

scDataByDate <- search_analytics("https://www.ben-johnston.co.uk", startDate = Sys.Date() -7, Sys.Date() -1, searchType = "web", dimensions = "date")

You’ll notice that nothing’s changed apart from adding the “dimensions = “date” parameter to our original call, which we discovered in the previous section. From running this, we’ll get the same dataframe as above, but broken down by date like so:

Wrapping Up 

And there we go. We’ve learned how to install and initialise R packages and had a tour of the main Google Analytics and Google Search Console R packages. Remember to save your script from this piece, because we’re going to be using these datasets in the next few articles. 

As always, if you have any questions, drop me a line on Twitter or through the contact form, and I’ll see you in a few days for the next piece, where we’ll talk about SEO data visualisation in R.

Our Code From Today

## Install Single Package

install.packages("tidyverse")

library(tidyverse)

install.packages("googleAnalyticsR")

library(googleAnalyticsR)

install.packages("searchConsoleR")

library(searchConsoleR)

install.packages("googleAuthR")

library(googleAuthR)

## Installing Multiple Packages

instPacks <- c("tidyverse", "googleAnalyticsR", "searchConsoleR", "googleAuthR")

lapply(instPacks, require, character.only = TRUE)

## Authorise Google Analytics

ga_auth()

## Find Google Analytics Accounts

gaAccounts <- ga_account_list()

viewID <- gaAccounts$viewId[7]

testData <- google_analytics(viewID, date_range = c("2022-02-01","2022-02-08"), metrics = "sessions")

GAData <- google_analytics(viewID, date_range =c("2022-02-01","2022-02-08"), metrics = c("sessions", "users", "pageviews", "bouncerate"))

## Adding Segments

GASegments <- ga_segment_list()

which(GASegments$name == "Organic Traffic")

orgSegment <- segment_ga4("orgSegment", segment_id = "gaid::-5")

GADataOrg <- google_analytics(viewID, date_range =c("2022-02-01","2022-02-08"), metrics = c("sessions", "users", "pageviews",                                                                                        "bouncerate"), segment= orgSegment)

## Date Range Dimension

GADataOrgDates <- google_analytics(viewID, date_range =c("2022-02-01","2022-02-08"), metrics = c("sessions", "users", "pageviews",                                                                                        "bouncerate"), dimensions = "date",segment= orgSegment)

## Dynamic Dates

GADataDynamicDates <- google_analytics(viewID, date_range =c("7DaysAgo","yesterday"), metrics = c("sessions", "users", "pageviews", "bouncerate"), dimensions = "date",segment= orgSegment)

## Search Console Data

scr_auth()

scData <- search_analytics("https://www.ben-johnston.co.uk", startDate = Sys.Date() -7, Sys.Date() -1, searchType = "web")

## Break By Date

scDataByDate <- search_analytics("https://www.ben-johnston.co.uk", startDate = Sys.Date() -7, Sys.Date() -1, searchType = "web", dimensions = "date")

This post was written by Ben Johnston on Ben Johnston

The R programming language has lots of benefits for SEOs, but it just doesn’t get as much love in the space as Python. I get it. The barrier to entry is a little higher and there are some things that you can do with Python or other languages that R is just not built for, but when it comes to analysis of chunky datasets or common SEO analytical functions, R can do it just as well as any other language – better in some cases.

R was the first programming language I learned “properly” after self-teaching a few bits of different ones here and there and, although I’m moving more towards focusing on Python and Julia these days for some of the work I’m doing, R will always hold a special place in my heart. With that in mind, I wanted to share a series of posts where I’ll show you just how you can use R for SEO.

How My R For SEO Series Will Work

Over the next eight posts, I’ll be taking you from complete R newbie up to the point where we’re doing serious SEO work, like building a rank checker and dashboard with the language, covering functions, visualisation, replicating Excel formulae and using APIs along the way. If you want to be the first to know when a new entry has dropped, sign up for my FREE email list. No spam, no sales, just updates of new content.

What’s Your Email Address? *

If you are human, leave this field blank.

Δ

While it’s not a completely exhaustive course on R, my hope is that by the end, you’ll have enough of an understanding of the language to use it in your day-to-day SEO work and be able to find answers to any issues you’re having. I’d also absolutely love it if you’d share this series with your network. Over the next few months, I’ll be doing some more stuff for the R community focusing on SEO, so it would be great to have that amplification.

Today, we’re going to be covering the very basics of using R for SEO and future posts will centre around specific elements of the language. I’ll update this post with links to those when they get published as well. Every post will introduce the concepts that we’re discussing and take you through how they work and, at the end, I’ll demonstrate what we’ve learned with an SEO-specific use case.

As always, feel free to use the table of contents below to skip around if you’re looking for a specific area and the code that we use in each section will be compiled into one script and posted here.

OK, let’s get started.

Why Use R?

I’m not going to get into the R vs Python/ SAS/ Matlab/ Julia debate, but I suppose it’s a worthwhile place to start. Just why would we use R, a statistical programming language, in SEO?

Personally, I’ve always loved data and the pattern recognition that comes with its analysis – pretty similar to why I love SEO. I’ve always used data as my secret weapon within SEO and, when I trained as a data analyst, I was keen to make sure I learned to make the skills as transferrable as possible. These days, we’re seeing a lot of analytical programming being used by SEOs, and R is a fantastic way to leverage that.

Ultimately, in SEO, we’re always working with data, sometimes lots of it, and there are situations where the trusty Excel spreadsheet will either not suffice or the size of the dataset will kill your machine, so it’s worth learning R or similar as an additional string to your bow. Think of it as Excel on steroids and you won’t go far wrong.

As languages go, R is a bit on the limited side compared to others. It’s built for statistical analysis and it does that one thing very well, although as you may have seen with my bulk image resizing post, you can make it do plenty of other things as well. For pure data analysis, there aren’t many languages that are better, and the range of packages, visualisation options and, in my opinion, the best IDE on the market makes R a great choice for the data-savvy SEO.

Installing R

Now that we’re ready to get started, the first thing we have to do is install the R language on our machine. This is really simple.

Firstly, go to the CRAN site at https://cran.r-project.org/mirrors.html and select the server that’s closest to you.

From here, choose the appropriate download for your machine. R is available on Windows, Linux and Mac, and it’s open source, so there’s really no barrier to getting started.

Once your installer has downloaded, install as you usually would any other program. The only change to the installation process I suggest making is unticking “Associate with .Rdata” and .R files. The reason for that is that we’re going to use RStudio for our IDE and we don’t want to just open up the language console every time we double-click on a file.

I generally also recommend only installing the 64-bit version. Using a 32-bit version of the language kind of defeats the object of using this and if you’re still using a 32-bit machine in 2022, you should probably have a serious conversation with yourself or your IT department.

Once everything’s installed, we’re ready to go to the next step: installing RStudio.

Installing RStudio

RStudio is R’s IDE (Integrated Development Environment) and, in my opinion, the best IDE on the market for data analysis, although I do also love PyCharm for working in Python.

An IDE like RStudio lets you write and run your code as well as manage your objects, merge everything you’re working on into specific projects and see your visualisations in one window. They’re essential for any programming work that requires analysis, so it’s great that R has one that’s so good.

You can get RStudio from https://www.rstudio.com/products/rstudio/ – don’t let the paid options scare you, I’ve been using the free one for years and it’s more than good enough for any application.

Installation is just as simple as any other program, but you’ll want to make sure that it’s associating with the relevant file types. If it’s your first installation, select all options. 

OK, now we’re all installed let’s start using R. 

A Quick Tour of RStudio 

The easiest way to show you about how RStudio works is to simply show you how RStudio works, which you’ll find in the image below. 

Again, we’re not going fully exhaustive here, but hopefully this screenshot will give you an idea of what’s where. We’ll cover the specific functionality of each section shortly, but here’s a top-line overview: 

1. The Script Window: Where you write and save your code. You can write anything here without breaking anything and it saves down to a .R file, letting you save your code
2. The Environment Explorer: Where you can see the datasets, variables, objects and functions you’ve written into R
3. The R Console: This is where you’ll actually run your code after writing it in the script window
4. The Plot Window: Where you’ll see the graphs you generate. The different tabs also let you see the files in your working directory, the packages you’ve got installed and view help documentation

Now we know what’s where, the best way to get to grips with R is just to start using it, so let’s go. 

Your First RStudio Project 

Before we do anything, we need to create an RStudio project, which will store all our datasets, our code and everything else we’re working on. You should do this with every single piece of work you do with R, just so you can go back to it later. 

As you get a bit more advanced, you may well want to use version control, which I highly recommend. You can read my guide to Git for Data Analysts to get an understanding of how you can use that and what best practices would look like, but we’re not there yet. 

First, create a folder that you’d like to save your first project in. 

Now in RStudio, click on File and select “New Project” like so. 

Now you’ll want to select “Existing Directory” since we’ve just created that folder. 

Navigate to your project directory and click “Create Project”. 

Finally, click File and “New Script” to give yourself a .R file to write and store your code.

OK, great. Now we’re ready to get cracking on our first R project. But first, we need to get some understanding of how it works.

Datasets And Basic Calculations

Since we’re starting from the very beginning here, we need to start from the actual beginning – creating datasets and basic calculations. Let’s not run before we can walk.

Let’s create our first data frame – essentially, a dataset in its own right.

There are three stages to a data frame: 

1. The name: What we call this variable. It’s always best to choose the most descriptive name you can so you can figure out what it’s doing later
2. The function: This isn’t always essential if your variable is just a number or a string of text, but sometimes, you’ll want to call a function. More on that later
3. The data: What information are we including in this variable? This is where we define it 

Again, we’re only at the basics here, so these aren’t always necessary here, and sometimes as we progress, there will be more elements.

Our First R Dataset

Let’s create and store our first object: 

x <- 2

Seems simple, right? That’s because it is, but let’s break it down anyway. 

• x: The name of our dataset. I know I said to keep your names descriptive, but all shall become clear later 
• <-: The arrow is the most common way of telling R that the name we typed is a dataset name. Some people use the = symbol, which also works fine, but in R, <- is the standard way of doing it 
• 2: The value of our dataset. This will be expanded exponentially later on, but we’re starting at the basics right now 

So, following that, we can tell that our dataset called “x” has a value of 2. 

Paste this into your console window and hit enter, and you should see it in your RStudio Environment Explorer. 

Our First Calculation In R

Now we’ve got our first object, x, with a value of 2, let’s create another one before we start our first calculation in R. 

y <- 3

Now we’ve got a second variable called y with a value of 3. What if we created a new object called z where we try different calculations of the two? 

z <- x+y

As you may have guessed, here we’re adding our x and y datasets and creating a new object called z with the total. 

Try it out. You can just type your dataset name into the R console in RStudio to print the value in the console, or type print(z), whichever you prefer. 

If you used the same values I used for the datasets, you should be given the following in the R console: 

[1] 5

Here, you can see the row number of our output ([1] in this case, since there’s only one row in this dataset), and the value of it. 5, in this case. 

You can do the same thing with other common mathematical calculations: 

• z <- x*y: This will multiply x and y 
• z <- x-y: This will subtract y from x 
• z <- x/y: This will divide x by y 

It’s worth knowing that you can overwrite your dataset names at any time whenever you need to make changes to your code, so you can use the same dataset multiple times, but it’s generally not best practice to do so unless you’ve made a mistake. More on that later. 

But we didn’t start learning R to just do basic calculations, did we? Let’s start using it properly on some real SEO data. 

Of course, in order to do that, we’re going to need some data to work with. Let’s import a spreadsheet and work with that.

Reading CSV Data In R

Since we’re focusing our R learning on SEO, it makes sense for our first project to be using SEO data, so why not use a Google Search Console export? As we go forward, I’ll show you how to get this directly from the API, but that’s another lesson for another time. For now, just do the standard Google Search Console export of your queries. 

This should give you a file called “Queries.csv”. Move that file into your project directory. 

Now we’re going to use a command which you will be using an awful lot with your time in R – read.csv. 

This command reads the contents of a CSV file into your R environment, retaining the structure that you’d see if you opened it in Excel. Let’s see how it works. 

queries <- read.csv("Queries.csv", stringsAsFactors = FALSE)

There it is, your first piece of “proper” R code. Feels pretty cool, right? Put this in your script editor window and, when you’re happy with it, paste it into your console and hit enter. 

You’ll see a dataset called “queries” pop up in the environment explorer in the top right of your RStudio window.

Let’s break it down. As we go through, I’m not going to break down every single command, but this is our first, so it makes sense to.

• queries <-: We’re telling R that we want to call our new dataset “queries”. Again, the “<-“ command is similar to the “=” you’d see in other languages like Python. = does actually work in R, but it’s most common to use <- to define our datasets
• read.csv: The name of our command – in this case, the function to read a CSV file into our environment is helpfully named “read.csv”. Don’t get too used to this, there are some R functions that have me wondering why you’d ever call them that, but that’s part and parcel of any programming language
• (“Queries.csv”,: The name of the CSV file we’re reading in. Don’t forget the quotation marks or to put the full name of the file in
• stringsAsFactors = FALSE): There’s a fantastic article on R-Bloggers about what this does and what you should use it for, but in general, factors are absolutely terrible things and you will almost never want your data to be read in using factors, so I would say for 99% of the occasions you’re reading in CSV data, the stringsAsFactors = FALSE command should be added

And there we have it, we’ve got some data to work with.

One of the key differences between analysing data with code compared to analysing data through Excel is that you can’t actually see it in code compared to looking at a spreadsheet. But not to worry, R has a number of really easy ways to explore and investigate your SEO data.

Basic Data Exploration In R

There are a few key functions that we can use to explore datasets in R: 

• nrow: Tells us how many entries we have in there 
• str: Tells us the structure of the dataset – the headers, the type of data it is and gives us a couple of figures from the top of the dataset
• head: Shows the top results from the dataset 
• tail: Shows the bottom results from the dataset 

There are a few others, which we’ll look at in a second. 

First, we want to see how many rows we have in our dataset. You can just look in the Environment Explorer to the top right like so: 

But if we want to see it in our Console window, we’d type the following: 

nrow(queries)

This will return the number of rows in our dataset. In my case, it’s given me 646. Your number will almost certainly be different (and probably higher if you spend any actual time working on your site, which I am very bad for).

Now let’s see what the structure looks like. What headers do we have in our dataset? 

To do that, type the following: 

str(queries)

This will return the following output:

As you can see, we’ve got the following headers in our Search Console dataset: 

• Top.queries: The search terms that people have used to find our website 
• Clicks: The number of times each search term has been clicked 
• Impressions: The number of times each search term has been seen in search results
• CTR: The percentage of clicks to impressions 
• Position: The average position that the search term has been seen in 

This information is obviously really useful to us as SEOs and over the next series of posts, we’ll be looking at how we can use it more.

Head And Tail Investigations In R Datasets 

Now for further investigation, let’s take a look at the top and bottom values of our Search Console dataset using R’s “head” and “tail” functions. 

To see the top of your dataset, in your console, type: 

head(queries)

And to see the bottom of it, type: 

tail(queries)

These will show the top 20 and bottom 20 results respectively, and can be vital in exploring your datasets.

Sum, Average, Max And Minimum Values In R

This is all really useful, but sometimes we just want to see the maximum, minimum, average or total values of a dataset or a variable. Here’s how you do that, using our Google Search Console data. 

Firstly, we need to understand how to focus on a specific variable (similar to a column) in a dataset rather than the dataset as a whole. This is really easy – you simply type your dataset name and add the $ character. If you know the name of your variable, which you can find from the str() command, or RStudio will give you a lovely dropdown menu which will autocomplete. I told you RStudio was great! 

To see the total value of your Impressions variable (similar to running =SUM on a column in Excel), type: 

sum(queries$Impressions)

To see the mean average value of your Impressions variable (similar to using =AVERAGE on a column in Excel), type: 

mean(queries$Impressions)

And if you want to see the median value of your Impressions, you’d type: 

median(queries$Impressions)

Now let’s see what the maximum and minimum values are. We’ll cover this in more detail in part 5 when we start replicating common Excel functions in R. 

max(queries$Impressions)

This will show you the highest number of impressions you’ve had. 

min(queries$Impressions)

And this will show you the lowest number. 

Exploring Data With Summarise In R

Finally, let’s create a summary of our impressions data, so we can see all these values in one go. For bonus points, we’ll create a dataframe of it, so we can review it at any time. 

To use the summarise (summarize if you’re installing in EN-US) function, we’re going to be identifying specific headers from the dataset rather than using it as a whole. To do that, we’re going to use the “$” symbol to call out the specific headers we want to look at, which our str() command will help us find (although RStudio will also autocorrect for us).  

Let’s get a summary of our impressions. 

Type: 

impVals <- summary(queries$Impressions)

This will give you a new variable called impVals (short for Impression Values – remember how I was saying about giving variables memorable names?), which incorporates the following: 

• Min: The lowest number in our range 
• 1^st Qu: The first quartile, or the point at which 25% of the data is cut off in ascending order 
• Median: The middle average value 
• Mean: The average value 
• 3^rd Qu: The third quartile, the point at which 75% of the data is cut off in ascending order
• Max: The highest value in the range 

Summaries can be incredibly useful when exploring data. In fact, it’s quite rare that I don’t have a summary in my R environment of most of the pieces of data I work with, because they’re just so handy to have for reference.

Subsetting Data In R

One of the unfortunate truths of working with data is that most datasets will have a lot of information in them that you don’t want. From irrelevant queries, to numbers too small to be useful, there will be times that you want to just cut information from your dataset, or focus on one specific element. That’s where subsetting comes in.  

Subsetting is a hugely powerful tool for your SEO analysis, so it’s well worth learning how to do it with R. We’ll be using it in the coming articles, so what better time to go through how it works? 

Google Search Console data in particular is a prime candidate for subsetting since you’ll wind up with a lot of random queries in there at times, which are worth cutting out. 

Let’s look at our Google Search Console dataset that we’ve already read into our environment. From the summary in my example, we can see that there are a lot of queries that have only had one impression. We can say that we don’t want to include these in our analysis. 

What we’re going to do here is to create a new dataset based on our queries dataset, but cutting out any queries with 10 or fewer impressions. This would take a few steps to do in Excel, but thankfully, we can do it in one line with R. 

queriesSub <- subset(queries, Impressions >=20)

That’s it. Let’s break it down:

How The Subset Command Works 

The subset command above works as follows: 

• queriesSub <-: Our dataset’s name
• subset(: We’re telling R that subset is the function we want to use 
• queries: The name of the dataset we’re working on (queries in this case, the Search Console data we imported earlier) 
• Impressions: The variable within the dataset that we want to focus on (Impressions in this case) 
• >=20): We’re telling R that we want to only include queries that have 20 or more impressions – >= means “Greater than or equal to”. Don’t forget your closing bracket 

Now we have a new dataset called queriesSub without as many junk impressions included. You can also use a range of other commands with subsets, such as <= (less than or equal to), == (exactly equal to – don’t forget the double = symbol for an exact match) and more. You can also subset based on specific text strings and much more. We’ll cover more of that in future pieces. 

Exporting To CSV With R

As you work further through your analysis, there will certainly be times that you need to export your data to CSV. Perhaps you need to share it, maybe you want to use the information in another tool or spreadsheet, whatever. The point is, you’ll need to do it. Fortunately, this is very simple in R using the write.csv command. 

Let’s export the subset we created of queries with more than ten impressions.  

write.csv(queriesSub, "queriesSub.csv")

Nice and simple. Now if you look in the files pane in RStudio, you’ll see your new file, and it’ll be in your project directory too, all ready for sharing or importing into something else.  

Let’s break it down.

How Write.csv Works In R

Write.csv is a base R command, meaning you don’t need any extra packages or dependencies to run it. It works as follows:

• write.csv(: Here, we’re telling R that we want to use the write.csv command and export a specific dataset to the csv format. Others such as write.txt are available if you need a text file export, they all work largely the same way 
• queriesSub: We’re saying that the dataset that should be written to CSV is our queriesSub dataset. As you do more with R, you’ll be changing this a lot, but it’s a nice and simple command 
• “queriesSub.csv”): Here, we’re naming the file that we want to export to. Nice and simple, but don’t forget the quote marks 

There we go. Now you know how to do some of the basics in R, which will hopefully help you elevate your SEO analysis game.

And We’re Done

Thanks for working your way through the first instalment in my R for SEO series. Hopefully now you’ve got a grasp of the basics of R and you’re all ready for the next piece where we’ll be covering Packages, Google Analytics and Google Search Console data. 

This is where you’ll start to see it coming together, so I really hope you’ll join me next week. 

If you’re going to be along for the ride, thank you. Sign up for my mailing list below and you’ll get an email notification of when it gets published and you can work through the exercises at your leisure, and if you have any questions, hit me up on Twitter.

Until next week.

Our Code From Today

# First Variables

x <- 2

y <- 3

# First Calculations

z <- x+y

print(z)

z <- x*y

z <- x-y

z <- x/y

# Read In Search Console Data

queries <- read.csv("Queries.csv", stringsAsFactors = FALSE)

# Explore Search Console Data

nrow(queries)

str(queries)

head(queries)

tail(queries)

## Further Exploration

sum(queries$Impressions)

mean(queries$Impressions)

median(queries$Impressions)

max(queries$Impressions)

min(queries$Impressions)

## Summary

impVals <- summary(queries$Impressions)

# Subsetting

## Subset Queries To 10 Or More Impressions

queriesSub <- subset(queries, Impressions >=10)

## Subset Queries To 50 Or Fewer Impressions

queriesSub <- subset(queries, Impressions <=50)

## Subset Queries To Exactly 50

queriesSub <- subset(queries, Impressions ==50)

This post was written by Ben Johnston on Ben Johnston

Keyword and topic clustering for SEO has been a hot topic for years, but one of the things I’ve noticed is that there’s been a distinct lack of discussion around how to actually do it. There’s just been a torrent of theory and a handful of (rather expensive) tools which say they’ll do it for you. With that in mind, I thought I’d spend a bit of time putting together a really basic guide using R to get you started.

I’ve been using keyword and topic clustering as part of my SEO keyword research and content planning approaches for years, also incorporating sentiment analysis and a couple of other fun areas to help my teams really target their content. While I’m not quite ready to give up all my secrets, I see so much discussion without anyone ever showing their working, doing it manually or trying to sell their tools that I wanted to help people make a bit of a start using free, open-source software.

Ready to get started? Cool.

If you’ve got some familiarity with these methodologies, feel free to skip around using the table of contents below and if you find this useful and you’d like more content like this in your inbox, please sign up for my free email newsletter.

What’s Your Email Address? *

If you are human, leave this field blank.

Δ

What You’ll Need

Before getting started, you’ll need the following things:

• Some keyword data in CSV format: Doesn’t need to be a lot and it doesn’t really matter where you got it from, you’ll just need to be aware of the column headers and edit the code accordingly. For this example, I’ve used my Google Search Console data from this site
• R: The open-source statistical language. You can get it from here
• RStudio: The best IDE for R and a lot of other languages too. You can get it from here
• The TM package for R: Packages are like plugins for the language which contain a lot of pre-built functions for specific tasks. The TM package is the best for text mining, which we’ll need to do during this process
• The Tidyverse package for R: I honestly can’t imagine a situation where I open R and don’t get Tidyverse loaded. The Tidyverse package is the definitive collection of other packages to make working with data and visualising it a lot more effective and actually fun

That’s it. You have spent precisely zero pennies to do this!

Read In Your Data

I’m going to try and be pretty thorough with this piece, but I’m also not going to be writing a step-by-step guide to using R. There’s some assumed knowledge on your part, but where required, I’ll be linking to relevant resources so you can learn more about the language and the process.

The first thing we need to do when working with any data in R is to actually read the data into our environment. After you’ve created your RStudio project, get your dataset in CSV format into your working directory and use the following command:

queries <- read.csv("Queries.csv", stringsAsFactors = FALSE)

Rename “Queries.csv” as whatever you’ve called the file with your keyword data.

Preparing Your Text Data For Clustering

Clustering is primarily a numerical function, so we’re going to need to make our text workable in a numerical world. The way we’ll do this is to turn our keywords into a Document Term Matrix using the Corpus function, and then we’ll clean that corpus up in line with best practice for text analysis.

Firstly, after reading in our data, we want to make sure that we’ve got our packages installed and live in our R environment. You can do this with the following commands:

install.packages("tm")

install.packages(“wordcloud”)

install.packages("tidyverse")

library(tm)

library(wordcloud)

library(tidyverse)

If you want to cut down on the amount of code you’re using when installing packages, you can use the combine and lapply functions like so:

instPacks <- c("tidyverse", "tm", "wordcloud")

lapply(instPacks, require, character.only = TRUE)

Now we’ve got our packages in place, we need to create that Document Term Matrix or corpus from our text. We do that with the following command:

dtm <- Corpus(VectorSource(queries$Query))

By doing this, we’ve turned our Search Console queries into a Corpus in our R environment, and it’s ready to work with. Again, adapt the “queries$Query” to whatever output you have to work with.

Cleaning a Corpus

When you’re working with text data in R, there are a few steps that you should take as standard, in order to ensure that you’re working with the most important words and also eliminating possible duplication due to capitalisation and punctuation.

I generally recommend doing the following to every corpus:

• Change all text to lower case: This brings consistency, rather than including duplicates caused by capitalisation
• Turn it to a plain-text document: This eliminates any possible rogue characters
• Remove punctuation: Eliminates duplication caused by punctuation
• Stem your words: By cutting extensions from the words in your corpus, you eliminate the duplication caused by adding “S” to some words, for example

In a lot of cases, I’d usually remove stopwords (terms such as “And”), but in this case, it’s worth keeping them since we’re going to be working with full queries rather than fragments.

Here’s how to clean that corpus up.

A Corpus-Cleaning Function For R

Functions in R are a way to make particular commands or pieces of code reproducible. Rather than entering a series of commands every time you need to use them, you can just wrap them into a function and use that function every time. They’re a time-saver and a great way to ensure that your code is easier to work with, as well as easier to share.

Based on the criteria above, here’s an R function to help you clean your keyword corpus up to prepare it for clustering your keywords:

corpusClean <- function(x){
  
  lowCase <- tm_map(x, tolower)
  
  plainText <- tm_map(lowCase, PlainTextDocument)
  
  remPunc <- tm_map(lowCase, removePunctuation)
  
  stemDoc <- tm_map(remPunc, stemDocument)
  
  output <- DocumentTermMatrix(stemDoc) 
}

Here, we’re using the TM packages’ built-in functionality to transform our corpus in the ways described above. Again, I’m not going to go into the minutiae of how this works – I don’t think I’ve got another 11k+ word post in me today – but I hope the notation is reasonably clear.

Paste this function into your R console and then we need to actually run it on our corpus. This is really easy:

corpusCleaned <- corpusClean(dtm)

We’ve created a new variable called corpusCleaned and run our function on the previous dtm variable, so we have a cleaned-up version of our original corpus. Now we’re ready to start clustering them into topics.

Using K-Means Clustering For Keywords In R

There are loads of clustering models out there that can be used for keywords – some work better than others, but the best one to start with is always the tried and trusted k-means clustering. Once you’ve done a little bit of work with this, you can explore other models, but for me, this is the best place to start.

K-means clustering is one of the most popular unsupervised machine learning models and it works by calculating the distance between different numerical vectors and grouping them accordingly.

Wikipedia is going to explain the math far better than I can, so just look at that link if you’re interested, but what we’re here to talk about today is how this can be used to cluster your search terms or target keywords into topics.

Now that we’ve cleaned up our corpus and gotten it into a state where the k-means algorithm can tokenise the terms and match them up to numeric values, we’re ready to start clustering.

Finding The Optimal Number Of Clusters

One of the biggest errors I see when people try to apply data analytics techniques to digital marketing and SEO is that they never actually make the analysis useful, they just make a pretty graph for a pitch and the actual output is never usable. That’s certainly a possible failing of topic and keyword clustering if you’re not smart about it, which is why we’re going to run through how to identify the optimal number of clusters.

There are lots of different ways that you can identify the optimal number of clusters – I’m partial to a Bayesian inference criterion, myself, although good luck getting that to run quickly in R. Since today we’re just doing an introduction, I’m going to take you through the most commonly-used way to identify the best number of topic clusters for your keywords: the Elbow Method.

The Elbow Method

The Elbow Method is probably the easiest way to find the optimal number of clusters (or k), and it’s certainly the fastest way to process it in R, but that still doesn’t mean it’s particularly quick.

Essentially, the Elbow Method computes the variance between the different terms and sees how many different clusters these could be put in up to the point that adding another cluster doesn’t provide better modelling of the data. In other words, we use this model to identify the point at which adding extra clusters becomes a waste of time. After all, if this approach doesn’t become efficient, no one will use it.

But how do we make the Elbow Method work? How do we use it to identify our target number of clusters, our k? The easiest way to do that is to visualise our clusters and take a judgement from there, hence why we installed the ggplot2 package earlier.

Running The Elbow Method In R

Before we get started, I have to say one thing: R isn’t the fastest language in the world (hence why I’m moving away from it these days) and it may take quite a while to run this process if you’ve got a large dataset. If you do have a lot of keywords, make sure you’ve got something else to do, because otherwise, you might be looking at your screen for a while.

Firstly, we need to create an empty data frame to put our cluster information into. That’s easy, we’ll just use the following command:

kFrame <- data.frame()

Now we have to use a for loop to run the clustering algorithm and put it into that data frame. This is where the processing time comes in, and I know it’s not the cleanest way to run it in R, but it’s the way that I’ve found it to work the best.

for(i in 1:100){
  k <- kmeans(corpusCleaned, centers = i, iter.max = 100)
  kFrame <- rbind(kFrame, cbind(i, k$tot.withinss))
}

This loop will take our tidied-up corpus (our corpusCleaned variable) and use the k-means algorithm to break it out into as many relevant clusters as it can, up to 100 and then put that data into our empty data frame. Obviously we don’t want 100 clusters – no one’s going to work with that. What we want to find here is the break point, the number at which we get diminishing returns by adding new clusters.

It may take a while to run this if you’ve got quite a lot of keywords, but once it’s done, paste the following:

names(kFrame) <- c("cluster", "total")

All we’re doing here is naming our column headers, but it’ll be important for our next stage of finding k.

Visualising The Elbow Method Using GGPlot2

As I said earlier, there’s still a certain amount of manual work involved in this keyword & topic clustering, and a big chunk of that is around finding k and then identifying what the clusters actually are.

Fortunately, it’s not actually a lot of manual work and it will really help with your SEO and content targeting, so it’s really worth taking the time.

Use the following command to create a plot of your clusters:

ggplot(data = kFrame, aes(x=cluster, y=total, group=1))+ 
  theme_bw(base_family = "Arial")+ geom_line(colour = "darkgreen")+
  scale_x_continuous(breaks = seq(from=0, to=100,by=5))

Certain elements, such as the colour, font and your chosen dataset can be switched up, obviously.

Using my Search Console dataset, I get the following result:

Using The Elbow Method To Identify The Optimal Number Of Clusters

Now we’ve got our graph, we need to use a bit of human intelligence to identify our number of clusters. It’s not perfect, and that’s why other models exist, but I hope this is a good start for you.

When we look at these charts with the Elbow Method, we’re looking for the point that the chart curves and drops down sharply. The point at which additional clusters become less useful. From looking at the chart below using my dataset, we can see that seven clusters is the point at which we should stop adding extra clusters.

Now we’ve identified k and broken our terms out into clusters, now we need to match it back to our original dataset and name our topics.

From the piece of analysis above, we can see that the optimal number of clusters on this dataset, our k, is seven. Now we need to run the following commands to match them back to our original dataset:

kmeans7 <- kmeans(corpusCleaned, 7)

This is fairly self-explanatory, I hope, but essentially what we’re doing here is creating a new variable called kmeans7 (change it to whatever you like), telling R to use its base kmeans command on our corpusCleaned variable and to use it on the number of clusters we’ve identified. You can obviously adapt this to whatever number of SEO keyword or topic clusters your analysis identified using your own datasets.

Finally, you’ll want to turn this into a clean data frame. You can do that like so:

kwClusters <- as.data.frame(cbind(queries$Query, kmeans7$cluster))

names(kwClusters) <- c("Query", "Cluster")

Now your original keywords are in a data frame with their assigned cluster in the next column.

Now your original keywords are in a data frame with their assigned cluster in the next column and the columns are named “Query” and “Cluster”, keeping them consistent with our main dataset.

From here, we’ll want to get those clusters assigned to our main dataset. Dplyr from the Tidyverse has a really handy left_join function which works a bit like Excel’s Index Match and will let you match this easily.

queries <- left_join(queries, kwClusters, by = "Query")

Again, you’ll need to adapt your variables to fit your datasets, but this is how it’s working with my particular example

What Can You Do With This?

There’s a lot that can be done with this and today I’m updating this post with a few examples of how to do the different elements I’d just suggested previously.

Explore Your Clusters By Subsetting

The easiest way to get to grips with what’s contained in each cluster is to subset and explore accordingly.

Subsetting is one of the most essential elements of working with large datasets, so it’s definitely worth getting to grips with. Fortunately, R has a number of base functions to let you do just that.

Let’s take a look at our first cluster in isolation:

clusterOne <- subset(queries, Cluster == 1)

Here, we’ve cut down our dataset to only look at cluster one. When you’re subsetting or using other exact matches in R, you need to use the ==, otherwise things can get a bit skewey.

Let’s explore it a little. First, we want to see how many observations (keywords in this case), we have in this cluster. Nice and easy – in fact, in RStudio, you can just look in the Data pane like so:

But let’s do it with some code anyway. The nrow function in base R will do that for you.

nrow(clusterOne)

How about if we want to see the number of impressions and clicks from that cluster? We can use the Tidyverse’s summarise function for that:

clusterOne %>% summarise(Impressions = sum(Impressions), Clicks = sum(Clicks))

This will give us an output of the total number of impressions and clicks from that cluster, which can be useful when identifying the opportunity available. Obviously, you can use this for other elements as well. Perhaps you’ve merged some search volume data in, for example, or you want to see what your average position is for this cluster.

Exploring your data in subsets will give you a much greater understanding of what each cluster contains, so it’s well worth doing.

Create Wordclouds By Cluster

Wordclouds are something that always tend to go over well in client presentations, and, although a lot of designers hate them, they’re often a fantastic way to see the most common keywords and terms in your dataset. By doing this by cluster, we’ve got a great way to dig into what each cluster is discussing.

The Wordcloud package for R has everything you need to do this.

Let’s take a look at the queries in my first cluster:

wordcloud(clusterOne$Query, scale=c(5,0.5), max.words=250, random.order=FALSE, 
          rot.per=0.35, use.r.layout=FALSE, colors=brewer.pal(8,"Dark2"))

This will create a wordcloud from that keyword cluster’s query column, give it some pretty colours and present it in the RStudio plots pane. In this cluster’s case, the wordcloud looks like this:

As you can see from here, cluster one of my Search Console data is all about tracking email in Google Analytics.

Wordclouds are a really quick and visual way to explore and identify the topics covered in your keyword clusters and using R instead of a separate tool will help you do that nice and easily without needing to leave RStudio.

Plotting A Combo Chart With R & GGPlot

The final idea I’m going to run through today is to plot a combo chart where we look at impressions and clicks by cluster. The analyst in me is not a big fan of combo charts since they’re rather flawed, but they are a great way to quickly identify opportunities to improve SEO performance in your keyword clusters. I’m also not very good at making R graphs look pretty, so sorry for that!

Again, you can adapt this to look at a wide variety of different metrics and using the GGPlot package from the Tidyverse gives you a lot of fun graphical avenues to explore.

Firstly, we want to create a dataframe containing a summary of the dataset. You can do that like so with a Dplyr function:

querySummary <- queries %>% group_by(Cluster) %>%
  summarise(Impressions = sum(Impressions), Clicks = sum(Clicks), Avg.Position = mean(Position),
            Avg.CTR = mean(CTR))

Now we have a frame which contains all our keyword clusters with the total impressions, total clicks and the average position and CTR in place, which will be useful for a wide variety of visualisations, not just this example.

The code below will create a column graph with the impressions by cluster and the clicks on a line chart with a secondary axis scaled by 20 to accommodate the differences between the two variables. You can absolutely feel free to change the colours to make it look better.

ggplot(querySummary)+
  geom_col(aes(Cluster, Impressions), size = 1, colour = "black", fill = "darkgray")+
  geom_line(aes(Cluster, 20*Clicks), size = 1, colour = "darkgreen", group =1)+
  scale_y_continuous(sec.axis = sec_axis(~./20, name = "Clicks"))

This will give the following graph:

And there you have it – a really simple introduction to keyword clustering for SEO and some things you can do with it before you start creating your content accordingly.

Wrapping Up

I really hope that this will help some of you that have been wanting to use keyword and topic clustering to better target your SEO but haven’t had the budget for the tools. Everything I’ve talked about here is free to use and, while you might need to do a bit of extra reading to fully get what’s going on, I really hope it’s been helpful.

The most important thing to do with this is to be ruthless. Keep your keyword and topic clusters small and investigate the classifications manually because the wider the base, the less you’ll do with it and the less effective it’ll be.

There is so much that can be done with this that one day, if the world ever goes back to normal, I’ll maybe do a talk on it somewhere, but the key thing to understand is that techniques like this don’t do the job for you, but they do help you do the job a hell of a lot better.

As usual, any questions, shoot me a line on Twitter or through the contact form.

I’ve put the full script below, amend as required.

Until next time.

The Full Keyword And Topic Clustering For SEO R Script

## Install Packages

options(warn=-1)

set.seed(12)

memory.limit(1600000000)

instPacks <- c("tidyverse", "tm", "wordcloud")

lapply(instPacks, require, character.only = TRUE)

## Read Data

queries <- read.csv("Queries.csv", stringsAsFactors = FALSE)

## Prepare Text

dtm <- Corpus(VectorSource(queries$Query))

corpusClean <- function(x){
  
  lowCase <- tm_map(x, tolower)
  
  plainText <- tm_map(lowCase, PlainTextDocument)
  
  remPunc <- tm_map(lowCase, removePunctuation)
  
  stemDoc <- tm_map(remPunc, stemDocument)
  
  output <- DocumentTermMatrix(stemDoc)
  
}

corpusCleaned <- corpusClean(dtm)

## Elbow Method To Find K

kFrame <- data.frame()

for(i in 1:100){
  k <- kmeans(corpusCleaned, centers = i, iter.max = 100)
  kFrame <- rbind(kFrame, cbind(i, k$tot.withinss))
}

names(kFrame) <- c("cluster", "total")

## Visualise Elbow To Find K

ggplot(data = kFrame, aes(x=cluster, y=total, group=1))+ 
  theme_bw(base_family = "Arial")+ geom_line(colour = "darkgreen")+
  scale_x_continuous(breaks = seq(from=0, to=100,by=5))

## Identify Clusters

kmeans7 <- kmeans(corpusCleaned, 7)

kwClusters <- as.data.frame(cbind(queries$Query, kmeans7$cluster))

names(kwClusters) <- c("Query", "Cluster")

## Merge Clusters To Query Data

queries <- left_join(queries, kwClusters, by = "Query")

## Explore Clusters With Subsets

clusterOne <- subset(queries, Cluster == 1)

## Wordclouds

wordcloud(clusterOne$Query, scale=c(5,0.5), max.words=250, random.order=FALSE, 
          rot.per=0.35, use.r.layout=FALSE, colors=brewer.pal(8,"Dark2"))

## Impressions/ Clicks By Cluster

querySummary <- queries %>% group_by(Cluster) %>%
  summarise(Impressions = sum(Impressions), Clicks = sum(Clicks), Avg.Position = mean(Position),
            Avg.CTR = mean(CTR))

ggplot(querySummary)+
  geom_col(aes(Cluster, Impressions), size = 1, colour = "black", fill = "darkgray")+
  geom_line(aes(Cluster, 20*Clicks), size = 1, colour = "darkgreen", group =1)+
  scale_y_continuous(sec.axis = sec_axis(~./20, name = "Clicks"))

This post was written by Ben Johnston on Ben Johnston