Aller au contenu

Comment analyser des paramètres d'URL avec Google Sheets ?

Antoine Brisset
Antoine Brisset
Temps de lecture : 3 min
Comment analyser des paramètres d'URL avec Google Sheets ?

Table des matières

L'édition du jour est directement inspirée de l'article paru il y a a quelques jours sur le blog Yapasdequoi d'Aymeric Bouillat.

Dans son (très bon) article, Aymeric explique comment faire l'agrégation du nombre de hits Googlebot sur les paramètres d'URL présents dans une grosse liste d'URLs issue des logs, à l'aide d'un script Bash.

Une analyse qui peut être super utile sur des sites à forte volumétrie, notamment pour identifier des problèmes de contenu dupliqué, de consommation inutile de budget de crawl, etc.

En lisant l'articke, je me suis dit que ça pouvait sympa de reproduire ça en utilisant Google Sheets.

Allez, c'est parti !

Etape 1 : récupérer les paramètres de l'URL

Il faut d'abord extraire, pour chaque URL, la partie qui commence après le ?, c'est-à-dire après le premier paramètre apparaissant dans l'URL.

Pour aller plus vite, je vais reprendre la formule que j'avais donnée ici pour sélectionner le chemin de l'URL, en l'adaptant à mon besoin.

La voici :

=RIGHT(A1;LEN(A1)-FIND("?";A1)

Explications :

  1. je cherche le signe ? dans l'URL et je récupère sa position avec FIND
  2. je calcule le nombre de caractères de la portion d'URL contenant les paramètres, en faisant la différence entre la longueur totale de l'URL et la position du point d'interrogation récupérée à l'étape 1
  3. ce qui me donne le nombre de caractères à extraire à partir de la fin de l'URL avec RIGHT
Fonction RIGHT

Etape 2 : supprimer les valeurs de paramètres

Ensuite, je vais supprimer les valeurs de paramètres, car je n'en ai pas besoin (j'ai uniquement besoin des noms de paramètres).

En gros, dans la chaine "?utm_source=google", j'ai besoin de "utm_source" mais pas de "=google".

Hop, je reprends la formule que j'avais déjà mentionnée ici. L'idée étant de remplacer tous les caractères autres que & par... rien.

=REGEXREPLACE(RIGHT(A1:A;LEN(A1:A)-FIND("?";A1:A));"=[^&]+";"")
Fonction REGEXREPLACE

Etape 3 : découper les paramètres

Ensuite, un petit coup de SPLIT pour que chaque nom de paramètre soit affiché dans une cellule distincte.

=SPLIT(REGEXREPLACE(RIGHT(A1:A;LEN(A1:A)-FIND("?";A1:A));"=[^&]+";"");"&")
Fonction SPLIT

Etape 4 : traiter toute la liste avec ARRAYFORMULA

Pour aller plus vite, je vais utiliser ARRAYFORMULA pour que ma formule s'applique à toutes les URLs de la colonne d'un coup.

J'ajoute également un ISBLANK entre deux pour exclure les cellules vides.

=ARRAYFORMULA(IF(ISBLANK(A1:A);"";SPLIT(REGEXREPLACE(RIGHT(A1:A;LEN(A1:A)-FIND("?";A1:A));"=[^&]+";"");"&")))
Fonction ARRAYFORMULA

Etape 5 : afficher les paramètres les uns en dessous des autres

Pour que tous les paramètres s'affichent les uns en dessous des autres, j'utilise FLATTEN, en prenant le soin de filtrer les valeurs vides avec FILTER.

=FILTER(FLATTEN(ARRAYFORMULA(IF(ISBLANK(A1:A);"";SPLIT(REGEXREPLACE(RIGHT(A1:A;LEN(A1:A)-FIND("?";A1:A));"=[^&]+";"");"&"))));FLATTEN(ARRAYFORMULA(IF(ISBLANK(A1:A);"";SPLIT(REGEXREPLACE(RIGHT(A1:A;LEN(A1:A)-FIND("?";A1:A));"=[^&]+";"");"&"))))<>"")
Fonction FILTER

Etape 6 : faire le group and count avec QUERY

Il ne reste plus qu'à faire un group and count avec la fonction QUERY, en triant par ordre décroissant du nombre d'occurrences trouvé.

=QUERY(FILTER(FLATTEN(ARRAYFORMULA(IF(ISBLANK(A1:A);"";SPLIT(REGEXREPLACE(RIGHT(A1:A;LEN(A1:A)-FIND("?";A1:A));"=[^&]+";"");"&"))));FLATTEN(ARRAYFORMULA(IF(ISBLANK(A1:A);"";SPLIT(REGEXREPLACE(RIGHT(A1:A;LEN(A1:A)-FIND("?";A1:A));"=[^&]+";"");"&"))))<>"");"SELECT Col1, COUNT(Col1) WHERE Col1 != '' GROUP BY Col1 ORDER BY COUNT(Col1) DESC")
Formule finale

Et voilà !

Bien entendu, cette méthode ne conviendra pas pour des listes d'URLs de plusieurs millions de lignes.

Mais pour un site avec quelques centaines ou milliers d'URLs avec paramètres, ça peut être utile 😉

Google Sheets

Antoine Brisset Twitter

Consultant SEO depuis 2010. Je traque les clics inutiles et j'automatise les tâches répétitives pour gagner du temps dans mon quotidien de travailleur du web.


Articles Similaires

Membres Public

RECHERCHEV dans Google Sheets : comment retourner plusieurs colonnes à la fois ?

Il y a quelques jours, Mickaël Zerrougui a partagé un petit tuto sympa sur la RECHERCHEV en SEO. Si tu l'as loupé, je te le remets ici ⬇️ 🏆 La RechercheV en SEO 🏆 Y'a des SEO qui savent toujours pas utiliser la rechercheV ?😱 Vous en avez marre des

RECHERCHEV dans Google Sheets : comment retourner plusieurs colonnes à la fois ?
Membres Public

Comment extraire les dossiers d'une URL avec Google Sheets ?

La semaine dernière, Nicolas m'interpellait sur Twitter suite à ce tweet. Help : Tips Gsheet Hello les SEO J ai des urls dans mon gsheet du type : Site/cat/cat1/cat2 etc.. J’aimerai extraire automatiquement les 2 derniers slug d’url Une idee ? — Nicolas Evenou (@NicolasEvenou) October 23,

Comment extraire les dossiers d'une URL avec Google Sheets ?
Membres Public

Importer des datasets facilement grâce aux fonctions nommées de Google Sheets

Aujourd'hui, je vais te montrer comment importer facilement un jeu de données que tu utilises souvent à l'intérieur de tes feuilles Google Sheets. Exemple : tu travailles sur du SEO local et tu as besoin, régulièrement, de récupérer la liste des 36000 communes françaises. Pour éviter de

Importer des datasets facilement grâce aux fonctions nommées de Google Sheets