Can we predict the crawling of the Google-Bot?

Estimating the hits of a single page

Now if we add a new page or otherwise want to predcit how often a single page will be visited by the crawler, we would like to build a prediction model. For this, we will use 4 sources of data:

1. The Loghero data – Where was the crawler in the past and how often?
2. The standard Google Analytics Data – Where and how long do human users stay?
3. Screaming Frog page data – Meta data of each page (text ratio, word count, size…)
4. Screaming Frog link data – (Unique) in and outlinks to the page

So let us gather all this data. We will be using the googleAnalyticsR package this time.

# GA data
googleAnalyticsR::ga_auth(new_user = TRUE)

# Loghero Data
df_loghero = google_analytics_3(id = ">your_LOGHERO_ID<",start = '2018-05-01',end = '2018-10-15',
                       dimensions = c("ga:dimension2","ga:dimension1", "ga:dimension4" ,"ga:pagePath"),
                       metrics = c("ga:hits","ga:metric1"),
                       filters = "ga:dimension2=~^Googlebot(\\-Mobile)?",
                       samplingLevel = "WALK")

# Google Analytics data
df_user_ga = google_analytics_3(id = ">your_GA_ID",start = '2018-05-01',end = '2018-08-15',
                                dimensions = c("ga:pagePath","ga:hostname"),
                                metrics = c("ga:pageviews","ga:avgTimeOnPage","entrances"),
                                samplingLevel = "WALK")

# Screaming Frog data
# Written to csv files in screaming frog application
page_data = fread(input = "internal_html.csv")
page_inlinks = fread(input = "all_inlinks.csv")

To merge all our data together we have to do some basic data wrangling.

library(googleAnalyticsR)
library(igraph)
library(ranger)
library(data.table)
library(dplyr)

# Feature Engineering and Type Casting
df_loghero$avgMetric1 = df_loghero$metric1 / as.numeric(df_loghero$hits) 
df_loghero$hits = as.factor(df_loghero$hits)

# Build into a single file
page_data$pagePath = gsub(pattern = "http://ohren-reinigen.de","",page_data$Address)

df_loghero$pagePath<-gsub("sitemap", "sitemap",df_loghero$pagePath)
df_loghero$pagePath<-gsub(".*sitemap.*", "sitemap",df_loghero$pagePath)
df_loghero$pagePath<-gsub("^\\/$", "Home",df_loghero$pagePath)

df_user_ga$pagePath<-gsub("sitemap", "sitemap",df_user_ga$pagePath)
df_user_ga$pagePath<-gsub(".*sitemap.*", "sitemap",df_user_ga$pagePath)
df_user_ga$pagePath<-gsub("^\\/$", "Home",df_user_ga$pagePath)

# summarise 
df_user_ga %>% 
  group_by(pagePath) %>% 
  summarise(pageviews = sum(pageviews),
            avgTimeOnPage = mean(avgTimeOnPage),
            entrances = sum(entrances)) %>% 
  ungroup() ->
  df_user_ga

df_loghero %>% 
  group_by(pagePath) %>% 
  summarise(hits = sum(as.numeric(hits)),
            avgMetric1 = mean(avgMetric1)) ->
  df_loghero

# merge 1
df_merged = merge(page_data, df_loghero, by = "pagePath")
df_merged = merge(df_merged, df_user_ga, by = "pagePath", all.x = TRUE)


# Basic imputation
df_merged$pageviews[is.na(df_merged$pageviews)] = mean(df_merged$pageviews,na.rm = TRUE)
df_merged$avgTimeOnPage[is.na(df_merged$avgTimeOnPage)] = mean(df_merged$avgTimeOnPage,na.rm = TRUE)
df_merged$entrances[is.na(df_merged$entrances)] = mean(df_merged$entrances,na.rm = TRUE)


# Use only R standard variable names
names(df_merged) = make.names(names(df_merged))

We will now use to basic models which we will train on a subset and test on test set. First, we will use a Random Forest to estimate a basic regression, ignoring the ordinal factor scale of counting the visits discreetly, but enjoying the non-linearity. In the second model, we will use a generalize linear model with poisson link and thusly incorporate the fact we are predicting a count variable but losing non-linearity in the proess.

# Modelling

# Train Test Split
train_idx = sample(1:nrow(df_merged),size = round(2*nrow(df_merged)/3),replace = FALSE)
train = df_merged[train_idx,]
test = df_merged[-train_idx,]

# Ranger basic Model
mod_rf = ranger(formula = hits ~ Word.Count
                              + Unique.Inlinks
                              + Unique.Outlinks
                              + Inlinks
                              + Outlinks
                              + Text.Ratio
                              + Response.Time
                              + Size
                              + pageviews
                              + avgTimeOnPage
                              + entrances
                 , data = train, num.trees = 500,importance = "impurity")

mod_glm = glm(formula = hits ~ Word.Count
                           + Unique.Inlinks
                           + Unique.Outlinks
                           + Inlinks
                           + Outlinks
                           + Text.Ratio
                           + Response.Time
                           + Size
                           + pageviews
                           + avgTimeOnPage
                           + entrances,
                           data = train,
              family = poisson)

# Predict values
test$pred_rf = round(predict(mod_rf, data = test)$predictions)
test$pred_glm = round(predict(mod_glm, newdata = test))

We have trained the models and now predicted the number of crawler visits for pages the model hast never seen. Let’s compare the predicted values with the actual ones:

test %>% 
  dplyr::select(pagePath,hits,pred_rf,pred_glm) %>% 
  arrange(desc(hits))

test %>% 
  dplyr::select(pagePath,hits,pred_rf,pred_glm) %>% 
  arrange(desc(hits)) %>% 
  mutate(rank = rank(1/hits, ties.method = "min"), 
         rank_rf = rank(1/pred_rf, ties.method = "min"), 
         rank_glm = rank(1/pred_glm, ties.method = "min")) %>% 
  dplyr::select(pagePath, rank, rank_rf, rank_glm)

As we can see, the random forest is far more capable of dealing with the large differences in scale in between the pages than the glm. But if we only consider the rank of the pages, both models acutally work pretty well. From an user perspective, the concret number of predicted visits is not as important as the fact which pages are visited more often. The GLM of course offers to take a look at the beta values:

summary(mod_glm)$coefficients

## Estimate Std. Error z value Pr(>|z|)
## (Intercept) 11.7771264452 1.884972e+00 6.2479049 4.159949e-10
## Word.Count 0.0063256324 7.856065e-04 8.0519097 8.151201e-16
## Unique.Inlinks 0.0967094083 3.276334e-02 2.9517569 3.159715e-03
## Unique.Outlinks 0.2747712141 1.120261e-01 2.4527436 1.417714e-02
## Inlinks 0.0062746899 6.595578e-03 0.9513481 3.414277e-01
## Outlinks -0.0886505758 4.462267e-02 -1.9866712 4.695885e-02
## Text.Ratio -0.4113764679 7.342136e-02 -5.6029539 2.107293e-08
## Response.Time 2.2484413075 1.285903e+00 1.7485306 8.037219e-02
## Size -0.0002508579 6.675199e-05 -3.7580583 1.712370e-04
## pageviews -0.0004068211 1.399437e-04 -2.9070336 3.648740e-03
## avgTimeOnPage 0.0059767188 1.986728e-03 3.0083225 2.626942e-03
## entrances 0.0004635700 1.624705e-04 2.8532566 4.327367e-03

With these information you can further tune and adjust your homepage and find out what the crawler is actually doing.