Comment vérifier automatiquement la présence d'un mot-clé dans les zones chaudes d'une page avec Google Sheets ?

Aujourd'hui, j'ai envie de partager avec toi une méthode de travail Google Sheets que j'utilise très souvent.

L'idée m'est venue à la lecture, il y a quelques mois déjà, de cet excellent article de Lee Foot : https://www.searchenginejournal.com/python-seo-striking-distance/423009/.

Dans cet article, l'auteur explique comment vérifier, avec Python, si un mot-clé est présent ou non dans le h1, le title, ou le contenu d'une page.

L'objectif étant de faire tourner le script sur une liste de mots-clés et ainsi d'obtenir une base de travail pour un chantier d'optimisations on-page.

Même si j'ai quelques bases en Python, j'avais envie de pouvoir répliquer la logique de ce script directement dans Google Sheets.

Tu vas voir qu'au final, c'est assez simple.

Collecter les données de positionnement

La 1ère étape, c'est d'importer dans ta feuille Google Sheets les données de positionnement du site sur lequel tu souhaites mener l'analyse.

Pour ma part, je me sers de Ranxplorer pour collecter les données mais tu peux bien entendu utiliser Semrush, Yooda Insights, Ahrefs ou tout autre outil, du moment que tu as :

• le mot-clé
• l'URL
• la position

Pour la démonstration, je vais prendre en exemple le site abondance.com d'Olivier Andrieu.

J'importe les 100 premières lignes du rapport "Mots-clés" de Ranxplorer pour le site Abondance et je nettoie le fichier pour ne garder que les colonnes suivantes : "Pos.", "Mot-clé", "URL".

Les URL dans l'export Ranxplorer ne contenant pas le protocole HTTP, j'ajoute celui-ci avec un simple rechercher/remplacer. Ce sera utile pour la suite.

Filtrer les mots-clés intéressants

La 2ème étape consiste à filtrer la liste de mots-clés pour ne garder que les "vraies" opportunités de positionnement.

Je vais donc filtrer sur les positions >= 5 et <= 20 et sur les mots-clés ne contenant pas la marque "abondance".

Vérifier la présence du mot-clé dans le h1, le title ou le contenu de la page

Passons maintenant au plus intéressant : vérifier si le mot-clé est présent dans les zones chaudes du SEO, à savoir le h1, le title ou le contenu principal (ce que Lee Foot appelle "copy").

Pour cela, je vous donne ici 2 astuces.

La 1ère astuce concerne la fonction IMPORTXML. L'utilisation de celle-ci étant limitée par Google (je ne connais pas le quota exact mais au-delà de 50-100 requêtes cela devient compliqué), l'idée va être d'économiser au maximum le nombre de requêtes en groupant les résultats.

Je vais donc utiliser la fonction XPath concat pour mettre bout à bout le résultat de chacune des extractions, en les séparant par une virgule.

Concrètement, au lieu de faire une requête pour le h1, une requête pour le title et une requête pour le contenu, donc 3 requêtes au total, je ne vais en faire qu'une.

La 2ème astuce concerne le check en lui-même : je vais en effet utiliser la fonction boolean qui permet de retourner true ou false selon que l'expression XPath demandée renvoie quelque chose ou non.

Ici, je vais tester la présence du mot-clé :

1. dans le h1 avec boolean(//h1[contains(text(),'mot-clé')])
2. dans le title avec boolean(//title[contains(text(),'mot-clé')])
3. dans le contenu principal avec boolean(//*[@class="post-wrap"]//text()[contains(.,'mot-clé')])

Pour trouver le XPath de la zone de contenu, je te conseille d'utiliser la console Google Chrome.

Au final, en considérant que le mot-clé est en B2 et l'URL en C2, j'aurai en D2 :

=IMPORTXML(C2;"concat(boolean(//h1[contains(text(),'"&B2&"')]),',',boolean(//title[contains(text(),'"&B2&"')]),',',boolean(//*[@class='post-wrap']//text()[contains(.,'"&B2&"')]))")

Découper le résultat final dans des colonnes distinctes

Pour terminer, il ne reste plus qu'à splitter le résultat de la fonction IMPORTXML afin d'avoir dans chaque colonne (h1, title, contenu) un flag true ou false.

J'utilise donc la fonction SPLIT en précisant que le séparateur est la virgule, ce qui donne :

=SPLIT(IMPORTXML(C2;"concat(boolean(//h1[contains(text(),'"&B2&"')]),',',boolean(//title[contains(text(),'"&B2&"')]),',',boolean(//*[@class='post-wrap']//text()[contains(.,'"&B2&"')]))");",")

Et voilà !