Patterns de requêtes GA4 BigQuery

Interroger efficacement les données GA4 dans BigQuery nécessite de comprendre quelques patterns qui diffèrent des requêtes standard sur des tables partitionnées. GA4 utilise des tables date-shardées (et non des tables nativement partitionnées), stocke la plupart des données analytiques dans des tableaux imbriqués, et expose des pièges de coûts faciles à déclencher accidentellement.

Toujours filtrer `_TABLE_SUFFIX`

GA4 utilise des tables date-shardées : des tables séparées nommées events_YYYYMMDD plutôt qu’une seule table partitionnée. La syntaxe wildcard events_* les interroge toutes en même temps. Sans filtre _TABLE_SUFFIX, vous scannez toute votre historique — potentiellement des années de données.

-- Sans filtre : scanne tout, coûteux
SELECT event_name, COUNT(*) AS cnt
FROM `project.analytics_123456789.events_*`
GROUP BY event_name

-- Avec filtre : scanne uniquement la plage spécifiée
SELECT event_name, COUNT(*) AS cnt
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY event_name

Le pattern FORMAT_DATE('%Y%m%d', ...) génère le format de chaîne YYYYMMDD qui correspond au suffixe de la table. DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) exclut les données du jour, qui sont encore dans la table intraday et peuvent ne pas être complètes dans les tables quotidiennes.

Pour les requêtes sur un seul jour :

WHERE _TABLE_SUFFIX = '20260127'

Note sur le fuseau horaire : _TABLE_SUFFIX suit l’heure du Pacifique, pas le fuseau horaire de votre propriété. Consultez Gestion des fuseaux horaires GA4 BigQuery pour les implications complètes et le pattern consistant à élargir votre plage _TABLE_SUFFIX d’un jour lorsque vous travaillez avec des fuseaux horaires non américains.

Deux approches UNNEST

Les champs event_params, user_properties et items de GA4 sont des REPEATED RECORDs — des tableaux. Extraire des données depuis ceux-ci nécessite UNNEST, mais il existe deux patterns distincts avec des cas d’usage différents.

UNNEST inline (sous-requête corrélée)

Le pattern inline extrait des paramètres spécifiques tout en conservant une ligne par événement :

SELECT
  event_name,
  event_timestamp,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
  user_pseudo_id,
  device.category,
  geo.country
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = '20260127'

Chaque sous-requête plonge dans le tableau event_params, trouve la clé correspondante et retourne la valeur. Un événement avec 20 paramètres produit une seule ligne en sortie. Utilisez ce pattern pour la plupart des requêtes lorsque vous savez quels paramètres vous souhaitez.

L’optimiseur de requêtes BigQuery peut regrouper plusieurs sous-requêtes corrélées sur le même tableau en un seul passage, de sorte qu’extraire 10 paramètres ne coûte pas 10 fois plus que d’en extraire 1.

UNNEST en clause FROM (expansion de lignes)

Le pattern en clause FROM développe le tableau en lignes séparées — une ligne par élément du tableau :

SELECT
  ep.key,
  COUNT(*) AS occurrences
FROM `project.analytics_123456789.events_*`,
  UNNEST(event_params) AS ep
WHERE _TABLE_SUFFIX = '20260127'
GROUP BY ep.key
ORDER BY occurrences DESC

Un événement avec 15 paramètres devient 15 lignes. Utilisez ce pattern pour la découverte de paramètres (quels paramètres existent et à quelle fréquence ?) et pour l’analyse qui opère sur la structure du tableau elle-même.

Attention aux tableaux multiples : Si vous effectuez un UNNEST de deux tableaux de la même table dans la clause FROM, vous obtenez un produit cartésien. Un événement avec 15 event_params et 3 items devient 45 lignes. Ce n’est presque jamais ce que vous souhaitez. Lorsque vous avez besoin de données des deux tableaux, utilisez UNNEST inline pour l’un et UNNEST en clause FROM pour l’autre, ou utilisez des CTEs séparées.

Macros dbt réutilisables

Dans un projet dbt, l’extraction de paramètres est répétitive. L’encoder dans des macros réduit la répétition et rend l’hypothèse de type explicite :

-- macros/ga4_param_string.sql
{% macro ga4_param_string(column, param_name) %}
(SELECT value.string_value FROM UNNEST({{ column }}) WHERE key = '{{ param_name }}')
{% endmacro %}

-- macros/ga4_param_int.sql
{% macro ga4_param_int(column, param_name) %}
(SELECT value.int_value FROM UNNEST({{ column }}) WHERE key = '{{ param_name }}')
{% endmacro %}

Utilisation dans un modèle :

SELECT
  {{ ga4_param_int('event_params', 'ga_session_id') }} AS session_id,
  {{ ga4_param_string('event_params', 'page_location') }} AS page_location,
  {{ ga4_param_string('event_params', 'page_title') }} AS page_title
FROM {{ source('ga4', 'events') }}
WHERE _TABLE_SUFFIX = '{{ var("run_date") }}'

Le nom de la macro signale le type attendu. Lorsque le type d’un paramètre est incertain ou historiquement incohérent, utilisez une variante COALESCE — consultez Détection de type event_params GA4 pour ce pattern.

Gérer à la fois les tables quotidiennes et intraday

Lorsque votre pipeline doit interroger les données les plus récentes disponibles (intraday pour aujourd’hui, quotidien pour les jours précédents) :

-- Interroger les tables quotidiennes pour l'historique, intraday pour aujourd'hui
SELECT * FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260120' AND '20260126'

UNION ALL

SELECT * FROM `project.analytics_123456789.events_intraday_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())

Rappel : les tables intraday n’ont pas traffic_source, user_ltv ni is_active_user. Si votre requête référence ces champs, le UNION ALL produira des nulls depuis la partie intraday. Gérez avec COALESCE si nécessaire, ou documentez que ces champs ne sont disponibles que pour les jours terminés.

Pratiques de contrôle des coûts

Évitez SELECT * sur les tables imbriquées. Les tables d’événements GA4 contiennent des structures profondément imbriquées. Sélectionner toutes les colonnes force BigQuery à scanner l’intégralité de la structure imbriquée même si vous n’avez besoin que de quelques champs. Soyez explicite sur ce dont vous avez besoin.

Effectuez un dry run avant d’exécuter de grandes requêtes. Dans la console BigQuery, l’estimation des octets traités apparaît avant l’exécution. Pour les requêtes couvrant plusieurs mois de données GA4, estimez le coût (bytes_processed / 1e12 * 5 pour la tarification à la demande) avant de valider.

Matérialisez les agrégations fréquentes. Si la même requête de plage de dates s’exécute plusieurs fois par jour (rafraîchissements de tableaux de bord, requêtes d’outils BI), une requête planifiée ou un modèle dbt incrémental qui pré-agrège les données élimine les scans répétés.

Utilisez APPROX_COUNT_DISTINCT pour les comptages d’utilisateurs. Lorsque la précision exacte n’est pas requise, APPROX_COUNT_DISTINCT(user_pseudo_id) est significativement moins coûteux que COUNT(DISTINCT user_pseudo_id) à l’échelle. Il correspond également plus étroitement à la propre méthodologie de comptage de GA4. Consultez Écarts de chiffres entre GA4 et BigQuery pour comprendre pourquoi les comptages exacts et les chiffres de l’interface GA4 ne concordent de toute façon jamais parfaitement.

Partitionnez vos modèles intermédiaires. Lors de la matérialisation d’un modèle GA4 de base ou intermédiaire dans dbt, partitionnez par event_date et clusterisez par session_key ou user_pseudo_id. Les requêtes en aval qui filtrent par date ou par utilisateur ne scanneront que les partitions pertinentes plutôt que la table complète.

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
      'field': 'event_date',
      'data_type': 'date'
    },
    cluster_by=['session_key', 'event_name']
) }}

Pour une vue d’ensemble de l’optimisation des coûts BigQuery, consultez Modèle de coûts BigQuery et Patterns de partition pruning BigQuery.

Toujours filtrer _TABLE_SUFFIX