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

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

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));"=[^&]+";"")

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));"=[^&]+";"");"&")

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));"=[^&]+";"");"&")))

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));"=[^&]+";"");"&"))))<>"")

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")

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 😉