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 article)
1
library(Rcrawler)
2
Rcrawler(Website = "https://www.rforseo.com/")
Copied!
We now have a dataset (dataframe) of urls associated to their crawl depht called INDEX
1
View(INDEX)
Copied!
second column is the url
2. Google analytics data
Using googleAnalyticsR package we grab Google Analytics SEO Landing page (see How so use googleAnalyticsR article)
1
​
2
# Between 1 january and 1 feb 2021
3
# we want the sessions
4
# we request landing and medium info too 
5
# and using the anti sampling option
6
​
7
ga <- google_analytics(ga_id, 
8
    date_range = c("2021-01-01", "2021-02-01"),
9
    metrics = "sessions",
10
    dimensions = c("medium","landingPagePath"),
11

    anti_sample = TRUE)
12
​
13
​
14
# We filter the data to only keep the SEO sessions
15
​
16
ga_seo <- ga %>% filter(medium == "organic")
Copied!
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. 
1
INDEX$landingPagePath <- paste0("/",urltools::path(INDEX$Url))
2
​
3
INDEX$landingPagePath[INDEX$landingPagePath == "/NA"] <- "/"
Copied!
and now we can merge
1
crawl_ga_merged <- merge(INDEX,ga_seo)
Copied!
That's it really. Lets display the data
1
View(crawl_ga_merged)
Copied!
​
​

Export and read Data - Previous

Send and read SEO data to Google Sheet x

Next - data wrangling & analysis

Count words, n-grams, shingles x

Last modified 3mo ago

Copy link

Contents

1. Crawl data

2. Google analytics data

3. Fuuuuu...sion!