Excel et SEO : comment faire un tableau de bord pas à pas ?

Excel est un logiciel de la suite Office quasiment indispensable lorsque l'on fait du référencement naturel. En effet, on a souvent beaucoup de données à trier et à compiler ensemble. Excel sera votre meilleur allié pour mener à bien ce travail et effectuer vos différents tableaux de bord, reportings et analyses. Dans cet exemple pratique, nous allons voir les formules les plus souvent utilisées en SEO pour créer un tableau de bord. Celui-ci permettra de déterminer les pages ayant le plus de potentiel en référencement naturel et les axes de travail prioritaires pour les améliorer davantage...

Par Julie Chodorge

Dans notre article, l'analyse sera axée sur le site d'Abondance afin de récolter suffisamment de données intéressantes et exploitables.

Export des données et rassemblement

Nous allons commencer par lister toutes les pages ayant des statistiques dans les outils de suivi SEO, tels que SEMRush, Ahrefs, SEObserver… Dans l'idéal, pour vos sites ou les sites des clients, il est intéressant également d'extraire les données sur un an de Google Analytics et de Google Search Console pour les croiser avec les autres données.

Mettre toutes les formules au même format : CONCATENER

Avant de pouvoir rassembler toutes les données, il faut d'abord s'assurer que toutes les URL soient au même format, c'est-à-dire https://www.abondance.com. Or, dans l'export de Ranxplorer, les URL sont au format www.abondance.com, sans le https. La formule Concatener va permettre de résoudre ce problème en rajoutant https:// automatiquement au début de chaque URL.

Application de la formule Concatener :

=CONCATENER("https://";[@Url])

Si on souhaite concatener un même élément pour toutes les cellules, il faut l'écrire entre des guillemets. C'est le cas pour la première partie avec https://. La séparation des différents éléments se fait avec un point-virgule. Ensuite, pour la deuxième partie, nous reprenons la cellule du tableau comprenant l'URL. Etant donné que c'est un élément du tableau Excel, il n'y a pas besoin de guillemets cette fois-ci.
La formule concatener permet de mettre à la suite autant d'éléments souhaités, à condition de bien les séparer par un point-virgule à chaque fois.

Fig. 1. Rajout de https au début des URL avec Concatener.

Fusionner les cellules en double : Suppression des doublons

Une fois que nous avons toutes nos données extraites des différentes sources, nous pouvons passer à leur rassemblement. Ainsi, il nous suffit de copier-coller à la suite l'ensemble des URL afin de s'assurer de n'en oublier aucune.

Enfin, afin de ne pas avoir plusieurs fois la même URL, nous allons pouvoir supprimer les doublons. Pour cela, il suffit d'aller dans « Données », « Outil de données » puis de cliquer sur « Supprimer les doublons ». Il faut sélectionner la colonne dans laquelle supprimer les doublons, dans notre cas c'est donc celle contenant les URL. Après validation, il ne nous reste plus que chaque URL une seule fois.

Fig. 2. Suppression des doublons après rassemblement des URL.

Rassemblement de toutes les données dans le même tableau : RECHERCHEV

Pour notre tableau de bord, nous avons donc exporté les données de plusieurs outils SEO, à savoir : SEMRush, Ahrefs, SEObserver, Ranxplorer et Majestic SEO. Il nous faut maintenant rassembler ensemble toutes les données extraites afin de pouvoir les compiler dans notre tableau de bord SEO.

La fonction RECHERCHEV va nous permettre de le faire, en recherchant verticalement une donnée commune et en l'important dans l'emplacement désiré. La fonction RECHERCHEH permet de la même manière de faire une recherche horizontale si on se trouve dans un tableau transposé.

Les données qui nous intéressent sont dans le tableau SEObserver. Nous recherchons à importer le Trust Flow ainsi que le nombre de domaines référents de chaque URL dans notre tableau récapitulatif, ce qui correspond aux colonnes 3 et 5 (par rapport à la position des URL en 1ère colonne).

Fig. 3. Récupération des données SEObserver.

La formule à appliquer pour aller chercher les données en question est donc :

=RECHERCHEV([@URL];SEObserver;3;"faux")
=RECHERCHEV([@URL];SEObserver;5;"faux")

En premier lieu, nous renseignons la cellule de référence dans le tableau dans lequel on se trouve, ici donc l'URL. Ensuite, après un point-virgule, nous indiquons l'endroit où aller chercher les données à croiser. Après un autre point-virgule, le numéro de la colonne des données à rapatrier, et enfin le "faux" à la fin de la formule permet d'indiquer que nous ne souhaitons pas de données approximatives.

Fig. 4. Application de la formule RECHERCHEV.

Il suffit ensuite de refaire la même application pour toutes les données à rassembler dans le même tableau. Afin de s'assurer que toutes les données sont bien stockées au même endroit, après import, mieux vaut copier-coller en valeur le contenu des colonnes.

Rassemblement de données provenant de lignes multiples : SI

Les données exportées de SEMRush comportent de multiples lignes et plusieurs concernent la même URL (quand celle-ci positionne plusieurs mots-clés différents). Il faut donc importer les données qui nous intéressent en utilisant des conditions, grâce aux formules utilisant « SI ».

Fig. 5. Tableaux exporté de SEMRush avec les données à rassembler.

Pour commencer, nous allons utiliser la formule NB.SI qui permet de savoir le nombre de fois qu'une plage de cellules contient le critère que l'on recherche. Dans notre cas, l'URL.

=NB.SI(SEMRush[URL];[@URL])

Cette formule nous permet de savoir le nombre de fois où une URL apparaît dans le tableau de SEMRush, et ainsi le nombre de positions d'une URL. Ensuite, nous allons faire une somme avec une condition. Ceci correspond à la formule SOMME.SI. Cette formule nous permettra de faire la somme de la volumétrie de recherche totale en additionnant tous les volumes de recherche des différentes positions d'une URL.

=SOMME.SI(SEMRush[URL];[@URL];SEMRush[Search Volume])

Dans cette formule, voici les éléments indiqués : en premier lieu, nous prenons l'ensemble de la colonne qui contient les URL exportées de SEMRush, ensuite nous sélectionnons le critère à prendre en compte, donc la cellule dans laquelle se trouve l'URL de référence. Et enfin, nous sélectionnons l'ensemble de la colonne qui contient les nombres à additionner, donc les volumes de recherche.

Enfin, nous allons appliquer le même type de formule pour faire une moyenne. Ceci permettra d'obtenir le positionnement moyen de chacune des URL.

=MOYENNE.SI(SEMRush[URL];[@URL];SEMRush[Position])

De la même manière que dans la formule SOMME.SI, dans la formule MOYENNE.SI, nous indiquons la plage où rechercher la donnée commune, l'emplacement du critère, et enfin la plage de nombres dont il faut faire la moyenne. Nous obtenons ainsi la position moyenne de chaque URL en fonction de toutes les requêtes qui positionnent la page en question.

Toutes les données sont donc maintenant rassemblées dans le même tableau. Pour la suite de l'expérience, nous n'allons garder que les URL ayant drainé le plus de trafic sur les différents outils SEO afin de partir sur une base d'URL ayant le plus de potentiel pour le SEO. Nous conservons donc seulement les pages qui ont un trafic moyen supérieur à zéro, ce qui nous permet de partir d'une base de 2 086 URL.

Ajout de données communes aux URL SEO

Maintenant que nous avons nos 2 000 URL environ qui drainent du trafic naturel, nous allons pouvoir leur ajouter des données communes afin de mieux comprendre les différents axes d'optimisation et pour les faire progresser encore plus dans les résultats de recherche organiques.

Pour cela nous allons utiliser, en plus du crawleur Screaming Frog, le module SEO Tools For Excel qui permet de scraper des pages web et de récupérer les données qui nous intéressent. Des formules existent pour récupérer des métriques habituelles telles que les balises meta, titres H1, codes réponse, domaine principal… Au-delà de ça, nous allons également pouvoir scraper les URL pour aller chercher le contenu qui nous intéresse : nombre d'articles par catégorie, chemin du fil d'Ariane, notation des articles…

Mise en forme automatique du contenu : mise en forme conditionnelle

Etant donné que ce tableau doit être un tableau de bord, il est important qu'il soit visuellement agréable à regarder et que l'on puisse tout de suite voir les données qui nous intéressent. Pour cela, nous allons utiliser la mise en forme conditionnelle de Excel qui permet de mettre des couleurs automatiquement sur les pages, de manière croissante ou décroissante.

Pour cela, il suffit de sélectionner la colonne sur laquelle nous souhaitons mettre en place la mise en forme conditionnelle, puis de cliquer dans l'onglet « Accueil » sur « Mise en forme conditionnelle ». Excel se charge tout seul de mettre du vert sur les valeurs les plus élevées et du rouge sur les valeurs les plus faibles.

Fig. 6. Mise en forme conditionnelle.

D'autres mises en forme conditionnelles existent pour différents usages.

Récupération de données avec les formules SEO Tools for Excel

Code réponse

Afin de récupérer directement dans Excel le code réponse des URL, il faut appliquer la formule :

=HttpStatus([@URL])

Nous récupérons ainsi tous les codes réponse, et pouvons tout de suite identifier les URL qui ne répondent pas en 200.

Dans notre cas pratique, cela permet de relever X erreurs 404 qui seront à corriger en priorité car elles sont positionnées et créent du trafic :

Fig. 7. Codes réponse ne répondant pas en 200.

Cette fonction a également permis de mettre en avant des chaînes de redirection avec plusieurs redirections 301 à la suite, ainsi qu'un problème de certificat SSL sur un sous-domaine.

Pour la suite de l'expérience, nous n'allons procéder aux tests que sur les URL répondant bien en 200 afin qu'elles soient accessibles et que les données puissent être scrapées.

Balises Meta Robot et Canonical

Afin de nous assurer que les URL qui génèrent du trafic SEO sont bien indexables, nous allons nous assurer qu'elles ne sont ni en noindex ni canonisées.
Pour cela, nous allons utiliser des fonctions de SEO Tools for Excel :

=HtmlMetaRobots([@URL])
=HtmlCanonical([@URL])

Cela nous permet de voir que sur les 1 910 URL en 200 générant du trafic naturel, aucune n'est en noindex (ce qui assez logique puisqu'elles sont positionnées et donc logiquement indexées). En revanche, certaines ont des balises canonical indiquant une autre URL.

Afin d'avoir la liste complète, nous utilisons la formule SI qui permet de comparer les URL et les balises canonical pour observer si ce sont les mêmes URL, et donc si elles sont canonisées ou non.

=SI([@URL]=[@[Balise Canonical]];"auto-référente";"canonisée")

Ceci nous permet de remonter 10 URL canonisées qui créent malgré tout du trafic naturel. Il faudra les regarder en détail pour identifier si la balise canonical est justifiée.

Fig. 8. Récupération des balises canoniques et interprétation.

Autres statistiques de la page

Maintenant que nous avons la liste de toutes les URL en code 200 indexables, nous allons pouvoir relever un certain nombre de données sur ces URL, à savoir :

Taille de la page : connaître le poids de la page pour identifier rapidement des URL qui sont trop lourdes et qu'il faudrait alléger.

=PageSize([@URL])

Temps de réponse : connaître le temps de chargement des pages et vérifier qu'elles ne pourraient pas se charger plus vite pour optimiser leur crawl.

=ResponseTime([@URL])

Balise Title : identifier si des balises Title pourraient être mieux optimisées pour le SEO.

=HtmlTitle([@URL])

Balise Meta Description : identifier si des balises meta description sont vides et pourraient être renseignées pour augmenter le taux de clic.

=HtmlMetaDescription([@URL])

Titre H1 : optimiser les titres H1 pour le SEO.

=HtmlH1([@URL])

Nombre de mots : les pages qui se positionnent ont-elles assez de mots ? Ne faudrait-il pas en rajouter à certains endroits pour optimiser leur référencement ?

=WordCount([@URL])

Nombre de liens : Combien y a-t-il de liens (internes et externes) sur les pages ? N'y-a-t-il pas une perte de jus qui pourrait être corrigée ?

=LinkCount([@URL])

Nous recueillons ainsi de nombreuses données utiles pour le référencement naturel, qui vont nous permettre d'optimiser les pages ayant le plus de potentiel SEO.

Fig. 9. Recueil des données HTML des pages SEO pour les optimiser.

Couplage avec des données exportées d'outils SEO

Au-delà des données que nous récupérons avec SEO Tools, nous allons également nous intéresser à des données exportées d'outils SEO tels que le nombre de liens externes avec Majestic SEO et le nombre de liens internes avec Screaming Frog.

Ceci nous permettra d'identifier si certaines URL qui créent déjà du trafic naturel ne pourraient pas être encore mieux maillées, que ce soit au sein du domaine ou d'internet en général.

Liens externes : Majestic SEO

A l'aide de la fonction recherchev expliquée ci-dessus, nous allons récupérer le nombre de liens externes de nos pages SEO.

=RECHERCHEV([@URL];Majestic[[URL]:[ReferringExtBackLinks]];5;"faux")

Ici, nous allons donc chercher les URL dans le tableau Majestic, la donnée à récupérer étant le nombre de backlinks située en colonne 5.

Nous obtenons ainsi le nombre de backlinks pour chaque URL avec du potentiel SEO. Nous pouvons ainsi identifier rapidement celles qui en manquent et dont il faudrait renforcer la stratégie de netlinking.

Liens internes : Screaming Frog

Autre élément intéressant à rassembler dans ce tableau de bord SEO : le nombre de liens internes. Nous pouvons obtenir ces données après un crawl avec Screaming Frog en exportant les liens internes. Le souci est que lors d'un export Screaming Frog, les données sont dans un fichier csv, et sont donc séparées par des virgules.

Pour obtenir les chiffres dans des colonnes, il faudra convertir les données. Pour cela, il faut cliquer dans l'onglet « Données » sur « Convertir ».

Fig. 10. Convertir un fichier CSV en Excel.

Il faut choisir un type de données d'origine, nous sélectionnons donc ici « Délimité ». Ensuite nous sélectionnons le type de séparateurs. Ici, ce sont des virgules. Dans un autre cas, cela pourrait être des points-virgules, des slashs, des points…

Après avoir cliqué sur « Terminer », nous obtenons les données converties.

Fig. 11. Convertir un fichier CSV en Excel.

Les données étant maintenant exploitables, nous pouvons maintenant rassembler les données dans notre tableau de bord avec une formule NB.SI pour calculer le nombre d'occurrences dans les liens de destination sur le fichier de liens internes.

Fig. 12. Récupération des liens internes exportés de Screaming Frog.

Nous savons maintenant quelles sont les pages qui reçoivent le plus de jus interne et celles qui sont laissées dans la stratégie du maillage interne. Ceci peut nous permettre de nous rendre compte d'un possible problème dans la structure du site et des pages avec du potentiel SEO auxquelles il faudrait donner plus d'importance dans le maillage.

Récupération de données spécifiques sur les pages

Dernière étape pour récupérer toutes les données qui pourraient nous intéresser pour un tableau de bord SEO : relever des données propres aux problématiques de notre site, ou du site de notre client.

Abondance étant un blog SEO très lu des professionnels du métier, nous allons nous intéresser aux interactions sur le site et donc aux commentaires. En effet, ceci peut être un bon levier pour mettre en avant des articles ayant été beaucoup commentés et qui intéressent donc le public. Ces articles pourront être travaillés pour le référencement naturel afin d'être mieux positionnés et créer encore plus d'engouement.

Pour cela, nous allons scraper les URL à l'aide de la formule XPATHONURL de SEO Tools for Excel.

Nous commençons par nous rendre sur une page d'article avec des commentaires afin d'identifier où se situe le nombre de commentaires dans le code source.

Fig. 13. Localisation des données à scraper.

Dans l'impression d'écran ci-dessus, nous observons que le nombre de commentaires est indiqué dans un titre H2 dont l'ID est « Comments ». Nous allons donc utiliser ce dénominateur commun à tous les articles pour récupérer le nombre de commentaires de chacun.

Dans une même logique, nous pourrions également scraper à partir d'une div, d'une section, d'une liste à puces… en reprenant l'ID ou la classe. L'avantage de s'appuyer sur l'ID d'un élément est qu'il est unique sur la page, alors qu'une classe peut être utilisée sur plusieurs éléments d'une même page.

La formule à utiliser est donc celle-ci :

=XPathOnUrl([@URL];"//h2[@id='comments']")

Nous commençons par indiquer l'élément dans lequel nous allons chercher la donnée, ici un titre H2, puis le type de dénominateur, ici l'ID, et enfin le nom de ce dénominateur, ici « comments ». Nous récupérons ainsi le nombre de commentaires pour chaque article.

Fig. 14. Récupération du nombre de commentaires par article.

En remplaçant ces données par des nombres et en appliquant une mise en forme conditionnelle, on se rend tout de suite compte des articles qui suscitent l'intérêt des lecteurs et pourraient être davantage travaillés pour le SEO.

Fig. 15. Articles les plus commentés à travailler pour le SEO.

Récapitulatif des formules

Nous avons ainsi un tableau de bord SEO qui peut être modelé en fonction des besoins de vos sites et/ou des sites de vos clients. A vous de vous familiariser avec ces formules afin de pouvoir ensuite produire votre propre tableau de bord avec les données qui vous intéressent au quotidien.

Parmi les formules présentées, voici celles dont vous devriez avoir principalement besoin :