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.

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.

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

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.

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

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.

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 :

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 :

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 !

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 !) :

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.

Sparklines for Excel (SfE)

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 :

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.

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 ».

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à !

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 ;-)