Toi aussi, scrape avec Excel !

Préambule : ceci est le premier article d’une série consacrée à Excel pour le SEO. Alors Stay Tuned !

Nous avons tous eu besoin un jour ou l’autre de scraper les résultats de Google, que ce soit pour un audit, un suivi de positionnement ou tout un tas d’autres choses. Il existe aujourd’hui sur le marché de très nombreuses solutions pour récupérer les résultats des pages de recherche. Je pense en premier lieu à l’excellent RDDZ Scraper, que j’utilise très souvent, mais il y en a d’autres. Le souci (mon souci) c’est que l’analyse s’effectue systématiquement sur Excel. Il faut donc exporter le résultat en CSV, l’importer dans Excel, le formater, bref …

Je vous propose en bas de cet article un document Excel que je mets à disposition gratuitement et qui va vous permettre de scraper directement dans Excel. Mais ne sautez pas les étapes et lisez bien les paragraphes ci-dessous 😉

Edit 24/09/14 – 10:15 : la version 1.5 contenait un bug, pensez à télécharger la dernière version (1.6).

J’ai besoin de quoi pour scraper directement dans Excel ?

Il vous faut bien sûr une version d’Excel pas trop ancienne si possible. Je n’ai pas testé, mais disons que 2003 est le strict minimum. Ensuite, il y a un outil incontournable en SEO lorsque l’on travaille sur Excel, c’est le plugin SEO Tools for Excel, développé par le Suédois Niels Bosma. Il va donc falloir commencer par installer ce plugin. Pour ce faire, rendez-vous sur cette page http://nielsbosma.se/projects/seotools/download/ et suivez les instructions. Attention, vérifiez bien votre version de Microsoft Excel avant de télécharger le plugin (32-bit ou 64-bit). Inutile de préciser que cette extension est entièrement gratuite, alors même si vous galérez un petit quart d’heure pour l’installer, ne vous en faites pas, ce sera vite rentabilisé.

Si vous disposez d’un compte chez Majestic, alors c’est encore mieux. Vous pourrez obtenir les données de Majestic SEO (notamment les Flow Metrics, le nombre de liens entrants et de referers) directement dans Excel. Vous trouverez comment faire sur cette page : Accéder à Majestic SEO dans Excel. Et si vous n’avez pas de compte Majestic SEO, je vous recommande vivement l’offre SILVER à 39,99€ qui sera suffisante.

Ce que vous allez pouvoir faire

Vous allez pouvoir obtenir la liste des 100 premières URLs et noms de domaines dans les SERPs pour une requête donnée, récupérer le title, le TBPR, l’Alexa Traffic Rank, le nombre de domaines référents, le nombre de backlinks, le nombre d’URLs indexées et les Flow Metrics.

Google Scraper — Aperçu du résultat pour un scrap complet des deux premières pages des SERPs sur la requête « Fleuriste Paimpol »

Pour ce faire c’est simple, vous allez :

entrer votre requête (un mot-clé, un footprint)
sélectionner un nombre de pages à scraper (entre 1 et 10)
choisir si vous souhaitez récupérer le title
déterminer si vous souhaitez analyser l’URL ou le domaine racine
choisir si vous souhaitez afficher les données Majestic SEO (nécessite un compte payant Majestic SEO)
choisir si vous souhaitez obtenir le TBPR et l’Alexa Traffic Rank
sélectionner une localité

La prise en compte de la localisation

C’est le petit plus du document. Vous allez pouvoir directement choisir votre localisation et expliquer à votre client qu’il n’est peut-être que 84e sur « fleuriste Paimpol » au niveau national comme lui l’indique son outil de suivi de positionnement, mais qu’il apparaît bien en 2e position lorsque l’internaute est situé à Paimpol, Lannion ou Guingamp (#BreizhPower). Merci à Kévin pour son article Géolocaliser ses SERPs à l’échelle ultra-locale dont je me suis beaucoup servi pour ajouter cette fonctionnalité.

NB : si vous ne souhaitez pas utiliser cette fonctionnalité, remontez en haut de la liste déroulante et laissez le champ vide.

Téléchargez le document

Je vous l’avais promis. Voici le document que j’ai appelé Google Scraper (version 2.1).

Edit 24/09/14 – 10:15 : la version 1.5 contenait un bug, pensez à télécharger la dernière version.

Edit 12/05/16 – 10:00 : la version 2.0 affiche désormais la description présente dans les SERPs.

Edit 06/06/18 – 15:20 : la version 2.1 contient la nouvelle version du connecteur Majestic et ne contient plus les données PR et Alexa.

Quelques consignes au passage :

Soyez patients, si vous cochez toutes les cases et que vous souhaitez récupérer 10 pages de résultats, ça peut prendre quelques minutes.
Récupérer les 100 premiers résultats n’est pas toujours utile. Si vous pouvez vous contenter des 20 premiers c’est mieux.
Un conseil, dans Excel, choisissez de lancer les calculs manuellement (Onglet Formules | Section Options de calcul | Manuel). Et appuyer sur F9 lorsque vous voulez lancer le scrap.
Ici on scrape sans proxy, donc l’outil n’est pas forcément adapté à une utilisation industrielle, cela dit, vous pouvez bosser sur l’audit concurrentiel de votre client les yeux fermés.
Pour les initiés, vous pouvez modifier le Xpath en colonne C (attention, il s’agit d’une formule matricielle)
Edit 24/09/14 – 10:15 : les lignes blanches qui apparaissent parfois correspondent aux résultats Google Image que j’ai pas (encore) réussi à exclure.
Edit 25/01/17 – 09:45 : si cela ne fonctionne pas, rendez-vous dans l’onglet Settings de SEO Tools, puis cliquez sur Global HTTP Settings. Dans le premier tableau, renseignez le User-agent suivant : Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36 puis cochez Save configuration en bas à droite et sauvegardez.

La suite du dossier Excel pour le SEO

Besoin de noms de domaines expirés ? Testez PBN Premium

Sur ce blog, je me suis fixé des règles très strictes : au moins un article par an. Voici donc celui de 2017 🙂

Cela ne vous a sans doute pas échappé si vous suivez un minimum la sphère SEO sur Twitter, il y a un outil qui fait le buzz depuis quelques semaines : PBN Premium. Après avoir lancé le tool discrètement en Février, notre ami NinjaLinker nous a dévoilé il y a quelques jours qu’il était à l’origine de ce projet. Je vais donc commencer par le féliciter : bravo David et chapeau, car comme c’est déjà le cas avec SEObserver, nous avons à nouveau ici un tool pour les SEO pensé par un SEO … et ça change tout !

Un domaine expiré ? Pour quoi faire ?

Ce n’est pas un scoop, la recherche de noms de domaines expirés est à la fois coûteuse et fastidieuse pour de nombreux SEO. Pourtant les expirés sont encore aujourd’hui la base de nombreuses stratégies SEO. Il était donc temps de disposer d’un vrai outil capable de répondre à nos besoins, nous les pros du SEO 😀

Qu’attend-on d’un bon nom de domaine expiré ?

on souhaite que celui-ci ait des metrics intéressantes
de plus en plus, on cherche à ce qu’il corresponde à une thématique bien précise
on veut s’assurer n’ait pas été spammé durant ses vies antérieures
on cherche à éviter les enchères incontrôlables et à maîtriser ses coûts.

Et c’est justement là la force de PBN Premium puisque l’outil nous donne accès à toutes ces infos en un coup d’œil. On a accès à une liste de plus de 175 000 noms de domaine expirés français et internationaux (manifestement un travail important est fait pour limiter le spam). Grâce au dashboard, on dispose de toutes les metrics concernant le NDD sélectionné :

les données de Moz (Domain Authority et Page Authority)
les metrics de Majestic : TF, CF, ratio TF/CF, nombre de domaines référents, nombre de backlinks, mais aussi et surtout les principaux « topicals », les principales ancres, le top 5 des domaines référents, sans oublier le graph de Majestic qui, quand on a un peu l’habitude, peut presque suffire à juger de la qualité d’un profil de liens
des captures de WebArchives pour visualiser en un clic le passif du nom de domaine

PBN Premium, la puissance des filtres

Moi qui suis un adepte d’Excel (tiens première nouvelle !!), j’ai besoin de pouvoir filtrer la data dans tous les sens. Et c’est vraiment quelque chose qui fonctionne très bien sur PBN Premium. Le tool dispose en effet d’un grand nombre de filtres : certains que l’on a l’habitude de voir (RD, TF, CF, DA, etc.) et d’autres beaucoup plus novateurs (et ultra pertinents) tels que le filtre par langue, le filtre par topical, par TLD ou encore la possibilité de rechercher un mot-clé dans les ancres ou dans le nom de domaine.

Bref, tout ce qu’il faut pour être ultra efficace et sélectionner les bons noms de domaine, en toute connaissance de cause.

Plusieurs offres au choix

Vous l’aurez compris, sur PBN Premium, on n’achète pas de nom de domaine, mais exclusivement de la data. L’énorme avantage : on paie les NDD choisis au prix du registrar (soit par exemple 5,49€ en ce moment chez OVH pour un .fr).

Pour accéder au service, deux solutions :

L’offre Freemium (à partir de $19) : ici on achète des crédit pour accéder à la data des NDD que l’on souhaite. Une bonne manière de faire connaissance avec le tool et de répondre aux besoins des SEO qui ne nécessitent qu’un ou deux expirés, de temps en temps.
Une offre Premium « open bar » ($190) grâce à laquelle on accède à l’ensemble des domaines, à volonté. Parfait pour ne rater aucune perle et pour naviguer facilement sur le site, sans limite. Super intéressant quand on a des besoins fréquents et/ou importants en NDD expirés.

Et maintenant le petit cadeau

Voici un code promo gentiment offert par NinjaLinker et valable jusqu’au 30 juin 2017, pour tester le service :

100CNinja50

L’occasion de profiter d’un pack de 100 crédits à $9,50 au lieu de 19$. Go Go Go !!

Collectez automatiquement les données Search Console avec Zennoposter

[Le dernier article sur ce blog datait d’Octobre 2014, je crois qu’il était temps de s’y remettre !]

S’il y a bien quelque chose que j’ai du mal à supporter en SEO, c’est le fait de perdre des données. Or comme vous le savez tous, dans notre cher outil Google Search Console, les données liées à l’analyse de la recherche (impressions, clics, CTR et positions) ne sont plus accessibles passé un délai de 90 jours. Alors OK les mots-clés c’est has been, le SEO a beaucoup évolué, bla bla bla. Mais au quotidien, quel SEO peut aujourd’hui s’affranchir de démarrer un audit ou un accompagnement SEO sans s’attarder un minimum sur les keywords ?

Télécharger les données tous les mois

Donc si l’on ne veut pas ‘perdre’ ces données, les options sont limitées. Il faut aller régulièrement télécharger le fichier CSV contenant ces informations … site par site, compte par compte. Si vous gérez 3 sites, aucun problème. Si vous êtes éditeur de sites, consultant SEO ou agence, vous gérez peut-être 30 sites, 100 sites, 1000 sites ! Bref, inimaginable de devoir passer 4 heures à télécharger ces données tous les mois.

ZennoPoster mais quelle bonne idée

Il y a environ 6 mois (avec les copains Arnaud, Régis, Annick et Luc) j’ai suivi une formation ZennoPoster [<– this is not an affiliate link] assurée par Seoxis. Dans cet article, je vous passe les atouts et la puissance de l’outil, j’espère y revenir dans un article dédié. Bref, ZennoPoster est l’outil idéal pour ce genre de tâche répétitive qui demande par ailleurs un planning d’exécution.

J’ai donc construit un template qui, chaque 1er jour du mois, va aller télécharger les données du mois précédent pour tous les sites présents dans Search Console. On obtient ensuite un dossier par domaine et pour chaque domaine, un fichier CSV contenant les données de recherche :

À vous ensuite d’utiliser ces données à bon escient. Et dans ce cas vous le savez : Excel est votre ami.

Utilisation du template

Pour utiliser le template, rien de plus simple. Ajoutez-le dans ZennoPoster. Cliquez droit pour modifier les paramètres (Settings). Entrez vos login/password Google.

Vous avez ensuite le choix entre deux modes :

Manuel : sélectionnez le mode manuel si vous voulez extraire ponctuellement les data pour une période donnée. Ex ci-dessous, vous souhaitez récupérer les données pour la période du 1er au 12 avril 2016. Cliquez ensuite sur OK et exécuter votre template. Checkez les logs. C’est terminé.
Automatique : sélectionnez le mode automatique si vous souhaitez extraire les données chaque mois de façon automatique. Dans ce cas, vous pouvez laisser les champs de date vides. Cliquez sur OK. Il vous faut maintenant planifier l’exécution du template. Celui-ci sera exécuté une fois par jour, mais n’ira télécharger les données que si nous sommes le 1er jour du mois.

The end

Je suis encore un noob sur ZennoPoster, donc soyez indulgents, le template pourrait certainement être optimisé.

Pourquoi je vous le donne ici alors que j’ai passé des heures à le construire ? D’une part parce que je crois dur comme au fer au partage au sein de la communauté SEO. C’est grâce à ceux qui partagent ou ont partagé que j’ai pu avancer (et je sais que je ne suis pas le seul). D’autre part parce que ce serait dommage que je sois le seul à pouvoir en profiter.

Voici donc le template à télécharger : ZennoPoster – Search Console Extractor.

Comme toujours, vos retours sont les bienvenus.

Save The Date

Je profite de cet article pour vous rappeler deux événements SEO à ne pas rater dans les jours et les semaines qui viennent. Tout d’abord le Google Black Day dont la 3e édition aura lieu le samedi 23 avril. Rappel pour ceux qui aurait raté les deux premières éditions : il s’agit de conférences en ligne entièrement gratuites. Vous pouvez d’ailleurs retrouver les vidéos des éditions précédentes sur la chaîne Youtube du Black Labo. Pour cette édition, nous aurons le plaisir d’écouter Jaffaar Saleh, Julien Gandaho, Pierrick Buty, Manuel Cebrian et David Dragesco. Je vous laisse le soin d’aller jeter un oeil au programme ici. Et pour vous s’inscrire c’est par là.

L’autre événement à ne pas rater c’est bien entendu le WebCampDay d’Angers organisé par l’ami Nicolas et qui aura lieu le vendredi 13 Mai (paranoiaques s’abstenir). Toutes les infos sont disponibles sur webcampday.com. Les inscriptions sont ouvertes depuis quelques jours (voir ici) et à 25€ la place, impossible de passer à côté.

Cheat sheet : les fonctions Excel à connaître pour le SEO

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

On utilise chaque jour de nombreux logiciels pour nous faciliter la vie, nous les SEO, mais finalement, celui qui revient le plus souvent pour la plupart d’entre nous c’est Excel ! Pourquoi ? Tout simplement parce que c’est là qu’on va venir coller nos exports Analytics, Xenu, Screaming Frog ou Majestic, nos scraps, la liste de mots-clés de nos clients, la liste des BL de nos concurrents à désavouer (#joke), etc. etc.

Et lorsqu’on ne maîtrise pas l’outil correctement, on perd du temps. Je vais donc commencer par vous recommander ce très bon article chez distilled.net (en anglais). L’article, très complet, reprend bien plus que les fonctions utiles au SEO. Il explique comment utiliser la recherchev, la concaténation, et ainsi de suite => mustread !

Le document que je vous propose aujourd’hui est bien moins complet, mais beaucoup plus simple. Voici ce que vous y trouverez.

Que contient le document ?

Le fichier Excel présente 28 fonctions Excel regroupées en 6 catégories. A partir d’une URL, d’un nom de domaine et/ou d’un mot-clé, ces 28 fonctions vous donneront :

Le nombre de résultats dans Google
Le nombre de pages indexées sur Google
Le Toolbar PageRank
Le Trust Flow
Le Citation Flow
Le nombre de referers
Le nombre de backlinks
Le nombre d’occurrences d’un mot sur la page
Le Title
Le H1
L’URL canonique
Le nombre de liens sur la page
Le code HTTP
Si le code HTTP est 301, l’adresse de redirection
La meta description
La meta Robots
La présence du mot-clé dans l’URL
L’Alexa Trafic Rank
L’adresse IP
Le pays
La date de création du whois
Le nombre d’interactions Facebook
Le nombre de Google +1
Le nombre de tweets
Le sous-domaine
Le domaine
Le tld
Le path

Je précise une dernière fois qu’il vous faudra un accès à l’API Majestic pour obtenir les Flow Metrics et bien entendu l’extension SEO Tools for Excel pour la totalité des autres résultats. Autre précision, pour les Flow Metrics de Majestic, entrez ndd.fr pour obtenir les données liées au domaine racine et http://ndd.fr/article pour les données liées à l’URL.

Télécharger le fichier Excel

Voici le document, en téléchargement libre comme toujours : SEO-Excel Cheat Sheet – v.1.0.

Pour aller plus loin : je vous recommande également cette méthode pour apprendre à supprimer les doublons dans Excel. Et bientôt je vous expliquerai comment recouper facilement deux bases de données !

Le dossier complet Excel pour le SEO

Pour aller plus loin

Maîtriser Excel pour le SEO, c’est bien, c’est même indispensable la plupart du temps. Mais bien entendu cela ne suffit pas. Nous avons la chance en France de disposer de très bon formateurs SEO, c’est la raison pour laquelle je vous encourage vivement à suivre cette formation Trafic Google ! Elle vaut le détour je vous le garantis !!

Comment monitorer les SERPs avec Excel ?

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

Le document que je vous propose aujourd’hui va vous permettre de suivre l’évolution des SERPs sur un mot-clé donné. Il s’agit bien entendu d’une utilisation « artisanale » avec Excel, mais suffisamment automatisée pour que ce ne soit pas une usine à gaz. Si vous cherchez un outil plus pointu, je vous invite sans tarder à découvrir cet outil. Finalement, ce qu’on va faire ici, c’est un peu ce que nous avait proposé Aurélien il y a quelques semaines, mais d’une manière un peu différente.

Et bien sûr, si tout cela vous dépasse, alors vous pouvez toujours consulter le classement des consultants SEO ou bien même faire appel à bon consultant Adwords pour booster votre visibilité sur Google.

La check list

Je ne vais pas me répéter à chaque article. Alors pour résumer, il vous faudra une version d’Excel récente et avoir activé les macros.

Edit 15/10/14 – 13:30 : Important, j’oubliais : vous aurez également besoin du plugin SEO Tools for Excel qu’on ne présente plus. Si vous ne l’avez pas, je vous invite à relire ceci.

Le tutoriel

Voici comment se présente l’outil (pour l’exemple, j’ai repris le mot-clé du concours Auvairniton Bourgrire et j’ai adapté quelques données) :

un onglet de paramétrage dans lequel vous renseignez
- le mot-clé en question
- le nombre de pages à scraper (10 par défaut, mais c’est souvent inutile de dépasser 5 voire 3)
- les domaines à surveiller (attention, j’ai dit les domaines, ne mettez pas d’URL et supprimez éventuellement les www.), 15 au maximum.
Un onglet de détail dans lequel vous retrouvez les positions par jour et par concurrent
Un onglet de consolidation dans lequel vous pouvez obtenir
- un aperçu des SERPs à deux dates données ainsi que les écarts
- un graphique qu’il suffit de mettre à jour en cliquant sur le bouton rouge.

L’utilisation est on ne peut plus simple. Il suffit d’ouvrir le document régulièrement, à l’ouverture le fichier récupérera les données puis se fermera. Ouvrez-le à nouveau pour consulter les données.

A chacun sa méthode, mais en ce qui me concerne, comme pour la plupart des mes documents Excel, j’ai mis en place une tâche automatisée sur Windows qui me permet de récupérer les données chaque jour sans avoir quoique ce soit à faire (j’expliquais la marche à suivre dans cet article). Ce qui permet une analyse fine et évite les erreurs de relevé de position (ex. si vous relevez vos positions une fois par mois, vous prenez le risque de le faire une journée où les SERPs ne sont pas stables ce qui peut fausser l’analyse).

Avez cette méthode, vous aurez du mal à suivre 100 mots-clés, par contre, vous pouvez le faire sans aucun souci pour vos 10/20 expressions clés (ou le top keyword de chacun de vos clients).

NB : ce document n’est réellement intéressant que dans la durée (ou pour déceler facilement les gagnants et les perdants lors de mises à jour d’algorithme – c’est d’actualité paraît-il) alors laissez le tourner une petite semaine voire plus avant d’y revenir (faute de quoi vous ne verrez que de belles droites sur le graphique).

Télécharger le document

Voici donc le document en question : Google Monitor v.1.23.
(NB : si vous avez la version 1.19, pensez à télécharger la nouvelle version qui vient corriger une erreur dans la macro).

Pour supprimer les données existantes, cliquez droit sur n’importe quel onglet. Puis choisissez Afficher / Histo et supprimez les lignes 2 à n.

Le dossier complet Excel pour le SEO

Si tout ceci vous paraît obscure mais si vos besoins sont là, je vous recommande de faire appel à cet excellent développeur web à Lyon ! Vous ne serez pas déçu !

Suivez vos positions dans Google Image avec Excel

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

Cet article sera plus court que les premiers de la série car finalement, le principe est le même que dans l’article précédent. Aujourd’hui, je vais vous mettre à disposition un document Excel qui vous permettra de relever la position de vos images dans Google Images. Je n’ai pas préparé de plaidoyer sur l’importance des images, je dirais simplement qu’elle peuvent parfois apporter un trafic conséquent.

Donc, comme nous l’avais proposé Julien il y a quelques mois sur son blog, nous allons relever la position de nos images. Simplement, ce qui nous intéresse ici, c’est non seulement le positionnement de nos images sur plusieurs mots-clés, plusieurs sites mais aussi et surtout d’en suivre l’évolution.

Le document Excel à télécharger

Sans plus tarder, voici le document que vous allez pouvoir utiliser : Google Image Ranking v.3.0. J’ai volontairement laissé une partie des données pour deux de mes sites afin que vous puissiez voir ce que ça donne. Attention, mots-clés ultra-concurrentiels inside 😉 .

La méthode

Il s’agit d’un classeur Excel contenant des macros. Contrairement au document précédent, celui-ci contient uniquement les données brutes. A vous d’aller créer vos tableaux de bord si vous le souhaitez. Pour l’utiliser, je vous invite à relire une partie de l’article précédent concernant l’activation des macros puis l’éventuelle automatisation du document (que je vous recommande vivement de mettre en place). Ensuite, il vous suffira d’entrer pour chaque onglet :

vos mots-clés en ligne 4
l’URL de votre site en B2.

Lors de la première ouverture de la journée, le document relève les positions, puis se ferme. Vous devez l’ouvrir à nouveau pour consulter les données. Pour lancer la macro manuellement, appuyez sur CTRL+MAJ+U.

Et pour compléter, si vous vous intéressez de près à Google Images, je vous recommande cet excellent article de Razvan Gavrilas, fondateur de Cognitive SEO : How Will Google “Read” & Rank Your Images in the Near Future.

Le dossier complet Excel pour le SEO

Suivez vos positions gratuitement avec Excel

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

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 :

Créez un dossier dans votre explorateur (ex. SERP-Ranking sur le disque F:\)
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)
Placez le fichier SERP-Ranking-1.2.7.xlsm dans ce dossier (surtout pas d’espace dans le nom du fichier)
Placez un raccourci de Microsoft Excel dans ce dossier. Nommez le excel.lnk
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
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 ».

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

Faites indexer vos backlinks en moins de deux minutes !

Créer des liens, c’est bien (enfin, en réalité c’est le mal absolu, mais disons que créer des liens c’est bien). Faire en sorte que Google les prenne en compte, c’est mieux ! Combien de fois suis-je allé me créer un profil sur tel ou tel support pour m’apercevoir deux semaines plus tard que Google n’était toujours pas passé voir la page.

Jusqu’au jour où …

Jusqu’au jour où je suis tombé sur cet excellent article d’investirenslip.fr (le NDD me fait toujours autant marrer) : L’approche Blanche Neige. On savait déjà qu’on pouvait faire indexer ses propres pages en deux clics via Google Webmaster Tools, mais faire indexer des pages sur des sites tiers (avec toute les dérives que l’on peut imaginer), c’était particulièrement astucieux. Dans cet article, l’auteur explique donc comment via une simple redirection 301, il faisait indexer ses backlinks à la pelle. J’ai testé, ça marchait du tonnerre !

Oui mais voilà, je n’ai pas la date exacte de la modification de l’interface Google Webmaster Tools, mais disons que depuis début juin à peu près, ça ne fonctionne plus. Terminée la belle époque où l’on envoyait le spider où on voulait via nos 301. Désormais, Google n’affiche plus le bouton « Envoyer pour indexation » lorsqu’il détecte une redirection. Allez, on va dire que c’est assez logique, on a profité d’une petite faille au début mais c’est terminé.

Terminé ? Pas tout à fait

Il y a encore un moyen de contourner le système. Cela prend un peu plus de temps, mais c’est faisable. Je vous explique comment avec cet exemple : je vais créer mon profil sur Hellocoton. La fiche permet d’obtenir un beau lien dofollow, avec une belle ancre optimisée comme on adore en ce moment.

Et voilà le résultat :

Maintenant, une petite recherche dans les SERPs pour vérifier que la page n’est pas indexée :

Le tutoriel

Pour demander à Google d’indexer cette page, on va maintenant partir de la technique de Noé, d’investirenslip.fr, mais en l’adaptant aux nouvelles contraintes de GWT. Voici les étapes, à réaliser dans l’ordre impérativement :

on crée une nouvelle page à la racine du FTP. Ex. cedricguerin.fr/test
via GWT, on demande à Google d’aller explorer la page /test
une fois explorée, on crée une redirection dans .htaccess de cette page vers la page à indexer (ici donc RedirectPermanent /test http://www.hellocoton.fr/mapage/cyd)
on vérifie que la 301 est bien effective en tapant cedricguerin.fr/test dans le navigateur
lorsque tout est OK, on clique sur « Envoyer pour indexation » en cochant « N’explorer que cette URL »
on patiente deux minutes (à peine en général), et voilà le résultat :

En résumé, on demande à Google d’aller explorer une page mais d’en indexer une autre.

Quelques précisions :

ça ne marche pas à tous les coups, certaines pages ne seront pas indexées quoiqu’il arrive (blocage par le robots.txt, page en noindex voire d’autres cas sans raison apparente).
j’ai remarqué que cela fonctionnait mal, voire pas du tout avec les URLs en .html ou .php (cedricguerin.fr/test marchera mieux que cedricguerin.fr/test.html ou cedricguerin.fr/test.php) Une simple réécriture d’URL permettant de virer l’extension augmente les chances de réussite.
on peut utiliser n’importe quel site pour effectuer la 301, il suffit d’en être propriétaire et d’avoir un accès à GWT.

Auvairniton Bourgrire : un point sur le concours SEO 2014

Rappelez-vous, fin 2013, le forum RankSEO lançait son premier concours SEO : Dromardennes. Concours auquel j’avais eu plaisir à participer et pour lequel j’avais livré une grande partie de ma stratégie dans cet article. Une expérience enrichissante pour tous les participants et une rampe de lancement parfaite pour le concours SEO 2014. En ce qui me concerne, je n’avais pas vraiment le choix, je devais remettre mon titre en jeu ! Cette année, la tâche s’est avérée particulièrement ardue. D’une part, Bruno, l’arbitre, avait d’ores et déjà annoncé quelques semaines à l’avance, que le mot-clé serait en réalité composé de plusieurs mots (difficile donc de prévoir du contenu à l’avance … même si finalement certains ont relevé le défi haut la main) et d’autre part, la période du concours collait assez mal avec mon planning (je partais 10 jours en vacances deux jours après le lancement du concours). Mais qu’à cela ne tienne !

Ce que j’avais prévu de faire

Les concours sont faits pour tester, bien plus que pour savoir qui a la plus grosse. Autrement dit, hors de question pour moi de répéter la stratégie (pourtant payante) mise en place lors du précédent concours, à savoir un vrai concept avec du contenu préparé à l’avance et un produit « buzzable ». Cette année, je voulais tester un maximum de choses et surtout en profiter pour monter en compétences dans des domaines que je maîtrisais mal voire pas du tout.

Le concours Auvairniton Bourgrire devait donc me permettre de me former sur :

le content spinning
le cloaking
un logiciel d’automatisation de création de backlinks (type Xrumer, Senuke, mais en l’occurrence j’en avais choisi un autre)

J’avais également pressenti que nous serions plus de participants que lors de l’édition précédente, que la portée du concours serait naturellement plus grande et que par conséquent les actions menées seraient trackées, analysées …. puis copiées. Il fallait donc absolument :

brouiller les pistes pour éviter de subir dès le départ une campagne de NSEO
masquer certains liens pour éviter de griller des plans

Voici donc résumé en quelques points ce que j’avais prévu :

acheter un EMD : auvairniton-bourgrire.fr
y placer une page html optimisée et cloker le contenu
créer des liens en bourrinant un peu sur les ancres
cloaker les liens dans la mesure du possible
faire ranker ce site du mieux possible, puis faire une 301 quelques semaines avant la fin vers mon domaine principal
sur mon domaine principal (breizhpower.fr), monter puis cloaker le WordPress complet
placer un article optimisé en Home Page
créer du contenu spinné pour le blog avec trois articles par jour
bloquer l’indexation du site le plus longtemps possible et miser sur l’effet « prime de fraîcheur » quelques jours avant la fin du concours
créer une linkwheel avec des liens sur lesquels j’avais la main (blog, CPs, etc.)
acheter deux domaines expirés sur la même thématique (Bretagne en l’occurrence) et y placer un peu de contenu
créer des liens vers ces domaines expirés via un logiciel automatisation de création de backlinks

Ce qui s’est réellement passé

Eh bien finalement, tout s’est quasiment passé comme prévu. J’ai réussi à me cacher pendant près de 6 semaines. Seuls quelques amis qui ont accepté de soutenir Breizh Power avaient connaissance de mon domaine pour le concours. Un point positif puisque j’ai finalement été le seul à ne pas me faire pomper mon contenu. J’ai réussi à cloaker mes sites de sorte que personne n’ait la moindre idée de ce qui se cachait derrière et fin juin, le site comptait plus de 160 pages indexées, dont 159 pages spinnées.

Comme prévu, courant juin, j’ai redirigé mon EMD (qui rankait 10e) vers mon domaine principal sur lequel j’ai fini par lever le noindex.

Pourquoi ça n’a pas marché

Si tout s’est passé comme prévu et que je n’ai ni gagné, ni même réussi à me positionner en première page, c’est que la stratégie de départ était bancale. Effectivement, en relisant les 12 points de mon plan de route et en regardant au fur et à mesure ce que faisaient les copains, je me suis vite rendu compte d’une lacune. J’avais zappé les backlinks (ou presque). J’avais bien prévu de créer des liens en automatique vers mes domaines expirés, mais pas réellement vers mon domaine principal, mis à part une petite linkwheel créée avec une poignée de sites.

Lors du dernier concours, j’avais misé sur le marketing !

J’avais un vrai concept, un produit intéressant (fictif certes, mais qui existait en ligne), un site web avec du contenu, des personnages et une histoire. Autrement dit, de quoi aller vanter tout ça sur des annuaires, des blogs, les réseaux sociaux, etc etc. Cette année, tous mes domaines affichaient pour seul et unique contenu le logo Breizh Power sur fond blanc. Impossible donc d’aller créer des liens qualitatifs ou même simplement des liens dans des annuaires.

Et surtout, j’ai passé tellement de temps à appréhender le logiciel que j’avais sélectionné pour l’automatisation, à apprendre les rouages du content spinning et à me casser les dents sur la manière de cloaker un WordPress, que j’en ai oublié de bosser sur mon site. Bilan, là où mes concurrents affichent entre 200 et 300 referers selon Majestic SEO, je me traîne avec 35 domaines référents pour Breizh Power. OK, si l’on prend en compte les 301 + les liens que j’ai réussi à masquer, on arrive peut-être à 60 ou 70, mais c’est bien en dessous de qu’il aurait fallu.

A savoir : j’ai également profité de ce concours pour me pencher sur l’analyse de logs. J’ai donc installé la fameuse Watussi Box de Jean-Benoît en version gratuite et cela m’a permis de détecter que mon site, hébergé chez Euro WH, était régulièrement indisponible pour les moteurs. Autrement dit, tout le monde y avait accès, mais Google ne pouvait pas le crawler, et ce durant plusieurs jours. J’ai dû perdre du temps pour effectuer des tests pour vérifier d’où venait le problème, et ces tests ont confirmé qu’un même site hébergé chez Planethoster ou O2 Switch était bien crawlé par Google. Réponse du support technique : c’est un souci chez Google, il faut patienter … à chacun d’en tirer ses propres conclusions.

Aperçu du module Explorer comme Google dans Google Webmaster Tools. Message d’erreur : URL momentanément inaccessible (erreur 503).

Conclusion

Gagner le concours 2013 et finir 10e du concours 2014, c’est forcément très décevant (pour ceux qui voudraient avoir un aperçu de l’évolution des positions au fur et à mesure du concours, c’est par ici. Désolé pour la mise en page, c’est sommaire mais je n’ai pas pris le temps de chercher un meilleur outil de data vizualisation). Mais j’essaie de voir le bon côté des choses. Je maîtrise désormais de nouvelles techniques. J’ai constaté que tout ce que j’avais mis en place au niveau de la création automatisée de liens n’avait rien donné. Et surtout, cela m’a rappelé deux choses :

ce qui paie avant toute chose, c’est le travail et l’assiduité
l’essence même du moteur de Google, ce sont les liens. Et pour obtenir des liens, mieux vaut avoir un contenu intéressant, qui plus est un contenu tout court.

Pour aller plus loin, je vous recommande le compte-rendu de Jimmy, qui a remporté le concours haut la main et qui confirme parfaitement les deux points ci-dessus et également le résumé de Yann, que je félicite officiellement pour son beau parcours.

PS : un grand merci à tous ceux qui m’ont donné un coup de main pour le concours (ils se reconnaîtront). A charge de revanche 😉

Apéro SEO à Rennes le 02 avril 2014

Jour pour jour après l’annonce du dernier apéro SEO Rennes l’an passé, qui d’ailleurs avait rencontré un franc succès si mes souvenirs sont bons, je vous propose de re-signer pour quelques bières artisanales entre SEO la semaine prochaine.

Jour pour jour après l’annonce du dernier apéro SEO à Rennes l’an passé, qui d’ailleurs avait rencontré un franc succès si mes souvenirs sont bons, je vous propose de re-signer pour quelques bières artisanales entre SEO la semaine prochaine.

J’ai comme le pressentiment que les apéros SEO à Rennes peuvent devenir des moments réellement sympa où chacun aura plaisir à retrouver ses pairs et à échanger autour d’un verre … plusieurs même probablement. Agence ou freelance, expert ou novice, à Rennes les apéros SEO seront désormais l’occasion de picoler intelligemment et avec un prétexte en béton.

Save the date

Edit du 29/03/14 : Ce sera donc le mercredi 2 avril 2014 à partir de 19:30, à l’étage du Tio Paquito. Le bar est situé Place Rallier du Baty, en haut de la Place des Lices (voir la carte).

Si vous souhaitez participer, merci de bien vouloir vous inscrire avec votre pseudo Twitter dans ce Doodle.

Comme toujours, la rencontre est ouverte à tous ceux qui touchent de près ou de loin au SEO, au webmarketing ou au web en général.

Edit du 03/04/14 : retrouvez la liste des inscrits à l’apéro d’hier : Liste des SEO Rennais.

Les apéro SEO à Rennes, une institution

Edit 02/02/15 : bientôt un an depuis cet apéro SEO et je dois avouer que la mayonnaise a bien pris. Il fallait s’en douter, une fois par mois ce n’était pas suffisant pour les Bretons. Les rencontres ont donc désormais lieu tous les trois mois !