Reporting SEO avancé avec Google Sheets – Modèle et guide complet

Pour la Fabrique du Net, le SEO est un sujet majeur puisque c’est le canal qui drive l’essentiel de notre trafic ! Longtemps on a travaillé exclusivement « à la main », épluchant chaque semaine les données de Google Analytics et de Google Search Console pour analyser les fruits de notre travail. Mais lorsque un site web se porte bien et grossit, il arrive un moment où, face au volume de données, ce travail exige de nouveaux outils afin de pouvoir :

• Suivre de façon régulière et précise l’évolution des principaux indicateurs SEO
• Disposer de données réellement actionnables dans une logique d’optimisation

Je vous propose donc d’apprendre à construire un reporting SEO à la fois simple et très puissant ! Grâce à Google Sheets, aux API de Google Search Console et Google Analytics et un peu d’huile de coude vous allez vite comprendre que seule votre créativité peut réellement vous brider dans cet exercice.

 

Vous manquez de temps et cherchez un outil prêt à l’emploi ? Je mets à votre disposition un reporting SEO construit sous Google Sheets grâce à la méthode que je m’apprête à vous décrire. Je vous invite à en enregistrer une copie que vous pourrez bidouiller à souhait. Ce reporting SEO est munis d’un « Guide d’utilisation » qui vous expliquera comment le prendre en main et l’actualiser avec les données de votre propre site.

Tous nos modèles téléchargeables

Vous voulez comprendre comment j’ai construit ce reporting SEO et pourquoi j’ai choisi cette méthode ? Poursuivez votre lecture et vous serez bientôt capable de construire vous-même un reporting SEO sur-mesure !

5 bonnes raisons de construire votre reporting SEO avec Google Sheets

1. Google Search Console ne garde pas l’historique des données

Si vous n’êtes pas un « bleu » du SEO vous avez déjà dû le constater vous-mêmes. Google Search Console ne met à notre disposition que 90 jours de données historiques.

C’est particulièrement frustrant parce qu’en SEO les tendances s’analysent sur le long terme et les résultats d’un travail de fond en SEO ne se mesurent que plusieurs mois après implémentation.

Pour remédier à cette faiblesse de Google Search Console il faut donc pouvoir stocker son historique de données. On préférera stocker des metrics calculées, qui seront plus exploitables que des exports bruts de données, sinon bonjour à la capacité à actionner cette donnée !

2. Google Search Console n’offre que peu de souplesse pour l’analyse

Là encore c’est une frustration que chacun a pu éprouver en tentant d’approfondir des analyses SEO sur Google Search Console. Le rapport « Analyse de la recherche » est extrêmement limité quand il s’agit justement de mener des… analyses !

On a bien quelques filtres sympathiques sur les requêtes ou les pages. Mais est-ce que vous avez déjà réussi à dans GSC vos pages qui ont sont en première page de Google mais ont un CTR inférieur à 2% ? Non ? Eh bien moi non plus. Pourtant, pour faire de l’optimisation de CTR en retravaillant en priorité le title et la meta-description de vos pages qui ont le plus de potentiel ça serait quand même bien utile !

Du coup on est obligé de travailler à froid sur des exports de 90 jours de données, une fois de temps en temps. Pourtant quel progrès ça serait d’avoir un reporting SEO qui vous indique toutes les semaines quelles sont vos pages qui sous-performent niveau CTR par rapport à leur position moyenne dans les SERPs. Si vous pensez comme moi, continuez à lire, vous n’êtes plus très loin du graal 😉

3. Inutile de se ruiner pour un outil de reporting SEO performant

Bien sûr on trouve sur le marché des outils très puissants dédiés au SEO : Moz, Ahrefs, Botify, … Mais bien souvent ces logiciels sont de véritables couteaux suisses dont vous ne saurez pas exploiter 50% du potentiel par manque de temps ou de compétences (tout en payant 100% du prix !).

Si vous cherchez un outil de reporting et d’analyse qui fournisse une vision consolidée et actionnable de vos données SEO, c’est là-dessus que vous devez vous concentrer. Dans le genre, vous trouverez par exemple Seeurank de Yooda que je vous invite à tester pour la culture (15 jours gratuits). Mais à plus de 450€ / an minimum ça représente déjà un certain budget que nous ne sommes pas tous prêts à investir en SEO !

Grâce à la puissance combinée de Google Sheet, Google Analytics et Gogle Search Console, je vous propose d’arriver un résultat presque aussi performant à coût 0 (moyennant quand même un peu de bonne volonté).

4. Pour être actionnable un reporting SEO doit être construit sur-mesure

Evidemment on sera tous intéressés par le suivi de quelques metrics globales : le volume global d’impressions, le nombre d’URL qui génèrent du trafic SEO, etc. Mais en matière de SEO, chaque site a ses propres spécificités :

• Un site qui vient de mener une refonte de son responsive design voudra suivre de façon plus précise ses KPI sur mobile et tablette.
• Un site media qui possède des milliers de pages s’interrogera sur l’intérêt de couper dans le lard en supprimant des pages inutiles pour maintenir un niveau de qualité plus élevé aux yeux de Google.
• Un site positionné sur une niche pourra avoir une analyse plus approfondie des mots-clés de longue-traîne.

Bref, à chaque site ses problématiques SEO ! Pour un suivi efficace et actionnable il ne suffit donc pas de récupérer et faire tourner le premier dashboard SEO qui passe.
Il est primordial de commencer par se poser les bonnes questions et de construire un reporting SEO sur-mesure qui vous permette d’apporter les réponses à ces questions.

5. Google Sheets offre toutes les fonctionnalités dont vous pouvez rêver

Bon je ne vais pas tourner autour du pot, l’outil que je vous propose d’utiliser pour créer votre reporting SEO c’est Google Sheets. Pourquoi ? Tout simplement parce que c’est super puissant !

Grâce à quelques plugins bien sentis on va pouvoir requêter les API de tous les services Google et automatiser la récupération des données. On aura la même souplesse d’utilisation que sous Excel avec en bonus quelques fonctions bien senties comme QUERY() ou ImportXML() qui vont nous permettre de gagner un temps précieux en retraitement de données. On va ensuite ajouter une jolie couche de dataviz à la sauce Google pour obtenir un dashboard SEO tout ce qu’il ya de plus propre. En bonus, votre spreadsheet actualisé sera toujours instantanément partagé entre tous ses utilisateurs potentiels via le Cloud.

Vous commencez à sentir la puissance potentielle de votre futur reporting SEO ? On peut aller encore plus loin ! Grâce à Google Apps Script, avec quelques connaissances en programmation on peut même transformer notre reporting SEO en une véritable web app avec une interface super poussée se rapprochant du rendu d’un Seeurank.

C’est bon vous êtes convaincus ? Allez, maintenant on passe à la pratique !

Collecter les données pour votre reporting SEO

Pour un reporting SEO pratique à mettre à jour l’objectif c’est de pouvoir automatiser un minimum la collecte des données. Ça tombe bien, Google Sheets va nous faciliter la tâche.

Les données de Google Search Console

Pour récupérer les données de Google Search Console on va utiliser un plugin du navigateur Chrome qui est aussi simple qu’efficace, j’ai nommé Search Analytics for Sheets !

Ce plugin via nous permettre de collecter le nombre de clics, le nombre d’impressions, le CTR et la position moyenne de chaque page de notre site pour une période donnée. Il va également nous permettre d’obtenir les mêmes données pour tous les mots-clés identifiés sur lesquels notre site a été positionné pendant la période.

Il est également possible d’obtenir ces données agrégées par pays de provenance des visiteurs, par type de device utilisé ou bien simplement pour chaque jour de la période. Vous pouvez enfin croiser ces modes d’agrégation de données. On peut ainsi connaître tous les mots-clés liés à chaque page de votre site et toutes les metrics associées !

Une fois installé dans Chrome, ce plugin devient utilisable sur n’importe quel Google Sheet depuis le menu principal : Modules complémentaires > Search Analytics for Sheets > Open sidebar. Cela ouvre une sidebar sur la droite de votre spreadsheet.

A partir de l’onglet « Requests » (qui s’ouvre par défaut) de cette sidebar vous allez pouvoir effectuer des requêtes à l’API de Google Search Console sans aucune connaissance technique. Il vous suffit de :

• Sélectionner un site web associé à votre compte Google Search Console.
• Spécifier les dates de début et de fin de votre export de données.
• Choisir le (ou les) mode de regroupement des données : choisir « Page » pour obtenir les données par URL ou bien « Query » pour obtenir les données par mot-clé.
• Appliquer d’éventuels filtres, par exemple pour restreindre l’export aux données des visiteurs sur terminal mobile.
• Choisir la feuille de votre spreadsheet qui va recevoir les données requêtées.

Pour les autres options et fonctionnalités du plugin je vous laisse consulter vous-même la documentation mise à disposition par son concepteur en suivant le lien ci-dessus.

Grâce à ce plugin, nous allons extraire chaque semaine en quelques clics, l’ensemble des données utiles de Google Search Console. Dans mon modèle de reporting SEO, 4 feuilles distinctes servent à collecter ces données : Page (données par URL), Page&Query (détail des mots-clés pour chaque URL), Query (données par mots-clés) et Device (données par type de d’appareil).

Le petit conseil de La Fabrique du net
Notez qu’il est important de toujours importer les données dans les mêmes feuilles de calcul semaine après semaine (grâce à l’option « Results Sheet ») pour pouvoir construire une couche de retraitement par-dessus. En effet, il ne faut pas ajouter de formules directement dans les feuilles qui servent à l’extraction des données car leur contenu est écrasé à chaque nouvelle récupération de données. Nous verrons un peu plus loin comment procéder pour le retraitement de ces données.

Les données tirées de Google Analytics

Google Search Console ne permet pas collecter des données d’engagement des visites SEO. Il va donc nous falloir trouver un autre moyen de récupérer des données telles que : le taux de rebond, le temps moyen par landing page, le taux de conversion e-commerce ou bien par objectif, etc.

Nous allons cette fois faire appel à un autre plugin de Chrome que je vous invite à installer. Il s’agit d’un plugin officiel de Google proposé pour requêter l’API de Google Analytics : Google Analytics – Google Sheets add-on

Ce plugin permet d’obtenir à peu près n’importe quelle donnée que Google Analytics pourrait vous fournir. Seule difficulté, il utilise une syntaxe assez précise qui est celle de l’API Google Analytics. Si vous avez besoin de vous y plonger, vous trouverez ici et là l’essentiel des infos utiles.

Pour la création de votre reporting SEO vous devrez activer le plugin via le menu principal : Modules complémentaires > Google Analytics > Create new report. Cela va ouvrir une sidebar à droite de votre spreadsheet dans laquelle vous allez paramétrez l’export de données GA que vous ferez tourner chaque semaine :

Donnez-lui un nom (« GA-SEO » dans mon exemple de reporting SEO).

Sélectionnez le compte, la propriété et la view du site web (vous devez disposer des droits d’accès dans votre compte Google Analytics).

Sélectionnez ensuite les metrics et les dimensions qui vous intéressent.

Je vous propose a minima de sélectionner les metrics : « Bounce rate », « Average time on page » et « Transactions », ainsi que les dimensions : « Landing Page », « Source » et « Medium ».

Cliquez ensuite sur « Create report », cela va ajouter une feuille intitulée « Report Configuration » à votre spreadsheet. Cette feuille devrait ressembler à la capture ci-dessous :

 

Nous allons à présent apporter quelques ajustements à cette configuration :

• Par défaut ce rapport est programmé pour s’exécuter sur les 7 derniers jours (Last N days = 7). Nous allons remplacer ça par l’utilisation de « Start date » et « End date » pour pouvoir actualiser le reporting quand bon nous semble.
• Puisque nous ne nous intéressons ici qu’aux données des visites SEO provenant de Google pour les recroiser avec les données de Google Search Console, nous allons ajouter les deux filtres suivants : ga:medium==organic;ga:source==google
• Enfin, nous allons classer les données par ordre décroissant de volume d’entrées, nous allons donc ajouter la metric ga:entrances ainsi que le sort -ga:entrances (notez bien le moins devant)

Cela nous donne finalement la configuration ci-dessous.

Vous désormais prêts à faire tourner votre première requête à l’API GA en cliquant sur : Modules complémentaires > Google Analytics > Run reports.

Une pop-in vous confirmera la réussite de l’export de données et vous aurez le plaisir de découvrir une nouvelle feuille dans votre spreadsheet portant le nom que vous aviez indiqué pour votre rapport (« GA-SEO » chez moi pour ceux qui suivent).

En consultant cet onglet vous constaterez probablement (avec horreur) que les URL de la première colonne « ga:landingPagePath » sont en fait des URI qui ne contiennent pas votre nom de domaine.
Comment recroiser ces URI avec les URL complètes fournies dans le rapport de « Page » par Google Search for Analytics ? Pas de panique, nous allons voir comment faire un peu plus loin.

Les données de crawl de votre site

L’objectif c’est d’aller encore un peu plus loin, par exemple en intégrant au reporting SEO d’autres facteurs explicatifs de la performance de vos landing page. On va donc chercher à récupérer au moins le titre et la meta-description des pages de votre site. Pour cela je vous propose deux méthodes.

La première consiste à utiliser un outil de crawl, comme le célèbre Sreaming Frog SEO (gratuit jusqu’à 500 URL) ou bien le moins contenu mais plus gratuit BeamUsUp. En faisant tourner de temps en temps l’un de ces deux outils vous allez récupérer les données qui nous sont utiles « à froid ». Il suffira d’en faire un export manuel, de nettoyer et mettre en forme les données, puis de les copier-coller à la main d’un nouvel onglet de notre reporting SEO que j’ai nommé « Crawl Data ».  Voici par exemple à quoi ressemble mon onglet de données de crawl.

La méthode précédente, bien que non automatisée, me semble acceptable. Cependant si vous publiez chaque semaine beaucoup de nouvelles pages ou modifiez souvent les title et meta-descriptions de vos pages (ce qu’il vaut mieux éviter), vous préférerez peut-être utiliser la fonction Google Sheet ImportXML(). Elle permet de scrapper le contenu d’une page web à partir de son URL, y compris son title et sa meta-description.

Pour un exemple concret d’utilisation de cette fonction dans le cas qui nous intéresse, je vous propose de jeter un œil à ce spreadsheet créé par RankTank. Si vous avez un peu de temps, consultez les autres ressources proposées par RankTank c’est une vraie mine d’or SEO !

Jusqu’à présent nous avons vu comment agréger dans un même spreadsheet toutes les données qui vont nous servir à alimenter notre reporting SEO : données Google Search Console, données Google Analytics et données de crawl. Il s’agit maintenant de définir les indicateurs et fonctionnalité de notre reporting afin de savoir comment retraiter et stocker intelligemment ces données.

Définir le périmètre du reporting SEO et les indicateurs à analyser

Avec l’ensemble des données que nous sommes maintenant capables de collecter, les possibilités d’analyses sont presque illimitées. Pour donner forme à notre reporting SEO, il nous faut pourtant définir les metrics que nous souhaitons suivre dans le temps et les fonctionnalités à incorporer à notre outil de suivi. C’est en fonction de ce « périmètre » que nous allons bâtir le modèle de stockage et de traitement des données tel que présenté dans la suite de cet article.

Un dashboard SEO composé d’indicateurs de suivi et d’alerting

Le rôle premier de notre reporting SEO, c’est nous permettre de nous faire une idée de l’état du SEO de notre site un simple coup d’œil, semaine après semaine. La partie dashboard constituée de graphiques doit notamment aider à identifier tout phénomène anormal : une baisse soudaine du nombre de pages générant du trafic, une perte globale de positions, un problème de taux de rebond sur certains types de contenus, etc. Suite à l’identification d’un tel problème on pourra ensuite investiguer plus en profondeur pour prendre rapidement des mesures correctives.

Il faut donc bien choisir les indicateurs qui composent ce dashboard pour qu’ils soient exploitables. Inutiles de calculer et stocker des dizaines de types de données qui ne vous serviront à rien !

Voici quelques exemples d’indicateurs que vous pouvez intégrer à votre reporting SEO, cette liste n’étant absolument pas exhaustive :

• Volume d’impression, de clics et pages recevant du trafic SEO

• Nombre de mots-clés générant des impressions et du trafic SEO

• % de requêtes dont les mots-clés sont identifiées et fournis par Google

• Poids de la longue traîne en termes de nombre de mots-clés, de part du trafic SEO

• Nombre d’URL générant des impressions et du trafic SEO

• Distribution des URL en fonction des volumes de visites SEO générées

• Distribution des clics et impressions en fonction de la profondeur d’URL

• Distribution des clics et impressions en fonction en fonction du type de device

• Nombre d’URL générant du trafic SEO dans telle ou telle catégorie de contenu, volume de visites associées, taux de rebond moyen et taux de conversion moyen de ces visites

• Distribution des mots-clés par tranches de position moyenne

• Suivi des positions d’une liste précise de mots-clés stratégiques

Des fonctionnalités avancées d’analyse SEO

En plus de ces divers graphiques à assembler dans un dashboard de suivi du SEO, vous pouvez utiliser le reporting pour construire une interface permettant d’effectuer des requêtes poussées sur les données exportées grâce aux plugins.

Une interface pour gérer des requêtes avancées sur les URL

Vous pouvez par exemple construire une interface pour requêter en temps réel les URL répondant à des critères multiples tels que :

• Nombre minimum et/ou maximum d’impressions
• Nombre minimum et/ou maximum de clics
• Taux de clic (CTR) minimum et/ou maximum
• Position moyenne minimum et/ou maximum
• Taux de rebond SEO minimum et/ou maximum
• Taux de conversion minimum et/ou maximum des landing page SEO
• Nombre de caractère du titre de la page
• …

En combinant plusieurs de ces critères vous allez par allez pouvoir construire des segments d’analyses extrêmement intéressants. Vous pourrez par exemple identifier toutes les URL qui apparaissent en 2^e page des résultats de Google et qui méritent un travail d’optimisation SEO pour les faire remonter en 1ère page de Google.

Pour construire un tel segment de « Pages à booster » il suffira de prendre les pages ayant une position moyenne minimum de 20 et maximum de 10, et restreindre le segment aux pages ayant au moins 500 impressions par semaine pour que le travail d’optimisation SEO (maillage interne renforcé, amélioration du content, création de backlinks, etc.) soit vraiment rentable !

Dans mon exemple de reporting qui accompagne cet article, c’est l’onglet « Page analysis » qui permet d’effectuer ce genre de requêtes avancées sur les URL de la dernière période d’actualisation du reporting. Nous allons voir un peu plus loin comment construire une telle interface.

Le petit conseil de La Fabrique du net
Si vous souhaitez appliquer cette interface de requête à une période plus large pour certaines analyses, il suffit d’adapter la période d’extraction de données dans les onglets de collecte, sans mettre à jour les graphiques du reporting (via le menu « SEO report »).

Une interface pour gérer des requêtes avancées sur les mots-clés

De la même façon on pourra construire une interface permettant d’effectuer des requêtes sur les mots-clés identifiés ayant généré des impressions SEO pour votre site web. On retrouvera le même genre de critères que précédemment : volume d’impressions et de clics, CTR, position moyenne de vos pages pour le mot-clés, etc.

On pourra y ajouter le nombre de mots composant l’expression recherchée, ainsi que tout indicateur que vous serez capable de de calculer pour l’ensemble des mots-clés en retraitant les données collectées. Je pense par exemple à un potentiel de gain de clics qui serait basé sur la différence de CTR entre un mot-clé donné et le CTR moyen de l’ensemble de vos mots-clés ayant la même position moyenne.

Voici à quoi peut ressembler une telle interface qui s’actualise instantanément à chaque modification de l’un des critères de recherche.

Vous trouverez cette interface dans l’onglet vert « Keyword analysis » de mon reporting SEO.

Autres exemples de fonctionnalités avancées pour votre reporting SEO

Pour aller encore plus je vous suggère deux autres fonctionnalités avancées que j’ai intégrées à mon exemple de reporting SEO et qui serviront à l’optimisation du CTR de vos landing page SEO.

La première est en quelques sortes une déclinaison de l’interface de requête des URL présentée un peu plus haut. La différence c’est qu’on va regrouper les URL de notre site par tranches en fonction de leur position moyenne dans les SERPs (ex : position 1, positions 1 à 3, positions 3 à 5, position 5 à 10, etc.).

Pour chaque tranche on calculera le CTR moyen des pages concernées et on fixera un seuil de sous-performance de CTR à ne pas dépasser. Ainsi On va par exemple repérer toutes les pages ayant une position moyenne entre 3 et 5 mais dont le CTR est au moins 30% inférieur à la moyenne des pages de cette tranche. On rajoutera une condition de minimum d’impression pour ne travailler que sur des pages qui en valent la peine.

Il ne reste plus qu’à chercher à améliorer le CTR de ces pages en retravaillant leur titre et leur meta-description ! Vous trouverez ce rapport dans l’onglet « Low CTR pages » de mon exemple de reporting SEO.

La deuxième fonctionnalité que je vous propose est liée à la première. Il s’agit d’analyser l’impact marginal de la présence de certains mots dans les titres ou les meta-descriptions sur le CTR de vos pages.

Pour cela on va calculer la moyenne du CTR de toutes les pages où l’on trouve les mots-clés recherchés soit dans le titre, soit dans la meta-description soit dans les deux. On va ainsi identifier des mots-clés qui ont un impact positif (CTR supérieur à la moyenne) et d’autres à éviter car ayant un impact négatif.

On pourra utiliser cette analyse pour réécrire le title et la meta-desciption des pages identifiées comme ayant une sous-performance de CTR grâce au rapport « Low CTR pages ».

Dans mon exemple de reporting SEO, cette fonctionnalité est disponible dans l’onglet intitulé « CTR-Wording ».

Quelques conseils pour construire et structurer votre reporting SEO

Conseil #1 : Ne retraitez jamais les données dans les feuilles de collecte

Comme je vous l’ai déjà précisé au début de cet article, il ne faut surtout pas ajouter de formules de retraitement directement dans les feuilles qui servent à l’extraction des données car leur contenu est écrasé à chaque nouvelle récupération de données.

On va donc construire des feuilles dédiées au retraitement, une pour les données page-centric (« Process-Page » dans mon propre reporting) et une pour les données keyword-centric (« Process-Query » dans mon reporting). Dans ces feuilles on va utiliser 5 colonnes à gauches pour charger les données (Query ou Page + Clicks + Impressions + CTR + Position) récupérées via notre plugin Search Analytics for Sheets dans un autre onglet. Pour cela vous pouvez par exemple utiliser la fonction QUERY() de Google Sheets que je vous présente de façon un peu plus détaillée au conseil #5.

Pour chaque ligne on va ensuite pouvoir procéder à divers retraitement. Dans le cas des données de pages, on va pouvoir aller récupérer pour chaque page le taux de rebond SEO et le taux de conversion e-commerce qui nous ont été fournis par le plugin Google Analytics dans une autre feuille du spreadsheet. Pour y parvenir on utilisera la fonction RECHERCHEV() (ou VLOOKUP() en anglais) en utilisant comme clé de matching l’URL de la page.

Par la même méthode on va pouvoir ajouter d’autres colonnes à notre feuille « Process-Page » pour associer à chaque URL le titre de la page et sa meta-description à l’aide d’un VLOOKUP() sur les données de crawl (qui se trouvent chez moi dans la feuille « Crawl Data).

Le petit conseil de La Fabrique du net
Attention vous n’aurez pas toujours l’URL complète de vos pages dans tous vos onglets d’exports de données. Le plugin Google Analytics, selon la configuration de votre compte, ne sortira probablement que l’URI (sans le nom de domaine). Il faudra alors effectuer vos VLOOKUP() en concaténant le nom de domaine et l’URI, sinon ça ne marchera pas !

A titre d’exemple voici ce que donne ma formule pour aller chercher le taux de rebond SEO dans la feuille « GA-SEO » à partir des URL de la feuille « Process-Page » :

  =IF(ISERROR(VLOOKUP(SUBSTITUTE($B2,"https://www.lafabriquedunet.fr","",),'GA-SEO'!$A$15:$F$10000,5,FALSE)),0,VLOOKUP(SUBSTITUTE($B2,"https://www.lafabriquedunet.fr","",),'GA-SEO'!$A$15:$F$10000,5,FALSE))  

En plus de recroiser des données de sources multiples, les feuilles (ou onglets) de retraitement permettent de calculer certaines informations comme la profondeur d’URL d’une page. Dans mon exemple j’utilise la formule ci-dessous :

  =IF(LEN(B2)-LEN(SUBSTITUTE(B2,"/","",))>=3,LEN(B2)-LEN(SUBSTITUTE(B2,"/","",))-3,"NA")  

 

Dans le cas du retraitement des données de keywords, une formule assez proche permettra de calculer le nombre de mots contenus dans l’expression (et ainsi de determiner s’il s’agit ou non d’une expression de longue traîne) :

  =IF(LEN(B2)-LEN(SUBSTITUTE(B2," ","",))+1>=Parametres!$H$27,1,0)  

 

Vous noterez ici la référence à une valeur contenue dans l’onglet « Paramètres », dans cette formule il s’agit du nombre de mots minimum à partir duquel une expression de recherche sera considérée comme appartenant à la longue traîne. Je vous donne quelques précisions sur l’intérêt de cet onglet « Paramètres » au conseil #2.

Conseil #2 : Utilisez un onglet structurant de « Paramètres »

Plus vous cherchez à construire un reporting spécifique à un site donné, plus vous allez devoir intégrer à vos formules de retraitement et de calcul des paramètres propre à ce site. Mais la construction d’un reporting SEO n’est pas linéaire. On se rend parfois compte que le premier jet n’était pas optimal et qu’il faut modifier ce qui a été fait.

Si tous vos paramètres ont été injecté « en dur » dans des formules étendues ensuite sur des milliers de cellules, le moindre changement vous prendra un temps considérable et vous ne serez jamais à l’abri de ne pas avoir oublié de modifier quelques cellules. Pour éviter cela, utilisez un onglet « Paramètres » dans lequel vous allez regrouper toutes les données spécifiques à votre site et susceptibles de modifications. Je vous laisse regarder l’onglet « Paramètres » de mon propre reporting SEO à titre d’exemple.

L’autre intérêt c’est qu’avec un tel onglet il sera beaucoup plus facile de réutiliser et d’adapter le reporting à un nouveau site web !

Conseil #3 : Utilisez un onglet dédié au stockage des données historiques

L’analyse en SEO implique de prendre un peu de recul et d’observer autant des tendances de fond que des phénomènes ponctuels. Il est donc capital de stocker des données historiques pour construire des graphiques qui permettront de visualiser plusieurs mois d’historique.

Pour cela, on va utiliser un onglet (« Data » tout simplement dans mon reporting) dans lequel seront calculés, puis stockés, l’ensemble des indicateurs alimentant le dashboard SEO et ses graphiques. Regardez la capture d’écran ci-dessous, on voit que la ligne 2 contient des formules qui vont calculer des indicateurs à partir d’autres onglets (notamment les onglets de retraitement de données).

A partir de la ligne 6 en revanche il ne s’agit plus que de données historiques qui ont été copiées et collées en valeurs (j’insiste sur le « en valeurs » ) à partir de la ligne 2 à chaque actualisation du reporting (c’est un processus qui peut s’automatiser cf. conseil #6).

Conseil #4 : Soignez la mise en forme de votre dashboard SEO

Je ne pense avoir trop besoin d’insister sur le sujet. Si vous souhaitez que votre reporting SEO soit maintenu dans le temps et exploité correctement dans le cadre d’un travail collaboratif, il est primordial de soigner sa mise en forme. C’est avant tout votre capacité à structurer et organiser ce reporting qui en fera outil pédagogique pour vos collaborateurs.

Il n’y a pas de recette miracle, mais la lisibilité doit primer. N’hésitez pas à utiliser différents onglets pour organiser vos graphiques. Donnez des titres explicites à chacun d’entre eux, choisissez bien les couleurs et surtout les types de visualisation en fonction des données qui sont modélisées : lignes brisées pour des séries temporelles, camembert de répartition à un instant T, bar charts pour comparer les valeurs d’entités distinctes, etc.

Conseil #5 : Tirez parti de la puissance de la fonction « QUERY() »

Cette fonction de Google Sheets permet d’appeler les données provenant d’une autre feuille avec plusieurs options :

• Vous sélectionnez uniquement les colonnes qui vous intéressent
• Vous pouvez appliquer des filtres pour ne faire remonter que les lignes qui répondent à certains critères (comme un nombre d’impressions supérieur à 50 par exemple)

La syntaxe est hyper simple (proche de celle du SQL) ce qui rend cette fonction très facile et intuitive à utiliser. Pour plus de détails à ce sujet je vous renvoie vers la documentation officielle de la fonction.

Pourquoi c’est super puissant ? Vous vous souvenez du conseil #1 qui consiste à ne pas écrire de formule dans les onglets d’imports de données mais à créer des onglets de retraitement dédiés ? Et bien grâce à la fonction QUERY() vous allez pouvoir dupliquer le contenu de vos onglets d’imports en une seule formule et chaque fois que vous actualiser le contenu d’un onglet d’import, ce contenu se retrouvera aussitôt actualisé dans l’onglet de retraitement ! Pas mal non ? Voici par exemple la formule toute simple pour les données de page de l’onglet de retraitement « Process-Page » :

  =QUERY(Page!A1:E10000,"SELECT A,B,C,D,E")  

 

Si vous avez bien compris tout ce que je viens de vous dire, vous vous doutez qu’il y a encore mieux à faire avec cette formule ! Puisqu’elle peut contenir des filtres et que son résultat s’actualise en temps réel, c’est cette fonction qu’on va utiliser pour créer les interfaces permettant de requêter les données de pages ou de keywords dans les feuilles « Pages analysis » et « Keywords analysis ». Voici le genre de formule qu’on pourra écrire pour un usage plus poussé :

Conseil #6 : Automatisez vos reporting avec Google Scripts

Un dernier conseil pour les plus geeks d’entre vous : tirez profit de la puissance de Google Scripts ! Si vous avez des bases en javascript, vous allez facilement réussir à écrire quelques scripts pour ajouter de nouvelles fonctions au spreadsheet de votre reporting SEO.

Vous pouvez par exemple automatiser l’enregistrement des données historiques une fois vos imports données réalisés pour que cela se fasse en un clic à partir d’une nouvelle fonction que vous aurez ajoutée au menu du spreadsheet. Vous pouvez même aller beaucoup plus loin et faire de votre reporting une véritable web app ! Sky is the limit 😉

Guide d’utilisation du reporting SEO de la Fabrique du Net

Introduction

• Le reporting SEO fait appel à 3 types de données : les données de Google Search Console, les données de Google Analytics et des données de crawl de votre site. Vous devez donc avez les droits d’accès GSC et GA pour le site pour lequel vous construisez le reporting.

• Ce reporting SEO tourne grâce notamment à deux plugins de Chrome qui permettent de requêter les API de Google Search console et de Google Analytics. Il faut donc ajouter ces plugins à votre navigateur Chrome avant d’actualiser le reporting :
  • Search for analytics sheets
  • Google Analytics for Spreadsheets

• Ce reporting SEO a été pensé pour une actualisation hebdomadaire mais vous pouvez également l’utiliser pour une actualisation quotidienne ou mensuelle selon votre fréquence de suivi de vos données SEO

• L’organisation des onglets du reporting répond à un code couleur :
  • Le Guide d’utilisation est en violet
  • Les onglets jaunes sont les onglets de paramétrage et d’actualisation du reporting
  • L’onglet rouge contient les données de crawl à importer manuellement (il peut être actualisé moins fréquemment que les onglets jaunes)
  • Les onglets bleus servent à retraiter et stocker les données, il ne faut pas les modifier
  • Les onglets verts servent à visualiser le reporting SEO et effectuer des requêtes à la demande sur les données collectées

• Attention à ne pas renommer les onglets, cela risquerait d’altérer le fonctionnement du reporting !

Prise en main initiale du reporting SEO

Il s’agit principalement d’adapter les données de l’onglet « Paramètres » aux besoins de votre site en modifiant uniquement les valeurs des cellules en gras et en bleu.

1. Définir les seuils en fonction du niveau d’activité :

NB : Le reporting SEO est actuellement calibré pour un site comptant 500 à 1 000 pages avec 100 000 à 200 000 visites SEO par mois.

2. Définir la typologie de content grouping :

Vous pouvez définir des groupes de contenus qui seront analysés de façon spécifique dans les onglets verts, en utilisant 2 critères : la profondeur d’URL des pages et la présence d’un « pattern » dans l’URL.

Par exemple sur www.lafabriquedunet.fr, tous les articles de blog ont des URL de la forme : https://www.lafabriquedunet.fr/blog/modele-business-plan-excel-startups-web/

Ces articles de blog ont donc tous une profondeur d’URL de 2 et le pattern « /blog/ », cette combinaison excluant tout autre type de page du site.

NB : Vous pouvez définir jusqu’à 16 groupes de contenus distincts

3. Choisir votre liste de mots-clés à suivre :

Sélectionnez 20 mots-clés pour lesquels vous souhaitez suivre l’évolution de la position moyenne de votre site semaine après semaine dans l’onglet vert dédié « Positions-Graph »

4. Configurer le plugin Google Analytics :

Dans votre compte Google Analytics, allez récupérer l’ID du profil correspondant à votre site web et depuis lequel vous souhaitez extraire des données.

Allez ensuite dans l’onglet « Report Configuration » du Reporting SEO, puis dans la colonne « GA-SEO » remplacez la valeur « View (Profile ID) / ids » par l’ID que vous venez de récupérer dans Google Analytics

5. Remettre à zéro les données historiques :

A titre d’exemple, ce reporting SEO contient des données factices qui vous permettent d’avoir un aperçu des graphiques des onglets verts « Global reporting » et « Positions-Graph ».

Après avoir fait un tour sur ces onglets, si vous souhaiter utiliser ce reporting pour vos propres sites web il vous faudra nettoyer les données présentes. Pour cela, rendez-vous dans l’onglet bleu « Data » et supprimez le contenu de toutes les lignes ayant pour numéro 6 ou plus (surtout ne pas toucher aux autres lignes).

Les autres données seront automatiquement effacées lorsque vous actualiserez les différents onglets jaunes en respectant la méthode décrite ci-après.

Actualisation du reporting SEO

NB : Le processus d’actualisation peut prendre jusqu’à quelques minutes pour un site de taille importante (plusieurs milliers de pages et plusieurs centaines de milliers de visites SEO par mois)

1. Sélectionner les dates de début et de fin de la période d’actualisation :

2. Mettre à jour les 4 onglets de données Google Search Console :

• Grâce au plugin Search Analytics for Sheets on va actualiser un par un les onglets suivants : « Page », « Page&Query », « Query » et « Device ».
• Attention à bien respecter l’ordre ci-dessus pour actualiser les différents onglets
• Pour commencer, ouvrir l’onglet « Page », puis cliquer sur : Modules complémentaires > Search analytics for Sheets > Open sidebar

• Dans la sidebar qui s’affiche à droite de l’écran :
  • Sélectionnez la propriété Google Search Console correspondant à votre site,
  • Renseignez les dates de la période d’actualisation (les mêmes qu’à l’étape 1)
  • Sélectionnez la valeur « Page » pour l’option « Group by »
  • Attention à bien sélectionner « Active sheet » pour l’option « Results Sheet » !!
  • Cliquez tout en bas de la sidebar sur le bouton « Request data »

• Une fois que le spreadsheet a récupéré les données de l’onglet et a fini de s’actualiser, passez à l’onglet suivant et répétez cette opération et changeant uniquement l’option « Group by » en fonction du nom de l’onglet à actualiser :
  • Group by = Page pour l’onglet « Page »
  • Group by = Page + Query (bien respecter l’ordre) pour l’onglet « Page&Query »
  • Group by = Query pour l’onglet « Query »
  • Group by = Device pour l’onglet « Device »

3. Mettre à jour les données de l’onglet « GA-SEO » :

• Grâce au plugin Google Analytics for Sheets dont le « report » aura été préalablement configuré, il suffit de cliquer sur : Modules complémentaires > Google Analytics > Run reports

4. Mettre à jour manuellement les données de crawl :

• Avec Screaming Frog SEO, BeamUsUp ou ImportXML de Google Sheets, récupérez à minima l’URL complète, le title et la meta-description de chaque page de votre site.
• Mettez ces données en forme pour respecter l’ordre des colonnes de l’onglet « Crawl data », puis collez les données directement dans cet onglet.
• Cette étape n’a besoin d’être répétée dans le futur que si vous avez ajouté de nouvelles pages sur votre site ou modifié les title et descriptions de certaines pages.

5. Mettre à jour l’onglet « Reporting global » :

Le petit conseil de La Fabrique du net
Le traitement des données importées peut prendre un certain temps. Avant d’enclencher la mise à jour des onglets de reporting par la méthode qui suit, il est impératif d’attendre que le Google sheet ait fini ses calculs et que la jauge grise située en haut à droite ait disparu !

• La mise à jour des graphiques de l’onglet « Global Reporting » se fait grâce un script intégré au Google Sheet. Ce script ajoute l’élément « SEO report » au menu du spreadsheeet. Pour procéder à la mise à jour des graphiques il faut donc cliquer dans le menu sur : SEO report > Update global reporting

• Lors de la première utilisation du script, Google va vous demander de donner l’autorisation au script de s’exécuter. Dans la 1ere fenêtre « Autorisation requise » il faudra donc cliquer sur le bouton « Continuer » puis dans la 2eme fenêtre cliquer sur « Autoriser ». Le script n’agit que sur cette feuille de calcul.

6. Mettre à jour l’onglet « Positions-Graph » :

• Pour mettre à jour les graphiques de suivi de position des mots-clés dans l’onglet vert « Positions-Graph », il suffit de cliquer dans le menu sur : SEO report > Update positions

Présentation des rapports aux onglets verts : 

• L’onglet « Global reporting » regroupe un ensemble de graphiques et constitue le principal outil de suivi SEO et d’alerting de ce spreadsheet

• L’onglet « Positions-Graph » permet de visualiser l’évolution au cours des dernières semaines de la position moyenne de vos 20 mots-clés suivis

• L’onglet « Keywords analysis » est une interface qui vous permet d’effectuer des requêtes multicritères instantanées sur l’ensemble des mots-clés de la dernière période d’actualisation du reporting SEO. Vous pouvez ainsi obtenir l’ensemble des mots-clés correspondant à un ensemble de critères basés sur : le nombre de clics, d’affichages, la valeur du CTR, la position, le nombre de mots dans l’expression

• L’onglet « Pages analysis » fonctionne sur le même principe pour vos URL. Les critères pris en compte sont : les clics, les impressions, le CTR, la position, le taux de rebond, le taux de conversion e-commerce et la longueur du titre de la page.

• L’onglet « Low CTR pages » vous affiche à chaque actualisation du reporting l’ensemble des pages qui sous-performent en termes de CTR compte tenu de leur position moyenne et de la performance de vos autres pages ayant une position similaire. Vous pouvez définir le seuil de sensibilité de sous-performance ainsi que les groupes de positions.

• L’onglet « CTR-Wording » vous permet de calculer l’impact sur le CTR de la présence de certains mots-clés de votre choix dans le titre et / ou la meta-description de vos pages.