Pattern UNNEST des items e-commerce GA4

Les données e-commerce GA4 résident dans le champ items — un REPEATED RECORD contenant un élément par produit dans un événement donné. Un seul événement purchase pour un panier de trois produits produit trois éléments dans le tableau items. Construire une analyse e-commerce précise nécessite de développer ce tableau en un modèle séparé au grain item.

Pourquoi un modèle séparé

Le reste de votre pipeline GA4 fonctionne au grain événement : une ligne par événement. Le modèle d’événements sessionisés maintient ce grain soigneusement — des sous-requêtes corrélées pour event_params spécifiquement parce qu’elles ne développent pas les lignes.

items est différent. Vous avez véritablement besoin d’une ligne par item par événement. Un achat avec trois produits a besoin de trois lignes pour suivre correctement le chiffre d’affaires, la quantité et la catégorie de chaque produit. Conserver les items intégrés dans la table au grain événement n’est pas faisable — vous devriez les agréger en tableaux ou colonnes dénormalisées, perdant la capacité d’interroger par produit.

La solution est un modèle intermédiaire dédié au grain item, matérialisé comme une vue (peu coûteux car il référence un modèle de base incrémental déjà matérialisé).

Le modèle

-- models/intermediate/ga4/int__ga4__event_items.sql

{{
    config(
        materialized='view'
    )
}}

WITH events_with_items AS (

    SELECT
        event__key,
        event__date,
        event__timestamp_utc,
        user__pseudo_id,
        session__key,
        event__name,
        transaction__id,
        items__array
    FROM {{ ref('base__ga4__events') }}
    WHERE event__name IN ('purchase', 'add_to_cart', 'remove_from_cart',
                         'view_item', 'begin_checkout', 'add_payment_info')
        AND ARRAY_LENGTH(items__array) > 0

)

SELECT
    e.event__key,
    e.event__date,
    e.event__timestamp_utc,
    e.user__pseudo_id,
    e.session__key,
    e.event__name,
    e.transaction__id,

    -- Détails de l'item
    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.item_variant AS item__variant,
    item.price AS item__price,
    item.quantity AS item__quantity,
    item.coupon AS item__coupon,
    item.item_list_name AS item__list_name,
    item.item_list_index AS item__list_index,

    -- Calculer le chiffre d'affaires de l'item
    COALESCE(item.price, 0) * COALESCE(item.quantity, 1) AS item__revenue

FROM events_with_items e,
UNNEST(e.items__array) AS item

Ce modèle utilise intentionnellement le pattern UNNEST cartésien (FROM events_with_items e, UNNEST(e.items__array) AS item) plutôt qu’une sous-requête corrélée. L’expansion des lignes est exactement ce que vous souhaitez : une ligne par item par événement.

Le filtre sur les événements

Tous les événements GA4 ne portent pas de données d’items. Le filtre event__name IN ('purchase', 'add_to_cart', ...) fait deux choses :

Réduit significativement la taille des données d’entrée — seuls les événements e-commerce ont des items
Rend la finalité du modèle explicite

La garde ARRAY_LENGTH(items__array) > 0 empêche UNNEST de produire des lignes avec un item null lorsque le tableau est vide. Certaines implémentations GA4 envoient des événements e-commerce sans tableaux d’items — cela empêche ces cas d’apparaître dans le modèle comme des lignes avec tous les champs d’item nuls.

Relier les items aux sessions

Le modèle d’items préserve event__key et session__key depuis le modèle de base. Cela rend les jointures naturelles :

-- Chiffre d'affaires par item et canal de session
SELECT
    s.session__channel_grouping,
    SUM(i.item__revenue) AS total_revenue,
    COUNT(DISTINCT i.transaction__id) AS transactions
FROM mrt__analytics__sessions s
JOIN int__ga4__event_items i
    ON s.session__key = i.session__key
WHERE i.event__name = 'purchase'
GROUP BY 1
ORDER BY 2 DESC

Le modèle d’événements sessionisés agrège les données d’items au niveau événement :

-- Dans int__ga4__events_sessionized
event_items AS (
    SELECT
        event__key,
        COUNT(*) AS event__items,
        SUM(item__revenue) AS event__items_revenue
    FROM {{ ref('int__ga4__event_items') }}
    GROUP BY event__key
)

Cette pré-agrégation évite les jointures répétées en aval. La table sessionisée porte event__items et event__items_revenue comme simples colonnes entière et flottante sur chaque événement d’achat — aucune jointure requise pour les métriques e-commerce standard.

Catégories d’items dans GA4

GA4 prend en charge une hiérarchie de catégories produit à cinq niveaux (item_category à item_category5). En pratique, la plupart des implémentations n’utilisent que les deux ou trois premiers niveaux. Le modèle extrait les trois niveaux significatifs ; ajustez selon ce que votre implémentation de tracking renseigne effectivement.

Depuis octobre 2023, GA4 prend également en charge item_params — un champ repeated imbriqué dans chaque item pour les dimensions produit personnalisées. Cela ajoute une autre couche de complexité UNNEST si votre implémentation utilise des paramètres d’items personnalisés. Gérez-le avec un CTE d’extraction séparé ou un modèle supplémentaire si nécessaire.

Choix de matérialisation : vue

Le modèle d’items est une vue, pas une table incrémentale. C’est intentionnel :

Coût de stockage évité : Le modèle de base est déjà matérialisé comme table incrémentale. Une vue au-dessus n’ajoute aucun coût de stockage.
Fraîcheur automatique : La vue reflète toujours l’état le plus récent du modèle de base. Pas de logique incrémentale nécessaire.
Coût de requête acceptable : Les requêtes sur les items e-commerce sont généralement exécutées sur des plages de dates bornées. Le partition pruning BigQuery sur le modèle de base limite les coûts de scan indépendamment de la couche vue.

Si les requêtes au niveau item deviennent coûteuses à grande échelle, matérialisez la vue comme table incrémentale avec le même pattern de lookback que le modèle de base. Mais commencez par une vue — la simplicité en vaut la peine.