# Using R for SEO, What to expect? ## The power of R'. What's different about it? **R'** is a high-level programming language that mainly focuses on data analysis. Meaning it's "specialized". With a few lines of code, you can do a lot. Let me give you an example: ```r internal_linking = read.csv(file.choose()) View(internal_linking) ``` These lines of code will : * prompt a select file menu for you to select a CSV (*`file.choose`*) * It will import data inside R (*`read.csv`*) into `internal_linking` var * The second line will just display it (*`View`*) Let's do it with a website links file ![internal hyperlinks ](/files/-MXv_LDWhPsSReDr5uQj) This is how you open and **browse a file with 2.6 Million rows effortlessly**. Noticed the small search icon on the top right? Yes, you can search within it quite easily too. ![search for dead links using http code](/files/-MXvahaWPvWoB8ERtDOE) Want to count HTTP code? Here it is ```r View(table(internal_linking$Status)) ``` You can recognize the `View` function from before. the `table` function just count values. the **`$`** is a shortcut to access column values It displays: ![count of http code](/files/-MXvcvIFm8iHXCESlzJF) This is 30 secondes job. The most time-consuming part was finding the file on the hard disk. Of course, this is just a silly example. There are countless ways to do this (third-party app, terminal, Excel pivot, panda/polar), but it gives a nice introduction to R's possibilities and how simple that is. ## *'There is a package for that'* The real power of R relies on R packages. What's a package you may ask? It's an on-demand library of functions you can load to help you in specialized tasks. Again let's take some examples. ### ⬢ `ggplot2` It's one of the most famous packages. it can be used to build advanced charts and plots. To use it, you just have to install it once like this ```r install.packages("ggplot2") ``` to load it ```r library("ggplot2") ``` and after that, you can now use it ```r ggplot(internal_linking)+ aes(x = Status, fill = Status) + geom_bar() + scale_fill_hue() + theme_minimal()+ coord_flip() ``` ![](/files/-M_CA4339S7EtNtsjEzX) Because we only want to see the problematic http codes, we are going to filter ```r internal_linking_filtered <- filter(internal_linking, !(Status %in% c("200 no error", "Not checked","999 LinkedIn blocking automated testing"))) ggplot(internal_linking_filtered)+ aes(x = Status, fill = Status) + geom_bar() + scale_fill_hue() + theme_minimal()+ coord_flip() ``` ![](/files/-M_CwFZO25jyKGJhuX6Z) Let's not go into details for now, but believe it or not, I'm not capable of writing this code, I just googled: "Bar charts chart ggplot" , "flip axis ggplot", ... shamelessly copy-paste the codes. gggplot2 is powerful, it can make basically every chart you can think of A few examples of plots done using `ggplot2` ![](/files/-Md1X-gfWVMDjdrQyVYN) ![](/files/-Md1X6BEOw9Hwig3DS5R) ![](/files/-Md1X3Dg1EbDjj5GVuyv) ![](/files/aBltR1tAxMysbsvNCDM4) To see more examples: * [The R Graph Gallery](https://www.r-graph-gallery.com/)`/` [Top 50 ggplot2 Visualizations](), some nice code to copy-paste * [Tidy Tuesday](https://github.com/HudsonJamie/tidy_tuesday), nice to see how far ggplot2 can be pushed Let's look at another package ### ⬢ `Lubridate` [Lubridate](https://lubridate.tidyverse.org/) will help to deal with our timestamp values. After the now-classic installing and loading ```r install.packages("lubridate") library("lubridate") ``` It can be used to guess and transform this `Time.stamp`into a real date format ```r internal_linking$real_date = dmy_hms(internal_linking$Time.stamp) ``` Values have been transformed into a true `Date` format. ![before and after using Lubridate function](/files/-MYHV7FPwXmrtu9m9uOo) No more "at" in the middle or "am/pm". It's now easier to read and sort. The `dmy_hms` function guessed successfully that the "at" was useless. Now that those are real dates and no longer character string, we can plot them using `ggplot` ```r ggplot(internal_linking) + aes(x = real_date) + geom_histogram() + theme_minimal() ``` ![the number of links discovered per date.](/files/-MYHXLuqusJopbTjwr2u) the `Lubridate` package can also help with duration, time zone, intervals, ... Have a look at the [cheatsheets](https://rawgit.com/rstudio/cheatsheets/master/lubridate.pdf). It is a bit complex to get into but so much less than trying to do it yourself. I've lost literally days of my working life, trying to do this kind of stuff badly in Excel/Google Sheet. ### ⬢ `urltools` One last example for the road. 'Want to extract links domains? You can sure use regex, or even try to split the string using "/" as a separator... OR you can use the more reliable `urltools` package which as a dedicated `domain()` function to do exactly that. ```r # Installing and Loading Package install.packages("urltools") library("urltools") # extract domain and feed it to a new data column called 'domain' internal_linking$domain <- domain(internal_linking$URL) ``` Let's check out the values, nearly the same code as before: ```r View(table(internal_linking$domain)) ``` ![top domains](/files/-MYHZGehi8KYCb_LmEA7) ### Where to find packages? Good question! All the previous packages have been downloaded from CRAN. It's a repository that contains [thousands of packages](https://cran.r-project.org/web/packages/available_packages_by_date.html). Github is also a great source. There are so many that, the problem is often to find the right one. The way to go is usually to ask around using: * Twitter using the #rstats hashtag * [rstats subreddit](https://www.reddit.com/r/rstats/) * [rstudio forum](https://community.rstudio.com/) * There are a couple of nice slacks like the [Measurecamp's one](http://join.measure.chat/) The community is smaller than other programming languages but people are more willing to help, it compensates. ## The confusing things about R ### The name > *Oh you do '*R programming'*, that's cool. Is it like* Air Guitar? You do fake programming?\ > \- An anonymous member of my family "R" is a weird name, especially in this covid time, and it's not the most Google-friendly name either. So here are few links to help find R resources. * - R search engine * - R blogs aggregator * all the R free books * Search github for R source code ### the `<-` If you've seen some R' code before and you might have been surprised to see this "<-" being used. it's just a legacy thing, historically R differentiates "assignation" and "comparison", example: *assignation -* If you want to **set** the value of X to 3. ```r x <- 3 ``` *comparison -* Is X **equal** to 3? ```r x == 3 ``` If you want to keep this little *tradition* alive you can use <- but it is really up to you. Perfectly fine to use **=** ```r x = 3 # same as x <- 3 ``` #### The `|>` or`%>%` The (weird) **`|>`** operator allows operations to be carried out successively. Meaning: the results of the previous command are the entries for the next one. Like the **`>`** ( “pipe”) command line for the terminal if you came across it. You might also see `%>%` sometimes Always better with an example, let's take the first line of code of this page ```r View(read.csv(file.choose())) ``` Its 3 functions are used one after the other. The readability is decent. I wouldn't recommend adding a fourth. the **|>** operator fixes this soon-to-be problem. ```r # equivalent to the previous instruction file.choose() |> read.csv() |> View() # again equivalent file.choose() |> read.csv() |> View() ``` As you can see, fairly easy to read. This operator is so practical that most R practitioners now use it. ### R' **relies a lot on vectors which are confusing** Let's see some examples. ```r #this instruction combine 3 numbers to make a vector and define the x variable. x <- c(1,2,3) # this will display our vector x # this will concatenate the x vector twice c(x,x) # Unlike tables, vectors first element need to be called with 1 x[1] ``` the good part is you don't need to make a loop every time you need to make some basic operations ```r #this will add one to all vector elements y <- x+1 #Want to add up two vectors with each other? this will work x+y # it also works with function x <- as.double(x) ``` # Getting started ## Install R R can be downloaded from the [CRAN website](https://cran.r-project.org/) After the installation, you'll be able to use R on the command line by typing `r.` It's a bit dry as you can see. ![Using R in the Mac OS Terminal](/files/-MYVdE-cpT-8j3W7dKoq) ## Install RStudio the best way to use R is to use a development environment and the most widely used is RStudio. All the examples in this book will be using it. [Here the webpage to download it](https://www.rstudio.com/products/rstudio/download/). After this installation, you are ready to go 🙌 For the real R beginners, I would recommend to at least to check out in your app where are: * the Console panel, where you can execute code that is not meant to be store
* the Source panel, where you can execute R file code. Where any piece of code can be selected and executed. here is [quick youtube demo](https://www.youtube.com/watch?v=MGHjnpj46IU\&t=181s) that might help. For your culture, you should know that it is also possible to [execute R code online.](/ressources/execute-r-code-online) ## Next? Choose from the menu on the left what you want to do. if you want to start with something easy I would recommend the [XML sitemap](/crawl/download-xml-sitemaps) # What is R? What is SEO? ### What is SEO? Search Engine Optimization, or SEO, is the process of improving the quality and quantity of website traffic to a website or a web page from search engines. ### What is R? R is a free programming language and software environment. ### Why would you use R for SEO? R is specialized in data mining, statistical & data analysis, and data visualization. It also has a good capacity for crawling. Basically, everything that is useful for doing some SEO. When you have understood the fundamentals, R' is also quite easy to read and write. Even if you don't want to really learn it, just copy/paste 3 lines of code, you can crawl a website or extract some Google Analytics data and export them into a CSV. ### When should you be using R for SEO? R becomes useful when you start to deal with big websites with thousands of pages. I'm a big fan of automation but the relevance needs to be properly evaluated. There are also a lot of great SEO tools out there, R' will never replace them but it is definitely a very nice addition to your set of tools. ### So... Why not Python? Python is an extraordinary programing language, very versatile, in many cases faster. Python is better than R, 90% of the time. The 10% where R is superior is data visualization syntax (nothing beats`ggplot2`so far) and easy-to-use data manipulation (thanks to `Dyplr` ). Finally, it's less wordy, often easier to read. Using R is a pragmatic choice, R is often used by academics and people that have another job, full-time dev logically learns Python. # About this Book *This guide, **in the course of writing**, is dedicated to all those who share their knowledge online and especially Hamlet Batista. I'm not a Python user like him and I just came across the guy once, but his disappearance made us all remember that sharing is caring and that Life is so damn short.* ### 1.1 Motivation & limits Even though R' is a terrific option for SEO, there are simply not enough resources out there. This guide is not here to deliver a course about R, there are plenty already. This guide is meant to be as practical as possible. How things should be done in an "R-ish way" is not the purpose of this guide. \ Grab what you want to grab and feel free to reach if you have a better solution, I'll add it giving you credit. ### 1.3 Project home All documents, markdown files, scripts, and project images are also publicly available in [this repository](https://github.com/pixgarden/rforseo) All errors are my own. All comments are welcome to improve this guide. ### 1.4 License This website is (and will always be) **free to use** and is licensed under the [Creative Commons Attribution-NonCommercial-NoDerivs 3.0](http://creativecommons.org/licenses/by-nc-nd/3.0/us/) License. # What's crawling and why is it useful? ### What is crawling? It's fetching the contents of a web page using an app or a script. This is what Google is doing when its bot explores the web and analyzes webpage content. ### How crawling is useful for SEO? As someone doing SEO you need to know what you are showing to Google. What your website looks like from a (Google) bot perspective. You need to [check the quality of your XML sitemap](/crawl/download-xml-sitemaps) if you are submitting one. You need to [check your website webpages and meta data](/crawl/rcrawler). Checking the web server logs is also a good idea, to know what Google bot is doing on your website. You can also respectfully crawl your competitors' websites to better understand their SEO strategy. ### Crawling is also interesting to grab data. There are some great public datasets out there, even wikipedia is a great source. Let's take this [world population data](https://en.wikipedia.org/wiki/World_population) that can be crawled: ![](/files/-MbBFgCQ31pAmDFKWqC2) ```r library(dplyr) library(rvest) url <- "https://en.wikipedia.org/wiki/World_population" population <- url %>% read_html() %>% html_nodes(xpath='//*[@id="mw-content-text"]/div[1]/table[7]') %>% html_table() %>% as.data.frame() #removing extra row population = population[-1,] # convert to numeric population$Population <- as.numeric(gsub(",","",population$Population)) population$Year <- as.numeric(population$Year) ``` and displayed as a plot ```r library(ggplot2) ggplot(population) + aes(x = Year, y = Population) + geom_point() + theme_minimal() + scale_y_continuous(labels = scales::comma) ``` *et voila* ![](/files/-M_D4f3rRvwO1CdD53gs) It's not really SEO, but it can be useful. I've also been using it to check the quality of the data on websites, like product prices, image availability, etc. Again, [Screamingfrog](https://www.screamingfrog.co.uk/) or other crawlers might be a better choice, it depends on how integrated you want that to be and how custom those checks should be. Let's move to a more practical use case, Download and [check XML sitemap quality](/crawl/download-xml-sitemaps) #### # Download and check XML sitemaps using R' {% hint style="info" %} If you are coming from Google you don't know anything about R' and just want to download an XML sitemap, use this tool \ \ If you want to learn how to do it yourself, keep on reading ⌄ {% endhint %} It's not required to submit an XML sitemap to have a successful website but it's definitely an SEO nice to have. Nevertheless, if you do submit one, it's best to make sure it's error-free and as you will see its is quite straightforward to extract URLs using R ### Install xsitemap R’ Package (to be done once) and Load ```r # Installing libraries and Loading libraries install.packages("devtools") library(devtools) install_github("pixgarden/xsitemap") library(xsitemap) ``` ### Find and fetch XML sitemaps ```r xsitemap_urls <- xsitemapGet("https://www.nationalarchives.gov.uk/") ``` This function will first search for XML sitemap url. It will first check the robots.txt file to see if an XML sitemap url is explicitly declared. if not, the script will do some random guess (‘sitemap.xml’, ‘sitemap\_index.xml’ , …) most of the time, it will find the XML sitemap url. Then, the XML sitemap URL is fetched and the URLs extracted. If it’s a classic XML sitemap, a data frame (a special kind of array) will be produced and returned. If it’s an **index** XML sitemap, the process will get back from the start with every XML sitemap inside. This will produce a data frame with all the information extracted. ```r View(xsitemap_urls) ``` ![](/files/GdJvtO8rx4iUklvdqQa7) ### Check URLs HTTP code Another interesting function allows you to crawl the sitemap URLs and verify if your web pages send proper 200 HTTP codes, using HEAD Requests (easier on the website server) It can take some time depending on the number of URLs. It took several hours for for example. ``` xsitemap_urls_http <- xsitemapCheckHTTP(xsitemap_urls) ``` It will add a dedicated column with the HTTP code filled in. You can check data inside rstudio by using ```r View(xsitemap_urls_http) ``` ![](/files/-MaNlyFO0nLRY84thMaQ) or if you prefer, [generate a CSV](/export-data/send-and-read-seo-data-to-excel#export-your-data-into-a-csv) ### Count HTTP codes Like in the [intro](/r-intro#the-power-of-r-whats-different-about-it), it's quite easy to count HTTP codes ```r View(table(xsitemap_urls_http$http)) ``` to discover, at the time of writing that most of the XML sitemap URLs are actually redirects... ![](/files/-MaNmP00FGZA_y3DPB1e) ### Plot the years the pages were added You might have noticed that in this XML sitemap with a "lastmod" field. This is an optional field that explicitly declares to Google last modification date. This allows theoretically Google to optimise website crawls. It also allows us to understand how fresh is one's website content as we can plot it ```r library(ggplot2) ggplot(xsitemap_urls) + aes(x = lastmod) + geom_histogram(bins = 90L, fill = "#112446") + theme_minimal() ``` *(I've got help from the* [*esquisse*](/data-viz/using-esquisse-package-x) *library)* ![Most of the content originated from 2014-2015, The oldest page have been updated in 2001](/files/-MfSOEyOJ3cHRG748jHR) Let's try to get a clearer picture by extracting years ```r # We extract from the Date the year and # store the value into a new column called 'year' xsitemap_urls$year <- format(xsitemap_urls$lastmod,"%Y") # Removing not available values (NA's) # and ploting the url by year xsitemap_urls %>% filter(!is.na(year)) %>% ggplot() + aes(x = year) + geom_bar(fill = "#112446") + theme_minimal() ``` ![](/files/-MfSRzAJoFZmIBWE91nK) If you prefer a % cumulative view: ```r plot(ecdf(xsitemap_urls$year)) ``` ![](/files/-MfSTUhVVmrq-egRjdMl) # Crawling with rvest If you want to crawl a couple of URLs for SEO purposes, there are many ways to do it but one of the most reliable and versatile packages you can use is the [rvest](https://cran.r-project.org/web/packages/rvest/) package. Here is a [simple demo](https://stat4701.github.io/edav/2015/04/02/rvest_tutorial/) from the package documentation using the IMDb website: ```r # Package installation, instruction to be run only once install.packages("rvest") # Loading rvest packagelibrary(rvest) ``` The first step is to crawl the URL and store the webpage inside a ‘lego\_movie’ variable. ```r lego_movie <- read_html("http://www.imdb.com/title/tt1490017/") ``` Quite straightforward, isn’t it? *`lego_move`* is an *xml\_document* that need to be parse in order to extract the data. Here is how to do it: ```r rating <- lego_movie %>% html_nodes("strong span") %>% html_text() %>% as.numeric() ``` For those who don’t know **%>%** operator here is [simple explanation](/r-intro#the-greater-than)\ \ *html\_nodes*() function will extract from our webpage, HTML tags that match CSS style query selector. In this case, we are looking for a \ tag whose parent is a \ tag.\ then script will extract the inner text value using *html\_text*() then convert it to a number using *as.numeric*(). Finally, it will store this value inside *rating* variable to display the value just write: ```r rating # it should display > [1] 7.8 ``` Let’s take another example. This time we are going to grab the movies’ cast.\ \ Having a look at the HTML DOM, it seems that we need to grab an HTML \ tag who’s parent tag have ‘titleCast’ as an id and ‘primary\_photo’ as a class name and then we’ll need to extract the alt attribute ```r cast <- lego_movie %>% html_nodes("#titleCast .primary_photo img") %>% html_attr("alt") cast # Should display:# > [1] "Will Arnett" "Elizabeth Banks" "Craig Berry"# > [4] "Alison Brie" "David Burrows" "Anthony Daniels"# > [7] "Charlie Day" "Amanda Farinos" "Keith Ferguson"# > [10] "Will Ferrell" "Will Forte" "Dave Franco"# > [13] "Morgan Freeman" "Todd Hansen" "Jonah Hill" ``` Last example, we want the movie poster url. First step is to grab \ tag who’s parent have a class name ‘poster’ Then extract src attribute and display it ```r poster <- lego_movie %>% html_nodes(".poster img") %>% html_attr("src") poster # Shoudl display:# [1] "https://m.media-amazon.com/images/M/MV5BMTg4MDk1ODExN15BMl5BanBnXkFtZTgwNzIyNjg3MDE@.V1_UX182_CR0,0,182,268_AL.jpg" ``` ### Now a real-life crawl example Now that we’ve seen an example by the book. We’ll switch to something more useful and a little bit more complex. Using the following tutorial, you’ll be able to extract the review score of any WordPress plugins over time. For example here are the stats for [**Yoast**](https://yoast.com/wordpress/plugins/seo/), the famous SEO plugin:![](https://www.gokam.fr/wp-content/uploads/2020/02/yoast.png) Here are the ones’ for[ **All in one SEO**](https://en-gb.wordpress.org/plugins/all-in-one-seo-pack/), his competitor![](https://www.gokam.fr/wp-content/uploads/2020/02/all-in-one-seo.png) Very useful to follow if your favourite plugin new release is well received [or not.](https://twitter.com/tuf/status/1229363279388082176) But before that, a little warning, the source code I’m about to show you has been made by me. It’s full of flaws, couple of stack overflow copypasta but… it works. 😅 So Dear practitioners please don’t judge me\ It’s one of the beauties of R, you get your ends relatively easily. (but I gladly accept any ideas to make this code easier for beginner, don’t hesitate to contact me) So let’s get to it, the first step is to grab a [reviews page](https://wordpress.org/support/plugin/wp-fastest-cache/reviews/) URL. On this one, we have 49 pages of reviews. We’ll have to make a loop to run into each pagination. Another problem is that no dates are being displayed but only durations, so we’ll have to convert them. As usual, we’ll first load the necessary packages. If there are not installed yet, run the install.packages() function as seen before. ```r #Loading packages library(tidyverse) library(rvest) ``` ```r # we store plugin url inside a variable, to make the code easy to reuse pluginurl <- "https://wordpress.org/support/plugin/wp-fastest-cache/" # we create and empty dataframe to receive the data that will be retrieved from each pagination. If you don't know what's a data frame think of them as excel file all_reviews <- data.frame() ##### beginning of the LOOP #### # if copy past stuff, don't forget to grab the code until the end of the loop at least for(i in 1:49) { # sending to console the loop status # paste0() function is just a concatenation function with a weird name message(paste0("Page ",i)) # faculative: make a small break betweeh each loop iteration # this pause the loop for 2 secondes # Sys.sleep(2) # we grab the webpage and store the result inside html_page variable to be able to reuse it several times html_page <- read_html(paste0(pluginurl,"reviews/page/",i,"/")) # html_nodes is function that use the css or xpath to extract the value from the html page. This part is to extract the number of stars reviews <- html_nodes(html_page, ".wporg-ratings") ``` If you need help to select elements, chrome inspector is great. You can copy/paste xpath and .css style selector directly:![](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-04-14.00.37-1024x672.png) ```r # Then we are getting every htmml attributes values into columns and rows # it's a copy/past from stackoverflow, it's works don't ask me how. extract <- bind_rows(lapply(xml_attrs(reviews), function(x) data.frame(as.list(x), stringsAsFactors=FALSE))) ``` In other words, it transforms this HTML data hard to deal with![](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-06-14.50.55-1024x412.png) into a clean data frame with nice columns![](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-06-14.51.36.png) ```r # using the extract() function get the number of stars extract <- extract %>% extract(title,c("note")) # same process but this time to extract the duration # Grabing from the html file, the duration being displayed dates <- html_nodes(html_page, ".bbp-topic-freshness") # Extracting the real duration value from text: we remove line breaks and what's after "ago" extract$dates <- html_text(dates, trim = T) %>% str_replace_all("[\r|\n|\t]" , "") %>% str_replace_all(" ago.*$" , "") # apply duration type to values, necessary for future conversions # more info https://lubridate.tidyverse.org/reference/duration.html extract$duration <- lubridate::as.duration(extract$dates) # removing from the data frame the now useless columns & rows extract$class <- NULL extract$title <- NULL extract$style <- NULL extract$note$class <-NULL extract$note$style <- NULL extract <- extract[-1,] # erase rownames rownames(extract) <- c() # converte values to the right type extract$note <- as.vector(extract$note) extract$note <- as.numeric(extract$note) # adding all date retrieved during this loop to the main data frame 'all_reviews' all_reviews <- rbind(all_reviews, extract) ##### END OF THE LOOP ##### } ``` The next step is to convert these durations into days. It’s going to be quick: ```r # .Data is the number of seconds, we divided by 86400 to have the number of days and we round it all_reviews$duration2 <- round(all_reviews$duration@.Data/86400) # Today date minus review age will give us the review date all_reviews$day <- today()-all_reviews$duration2 ``` ```r # we want to see number of stars as a category not as a scale all_reviews$note <- as.factor(all_reviews$note) ``` the data is now ready, [export your data ](https://www.gokam.co.uk/export-your-data-from-r/)or make a small graph to display it using ggplot package ```r library(ggplot2) ggplot(all_reviews, aes(x=day, fill=note))+ geom_histogram() ``` ![](https://www.gokam.fr/wp-content/uploads/2020/03/Rplot.png)
# Website Crawling and SEO extraction with Rcrawler This section is relying on a package called [Rcrawler](https://cran.r-project.org/web/packages/Rcrawler/Rcrawler.pdf) by Salim Khalil. It’s a very handy crawler with some nice functionalities. ### Installation After [R](https://www.r-project.org/) is being installed and [rstudio](https://www.rstudio.com/) launched, same as always, we’ll install and load our package: ```r #install to be run once install.packages("Rcrawler") # and loading library(Rcrawler) ``` ### Crawl an entire website with Rcrawler To launch a simple website analysis, you only need this line of code: ```r Rcrawler(Website = "https://www.gokam.co.uk/") ``` It will crawl the entire website and provide you with the data
Less than 30s to crawl a small website
After the crawl is being done, you’ll have access to: **The INDEX variable** it’s a data frame, if don’t know what’s a data frame, it’s like an excel file. Please note that it will be overwritten every time so [export it](https://www.gokam.co.uk/export-your-data-from-r/) if you want to keep it! To take a look at it, just run ```r View(INDEX) ``` ![INDEX data frame](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-09-18.02.35-1024x476.png) Most of the columns are self-explanatory. Usually, the most interesting ones are ‘**Http Resp**‘ and ‘**Level**‘ The Level is what SEOs call “crawl depth” or “page depth”. With it, you can easily check how far from the homepage some webpages are. Quick example with [BrightonSEO](https://www.brightonseo.com/) website, let’s do a quick ‘ggplot’ and we’ll be able to see pages ![page count by level](https://www.gokam.co.uk/wp-content/uploads/2020/08/brightonSEO_crawl_depht_2020.png) ```r #here the code to run to see the plot # install ggplot plot library to be run once install.packages("ggplot2") # Loading library library(ggplot2) # Convert Level to number INDEX$Level <- as.integer(INDEX$Level) # Make plot # 1 define dimensions (only 'Level') # 2 set up the plot type # 3 customise the x scale, easier to read ggplot(INDEX, aes(x=Level))+ geom_bar()+ scale_x_continuous(breaks=c(1:10)) #alternative command to count webpages per Level table(INDEX$Level) # Should display something like that: # 0 1 2 3 4 5 6 7 8 9 10 # 1 32 306 91 116 127 61 54 90 149 255 ``` **HTML Files** By default, the rcrawler function also store HTML files in your ‘working directory’. Update location by running setwd() function ![](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-09-19.05.35-1024x386.png) Let’s go deeper into options by replying to the most commons questions: ### So how to extract metadata while crawling? It’s possible to extract any elements from webpages, using a CSS or XPath selector. We’ll have to use 2 new parameters * **PatternsNames** to name the new parameters * **ExtractXpathPat** or **ExtractCSSPat** to setup where to grab it in the web page Let’s take an example: ```r #what we want to extract CustomLabels <- c("title", "h1", "canonical tag", "meta robots", "hreflang", "body class") # How to grab it CustomXPaths <- c("///title", "///h1", "//link[@rel='canonical']/@href", "//meta[@rel='robots']/@content", "//link[@rel='alternate']/@hreflang", "//body/@class") Rcrawler(Website = "https://www.brightonseo.com/", ExtractXpathPat = CustomXPaths, PatternsNames = CustomLabels) ``` You can access the scraped data in two ways: * **option 1 =** **DATA** – it’s an environment variable that you can directly access using the console. A small warning, it’s a ‘list’ a little less easy to read ![View(DATA) will display something like that](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-15-16.14.38-1024x670.png) If you want to convert it to a data frame, easier to deal with, here the code: ```r NEWDATA <- data.frame(matrix(unlist(DATA), nrow=length(DATA), byrow=T)) ``` * **option 2 =** **extracted\_data.csv**\ \ It’s a CSV file that has been saved inside your working directory along with the HTML files. It might be useful to merge INDEX and NEWDATA files, here the code ```r MERGED <- cbind(INDEX,NEWDATA) ``` As an example, let’s try to collect webpage type using scraped body class ![Seems that the first word is the page type](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-15-16.35.00.png) Let’s extract the first word and feed it inside a new column ```r MERGED$pagetype <- str_split_fixed(MERGED$X7, " ", 2)[,1] ``` A little bit a cleaning to make the labels easier to read ```r MERGED$pagetype_short <- str_replace(MERGED$pagetype, "-default", "") MERGED$pagetype_short <- str_replace(MERGED$pagetype_short, "-template", "") #it's basically deleting "-default" and "-template" from strings #as it doesn't help that much understanding data ``` ![the 3 steps being displayed](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-15-19.29.37.png) And then a quick ggplot ```r #loading graphic library library(ggplot2) p <- ggplot(MERGED, aes(x=Level, fill=pagetype_short))+ geom_histogram(stat="count")+ scale_x_continuous(breaks=c(1:10)) p ``` ![Count of Pagetype per level](https://www.gokam.co.uk/wp-content/uploads/2020/08/brightonSEO_plot_pagetype_2020.png) Want to see something even cooler? ### An interactive graph ```r #install package plotly the first time #install.packages("plotly") library(plotly) ggplotly(p, tooltip = c("count","pagetype_short")) ``` ![An interactive graph](https://www.gokam.co.uk/wp-content/uploads/2020/08/1Qf42NR6sd.gif) This is a static HTML file that can be store anywhere, even on [my shared hosting](https://www.gokam.co.uk/pagetype.html) ### Explore Crawled Data with rpivottable ```r #install package rpivottable the first time #install.packages("rpivottable") # And loading library(rpivottable) # launch toolrpivotTable(MERGED) ``` ![This create a drag & drop pivot explorer](https://www.gokam.co.uk/wp-content/uploads/2020/08/LgfVsFu6NL.gif) ![It’s also possible make some quick data viz](https://www.gokam.co.uk/wp-content/uploads/2020/08/UmtYC25Kdh.gif) Full [DEMO – see by yourself](https://www.gokam.co.uk/rpivottable.html) ### Extract more data without having to recrawl All the HTML files are stored in your hard drive, so if you need more data extracted, it’s entirely possible. You can list your recent crawl by using ListProjects() function, ![it displays 2 recent crawling projects](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-24-21.32.24.png) First, we’re going to load the crawling project HTML files: ```r LastHTMLDATA <- LoadHTMLFiles("gokam.co.uk-242115", type = "vector") # or to simply grab the last one: LastHTMLDATA <- LoadHTMLFiles(ListProjects()[1], type = "vector") ``` ```r LastHTMLDATA <- as.data.frame(LastHTMLDATA) colnames(LastHTMLDATA) <- 'html' LastHTMLDATA$html <- as.character(LastHTMLDATA$html) ``` Let’s say you forgot to grab h2’s and h3’s you can extract them again using the ContentScraper() also included inside rcrawler package. ```r for(i in 1:nrow(LastHTMLDATA)) { LastHTMLDATA$title[i] <- ContentScraper(HTmlText = LastHTMLDATA$html[i] ,XpathPatterns = "//title") LastHTMLDATA$h1[i] <- ContentScraper(HTmlText = LastHTMLDATA$html[i] ,XpathPatterns = "//h1") LastHTMLDATA$h2[i] <- ContentScraper(HTmlText = LastHTMLDATA$html[i] ,XpathPatterns = "//h2") LastHTMLDATA$h3[i] <- ContentScraper(HTmlText = LastHTMLDATA$html[i] ,XpathPatterns = "//h3") } ``` ![](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-25-22.46.42-1024x427.png) ### Categorize URLs using Regex For those not afraid of regex, here is a complimentary script to categorize URLs. Be careful the regex order is important, some values can overwrite others. Usually, it’s a good idea to place the home page last ```r # define a default category INDEX$UrlCat <- "Not match" # create category name category_name <- c("Category", "Dates", "author page", "Home page") # create category regex, must be the same length category_regex <- c("category", "2019", "author","example\.com.\/$") # categorize for(i in 1:length(category_name)){ # display a dot to show the progress cat(".") # run regex test and update value if it matches # otherwise leave the previous value INDEX$UrlCat <- ifelse(grepl(category_regex[i], INDEX$Url, ignore.case = T), category_name[i], INDEX$UrlCat) } # View variable to debug View(INDEX) ``` ### What if I want to follow robots.txt rules? just had **Obeyrobots** parameter ```r #like that Rcrawler(Website = "https://www.gokam.co.uk/", Obeyrobots = TRUE) ``` ### What if I want to limit crawling speed? By default, this crawler is rather quick and can grab a lot of webpage in no times. To every advantage an inconvenience, it’s fairly easy to wrongly detected as a DOS. To limit the risks, I suggest you use the parameter **RequestsDelay**. it’s the time interval between each round of parallel HTTP requests, in seconds. Example ```r # this will add a 10 secondes delay between Rcrawler(Website = "https://www.example.com/", RequestsDelay=10) ``` Other interesting limitation options: **no\_cores**: specify the number of clusters (logical cpu) for parallel crawling, by default it’s the numbers of available cores. **no\_conn**: it’s the number of concurrent connections per one core, by default it takes the same value of no\_cores. ### What if I want to crawl only a subfolder? 2 parameters help you do that. *crawlUrlfilter* will limit the crawl, *dataUrlfilter* will tell from which URLs data should be extracted ```r Rcrawler(Website = "http://www.glofile.com/sport/", dataUrlfilter ="/sport/", crawlUrlfilter="/sport/" ) ``` ### How to change user-agent? ```r #as simply as that Rcrawler(Website = "http://www.example.com/", Useragent="Mozilla 3.11") ``` ### What if my IP is banned? **option 1: Use a VPN on your computer** **Option 2: use a proxy** Use the **httr** package to set up a proxy and use it ```r # create proxy configuration proxy <- httr::use_proxy("190.90.100.205",41000) # use proxy configuration Rcrawler(Website = "https://www.gokam.co.uk/", use_proxy = proxy) ``` Where to find proxy? It’s been a while I didn’t need one so I don’t know. ### Where are the internal Links? By default, RCrawler doesn’t save internal links, you have to ask for them explicitly by using **NetworkData** option, like that: ```r Rcrawler(Website = "https://www.gokam.co.uk/", NetworkData = TRUE) ``` Then you’ll have two new variables available at the end of the crawling: * **NetwIndex** var that is simply all the webpage URLs. The row number are the same than locally stored HTML files, so\ row n°1 = homepage = 1.html ![](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-15-20.14.14.png)**NetwIndex** data frame * **NetwEdges** with all the links. It’s a bit confusing so let me explain: ![](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-15-20.16.20.png)**NetwEdges** data frame Each row is a link. **From** and **To** columns indicate “from” which page “to” which page are each link.\ \ On the image above:\ row n°1 is a link from homepage (page n°1) to homepage\ row n°2 is a link from homepage to webpage n°2. According to NetwIndex variable, page n°2 is the article about [rvest](https://www.gokam.co.uk/crawling-with-r-using-rvest-package/).\ etc… **Weight** is the Depth level where the link connection has been discovered. All the first rows are from the homepage so Level 0.\ \ **Type** is either 1 for internal hyperlinks or 2 for external hyperlinks ### Count Links I guess you guys are interested in counting links. Here is the code to do it. I won’t go into too many explanations, it would be too long. if you are interested (and motivated) go and check out the [dplyr](https://dplyr.tidyverse.org/) package and specifically [Data Wrangling functions](https://rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf) **Count outbound links** ```r count_from <- NetwEdges[,1:2] %>% #grabing the first two columns distinct() %>% # if there are several links from and to the same page, the duplicat will be removed. group_by(From) %>% summarise(n = n()) # the counting View(count_from) # we want to view the results ``` ![the homepage (n°1) has 13 outbound links](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-17-23.22.18.png) To make it more readable let’s replace page IDs with URLs ```r count_from$To <- NetwIndex View(count_from) ``` ![using website URLs](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-23-22.48.11.png) **Count inbound links** The same thing but the other way around ```r count_to -> NetwEdges[,1:2] %>% #grabing the first two columns distinct() %>% # if there are several links from and to the same page, the duplicat will be removed. group_by(To) %>% summarise(n = n()) # the counting View(count_to) # we want to view the results ``` ![count of inbound links](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-17-23.25.06.png) Again to make it more readable ```r count_to$To <- NetwIndexView(count_to) ``` ![using website URLs](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-23-22.29.18.png) So the useless ‘[author page](https://www.gokam.co.uk/author/gokam/)‘ has 14 links pointing at it, as many as the homepage… Maybe I should fix this one day. ### Compute ‘Internal Page Rank’ [section moved here](https://www.rforseo.com/analysis/page-ranks) ### What if a website is using a JavaScript framework like React or Angular? RCrawler handly includes **Phantom JS**, the classic headless browser.\ Here is how to to use ```r # Download and install phantomjs headless browser # takes 20-30 seconds usually install_browser() # start browser process br <-run_browser() ``` After that, reference it as an option ```r Rcrawler(Website = "https://www.example.com/", Browser = br) # don't forget to stop browser afterwards stop_browser(br) ``` It’s fairly possible to run 2 crawls, one with and one without, and compare the data afterwards This *Browser* option can also be used with the other Rcrawler functions. ⚠️ Rendering webpage means every Javascript files will be run, including **Web Analytics tags**. If you don’t take the necessary precaution, it’ll change your Web Analytics data ### So what’s the catch? Rcrawler is a great tool but it’s far from being perfect. SEO will definitely miss a couple of things like there is no internal dead links report, It doesn’t grab nofollow attributes on Links and there is always a couple of bugs here and there, but overall it’s a great tool to have.\ \ Another concern is the [git repo](https://github.com/salimk/Rcrawler) which is quite inactive. This is it. I hope you did find this article useful, reach to me for slow support, bugs/corrections or ideas for new articles. Take care. ref:\ *Khalil, S., & Fakir, M. (2017). RCrawler: An R package for parallel web crawling and scraping. SoftwareX, 6, 98-106.* # Perform automatic browser tests with RSelenium ## What's **Selenium?** Selenium is a classic tool for [QA](https://en.wikipedia.org/wiki/Quality_assurance) and it can help perform automatic checks on a website. This is an intro to how to use it ## **Why Selenium is an interesting solution?** One of the great advantages of using Selenium is that **you can alternate automatic and manual actions** in the same session.\ \ For example, you can log on somewhere and run an automatic script after pretty easily or… fill in a captcha and run your script. ### Let's start The first step is, as always, to install and load the RSelenium package ```r #install to run once install.packages("RSelenium") library(RSelenium) ``` We’ll launch a selenium server with a Firefox browser in a controlled mode.\ \ It will take quite some time the first time but after it will load in a few seconds. *here is the R command:* ```r rd <- rsDriver(browser = "firefox", port = 4444L) ``` ![](https://www.gokam.fr/wp-content/uploads/2020/03/nk2TuJCDvS.gif) At the end of the process, it should open a firefox window like this one![](https://www.gokam.fr/wp-content/uploads/2019/11/Screenshot-2019-11-17-20.24.31-1.png) Then we’ll grab the instance to be able to control our browser ```r remDr <- rd[["client"]] ``` It’s now possible to send action to our browser. \ To open a website URL just type ```r remDr$navigate("http://www.bbc.com") ``` ![](https://www.gokam.fr/wp-content/uploads/2019/11/Screenshot-2019-11-17-21.08.32.png) You will notice the robot head icon which means that it is a remote-controlled browser
Here are some useful commands: ```r # find a dom element using the class selector and grab inner text remDr$findElement(using = "class", value ="top-story")$getElementText() # find a dom element using a class selector and click on it remDr$findElement(using = "class", value ="top-story")$clickElement() # get h1 textusing a tag selector remDr$findElement(using ="tag", value = "h1")$getElementText() # refresh browser remDr$refresh() ``` \ When you are done with it, don’t forget to ```r # close browser remDr$close() # stop the selenium server rd[["server"]]$stop() # and delete it rm(rd) ``` Otherwise, it’s gonna be a mess when you’ll get back on it ## **How to quickly** configure a **Selenium scenario?** You can check the [documentation](https://cran.r-project.org/web/packages/RSelenium/vignettes/basics.html) to learn all the functions to find DOM elements and actions but there is a quicker way: Use a chrome extension like [Katalon recorder](https://chrome.google.com/webstore/detail/katalon-recorder-selenium/ljdobmomdgdljniojadhoplhkpialdid#:~:text=Katalon%20Recorder%20is%20the%20most,%2C%20automating%20games%2C%20etc.%20%E2%80%94), record and copy/paste the instructions directly ![](/files/-MXYjkO5i5UOU2uYWZKh) # Grab Google Suggest Search Queries using R' To make things easier, I've created two dedicated functions: * `getGSQueries` this one grabs the queries * `suggestGSQueries` this one merges each request's results Just copy and paste those 2 functions inside your RStudio Console ```r getGSQueries <- function (search_query, code_lang) { packages <- c("XML", "httr") if (length(setdiff(packages, rownames(installed.packages()))) > 0) { install.packages(setdiff(packages, rownames(installed.packages()))) } library(httr) library(XML) query <- URLencode(search_query) url <- paste0( "http://suggestqueries.google.com/complete/search?output=toolbar&hl=", code_lang, "&q=", query ) # message(url) # use GET method req <- GET(url) # extract xml # message(req$status_code) xml <- content(req) # parse xml doc <- xmlParse(xml) # extract attributes from # list <- xpathSApply(doc, "//CompleteSuggestion/suggestion", xmlGetAttr, 'data') #print results #print(list) return(list) } ​ ``` ```r suggestGSQueries <- function (search_query, code_lang, level) { if(length(search_query) == 1){ all_suggestion <- getGSQueries(search_query, code_lang) message("level 1") if (level > 1) { for (l in letters) { message("level 2 ", l) Sys.sleep(runif(1, 0, 2)) local_suggestion <- getGSQueries(paste0(search_query," ", l), code_lang) all_suggestion <- c(all_suggestion, local_suggestion) } if (level > 2) { for (l1 in letters) { for (l2 in letters) { Sys.sleep(1+runif(1, 0, 9)) message("level 3 ", l1, l2) local_suggestion <- getGSQueries(paste0(search_query," ", l1, l2), code_lang) all_suggestion <- c(all_suggestion, local_suggestion) } } } } all_suggestion <- unique(all_suggestion) } else { message(1," ",search_query[1]) all_suggestion <- getGSQueries(as.character(search_query[1]), code_lang) for (word in 2:length(search_query)){ Sys.sleep(1+runif(1, 0, 9)) message(word," ",search_query[word]) all_suggestion <- c(all_suggestion,getGSQueries(as.character(search_query[word]), code_lang)) } all_suggestion } } ``` This is how you can use it: ```r kwd <- suggestGSQueries('covid', 'en', 2) ​ View(as.data.frame(unlist(kwd))) ``` The first parameter is the *seed* keyword, the second one is the language (or [host language](https://developers.google.com/custom-search/docs/xml_results?hl=en#WebSearch_Query_Parameter_Definitions:~:text=pizza%26gl%3Duk-,hl,-Description)), and the last one is the level of details (1,2 or 3). **1** will just grab the first suggestion list, **2** will grab suggestions if you add another letter ('covid a', 'covid b', 'covid c', ...), **3**, which I don't recommend, will add two letters (covid aa, covid ab, ..)\ \ it's also possible to pass a vector instead of a string. In this example, we ask for Google suggestions for each of the results in the previous step. it will drastically increase the keyword list and... it might a little bit of time too :) ```r deeper_kwd <- suggestGSQueries(kwd, 'en', 1) View(as.data.frame(unlist(deeper_kwd))) ``` Use these functions with caution because they can send a lot of queries to Google and you might get your IP banned. # Grab Google Analytics Data x ⚠️ THIS IS A WORK IN PROGRESS [googleAnalyticsR](https://code.markedmondson.me/googleAnalyticsR/) is an amazing package by Mark Edmondson ``` ## setup library(googleAnalyticsR) ## authenticate ga_auth() ## get your accounts account_list <- ga_account_list() ## account_list will have a column called "viewId" account_list$viewId ## View account_list and pick the viewId you want to extract data from ga_id <- 123794729 ## simple query to test connection ## simple query to test connection extract <- google_analytics(ga_id, date_range = c("2021-01-01", "2021-02-01"), metrics = "sessions", dimensions = c("date","medium","landingPagePath"), anti_sample = TRUE) View(extract) ``` # Grab keywords search volume from DataForSeo API using R' ### What is DataForSeo? DataForSEO is an all-in-one paid API that provides SEO data. it allows, for example, to retrieve keywords ranking and keyword search volume directly from Google Ads or Bings Ads.
### How to use the DataForSeo API? [>> Create an accout <<](https://app.dataforseo.com/api-dashboard) When you create your account, you are given a few dollars to test the service. ### API Authentication Grab your credentials can be found inside your personal [dashboard](https://app.dataforseo.com/api-dashboard) ![⚠️ Developer credentials (such as passwords, keys and client IDs) should be kept confidential. ⚠️](/files/6YTfdVek4j7hFF4zPmMu) ```r # We need to load a few packages to run this script # # If you don't have them installed yet, # you need to run the dedicated instruction: install.packages('demoPackageName') # Package for working with HTTP requests library(httr) # Package for working with JSON files library(jsonlite) # here you will the values with your own credentials # They can be found here https://app.dataforseo.com/api-dashboard username <- "APILOGIN" password <- "APIPWD" # This will create a header that we'll use to authenticate ourselves each time we use the API. # This code block needs to be kept private as anyone could use your credits. headers = c( `Authorization` = paste('Basic',base64_enc(paste0(username,":",password))), `Content-Type` = 'application/json' ) ``` ## Request Google Ads Search Volume for a keyword Before making the script run over a list, we'll run it for the keyword and break down each steps ```r # here is the list of the parameters of our request data = paste0('[{"device":"all", "search_partners":false, "keywords":["','R for SEO', '"], "location_code":2840, "language_code":"en", "sort_by":"search_volume"}]') # keyword: R for SEO # device: all, we want desktop, tablet and mobile search volume # search_partners:false, because If we are doing SEO, we don't care about Google Ads being displayed outside of Google Search. # location_code":2840 is for the united states # all the location codes can be downloaded from this page https://docs.dataforseo.com/v3/serp/google/locations/?bash # language_code:en, we want English # sort_by: search_volume, this only matters when requesting several keywords at the same time. # This is our data request res <- httr::POST(url = 'https://api.dataforseo.com/v3/keywords_data/google_ads/search_volume/live', httr::add_headers(.headers=headers), body = data) # the httr:: is here to be sure to use the function inside the httr package # This will transcribe the reply into characters res_text <- httr::content(res, "text") # This will transcribe between JSON format data to an easy to read R object. res_json <- jsonlite::fromJSON(res_text, flatten = TRUE) ``` Search volume can be displayed by using this cmd in the terminal. ```r res_json[["tasks"]][["result"]][[1]][["search_volume"]] ``` If the reply is `NULL` something is wrong and you might want to explore the full API response by displaying it in full like this: ```r View(res_json) ``` !\[this is the response from an API request that worked as shown by the success = TRUE at the top Click on the blue arrows to view details]\(/files/s9sBhnWpevE1kelTqOwv) If you are happy with the results you can now save the value. ```r search_volume <- res_json[["tasks"]][["result"]][[1]][["search_volume"]] ``` I would suggest to also store the 'competition', 'competition\_index', 'low\_top\_of\_page\_bid', 'high\_top\_of\_page\_bid'. Better to have more data, its up to you to use it or not later. ```r competition <- res_json[["tasks"]][["result"]][[1]][["competition"]] competition_index <- res_json[["tasks"]][["result"]][[1]][["competition_index"]] klow_top_of_page_bid <- res_json[["tasks"]][["result"]][[1]][["low_top_of_page_bid"]] high_top_of_page_bid <- res_json[["tasks"]][["result"]][[1]][["high_top_of_page_bid"]] ``` Now that the script is validated we can run the script run over the full list ## Request Google Ads Search Volume for a batch of keywords The first step is to load our keywords list ```r # This will prompt a file selector which can be a text file or a CSV file, # as long as, if its a csv, there is keywords in the first one column kwds <- read.csv(file.choose()) # This will remove duplicate values kwds <- unique(kwds) # We will rename the first column name for convenience # and to make the rest of the R script easier to read colnames(kwds)[1] <- "Kwd" ``` Then we run a keyword request through a loop ```r for(i in 1:nrow(kwds)) { # for-loop over rows # if the search volume is already defined we skip to next keyword if(is.null(kwds[i,"search_volume"]) || is.na(kwds[i,"search_volume"])){ data = paste0('[{"device":"all", "search_partners":false, "keywords":["',kwds[i,"Kwd"], '"], "location_code":2840, "language_code":"en", "sort_by":"search_volume"}]') # We don't want the script to stop if one query fails. # So we are using a tryCatch function to avoid that tryCatch( expr = { res <- httr::POST(url = 'https://api.dataforseo.com/v3/keywords_data/google_ads/search_volume/live', httr::add_headers(.headers=headers), body = data) res_text <- httr::content(res, "text") res_json <- jsonlite::fromJSON(res_text, flatten = TRUE) kwds[i,"search_volume"] <- res_json[["tasks"]][["result"]][[1]][["search_volume"]] kwds[i,"competition"] <- res_json[["tasks"]][["result"]][[1]][["competition"]] kwds[i,"competition_index"] <- res_json[["tasks"]][["result"]][[1]][["competition_index"]] kwds[i,"low_top_of_page_bid"] <- res_json[["tasks"]][["result"]][[1]][["low_top_of_page_bid"]] kwds[i,"high_top_of_page_bid"] <- res_json[["tasks"]][["result"]][[1]][["high_top_of_page_bid"]] message(i, " ",kwds[i,"Kwd"], " ok") # (Optional) # make the script sleep between each request # we don't want to go over the API hit rate limit Sys.sleep(2) # (Optional) # save on the hard drive the results, for the paranoid write.csv(kwds, "kwds.csv") }, error = function(e){ # (Optional) # Do this if an error is caught... message(i, " ",kwds[i,"Kwd"], " error") }, warning = function(w){ # (Optional) # Do this if an warning is caught... message(i, " ",kwds[i,"Kwd"], " warning") }, finally = { # (Optional) # Do this at the end before quitting the tryCatch structure... } ) } } ``` ## Request Google Ads Search Volume for a big batch of keywords ⚠️ DataForSEO is actually charging per request. So if you have lots of keywords to check will be much cheaper to group keywords. This is the script that will help you request queries in batches of 100. ```r pas <- 100 i <- 1 # we prepare the kwds to receive the data by adding the proper column kwds[ ,c("spell", "location_code", "language_code", "search_partners", "competition", "competition_index","search_volume", "low_top_of_page_bid", "high_top_of_page_bid")] <- NA for(i in seq(from=1, to=nrow(kwds), by=pas)){ # for-loop over rows if(is.null(kwds[i,"search_volume"]) || is.na(kwds[i,"search_volume"])){ # building the list of kwd to request data <- paste0('[{"device":"all", "search_partners":false, "keywords":["', kwds[i,"Kwd"]) for (idkwd in 1:(pas-1)) { if(!is.null(kwds[i+idkwd,"Kwd"]) && !is.na(kwds[i+idkwd,"Kwd"])){ data <- paste0(data,'", "',kwds[i+idkwd,"Kwd"]) } } data <- paste0(data, '"], "location_code":2840, "language_code":"en", "sort_by":"search_volume"}]') tryCatch( expr = { res <- httr::POST(url = 'https://api.dataforseo.com/v3/keywords_data/google_ads/search_volume/live', httr::add_headers(.headers=headers), body = data) res_text <- httr::content(res, "text") res_json <- jsonlite::fromJSON(res_text, flatten = TRUE) # cleaning results batch <- as.data.frame(do.call(cbind, res_json[["tasks"]][["result"]][[1]])) batch <- data.frame(lapply(batch, as.character), stringsAsFactors=FALSE) data.table::setnames(batch, "keyword", "Kwd") batch$monthly_searches <- NULL # inserting result inside our main data frame kwds kwds[match(batch$Kwd, kwds$Kwd), ] <- batch message(i, " ",kwds[i,"Kwd"], " OK") # (Optional) # make the script sleep between each request # we don't want to go over the API hit rate limit Sys.sleep(5) # (Optional) # save on the hard drive the results, for the paranoid write.csv(kwds, "kwds.csv") }, error = function(e){ # (Optional) # Do this if an error is caught... message(i, " ",kwds[i,"Kwd"], " error") break }, warning = function(w){ # (Optional) # Do this if an warning is caught... message(i, " ",kwds[i,"Kwd"], " warning") }, finally = { # (Optional) # Do this at the end before quitting the tryCatch structure... } ) } } ``` # Grab Google Rankings from VALUE SERP API using R' ### What is VALUE SERP? It's a great paid API that provides ranking data for a cheap price. ### How to use its API? [>> Create an account <<](https://app.valueserp.com/signup) When you create your account, you are given a few dollars to test the service. ### API Authentication Grab your API key from your [profile](https://app.valueserp.com/profile) ![⚠️ Developer credentials (such as passwords, keys and client IDs) should be kept confidential. ⚠️](/files/DGOp4wos0P020cfT4d4q) Here is how you can send query, replace the api\_key with your own below. ```r # Loading the right libraries library(httr) library(jsonlite) # Parameters list params = list( `api_key` = 'XXXXXX', `q` = "covid", `gl` = "fr", `hl` = "fr", `num` = 20, `google_domain` = 'google.fr' ) # q : the search query # gl : 2 letter country code # hl : language code # num : number of result asked # ask for the data res <- httr::GET(url = 'https://api.valueserp.com/search', query = params) # translate to string res_text <- httr::content(res, "text") # translate to a more readable format res_json <- jsonlite::fromJSON(res_text, flatten = TRUE) ``` You can inspect the result by running this command line ```r View(res_json) ``` ![](/files/0MY86dn0j6Nt4lXgQWbI) To make it easier for you, I have created a function that you can copy and paste, just replace the `api_key` with your own below. ```r serpValueRank <- function(myKwd, glang, hlang, nbr, tld){ library(httr) library(jsonlite) all_full_txt <- data.frame(matrix(ncol = 2, nrow = 0)) colnames(all_full_txt) <- c("kwd", "POS1") for (i in 1:length(myKwd)) { params = list( `api_key` = 'XXXXXX', `q` = myKwd[i], `gl` = glang, `hl` = hlang, `num` = nbr, `google_domain` = paste0('google', tld) ) message(i, " ", myKwd[i]) all_full_txt[i, "kwd"] <- myKwd[i] res <- httr::GET(url = 'https://api.valueserp.com/search', query = params) res_text <- httr::content(res, "text") res_json <- jsonlite::fromJSON(res_text, flatten = TRUE) for (rslt in 1:length(res_json[["organic_results"]][["link"]])) { all_full_txt[i, paste0("POS", rslt)] <- res_json[["organic_results"]][["link"]][[rslt]] } } return(all_full_txt) } ``` after this has been copy-pasted, you can just launch the function as many times as you want.\ you can ask for one ranking ```r ranking <- serpValueRank("covid", "fr", "fr", 20, ".fr") View(ranking) ``` or several at once ```r kwds <- c("covid", "covid test", "covid booster") kwdsRankings <- serpValueRank(kwds, "fr", "fr", 20, ".fr") View(kwdsRankings) ``` You can also save those results [as a CSV or an Excel file](/export-data/send-and-read-seo-data-to-excel) ⚠️ By default, VALUE SERP will separate all SERP features. Meaning 'organic result' will exclude the video carousel for example. If you would like all of them flattened into the organic\_results array, then you could use “flatten\_results=true”. The flattened “position” property will include every SERP features # Classify SEO Keywords using GPT-3 & R' Thanks to [OpenAI](https://openai.com/) it's now possible to use artificial intelligence to help your SEO ✨ They have made available an API called GPT-3 that allows us to get a reply from their Generative Pre-trained Transformer (whatever it means) It's possible to ask silly questions in GPT-3 [playground](https://beta.openai.com/playground) but it's also possible to use the API for SEO work. ![](/files/oEsO951IIffWuQIcDb3t) For this example, let's imagine we have a recipe website classicly structured by starters, main courses and desserts. For users and SEO purposes we want to also classify each dish per country of origin. The goal is to build landing pages like "Top traditional german recipes", etc We will ask GPT3 to go through our full catalogue of recipes ### Install and load the OpenAI R Package ```r devtools::install_github("samterfa/openai") library(openai) library(purrr) ``` ### API Keys Grab your API key from here: and your organisation key from ![](/files/7Y4RHBl3iKxi0Rm1bmIK) and replace the value in the code below: ```r Sys.setenv(openai_organization_id = "XXXXX") Sys.setenv(openai_secret_key = "XXXXXX") ``` I've made a small function to make it easier but to explain simply: * We provide a prompt and some examples and ask the AI to complete the text * The `temperature`, it's a number between 0 and 2 and some say it's the randomness. It may require a bit of tweaking, that's why I left it as a function parameter. ```r course_gpt3 <- function(val, temp){ initPrompt <- "Classify each dish by its country of origin" kw1 <- "Creme Brulée" Cl1 <- "France" kw2 <- "Schwarzwälder" Cl2 <- "Germany" kw3 <- "Couscous" Cl3 <- "Morocco" kw4 <- "Paella" Cl4 <- "Spain" kw5 <- "Fish and Chips" Cl5 <- "England" catPrompt = paste0("prompt : ", initPrompt, "\ndish: " , kw1 , ", country :" , Cl1 , "\ndish: " , kw2 , ", country :" , Cl2 , "\ndish: " , kw3 , ", country :" , Cl3 , "\ndish: " , kw4 , ", country :" , Cl4 , "\ndish: " , kw5 , ", country :" , Cl5 , "\n\ndish: ",val,", country :") result <- create_completion( engine_id = 'davinci', max_tokens = 5, temperature = temp, top_p = 1, n = 1, stream = F, prompt = catPrompt) %>% pluck('choices') %>% map_chr(~ .x$text) strsplit(result,"\n")[[1]][1] } ``` And after that you just need to run it. ```r course_gpt3("matcha tea", 0.2) course_gpt3("pizza", 0.2) course_gpt3("nyc pizza", 0.2) course_gpt3("sausage", 0.2) ``` Now that you have this example, it's up to you to update the prompt and the examples to fit your use case. Good luck! # Grab Google Search Console Data x ⚠️ THIS IS A WORK IN PROGRESS ## SearchConsoleR First, we’ll load *searchConsoleR*, an awesome package by [Mark Edmondson](https://github.com/MarkEdmondson1234).\ This will allow us to send requests to Google ‘Search Console API’ very easily. ```r install.packages("searchConsoleR") library(searchConsoleR) ``` and to help to deal with Google Account Authentication (still by Mark Edmondson). It will spare the pain of having to set up an API Key. ```r install.packages("googleAuthR") library(googleAuthR) ``` ### Gather DATA Let’s initiate authentification. This should open a new browser window, asking you to validate access to your GSC account. The script will be allowed to make requests for a limited period of time. ```r scr_auth() ``` This will create a **sc.oauth** file inside your working directory. It stores your temporary Access tokens. If you wish to switch between Google accounts, just delete the file, re-run the command and log in with another account. Let’s list all websites we are allowed to send requests about: ```r # Load sc_websites <- list_websites() # and display the list View(sc_websites) ``` and pick one ```r hostname <- "https://www.example.com/" ``` *don’t forget to update this with your hostname* As you may know, Search Console data is not available right away. If we want, for example, to request data for the last *available* 2 months, we'll need the date range to be between 3 days ago and 2 months before that… [As seen before](/r-intro#lubridate) we will be helped by the Lubridate package ```r install.packages("lubridate") require(lubridate) tree_days_ago <- lubridate::today()-3 beforedate <- tree_days_ago month(beforedate) <- month(beforedate) - 2 day(beforedate) <- days_in_month(beforedate) ``` and **now the actual request (at last!)** ```r gsc_all_queries <- search_analytics(hostname, beforedate, tree_days_ago, c("query", "page"), rowLimit = 80000) ``` We are requesting ‘query’ and ‘page’ dimensions. If you wish, it’s possible to restrict the request to some type of user device, like ‘desktop only’. See function [documentation.](https://www.rdocumentation.org/packages/searchConsoleR/versions/0.3.0/topics/search_analytics) There is no point in asking for a longer time period. We want to know if our web pages currently compete with one another now. *`rowLimit`* is a bit of a big random number, this should be enough. If you have a popular website, with a lot of long-tail traffic. You might need to increase it. API respond is store inside *gbr\_all\_queries* variable as a data frame. ![](https://www.gokam.fr/wp-content/uploads/2019/03/google_search_r.png) If you happen to have several domains/subdomains that compete with each other for the same keywords, this process should be repeated. The results will have to be aggregated, [*bind\_rows*](https://dplyr.tidyverse.org/reference/bind.html) function will help you bind them together. This is how to use it : ```r bind_rows(gsc_queries_1,gsc_queries_2) ``` # Grab 'ahrefs' API data x ⚠️ THIS IS A WORK IN PROGRESS ### What is ahref? Ahrefs is an [All-in-one SEO toolset](https://ahrefs.com/#seo-toolset) that allows to * Optimize your website Site Audit * Analyze your competitors * Study what your customers are searching * Learn from your industry’s top performing content * Track your ranking progress We will be using a package called [RAhrefs](https://github.com/Leszek-Sieminski/RAhrefs) by [Leszek Siemiński](https://twitter.com/leszek_sieminsk) use ahref with R ### Installation ```r # main version on CRAN: install.packages("RAhrefs") # development version: # install.packages("devtools") # devtools::install_github("Leszek-Sieminski/RAhrefs") ``` ### Authentication ```r library("RAhrefs") api_key <- "012345" RAhrefs::rah_auth(api_key) # will return "API authorized" if success ``` ### Checking available reports To check what Ahrefs data are available in R through API, you need to check provided help dataset: ```r library("RAhrefs") View(ahrefs_reports) # view dataset in a new tab (RStudio) print(head(ahrefs_reports, 5)) # see first 5 reports in the console # > report_name function_name short_description url_address # > 1 ahrefs_rank rah_ahrefs_rank Contains the URLs and the rankings. https://ahrefs.com/api/documentation/ahrefs-rank # > 2 anchors rah_anchors Contains the anchor text and the num of backlinks, referring pages and referring domains that has it. https://ahrefs.com/api/documentation/anchors # > 3 anchors_refdomains rah_anchors_refdomains Contains the num of anchors and backlinks with that anchor, per domain. https://ahrefs.com/api/documentation/anchors-refdomains # > 4 backlinks rah_backlinks Contains the backlinks and details of the referring pages, such as anchor and page title. https://ahrefs.com/api/documentation/backlinks # > 5 backlinks_new_lost rah_backlinks_new_lost Contains the new or lost backlinks and details of the referring pages. https://ahrefs.com/api/documentation/backlinks-new-lost ``` ### Checking available reports To check what Ahrefs data are available in R through API, you need to check provided help dataset: ```r library("RAhrefs") View(ahrefs_reports) # view dataset in a new tab (RStudio) print(head(ahrefs_reports, 5)) # see first 5 reports in the console # > report_name function_name short_description url_address # > 1 ahrefs_rank rah_ahrefs_rank Contains the URLs and the rankings. https://ahrefs.com/api/documentation/ahrefs-rank # > 2 anchors rah_anchors Contains the anchor text and the num of backlinks, referring pages and referring domains that has it. https://ahrefs.com/api/documentation/anchors # > 3 anchors_refdomains rah_anchors_refdomains Contains the num of anchors and backlinks with that anchor, per domain. https://ahrefs.com/api/documentation/anchors-refdomains # > 4 backlinks rah_backlinks Contains the backlinks and details of the referring pages, such as anchor and page title. https://ahrefs.com/api/documentation/backlinks # > 5 backlinks_new_lost rah_backlinks_new_lost Contains the new or lost backlinks and details of the referring pages. https://ahrefs.com/api/documentation/backlinks-new-lost ``` ### Checking available metrics To check what metrics can be choosen, you need to check provided help dataset: ```r library("RAhrefs") View(ahrefs_metrics) # view dataset in a new tab (RStudio) print(head(ahrefs_metrics, 5)) # see first 5 metrics in the console # > metric type use_where? use_having? description # > 1 url_from string TRUE TRUE URL of the page where the backlink is found. # > 2 url_to string TRUE TRUE URL of the page the backlink is pointing to. # > 3 ahrefs_rank int TRUE TRUE URL Rating of the referring page. # > 4 domain_rating int FALSE TRUE Domain Rating of the referring domain. # > 5 ahrefs_top int FALSE TRUE Ahrefs Rank of the target domain. ``` However, different functions can accept different metrics for experimental `where` & `having` conditions. To find out which ones are available for a particular function, check that function's documentation. ### Creating conditions Ahrefs API can use `where`, `having` and `order_by` parameters. However, behaviour of `where` and `having` may change in further updates. ```r # first, create all needed conditions in single form: cond_1 <- RAhrefs::rah_condition( column_name = "first_seen", operator = "GREATER_THAN", value = "2018-01-01", is_date = TRUE) cond_2 <- RAhrefs::rah_condition( column_name = "backlinks", operator = "GREATER_THAN", value = "10") # next, create a set of conditions from them: final_condition_set <- RAhrefs::rah_condition_set(cond_1, cond_2) # finally, use the set of conditions to download choosen results: result <- RAhrefs::rah_anchors( target = "ahrefs.com", limit = 1000, where = final_condition_set) ``` ### Usage ```r # library ---------------------------- library("RAhrefs") # authentication --------------------- api_key <- "012345" RAhrefs::rah_auth(api_key) # downloading data ------------------- ahrefs_data <- RAhrefs::rah_anchors( target = "ahrefs.com", mode = "domain", limit = 2, where = RAhrefs::rah_condition_set( RAhrefs::rah_condition( column_name = "backlinks", operator = "GREATER_THAN", value = "10"), RAhrefs::rah_condition( column_name = "refpages", operator = "GREATER_THAN", value = "20")), order_by = "refpages:asc") print(ahrefs_data) # > anchor backlinks refpages refdomains first_seen last_visited # > 1.21 driver 42 21 1 2018-06-06 07:16:28 2019-01-05 10:37:13 # > a href's 21 21 1 2015-11-22 14:30:18 2015-11-22 14:30:18 str(ahrefs_data) # > 'data.frame': 2 obs. of 6 variables: # > $ anchor : chr "1.21 driver" "a href's" # > $ backlinks : int 42 21 # > $ refpages : int 21 21 # > $ refdomains : int 1 1 # > $ first_seen : POSIXct, format: "2018-06-06 07:16:28" "2015-11-22 14:30:18" # > $ last_visited: POSIXct, format: "2019-01-05 10:37:13" "2015-11-22 14:30:18" ``` # Grab Google Custom search API Data x ⚠️ THIS IS A WORK IN PROGRESS ``` library(httr) query="https://www.googleapis.com/customsearch/v1?key=API_KEY&cx=ENGINE_ID&q=SEARCH_TERM" content(GET(query)) ``` # Send requests to the Google Indexing API using googleAuthR *This guide has been written by* [*Ruben Vezzoli*](https://twitter.com/RubenVezzoli) *in July 2020. Ruben is a 25 years old , Italian, Data Analyst checkout*[ *his website* ](https://www.rubenvezzoli.online/)*with other interesting R scripts.* Two years ago, Google introduced the **Indexing API** with the intent of solving an issue that affected jobs/streaming websites – having outdated content in the index. The [Google Developers documentation](https://developers.google.com/search/apis/indexing-api/v3/using-api) says: “*You can use the Indexing API to tell Google to update or remove pages from the Google index. The requests must specify the location of a web page. You can also get the status of notifications that you have sent to Google. Currently, the Indexing API can only be used to crawl pages with either job posting or livestream structured data.*” Many SEOs are using Indexing API also for non-job-related websites and that’s why I decided to build an R script to try out the API (and it worked). I’m going to show you how the script works, but don’t forget that there is a **free quota of 200 URLs sent per day**! ### Create the Indexing API Credentials First of all, you have to generate the client id and client secret keys for the APIs. Open the [Google API Console](https://console.developers.google.com/apis/) and go to the API **Library**. ![](https://www.rubenvezzoli.online/wp-content/uploads/2020/07/indexing-api.jpg) Open the Indexing API page and enable the API. Then go to the **Credentials** page and there you’ll find your credentials. ![](https://www.rubenvezzoli.online/wp-content/uploads/2020/07/indexing-api-credentials-1024x231.jpg) ### googleAuthR package & Indexing API options I created the script using the [googleAuthR](https://code.markedmondson.me/googleAuthR) package, which allows you to send requests to Google APIs. The code takes in **input,** a **character vector of maximum 200 URLs** and returns in a data frame the response of the API (see the screenshot below). You can use the script to update or delete pages. You just have to change the **line 38**: * Use type = “URL\_UPDATED” if you have to update the page * Use type = “URL\_DELETED” if you have to remove the page ![](https://www.rubenvezzoli.online/wp-content/uploads/2020/07/output-indexing-api-1024x132.png) ```r # LAST UPDATE: 20-03-2021 # Install & Load the packages install.packages("googleAuthR") install.packages("tidyverse") install.packages("readr") library("googleAuthR") library("tidyverse") library("readr") # Set credentials and scope clientId <- "PASTE HERE YOUR CLIENT ID" clientSecret <- "PASTE HERE YOUR CLIENT SECRET" scope <- "https://www.googleapis.com/auth/indexing" options("googleAuthR.client_id" = clientId, "googleAuthR.client_secret" = clientSecret, "googleAuthR.scopes.selected" = scope, "googleAuthR.verbose" = 0 # Not mandatory - I just use it to debug the script ) # Google API OAuth gar_auth() # List of URLs - Daily Limit of 200 URLs urls <- read_csv("~/Desktop/Your-file-name.csv") urls <- urls[ ,1] # indexingApi function - you can use the function to send requestes to the indexing API using urls vector as an input # It also GET the response from the API and stores it in a data frame indexingApi <- function(page) { body <- list( url = page, type = "URL_UPDATED") f <- gar_api_generator("https://indexing.googleapis.com/v3/urlNotifications:publish", "POST") result <- f(the_body = body) result <- as.data.frame(result[[6]][[1]][[2]]) return(result) } # The API responses are stored in a data frame APIResponse <- map_dfr( .x = urls, .f = indexingApi) # You can download the API responses as .csv file write.csv(APIResponse, "Your-file-name.csv") ``` # other APIs x ⚠️ THIS IS A WORK IN PROGRESS ### SimilarWeb API The {swapir} package is a tidy API wrapper for the SimilarWeb API. With {swapir}, you can retrieve web analytics metrics from a given site such as number of visits, visit duration, visitor demographic info {% embed url="" %} # Send and read SEO data to Excel/CSV CSV and Excel file remain one of amongst the most well-used file formats for exchange data. ### Read your data from a CSV ``` #setup where to read the file setwd("~/Desktop") # en write the file test <- read.csv(df, "data.csv") ``` ### Export your data into a CSV assuming your data is store inside **df** var, fairly simple: ``` #setup where to write the file setwd("~/Desktop") # en write the file write.csv(df, "data.csv") ``` ### Read an excel ``` # the file.choose() will prompte a file selector # the 1 say we want to load the first tab test <- xlsx::read.xlsx(file.choose(),1) ``` ### Export your data into an excel file A little bit more complex, we’ll use the ‘xlsx’ package ``` #setup where to write the file setwd("~/Desktop") # if the package is not instal yet, run this # install.packages("xlsx") # Loading the package library(xlsx) # we write the file write.xlsx(df, "data.xlsx") ``` A few more tips for you: I’ll like to use the **sheetName** option to explicitly name the tab. The default name is “Sheet1”. Quite useful to have a record of when the file has been generated for example. Replace last instruction what follows and you’ll be able to know. ``` write.xlsx(df, "data.xlsx", sheetName=format(Sys.Date(), "%d %b %Y")) ``` Another good one that I like is to send the excel file to a Shared folder directly. Replace first instruction by ``` setwd("/Users/me/Dropbox/Public") ``` Of course, replace the file path with yours. ### Import and merge a batch of CSV files Aggregate several CSV files into one using file name as a column ``` library(plyr) library(readr) library(purrr) # add the path where the csv's are located setwd("./Downloads/test/") # list csv files inside the directory # for each: import the csv (read.csv function) # add filename as a column # and merge Tbl <- list.files(path = "./", pattern="*.csv", full.names = T) %>% map_df(function(x) read_csv(x, col_types = cols(.default = "c")) %>% mutate(filename=gsub(".csv","",basename(x)))) View(Tbl) ``` # Send your data by email using gmail API When a script has finished running, you may want to email the results by using the Gmail API.\ \ you must first install and load the package gmailr package. ```r install.packages("gmailr") library(gmailr) ``` if you are going to display some kind of table I would suggest to also install the tableHTML package. ```r install.packages("tableHTML") library(tableHTML) ``` ``` # # Packages loading # ``` The Next step is to connect to Gmail API. You need to activate it in your Google Cloud project [More info here ](https://gargle.r-lib.org/articles/get-api-credentials.html)and then replace the fake value with your key and secret in the following example. ```r gm_auth_configure("mykey.apps.googleusercontent.com", "mysecret") ``` This commande will transform your dataframe into an html table ```r #transform the data frame 'df' to a html table msg = tableHTML(df) # Construct email # end send it ``` we build the email ``` test_email <- gm_mime() %>% gm_to("another@example.com") %>% gm_from("me@example.com") %>% gm_subject("Email title") %>% gm_html_body(paste("Hi Mate,
Here are the data you requested:", msg,"

Kind regards,
Me")) ``` and we send it ``` gm_send_message(test_email) ``` # Send and read SEO data to Google Sheet x ⚠️ THIS IS A WORK IN PROGRESS ``` install.packages("googlesheets4") library(googlesheets4) gs4_auth() (demo <- gs4_create("demo", sheets = list(flowers = head(iris)))) class(demo) read_sheet(demo) gs4_get(demo) ``` This package is also interesting to reach public google sheets. ``` gs4_deauth() salary <- read_sheet("https://docs.google.com/spreadsheets/d/1rGCKXIKt-7l5gX06NAwO3pjqEHh-oPXtB8ihkp0vGWo/view", skip = 1) ``` # Join Crawl data with Google Analytics Data The SEO data to be analyzed often comes from different sources that why it's better to know how to connect or merge them. \ \ Let's imagine we have crawled your website, it might be quite nice to check which one of these pages got some SEO traffic. To do that we'll need to `merge` or `join` the two "datasets" ### 1. Crawl data Using `rcrawler`, we've collected our pages (see [How to use rcrawler](/crawl/rcrawler) article) ```r library(Rcrawler) Rcrawler(Website = "https://www.rforseo.com/") ``` We now have a dataset (dataframe) of urls associated to their crawl depht called `INDEX` ```r View(INDEX) ``` ![second column is the url](/files/-MYqUZoEsqh5RPnp5ZFk) ### 2. Google analytics data Using `googleAnalyticsR` package we grab Google Analytics SEO Landing page (see [How so use googleAnalyticsR](/apis/web-analytics-google-analytics) article) ```r # Between 1 january and 1 feb 2021 # we want the sessions # we request landing and medium info too # and using the anti sampling option ga <- google_analytics(ga_id, date_range = c("2021-01-01", "2021-02-01"), metrics = "sessions", dimensions = c("medium","landingPagePath"), anti_sample = TRUE) # We filter the data to only keep the SEO sessions ga_seo <- ga %>% filter(medium == "organic") ``` ### 3. Fuuuuu...sion! First, you need to define what's the common ground. We have on the crawler data side the `Url` column and on the GA side the `landingPagePath` So we need to make a conversion. We'll remove the hostname from the Url using the `path` function `urltools` package. ```r INDEX$landingPagePath <- paste0("/",urltools::path(INDEX$Url)) INDEX$landingPagePath[INDEX$landingPagePath == "/NA"] <- "/" ``` and now we can merge ```r crawl_ga_merged <- merge(INDEX,ga_seo) ``` That's it really. Lets display the data ```r View(crawl_ga_merged) ``` ![](/files/-M_aL8mONImRmQbaR0Ed) # Count words, n-grams, shingles x ⚠️ THIS IS A WORK IN PROGRESS ``` library(stringr) top_words <- all_full_txt %>% unnest_tokens(word, txt) %>% anti_join(get_stopwords()) %>% filter(!str_detect(word, "[0-9]+") == TRUE) %>% group_by(url) %>% count(word, sort = F) %>% View() ``` # Hunt down keyword cannibalization ⚠️ THIS IS A WORK IN PROGRESS ### What is keyword cannibalization? if you put a lot of articles out there, at some point, some articles will compete with one another for the same keywords in Google result pages. it’s what SEO people call ‘keyword cannibalization’. ### Does it matter SEO-wise? Sometimes it’s perfectly normal. I hope, for your sake, that several of your web pages show up when someone is typing your brand name in Google. Sometimes it’s not. Let me give an example: 💭 Imagine you run an e-commerce website, with various page type: products, FAQ’s, blog posts, … At some point, Google decided to make a switch: a couple of Google search queries that were sending traffic to product pages, now display a blog post of yours. Inside Google Analytics, SEO sessions count is the same. Your ‘Rank Tracking’ software will not bring you up any position changes. And yet, these blog post pages will be able to convert much less, and at the end of the month, this will result in a decrease in sales. Sometimes it can't be fixed, the search intent is now different but sometimes it just because you neglected your product pages. Either way, it's good to know what's happening. ### How to check for keyword cannibalization? There are several ways to do it. Of course, SEO tools people want you to use their tools, the [method from ahref](https://ahrefs.com/blog/keyword-cannibalization/) is definitely useful. Unfortunately, this kind of tool can be sometimes imprecise, it doesn’t take into account what’s really happening. So let do it using Google Search Console and R’. Once set up, you’ll be able to check big batches of keywords in minutes. ### step 0: [install R & rstudio](/classic-r-operations#install-r) ### step 1: install the necessary packages First, we’ll load `searchConsoleR` package by [Mark Edmondson](https://github.com/MarkEdmondson1234).\ This will allow us to send requests to Google ‘Search Console API’ very easily. ```r install.packages("searchConsoleR") library(searchConsoleR) ``` Then let’s load *tidyverse*. For those who don’t know about it, it’s a very popular master package that will allow us to work with data frames and in a graceful way. ```r install.packages("tidyverse") library(tidyverse) ``` and finally, something to help to deal with Google Account Authentication (still by Mark Edmondson). It will spare the pain of having to set up an API Key. ```r install.packages("googleAuthR") library(googleAuthR) ``` ### step 2 – gather DATA Let’s initiate authentification. This should open a new browser window, asking you to validate access to your GSC account. The script will be allowed to make requests for a limited period of time. ```r scr_auth() ``` This will create a **sc.oauth** file inside your working directory. It stores your temporary Access tokens. If you wish to switch between Google accounts, just delete the file, re-run the command and log in with another account. Let’s list all websites we are allowed to send requests about: ```r sc_websites <- list_websites() View(sc_websites) ``` and pick one ```r hostname <- "https://www.example.com/" ``` *don’t forget to update this with your hostname* As you may know, Search Console data is not available right away. That’s why we want to request data for the last *available* 2 months, so between 3 days ago and 2 months before that… again using a little useful package! ```r install.packages("lubridate") require(lubridate) tree_days_ago <- lubridate::today()-3 beforedate <- tree_days_ago month(beforedate) <- month(beforedate) - 2 day(beforedate) <- days_in_month(beforedate) ``` and **now the actual request (at last!)** ```r gsc_all_queries <- search_analytics(hostname, beforedate, tree_days_ago, c("query", "page"), rowLimit = 80000) ``` We are requesting ‘query’ and ‘page’ dimensions. If you wish, it’s possible to restrict request to some type of user device, like ‘desktop only’. See function [documentation.](https://www.rdocumentation.org/packages/searchConsoleR/versions/0.3.0/topics/search_analytics) There is no point in asking for a longer time period. We want to know if our web pages currently compete with one another now. *rowLimit* is a bit of a big random number, this should be enough. If you have a popular website, with a lot of long tail traffic. You might need to increase it. API respond is store inside *gbr\_all\_queries* variable as a data frame. ![](https://www.gokam.fr/wp-content/uploads/2019/03/google_search_r.png) If you happen to have several domains/subdomains that compete with each other for the same keywords, this process should be repeated. The results will have to be aggregated, [*bind\_rows*](https://dplyr.tidyverse.org/reference/bind.html) function will help you bind them together. This is how to use it : ```r bind_rows(gsc_queries_1,gsc_queries_2) ``` ### step 3 – clean up First, we’ll filter out queries that are not on the 2 first SERPs and that doesn’t generate any click. There is no point of making useless time-consuming calculations. We’ll also remove branded search queries using a regex. As said earlier, having several positions for your brand name is pretty classic and shouldn’t be seen as a problem. ```r gsc_queries_filtered <-gsc_all_queries %>% filter(position<=20) %>% filter(clicks!=0) %>% filter(!str_detect(query, 'brandname|brand name')) ``` *update this with your brand name* ### step 4 – computations We want to know for one query, what percentage of clicks are going to each landing page. First, we’ll create a new column **clicksT** with the aggregated number of clicks for each search query.\ Then, using this value to calculate what we need inside a new **per** column. ```r gsc_queries_computed <- gsc_queries_filtered %>% group_by(query) %>% mutate(clicksT= sum(clicks)) %>% group_by(page, add=TRUE) %>% mutate(per=round(100*clicks/clicksT,2)) View(gsc_queries_computed) ``` A **per** column value of 100 means that all clicks go the same URL. Last final steps, we will sort rows ```r gsc_queries_final <- gsc_queries_computed %>% arrange(desc(clicksT)) ``` It could also make sense to remove rows where cannibalization is not significant. Where **per** column value is not very high. Removing now useless columns: click, impression and total click per query group ```r gsc_queries_final <-gsc_queries_final[,c(-3,-4,-7)] ``` Now it’s your choice to display it inside rstudio ```r View(gsc_queries_final) ``` Or write a CSV file to open it elsewhere ```r write.csv(gsc_queries_final,"./gsc_queries_final.csv") ``` Here is my rstudio view (anonymized sorry 🙊) ![](https://www.gokam.fr/wp-content/uploads/2019/03/Screenshot-2019-03-19-19.23.34-copy.png) ### step 5 – analysis You should check data inside each “query pack”. Everything is sorted using the total number of clicks, so, first rows are critical, bottoms rows not so much. To help you deal with this, let’s check the first one’s ![](https://www.gokam.fr/wp-content/uploads/2019/03/seqrch-query-1.jpg) ***For Search query 1:***\ 97% of click are going to the same page. Their is no Keyword cannibalization here. It’s interesting to notice that the ‘second’ landing page, only earn 1,4% of clicks, even though, it got an average position of 1,5. Users really don’t like the second ‘Langing page’. Page metadata probably sucks. Check if the first landing page is the right one and we should move on. ![](https://www.gokam.fr/wp-content/uploads/2019/03/seqrch-query-2.jpg) ***For Search query 2:***\ 63% of clicks are going to the first landing page. 36% to the second page. This is Keyword cannibalization.\ It could make sense to adapt internal linking between involved landing pages to influence which one should rank before the other one’s, depending on your goals, pages bounce rates, etc. And so on… This is it, I hope you’ll find it be useful. # Duplicate content analysis x ⚠️ THIS IS A WORK IN PROGRESS Now that we have count words, we can try to analyze them # Compute ‘Internal Page Rank’ ⚠️ THIS IS A WORK IN PROGRESS It is very much an adaptation of [Paul Shapiro](https://twitter.com/fighto) awesome [Script](https://gist.github.com/pshapiro/616b64a4e4399326c82c34734885d5bd) but Instead of using ScreamingFrog export file, we will use the data from a [Rcrawler](/crawl/rcrawler) crawl. Lets crawl with the link data enabled ```r Rcrawler(Website = "https://www.rforseo.com", NetworkData = TRUE) ``` When it's done, The links will be stored in the `NetwEdges` variable. ```r View(NetwEdges) ``` ![](/files/-MamROdqd99jAKtFY5zu) \ \ We only want to first 2 column: ```r library(dplyr) links <- NetwEdges[,1:2] %>% #grabing the first two columns distinct() # loading igraph package library(igraph) # Loading website internal links inside a graph object g <- graph.data.frame(links) # this is the main function, don't ask how it works pr <- page.rank(g, algo = "prpack", vids = V(g), directed = TRUE, damping = 0.85) # grabing result inside a dedicated data frame values <- data.frame(pr$vector) values$names <- rownames(values) # delating row names row.names(values) <- NULL # reordering column values <- values[c(2,1)] # renaming columns names(values)[1] <- "url" names(values)[2] <- "pr" View(values) ``` ![Internal Page Rank calculation](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-17-23.57.20.png) Let make it more readable, we’re going to put the number on a ten basis, just like when the PageRank was a thing. ```r #replacing id with url values$url <- NetwIndex # out of 10 values$pr <- round(values$pr / max(values$pr) * 10) #display View(values) ``` ![](https://www.gokam.fr/wp-content/uploads/2020/03/Screenshot-2020-03-18-00.09.37.png) On 15 webpages website, it’s not very impressive but I encourage you to try on a bigger website. # SEO traffic Forecast x ⚠️ THIS IS A WORK IN PROGRESS ![](/files/-MYyxqBJvumfTiI7mIST) For this one will be using a Forcast library done by Facebook called [**Prophet**](https://facebook.github.io/prophet/) ``` path <- file.choose() df <- read.csv(path, skip = 6) View(df) df <- data<-na.omit(df) library(lubridate) library(prophet) origin_date <- ymd("2019-01-01") origin_date + ddays(1) df$index <- as.numeric(rownames(df))-1 df$ds <- origin_date+ddays(df$index-1) df$ds <- df$Day.Index df$y <- df$Sessions df$Sessions <- NULL df$Day.Index <- NULL #ggplot(df) m <- prophet(df) future <- make_future_dataframe(m, periods = 365) # R forecast <- predict(m, future) tail(forecast[c('ds', 'yhat', 'yhat_lower', 'yhat_upper')]) # View(forecast) plot(m, forecast) prophet_plot_components(m, forecast) dyplot.prophet(m, forecast) ``` # URLs categorization ```r # default category INDEX$UrlCat <- "Not match" # create category name category_name <- c("Category", "Dates", "Page Auteur", "Page d'accueil") # create category regex, must be the same length category_regex <- c("category", "2019", "author","example\.com.\/$") # categorize for(i in 1:length(category_name)){ cat(".") INDEX$UrlCat <- ifelse(grepl(category_regex[i], INDEX$Url, ignore.case = T), category_name[i], INDEX$UrlCat) } # debug View(INDEX) ``` # Track SEO active pages percentage over time x ### What are active pages? and Why would you want to track them? An active page is a page which generates at least one SEO visit over a period. If a page has at least one visit it means that its indexed and 'Google" doesn't think it's a useless page. It is a good indicator of the SEO health of a website. To make things even more interesting we will grab google search console data and compare them to the number of pages submitted in the XML sitemap file. ### step 1: Counting active URLs using Search Console data ( see article about [grabbing Search Console data](/apis/searchconsoler-x)) ```r library(searchConsoleR) library(googleAuthR) scr_auth() # Load sc_websites <- list_websites() # and display the list View(sc_websites) # pitck the one hostname <- "https://www.rforseo.com/" require(lubridate) # we want data between now and 2 months ago now <- lubridate::today()-3 month(beforedate) <- month(now) - 2 day(beforedate) <- days_in_month(beforedate) # we ask for data with dates and pages gsc_all_queries <- search_analytics(hostname, beforedate,now, c("date", "page"), rowLimit = 80000) ``` ```r library(dplyr) # we count url with clicks gsc_all_queries_clicks <- gsc_all_queries %>% filter(clicks != 0) %>% group_by(date) %>% tally() colnames(gsc_all_queries_clicks) <- c("date","clicks") # we count url with impressions gsc_all_queries_impr <- gsc_all_queries %>% filter(impressions != 0) %>% group_by(date) %>% tally() colnames(gsc_all_queries_impr) <- c("date","impr") # we merge those two gsc_all_queries_stats <- merge(gsc_all_queries_clicks, gsc_all_queries_impr) ``` ```r # we scrape the url count from github csv urls <- read.csv(url("https://raw.githubusercontent.com/pixgarden/scrape-automation/main/data/xml_url_count.csv")) # rename columns colnames(urls) <- c("date","urls") # transform string date into real dates urls$date <- as.Date(urls$date) # merge with google search console data # because column names match the merge function dont need arguments gsc_all_queries_merged <- merge(gsc_all_queries_stats, urls) ``` ```r # we count url with no but with impression gsc_all_queries_merged$impr <-gsc_all_queries_merged$impr - gsc_all_queries_merged$clicks # we count url with no impression and no clicks gsc_all_queries_merged$urls <-gsc_all_queries_merged$urls - gsc_all_queries_merged$impr # rename columns colnames(gsc_all_queries_merged) <- c("date", "url-with-clics","url-only-impr","url-no-impr") ``` ```r require(tidyr) test <- gather(gsc_all_queries_merged, urls, count, 2:4) esquisse::esquisser(test) ggplot(test) + aes(x = date, fill = urls, weight = count) + geom_bar() + scale_fill_hue() + theme_minimal() library(ggplot2) ggplot(test) + aes(x = date, fill = urls, weight = count) + geom_bar() + scale_fill_hue() + theme_minimal() ``` # Why Data visualisation is important? x ⚠️ THIS IS A WORK IN PROGRESS As an SEO professional, one of your goals is to communicate your findings to your teams, clients or boss. One of the best ways to do that is to communicate using data visualization. A little plot will be 100 times more effective than a paragraph of text. Especially in a sector that needs as much trust as SEO # Use Esquisse to create plots quickly ggplots is great package but it can be a bit overwhelming to deal with. So many options and functions. Lucky for us, thanks to Fanny Meyer & Victor Perrier, there is a shortcut, the [esquisse package](https://dreamrs.github.io/esquisse/index.html). It basically helps explore your data quickly and build a ggplot. Let me take an example\ \ As always *installing* and *loading* ``` install.packages("esquisse") library("esquisse") ``` After that, only one line of code to make the magic happens ``` esquisser() ``` ![esquisse wiward](/files/-MYWo3fVcv04CXzsMDzY) you pick your dataset, the field you want to import and it's now possible to choose what metrics to display on each axis by drag & dropping them into each selector ![](/files/-MYWpahtQgCrdXKoAX34) Pick the plot style ![](/files/-MYWqSt1Bofg88Dh7dlY) you can also customise the plot titles and legends ![](/files/-MYWpsFwLYtI1gY2EFn3) you can pick the colors and plot style ![](/files/-MYWpxttynURaCtljypb) Filter the values ![](/files/-MYWq6LPlLCpKoG7n_Nm) Last and foremost, it gives you the code to generate this plot + some extra export option. ![](/files/-MYWqEXIEgK7gvbFUeLn) Esquisse is not capable of doing every available ggplots but for a simple graph, it's a great way to speed up the processs. # Explore data with rPivotTable [rpivotTable](https://cran.r-project.org/web/packages/rpivotTable/vignettes/rpivotTableIntroduction.html) is a great package by Enzo Martoglio that allows you to explore a small dataset using an HTML drag\&drop interface that looks like the pivot interface from Google Sheet or Excel. ## Install and load rPivotTable as usual the instruction are quite straightforward ```r #install package rpivottable to be done once install.packages("rpivottable") # And loading Library(rpivottable) ``` Imagine you want to explore a data frame called MERGED ( see [how to create a data frame using a CSV file](/export-data/send-and-read-seo-data-to-excel#read-your-data-from-a-csv) ) **Its just one line of code** ```r # launch rpivotTable(MERGED) ``` it will open an HTML page, and you'll be able to drag and drop KPIs from the left column ![This create a drag & drop pivot explorer](https://www.gokam.co.uk/wp-content/uploads/2020/08/LgfVsFu6NL.gif) You can also use the top dropdown list to make it display a plot instead of a table. ![It’s also possible make some quick data viz](https://www.gokam.co.uk/wp-content/uploads/2020/08/UmtYC25Kdh.gif) To easily sort ![](/files/-MZvmSpYC2Ao60vHrGmU) and filter ![](/files/-MZvmaGdTHANQKXQ5rKP) [Here is a demo HTNL file ](https://www.gokam.co.uk/rpivottable.html)to try it yourself. # Launch an R script using github actions The easiest way to do that is to duplicate this repository on GitHub {% embed url="" %} Just push the "Fork" button to create your copy. ![](/files/-M_Hr0rAXKwj7TUxaJ4K) Let me explain how it works. It's basically all about two files: ### **sitemap\_scraping.R** this is the classic R script. It reaches this website [XML sitemap](https://www.rforseo.com/sitemap.xml) and counts the number of url submitted. It relies on `rvest` package ( see [article about rvest](/crawl/rvest) ) ```r #Load library library(tidyverse) library(rvest) # declare XML sitemap url url <- 'https://www.rforseo.com/sitemap.xml' # grab html url_html <- read_html(url) # Select all the 's # and count them nbr_url <- url_html %>% html_nodes("loc") %>% length() # create a new row of data, with todayd's date and urls number row <- data.frame(Sys.Date(), nbr_url) # append at the end of the csv the new data write_csv(row,paste0('data/xml_url_count.csv'),append = T) ``` ### main.yml This is where we are going to schedule the process. ```r name: sitemap_scraping # Controls when the action will run. on: schedule: - cron: '0 13 * * *' jobs: autoscrape: # The type of runner that the job will run on runs-on: macos-latest # Load repo and install R steps: - uses: actions/checkout@master - uses: r-lib/actions/setup-r@master # Set-up R - name: Install packages run: | R -e 'install.packages("tidyverse")' R -e 'install.packages("rvest")' # Run R script - name: Scrape run: Rscript sitemap_scraping.R # Add new files in data folder, commit along with other modified files, push - name: Commit files run: | git config --local user.name actions-user git config --local user.email "actions@github.com" git add data/* git commit -am "GH ACTION Headlines $(date)" git push origin main env: REPO_KEY: ${{secrets.GITHUB_TOKEN}} username: github-actions ``` Parts you may want to modify are * the execution frequency rule. It's the weird line with `cron.` this one means " Runs at 13:00 UTC every day." here is the full [syntax documentation](https://docs.github.com/en/actions/reference/events-that-trigger-workflows#scheduled-events).
* If you are using packages, you need to ask Github to install them before running the script so be sure to include those on the list. the resulting CSV is updated every day and can be scrape {% embed url="" %} ![](/files/-M_aHi8bgCFnAabKPEpY) RAW LINK: ### # Types / Class & packages x ⚠️ THIS IS A WORK IN PROGRESS Its good practice to check that you are dealing with ## How do you check an object type? you should use a *class* function(). here are some examples ```r x <- 2 class(x) # should display "numeric" y <- "2" class(y) # should display "character" ``` ## the classic types ### data frames ### xml\_document ## advanced types # SEO & R People x ⚠️ THIS IS A WORK IN PROGRESS ## MJ Cachón 🇪🇸 SEO consultant and director of LAIKA {% embed url="" %} ## Ben Johnston 🇬🇧 {% embed url="" %} ## Zach Doty 🇺🇸 {% embed url="" %} ## Rémi Bacha 🇫🇷 {% embed url="" %} ## Marco Giordano 🇨🇭 {% embed url="" %} ## Ruben Vezzoli 🇮🇹 {% embed url="" %} # Execute R code online ### Use RPubs to publish R code If the data is public and you want to show off your R skills you can use rpubs directly You'll need to install the [knitr](https://github.com/yihui/knitr#readme) package (v0.5 or later), there is a mini-tutorial just after registration [Create an account ](https://rpubs.com/) ### Use RStudio Cloud to publish private code. There is a 'Cloud' version of RStudio, perfect for a quick demo. For more serious work, there is some [dedicated plans](https://rstudio.cloud/plans/free). ![](/files/-MYVh9ImPh-XdZefuu0l) Create an account here ### Use GoogleColab for collaborative work This is a little-known option but it's possible to use R inside GoogleColab Start [*rmagic*](https://rpy2.github.io/doc/latest/html/interactive.html) by executing this in a cell: ``` %load_ext rpy2.ipython ``` Then start your script by `%%R` to execute ```r %%R x <- 1:10 x # it should display number from 1 to 10 ``` [more details about this feature](https://towardsdatascience.com/how-to-use-r-in-google-colab-b6e02d736497) #### Native R on GoogleColab you can switch runtime from Python to R in the options and execute native code directly ( *tested in may 2024* )
#### Execute R code on GitHub [See this article](/ressources/launch-an-r-script-using-github-actions) # useful SEO XPath's & CSS selectors X ⚠️ THIS IS A WORK IN PROGRESS If you are crawling website for SEO purposes, whether it is with [rcrawler](/crawl/rcrawler), [rvest](/crawl/rvest) or another way. You will probably need to use some CSS/Xpath selector to extract the useful bit from each page.\ \ here is a collection of the most useful ones. Feel free to reach out if you think it misses important ones. | Value | xpath | CSS | | ---------------------- | ------------------------------------- | -------------------------------- | | Robots (Index/Noindex) | //meta\[@name='robots']/@content | head > meta\[rel="robots"] | | canonical tag | //link\[@rel='canonical']/@href | head > link\[rel="canonical"] | | Page Title | //title | head > title | | Meta Description | //meta\[@name='description']/@content | head > meta\[name="description"] |