ServicesÀ proposNotesContact Me contacter →
EN FR
Note

Matérialisation des événements GA4 aplatis

Quand et comment pré-désimbriquer les événements GA4 dans une table plate — le compromis coût-performance, le pattern CREATE TABLE, et pourquoi les modèles dbt formalisent cette approche.

Planté
ga4bigquerydata engineeringcost optimization

La pré-désimbrication des paramètres GA4 dans une table plate réduit le coût répété des requêtes et améliore la compatibilité avec les outils BI. Le pattern : désimbriquer les paramètres nécessaires dans une table matérialisée unique, puis diriger toutes les requêtes en aval vers la version plate. Pour l’exploration ad hoc, il suffit d’interroger les tables imbriquées brutes ; la matérialisation est rentable lorsque plusieurs consommateurs interrogent les mêmes données de manière répétée.

Le pattern de base

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

Cela crée une table partitionnée et clusterisée avec les paramètres les plus couramment interrogés extraits en colonnes plates. Les requêtes en aval contre ga4_events_flat sont du SQL standard — pas de UNNEST, pas de sous-requêtes corrélées, pas d’accès à des champs imbriqués.

Pourquoi cela fonctionne

Les économies de coûts se cumulent car :

  1. L’UNNEST n’a lieu qu’une seule fois. Dix panneaux de tableau de bord ciblant la même plage de dates ne paient pas chacun le coût de la requête imbriquée. Ils interrogent des colonnes plates.
  2. Le partitionnement permet l’élagage. Les requêtes filtrées par event_date n’analysent que les partitions pertinentes, comme les tables nativement partitionnées.
  3. Le clustering sur event_name et session_id accélère les patterns de requête les plus courants : filtrage par type d’événement et regroupement par session.
  4. Les outils BI fonctionnent mieux avec les schémas plats. Looker Studio, Metabase et la plupart des outils BI peinent avec les champs imbriqués/répétés. Une table plate élimine le besoin de SQL personnalisé dans chaque visualisation.

Le compromis est le stockage. Les données sont dupliquées par rapport à celles déjà présentes dans les tables brutes. Pour 90 jours d’un site à trafic moyen, la table plate représente 30 à 50 % de la taille des tables brutes (on extrait un sous-ensemble de paramètres, pas tous). Avec la tarification de stockage de BigQuery, cela est généralement négligeable par rapport aux économies de coût des requêtes.

Planification du rafraîchissement

L’approche CREATE OR REPLACE ci-dessus reconstruit l’intégralité de la fenêtre de 90 jours à chaque exécution. Pour une requête planifiée quotidienne, cela fonctionne mais n’est pas optimal — 89 jours de données inchangées sont retraités.

Mieux : utiliser INSERT avec un filtre de date pour les ajouts quotidiens, plus une reconstruction complète périodique pour capturer les données arrivant en retard :

-- Ajout quotidien (exécuté quotidiennement via requête planifiée ou Cloud Scheduler)
INSERT INTO `project.dataset.ga4_events_flat`
SELECT
  PARSE_DATE('%Y%m%d', event_date) AS event_date,
  TIMESTAMP_MICROS(event_timestamp) AS event_timestamp,
  -- ... mêmes colonnes qu'au-dessus ...
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

L’ajout quotidien ne traite que les données d’hier. Exécuter une reconstruction complète hebdomadaire (DELETE + INSERT pour les 90 derniers jours) pour gérer la fenêtre de traitement des données en retard de 72 heures de GA4 et toute correction de schéma.

La version dbt

C’est exactement ce pattern qu’un modèle incrémentiel dbt formalise. La couche de base dans un projet dbt fait la même chose avec de meilleures garde-fous :

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

La stratégie insert_overwrite remplace des partitions entières, ce qui gère proprement les données arrivant en retard. La fenêtre de lookback (généralement 3 jours) retraite les partitions récentes pour capturer les données arrivées après l’export initial. C’est plus robuste que l’approche manuelle CREATE OR REPLACE et s’intègre au reste de votre DAG dbt.

Si vous utilisez déjà dbt, utilisez un modèle incrémentiel. Sinon, l’approche par requête planifiée fonctionne. L’idée clé est la même dans les deux cas : désimbriquer une seule fois, interroger en mode plat.

Quoi inclure dans la table plate

Soyez sélectif. Incluez les paramètres que vous interrogez fréquemment ; excluez les paramètres dont vous avez rarement besoin. L’objectif n’est pas d’aplatir tout — c’est d’aplatir les 80 % de paramètres qui représentent 95 % de vos requêtes.

Candidats courants :

  • page_location, page_title, page_referrer (analyse de contenu)
  • ga_session_id, session_engaged, engagement_time_msec (analyse de session)
  • entrances (identification des pages d’atterrissage)
  • Champs de device, géo et source de trafic (segmentation)
  • ecommerce.transaction_id, ecommerce.purchase_revenue (e-commerce)

Conservez les tableaux event_params et items originaux dans la table plate si vous avez besoin d’un accès occasionnel aux paramètres que vous n’avez pas extraits. Cela augmente le coût de stockage mais évite de devoir rejoindre les tables brutes pour les requêtes de cas limites.

Si vous êtes dans un projet dbt, c’est le rôle du modèle de base. Les couches intermédiaires et mart s’appuient ensuite sur la base plate sans jamais toucher à nouveau aux tableaux imbriqués.

Quand NE PAS matérialiser

Pour l’analyse ponctuelle ou l’exploration, il est parfaitement acceptable d’interroger directement les tables imbriquées brutes. Les charges liées à la matérialisation (stockage, planification, maintenance) ne sont rentables que lorsque :

  • Plusieurs consommateurs interrogent les mêmes données de manière répétée
  • Les outils BI se connectent directement à la table
  • Les coûts de requête sont devenus un poste budgétaire valant la peine d’être optimisé
  • Vous construisez un pipeline multicouche où l’aplatissement en amont bénéficie à chaque modèle en aval

Si vous êtes le seul analyste et que vous exécutez des requêtes ad hoc quelques fois par semaine, les tables brutes avec un bon filtrage _TABLE_SUFFIX sont suffisantes. Matérialisez lorsque le coût répété ou la compatibilité avec les outils BI l’impose.