Analyse concurrentielle express : Semrush et Power query 6


zenobral

zenobral

Depuis 9 ans, le SEO c'est mon dada. Mais ce que j'aime particulièrement, c'est le traitement de data : crawl, logs, outils en ligne de commande, excel, python.... Je partage avec vous, sur CanYouSeoMe, une partie de mon expérience, en espérant vous débloquer ou vous guider vis à vis de problèmes fréquemment rencontrés.
zenobral

Qu’on soit SEO in house ou en agence, plutôt spécialisé crawl ou linking, il y a une étape à laquelle on ne coupe généralement pas dans la prestation réalisée sur un site, c’est la recherche d’opportunités de mots clés.

Je vous livre ici une petit méthodo perso qui, si elle ne constitue pas complètement une vraie analyse sémantique (pas de catégorisation, d’analyse des pages de destinations ou tout simplement de l’offre) vous prémâchera en grande partie le travail pourvu que vous vous donniez la peine de maîtriser les outils ( et croyez moi, cela vaut le coup !)

Les outils

Semrush

Pour tout ceux qui vivaient dans une grotte ces dernières années, Semrush est un outil de monitoring des positions et des backlinks des sites. Disposant d’une base de plusieurs millions de mots clés dans plusieurs langues, il crawle chaque jour Google et relève pour chaque mot clé les positions en naturel et adwords des sites ainsi que les éventuels features Google lié à la page de résultat ( avis, knowledge graphe, etc…)

C’est personnellement mon outil favori pour la surveillance de la concurrence ( niveau positionnement en tout cas) et c’est celui que je vais utiliser ici. Je vous encourage néanmoins à tester les autres solutions du marché qui fournissent des services équivalents pour voir ce qui vous convient le mieux. L’essentiel sera de pouvoir avoir, pour un site donné, les concurrents proches en termes de positionnement, et pour chaque site, la liste des mots clés positionnés avec les volumes de recherche.

Classées de manière arbitraire et listées de manière non exhaustive, voici d’autres solution à tester qui font un job équivalent et parfois meilleur selon vos besoins :

Power query

Power query est à l’origine une extension Excel, mais qui a été intégrée nativement dans Excel 2016. C’est une extension fabuleuse, qui demande à être prise en main, mais qui vous fera gagner un temps fou dès que vous aurez des données à importer et à traiter… en particulier s’il s’agit de fichier massifs.

Le gros avantage de cette extension est qu’elle applique l’ensemble des traitements avant son affichage dans Excel sans les stocker, ce qui évite d’avoir un classeur avec des formules complètement délirantes de tous les cotés, avec le poids que cela implique. L’ensemble des traitements s’appelle une requête, et la grande force de ce système est de pouvoir actualiser ces requêtes : il suffit de remplacer les fichiers sources de traitement, actualiser, et boum ! Le nouveau résultat apparaît sans avoir repassé 1H à faire 30 manipulations qui abîment le cerveau. Dans les fonctionnalités très pratiques vous aurez entre autres :

  • import de xml, json
  • Tableau croisé dynamique
  • Décroisement de tableau dynamique (une des mes fonctionnalités préférées)
  • Jointure (là c’est ma préférée)
  • Import de lot de fichiers (c’est d’ailleurs ce que l’on va voir)

Si vous possédez une version d’Excel antérieure à Excel 2016, vous pouvez télécharger l’extension ici : https://www.microsoft.com/fr-FR/download/details.aspx?id=39379. Si vous êtes sur Mac, et bien… tant pis pour vous, de toute manière Excel pour Mac est largement inférieur à sa version Windows.

Je ne vous guide pas dans l’installation, vous êtes des grands… ( mais si ce n’est pas le cas, j’ai pu trouver un guide ici : https://www.excelcampus.com/install-power-query/ ). Si tout s’est bien passé, la prochaine fois que vous ouvrirez Excel, vous disposerez d’un onglet Power Query.

Dans l’exemple ici, je vais tout faire avec Excel 2016, il y aura donc potentiellement des différences en termes d’ergonomie… mais vous devriez vous y retrouver.

Au boulot !

Je vais choisir un site que je ne connais pas, pour l’exemple : guitarshop.fr. Le but va donc être de trouver rapidement des opportunités de mots clés pertinents pour l’activité, sur lesquels le positionnement n’est pas là, où en tout cas améliorable.

Sur mon poste de travail, je vais donc créer un dossier guitarshop et à l’intérieur un autre dossier « data » pour contenir tout les Csvs. De manière générale, je vous recommande de TOUJOURS travailler avec des Csvs quand vous le pouvez. Tous les tableurs sont capables de travailler avec ce format, et si besoin, vous pourrez également les retraiter facilement en ligne de commande.

Récupérer les concurrents et les positionnements

On va commencer tout simplement par se rendre sur Semrush (ou le tool de votre choix), et regarder le rapport organique :

Puis sur le rapports concurrents, on récupère ceux qui ont le plus de chevauchement avec les keywords de Guitarshop.fr. Je me suis limité à 10, et n’ai pas filtré sur le type de site ( offre similaire? forum?). Après tout est question de choix et de ce que vous voulez trouver… Ici c’est juste pour la démonstration. D’ailleurs j’ai regretté de ne pas voir le site d’un confrère spécialisé sur la vente de cordes de guitare : https://www.lacartemusique.fr/

Enfin, pour guitarshop.fr et tous ses concurrents, on exporte les positions au format Csv, et on les sauve dans notre répertoire data :

Dans Power Query

Passons au traitement à proprement parler. A la racine de votre dossier guitarshop, créez un nouveau fichier excel et ouvrez-le.

On va directement importer tous les Csvs en un seul bloc et les traiter d’un coup d’un seul. Comme j’avais précisé plus haut, je donne la démarche pour excel 2016… Si vous possédez une version antérieure, il faudra aller chercher les options dans le ruban « Power Query » qui aura du être rajouté après installation de l’add-on.

Donc commencez par créer une requête (car c’est ainsi que cela s’appelle), en choisissant d’importer un dossier puis en sélectionnant votre répertoire « data » ou vous avez stocké tous vos Csvs :

Puis sélectionnez « modifier la requête » (bah oui ça serait trop simple !):

Vous allez à présent vous retrouver dans l’éditeur de requêtes. Sélectionnez les deux premières colonnes en cliquant sur leurs en-têtes en maintenant la touche « CTRL » enfoncée, puis faites un clic droit sur un en-tête et faites « supprimer d’autres colonnes » ( le wording est pas top)

On va nettoyer la colonne « Name » afin de ne faire apparaître que le nom du site à la place du nom du fichier. Les fichiers Csvs SemRush sont nommés « domain.com-domain_organic-nomdelabase.csv » (ici nomdelabase est « fr » ). On va donc remplacer la valeur « -domain_organic-nomdelabase.csv » dans la colonne :

Pour le moment les fichiers Csvs sont sous forme « binaire » et ne sont donc pas encore décodés. Pour les traiter on va ajouter une colonne personnalisée :

On va utiliser la fonction Csv.Document pour décoder ces fichiers binaires. Cette fonction prend également des paramètres optionnels donc le séparateur et l’encodage, qu’on va lui fournir ici. En effet, la plupart des Csvs que l’on peut télécharger (dont ceux de SemRush)  sont encodés en Utf-8, qui correspond au code 65001 dans Excel. Si vous êtes du genre à ouvrir des fichiers Csvs directement dans Excel puis à faire 80 « remplacer » pour enlever les caractères bizarres, sachez simplement que vous êtes en train de perdre un temps monstrueux, et qu’il s’agit simplement d’une problématique d’encodage qu’Excel sait parfaitement gérer à partir du moment où l’on maîtrise les fonctions d’import de texte (hors Power query).

On revient à nos moutons, saisissez dans la boite de dialogue la formule suivante puis cliquez sur « OK »

Vous devriez normalement vous retrouvez avec une colonne « Personnalisé » (si vous ne l’avez pas renommée) qui contient plein de « Table ». Cliquez sur l’espèce de double flèche à droite de l’en-tête « Personnalisé » afin de développer la colonne :

Puis validez en cliquant sur « OK » dans la boîte de dialogue qui s’est ouverte. Dans la capture ci-dessous, j’ai décoché « utiliser le nom de la colonne d’origine comme préfixe », mais ce n’est pas obligatoire :

Vous devriez à présent vous retrouver avec l’ensemble de vos colonnes développées et donc reconnaître les différents éléments du rapport de Semrush. Pour l’instant les colonnes sont nommées « Column1 », « Column2 », etc… ce qui n’est pas vraiment pratique. En réalité l’en-tête de la colonne se situe dans la première ligne de donnée. On va donc « promouvoir » les en-têtes en cliquant sur « Utiliser la première ligne pour les en-têtes » :

La première ligne nous sert à présent d’en-tête. On commence à être pas mal. Par contre les en-têtes des autres Csvs se baladent au milieu des données de mots clés, ce qui ne nous intéresse pas trop dans notre rapport final. On va donc filtrer la colonne Keyword (Par exemple) en lui demandant de ne garder que les mots clés qui ne sont pas égal à « Keyword » ( le nom de nos en-têtes donc ). Comme il s’agit d’un filtrage de ligne, et que tous les en-têtes sont toujours sur la même ligne, on est ainsi assurés de les enlever proprement.

Pour ce faire, cliquez sur la petite flèche à droite de « Keyword », puis dans les filtres de texte, choisissez de filtrer sur « n’est pas égal à » :

Saisissez la valeur « Keyword » dans la boite de dialogue, puis validez :

Vous suivez toujours ?

On va à présent ne garder que les colonnes qui nous intéressent pour notre analyse, à savoir la colonne avec les domaines (qui porte le nom du premier domaine de la liste si vous ne l’avez pas renommée), et les colonnes Keyword, Position et Search Volume. Comme tout à l’heure, cliquez sur les en-têtes des colonnes en maintenant la touche « CTRL » enfoncée, puis clic droit, puis « supprimer d’autres colonnes » :

Les colonnes Position et  Search Volume sont en type « n’importe lequel » car avant le filtre de « Keyword », les en-têtes se baladaient au milieu des valeurs numériques des positions et des volumes. On va donc explicitement dire qu’il s’agit de valeur numériques. Pour cela sélectionnez ces colonnes, puis cliquez sur « Type de données » et choisissez « Nombre entier » :

Ca va ? C’est quasi la fin ! je dirais même que c’est le moment de la magie. Pour terminer on crée un tableau croisé dynamique, en mettant les sites en colonne et en affichant pour chaque combo « Site-Mot clé » la position minimale relevée.

Pour faire cela, sélectionnez la colonne correspondant aux sites, puis dans « transformer », cliquez sur « colonne de tableau croisé dynamique » (l’intitulé se voit en survolant l’icône) :

Dans la boite de dialogue, sélectionnez comme « Colonne de valeurs » « Position » et comme « Fonction de la valeur agrégée » « minimum » puis cliquez sur « OK » :

Avec un simple glisser-déposer, on va replacer guitarshop.fr en premier dans la liste des sites, afin de faciliter la lecture ultérieure.

Il ne reste plus désormais qu’à charger les résultats dans la feuille Excel :

Vous devriez être à présent en possession d’un beau tableau avec, par mot clé, le volume de recherche et la position minimum de chaque concurrent.

Dernière bidouille dans Excel

Le problème avec des listes de mots clés aussi longues, c’est qu’évidemment on a souvent des termes qui ne sont pas du tout pertinents pour notre site… et fouiller dans plus de 35 000 mots clés ( comme c’est le cas ici ) c’est un peu long.

Afin de faciliter la lecture, on va rajouter une colonne supplémentaire qui nous servira de filtrage… On va compter le nombre de sites qui ont des positions inférieures à 20 sur une requête. Sur une dizaine de site, comme c’est le cas ici, on peut raisonnablement penser que au moins trois sites sont positionnés sur une requête, c’est que très certainement elle doit être au moins pertinente pour le secteur.

A droite de votre tableau, complètement collé, saisissez un nouveau nom de colonne : ceci aura pour effet d’étendre automatiquement le tableau. Puis dans la première cellule saisissez la formule suivante : NB.SI(plage, « <=20 ») où la plage correspond à l’ensemble des positions pour le premier mot clé du tableau. L’ensemble de la colonne devrait alors se mettre à jour (une des propriétés des tableaux sous Excel )

Maintenant à vous de jouer pour les analyses, où les graphes… Il vous suffit de cliquer sur les petits flèches à droite des en-têtes de colonne afin de filtrer ou trier les résultats. Vous pouvez par exemple :

  • Trier les mots clés par volume
  • Filtrer les positions de guitarshop.fr qui sont supérieures à 20
  • Filtrer sur les positions où il y a au moins 3 sites positionnés

Ceci vous donnera un premier aperçu rapide des mots clés potentiels à développer :

J’avoue, je n’ai pas regardé le catalogue de guitarshop.fr plus que cela, mais probablement que certains termes ne font pas partie de l’inventaire des produits vendus et il faudra encore affiner derrière.

Conclusion

Bon, cela peut paraître long comme cela, car il y a beaucoup de captures d’écran… mais étant donné que Power query est quasi inconnu des Seos ( Déjà quand je vois Excel), j’ai tenu à détailler au maximum les étapes.

La vérité est que, une fois les exports dans le dossier, tout ceci avec l’habitude prend moins de 2 minutes montre en main et main dans le slip. J’ai creusé plein de solutions et croyez moi, celle-ci est très certainement la plus rapide et la plus efficace que vous ne trouverez jamais.

Une des grandes forces de cet outil, c’est surtout d’actualiser les requêtes. Dans cet exemple… la source de données est simplement un dossier avec des fichiers Csvs standards. Si on respecte le format des fichiers que l’on a utilisés, il suffit simplement de remplacer, ajouter ou supprimer des Csvs dans le dossier et d’actualiser la requête pour qu’instantanément notre tableau soit mis à jour avec ce jeu de nouvelles données… et ça c’est vraiment un gain de temps incroyable.

A vous de vous plonger un peu dans l’outil… il faut parfois savoir perdre un peu de temps maintenant pour en gagner beaucoup plus tard. Les idées d’exploitation sont vraiment très larges… On peut, par exemple, parfaitement imaginer déposer des historiques de positionnement d’un client au format Csv, avec un fichier par date, et utiliser la même technique pour reconstruire un historique des positions, mais cette fois-ci les colonnes seraient des dates.

Je m’en suis personnellement beaucoup servi ces derniers temps pour recetter des migrations : csv crawl preprod à gauche, csv crawl prod à droite, un set de transformation, et à chaque modification de la préprod, je remplace le crawl-preprod.csv et j’actualise et je compare à nouveau… redoutablement efficace.

J’espère vous avoir donné envie de découvrir l’outil, n’hésitez pas à nous dire si vous souhaitez plus de tutos dessus, où éventuellement à nous communiquer des problématiques spécifiques sur lesquelles vous avez l’impression de passer énormément de temps pour rien.

Bonne analyse !

 

 


Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

6 commentaires sur “Analyse concurrentielle express : Semrush et Power query

  • Thomas BARBERA

    Ah oui j’adore !

    Je fais exactement ce type d’analyse concurrentiel généralement et à chaque fois, je dois ouvrir le CSV avec Notapad, copié-collé, supprimer les colonnes et faire tout le temps les mêmes manipulations avant d’arriver au même résultat.

    Là finalement avec Power, tu fais la tâche une fois et c’est terminé.
    Il y a peut-être moyen d’automatiser avec une Macro en plus non ?

    Franchement, c’est top, merci !

    • zenobral Auteur de l’article

      En fait la macro, c’est la requête elle même. A chaque fois que tu actualises il y a un « replay » de l’ensemble des transformations que tu as effectuées et les données sont mises à jour. Sur la partie purement « traitement » je pense qu’il est difficile de faire plus rapide. L’étape suivante serait plutôt d’automatiser la récupération des données… mais là on est déjà pas mal 🙂

  • Lucas Dominguez

    Merci Bruno pour ce tuto ultra-clair, simple à mettre en place et applicable ! Plus besoin de se galérer avec plusieurs feuilles volantes excel ou 15 onglets !
    Après un petit temps de découverte, je me retrouve avec un tableau super propre en moins de 5 minutes qui me permet de voir les opportunités grâce aux KW des concurrents sur lesquels j’existe pas en top 100 par ex.
    Au dela de l’analyse concurrentielle, le doc permet d’obtenir une étude de mots-clés complète et surtout pertinente (présence de 3 concurrents ou plus sur le même KW en top 20) en un temps record !

    Du lourd en somme, à quand la suite ? 🙂
    Lucas

    • zenobral Auteur de l’article

      Merci Lucas pour ce super retour :). J’espère que tes commentaires vont encourager les lecteurs à mettre la main à la pâte. Je pense en faire un similaire mais cette fois pour les backlinks à partir d’un bulk majestic SEO, ce qui sera l’occasion de voir une fonction sympa : le fractionnement d’Url.

  • Justine Flesch

    Très bon tuto ! Je viens de tester et c’est effectivement rapide et puissant.
    C’est un gain de temps énorme pour avoir un premier insight plutôt intéressant sur l’état du ranking d’un site et son environnement concurrentiel.
    Super d’avoir partagé ça ! Merci beaucoup Bruno (et merci à Lucas de m’avoir envoyé le lien).

  • Antoine Girault

    Et bien m***e, c’est bien la première fois que je regrette d’être sous Mac,… ou plutôt la deuxième, car j’ai déjà rencontré la même frustration avec SEO Tools for Excel ! Merci en tous cas pour le tuto, je le garde sous le coude au cas où je me motive à faire tourner un Excel 2016 sous PC !