consultant Seo

Suivez vos positions gratuitement avec Excel

Devenez indépendant

Préambule : cet article fait partie d’une série consacrée à Excel pour le SEO.  Il s’agit du second billet.

Maintenant que je vous ai montré comment on pouvait scraper Google facilement et gratuitement avec Excel, je vais vous proposer un document qui va vous permettre de suivre vos positions dans les moteurs de recherche. Ce n’est pas un scoop, il existe des dizaines et des dizaines d’outils pour le faire, certains gratuits, d’autres payants (j’en parlais déjà à la fin de cet article). La solution que je vous propose ici n’est ni moins bonne, ni meilleure. Ses deux avantages principaux  : elle est gratuite et elle permet de n’être dépendant d’aucun outil de tracking. Son inconvénient : elle ne gère pas les proxies, ce qui diminue le nombre de mots-clés maximum à suivre.

Et pour commencer, rendons à César ce qui appartient à César. Le document initial avait été créé en 2010 par Yoav Ezer sur searchenginepeople.com. Avec mon frère Ludovic, expert VBA entre autres, nous avons « simplement » modifié le document pour qu’il fonctionne en 2014 et créé deux onglets supplémentaires : un onglet de reporting et un onglet de visualisation.

NB : on ne scrape ici volontairement que les 3 premières pages (entre nous, quel intérêt d’aller plus loin ?), mais on peut modifier ce paramètre dans directement dans VBA au besoin.

Quelques captures pour vous montrer ce que ça donne aujourd’hui, avec un site fictif :

SERP Ranking History

Voilà le résultat de la macro

Analyse Suivi des Positions

L’onglet de reporting

La visualisation des données

La visualisation des données

Ce qu’il vous faut pour utiliser le document

Le document que je vais vous proposer en bas de cet article est au format .xlsm (autrement dit, Microsoft Excel avec macros). Il va donc vous falloir d’une part une version de Microsoft Excel relativement récente (idéalement 2010, 2013 mais cela doit aussi fonctionner avec 2003 et 2007) et d’autre part, vous devrez activer les macros (plus de détail ci-dessous).

Comme pour la plupart des outils de suivi de positionnement, l’analyse s’effectuera une fois par jour. A partir de là, trois options s’offrent à vous (par ordre de préférence) :

  • vous faites tourner le document sur un VPS (c’est l’idéal). Un VPS OVH à 9,99€/mois fera amplement l’affaire (d’ailleurs si certains ont des bons plans de ce côté là, n’hésitez pas à partager en commentaire)
  • vous faites tourner le document sur votre propre PC (possible, mais implique que l’appareil soit allumé en permanence)
  • vous lancez manuellement l’outil tous les matins (je l’ai fait pendant plus d’un an … c’est franchement pénible, mais c’est faisable).

Le tutoriel

Le document que je vous propose ci-dessous est bourré de formules dans tous les sens. Vous pouvez vous en affranchir en utilisant uniquement l’onglet bleu (SERP Ranking History), puis en construisant vos propres tableaux de bord, ce que je vous conseille de faire. Pour ma part, j’utilise ce document pour suivre plusieurs sites en même temps, d’où la complexité de certaines fonctions que j’ai pas pris le temps de simplifier.

Voici les étapes à suivre pour adapter le document :

1. Renseignez vos données

Dans l’onglet rouge, renseignez votre site en B2 et donnez-lui un ID en B3 (pas d’espaces). Renseignez en colonne A (à partir de A7) vos mots-clés et indiquez si vous le souhaitez les données issues du Google Keyword Planner concernant la concurrence et le volume de recherche. Je vous invite également à inscrire la date à laquelle vous avez relevé ces informations en B5.

Dans l’onglet bleu, renseignez l’URL de votre site en B2, puis vos mots-clés sur la ligne 4 à partir de B4 (vous pouvez utiliser le collage spécial « Transposer » pour copier vos mots-clés à partir de l’onglet rouge) et supprimer les données déjà présentes dans le document (lignes 5 à 37).

2. Renommez vos onglets

L’onglet beu doit être l’ID du site.

L’onget rouge doit être pipe+tiret+ID (dans mon exemple |-monmagasinbioamoi).

3. Lancez la macro une première fois

Pour vérifier si vous récupérez bien des données, appuyez sur CTRL+SHIFT+U pour lancer la macro une première fois. Vous devriez voir apparaître la date, puis vos positions. OK, ça fonctionne ? Parfait, passons à la suite.

4. Automatisez le processus

Option 1 : vous n’avez ni VPS, ni PC allumé en permanence

Vous allez devoir lancer le fichier manuellement pour récupérer les positions du jour (vous pouvez très bien le faire seulement une fois par semaine si ça vous chante). Ne vous en faites pas, c’est on ne peut plus simple. Ouvrez simplement le fichier, la macro se lancera au démarrage et le fichier se fermera automatiquement lorsque le calcul sera terminé. Libre à vous d’ouvrir ensuite le fichier pour analyser les données.

Option 2 : vous avez soit un VPS avec Windows, soit un PC allumé en permanence

Dans ce cas nous allons créer une tâche planifiée et vous n’aurez alors plus rien à faire. Voici les étapes à suivre :

  1. Créez un dossier dans votre explorateur (ex. SERP-Ranking sur le disque F:\)
  2. Dans Excel, ajoutez un emplacement approuvé (Options Excel / Centre de gestion de la confidentialité / Paramètres du centre de gestion de la confidentialité / Emplacements approuvés / Ajouter un nouvel emplacement) et activez les macros (Options Excel / Centre de gestion de la confidentialité / Paramètres du centre de gestion de la confidentialité / Paramètres des macros / Activer toutes les macros)
  3. Placez le fichier SERP-Ranking-1.2.7.xlsm dans ce dossier (surtout pas d’espace dans le nom du fichier)
  4. Placez un raccourci de Microsoft Excel dans ce dossier. Nommez le excel.lnk
  5. Créez un fichier task1.bat dans ce même dossier, éditez-le avec le bloc-note ou Notepad++ et écrivez :  excel.lnk SERP-Ranking-1.2.7.xlsm
  6. Planifiez une tâche Windows en appelant le fichier task1.bat et notez dans « Commencer dans (facultatif) » le chemin du dossier. Ex. F:\SERP-Ranking\. Dans l’onglet « Déclencheurs », choisissez « Chaque jour ».

Planification d'une tâche Windows

Téléchargement et derniers conseils

Vous voilà avec un bel outil de suivi de positionnement maison. Encore une fois, l’avantage principal au-delà de la gratuité, réside dans le fait que vous collectez vos propres données. Vous n’êtes pas dépendant d’un service tiers. Ensuite, une fois les données brutes rassemblées dans Excel, à vous de les exploiter comme bon vous semble.

Télécharger SERP Ranking v.1.33

Les derniers conseils :

  • L’onglet vert permet actuellement de visualiser l’historique sur les trois derniers mois d’un côté et les six derniers mois de l’autre. Vous pouvez choisir le mode Détail, qui reflétera l’évolution des positions au jour le jour ou le mode Trend qui fera une moyenne des positions sur plusieurs jours afin de lisser la courbe.
  • J’ai constaté qu’il était difficile de suivre plus de 180/200 mots-clés avec le même document, limite à partir de laquelle Google affiche un captcha. La solution : scinder l’analyse en plusieurs documents (un par site par exemple) et programmer des tâches planifiées pour les lancer à quelques heures d’intervalles. Aujourd’hui, j’utilise par exemple 4 documents différents qui se lancent à 1h, 3h, 5h et 7h du matin.
  • Lors de la première ouverture de la journée, le document lance la macro automatiquement pour récupérer les données du jour, puis ferme le classeur. Pour consulter les positions du jour, ouvrez-le une seconde fois.
  • Je ne suis pas un kador du graphisme sur Excel, et pourtant il y a de belles choses à faire. Alors si vous êtes comme moi, je vous invite à suivre de prêt ce que nous préparent Raph et Polo pour le VLC 2014 !!

Le dossier complet Excel pour le SEO

Tags:

About

Après plusieurs expériences en tant que responsable e-commerce et webmarketing, puis SEO in-house, j'ai décidé de me lancer en tant que Consultant SEO freelance, je suis passionné par l'univers du search et le webmarketing en général.

View all posts by

6 Responses

  1. Régis says

    Voilà l’article que j’attendais tant.
    Pour le moment, pas déçu après lecture, je vais télécharger tout ça et je te refais un retour (rapidement).

  2. Sandrine K says

    Franchement merci, c’est incroyable tout ce que l’on peut faire avec excel, s’il y a une formation à suivre c’est la maîtrise de cet outil. Pour le scraping, j’ai juste eu un problème avec le framework .net j’ai du installer la version 3.5 pour que le SEO tools excel fonctionne, et c’était nickel. Je vais tester ton outil de suivi, je vais le faire en manuel d’abord, après je verrais. Mille fois merci.

  3. Régis says

    Cédric,

    ton travail est nickel. Comme Sandrine, je me contente de le gérer manuellement pour le moment. Ce n’est que le deuxième jour alors je ne trouve pas ça encore trop lourd. Mais j’ai deux questions plus ou moins bêtes.

    1) Peut-on renommer le fichier XLS ? là j’avoue, elle ne vole pas haut.

    2) Comment faire pour suivre les positions dans un autre pays ET/OU une autre langue ? exemple Google.IT en italien. car je suppose que nous sommes en train de relever ici les positions sur Google.FR en français..

    Merci M’sieur.

  4. Cédric Guérin says

    @Sandrine, effectivement, Excel c’est le couteau suisse du SEO et il faut avouer que le plugin SEO Tools for Excel nous est d’une grande aide.

    @Régis, oui tu peux renommer le fichier Excel, ça ne devrait poser aucun problème. Pour modifier le moteur (passer sur google.it par exemple) tu dois modifier la macro (quand le document est ouvert, tu fais ALT+F11, tu cherches google.fr et tu remplaces par google.it). Si tu veux suivre google.fr d’un côté et google.it de l’autre, il va te falloir créer deux fichiers différents.

  5. Sylvain2 says

    Salut ! Super tuto, comme le premier 🙂
    Par contre je constate un (léger) écart entre mes positions relevées avec seeUrank et votre outil. D’ou cela peut il venir d’après vous ?

  6. Cédric Guérin says

    @Sylvain2, c’est difficile à dire car les raisons peuvent être très nombreuses. Quelques pistes :

    – l’aspect local fait varier de plus en plus les résultats. L'(excellent) outil de Yooda ne scrape pas à partir de la même localité. Le data center peut également être différent.
    – les relevés de positions ne sont pas exécutés simultanément et nous savons tous que les positions bougent régulièrement dans la journée (tout du moins sur certaines requêtes et parfois sur des périodes très courtes)
    – dernier aspect. Le document que je propose n’est pour le moment pas capable d’exclure les résultat Google Image. Ainsi, si vous êtes premier sur « chat rigolo » mais que Google affiche des images au-dessus du premier résultat naturel, alors Excel renverra la position 2. Je travaille actuellement sur ce point 😉