Récupérer les pages vues GA4 avec BigQuery

Les requêtes les plus simples peuvent paraître très complexe lorsque que l’on débute avec la donnée GA4 et BigQuery. Cependant, pas d’inquiétude ! Je vous propose une méthode que vous allez pouvoir utiliser dès à présent et adapter à vos besoins par la suite.

Pour que cet article puisse s’adresser au plus grand nombre, la donnée utilisée provient de l’échantillon de dataset de données GA4 fourni par Google. Si vous souhaitez reproduire la requête à l’identique, je vous invite à récupérer gratuitement l’accès.

Vous pouvez également utiliser la SandBox Bigquery pour vous exercer sans engendrer de coûts.

Notre objectif ici est de récupérer, par date et par page, le nombre de pages vues. Allons-y!

Etape 1 : Individualiser les événement

Nous récupérons dans un premier temps la date event_date, son utilisateur user_pseudo_id et le moment event_timestamp auquel a eu lieu chaque événement. Ces informations sont suffisantes pour individualiser les événements.

Comme l’hypothèse ici est de récupérer les pages vues, nous filtrons la donnée avec un WHERE pour récupérer uniquement les événements ‘page_view’.

⚠️ Le nom des événements peut être différent selon le contexte et le plan de marquage. Pour ma part, quand j’exploite la donnée d’applications, il s’agit de screen_view.
Plus d’infos sur le nom recommandé des événements GA 4 et les paramètres basiques associés : https://support.google.com/analytics/answer/9234069?hl=fr&ref_topic=9756175

Etape 2 : Récupérer les valeurs de paramètres

Dans le plan de marquage de notre data set, l’URL de la page est contenue dans le paramètre ‘page_location’. Notre objectif ici est de récupérer la valeur de ce paramètre pour chaque événement page_view.

Avec GA4, les paramètre d’événement sont nestés dans le champ event_params. Chaque événement est associé à un tableau qui contient les clés (key) ainsi que les valeurs (value).

💡 BigQuery utilise un modèle de donnée semi-structurée. Une partie de la donnée est imbriquée (nested). Les lignes dans BigQuery peuvent ainsi contenir des tableaux (array) de donnée. Les documentations suivantes donnent plus d’informations sur la façon de traitée la donnée imbriquée : https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening_arrays https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_and_structs

Pour accéder aux paramètres, nous faisons appel à l’opérateur UNNEST qui va mettre à plat les paramètres pour les rendre exploitables. Afin de différencier facilement le champ aplati, nous l’appellerons ici params.

L’objectif du champs ‘page’ est de récupérer la valeur du paramètre qui a pour clé (key) ‘page_location’. Pour faire cela, il faut connaître le type de paramètre. Ici il s’agit d’un string, nous récupérons alors params.value.string_value.

Pour exprimer avec des mots ce dont on a besoin, la phrase suivante

Quand la clé du paramètre est ‘page_location’ alors renvoie la valeur string fin.

Qui se traduit avec BigQuery en CASE WHEN params.key = ‘page_location’ THEN params.value.string_value END.

Nous avons précédemment individualisé les événements avec event_date, user_pseudo_id et event_timestamp. En utilisant l’opérateur UNNEST, nous avons multiplié les lignes par le nombre de paramètre de chaque événement. Si nous exploitons la donnée telle quelle, nous allons avoir, pour chaque événement une succession de valeur pour page ‘null’ et une ligne avec la vraie valeur de la page.

Comme nous avons individualisé chaque événement, nous pouvons récupérer la valeur max (celle qui contient la valeur cherchée) pour associer à chaque événement la page.

Etape 3 : Il ne nous reste plus qu’à compter les pages vues

Avec l’étape précédente, nous avons une table qui contient, pour chaque événement page_view, la page associée. Il ne nous reste plus qu’à compter le nombre de lignes par page !

Par date et par page, nous comptons le nombre de lignes COUNT(*) pour obtenir le nombre d’événement page_view.

Et voilà le résultat ! Nous obtenons une table qui contient, par date et par page le nombre de pages vues. Nous pouvons dès à présent connecter la table à notre outil de data visualisation préféré pour exploiter la donnée. (Petit spoil d’un article à venir ? 😉 )