Comment analyser vos log SEO avec R et Tableau

Comment analyser vos log SEO avec R et Tableau

En 2015 j’ai eu la chance de croiser Vincent Terrasi qui était à l’époque un grand passionné de Data Science mais aussi de SEO. Quelques mois plus tard, alors que j’animé une conférence sur les domaines expirés à QueDuWeb 2016 et lui une grosse conférence sur le R, nous avions partagé notre chambre d’hôtel et ce durant les 3 jours de l’évent.

Ce qui se passa durant ces 3 jours avec Vincent fut la découverte pour moi de la Data Science, de sa facilité à être mise en place mais surtout de pouvoir accomplir des taches disons le franchement très chiantes quand on traite un gros paquet de data. Bref 2016 fut pour moi une révélation.

Introduction

Vous trouverez sur mon blog 2 articles assez basique et simple pour le néophyte sur les bases du R et comment l’installer R studio. L’objectif de ce post est de vous montrer comment traiter vos log serveur apache, y détecter les différent type de bot google, puis de les classifier. Si vous suivez tout bien vous aurez un dashboard aussi bien en local sur votre machine qu’en SAAS et le tout interactif :

  • Un heatmap zoomable en heure et jour.
  • Une timeline pour voir la fréquence de passage des bots.
  • Les différents types de bot google et voir ce qu’il atteint en page ( status 200, 301 etc)
  • Les pages par requêtes avec leurs status
  • Les différents types de contenus et leurs status (js, css, xml…)
  • Un heatmap des repertoires les plus crawlé
  • La taille des pages crawlé

Si vous suivez bien mon article vous arriverez à sortir ce résultat

dashboard-seo-1
dashboard-seo-1

Comme vous le voyez je suis aussi sur un cas concret d’un de mes clients donc avec de la data concrète. Si par exemple je veux voir ce qu’il se passe globalement, on dirait que Google aime venir faire coucou le lundi à 10h. Si on veut zoomer sur les crawl de google mobile (le status 200 est en bleu), il suffit de cliquer et tout le dashboard se met à jour en relation à la sélection effectuée.

dashboard-seo-2
dashboard-seo-2

Préparation des logs apaches

Avant de commencer à traiter les logs apaches, je vais faire un rappel assez rapide de leurs structures, ça nous aidera pour la suite à bien comprendre ce qu’on cherche à en tirer, les formaliser sous un autre formats, bref avoir de la data propre.

log-apache
log-apache
ChampDescription
1l’adresse ip de la personne / machine réalisant la requête sur notre serveur
2le sympbole – est toujours présent à ce niveau
3Authentification du client distant. Si on a le symbole – c’est qu’aucunes identifications n’a été demandé
4deta et heure de la requete englobé entre [ ]
5la requete (ou page) effectuée englobé entre  » « 
6Le code réponse du serveur
7la taille de la page renvoyée par le serveur en bytes

Selon la configuration de votre serveur apache soit les logs google bot sont deja traitée dans un fichier spécifique et regroupé, soit dans un fichier mensuel, par semaine ou par jour. Selon votre cas, il vous faudra regrouper vos log dans un même fichier. Une fois vos datas de base prêtent, on peut passer à l’étape suivante

Tips: perso je travaille le plus souvent sur une data sur 90 jours. Après selon la taille du site et de ce que l’on cherche (une anomalie – que j’appellerais moi une tendance de saison si vous êtes dans un e-commerce, détection de pages orphelines etc etc. Bref à vous d’adapter ;).

Nettoyer et préparer les datas d’apache avec le langage R.

Dans un premier temps on charge nos datas dans une data frame

library(stringr)
library(tidyr)

#def local systdate
Sys.setlocale("LC_TIME", "uk")

raw <- read.table("C:/qcd/outGGbot.txt")

df <- raw

Tips: J’ai ici précisé mon système local en mode uk car le format de date arrive au format anglais ( par exemple Apr pour april … si je reste sur un format Français et que je souhaite traité ma data par la suite, ça va planter 😉 )

#the common combined log columns -> add/remove/switch columns depending on the format of your log
colnames(df)=c("host","ident","authuser","date","time_zone","request","status","bytes","referrer","user_agent")
df <- df[str_detect(tolower(df$user_agent), 'googlebot'),]

On renomme les colonnes puis on met en minuscule les user_agent contenant le mot clef googlebot. Rien de sorcier pour le moment (je vous montrerais une autre méthode de renommage plus basique plus bas au niveau des colonnes).

#extract the date&time in a common format
df$date=as.POSIXct(strptime(df$date, format = "[%d/%b/%Y:%H:%M:%S"), format="%Y-%m-%d %H:%M:%S")

On converti la date heure au format que l’on souhaite. Ici comme je vais utiliser Tableau, tool anglais, je me met au format anglais yyyy-mm-dd hh:mm:ss en exemple 2018-01-03 21:30:10

df$time_zone <- as.factor(sub("\\]", "", df$time_zone))
df$bytes <- as.factor(sub("-", "0", df$bytes))
df <- separate(df,request,c("request_type","request_url","http_header")," ")

#drop POST calls -> keep them if you think they might be useful for the analysis
df <- df[!str_detect(tolower(df$request_type), 'post'),]

On met au propre la time zone, puis si on se retrouve avec une page retournant un symbole – au niveau du champs 7 (sic log apache plus haut) on met 0 par défaut pour la taille en byte de la page. Les 2 autres lignes sont des séparations au niveaux des requêtes. La dernière est en option si besoin.

#extract the googlebot type
df$googlebot <- as.character(df$user_agent)
df$googlebot[str_detect(tolower(df$googlebot), 'googlebot-image')] <- "G Image"
df$googlebot[str_detect(tolower(df$googlebot), 'mobile')] <- "G Mobile"
df$googlebot[str_detect(tolower(df$googlebot), 'video')] <- "G Video"
df$googlebot[str_detect(tolower(df$googlebot), 'googlebot')] <- "Googlebot"

#extract resource type
df$content_type <- as.character(df$request_url)
df$content_type[str_detect(tolower(df$content_type), '\\.js')] <- "JS"
df$content_type[str_detect(tolower(df$content_type), '\\.css')] <- "CSS"
df$content_type[str_detect(tolower(df$content_type), '\\.jpg$|\\.png$|\\.jpeg$|\\.gif$|\\.svg$|\\.ico|\\.webp

 

Ici on attaque la grosse partie, le premier bloc vous le comprenez c est la qualification de notre brave bot google (image, mobile, desktop ...). Le deuxième bloc,c 'est l'extraction du type de page. J'ai mis les plus courante, si vous avez besoin d'autre suffit de faire copier ;). Le troisième bloc quand à lui se charge de la partie folder et en particulier du root folder ;). (la encore on pourrait aller plus loin mais pour mon usage j'en vois pas trop l’intérêt).
##Folders with less than 0.01% of crawls -> group them into Other
library(plyr)
counter <- count(df$folder)
minimum_crawls <- max(0.01/100*length(df$date),10)
df$folder[df$folder %in% counter$x[counter$freq < minimum_crawls]] <- "Other"

Ici on va regrouper toutes les pages qui sont le moins crawl dans un groupe que l’on va nommer « Other » (crawl < à 0,01%).

#extract the robots file crawls
df$robots_txt <- as.character(df$date)
df$robots_txt[!str_detect(tolower(df$request_url), 'robots.txt

 

Le premier bloc est un effet de style quand on deuxième bloc c'est une autre façon de renommer les colonnes. Pour le premier bloc je vous laisse creuser un peu votre curiosité ;).
#write the processed data to a CSV file
write.csv(subset(df, select=c(Date,TimeZone,RequestUrl,Status,Bytes,Referrer,Googlebot,ContentType,Folder,RobotsTxt)),file = "C:/qcd/processed_log.csv")

On sauvegarde nos données dans un fichier csv (la particularité ici c’est qu’on sélectionne les champs que l’on souhaite garder, à modifier selon votre convenance).

Mise en place du dashboard seo avec Tableau

Avant de commencer il va vous falloir installer la version gratuite de Tableau. Ici j’ai fait le choix de prendre Tableau pour vous présenter un outil de BI que je trouve sympas, mais on peut très bien faire la même chose avec Qlik Sense voir avec Google Data Studio (d’ailleurs je reviendrais sur les deux outils dans d’autres billets 😉 ).

Je vais pas trop rentrer dans les détails mais juste vous poster les Screenshots de configuration du dashboard, ça vous permettra de faire la même chose chez vous 😉

Charger la base de données

C’est simplement utiliser notre fichier csv comme data source ! On clic en bas à droite sur source de donnée (1) puis on selectione la source de données que l’on souhaite (2). Ou alors si déjà présente dans vos sources de données on glisse dépose sur la partie (3). Les deux méthodes indiqueront en (3) la source de données que vous avez choisi. En vert, vous avez l’aperçu de vos datas.

tableau-log-seo-1
tableau-log-seo-1

Maintenant on peut créer notre première feuille de calcul. A ce titre ce se trouve en bas à droite

tableau-log-seo-2
tableau-log-seo-2

La première icone est celle qui nous intéresse : la création d’une nouvelle feuille de calcul. La deuxième nous servira plus tard pour la création de notre dashboard seo.

Feuille de calcul heatmap

Quand vous avez créer votre feuille de calcul, vous vous retrouver avec une magnifique page blanche. On ne panique pas, je vais vous guider pas à pas.

tableau-log-seo-3
tableau-log-seo-3

Au niveau du champs colonne nous allons mettre les jours de la semaine. Pour cela, on va utiliser la fonction Datepart : DATEPART(‘weekday’, [Date]) (faire 2 clic sur le champs colonne et on copie colle 😉 ).

tableau-log-seo-4
tableau-log-seo-4

Et quand on clic n’importe où dans l’écran, notre formule se transforme en bleu et prend le nom « jour de la semaine ». Tableau nous montre les « données ».

tableau-log-seo-5
tableau-log-seo-5

Au niveau de la lignes, on va utiliser la même formule sauf que nous allons prendre les heures !! La formule à copier coller DATEPART(‘hour’, [Date])  et le Screenshot de ce que vous devriez avoir.

tableau-log-seo-6
tableau-log-seo-6

Pour l’instant vous voyez l’outil est vraiment très très simple à prendre en main, mais on veut un heatmap. Ça manque de couleur non ? Et les ABC, c’est mignon, mais ça ne veut rien dire pour le moment … Attention la suite des événements va vous piquer les yeux ;). On va jouer avec le bloc repères en orange dans le screenshoot qui suit.

tableau-log-seo-7
tableau-log-seo-7

La partie rose est double clicable et c’est la que l’on va y mettre une nouvelle fonction count sur le champs date. Le code a copier coller : COUNT([Date])

tableau-log-seo-8
tableau-log-seo-8

Si vous cliquer n’importe où sur l’écran, ben on aura toujours pas de couleur. Pour avoir la colorisation, on va glisser déposer notre fonction sur la boite couleur.

tableau-log-seo-9
tableau-log-seo-9

Et une fois qu’on relâche, on obtient notre heatmap coloré comme on le souhaite !

tableau-log-seo-10
tableau-log-seo-10

Bon je reviens en arrière, on aurait pu aussi faire comme cela pour avoir la couleur

Simple non ? Si on veut modifier la couleur bleu, on clic sur le bouton Couleur > modifier les couleurs. On clique sur le petit carrée (sic screenshoot) et on choisit la couleur que l’on souhaite. Essayer de garder une couleur sobre. Les couleurs farfelues risque de rendre votre rendu peut lisible ou compréhensible. (je vous ai fait un screen d’une couleur à ne pas utiliser dans mon cas 😉 ).

tableau-log-seo-12
tableau-log-seo-12
tableau-log-seo-13
tableau-log-seo-13

Voila pour la partie heatmap, je vous laisse le soin de trouver comment renommer l’onglet et le titre de la feuille, vous devriez vous en sortir. Pour la suite je vous met que les screenshoots de ce que vous devriez avoir.

tableau-log-seo-14
tableau-log-seo-14
tableau-log-seo-15
tableau-log-seo-15
tableau-log-seo-16
tableau-log-seo-16
tableau-log-seo-17
tableau-log-seo-17
tableau-log-seo-18
tableau-log-seo-18
tableau-log-seo-19
tableau-log-seo-19

et le dernier pour finir la série 😉

tableau-log-seo-20
tableau-log-seo-20

Voilà fini. Creusez bien, c’est pas bien compliqué de reproduire les différents écrans. N’oubliez pas que n’importe quel outil de BI simple, bête et discipliné fera aussi bien le travail (Google Data Studio, Qlick Sense et j’en passe). Il nous reste à réaliser le dashboard !

Mise en place du dashboard d’analyse de log SEO

Cette fois ci au lieux de cliquer sur nouvelle feuille de calcul, on va cliquer sur nouveau tableau de bord. On se retrouve avec une superbe page vide et toute nos feuilles de calculs que nous avons réalisé avant se trouve sur la partie gauche. Le jeu ici sera de les glisser déposer tout simplement !

tableau-log-seo-21
tableau-log-seo-21
tableau-log-seo-22
tableau-log-seo-22

En guise d’exemple ce petit screen. Pour le tableau de bord final je vous laisse revoir le premier écran au tout début de mon billet. Vous remarquerez en faisant vos glisser déposer que Tableau vous indique que les éléments seront afficher selon la tailler de l’écran. Notre version gratuite faisant des publication SAAS, l’affichage sera donc responsive, sympas non ? Si vous avez la version desktop, vous pourrez visualiser vos data avec Tableau Reader ;).

Quelques cas d’analyses de log avec le dashboard

Avoir un dashboad c’est bien mais qu’est ce qu’on peut en faire ?

On pourrait par exemple voir le crawl time d’un groupe de pages sélectionnées

tableau-log-seo-23
tableau-log-seo-23

On pourrait aussi jouer sur le type de bot, la date, le status des pages. L’idée serait de voir par exemple si on a eu une rupture au niveau du serveur, de vérifier à quel fréquence les bots viennent visiter le site, d’apporter des modifications a votre robots.txt pour l’interdire de passer sur certain path de votre site.

On peut aussi utiliser ce dashboard pour voir le volume de page en 301 / 404 etc assez rapidement et y apporter assez vite une solution. Au niveau des 404 au passage, on pourra vite détecter les pages qui n’existent pas sur votre site, sic potentiellement une attaque ? On pourra donc là aussi mettre en place des moyens de contre mesure 😉

Si dans votre rapport vous vous trouver avec un path avec /- c’est que vous avez un soucis de redirection (le script plus haut fait le taff aussi dans ce sens). La encore on pourra assez vite détecter les pages en souffrance et y apporter une solution assez rapidement.

Je pourrais continuer longtemps sur l’utilité de cette analyse de log, cela dépend en général ce qu’on cherche à faire et quel résultat on veut obtenir !

Pour aller plus loin

Update 2021 – Cette année d’une nouvelle formation que je recommande vivement pour ceux qui veulent se lancer la data science:

Et si vous avez envie de bosser avec moi sur vos analyses de log il suffit de me contacter sur mon adresse email pro: s-moity@lsproject.fr 😉


6 réponses à “Comment analyser vos log SEO avec R et Tableau”

  1. Article très intéressant sur l’analyse de log en DIY. Cette partie est très souvent négligée dans l’analyse d’un site. Du coup je crois que je vais me mettre à étudier le R. Merci pour toutes ces infos. 🙂

Répondre à Robin Annuler la réponse

Votre adresse e-mail ne sera pas publiée.