Sans aucun doute, l’une des principales étapes de la création d’une stratégie de référencement est l’analyse concurrentielle. Les backlinks des concurrents peuvent offrir des informations sur leurs stratégies de création de liens et vous donner la possibilité de renforcer votre propre profil de liens.
Ces opportunités sont difficiles à identifier manuellement, en particulier dans les niches concurrentielles, où les sites Web ont tendance à avoir une quantité importante de backlinks. Bien que certains outils existent pour faciliter ce processus, comme l’outil Moz Link Intersect, j’ai choisi de créer mon propre outil à l’aide d’Excel qui offrirait une plus grande flexibilité dans la gestion des données.
J’ai écrit ce guide pour expliquer comment vous pouvez créer votre propre analyse des liens concurrentiels dans Excel, y compris un modèle pour vous aider à démarrer immédiatement.
Que pouvez-vous trouver dans ce guide :
- Qu’est-ce que l’intersection de lien exactement ?
- Pourquoi aurais-je besoin de la version Excel ?
- Compris, maintenant montrez-moi la magie !
- Outils du métier
- Étape 1 : Exportez vos données de backlink (ignorez si vous le savez déjà)
- Étape 2 : Importez-le dans Excel (ignorez si vous l’avez déjà fait et avez supprimé les erreurs)
- Étape 3 : Appliquez les formules nécessaires
- Étape 4 : Créer le tableau croisé dynamique
- Étape 5 : Faites le tri et vous avez terminé
- Améliorations futures
- Modèle et instructions
Qu’est-ce que l’intersection de lien exactement ?
Comme vous le savez peut-être, le Intersection de lien Moz outil (également connu sous le nom de Outil de recherche de liens concurrentiels ou Recherche de liens concurrentiels), ainsi que d’autres outils de ce type, vous ont permis de trouver des domaines liés à vos concurrents, mais pas à vous. Vous pouvez ainsi trouver de nombreuses opportunités de liens, en particulier sur les domaines liés à plusieurs de vos concurrents, car il y a plus de chances qu’ils soient également liés à vous.
L’outil Moz est actuellement indisponible, mais je l’ai de bonne foi, il sera de retour sur la route.
La version Excel est un peu la même chose, vous permettant de visualiser ces domaines, les concurrents auxquels ils sont liés et à partir de quelle URL ils y sont liés, en plus d’autres mesures qui vous aideront à décider lesquels contacter.
Pourquoi aurais-je besoin de la version Excel ?
Voici les avantages d’utiliser la version Excel par rapport à d’autres outils :
Tout d’abord, la plupart des outils qui incluent la recherche d’opportunités de liens auprès de vos concurrents font partie d’une plate-forme plus grande qui nécessite généralement un abonnement mensuel. Excel est une chose unique (bien que la source de backlink soit généralement une plate-forme d’abonnement mensuel elle-même), et il y a de fortes chances que vous l’ayez déjà.
Deuxièmement, si vous avez une petite entreprise de référencement comme la mienne (ou si vous êtes un indépendant) et que vous ne pouvez pas encore vous permettre un développeur pour créer vos propres outils, Excel pourrait être l’un des logiciels les plus utiles à votre disposition. Il est idéal pour l’analyse et la visualisation des données et contient de nombreux plugins astucieux qui vous aident dans votre travail de référencement au quotidien. Plus encore, presque toutes les principales plates-formes liées au marketing en ligne ont la possibilité d’exporter vers Excel, ce qui vous donne un emplacement centralisé de toutes les données.
Troisièmement, la version Excel vous permettra de :
- Utiliser les données de backlink de N’IMPORTE QUEL fournisseur, pas seulement OSE, tant qu’il a une URL source (où le lien est publié) et une URL cible (où le lien pointe) ; bien sûr, toutes les métriques peuvent vous aider, mais sont facultatives pour la fonctionnalité de l’outil
- Triez les données comme vous en avez besoin, soit par le nombre de concurrents auxquels les domaines sont liés, soit par l’une des mesures fournies avec vos données
- Analysez autant de concurrents que vous le souhaitez (tant que votre ordinateur peut le gérer)
Compris, maintenant montrez-moi la magie !!
OK, si vous êtes toujours avec moi, je suppose que cet outil vous intéresse, alors je vais suivre une approche étape par étape pour vous expliquer comment le créer. Il inclut un tableau croisé dynamique, mais ce n’est vraiment pas si difficile à utiliser et j’utiliserai des captures d’écran pour montrer comment l’implémenter.
Si vous souhaitez passer au résultat final, le dernier chapitre comprend un modèle et des instructions sur la façon de l’utiliser.
Outils du métier
Avant de commencer l’Excel-fu, voici une liste de ce que vous devez avoir à votre disposition :
- Vous pourriez être choqué par celui-ci, mais vous aurez besoin d’Excel pour que cela fonctionne 🙂 J’ai utilisé la version 2010 32 bits dans mon exemple, mais d’autres versions devraient tout aussi bien fonctionner.
- Données de backlink. Vous pouvez utiliser Moz Link Explorer, l’explorateur de site de MajesticSEO, essentiellement tout ce qui répond aux exigences que j’ai mentionnées ci-dessus et qui a la capacité d’exporter vers Excel. Pour cet exemple, j’ai utilisé les exportations OSE. Une alternative serait d’utiliser une API pour obtenir les données, c’est à vous de décider.
- 6 à 8 heures de votre temps. Je blague 🙂
Étape 1 : Exportez vos données de backlink (ignorez si vous le savez déjà)
C’est assez basique. Si vous avez déjà utilisé Excel pour l’analyse des backlinks, vous savez probablement déjà comment procéder. Personnellement, j’ai un compte Moz PRO, donc j’utiliserai OSE pour cette étape.
Comme je viens tout juste de lancer le site Web de mon entreprise, je ne l’utiliserai pas comme exemple. De plus, comme je suis trop paresseux pour choisir un site Web au hasard, je vais utiliser seomoz.org comme mon domaine principal.
je choisirai 3 concurrents (J’ai mentionné que vous pouvez en choisir autant que vous le souhaitez, mais comme il s’agit de sites Web assez volumineux, trois devraient suffire pour cet exemple). Ceux-ci sont: distilled.net, seerinteractive.com et seogadget.co.uk.
Obtenir des données de backlink de chacun de ces sites (y compris le principal) est simple. Accédez à OSE, entrez le domaine et cliquez sur Rechercher. Ensuite, vous souhaiterez filtrer les résultats pour inclure uniquement des liens externes vers des pages du domaine racine ou du sous-domaine (ce dernier si le site est hébergé sur un sous-domaine assez distinct du domaine racine, comme un blog blogspot.com).
NOTE IMPORTANTE: Obtenir des liens vers le domaine racine vous fournira généralement plus de données, mais nécessitera deux formules supplémentaires à l’étape 3.
En option, vous pouvez filtrer davantage pour n’inclure que les liens dofollow. Assurez-vous de cliquer sur le bouton Filtrer une fois que vous avez terminé.
Ensuite, vous voudrez télécharger ces liens. Maintenant, OSE vous offre deux options. Soit utiliser le “Télécharger CSV” et obtenez jusqu’à 10 000 liens, ou utilisez le bouton Rapports avancés module où vous avez une limite de crédit quotidienne et pouvez exporter jusqu’à 100 000 liens.
Si vous l’utilisez, vous devrez choisir le “Page de liens externes” et “N’importe quelle page sur ce domaine racine” (ou sous-domaine, en conséquence). Tout le reste peut être laissé tel quel, bien que vous puissiez choisir de filtrer les liens avec DA/PA supérieur à une certaine valeur, afin de réduire le nombre total de résultats.
Notez que vous pouvez mettre en file d’attente les exportations, vous n’avez donc pas à attendre qu’une se termine jusqu’à ce que vous commenciez la suivante. Vous recevrez un e-mail lorsqu’ils auront terminé.
Répétez cette opération pour tous vos concurrents également. Au final, nous devrions avoir quatre fichiers CSV différents (un pour nos données de backlink, trois pour les données de nos concurrents).
Étape 2 : Importez-le dans Excel (ignorez si vous l’avez déjà fait et avez supprimé les erreurs)
Il est maintenant temps d’ouvrir le logiciel magique que nos gens appellent Excel.
Pour obtenir les données des fichiers CSV, nous avons deux options :
- Ouvrez directement les fichiers CSV, copiez les colonnes qui nous intéressent (il s’agit de l’URL, de l’URL cible et de toute métrique dont vous avez besoin), puis collez-les dans une nouvelle feuille de calcul.
- Ou utilisez l’assistant d’importation de texte Excel pour importer les données dans une feuille de calcul vide sans ouvrir les CSV
Les deux options sont assez simples, bien que la première soit la plus facile à faire (ne fera même pas de captures d’écran pour cela). Le problème est que la première option ne fonctionne pas si votre installation Windows est définie sur un pays européen.
C’est parce qu’un CSV contient Valeurs séparées par des virgules, la virgule étant le délimiteur de liste par défaut aux États-Unis. Pour les pays européens, le délimiteur par défaut est généralement le point-virgule (“;”), ce qui signifie qu’Excel ne lira pas correctement les fichiers CSV.
Pour résoudre ce problème, vous devez ouvrir le Options régionales et linguistiques depuis le Panneau de configuration de votre installation Windows et réglez-le sur États Unis Anglais)ou garder votre pays actuel et, dans le Réglages avancésmet le symbole décimal en point (“”.”) au lieu de la virgule, et le délimiteur de liste en virgule au lieu du point-virgule. Vous pouvez voir le processus exact ici (Solution #3):
Alternativement, vous pouvez utiliser la deuxième option. Le problème est qu’en raison de la manière dont Excel importe les données, certaines d’entre elles peuvent être affichées de manière erronée, ce qui entraînerait des étapes supplémentaires pour nettoyer les données en supprimant toutes les erreurs. En raison de ce problème, j’ai décidé de ne pas inclure de didacticiel expliquant comment procéder (mais vous pouvez le faire, peu importe si vous préférez ne pas modifier le délimiteur de votre liste).
Quel que soit votre choix, après avoir inclus les données du premier domaine, copiez les données des autres domaines en dessous, sans inclure à nouveau la ligne d’en-tête. De cette façon, vous aurez une liste continue des données de tous les domaines avec une seule ligne d’en-tête (la première).
NOTE IMPORTANTE: Si vous analysez un grand nombre de backlinks (plus de 50 000), n’en saisissez qu’un nombre limité au début (10 – 20 k) et ajoutez le reste (également par lots de 10 – 20 k) après avoir inséré les colonnes de formules du L’étape suivante. Cela est nécessaire en fonction de votre version d’Excel et de vos ressources pour éviter les avertissements d’erreur.
Bon, vous devriez maintenant avoir toutes les données importées dans Excel. Ceci est facultatif, mais je trouve qu’il est beaucoup plus facile de travailler si ces données sont dans un tableau. Pour ce faire, sélectionnez toutes les données jusqu’à présent (cliquez sur l’une des cellules contenant des données, comme A1et frappez CTRL-A), puis transformez-le en tableau (appuyez sur CTRL-L).
N’oubliez pas que sans tableau, vous devrez modifier les formules pour inclure des références de cellule exactes (par exemple $A2 à la place de [@URL]).
Étape 3 : Appliquez les formules nécessaires
Maintenant que nous avons toutes nos données dans Excel, nous devons appliquer les formules nécessaires pour la prochaine étape.
Nos deux premières formules prendront simplement les données des colonnes URL (source) et URL cible et supprimer tout sauf les sous-domaines. Ces formules astucieuses font également partie de l’excellent guide “Excel for SEO” de Distillé.
Nous aurons besoin de deux créer deux nouvelles colonnes pour contenir ces données. Nous nommerons le premier “Sous-domaine source“, et le second sera “Sous-domaine cible“. Puisque nous avons un tableau, il nous suffit d’entrer les noms dans les deux premières colonnes adjacentes, et Excel les joindra automatiquement au tableau.
La première formule est
Sous-domaine source
=MID([@URL],FIND("://",[@URL])+3,IFERROR(FIND("/",[@URL],9),LEN([@URL])+1)-(FIND("://", [@URL])+3)) (où [URL] est la colonne qui contient l’URL source, peut être nommée différemment si vous n’utilisez pas OSE ; merci à GerardGallegos d’avoir signalé une faute de frappe !)
et la seconde formule est :
Sous-domaine cible
=MID([@[Target URL]],FIND("://",[@[Target URL]])+3,IFERROR(FIND("/",[@[Target URL]],9),LEN([@[Target URL]])+1)-(FIND("://",[@[Target URL]])+3)) (fondamentalement le même, juste pour la colonne URL cible).
Les formules obtiennent et affichent essentiellement ce qui se trouve après la partie “://” et avant le premier “/” des URL (cela lui permet également d’obtenir des liens depuis et vers des emplacements sécurisés avec “https”). La partie IFERROR garantit que vous obtenez le bon résultat pour le cas où l’URL n’a pas de barre oblique de fin, comme l’URL de la page d’accueil (OSE ajoute toujours cette barre oblique, mais Majestic SEO ne le fait pas).
Il vous suffit de saisir ces à l’intérieur de la première cellule de chacune des colonneset Excel les remplira automatiquement pour toute la colonne.
NOTE IMPORTANTE: Si vous avez sélectionné l’option “pages vers sous-domaine” au lieu de “vers domaine racine” lors de la récupération de vos données, vous n’aurez pas besoin d’inclure les deux formules suivantes, puisque vous n’avez qu’un seul sous-domaine pour chaque site (par exemple, www.seomoz. org pour Moz, seogadget.co.uk pour SEO Gadget, etc.). Si tel est le cas, passez à la formule Domaines uniques.
Maintenant, vous vous demandez peut-être que nous n’avons obtenu que le sous-domaine de l’URL cible, et c’est un problème réel. Cela signifie que seomoz.org et www.seomoz.org sera compté comme sites différentsce qui peut poser problème plus tard (vous verriez des domaines liés à 4 concurrents ou plus, même si vous n’avez que 3 concurrents dans vos données).
Pour résoudre ce problème, nous devons extraire le domaine racine réel du sous-domaine. Malheureusement ce sera un peu compliqué puisqu’il faut différencier TLD (Domaines de premier niveau) et SLD (Domaines de second niveau), car l’un de nos concurrents est sur un SLD (SEO Gadget), et nous ne voulons pas nous retrouver avec le domaine “co.uk” au lieu de “seogadget.co.uk” (donc vous ne peut pas utiliser la routine “saisir simplement ce qui se trouve après le dernier point comme TLD”).
NOTE IMPORTANTE: Si vous avez le plugin SeoTools for Excel de Niels Bosma, vous pouvez ignorer les formules TLD et Target Root Domain, et n’utiliser qu’une seule formule pour obtenir le domaine racine. Dans ce cas, la formule serait =UrlProperty([@[Target Subdomain]]; “domaine”). Toutefois, si vous avez l’intention d’utiliser vos propres SLD personnalisés (par exemple “blogspot.com” pour éviter de regrouper différents blogs du même domaine), vous devrez utiliser les fonctions ci-dessous. Merci à Roald de m’avoir rappelé cette fonction !
Premièrement, nous devons répertorier tous les TLD et SLD nous nous attendons à rencontrer dans la colonne URL cible quelque part séparée de la table. J’ai choisi la colonne Z pour cela. Notre liste sera la suivante :
.com
.rapporter
.org
.co.uk
Toujours placer les SLD sous les TLD (par principe), ils sont donc détectés en dernier. Considérez-le comme un ensemble de règles, la formule vérifiera toutes les règles et renverra la dernière correspondance trouvée. Ainsi par exemple, si l’un de vos concurrents est un blog hébergé sur “.blogspot.com” (qui n’est pas vraiment un SLD, mais vous le considéreriez comme tel pour votre analyse, puisque vous n’êtes pas intéressé par “blogspot.com ” en tant que concurrent), vous voudriez le placer sous le TLD “.com” afin qu’il soit correctement mis en correspondance.
Avec la liste en place, notre prochaine formule récupérera le TLD/SLD (je les appellerai simplement TLD à partir de maintenant) à partir de la colonne Sous-domaine cible. Utilisez-le dans la prochaine colonne adjacente au tableau et nommez la colonne “TLD”. La formule est :
TLD
=LOOKUP(2^15,SEARCH($Z$1:$Z$4,[@[Target Subdomain]]),$Z$1:$Z$4)
La valeur 2^15 à l’intérieur de LOOKUP indique à la formule de toujours rechercher la dernière occurrence du TLD dans le sous-domaine cible. Si vous êtes curieux de savoir dans quel cas cela serait utile, imaginez le sous-domaine “test.comparison.org”. Vous voudriez récupérer le “.org“, car il s’agit clairement du TLD. Cependant, sans la partie 2^15, Excel rencontrerait d’abord “.comparaison” et s’arrêter, donc il le ferait alors correspondre à “.com“, ce qui serait une erreur.
La plage $Z$1:$Z$4 fait référence aux cellules qui contiennent les TLD.
Maintenant que nous avons le TLD, passons obtenir le domaine racine réel. Pour ce faire, nous obtenons essentiellement le sous-domaine cible, supprimons le TLD, récupérons tout ce qui se trouve après le dernier point, puis appliquons le TLD dessus. Cela signifie que si nous avons “quelque chose.exemple.com“, nous allons supprimer le TLD et obtenir “some.thing.example”, récupérer tout après le dernier point qui nous donne “example”, puis enfin appliquer le TLD pour obtenir le domaine racine “example.com”.
Tout ce qui précède est fait dans une formule, que vous placerez dans la colonne suivante pour être nommée “Target Root Domain”:
Domaine racine cible
=IFERROR(RIGHT([@[Target Subdomain]],LEN([@[Target Subdomain]])-FIND("|",SUBSTITUTE(LEFT([@[Target Subdomain]],LEN([@[Target Subdomain]])-LEN([@TLD])),".","|",LEN(LEFT([@[Target Subdomain]],LEN([@[Target Subdomain]])-LEN([@TLD])))-LEN(SUBSTITUTE(LEFT([@[Target Subdomain]],LEN([@[Target Subdomain]])-LEN([@TLD])),".",""))))),[@[Target Subdomain]]) Ouais, un peu long, je sais. Je voulais cependant l’intégrer dans une seule formule pour éviter de créer des colonnes inutiles et obtenir le domaine racine en une seule fois. La partie IFERROR au début est pour le cas où le sous-domaine cible est en fait le domaine racine, donc il le renvoie simplement à la place. Le reste de la formule fait exactement ce que j’ai décrit ci-dessus.
Nous avons maintenant nos domaines racine !
La cinquième (ou troisième, selon si vous avez utilisé les deux dernières) formule a pour but de vérifier si un domaine source est lié à un domaine cible au moins une foisafin que vous puissiez voir plus tard combien de vos concurrents obtiennent des liens à partir de cette source.
Dans le tableau croisé dynamique que nous construisons à l’étape suivante, la formule aura pour rôle de faire en quelque sorte un “compte distinct” des domaines racine cibles pour chaque URL source. Malheureusement, il n’y a aucun moyen de le faire sans la formule, sauf si vous utilisez Excel 2013.
Je dis cela parce que, pour créer ce “compte distinct“, nous pouvons en fait utiliser l’une des trois formules. Deux d’entre elles peuvent être plus rapides que la troisième, mais vous pouvez également recevoir des avertissements d’erreur d’Excel (au moins la version 2010 32 bits que j’utilise) sur une grande quantité de liens (comme 30k+). J’ai décidé d’utiliser la troisième formule qui, bien qu’elle puisse être plus lente, semble bien fonctionner avec beaucoup de liens et donne le même résultat.
Créez une nouvelle colonne adjacente appelée “Domaines uniques“, et ajoutez la formule suivante :
Domaines uniques
=IF(COUNTIFS(INDIRECT(ADDRESS(ROW(Table2[#Headers])+1,COLUMN([Source Subdomain]))&":"&ADDRESS(ROW([@[Source Subdomain]]),COLUMN([Source Subdomain]))), [@[Source Subdomain]],INDIRECT(ADDRESS(ROW(Table2[#Headers])+1,COLUMN([Target Root Domain]))&":"&ADDRESS(ROW([@[Target Root Domain]]),COLUMN([Target Root Domain]))), [@[Target Root Domain]])=1,1,0)
La fonction COUNTIFS compte combien de fois un sous-domaine source est associé au même domaine racine cible. L’IF identifie la première association de ce type et renvoie la valeur 1 pour celle-ci et la valeur 0 pour les associations suivantes (un peu comme dire “Oui, cette source est liée à cette cible au moins une fois”).
La formule peut sembler longue, mais elle est en fait équivalente à ceci :
=IF(COUNTIFS($O$2:$O2,$O2,$R$2:$R2,$R2)=1,1,0)
Dans ce cas, la colonne O est le sous-domaine source, tandis que la colonne R est le domaine racine cible. 2 est le numéro de ligne où vous introduisez la formule pour la première fois (la ligne juste en dessous de la ligne d’en-tête).
J’ai choisi la version plus longue pour pouvoir l’appliquer sans identifier le…
