Comment écrire une requete Google Analytics dans BigQuery ?

Avec Google Analytics Premium (GA3), il est possible d’enregistrer automatiquement les données dans BigQuery. Cela permet de faire des requêtes plus avancées à partir des hits bruts sans echantillonnage, ce qui n’est pas possible avec l’interface Google Analytics ou l’API de reporting. C’est aussi une étape incontournable pour importer les données dans un dataware ou un datalake.

bigquery

BigQuery a de nombreux avantages, mais il y a également des contraintes :

  • Une facturation supplementaire selon le volume de stockage et de requêtes
  • Les requêtes se font en langage SQL
  • Les données sont enregistrées dans une structure imbriquée qui n’est pas intuitive pour un débutant en SQL

Concernant la structure des données Google Analytics dans BigQuery, il faut prendre en compte les points suivants :

  • Il y a une table BigQuery par journée
  • Chaque ligne d’une table correspond aux données d’une session. Toutes les données de la session sont dans cette ligne
  • Dans une ligne, les données et variables sont imbriquées. C’est à dire que les données ne sont pas à plat, il y a plusieurs niveaux. Les données sont des tableaux, par exemple pour les hits de la session, il s’agit d’un tableau où chaque hit est une ligne (ce sont donc des lignes dans des lignes)

La requête BigQuery que nous allons voir permet de mettre à plat les données brutes, ce qui sera utile pour faire n’importe quel type de requête (plutôt que de faire la requête sur un état pré-consolidé de BigQuery, comme Google le fait parfois dans ses exemples). Cette methode permet aussi de bien comprendre la structure des données BigQuery.

Le code SQL de la requête BigQuery est ci-dessous.
Il s’agit d’une requête pour récupérer les ID des clients qui ont consulté les informations de resiliation en 2019. L’avantage de le faire avec BigQuery est de ne pas avoir d’echantillonage et de pouvoir exporter les résultats d’un seul coup. De plus, cette requête est construite pour récupérer les ID à partir des pages où l’utilisateur était connecté (pas forcément les pages de resiliation).

#standardSQL
SELECT DISTINCT date, visitor_id, session_id, client_id
FROM (
  SELECT
  date,
  fullVisitorId AS visitor_id,
  CONCAT(date,'-',fullVisitorId,'-',CAST(visitId AS STRING)) AS session_id,
  (SELECT value FROM UNNEST(hits.customDimensions) WHERE index=2 GROUP BY value) AS client_id
  FROM `mon-compte-big-query.xxx.ga_sessions_*`, UNNEST(hits) as hits
  WHERE _TABLE_SUFFIX BETWEEN '20190101' AND '20191231'
  )
WHERE session_id IN (
  SELECT
  CONCAT(date,'-',fullVisitorId,'-',CAST(visitId AS STRING)) AS session_id
  FROM `mon-compte-big-query.xxx.ga_sessions_*`, UNNEST(hits) as hits
  WHERE _TABLE_SUFFIX BETWEEN '20190101' AND '20191231'
  AND hits.type = 'PAGE'
  AND hits.page.pagePath = '/Resiliation/'
  )
AND client_id != ''
ORDER BY visitor_id

La requête est faite avec le langage SQL BigQuery standard (different du legacy).
Pour cela, on commence par « #standardSQL » pour éviter une confusion. L’avantage du SQL BigQuery standard est de pouvoir utiliser de nouvelles fonctionnalités (notamment l’utilisation de WITH pour l’ecriture des grosses requêtes, ce qui facilite la lecture et les corrections).

Pour préciser la période,
La methode est de faire reference à toutes les tables avec « FROM `mon-compte-big-query.xxx.ga_sessions_*` » (l’etoile à la fin précise qu’on fait reference à toutes les tables correspondantes).
Et de préciser la période dans la clause WHERE avec « WHERE _TABLE_SUFFIX BETWEEN ‘20190101’ AND ‘20191231’ « , ce qui ne va prendre en compte que les tables entre ces dates (le nom des tables finissant par la date du jour concerné).

Pour l’utilisation de custom dimension (client ID dans l’exemple),
Il faut extraire la custom dimension du tableau dans lequel elle est imbriquée.
Le tableau des custom dimensions est dans hits.customDimensions. La custom dimension Client ID a l’index 2 dans ce tableau.
L’extraction se fait avec une sous-requête qui va chercher la valeur pour l’index 2 dans ce tableau. Un regroupement est fait avec GROUP BY pour garder une unicité avec la desimbrication (faite par UNNEST).
Ce qui donne : SELECT value FROM UNNEST(hits.customDimensions) WHERE index=2 GROUP BY value) AS client_id

Pour desimbriquer tous les hits (avoir une ligne par hit, au lieu d’une ligne par session),
il faut utiliser la fonction UNNEST() pour désimbriquer le tableau des hits.
Et une jointure du resultat de la desimbrication des hits est à faire avec le reste de la table pour conserver les informations de session (qui ne sont pas dans le tableau hits).
Ce qui donne : FROM `mon-compte-big-query.xxx.ga_sessions_*`, UNNEST(hits) as hits

Pour que les ID puissent être récupérés sur d’autres pages,
Une condition est posée sur les session_id.
Les session_id sont construits à partir des ID visite et visiteur de GA, en rajoutant la date du jour pour être sur que l’ID soit unique (utile sur des grandes periodes).
Ce qui donne : CONCAT(date,’-‘,fullVisitorId,’-‘,CAST(visitId AS STRING)) AS session_id,
La condition sur les session_id est de ne prendre en compte que ceux qui ont vu les pages ‘/Resiliation/’. Ainsi, on récupere les session_id de ceux qui ont vu ces pages et on demande à BigQuery de regarder tous les hits de ces sessions qui contenaient un ID Client (quand l’internaute était connecté).

Autre astuce qui n’est pas dans l’exemple de requête ci-dessus :
Si vous souhaitez faire un calcul d’aggregation, par exemple connaitre le volume d’utilisateurs, il faut utiliser la fonction COUNT(DISTINCT visitor_id). Pour le volume de sessions, il s’agit de COUNT(DISTINCT session_id).

Pour + d’infos :
– Des exemples de requetes : https://support.google.com/analytics/answer/4419694?hl=fr
– La structure des tables BigQuery et les variables disponibles : https://support.google.com/analytics/answer/3437719?hl=fr

0.00 avg. rating (0% score) - 0 votes

Laissez un commentaire

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