L’export BigQuery de GA4 ne ressemble en rien à Universal Analytics. Au lieu d’une ligne par session, vous obtenez une ligne par événement. Au lieu de colonnes propres, vous avez des tableaux imbriqués. Au lieu d’une table de sessions, vous n’avez rien. Les sessions n’existent pas dans les données brutes ; c’est à vous de les construire.

La plupart des tutoriels vous apprennent à agréger en sessions immédiatement. Ça fonctionne pour des dashboards basiques, mais ça tue votre flexibilité. Vous voulez analyser la séquence d’événements menant à un achat ? Impossible avec des données au niveau session. Besoin d’expérimenter différentes fenêtres d’attribution ? Trop tard, l’agrégation a déjà eu lieu.

Ce tutoriel adopte une approche différente. Nous allons construire un modèle intermédiaire large qui enrichit chaque événement avec le contexte de session (flags de conversion, métriques de timing, données d’attribution) sans perdre la granularité événementielle. L’agrégation n’intervient que dans la couche mart finale, quand vous en avez vraiment besoin.

À la fin, vous aurez un pattern réutilisable qui fonctionne pour n’importe quelle propriété GA4.

Pré-requis

Avant de commencer, vous aurez besoin de :

• Un projet dbt connecté à BigQuery
• L’export BigQuery GA4 activé avec au moins quelques jours de données
• Une connaissance en SQL avec les CTEs, JOINs et window functions

Pas encore d’export GA4 ? Google fournit un dataset de démonstration public qui fonctionne parfaitement pour ce tutoriel :

bigquery-public-data.ga4_obfuscated_sample_ecommerce

Tout ce que nous construisons fonctionne avec.

Comprendre le schéma GA4

Une ligne = un événement (pas une session)

C’est le changement fondamental par rapport à Universal Analytics. Chaque ligne représente un événement unique : une page vue, un clic sur un bouton, un achat. La session d’un utilisateur peut générer des dizaines de lignes.

Voici à quoi ressemblent quelques lignes :

L’implication : vous construisez les sessions à partir des événements. Elles ne vous sont pas fournies.

Le tableau imbriqué event_params

C’est là que GA4 stocke la plupart des données utiles, et c’est la structure qui déroute la plupart des débutants.

event_params est un enregistrement répété (ARRAY de STRUCTs en termes BigQuery). Chaque élément a une key (chaîne) et une value (un struct imbriqué avec quatre champs de type) :

Un seul champ de type est rempli par paramètre ; les autres sont NULL. Cela signifie que vous devez savoir quel type extraire. Paramètres clés à connaître :

• ga_session_id : int_value (nous en reparlerons bientôt)
• ga_session_number : int_value (compteur séquentiel de sessions par utilisateur)
• page_location : string_value
• page_title : string_value
• engagement_time_msec : int_value

Note : float_value est essentiellement inutilisé par GA4. Pour les nombres décimaux, regardez double_value.

Nommage des tables : daily vs intraday

GA4 crée deux types de tables dans votre dataset BigQuery (analytics_<property_id>) :

• events_YYYYMMDD : Tables d’export quotidiennes. Elles peuvent être mises à jour pendant 72 heures au fur et à mesure que les événements tardifs arrivent.
• events_intraday_YYYYMMDD : Tables de streaming pour les données en temps réel. Supprimées automatiquement quand la table quotidienne est finalisée.

Pour ce tutoriel, nous utiliserons uniquement les tables quotidiennes. Elles ont des données complètes et incluent les informations de source de trafic que les tables intraday n’ont pas.

Timestamps et fuseaux horaires

Voici un piège subtil. GA4 vous donne deux champs temporels :

• event_timestamp : Microsecondes depuis l’époque Unix, toujours en UTC
• event_date : Une chaîne dans le fuseau horaire configuré de votre propriété GA4

Si vous comparez ces valeurs sans conversion, vous obtiendrez des heures de décalage. Pour une propriété configurée sur Paris, un événement à minuit heure locale affiche 20240115 dans event_date mais correspond à 23:00 le 14 janvier en UTC.

Pour convertir :

-- Microsecondes vers timestamp
TIMESTAMP_MICROS(event_timestamp) AS ga4__event__timestamp

-- Convertir vers le fuseau horaire local
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Europe/Paris') AS ga4__event__timestamp_local

Le piège de la Session ID

Pourquoi ga_session_id seul est dangereux

C’est l’erreur la plus courante que je vois. Ça semble logique : GA4 fournit un paramètre ga_session_id, donc utilisez-le pour identifier les sessions. Mais il y a un problème.

ga_session_id est en réalité un timestamp Unix (le moment où la session a commencé). Sur un site avec du trafic, plusieurs utilisateurs peuvent démarrer des sessions dans la même seconde et se voir attribuer le même session ID.

Prouvons-le. Exécutez cette requête sur vos données GA4 :

SELECT
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    COUNT(DISTINCT user_pseudo_id) AS user_count
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
GROUP BY 1
HAVING COUNT(DISTINCT user_pseudo_id) > 1
ORDER BY user_count DESC
LIMIT 10

Sur n’importe quel site avec un trafic raisonnable, vous trouverez des session IDs partagés par plusieurs utilisateurs. Utiliser ga_session_id seul pour l’analyse de sessions signifie que vous fusionnez accidentellement les sessions de différents utilisateurs.

Construire la bonne session key

Combinez user_pseudo_id (qui est unique par navigateur/appareil) avec ga_session_id :

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

Cela devient la colonne vertébrale de tout ce que nous construisons. Chaque modèle utilisera ce pattern.

Une note : ga_session_number est utile pour l’analyse “est-ce un nouvel utilisateur ou un utilisateur récurrent ?” (session 1 = nouvel utilisateur), mais n’est pas nécessaire pour l’identification des sessions.

La structure des modèles dbt

Notre approche à trois couches

Nous allons construire trois modèles :

1. Base (base__ga4__events) : Extraire les champs des structures imbriquées, typer, créer la session key. Toujours une ligne par événement.
2. Intermediate (int__event__sessionized) : Enrichir chaque événement avec le contexte de session (flags de conversion, métriques de timing, attribution). Toujours une ligne par événement.
3. Mart (mrt__reporting__ga4__acquisition_performance) : Agréger au grain jour × canal. Une ligne par jour par source/medium.

┌─────────────────┐     ┌─────────────────────────┐     ┌─────────────┐
│   Raw GA4       │     │   Intermediate          │     │   Mart      │
│   Events        │     │   Events + Session      │     │   Daily ×   │
│                 │────▶│   Context               │────▶│   Channel   │
│   1M rows       │     │   1M rows (enriched)    │     │   ~500 rows │
│   (events)      │     │   (events)              │     │   (per day) │
└─────────────────┘     └─────────────────────────┘     └─────────────┘

Mon point de vue là-dessus

La plupart des packages dbt GA4 (y compris le populaire package Velir/dbt-ga4) agrègent dans la couche base ou juste après. Cela simplifie les choses, mais vous perdez des capacités :

• Funnels de séquence d’événements : “Quel pourcentage d’utilisateurs qui voient un produit l’ajoutent au panier dans les 60 secondes ?” Nécessite des données au niveau événement.
• Analyse du temps entre événements : “Quel est le temps médian entre add_to_cart et purchase ?” Nécessite les timestamps d’événements.
• Expérimentations d’attribution personnalisée : “Et si on attribuait les conversions à la page vue 3 événements avant au lieu du dernier clic ?” Nécessite la granularité événementielle.

Mon approche : différer l’agrégation jusqu’à ce que vous en ayez vraiment besoin. Le modèle intermédiaire est le cheval de bataille, une table large où chaque événement connaît son contexte de session.

Construire le modèle Base

Définition de la source

D’abord, définissez la source dans models/base/ga4/_sources.yml :

version: 2

sources:
  - name: ga4
    database: your-project  # ou bigquery-public-data pour le dataset de démo
    schema: analytics_123456789  # ou ga4_obfuscated_sample_ecommerce
    tables:
      - name: events
        identifier: events_*
        description: Tables d'export d'événements GA4 (partitionnées par date)

Le modèle base__ga4__events

Créez models/base/ga4/base__ga4__events.sql :

{{ config(
    materialized='table',
    partition_by={
        "field": "ga4__event__date",
        "data_type": "date",
        "granularity": "day"
    },
    cluster_by=['ga4__event__user_pseudo_id', 'ga4__event__name'],
    tags=['base', 'ga4']
) }}

SELECT
    -- Session key (l'identifiant critique)
    CONCAT(
        user_pseudo_id,
        '-',
        CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    ) AS ga4__event__session_key,

    -- Identifiant utilisateur
    user_pseudo_id AS ga4__event__user_pseudo_id,

    -- Timestamps
    PARSE_DATE('%Y%m%d', event_date) AS ga4__measured_at,
    TIMESTAMP_MICROS(event_timestamp) AS ga4__event__timestamp,

    -- Détails de l'événement
    event_name AS ga4__event__name,

    -- Paramètres d'événements extraits
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga4__event__ga_session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga4__event__session_number,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS ga4__event__page_location,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS ga4__event__page_title,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS ga4__event__engagement_time_msec,

    -- Ecommerce (pour les événements purchase)
    ecommerce.transaction_id AS ga4__event__transaction_id,
    ecommerce.purchase_revenue AS ga4__event__purchase_revenue_usd,

    -- Source de trafic (premier contact au niveau utilisateur)
    traffic_source.source AS ga4__event__user_source,
    traffic_source.medium AS ga4__event__user_medium,
    traffic_source.name AS ga4__event__user_campaign,

    -- Source de trafic de session (dernier clic non direct)
    -- Disponible à partir de juillet 2024
    session_traffic_source_last_click.manual_campaign.source AS ga4__event__session_source,
    session_traffic_source_last_click.manual_campaign.medium AS ga4__event__session_medium,
    session_traffic_source_last_click.manual_campaign.campaign_name AS ga4__event__session_campaign,

    -- Appareil et géographie
    device.category AS ga4__event__device_category,
    device.operating_system AS ga4__event__operating_system,
    geo.country AS ga4__event__country

FROM {{ source('ga4', 'events') }}
WHERE _TABLE_SUFFIX >= '20260101'  -- Ajustez selon vos besoins

Quelques points à noter :

• Le nommage des colonnes suit source__entity__field, donc chaque colonne identifie clairement son origine (ga4__event__page_location).
• Nous créons ga4__event__session_key immédiatement : C’est notre identifiant de session unique.
• Le pattern de sous-requête (SELECT value.x FROM UNNEST(event_params) WHERE key = 'y') extrait les paramètres sans multiplier les lignes.
• Matérialisé en table avec partitionnement pour la performance des requêtes.

Gérer la table wildcard

Le pattern events_* correspond à toutes les tables partitionnées par date. La pseudo-colonne _TABLE_SUFFIX vous permet de filtrer des dates spécifiques efficacement ; BigQuery ne scanne que les partitions dont vous avez besoin.

Pour les modèles incrémentaux (couverts dans un futur article), vous filtreriez comme ceci :

{% if is_incremental() %}
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
{% endif %}

Le lookback de 3 jours gère les événements tardifs.

Construire le modèle Intermediate

Nous allons ajouter le contexte au niveau session à chaque événement sans agréger.

La philosophie : enrichir, pas agréger

Le résultat est une table large où chaque ligne d’événement contient des colonnes comme :

• event__session_has_purchase (booléen)
• event__session_has_add_to_cart (booléen)
• event__time_in_session_seconds (entier)
• event__session_event_number (entier)

Cela vous permet de filtrer tous les événements des sessions qui ont converti, calculer le temps jusqu’à conversion, ou séquencer les événements sans jointures.

Le modèle int__event__sessionized

Créez models/intermediate/event/int__event__sessionized.sql :

{{ config(
    materialized='table',
    partition_by={
        "field": "ga4__event__date",
        "data_type": "date",
        "granularity": "day"
    },
    cluster_by=['ga4__event__session_key', 'ga4__event__user_pseudo_id'],
    tags=['intermediate', 'event', 'ga4']
) }}

-- IMPORT: Importer les événements de base
WITH events AS (
    SELECT * FROM {{ ref('base__ga4__events') }}
),

-- TRANSFORMATION: Calculer les flags au niveau session
session_flags AS (
    SELECT
        ga4__event__session_key,
        MAX(CASE WHEN ga4__event__name = 'purchase' THEN 1 ELSE 0 END) AS has_purchase,
        MAX(CASE WHEN ga4__event__name = 'add_to_cart' THEN 1 ELSE 0 END) AS has_add_to_cart,
        MAX(CASE WHEN ga4__event__name = 'begin_checkout' THEN 1 ELSE 0 END) AS has_begin_checkout,
        MAX(CASE WHEN ga4__event__name = 'view_item' THEN 1 ELSE 0 END) AS has_view_item
    FROM events
    GROUP BY ga4__event__session_key
),

-- BUSINESS LOGIC: Ajouter timing et séquençage avec les window functions
enriched AS (
    SELECT
        e.*,

        -- Flags de session (en booléens)
        sf.has_purchase = 1 AS event__session_has_purchase,
        sf.has_add_to_cart = 1 AS event__session_has_add_to_cart,
        sf.has_begin_checkout = 1 AS event__session_has_begin_checkout,
        sf.has_view_item = 1 AS event__session_has_view_item,

        -- Timing de session
        FIRST_VALUE(e.ga4__event__timestamp) OVER (
            PARTITION BY e.ga4__event__session_key
            ORDER BY e.ga4__event__timestamp
        ) AS event__session_start_timestamp,

        TIMESTAMP_DIFF(
            e.ga4__event__timestamp,
            FIRST_VALUE(e.ga4__event__timestamp) OVER (
                PARTITION BY e.ga4__event__session_key
                ORDER BY e.ga4__event__timestamp
            ),
            SECOND
        ) AS event__time_in_session_seconds,

        -- Séquençage des événements
        ROW_NUMBER() OVER (
            PARTITION BY e.ga4__event__session_key
            ORDER BY e.ga4__event__timestamp
        ) AS event__session_event_number,

        -- Compteurs d'événements de session (pour le contexte)
        COUNT(*) OVER (PARTITION BY e.ga4__event__session_key) AS event__session_event_count

    FROM events e
    LEFT JOIN session_flags sf ON e.ga4__event__session_key = sf.ga4__event__session_key
)

SELECT * FROM enriched

Ce que les flags booléens permettent

Avec event__session_has_purchase comme colonne sur chaque événement, vous pouvez maintenant écrire :

-- Toutes les pages vues des sessions qui ont finalement converti
SELECT *
FROM {{ ref('int__event__sessionized') }}
WHERE ga4__event__name = 'page_view'
    AND event__session_has_purchase

Sans ce pattern, vous auriez besoin d’une sous-requête ou CTE pour identifier d’abord les sessions converties, puis faire une jointure.

Colonnes de timing de session

Les window functions calculent :

• event__session_start_timestamp : Quand la session a commencé (premier événement).
• event__time_in_session_seconds : Combien de temps après le début de session cet événement s’est produit.
• event__session_event_number : Position dans la session (1er, 5ème, 20ème événement).

Cas d’usage : “Quel est le temps moyen entre la première page vue et add_to_cart ?”

SELECT
    AVG(event__time_in_session_seconds) AS avg_seconds_to_add_to_cart
FROM {{ ref('int__event__sessionized') }}
WHERE ga4__event__name = 'add_to_cart'
    AND event__session_event_number > 1  -- Exclure les sessions où add_to_cart était le premier événement

Colonnes d’attribution de session

Nous avons déjà extrait ga4__event__session_source, ga4__event__session_medium, et ga4__event__session_campaign dans le modèle base depuis le champ session_traffic_source_last_click. Cela vous donne l’attribution au dernier clic non direct au niveau session.

Pour les données avant juillet 2024 (quand ce champ a été introduit), vous auriez besoin d’un fallback :

COALESCE(
    session_traffic_source_last_click.manual_campaign.source,
    collected_traffic_source.manual_source,
    '(direct)'
) AS ga4__event__session_source

Une note sur les modèles d’attribution :

• traffic_source : Premier contact au niveau utilisateur (la source qui a originellement acquis cet utilisateur).
• session_traffic_source_last_click : Dernier clic non direct au niveau session (correspond au rapport Traffic Acquisition de l’interface GA4).
• collected_traffic_source : Paramètres UTM bruts sans logique d’attribution appliquée.

Pour la plupart des analyses, session_traffic_source_last_click est ce que vous voulez.

Le résultat

Chaque ligne d’événement a maintenant plus de 10 colonnes supplémentaires fournissant le contexte de session. Le modèle est toujours au niveau événement (même nombre de lignes que la base) mais beaucoup plus utile pour l’analyse.

Construire le modèle Mart

Maintenant nous agrégeons. Le modèle intermédiaire a fait le gros du travail ; le modèle mart est simple.

Les marts doivent être légers, agrégés à un grain utile pour les dashboards, pas une ligne par session (ce qui serait presque aussi volumineux que l’intermédiaire). Pour l’analyse d’acquisition, nous allons agréger au niveau jour × source × medium.

mrt__reporting__ga4__acquisition_performance

Créez models/mart/reporting/ga4/mrt__reporting__ga4__acquisition_performance.sql :

{{ config(
    materialized='table',
    partition_by={
        "field": "acquisition__date",
        "data_type": "date",
        "granularity": "day"
    },
    cluster_by=['acquisition__source', 'acquisition__medium'],
    tags=['mart', 'reporting', 'ga4']
) }}

-- IMPORT: Importer les événements sessionisés
WITH sessionized_events AS (
    SELECT * FROM {{ ref('int__event__sessionized') }}
),

-- TRANSFORMATION: D'abord dédupliquer au niveau session
sessions AS (
    SELECT
        ga4__event__session_key,
        ga4__event__user_pseudo_id,
        MIN(ga4__event__date) AS session_date,
        MAX(ga4__event__session_source) AS session_source,
        MAX(ga4__event__session_medium) AS session_medium,
        MAX(ga4__event__session_campaign) AS session_campaign,
        MAX(CAST(event__session_has_purchase AS INT64)) AS has_purchase,
        MAX(CAST(event__session_has_add_to_cart AS INT64)) AS has_add_to_cart,
        MAX(CAST(event__session_has_begin_checkout AS INT64)) AS has_begin_checkout,
        SUM(ga4__event__purchase_revenue_usd) AS session_revenue_usd,
        COUNT(*) AS event_count
    FROM sessionized_events
    GROUP BY ga4__event__session_key, ga4__event__user_pseudo_id
),

-- AGGREGATION: Au grain jour × source × medium
aggregated AS (
    SELECT
        session_date AS acquisition__date,
        COALESCE(session_source, '(direct)') AS acquisition__source,
        COALESCE(session_medium, '(none)') AS acquisition__medium,
        COALESCE(session_campaign, '(not set)') AS acquisition__campaign,

        -- Métriques de volume
        COUNT(*) AS acquisition__session_count,
        COUNT(DISTINCT ga4__event__user_pseudo_id) AS acquisition__user_count,

        -- Métriques de conversion
        SUM(has_purchase) AS acquisition__purchase_count,
        SUM(has_add_to_cart) AS acquisition__add_to_cart_count,
        SUM(has_begin_checkout) AS acquisition__begin_checkout_count,

        -- Revenu
        SUM(session_revenue_usd) AS acquisition__revenue_usd,

        -- Engagement
        AVG(event_count) AS acquisition__avg_events_per_session

    FROM sessions
    GROUP BY 1, 2, 3, 4
),

-- ENRICHMENT: Calculer les taux
enriched AS (
    SELECT
        *,
        SAFE_DIVIDE(acquisition__purchase_count, acquisition__session_count) AS acquisition__conversion_rate,
        SAFE_DIVIDE(acquisition__revenue_usd, acquisition__session_count) AS acquisition__revenue_per_session_usd,
        SAFE_DIVIDE(acquisition__revenue_usd, acquisition__purchase_count) AS acquisition__avg_order_value_usd
    FROM aggregated
)

SELECT * FROM enriched

Ce mart est prêt pour les dashboards :

• ~500 lignes par jour au lieu de 50K sessions, donc les requêtes sont rapides et le stockage minimal.
• Taux pré-calculés : Taux de conversion, revenu par session, AOV.
• Partitionné par date : Filtrez sur les 30 derniers jours et BigQuery ne scanne que 30 partitions.
• Clusterisé par source/medium : Les patterns de filtrage courants sont optimisés.

Pourquoi la couche intermédiaire reste importante

Le mart vous donne des métriques de performance agrégées. Mais disons que vous voulez trouver “les sessions où l’utilisateur a vu un produit, puis l’a ajouté au panier, puis a acheté, dans cet ordre.”

Avec seulement le mart : Impossible. Le détail au niveau session a disparu.

Avec int__event__sessionized :

WITH sequenced AS (
    SELECT
        ga4__event__session_key,
        MAX(CASE WHEN ga4__event__name = 'view_item' THEN event__session_event_number END) AS view_item_position,
        MAX(CASE WHEN ga4__event__name = 'add_to_cart' THEN event__session_event_number END) AS add_to_cart_position,
        MAX(CASE WHEN ga4__event__name = 'purchase' THEN event__session_event_number END) AS purchase_position
    FROM {{ ref('int__event__sessionized') }}
    WHERE event__session_has_purchase
    GROUP BY ga4__event__session_key
)

SELECT COUNT(*) AS proper_funnel_sessions
FROM sequenced
WHERE view_item_position < add_to_cart_position
    AND add_to_cart_position < purchase_position

Le modèle intermédiaire rend les questions de séquence d’événements répondables. Le modèle mart rend les dashboards rapides et économiques. Ils servent des objectifs différents ; utilisez les deux.

Récapitulatif des pièges courants

Un résumé des pièges que nous avons couverts et évités :

Utiliser ga_session_id seul Plusieurs utilisateurs peuvent partager le même ID. Combinez toujours avec user_pseudo_id.

Oublier la conversion de fuseau horaire event_timestamp est en UTC ; event_date utilise le fuseau horaire de votre propriété. Comparer sans conversion cause des heures de décalage.

Interroger events_intraday_* pour l’analyse historique Les tables intraday sont incomplètes, peuvent avoir des doublons, et n’ont pas les données de source de trafic. Utilisez les tables quotidiennes.

Agréger trop tôt L’agrégation au niveau session perd la flexibilité des funnels et parcours. Notre couche intermédiaire préserve la granularité événementielle.

Ne pas filtrer avec _TABLE_SUFFIX BigQuery facture par octets scannés. Sans filtrage de partition, vous scannez tout. Cher et lent.

Extraire le mauvais type de valeur Utiliser string_value quand la donnée est dans int_value retourne NULL. Connaissez vos types de paramètres, ou utilisez COALESCE entre les types.

Le pattern que nous avons construit évite tous ces pièges.

Et ensuite

Nous avons construit les fondations : des données au niveau événement enrichies avec le contexte de session, plus une table de sessions agrégée pour les dashboards.

Le modèle intermédiaire ouvre des analyses que les données agrégées par session ne peuvent pas supporter :

• Analyse de funnel personnalisée avec des définitions d’étapes flexibles
• Métriques de temps entre événements
• Pattern matching de séquence d’événements
• Modèles d’attribution expérimentaux

Les prochains articles couvriront :

• Modèles GA4 incrémentaux : Gérer les événements tardifs et les coûts à l’échelle
• Attribution GA4 dans dbt : Construire des modèles d’attribution personnalisés au-delà du dernier clic

Annexe

event_params clés et leurs types de valeur

Champs d’attribution par date de disponibilité

Ressources

• Documentation du schéma d’export BigQuery GA4
• Dataset de démonstration GA4 public de Google