Monitorez les SERPs sans dépenser un rond (tutorial)

Que l’on soit SEO ou éditeur avec de bonnes bases, il peut être fort utile d’étudier les fluctuations des SERPs (Search Engine Result Pages) sur une expression choisie, et en profondeur tant qu’à faire. D’ailleurs certains le font déjà, cet article n’a pas la prétention d’inventer l’eau chaude. Je souhaite juste donner quelques billes pour montrer qu’il est tout à fait possible de faire ça soi-même, sans avoir à développer d’outil, et sans mettre la main à la poche… excepté le coût éventuel d’une licence Excel. Vous verrez, ce tutorial est très simple.

SERPs monitoring

Il faut d’abord expliquer quels peuvent être les intérêts à suivre les remous des SERPs de la sorte :

  • Se faire sa petite météo des SERPs sur une (ou des) expressions
  • Repérer des sites qui se font shooter par Google lors d’un update (ou pas)
  • Faire un suivi de positions amélioré : relever ses positions c’est bien, mais relever tous les sites autour d’une même expression, c’est carrément mieux !
  • Mieux analyser et anticiper la concurrence
  • Mesurer la stabilité d’un site dans les SERPs
  • Faire de la veille d’e-reputation
  • etc.

3, 2, 1…

Première étape incontournable : scraper !

L’idée est donc de récolter à intervalles réguliers la liste des urls qui répondent sur l’expression de son choix. Pour illustrer cet article, je prendrai pour exemple l’expression « rachat crédit »… au hasard hein !
Il faut pour ça un scraper, programme dédié à l’extraction des données sur le web, en l’occurrence, les résultats de Google. On parle souvent des populaires scrapebox, ou RDDZ Scraper plus intuitif et français. L’un comme l’autre ont de gros atouts, mais comme on est radin sur ce coup, pas question de dépenser un centime dans des licences et surtout l’achat de proxies toujours indispensables à ces outils. Scraperr.com est un petit outil en ligne qui récolte les 500 premiers résultats Google de n’importe quelle requête, et qui les restitue au format CSV. Hyper simple, gratos, pas de proxy, tranquille.

Scraperr

Sélectionnez Google.fr, entrez votre requête, cliquez sur « Get results ». Une fois le scrap terminé un bouton vous proposera de télécharger le fichier CSV des 500 premiers résultats. Il faut alors répéter l’opération à intervalle régulier. Disons 1 fois par semaine sur 5 semaines histoire d’avoir un résultat parlant.

Opération nettoyage

A partir de maintenant, tout se fait sur Excel. Je me suis basé sur Excel 2010, mais la version 2013 devrait le faire également. Je n’ai pas testé avec la version Mac, mais en théorie, ça devrait fonctionner.

Parfois scraperr.com génère des .CSV bien crades, des fois non…

J’ignore totalement pourquoi, mais la qualité des fichiers exportés n’est pas toujours constante. Alors il est impossible d’automatiser complètement le processus de nettoyage. Quoi qu’il en soit, ce n’est pas grand chose à faire. Donc de deux choses l’une :

Votre fichier CSV est clean : à savoir que toutes les URLs ne sont pas parasitées par des chaines de caractères en début et fin d’url (soyez attentif !). Dans ce cas, rien à faire, et passez à l’étape suivante.

Votre fichier est crade : il y a des rajouts en début d’url avec par exemple des http://www.google.fr/url?url= ou encore des /url?q=, et en fin d’url on a des paramètres rajoutés qui commencent généralement toujours par la même chaîne de caractères (par exemple : &sa=*). Bref, il faut nettoyer tout ça.
Utilisez la fonction rechercher/remplacer, et au besoin, supprimez les paramètres d’URLs rajoutés par scraperr avec la macro ci-dessous (à adapter, et cela nécessite de sélectionner la plage entière des URLs à nettoyer) :

Sub CleanURLs()

Dim cl      As Range
Dim stVal   As String
Dim iLeft   As Long
Dim iRight  As Long

For Each cl In Selection

iLeft = InStr(cl.Value, "http")
iRight = InStr(cl.Value, "&sa")

stVal = Mid(cl.Value, iLeft, iRight - iLeft)
cl.Value = stVal
Next cl
End Sub

Pour faire simple, dans les fichiers CSV, seules les URLs nous intéressent. Le reste ne nous concerne pas.

Nettoyage du CSV

Ci-dessus, un exemple de fichier CSV généré par Scraperr.com. En rouge, ce qu’il faut supprimer.

Très important : ne vous amusez surtout pas à trier ou changer l’ordre des URLs ! Mieux vaut prévenir, on ne sait jamais.

Création du fichier d’analyse

Créez un nouveau fichier Excel vierge. La colonne A, que l’on peut intituler « ranking » en A1, déterminera les positions de 1 à 500. Rien de plus simple, on met 1 en A2 et 2 en A3, on sélectionne les deux cases, et on tire le tout jusqu’à la ligne 501, la liste se fait automatiquement de manière incrémentale.
Colonne positions

Copiez-collez ensuite toutes vos listes d’urls préalablement nettoyées dans les colonnes B, C, D, E etc. Gardez la première ligne pour les entêtes, ou vous mettrez par exemple la date du scrap. Ce qui nous donne quelque chose comme ça :

Exemple de tableau

Voilà, on a maintenant un tableau avec plusieurs classements, beaucoup d’urls, mais une lisibilité quasi nulle. Pour faciliter la lecture des fluctuations, et tirer des statistiques dans tous les sens, il faut « renverser » le tableau. Mais attention, ici un tableau croisé dynamique ne suffit pas !

Pour bien faire, il faut à minima :

  • Dresser la liste de toutes les URLs et les dédoublonner.
  • Calculer et afficher la position de chaque URL pour chaque relevé effectué (scrap).
  • Enfin, faire en sorte que ce processus soit totalement automatisé ;-)

La macro VBA qui fait le job

Partons du principe que notre tableau de SERPs soit sur une feuille Excel nommée "Feuil1" (c’est normalement le nom par défaut). Créez (ou renommez) une deuxième feuille et nommez la "tablo" (si vous lui donnez un autre nom, il faudra modifier la macro VBA en conséquence). Faites ensuite un clic droit sur l’onglet de la feuille « tablo », puis cliquez sur « Visualiser le code » comme l’exemple ci-dessous :

Intégration macro VBA

Dans la fenêtre qui s’est ouverte, copiez-collez ensuite le code VBA ci-dessous :

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim Dercol As Integer, i As Integer, j As Integer, Ligne As Integer
Dim data
Dim tablo

Dercol = Sheets("Feuil1").Range("A1").SpecialCells(xlCellTypeLastCell).Column

Range("A2:A65536").ClearContents

    For i = 2 To Dercol
    Ligne = Range("A65536").End(xlUp).Row + 1
        With Sheets("Feuil1")
           .Range(.Cells(2, i), .Cells(501, i)).Copy Range("A" & Ligne)
        End With
    Next i

Set data = CreateObject("Scripting.Dictionary")
tablo = Sheets("TABLO").Range("A2:A" & Range("A65536").End(xlUp).Row)

    For j = 1 To UBound(tablo)
        data.Item(tablo(j, 1)) = data.Item(tablo(j, 1))
    Next j

Range("A2:A" & Range("A65536").End(xlUp).Row).ClearContents
Range("A2", Cells(data.Count + 1, "A")) = Application.Transpose(data.keys)

Ligne = Range("A65536").End(xlUp).Row
Range(Cells(2, 2), Cells(Ligne, Dercol)).FormulaR1C1 = _
            "=IFERROR(INDEX(Feuil1!R1C1:R501C1,MATCH(RC1,Feuil1!R1C:R501C,0)),"""")"
Range(Cells(2, 2), Cells(Ligne, Dercol)) = Range(Cells(2, 2), Cells(Ligne, Dercol)).Value

End Sub

Fermez la fenêtre VBA, ré-affichez la feuille "Feuil1", puis à nouveau « tablo ». Bingo !

Tableau après macro

Il ne vous reste plus qu’à répercuter les entêtes (dates). Notre tableau est maintenant beaucoup plus lisible, mais nécessite encore un peu de représentations graphiques, de colorations, et de courbes pour en faciliter encore plus la lecture et l’analyse. A partir de là, ça devient freestyle. Les plus expérimentés pourront même mettre en place un tableau de bord complexe, avec KPI et tout le tremblement. A mon modeste niveau, je ne vous donnerai que quelques pistes qui vous permettront déjà d’avoir un résultat bien exploitable.

Visualisation graphique, courbes, sparklines, et tableaux de bord

Mise en forme conditionnelle

On souhaite par exemple mettre en exergue les bonnes positions en colorant les cellules en vert, les mauvaises en rouge, et le ventre mou en jaune.
On sélectionne la plage des positions, puis on applique une mise en forme conditionnelle > nuance de couleurs > puis sélectionnez le dégradé rouge-jaune-vert (et pas l’inverse !) :
Mise en forme conditionnelle
Ca devient déjà un peu plus sexy, mais d’un point de vue SEO, la zone verte est carrément trop large car basée sur une échelle de 500 positions. On voudrait donc qu’elle se cantonne à la première page, soit le top 10. On re-sélectionne la plage des positions, puis de nouveau « mise en forme conditionnelle », puis « gérer les règles… ». Sur la boite de dialogue qui apparaît, cliquez ensuite sur « modifier la règle », puis modifiez la règle pour le milieu en changeant « Centile » par « Nombre », et indiquez comme valeur juste en dessous "10", puis Ok > Ok. Les positions acquises en première page sont maintenant beaucoup plus visibles. On peut bien sûr affiner et faire d’autres règles beaucoup plus fines. A vous de jouer.

Mise en forme conditionnelle

Sparklines for Excel (SfE)

Excel possède en natif des outils graphiques sparklines (dans le menu insertion), mais il ne sera pas utilisé car inadapté : par exemple la 500ème position est pour lui la plus élevée, et les courbes se retrouvent complètement inversées.
Mais il existe un plugin Sparklines for Excel (SfE) beaucoup plus puissant et plus souple que celui de Microsoft, et c’est bien entendu celui-ci que nous allons utiliser pour enrichir notre tableau de bord.

Sparklines for Excel

L’objectif ici est d’associer à chaque URL sa courbe de positionnement dans les SERPs. Il faut dans un 1er temps installer le « plugin » SfE. Après l’avoir téléchargé ici, vous pouvez l’activer de manière temporaire en l’ouvrant avec Excel, ou de manière permanente, en allant dans les options d’Excel (2010) > Compléments, puis dans la partie de droite en bas Gérer : « Compléments Excel » > Atteindre… Une nouvelle boîte de dialogue apparaît, cliquez sur parcourir, et choisissez le fichier fraîchement téléchargé. Vérifiez ensuite qu’il soit bien coché, puis validez le tout. Vous avez maintenant un nouvel onglet « Sparklines » permanent.

A droite des relevés de positions, sélectionnez la première cellule juste à côté de la première ligne (dans mon illustration, G2). Dans l’onglet/ruban Sparklines, sélectionnez l’outil « Line ». Un écran pour renseigner les arguments de fonction apparaît. Remplissez le comme suit :
Arguments de fonction

  1. Points1 (requis) : la plage de données de la première ligne. Dans mon exemple : B2:F2.
  2. HLine (optionnel) : si vous mettez la valeur "10" par exemple, une ligne rouge horizontale matérialisera la 10ème position sur les graphiques sparkline.
  3. MinZone (optionnel) : cela permet de colorer en gris la zone des 10 premières positions. Mettez "1" comme valeur minimale.
  4. MaxZone (optionnel) : mettez 10 pour matérialiser la 1ère page de Google.
  5. Tags : ajoute des points pour chaque position avec changement de couleur pour les minima/maxima. Mettez ici la valeur "6".
  6. VerticalFlip (très important) : mettez "1". Cela renverse la courbe afin d’avoir la position 1 au top, et 500 tout en bas.

Soit la formule suivante (sans élément optionnel) : =linechart(B2:F2;;;;;;;6;;;;;;;;;;;1)
Validez, puis étendez la formule à l’ensemble de la colonne. A savoir : Aucune courbe ne sera affichée si les positions de l’url sont toutes identiques, ce qui arrive bien souvent dans les tops positions. Ne soyez donc pas surpris lors de la création de la formule sur la première ligne si elle reste vide.

Présentation avec sparklines

Superpositions des courbes

On peut encore apporter un autre degré de lecture en superposant les courbes, et c’est très simple. Sélectionnez à nouveau une plage de positions en vous arrêtant vers la 20ème position et en prenant en compte les entêtes comme le montre le screenshot ci-dessous. Dans le ruban « insertion », cliquez ensuite sur « Ligne » (graphique) et sélectionnez « courbes avec marques ».

Sélection du top 20

Vous obtenez alors un suberbe graphique, mais hélas, complètement inversé. Renversez-le de la manière suivante : veillez à ce que le graphique soit sélectionné. Dans le ruban « Disposition » des outils de graphique, cliquez sur « Axes » > « axe vertical principal » > « Autres options de l’axe vertical principal… ». Dans la boite de dialogue « Format de l’axe », cochez « valeurs en ordre inverse » et voilà !

Fluctuation des positions dans le top 20

Si vous avez un peu d’imagination, vous pourrez aller beaucoup plus loin avec SfE, par exemple mettre en place des top 10 des plus grosses gamelles, les meilleures percées etc. Les possibilités sont infinies ;-)

Vous pouvez avoir un lien en DoFollow si :
  • Vous ne faites pas de lien optimisé (brand ok).
  • Votre contribution apporte de l'eau au moulin et ne se contente pas de remercier (même si c'est toujours appréciable).
  • Vous ne donnez pas l'impression de ne pas avoir lu l'article.
  • Votre site doit graviter dans l'univers du SEO / web marketing / IT.
  • Nouveau : Se suivre mutuellement sur Twitter (oui ça fait copinage, et j'assume !).
Le but n'étant pas d'être plus sévère, mais au contraire plus équitable et... naturel. N'oubliez-pas : moins de spamco = meilleur jus !

21 réflexions au sujet de « Monitorez les SERPs sans dépenser un rond (tutorial) »

  • Encore une fois un très bon article qui donne à la fois un tableau de suivi SEO et permet d’avoir un petit tuto sur les possibilité d’excel pour le référencement, qui est trop souvent sous estimé à mon goût.

    Dans le même genre pour les plus fainéants il y a une extension chrome (SERPTrends SEO Extension) qui permet de visualiser directement les changements de position de chaque résultat pour les requêtes effectuées. Les variations changent à chaque fois que l’on retape la requête.

  • Excellent article !! Comme toi je suis un inconditionnel d’Excel et j’utilise une macro assez proche pour relever mes positions tous les jours. J’ai un article en préparation qui viendra compléter à merveille celui-ci.

    Pour ma part, je n’utilise pas de proxies, mais je scinde le tout en plusieurs fichiers que je lance via une tâche automatisée par intervalle de deux heures sur mon VPS. Certes cela implique d’investir dans un VPS (à condition qu’on puisse parler d’investissement pour 1,99€ par mois chez OVH), mais cela présente l’avantage de pouvoir récupérer des données tous les jours pendant des semaines et des semaines (en congés par exemple) sans avoir à toucher à quoique ce soit.

    Je vais aller jeter un œil à Scraperr.com que je ne connaissais pas. Et merci pour le petit hack pour le passer en .fr.

  • Bonjour et merci pour cet article fort instructif.

    Evidemment, c’est un petit peu plus fastidieux qu’un outil maison qui fait tout le travail et qui génère lui-même des fichiers excel propres, mais c’est une solution gratuite et très utile à titre personnel et ponctuel.

  • Sinon y a un petit outils très sympa qui fonctionne sur WordPress.

    Découvert il y a peu je l’ai testé et il permet de voir le positionnement de nos pages sur nos mots clés mais aussi d’avoir une vue sur le positionnement de la concurrence. Bref un Ranks mais à 13$ sans abonnement et vraiment très simple a utiliser.

    D’ailleurs tellement simple et pas cher que je prévois de migrer dessus a la place de Ranks (vu ce que ça nous coute…).

    L’outils en question s’appelle Rankie , sous forme de plugin WordPress il ne coûte que 13$ mais n’est activable que sur un seul site par licence. Hormis cela il permet d’avoir un bonne visibilité de vos positionnement dans les SERPs et de surveiller la concurrence. propose des graphiques la progressions de vos mots clé etc etc…

    Pour ma part il est adopté et je l’utilise pour mes comparateurs.
    Surtout que même si il est lié a un site vous pouvez très bien vous en servir pour monitorer tous vos autres sites sans avoir besoin d’acheter de licences supplémentaire.

    Enfin bref voila ce que j’ai trouver de mon coté et que j’utilise maintenant :)

    Charles

  • Merci pour ce chouette cadeau ! Oui c’est un cadeau car aujourd’hui pour faire tout ça il existe, des outils mais cela coûte une blinde. Je ne connaissais pas Scrapperr mais il m’a l’air bien pratique. Va t-il rester gratuit ? Ce tuto me semble super intéressant pour Excel. Le résultat à l’air très lisible pour analyser la concurrence. (De qui faut-il se méfier, comment se comporte notre site dans les serps, etc.) Si l’on doit effectuer ceci pour plusieurs mots clés, Scrapperr reste sans limite de requêtes ? Je pense qu’il faudrait également recueillir d’autres données comme les indicateurs popularité (DA, PR, MozRank…) de chaque urls pour une analyse encore plus poussée. Cette méthode reste tout de même très efficace pour effectuer sa veille dans les serps et être au courant des changements de positions quotidiennement.

  • J’ai commencé a lire l’article sans reconnaitre ton blog. Plus je lisais et plus je me disais que c’était vraiment fort! Les résultats semblent tops. Bravo pour le partage de cette bonne pratique. A bientôt

  • Voilà une astuce qui dépote ! Après c’est sûr qu’on ne peut pas utiliser ce procédé pour suivre des centaines de positions, mais à mettre en place sur les top mots-clés, c’est vraiment intéressant ! Je vais tester ça dès demain, merci Aurélien pour ce genre de partage qui ce fait rare dans le monde du SEO de nos jours…

  • Excellent comme tuto pour moi qui suis fauché :), mais je vais essayer de pousser le concept plus loin : je vais essayer de la mettre en pratique sur LibreOffice…. :)

    Treve de plaisanterie, merci pour le partage.

  • Excellent article, comme d’hab!
    « Parfois scraperr.com génère des .CSV bien crades, des fois non… » => j’ai développé un scrapper en PHP et l’encodage des caractères dans les SERP c’est un gros bourbier. J’ai pas mal galéré pour réussir

    PS : elle déchire Armelle compte pour positionner 123rachatcredit.fr comme ça ^^

  • Un super article comme je les aime, qui a du te demander des plombes à écrire (la joie de faire la copie d’écran parfaite).

    Par contre je trouve ça dommage car cette solution est limitée à un seul mot clé, ideal donc pour se faire une météo mais fastidieux pour suivre une vraie évolution.

    Mais bon…ça reste gratuit et excellent à gérer et les possibilités sont énormes. Merci aussi pour l’addon excel, je connaissais pas celui là et il va m’être bien utile !

  • @Vincent : Le plus fastidieux n’a pas été les screenshots mais la macro qui « fait le job » ;-)

    Certes cette technique ne peut porter que sur un mot clé, et multiplier les mots clés revient à multiplier les processus. Mais il ne faut pas voir cette technique comme une alternative aux suivis de positions classiques (ranks, myposeo etc), surtout pour des SEO qui doivent suivre bcp de projets/KW.

    Merci à tous les autres !

  • L’article est très complet :) J’ai eu un petit soucis lors de l’insertion du code dans la feuille « tablo ». Une erreur d’exécution s’est affiché et dans le code, j’ai une erreur sur cette ligne :

    « Range(« A2 », Cells(data.Count + 1, « A »)) = Application.Transpose(data.keys) »

    Je ne pas ou est le problème donc si quelqu’un a une petite solution :) Merci à tous !

  • Hé bien…super article ! Beau travail ! C’est un très bon outil pour commencer et apprendre à faire par soi même.

    Je vais présenter ce tuto à un ami qui bosse dans une petite agence web pour se faire la main, ça devrait lui plaire.

  • Bonjour,

    J’aimerais beaucoup essayer ce tutoriel mais je rencontre un problème au niveau de scraperr.com, lorsque je lance une requête et que je télécharge le fichier .csv, ce dernier est vide (j’ai seulement quelques infos générales sur la requête, l’user agent, le proxy etc…). J’ai essayé avec chrome et mozilla, mais c’est toujours la même chose.

    Est-ce un bug de scraperr.com ou est-ce que je m’y prend mal?

  • Pour ceux que le sujet intéresse encore (suivi gratos de SERP), sachez que dans la nouvelle version de scrapebox (v2), vous pouvez récupérer dans un fichier texte le résultat du scrapping de vos mots-clé.
    Par exemple, j’ai récupéré les 100 premières URL de 100 mots-clé qui m’intéressaient, en quelques secondes.

    Ainsi, vous pouvez monitorer facilement de nombreux mots-clé.

    Bien sûr, pour que votre IP ne soit pas grillées par Google, il vous des proxies ou un VPN …

Les commentaires sont fermés.