Adrienne Vermorel

Unnester les événements GA4 : patterns pour chaque cas d'usage

28 janvier 2026

La première fois qu’on lance une requête sur GA4 dans BigQuery, on écrit quelque chose comme SELECT page_location FROM events… et on obtient une erreur. La colonne page_location n’existe pas. La donnée qu’on cherche est cachée dans un array appelé event_params, et l’extraire demande un pattern SQL que la plupart des analystes n’ont jamais croisé : UNNEST.

Cet article propose des patterns SQL prêts pour la production pour tous les scénarios GA4 courants — des simples pages vues aux funnels e-commerce complexes — plus des modèles dbt pour industrialiser ces patterns. Que vous ayez lu la référence du schéma dans l’article précédent ou que vous commenciez ici, vous repartirez avec des requêtes prêtes à copier-coller qui marchent vraiment.

Pourquoi les données GA4 nécessitent un UNNEST

GA4 stocke les paramètres d’événements dans des champs RECORD répétés plutôt que dans des colonnes classiques. Un événement page_view n’a pas de colonne page_location. À la place, il contient un array event_params avec des paires clé-valeur du type {key: 'page_location', value: {string_value: 'https://example.com/products'}}.

Ce choix de conception n’est pas arbitraire. Les structures imbriquées permettent à GA4 de stocker n’importe quel nombre de paramètres custom sans modifier le schéma, et le stockage en colonnes de BigQuery traite les données imbriquées plus efficacement que des tables classiques.

Trois arrays imbriqués apparaissent dans chaque export GA4 :

event_params : tous les paramètres de l’événement (page_location, ga_session_id, paramètres custom)
user_properties : dimensions custom au niveau utilisateur, définies via gtag ou Firebase
items : données produit pour les événements e-commerce (item_name, item_id, price, quantity)

Chaque paramètre stocke sa valeur dans l’un des quatre champs typés : string_value, int_value, float_value ou double_value. Un seul est rempli selon le type de donnée détecté. Si vous interrogez le mauvais, vous obtenez NULL.

Les deux approches UNNEST que tout analyste doit connaître

Il existe exactement deux façons d’extraire des données d’arrays imbriqués dans BigQuery. Choisir la mauvaise est la cause la plus fréquente de métriques GA4 incorrectes.

Les sous-requêtes corrélées préservent l’intégrité des lignes

L’approche recommandée pour extraire des paramètres spécifiques passe par une sous-requête dans le SELECT :

SELECT
  event_name,
  event_timestamp,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_location,
  (SELECT value.int_value
   FROM UNNEST(event_params)
   WHERE key = 'ga_session_id') AS ga_session_id
FROM `project.dataset.events_*`
WHERE event_name = 'page_view'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

Ce pattern conserve une ligne par événement, quel que soit le nombre de paramètres. Un événement avec 20 paramètres produit toujours exactement une ligne en sortie. La sous-requête va chercher dans l’array, trouve la clé correspondante et renvoie la valeur.

Utilisez les sous-requêtes corrélées quand vous savez quels paramètres vous voulez et que vous devez préserver le grain événementiel.

UNNEST dans le FROM éclate les arrays en lignes

Quand vous avez besoin d’analyser les paramètres eux-mêmes (compter les occurrences, découvrir quels paramètres existent, ou pivoter le contenu des arrays), utilisez UNNEST dans le FROM :

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

Ce pattern crée une ligne par élément de l’array. Un événement avec 15 paramètres devient 15 lignes, chacune exposant la clé et les champs de valeur d’un paramètre.

La multiplication des lignes est voulue : vous éclatez délibérément l’array pour analyser son contenu. Ce même comportement cause des erreurs quand il est utilisé à mauvais escient.

Comment choisir

Posez-vous la question : « Est-ce que j’ai besoin d’analyser la structure de l’array, ou juste de récupérer des valeurs précises ? »

Pour des valeurs précises, utilisez une sous-requête corrélée. Pour examiner ou agréger les éléments de l’array, utilisez UNNEST dans le FROM. En cas de doute, la sous-requête corrélée est plus sûre.

CROSS JOIN vs LEFT JOIN UNNEST

La syntaxe avec virgule dans FROM table, UNNEST(array) est un raccourci pour CROSS JOIN. C’est important car CROSS JOIN exclut sans prévenir les lignes où l’array est vide ou NULL.

-- CROSS JOIN implicite : les événements sans items disparaissent
SELECT event_name, item.item_name
FROM `project.dataset.events_*`,
     UNNEST(items) AS item
WHERE event_name IN ('purchase', 'session_start')

Si vous lancez cette requête en espérant voir les achats et les débuts de session, vous ne verrez que les achats. Les événements session_start n’ont pas d’items, donc CROSS JOIN les exclut.

Utilisez LEFT JOIN pour conserver toutes les lignes :

-- LEFT JOIN : tous les événements apparaissent, NULL pour les items manquants
SELECT event_name, item.item_name
FROM `project.dataset.events_*`
LEFT JOIN UNNEST(items) AS item
WHERE event_name IN ('purchase', 'session_start')

Pour event_params, CROSS JOIN convient presque toujours puisque chaque événement a des paramètres. Pour items et user_properties, préférez LEFT JOIN par défaut — sauf si vous voulez explicitement filtrer sur les événements qui contiennent ces arrays.

Patterns e-commerce

L’analyse e-commerce dans GA4 demande de comprendre quelles données se trouvent dans le RECORD ecommerce (totaux déjà agrégés au niveau commande) et lesquelles sont dans l’array items (détails par produit).

Métriques au niveau commande, sans UNNEST

Le champ ecommerce contient les totaux de transaction, pas besoin d’unnester :

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS order_date,
  COUNT(DISTINCT ecommerce.transaction_id) AS transactions,
  SUM(ecommerce.purchase_revenue) AS revenue,
  SUM(ecommerce.total_item_quantity) AS items_sold,
  SAFE_DIVIDE(
    SUM(ecommerce.purchase_revenue),
    COUNT(DISTINCT ecommerce.transaction_id)
  ) AS average_order_value
FROM `project.dataset.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY order_date
ORDER BY order_date

Cette requête traite des millions d’événements d’achat efficacement car elle ne touche jamais aux arrays imbriqués.

Performance produit au niveau article

Pour des métriques par produit, il faut unnester l’array items :

SELECT
  item.item_id,
  item.item_name,
  item.item_category,
  COUNT(DISTINCT ecommerce.transaction_id) AS orders_containing_item,
  SUM(item.quantity) AS units_sold,
  SUM(item.item_revenue) AS product_revenue,
  SAFE_DIVIDE(SUM(item.item_revenue), SUM(item.quantity)) AS avg_unit_price
FROM `project.dataset.events_*`,
     UNNEST(items) AS item
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY item.item_id, item.item_name, item.item_category
ORDER BY product_revenue DESC

Notez que le revenu vient de item.item_revenue, pas de ecommerce.purchase_revenue. Si vous sommez ecommerce.purchase_revenue après avoir unnesté les items, vous multipliez le total de la commande par le nombre d’articles — une erreur classique.

Analyse ajouts panier et vues produit

Le même array items apparaît dans add_to_cart, view_item et les autres événements e-commerce :

SELECT
  item.item_name,
  item.item_category,
  COUNTIF(event_name = 'view_item') AS product_views,
  COUNTIF(event_name = 'add_to_cart') AS add_to_carts,
  COUNTIF(event_name = 'purchase') AS purchases,
  SAFE_DIVIDE(
    COUNTIF(event_name = 'add_to_cart'),
    COUNTIF(event_name = 'view_item')
  ) AS view_to_cart_rate,
  SAFE_DIVIDE(
    COUNTIF(event_name = 'purchase'),
    COUNTIF(event_name = 'add_to_cart')
  ) AS cart_to_purchase_rate
FROM `project.dataset.events_*`,
     UNNEST(items) AS item
WHERE event_name IN ('view_item', 'add_to_cart', 'purchase')
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY item.item_name, item.item_category
HAVING product_views > 100
ORDER BY product_views DESC

Funnel de checkout complet

L’analyse de funnel par session demande d’extraire ga_session_id et de le combiner avec user_pseudo_id pour créer un identifiant de session vraiment unique :

WITH funnel_events AS (
  SELECT
    CONCAT(
      user_pseudo_id, '-',
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    ) AS session_id,
    event_name,
    event_timestamp
  FROM `project.dataset.events_*`
  WHERE event_name IN (
    'view_item', 'add_to_cart', 'begin_checkout',
    'add_shipping_info', 'add_payment_info', 'purchase'
  )
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN session_id END) AS sessions_viewed_item,
  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN session_id END) AS sessions_added_to_cart,
  COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN session_id END) AS sessions_began_checkout,
  COUNT(DISTINCT CASE WHEN event_name = 'add_shipping_info' THEN session_id END) AS sessions_added_shipping,
  COUNT(DISTINCT CASE WHEN event_name = 'add_payment_info' THEN session_id END) AS sessions_added_payment,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN session_id END) AS sessions_purchased,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN session_id END),
    COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN session_id END)
  ) AS overall_conversion_rate
FROM funnel_events

Patterns d’événements d’engagement

Au-delà de l’e-commerce, GA4 suit des dizaines d’événements d’engagement. Chacun demande d’extraire des paramètres spécifiques.

Pages vues avec parsing d’URL propre

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS event_date,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_url,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_title') AS page_title,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_referrer') AS referrer,
  -- Extraire le path sans protocole ni query params
  REGEXP_REPLACE(
    REGEXP_REPLACE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
      r'^https?://[^/]+', ''
    ),
    r'[\?#].*$', ''
  ) AS page_path,
  -- Identifier les landing pages
  IFNULL(
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances'),
    0
  ) = 1 AS is_landing_page
FROM `project.dataset.events_*`
WHERE event_name = 'page_view'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

Métriques de session avec les bonnes clés

Le paramètre ga_session_id est un timestamp Unix qui identifie une session, mais il n’est unique qu’au sein d’un même utilisateur. Deux utilisateurs différents peuvent avoir le même ga_session_id s’ils ont démarré leur session à la même seconde. Il faut toujours le concaténer avec user_pseudo_id :

WITH session_events AS (
  SELECT
    CONCAT(
      user_pseudo_id, '-',
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    ) AS session_id,
    user_pseudo_id,
    event_timestamp,
    event_name,
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'engagement_time_msec') AS engagement_time_msec,
    (SELECT value.string_value
     FROM UNNEST(event_params)
     WHERE key = 'session_engaged') AS session_engaged,
    traffic_source.source,
    traffic_source.medium,
    device.category AS device_category,
    geo.country
  FROM `project.dataset.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)

SELECT
  session_id,
  user_pseudo_id,
  MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start,
  MAX(TIMESTAMP_MICROS(event_timestamp)) AS session_end,
  TIMESTAMP_DIFF(
    MAX(TIMESTAMP_MICROS(event_timestamp)),
    MIN(TIMESTAMP_MICROS(event_timestamp)),
    SECOND
  ) AS session__duration_seconds,
  COUNT(*) AS session__events,
  COUNTIF(event_name = 'page_view') AS session__pageviews,
  SUM(engagement_time_msec) / 1000 AS session__engagement_seconds,
  MAX(session_engaged) = '1' AS session__is_engaged,
  ANY_VALUE(source) AS source,
  ANY_VALUE(medium) AS medium,
  ANY_VALUE(device_category) AS device_category,
  ANY_VALUE(country) AS country
FROM session_events
GROUP BY session_id, user_pseudo_id

Analyse de profondeur de scroll

L’événement scroll se déclenche quand l’utilisateur atteint 90 % de profondeur de scroll (par défaut) :

SELECT
  REGEXP_REPLACE(
    REGEXP_REPLACE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
      r'^https?://[^/]+', ''
    ),
    r'[\?#].*$', ''
  ) AS page_path,
  COUNT(*) AS scroll_events,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id, '-',
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  )) AS sessions_with_scroll
FROM `project.dataset.events_*`
WHERE event_name = 'scroll'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY page_path
ORDER BY scroll_events DESC

Pour calculer le taux de scroll, joignez avec le nombre de pages vues :

WITH page_views AS (
  SELECT
    REGEXP_REPLACE(
      REGEXP_REPLACE(
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
        r'^https?://[^/]+', ''
      ),
      r'[\?#].*$', ''
    ) AS page_path,
    COUNT(*) AS pageviews
  FROM `project.dataset.events_*`
  WHERE event_name = 'page_view'
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  GROUP BY page_path
),

scrolls AS (
  SELECT
    REGEXP_REPLACE(
      REGEXP_REPLACE(
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
        r'^https?://[^/]+', ''
      ),
      r'[\?#].*$', ''
    ) AS page_path,
    COUNT(*) AS scroll_events
  FROM `project.dataset.events_*`
  WHERE event_name = 'scroll'
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  GROUP BY page_path
)

SELECT
  pv.page_path,
  pv.pageviews,
  IFNULL(s.scroll_events, 0) AS scroll_events,
  SAFE_DIVIDE(s.scroll_events, pv.pageviews) AS scroll_rate
FROM page_views pv
LEFT JOIN scrolls s ON pv.page_path = s.page_path
WHERE pv.pageviews > 100
ORDER BY scroll_rate DESC

Tracking des clics et liens sortants

SELECT
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'link_url') AS link_url,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'link_domain') AS link_domain,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'outbound') AS is_outbound,
  COUNT(*) AS clicks
FROM `project.dataset.events_*`
WHERE event_name = 'click'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY link_url, link_domain, is_outbound
ORDER BY clicks DESC

Tracking des téléchargements

SELECT
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'file_name') AS file_name,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'file_extension') AS file_extension,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'link_url') AS download_url,
  COUNT(*) AS downloads,
  COUNT(DISTINCT user_pseudo_id) AS unique_users
FROM `project.dataset.events_*`
WHERE event_name = 'file_download'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY file_name, file_extension, download_url
ORDER BY downloads DESC

Funnel d’engagement vidéo

SELECT
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'video_title') AS video_title,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'video_provider') AS video_provider,
  COUNTIF(event_name = 'video_start') AS starts,
  COUNTIF(event_name = 'video_progress'
    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'video_percent') = 25
  ) AS reached_25_pct,
  COUNTIF(event_name = 'video_progress'
    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'video_percent') = 50
  ) AS reached_50_pct,
  COUNTIF(event_name = 'video_progress'
    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'video_percent') = 75
  ) AS reached_75_pct,
  COUNTIF(event_name = 'video_complete') AS completions,
  SAFE_DIVIDE(
    COUNTIF(event_name = 'video_complete'),
    COUNTIF(event_name = 'video_start')
  ) AS completion_rate
FROM `project.dataset.events_*`
WHERE event_name IN ('video_start', 'video_progress', 'video_complete')
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY video_title, video_provider
HAVING starts > 10
ORDER BY starts DESC

Paramètres custom et conversion de type

Les paramètres GA4 peuvent arriver dans différents types selon comment ils ont été envoyés. Un paramètre configuré comme nombre peut être stocké en int_value dans certains événements et en double_value dans d’autres.

Extraction universelle avec COALESCE

Pour une sortie texte (fonctionne pour tout type de paramètre) :

SELECT
  (SELECT COALESCE(
      value.string_value,
      CAST(value.int_value AS STRING),
      CAST(value.double_value AS STRING),
      CAST(value.float_value AS STRING)
    )
   FROM UNNEST(event_params)
   WHERE key = 'custom_dimension'
  ) AS custom_dimension
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX = '20240115'

Pour des calculs numériques :

SELECT
  (SELECT COALESCE(
      value.double_value,
      CAST(value.int_value AS FLOAT64),
      value.float_value
    )
   FROM UNNEST(event_params)
   WHERE key = 'custom_metric'
  ) AS custom_metric
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX = '20240115'

Extraction des user properties

L’array user_properties suit la même structure que event_params :

SELECT
  user_pseudo_id,
  (SELECT value.string_value
   FROM UNNEST(user_properties)
   WHERE key = 'customer_tier') AS customer_tier,
  (SELECT value.int_value
   FROM UNNEST(user_properties)
   WHERE key = 'lifetime_purchases') AS lifetime_purchases,
  COUNT(*) AS events
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY user_pseudo_id, customer_tier, lifetime_purchases

Paramètres custom au niveau article

Depuis octobre 2023, GA4 exporte les paramètres custom au niveau article dans un array imbriqué item_params à l’intérieur de chaque item. Il faut donc un double UNNEST :

SELECT
  item.item_name,
  item.item_id,
  (SELECT value.string_value
   FROM UNNEST(item.item_params)
   WHERE key = 'color') AS item_color,
  (SELECT value.string_value
   FROM UNNEST(item.item_params)
   WHERE key = 'size') AS item_size,
  SUM(item.quantity) AS units_sold
FROM `project.dataset.events_*`,
     UNNEST(items) AS item
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY item.item_name, item.item_id, item_color, item_size
ORDER BY units_sold DESC

Fonctions temporaires réutilisables

Pour les requêtes complexes qui extraient beaucoup de paramètres, CREATE TEMP FUNCTION évite la répétition :

CREATE TEMP FUNCTION GetParamString(params ANY TYPE, param_key STRING) AS (
  (SELECT value.string_value FROM UNNEST(params) WHERE key = param_key)
);

CREATE TEMP FUNCTION GetParamInt(params ANY TYPE, param_key STRING) AS (
  (SELECT value.int_value FROM UNNEST(params) WHERE key = param_key)
);

CREATE TEMP FUNCTION GetParamDouble(params ANY TYPE, param_key STRING) AS (
  (SELECT COALESCE(value.double_value, CAST(value.int_value AS FLOAT64))
   FROM UNNEST(params) WHERE key = param_key)
);

-- Les requêtes deviennent plus lisibles
SELECT
  event_name,
  GetParamString(event_params, 'page_location') AS page_location,
  GetParamInt(event_params, 'ga_session_id') AS ga_session_id,
  GetParamDouble(event_params, 'value') AS event_value
FROM `project.dataset.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

Optimisation des performances

Les tables GA4 grossissent vite. Un site à trafic moyen génère des millions d’événements par mois, et des requêtes mal écrites peuvent scanner des téraoctets et coûter des centaines de dollars.

Toujours filtrer sur _TABLE_SUFFIX

Les exports GA4 créent des tables shardées par jour (events_20240115, events_20240116, etc.). La pseudo-colonne _TABLE_SUFFIX permet le partition pruning. Sans elle, BigQuery scanne toutes les tables du dataset :

-- ❌ Scanne TOUT l'historique
SELECT COUNT(*)
FROM `project.dataset.events_*`
WHERE event_name = 'purchase'

-- ✅ Scanne uniquement janvier 2024
SELECT COUNT(*)
FROM `project.dataset.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

Éviter les produits cartésiens avec plusieurs UNNEST

Unnester plusieurs arrays dans la même requête crée un produit cartésien (un événement avec 15 paramètres et 3 articles devient 45 lignes) :

-- ❌ Dangereux : 15 params × 3 items = 45 lignes par événement
SELECT ep.key, item.item_name
FROM `project.dataset.events_*`,
     UNNEST(event_params) AS ep,
     UNNEST(items) AS item
WHERE event_name = 'purchase'

Si vous avez besoin de données de plusieurs arrays, utilisez des sous-requêtes corrélées ou traitez les arrays dans des CTEs séparées :

-- ✅ OK : une ligne par item, params extraits via sous-requête
SELECT
  item.item_name,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'transaction_id') AS transaction_id
FROM `project.dataset.events_*`,
     UNNEST(items) AS item
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

Quand matérialiser des tables intermédiaires

Pour les dashboards ou les requêtes répétées, pré-unnester dans une table plate peut réduire drastiquement les coûts :

CREATE OR REPLACE TABLE `project.dataset.ga4_events_flat`
PARTITION BY event_date
CLUSTER BY event_name, session_id
AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    TIMESTAMP_MICROS(event_timestamp) AS event_timestamp,
    event_name,
    user_pseudo_id,
    CONCAT(
      user_pseudo_id, '-',
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    ) AS session_id,
    (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 = 'engagement_time_msec') AS engagement_time_msec,
    traffic_source.source,
    traffic_source.medium,
    traffic_source.name AS campaign,
    device.category AS device_category,
    device.operating_system,
    device.web_info.browser,
    geo.country,
    geo.city,
    ecommerce.transaction_id,
    ecommerce.purchase_revenue
  FROM `project.dataset.events_*`
  WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
);

Planifiez cette requête en daily pour ajouter les nouvelles données, et les requêtes en aval sur la table plate seront plus rapides et moins chères.

Patterns de modèles dbt

Les patterns SQL ci-dessus se traduisent directement en modèles dbt. Un projet dbt bien structuré sépare les responsabilités en couches base, intermediate et mart.

Couche base : l’unnesting

Le modèle base unneste les paramètres courants tout en conservant les arrays originaux pour les usages en aval :

-- models/base/ga4/base__ga4_bigquery__events.sql
{{
  config(
    materialized='incremental',
    partition_by={'field': 'event_date', 'data_type': 'date'},
    incremental_strategy='insert_overwrite',
    cluster_by=['event_name']
  )
}}

WITH source AS (
  SELECT
    event_date,
    event_timestamp,
    event_name,
    event_params,
    user_pseudo_id,
    user_properties,
    traffic_source,
    device,
    geo,
    ecommerce,
    items
  FROM {{ source('ga4', 'events') }}
  WHERE TRUE
    {% if is_incremental() %}
      AND PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) >= DATE_SUB(
        (SELECT MAX(event_date) FROM {{ this }}),
        INTERVAL 3 DAY
      )
    {% else %}
      AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
    {% endif %}
)

SELECT
  -- Clés primaires
  CONCAT(
    user_pseudo_id, '-',
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'),
    '-',
    event_timestamp
  ) AS event__key,

  CONCAT(
    user_pseudo_id, '-',
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  ) AS session__key,

  -- Timestamps
  PARSE_DATE('%Y%m%d', event_date) AS event__date,
  TIMESTAMP_MICROS(event_timestamp) AS event__timestamp,

  -- Identification de l'événement
  event_name AS event__name,
  user_pseudo_id AS user__pseudo_id,

  -- Paramètres courants (unnestés)
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS event__ga_session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS event__page_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS event__page_title,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS event__page_referrer,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS event__engagement_time_msec,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS event__session_engaged,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') AS event__entrances,

  -- Source de trafic
  traffic_source.source AS event__traffic_source,
  traffic_source.medium AS event__traffic_medium,
  traffic_source.name AS event__traffic_campaign,

  -- Device et géo
  device.category AS event__device_category,
  device.operating_system AS event__operating_system,
  device.web_info.browser AS event__browser,
  geo.country AS event__country,
  geo.city AS event__city,

  -- E-commerce (conservé imbriqué pour la couche intermediate)
  ecommerce,
  items,

  -- Arrays originaux pour extraction custom
  event_params,
  user_properties

FROM source

Macro réutilisable pour l’extraction de paramètres

-- macros/unnest_event_param.sql
{% macro unnest_event_param(param_key, value_type='string') %}
  (SELECT value.{{ value_type }}_value
   FROM UNNEST(event_params)
   WHERE key = '{{ param_key }}')
{% endmacro %}

Utilisation dans les modèles :

SELECT
  {{ unnest_event_param('page_location', 'string') }} AS event__page_location,
  {{ unnest_event_param('ga_session_id', 'int') }} AS event__ga_session_id,
  {{ unnest_event_param('value', 'double') }} AS event__value
FROM {{ source('ga4', 'events') }}

Couche intermediate : modèles par type d’événement

Séparez les types d’événements pour une consommation plus simple en aval :

-- models/intermediate/ga4/int__ga4_events__page_views.sql
SELECT
  event__key,
  session__key,
  event__date,
  event__timestamp,
  user__pseudo_id,
  event__page_location,
  event__page_title,
  event__page_referrer,
  REGEXP_REPLACE(
    REGEXP_REPLACE(event__page_location, r'^https?://[^/]+', ''),
    r'[\?#].*$', ''
  ) AS event__page_path,
  IFNULL(event__entrances, 0) = 1 AS event__is_landing_page,
  event__traffic_source,
  event__traffic_medium,
  event__device_category,
  event__country
FROM {{ ref('base__ga4_bigquery__events') }}
WHERE event__name = 'page_view'

-- models/intermediate/ga4/int__ga4_events__purchases.sql
SELECT
  event__key,
  session__key,
  event__date,
  event__timestamp,
  user__pseudo_id,
  ecommerce.transaction_id AS purchase__transaction_id,
  ecommerce.purchase_revenue AS purchase__revenue,
  ecommerce.total_item_quantity AS purchase__total_item_quantity,
  ecommerce.shipping_value AS purchase__shipping_value,
  ecommerce.tax_value AS purchase__tax_value,
  event__traffic_source,
  event__traffic_medium,
  event__device_category,
  event__country,
  items
FROM {{ ref('base__ga4_bigquery__events') }}
WHERE event__name = 'purchase'
  AND ecommerce.transaction_id IS NOT NULL

-- models/intermediate/ga4/int__ga4_events__purchase_items.sql
SELECT
  e.event__key,
  e.session__key,
  e.event__date,
  e.ecommerce.transaction_id AS purchase__transaction_id,
  item.item_id AS item__id,
  item.item_name AS item__name,
  item.item_brand AS item__brand,
  item.item_category AS item__category,
  item.item_category2 AS item__category2,
  item.item_category3 AS item__category3,
  item.price AS item__price,
  item.quantity AS item__quantity,
  item.item_revenue AS item__revenue
FROM {{ ref('base__ga4_bigquery__events') }} AS e,
     UNNEST(items) AS item
WHERE event__name = 'purchase'

Couche mart : agrégations prêtes pour l’analyse

-- models/marts/ga4/mrt__analytics__sessions.sql
{{
  config(
    materialized='table',
    partition_by={'field': 'session__date', 'data_type': 'date'},
    cluster_by=['session__traffic_source', 'session__traffic_medium']
  )
}}

SELECT
  session__key,
  ANY_VALUE(user__pseudo_id) AS user__pseudo_id,
  MIN(event__date) AS session__date,
  MIN(event__timestamp) AS session__started_at,
  MAX(event__timestamp) AS session__ended_at,
  TIMESTAMP_DIFF(MAX(event__timestamp), MIN(event__timestamp), SECOND) AS session__duration_seconds,
  COUNT(*) AS session__events,
  COUNTIF(event__name = 'page_view') AS session__pageviews,
  SUM(event__engagement_time_msec) / 1000 AS session__engagement_seconds,
  MAX(event__session_engaged) = '1' AS session__is_engaged,
  COUNTIF(event__name = 'purchase') > 0 AS session__is_converted,
  SUM(CASE WHEN event__name = 'purchase' THEN ecommerce.purchase_revenue ELSE 0 END) AS session__revenue,
  ANY_VALUE(event__traffic_source) AS session__traffic_source,
  ANY_VALUE(event__traffic_medium) AS session__traffic_medium,
  ANY_VALUE(event__traffic_campaign) AS session__traffic_campaign,
  ANY_VALUE(event__device_category) AS session__device_category,
  ANY_VALUE(event__country) AS session__country
FROM {{ ref('base__ga4_bigquery__events') }}
GROUP BY session__key

Stratégies de test

Ajoutez des tests pour détecter les erreurs UNNEST courantes :

version: 2

models:
  - name: base__ga4_bigquery__events
    columns:
      - name: event__key
        tests:
          - unique
          - not_null
      - name: session__key
        tests:
          - not_null

  - name: int__ga4_events__purchases
    columns:
      - name: purchase__transaction_id
        tests:
          - unique
          - not_null

Test custom pour vérifier l’exactitude des revenus :

-- tests/assert_revenue_not_multiplied.sql
-- Ce test échoue si le revenu par article ne correspond pas au revenu par commande
WITH order_revenue AS (
  SELECT
    purchase__transaction_id,
    purchase__revenue
  FROM {{ ref('int__ga4_events__purchases') }}
),

item_revenue AS (
  SELECT
    purchase__transaction_id,
    SUM(item__revenue) AS total_item_revenue
  FROM {{ ref('int__ga4_events__purchase_items') }}
  GROUP BY purchase__transaction_id
)

SELECT
  o.purchase__transaction_id,
  o.purchase__revenue,
  i.total_item_revenue,
  ABS(o.purchase__revenue - i.total_item_revenue) AS difference
FROM order_revenue AS o
JOIN item_revenue AS i ON o.purchase__transaction_id = i.purchase__transaction_id
WHERE ABS(o.purchase__revenue - i.total_item_revenue) > 0.01

Conclusion

Unnester les données GA4 suit des patterns prévisibles une fois qu’on a intégré deux concepts clés : les sous-requêtes corrélées pour extraire des paramètres précis sans exploser le nombre de lignes, et le choix du bon type de JOIN pour les arrays qui peuvent être vides.

Les patterns SQL de cet article couvrent la grande majorité des cas d’analyse GA4. Pour les cas particuliers, gardez en tête que la structure imbriquée de BigQuery est une feature optimisée précisément pour ce type de données événementielles semi-structurées. Travailler avec plutôt que contre donne de meilleures performances.

Dans l’article suivant, nous déploierons ces patterns en production avec un template de projet dbt complet : stratégies de chargement incrémental, orchestration et monitoring de la qualité des données. La structure base-intermediate-mart introduite ici servira de fondation.