Work hard in silence, let success make the noise !
Étiquette : Excel
Vous trouverez dans cette section tous les articles traitant de près ou de loin d’Excel. Le plus souvent, il s’agira d’articles liés à la manière d’utiliser Excel dans le cadre de missions SEO. Parmi ces articles, on retrouvera notamment une série de cinq billets publiés en Septembre et Octobre 2014 et proposant chacun un document à télécharger gratuitement.
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.
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 !
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 !!
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.
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 !
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.
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 :
Voilà le résultat de la macroL’onglet de reportingLa 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 :
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.
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 !!
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.
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.
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.
