En SEO, on parle souvent des fondamentaux. La fonction Excel vlookup (dont l’équivalent en français est recherchev) qui signifie « recherche verticale », doit en faire partie, du moins quand il est question d’outils. Pourtant cette fonction est mal apprivoisée dans notre métier, alors que son potentiel est juste énorme.
Cet article est une mise en bouche de ma conférence au SEO camp de Genève le 22 mai 2015. A cette occasion, je ferai une présentation sur les méthodes pour discerner les pages actives et inactives, point de départ pour un plan d’action anti-panda. Cet article ne sera pas superficiel pour autant. Après avoir abordé les bases, la tournure deviendra plus « tutorial ». Les outils utilisés sont Screaming Frog, et surtout Excel, néanmoins des outils alternatifs et similaires peuvent faire l’affaire.Ok, alors on peut faire quoi avec vlookup dans une logique SEO ?
Je vous la fait courte, grâce à vlookup vous pourrez mesurer pour votre site :
- quelles sont les sections les plus performantes,
- quelles sont les pages inactives (ne recevant pas de visite),
- l’impact de la duplication de contenu sur le référencement naturel,
- l’éventuelle corrélation entre les votes sociaux, et le trafic organique généré par vos pages,
- quelles sont les URLs orphelines en comparant les URLs de GA, crawl, et sitemap.xml,
- quelles pages sont boudées par le Googlebot,
- dans quelle mesure la densité du contenu peut influencer vos visiteurs, et les moteurs,
- … la liste est infinie !
Mais encore ?
Vlookup (recherchev) est donc une fonction intégrée aux tableurs (Excel). Ca peut aussi marcher avec d’autres comme open office, Google spreadsheet etc, mais attention, le nom de la fonction peut changer. Néanmoins le principe reste le même. L’idée étant de regrouper au sein d’un même tableau, des données issues de plusieurs tableaux différents, mais ayant des données en commun (bien souvent les URLs).
C’est encore un peu flou ? Vous comprendrez mieux par la suite…
Celui qui ne fait pas de recoupement, fait du SEO en aveugle
Récolter des données c’est bien, c’est même la base. Mais en discutant avec certains confrères, même les plus grands, je suis surpris de voir que le croisement des données ne fait pas partie de leur méthodologie. Hargh ! Résultat, le travail est fait sans « vista » : on analyse les éléments « on page », le netlinking, la latence, le contenu… le tout de manière cloisonnée, sans vision d’ensemble, et sans véritablement mesurer l’incidence que chaque élément peut avoir sur les autres et le trafic organique en particulier.
Au début, il y a le crawl
Certains outils que je qualifierai de « multidimensionnels » permettent nativement de faire du recoupement en agrégeant plusieurs données issues d’outils différents. Je pense par exemple à Deepcrawl ou Botify. Leur point commun ? Ce sont tous les deux des crawlers (en concurrence frontale), qui offrent la possibilité de croiser les data récoltés avec Google analytics.
Tutorial pas à pas
Ce tuto s’attardera surtout sur le recoupement des URLs récoltées via le crawler, et Google analytics. Quand vous aurez compris le truc, seule votre imagination pourra vous limiter ;-) Des connaissances vraiment basiques d’Excel (ou autre tableur) sont requises (je ne vais pas vous expliquer comment supprimer une colonne ou une ligne).
La première étape consiste donc à crawler le site pour en récupérer toutes les URLs. L’étape est fondamentale car seul un crawler peut en dresser la liste exhaustive. On lance donc un crawl sur un site, puis on exporte (.csv/.xlsx) l’ensemble des URLs correspondant aux pages HTML.
L’export des URLs (HTML) dans Screaming Frog SEO Spider
On ouvre le fichier exporté avec Excel, où l’on retrouve grosso modo le même tableau :
On supprime ensuite les 2 premières lignes, puis on applique une « mise sous forme de tableau ». L’essentiel étant de pouvoir filtrer/trier les colonnes.
Voilà. On a maintenant un fichier Excel prêt pour bosser et bien présenté.
Récupération des données de Google analytics
On va maintenant récupérer les visites générées par chaque url dans Google analytics. Mais attention pas n’importe quelles visites ! Ce qui nous intéresse, ce sont les visites issues de la recherche organique (résultats naturels des moteurs), et plus précisément les entrées. J’insiste car c’est très important : pour mesurer la performance SEO d’une page, il faut regarder prioritairement le trafic qu’elle draine depuis les pages de résultats des moteurs (SERPs). Imaginons qu’un visiteur (organique) arrive sur une page A, puis poursuit sa navigation sur une page B : ce que l’on retiendra c’est son point d’entrée sur la page A, autrement dit, la page de destination.
Par ailleurs, les visites récoltées se feront sur un laps de temps de 30 jours précédant le crawl. On peut bien sûr faire plus, mais d’un point de vue SEO, cela n’aurait pas grand intérêt.
Allons-y ! Une fois dans Google analytics, placez-vous dans Comportement > contenu du site > Toutes les pages, puis vérifiez ensuite que vous êtes sur une période de 30 jours.
Il faut maintenant filtrer le trafic généré pour ne prendre en compte que celui généré par les résultats naturels. Par défaut, vous aurez certainement le segment « toutes les sessions » activé :
Cliquez dessus, puis décochez « toutes les sessions », et cochez ensuite « Trafic généré par les résultats naturels », puis validez.
Avant de faire un export, il faut afficher le plus grand nombre de résultats car GA ne permet d’exporter que la page en cours. En bas du tableau, « Afficher les lignes », on choisit 5000. Qui peut le plus, peut le moins. Dans la pluspart des cas, le nombre d’urls ne devrait pas dépasser 5000. Si c’est le cas, alors il faudra réaliser l’opération en 2 temps, voir plus.
Maintenant que tout est ok, et qu’il n’y a plus de pagination, on peut exporter les données, grâce au menu « exporter » tout en haut de la page.
Ouvrez ensuite le fichier avec Excel. Seul le feuillet « Ensemble de données1 » nous intéresse. Mais si on regarde la colonne « Page » (A), on remarque que les URLs sont amputées du nom de domaine. Il faut fixer ça.
Reconstitution des URLs
La technique est simple et rapide, à condition de la connaître.
On insert 2 nouvelles colonnes, de préférence avant « Page ». Dans la nouvelle colonne A, on ajoute en A2 « http://www.nom-domaine.fr » à remplacer bien sûr par le nom de votre domaine. Il est important de calquer les URLs sur celles du crawl. S’il y a « www » d’un côté, ils doivent être présents de l’autre. Ne surtout pas mettre de slash à la fin ! On « étend » ensuite la valeur à l’ensemble de la colonne, jusqu’à la fin du tableau :
En colonne B, on va maintenant ajouter une petite formule pour grouper les valeurs des colonnes A et C. Placez-vous sur B2, puis ajoutez la formule suivante : =A2&C2. Validez, puis étendez la formule à l’ensemble de la colonne.
Sélectionnez la colonne B (celle contenant les URLs complètes), faites un « copier », puis un coller en collant uniquement les valeurs (au lieu des formules).
Si vous avez réussi à coller uniquement les valeurs (urls complètes) dans la colonne B, vous pouvez maintenant supprimer les colonnes A et C. Nommez ensuite la nouvelle colonne A avec « URLs » (ou ce que vous voulez), puis enregistrez votre fichier avec un nom plutôt court (par exemple GA.xlsx).
Et c’est là que vlookup (recherchev) entre en scène…
A ce stade, on a 2 fichiers Excel ouverts : celui du crawl, et celui de Google analytics.
Dans le fichier du crawl, on va maintenant récupérer le nombre d’entrées organiques (si il y en a) pour chaque URLs.
Insérez une nouvelle colonne, par exemple entre « Address » et « Content », puis nommez-la « GA Entrées ». Placez-vous sur la première cellule (B2). Attention, soyez attentif, c’est maintenant que tout se joue !
En B2, commencez par écrire la formule suivante : =recherchev( sans oublier la parenthèse ouverte à la fin, et ne validez rien à ce stade. Cliquez ensuite sur « Fx » pour afficher la boîte d’arguments de la fonction :
Dans « valeur_cherchée », on indique qu’on cherche les URLs, car ce sont elles qui « matchent » dans nos deux fichiers. Tout en gardant la boîte de dialogue ouverte, et en cliquant sur la première URL (A2), on doit obtenir ceci :
Positionnez-vous ensuite sur « Table_matrice » (c’est important). Puis, affichez (Alt+tab ou barre de taches) votre fichier contenant les données Google analytics (GA.xlsx). Sélectionnez la plage (cliqué glissé) des colonnes allant de A à E et ne relâchez pas de suite le bouton de votre souris ! Remarquez le numéro de colonne qui s’affiche dans l’infobulle au fur et à mesure que vous sélectionnez des colonnes : il faut mémoriser le numéro de la colonne dont on souhaite récupérer les données. En l’occurrence, ici, c’est la colonne « Entrées » qui nous intéresse, et qui correspond à la colonne 5 si notre plage démarre de la 1ère colonne. Vu ?
Cliquez maintenant sur « No_index_col », ce qui devrait réafficher automatiquement le tableau du crawl en arrière-plan. Indiquez ensuite le numéro de la colonne qui nous intéresse : 5.
Enfin pour valeur_proche, indiquez toujours « faux ».
Cliquez sur Ok.
Et voilà ! Vous venez d’associer les données d’un tableau avec un autre ! En un coup d’oeil, vous voyez maintenant quelles sont les pages qui génèrent du trafic organique, et surtout celles qui n’en drainent pas (pages actives et inactives)… Et ça, en SEO c’est fon-da-men-tal !
Ce n’est pas terminé : le fichier du crawl étant maintenant dépendant du fichier GA.xlsx pour afficher le nombre d’entrées, il est préférable de systématiquement copier/coller les valeurs sur elles-mêmes comme abordé plus haut.
2: Vous pouvez remplacer les valeurs #N/A par rien histoire d’avoir un tableau plus propre (facultatif).
3: Pour plus de précision, et afin de tirer des statistiques fiables (j’y viens), filtrez la colonne D « Status Code » pour n’afficher que les codes 200.
Et maintenant ?
Et maintenant, les possibilités sont infinies. Au sein d’un même tableau, vous allez pouvoir regrouper les données de sources diverses et variées pour une analyse à 360° de votre site. En plus des stats Google analytics, vous pourrez associer de la même manière le nombre de hits enregistré par le GoogleBot lors d’une période donnée, j’explique dans un article comment récupérer (et visualiser avec Gephi) les access logs d’Apache. De même, après avoir scanné votre site avec un service comme siteliner.com vous aurez la réponse à des questions extrêmement pointues mais néanmoins très intéressantes :
- Quelle peut être l’incidence de la duplication du contenu interne sur la fréquence du crawl ? Et sur les visites ?
- Dans quelle proportion la profondeur des URLs influe-t-elle l’appétence du GoogleBot et la visibilité dans les SERPs ?
- La taille des articles ayant moins de 30% de contenu dupliqué a-t-elle une influence sur le volume de visites ? …
Et imaginez si vous récoltez les votes sociaux pour chaque URL grâce par exemple à SeoTools for Excel, ainsi que le nombre de liens entrants repérés dans Majestic ou Ahrefs… Ca y est votre imagination carbure à plein régime ? Tant mieux :)
Bref, vous voyez bien ce que cela peut vous apporter, et si vous ne voyez toujours pas, votre dernier recours sera d’apprendre le métier de SEO à un labrador.
Lors du SEOcamp à Genève le 22 mai prochain, j’aborderai également la manière dont on peut exploiter toutes ces données, comment tirer des statistiques déterminantes dans un processus préventif ou curatif contre le filtre Google panda.
PS : Il y a deux ans de ça, Excel était pour moi un logiciel pour secrétaires en talon aiguille…
Merci pour l’article.
Petite astuce pour la route :
Si vous voulez affichez plus de 5000 urls dans le rapport GA, allez à la fin de l’url et repérez « Count%3D5000/ ».
Puis changez 5000 par le nombre d’urls que vous souhaitez.
@Pink Hat Seo : Non hélas, ça ne marche plus :'( cf : https://twitter.com/RaphSEO/status/573407401325559808
@Aurélien Berrut : Exact. Uniquement accessible sur GWT.
Je me réjouis de voir ta prez du 22 mai maintenant ;-)
@Patrick : Je me réjouis de ton commentaire ;) Je vais éditer mon texte dans ce sens…
Merci pour l’article.
Marrant, j’ai fait la même chose lundi dernier pour un audit de site.
J’ai en plus ajouté les liens par url depuis les Webmaster Tools et le nombre de crawls par url depuis une watussi box.
C’est vrai que c’est intéressant à analyser et on voit rapidement que toutes les généralités que l’on peut entendre ne s’appliquent pas forcément.
Je pense que l’étape suivante est d’avoir une vraie méthodo statistique pour trouver des relations entre certaines variables analysées. On a vite fait de tirer de fausses conclusions sinon je pense. En revanche, ça demande un minimum de volumes et un savoir-faire. Je me rappelle avoir utilisé XLSTAT il y a bien longtemps, je ne sais pas s’il y a une alternative gratuite.
@Christophe : l’étape suivante, c’est effectivement les stats, mais je n’ai pas voulu aborder en détail cette partie ici. J’en parlerai lors de ma conf au SEOcamp de Genève, et pourquoi pas dans un prochain billet. Je n’ai jamais utilisé XLSTAT, mais avec les outils par défaut on peut déjà faire pas mal de choses je trouve.
Hello Aurélien, sympa comme approche par contre tout est fait en manuel ?!
Perso j’ai mes outils qui automatise tout cela, il faut que je fasse une démo à l’occasion.
Je n’aime pas faire manuellement ce qui peux être automatisé dans un outil. :-)
Pierre
Le taux de page active est un indicateur très important que j’utilise souvent.
Juste une question pourquoi prendre Comportement > contenu du site > Toutes les pages et pas comportement > contenu du site > page de destination ? car les pages de destination sont les pages d’entrée du site ?
@Pierre : j’adore également automatiser ce qui peut l’être. En revanche je refuse d’automatiser une tâche qui peut être améliorée et qui plus est à géométrie très variable d’un projet à l’autre. Cela dit, en macros VBA, je suis un vrai noob, et suis très preneur d’une démo :)
@othmane : A ce jour, je ne peux que te faire une réponse insatisfaisante : parce que j’ai toujours fais comme ça, parce que j’ai vu faire comme ça, et parce qu’en comparant avec les pages de destination, je trouve grosso modo le même résultat, mais légèrement moins exhaustif. Je compléterai ma réponse plus tard, car je suis également désireux de savoir la véritable différence entre les deux approches ;)
Edit 09/03/2015 : j’ai édité mon article : les 2 approches sont les même, mais « toutes les pages » donnent toutes les URLs connues par GA, même quand 0 visites.
C’est assez impressionnant, à chaque fois que tu fais un billet sur de l’audit je suis sur le c*l, je n’avais jamais pensé à faire ça, c’est comme tes billets avec Gephi, je vais devoir passer du temps pour voir toutes les possibilités, merci !
Tu me donne presque envie de venir te voir à Genève, à voir le planning.
@aurelien : merci ! N’hésite pas à venir à Genève, tu n’es pas très loin que je sache ;)
@Aurélien Berrut
Au début, vous parlez de faire attention et insister sur les pages qui draine du trafic depuis les Serps.
« Imaginons qu’un visiteur (organique) arrive sur une page A, puis poursuit sa navigation sur une page B : ce que l’on retiendra c’est son point d’entrée sur la page A, autrement dit, la page de destination »
C’est pourquoi il faut choisir -contenu du site- pages de destination et pas toutes les pages qui contiennent même les pages B.
Chez moi, et sur plusieurs exemple de site, les 2 approches ne sont pas du tout les même, exemple toutes pages contient 42 500 Urls avec 1 visites, et page destination 10 000 Urls. Il faut selon moi, prendre les stats de page de destination car toutes les pages représentent aussi les pages qui ont été visitée par la suite de la page d’entrée des Serps.
@othmane : non, je prends les « entrées », c’est explicite, il n’y a aucune notion de visite à 2 pages ou plus.
Super combo testé (avec Piwik et son export TSV + crawl dans NeoOffice) et j’avoue que ça ouvre de nouvelles perspectives d’analyse… Quoi qu’il en sera cette mise en bouche comme tu le dis donne vraiment envie de passer au plat de résistance Genevois… Si le cuisto est en forme proposera t’il un dessert maison ? ;-)
Bref tout ça pour dire : Bravo chef !
Merci pour cette article super intéressant. Sa annonce du très lourd pour Genève :)
Une très bonne approche de travail que de récupérer ces URLs (d’autres manières de les trier sont possibles aussi). Et c’est à ce moment là qu’il faut savoir quoi en savoir. :)
Hello,
C’est très sympa d’avoir pris le temps de faire ce tuto pas à pas :-)
Je vais faire un peu de pub, mais pour la bonne cause. Botify Analytics fait ce travail de matche entre les URL crawlées et réellement visitées avec l’API GA. Tu peux donc identifier les URL crawlables qui ne génèrent pas de trafic organique. Après tu as l’Analyzer de logs qui te permet de connaître les pages réellement visitées par Googlebot, ou celles qui ne le sont jamais, pour identifier les optimisations nécessaires.
Pour Excel, j’ai enfin compris son intérêt comme toi en étant confrontée à une masse de données à traiter et croiser (et merci aux personnes qui ont eu la patience de me former !), sortir des supers reportings. Et ça donne juste envie d’en apprendre encore plus. Mais ça devient juste ingérable sur les gros sites, le logiciel n’étant pas fait pour gérer des données trop volumineuses…
@Keeg : oui, le jus de cervelle est indispensable dans ce processus !
@dbotton : Botify peut faire le recoupement crawl, access logs, et analytics, mais ça s’arrête là, et cela a un coût. Mais cela n’enlève rien à ses qualités.
Pour Excel, en version 2010 64 bits et beaucoup de RAM, il faut vraiment énormément de données pour atteindre ses limites !