Architecture de la couche UNNEST dbt pour GA4

Les patterns SQL pour le UNNEST des données GA4 se traduisent directement en modèles dbt. Un projet dbt bien structuré sépare les préoccupations GA4 en couches base, intermédiaire et mart, chaque couche gérant une responsabilité spécifique. La couche base effectue le UNNEST. La couche intermédiaire filtre et remodèle. La couche mart agrège.

Cette séparation est importante car le UNNEST GA4 est coûteux et répétitif. Si chaque modèle mart accède indépendamment à event_params avec des sous-requêtes corrélées, vous payez le coût de scan imbriqué pour chaque table en aval. Concentrez le UNNEST en un seul endroit, matérialisez-le, et laissez tout ce qui est en aval travailler avec des colonnes plates.

Couche de base : le modèle UNNEST

Le modèle de base fait deux choses : convertit les tables shardées en table partitionnée, et extrait les paramètres fréquemment utilisés des tableaux imbriqués.

-- 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 (déimbriqué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éographie
  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 (conserver imbriqué pour la couche intermédiaire)
  ecommerce,
  items,

  -- Conserver les tableaux originaux pour extraction personnalisée
  event_params,
  user_properties

FROM source

Plusieurs décisions de conception méritent explication :

event__key inclut event_timestamp. GA4 ne fournit pas d’identifiant d’événement unique. La composition utilisateur + session + timestamp est l’approximation la plus proche. Elle n’est pas parfaitement unique — deux événements du même utilisateur dans la même session à la même microseconde produisent une collision — mais elle est suffisante pour la plupart des usages pratiques.

La fenêtre de lookback de 3 jours dans la logique incrémentale retraite les partitions récentes pour capturer les données tardives de GA4. GA4 peut prendre jusqu’à 72 heures pour finaliser les tables quotidiennes. Sans le lookback, vous manqueriez les événements qui arrivent après l’export initial.

Les champs ecommerce, items, event_params et user_properties sont préservés. Le modèle de base aplatit les paramètres les plus courants mais conserve les structures imbriquées originales pour les modèles en aval qui ont besoin d’accéder à des paramètres moins courants ou au tableau items. Cela évite de devoir rejoindre la source brute.

La macro d’extraction de paramètres

Les sous-requêtes corrélées ci-dessus sont répétitives. Une macro réutilisable rend le modèle de base plus lisible :

-- 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 :

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') }}

Pour les paramètres dont les types sont incertains, utilisez la variante COALESCE.

Couche intermédiaire : modèles spécifiques aux événements

La couche intermédiaire décompose des types d’événements spécifiques pour une consommation en aval plus facile. Chaque modèle filtre la table de base sur un seul type d’événement et applique des transformations spécifiques à cet événement :

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

Le modèle des items d’achat utilise le pattern UNNEST des items à cette couche :

-- 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'

C’est l’un des rares endroits où UNNEST en clause FROM est intentionnel — le modèle au grain item existe spécifiquement pour développer le tableau items. Le CROSS JOIN est sûr ici car les événements d’achat ont toujours des items.

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

La couche mart agrège en tables prêtes pour le métier :

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

Notez que le mart référence le modèle de base directement, pas les modèles intermédiaires. Le mart de sessions a besoin de tous les événements (pas seulement les pages vues ou les achats), donc il groupe la couche de base par session__key. Les modèles intermédiaires sont les plus utiles pour les marts spécifiques aux événements (performance des pages, performance des produits) ou lorsque d’autres marts se joignent à eux.

Tester le UNNEST

Ajoutez des tests qui détectent les erreurs courantes de UNNEST :

version: 2

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

L’erreur de UNNEST la plus insidieuse en e-commerce est la multiplication du chiffre d’affaires. Un test personnalisé la détecte :

-- tests/assert_revenue_not_multiplied.sql
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

Ce test échoue si la somme du chiffre d’affaires au niveau item ne correspond pas au chiffre d’affaires au niveau commande — un signal fort que quelque chose dans la logique UNNEST duplique ou perd des lignes.

La convention de nommage

Le préfixe double underscore dans les noms de colonnes (event__page_location, session__key, purchase__revenue) sert deux objectifs. Il espace les colonnes par leur grain ou domaine, rendant évident ce que chaque colonne représente lorsqu’on la voit dans une requête en aval. Et il évite les collisions de nommage lorsque plusieurs modèles sont joints — event__date et session__date peuvent coexister sans ambiguïté.

Cette convention est un choix, pas une exigence. La cohérence au sein du projet importe plus que le schéma spécifique ; quel que soit le pattern choisi, il doit être appliqué uniformément à travers tous les modèles GA4.