Catégoriser des URL avec Excel

Quand on bosse dans le référencement il est fréquent d'avoir à traiter divers types de listes d'URL (liens internes, pages crawlées, pages génératrices de trafic moteur, backlinks, etc.), parfois plutôt longue. Tout le monde n'ayant pas la chance d'avoir des compétences de développement poussées, nous allons voir aujourd'hui comment catégoriser ces listes avec Excel.

Microsoft Excel

Avant toute chose, je tiens à remercier mon collègue analyste Eric pour m'avoir donné cette astuce il y a quelques temps (hey, rendons à César ce qui lui appartient). Petit précision supplémentaire, vous trouverez en fin d'article un fichier d'exemple avec les 20 pages les plus liées en interne de ce blog.

Pré-requis

Pour réaliser ce travail, il nous faudra au préalable connaitre les catégories que l'on souhaite créer. Dans mon exemple, je souhaite différencier les URL pour savoir si elles correspondent à des tags, des catégories, des pages de contenu ou la home.

Concrètement, nous allons demander à Excel de rechercher dans la colonne où sont présentes les URL des morceaux qui trahissent l'appartenance de telle URL à telle catégorie. Dans mon exemple, s'il y a /tag/ dans l'URL, alors c'est qu'il s'agit d'une page de tags ;-)

Vous l'aurez compris, il est nécessaire d'avoir au sein des URL un marqueur permettant de différencier les types d'URL.

La formule Excel

C'est l'instant un peu barbare de l'article puisqu'en fonction du nombre de catégories que vous souhaitez, la formule peut vite devenir indigeste. C'est pour cette raison que j'ai gardé l'exemple volontairement simple avec seulement 4 types d'URL.

=SI(ESTNUM(CHERCHE("http://";A2));"Home";
SI(ESTNUM(CHERCHE("/tag/";A2));"Tag";
SI(ESTNUM(CHERCHE("/category/";A2));"Catégorie";
"Page")))

Concrètement, on dit à Excel "si dans la cellule A2 tu trouves http:// c'est que c'est la home, si tu trouves /tag/ c'est que c'est une page tag, si tu trouves /category/ c'est que c'est une catégorie, si tu ne trouves rien de tout ça c'est que c'est une page de contenu."

Pour ça on utilise 3 fonctions :

  • SI : =SI("ma condition";"valeur si la condition est présente";"valeur si la condition est absente")
  • ESTNUM : =ESTNUM(valeur) - ESTNUM permet de savoir si la valeur est un nombre ou non, nous ne l'utilisons pas pour ça ici, nous l'utilisons plutôt comme "référence" pour la fonction suivante
  • CHERCHE : =CHERCHE("valeur à chercher";"zone de recherche")

Rien de bien compliqué donc, la difficulté réside dans le fait que nous imbriquons ces formules à plusieurs reprises. La deuxième difficulté consiste à fermer le bon nombre de parenthèses à la fin de la formule.

Un bon moyen de fermer le bon nombre de parenthèses à la fin est de faire un retour chariot (Alt+Entrée sur PC, Cmd+Alt+Entrée sur mac) pour chaque nouvelle catégorie d'URL et de fermer autant de parenthèses qu'il y a de lignes, une parenthèse en moins.

Le fichier d'exemple

Les lignes précédentes ressemblent certainement à du charabia si on n'a pas d'exemple sous les yeux, le plus simple est de télécharger le fichier avec quelques URL et la formule déjà en place, ensuite, c'est à vous de vous amuser avec vos propres données !

Télécharger le fichier

Et Movember !

Pour ne pas trop vous spammer avec ça je n'ai pas mentionné l'opération la semaine dernière, mais sachez que Movember est toujours en cours et que j'y participe toujours ! Pour rappel, Movember est une opération visant à récolter des fonds pour lutter contre les maladies masculines. Vous pouvez jeter un oeil à mon Mo-Space et, si cet article (ou ce blog !) vous a été utile, n'hésitez pas à faire un don, c'est pour la bonne cause ;-) Vous pouvez voir dans la sidebar qu'il n'y a pas besoin de faire de gros dons, même 1 € suffit ! Et puis, vous me voyez avec la moustache, rien que pour ça vous devriez faire un geste !

Catégorie Référencement | Thématique .

4 réponses à Catégoriser des URL avec Excel

  1. Buzzgoo.tv says:

    Bonjour

    Toujours sympa au p’ti dèj de ce voir offir une petit function Excel bien pratique ;)

    Bon WE

  2. Cédric says:

    Toujours très pratique ces petites astuces sur Excel. Juste une remarque. Tu pars ici du principe que tes URLs de catégories, de tags et de contenu ne possèdent pas la racine du domaine. Or ce ne sera pas le cas si tu crawles ton site avec Xenu ou Screaming Frog par exemple.

    Voici une v2 du doc avec un petit complément … j’avoue que la formule pique un peu les yeux mais ça fonctionne (à condition que les URLs soient correctement fermée).
    http://cjoint.com/?CKDj3PLtN44.

    Il y a sûrement moyen d’optimiser tout ça … avec un peu de temps :)

  3. Belkawired says:

    J’adore ce genre d’articles car c’est vrai qu’Excel est un tool qui peut nous simplifier la life, en tous cas merci d’avoir sorti le fichier et également à ton collègue analyste Eric et Cédric de nous avoir fait une V2 ;-)
    Pour ton Movember, la vérité t’a trop un air de Freddy Mercury !! La cla cla class Sylvain !!

  4. marie says:

    Cette fonction Excel est très pratique effectivement. D’ordinaire, ayant des compétences en développement sous PHP, j’ai l’habitude de rédiger des articles plutôt sous le langage PHP, ce qui est ainsi très pratique pour mieux classifier les données.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

*

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Veuillez utiliser votre nom ou votre pseudo. Tout commentaire ne respectant pas cette règle verra son lien supprimé.