Adrienne Vermorel

Construire des tables de sessions à partir des événements GA4

Cet article est le 3ème d’une série de 5 sur GA4 BigQuery. Les articles précédents couvraient la référence complète du schéma et les patterns d’unnesting pour chaque cas d’usage.

Vous maîtrisez maintenant le schéma imbriqué de GA4 et savez extraire les paramètres d’événements, les items et les propriétés utilisateur. Le défi suivant est la sessionisation : ajouter le contexte de session à vos événements pour répondre à des questions comme « quel canal marketing a généré cet achat ? » ou « qu’ont fait les utilisateurs avant de convertir ? ».

Contrairement à Universal Analytics, qui fournissait des tables de sessions pré-agrégées, GA4 exporte des événements bruts sans structure au niveau session. La sessionisation devient votre responsabilité, mais c’est en fait un avantage : au lieu d’être enfermé dans la définition de session de Google, vous contrôlez entièrement la logique.

Cet article construit une table d’événements sessionisés : chaque événement enrichi avec l’identité de session, l’attribution et sa position. Cette approche préserve tout le détail au niveau événement tout en rendant l’analyse de session triviale. Besoin d’une table au grain session pour vos dashboards ? Un simple GROUP BY suffit. Besoin d’analyser la séquence d’événements avant un achat ? Les données sont déjà là.

Pourquoi le grain événement avec contexte session l’emporte sur le grain session

L’approche évidente pour la sessionisation consiste à construire une table au grain session : une ligne par session avec des métriques agrégées comme la durée, les pages vues et le revenu. Beaucoup de tutoriels enseignent ce pattern, et il fonctionne pour le reporting basique. Mais il a une limitation fondamentale.

Une fois agrégé au grain session, vous perdez le détail au niveau événement. La séquence des événements, le timing entre les interactions et les pages spécifiques visitées s’effondrent tous en métriques résumées. Quand un stakeholder demande « quelles pages les utilisateurs visitent-ils avant d’acheter ? », vous devez joindre la table de sessions avec les événements bruts. Quand il demande « combien de temps après l’arrivée les utilisateurs ajoutent-ils au panier ? », vous avez besoin des timestamps que vous avez agrégés.

L’approche par événements enrichis résout ce problème en ajoutant le contexte session comme colonnes sur chaque événement. Chaque ligne reste un événement individuel, mais porte désormais son identité de session, la source de trafic de la session, la landing page, et sa position dans la séquence. Vous pouvez ainsi exécuter des requêtes qui nécessiteraient des jointures complexes avec des tables au grain session :

  • « Montre-moi tous les événements des sessions qui ont converti » → simple clause WHERE
  • « Quel est le temps moyen entre le début de session et le premier ajout au panier ? » → calcul direct
  • « Quelles landing pages mènent à l’engagement le plus long ? » → GROUP BY sur landing_page avec timestamps au niveau événement

Le compromis est le stockage : plus de colonnes signifie une table plus volumineuse. Mais le stockage en colonnes de BigQuery et le partition pruning minimisent l’impact sur les coûts, et la flexibilité analytique compense largement le surcoût de stockage.

La mart de sessions dont vous pourriez avoir besoin pour les dashboards devient triviale à dériver. GROUP BY sur la clé de session, agrégez les métriques nécessaires, et c’est fait. Une seule source de vérité, plusieurs formes de sortie.

La clé de session : deux champs, pas un

L’erreur la plus courante dans la sessionisation GA4 est d’utiliser ga_session_id seul comme identifiant de session. Ce champ est stocké dans le tableau event_params comme valeur entière, et représente le timestamp Unix (en secondes) du début de la session. Le problème : plusieurs utilisateurs peuvent démarrer une session à la même seconde exactement.

Avec un volume de trafic raisonnable, les collisions de timestamps sont garanties. Utiliser uniquement ga_session_id regroupe des événements d’utilisateurs différents dans la même « session », corrompant toutes les métriques en aval. L’attribution devient fausse dès qu’une session contient des événements d’utilisateurs arrivés par différents canaux.

Le pattern correct concatène user_pseudo_id avec ga_session_id pour créer une clé de session véritablement unique :

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

Cette session_key devient la fondation de toutes les window functions. Chaque calcul scoped à la session partitionne par ce champ.

ga_session_id se trouve dans value.int_value, pas dans value.string_value. L’extraire comme string retourne des nulls.

Quelques cas limites nécessitent un traitement. Des événements peuvent arriver sans ga_session_id quand les utilisateurs refusent le consentement (à la fois ga_session_id et user_pseudo_id deviennent null), quand des hits Measurement Protocol n’ont pas de contexte de session, ou quand les événements session_start sont filtrés dans les sous-propriétés. Filtrez ces événements dans votre modèle base :

WHERE (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') IS NOT NULL

Les sessions cross-device présentent un défi différent. Le user_pseudo_id est spécifique à l’appareil, donc la même personne sur téléphone et laptop crée deux pseudo IDs séparés et donc deux sessions distinctes qui ne peuvent être liées sans résolution d’identité supplémentaire. L’article 4 couvre le user stitching pour gérer ce scénario quand les utilisateurs s’authentifient.

Colonnes descriptives de session via window functions

Une fois la clé de session établie, vous pouvez ajouter le contexte session à chaque événement en utilisant des window functions. Le pattern est cohérent : PARTITION BY session_key, optionnellement ORDER BY event_timestamp quand vous avez besoin de la première ou dernière valeur.

Les timestamps de session établissent les bornes de la session :

MIN(event_timestamp) OVER (PARTITION BY session_key) AS session_start_ts,
MAX(event_timestamp) OVER (PARTITION BY session_key) AS session_end_ts

Avoir les deux timestamps sur chaque événement permet des calculs de durée au moment de la requête sans ré-agrégation. La durée de session est (session_end_ts - session_start_ts) / 1000000 en secondes puisque les timestamps sont en microsecondes.

L’attribution de session nécessite de récupérer la source de trafic du premier événement de la session. GA4 fournit la struct session_traffic_source_last_click (disponible depuis juillet 2024), qui contient l’attribution scoped à la session correspondant à l’interface GA4 :

FIRST_VALUE(session_traffic_source_last_click.manual_campaign.source IGNORE NULLS)
OVER (PARTITION BY session_key ORDER BY event_timestamp) AS session_source,
FIRST_VALUE(session_traffic_source_last_click.manual_campaign.medium IGNORE NULLS)
OVER (PARTITION BY session_key ORDER BY event_timestamp) AS session_medium,
FIRST_VALUE(session_traffic_source_last_click.manual_campaign.campaign_name IGNORE NULLS)
OVER (PARTITION BY session_key ORDER BY event_timestamp) AS session_campaign

La clause IGNORE NULLS garantit d’obtenir la première valeur non-nulle, gérant les événements sans données d’attribution. Enveloppez avec COALESCE(..., '(direct)') ou '(none)' pour une sortie propre.

La landing page suit le même pattern :

FIRST_VALUE(
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')
IGNORE NULLS
) OVER (PARTITION BY session_key ORDER BY event_timestamp) AS landing_page

Le contexte device et géographie change rarement au sein d’une session, mais utiliser des window functions garantit la cohérence sur tous les événements :

FIRST_VALUE(device.category IGNORE NULLS)
OVER (PARTITION BY session_key ORDER BY event_timestamp) AS session_device_category,
FIRST_VALUE(geo.country IGNORE NULLS)
OVER (PARTITION BY session_key ORDER BY event_timestamp) AS session_country

Ordonnancement des événements et analyse de séquence

Au-delà du contexte descriptif, les colonnes positionnelles permettent l’analyse de séquence : comprendre non seulement ce qui s’est passé dans une session, mais dans quel ordre. Le défi est que event_timestamp seul ne garantit pas un ordonnancement correct. Plusieurs événements peuvent partager le même timestamp, et l’export ne préserve pas l’ordre dans lequel ils ont été déclenchés sur l’appareil.

GA4 fournit trois champs spécifiquement pour l’ordonnancement déterministe des événements :

ChampDescription
batch_event_indexOrdre séquentiel de chaque événement dans un batch, basé sur l’ordre d’occurrence sur l’appareil
batch_ordering_idNombre croissant de façon monotone, incrémenté à chaque requête réseau depuis une page
batch_page_idNombre séquentiel assigné à chaque page, croissant au fil de l’engagement

Ces champs apparaissent au niveau racine du schéma d’export. Combinés, ils fournissent la séquence exacte des événements tels qu’ils se sont produits sur l’appareil de l’utilisateur, résolvant les égalités de timestamp de façon déterministe.

La numérotation des événements au sein de la session utilise ces champs pour l’ordonnancement :

ROW_NUMBER() OVER (
PARTITION BY session_key
ORDER BY event_timestamp, batch_page_id, batch_ordering_id, batch_event_index
) AS event__number_in_session

Le ORDER BY composé assure un séquencement correct : d’abord par timestamp pour l’ordre grossier, puis par les champs batch pour départager les égalités dans l’ordre exact de déclenchement.

Les flags premier et dernier événement dérivent du numéro d’événement et du compte total :

ROW_NUMBER() OVER w_ordered AS event__number_in_session,
COUNT(*) OVER (PARTITION BY session_key) AS session__events

Puis dans votre SELECT final :

event__number_in_session = 1 AS event__is_session_start,
event__number_in_session = session__events AS event__is_session_end

Ces flags permettent l’analyse des entrées et sorties sans agrégation. Filtrez sur event__is_session_start = TRUE pour l’analyse des landing pages, ou event__is_session_end = TRUE pour les pages de sortie.

Le temps écoulé dans la session permet l’analyse de rythme :

(event_timestamp - session_start_ts) / 1000000 AS event__seconds_since_session_start

Cela répond directement à « combien de temps après l’arrivée les utilisateurs achètent-ils en général ? » depuis la table d’événements.

Attribution scoped session en détail

L’export BigQuery de GA4 contient des données de source de trafic dans quatre emplacements différents, chacun avec un scope et des cas d’usage différents. Utiliser la mauvaise source produit des rapports d’attribution incorrects.

La struct traffic_source contient l’attribution first-touch scoped utilisateur (la source qui a originellement acquis l’utilisateur, peu importe la session actuelle). Utilisez-la pour l’analyse d’acquisition utilisateur, pas pour l’attribution de session.

La struct collected_traffic_source contient les données brutes de collecte scoped événement sans modélisation d’attribution appliquée. Utile pour construire des modèles d’attribution personnalisés mais nécessite une logique supplémentaire pour déterminer les valeurs au niveau session.

La struct session_traffic_source_last_click (depuis juillet 2024) contient l’attribution scoped session avec le modèle last-non-direct de GA4 appliqué. Elle correspond à ce que vous voyez dans l’interface GA4 et constitue la source recommandée pour l’attribution de session.

Le tableau event_params contient parfois des clés legacy source et medium, mais elles sont peuplées de façon incohérente et ne devraient être utilisées qu’en fallback.

Pour les données à partir de juillet 2024, session_traffic_source_last_click est le choix évident. Elle gère automatiquement la complexité de l’attribution last-non-direct : si une session démarre en trafic direct, GA4 regarde jusqu’à 90 jours en arrière pour trouver une source non-direct précédente et lui attribue le crédit.

Pour les données historiques avant juillet 2024, vous devrez construire l’attribution de session à partir de collected_traffic_source en utilisant le pattern FIRST_VALUE montré plus tôt. À vous de juger si maintenir deux chemins de code vaut la complexité, ou si votre analyse peut démarrer à partir de juillet 2024.

Un problème connu affecte le trafic Google Ads. Les sessions avec des paramètres gclid apparaissent parfois comme organic ou direct parce que GA4 ne décode pas toujours le click ID en source et medium dans l’export. Si une attribution paid search précise compte, ajoutez une correction :

CASE
WHEN collected_traffic_source.gclid IS NOT NULL THEN 'google'
ELSE session_traffic_source_last_click.manual_campaign.source
END AS session_source,
CASE
WHEN collected_traffic_source.gclid IS NOT NULL THEN 'cpc'
ELSE session_traffic_source_last_click.manual_campaign.medium
END AS session_medium

Pour des données Google Ads complètes (nom de campagne, ad group, mots-clés), envisagez de joindre avec les exports Google Ads Data Transfer via le champ gclid.

Patterns d’implémentation dbt

Le modèle d’événements sessionisés se place dans la couche intermediate de votre projet dbt. Il prend les événements nettoyés des modèles base et ajoute le contexte de session, alimentant les marts et modèles d’analyse en aval.

# structure dbt_project.yml
models:
your_project:
base:
ga4:
+materialized: view
intermediate:
ga4:
+materialized: incremental
marts:
ga4:
+materialized: table

Pour la configuration incrémentale, insert_overwrite avec partitionnement par date offre les meilleures performances sur BigQuery :

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

Le clustering par session_key optimise la performance des window functions. Puisque chaque window partitionne par ce champ, BigQuery peut traiter les événements de chaque session ensemble sans scanner des données non liées.

La fenêtre de lookback mérite une attention particulière. GA4 met à jour les tables d’export jusqu’à 72 heures après la date de l’événement, donc vous devez retraiter les partitions récentes à chaque exécution. Mais les window functions ont besoin de tous les événements d’une session pour calculer correctement. Si une session s’étend sur deux jours et que vous ne retraitez qu’aujourd’hui, les window functions pour la portion d’hier de cette session deviennent obsolètes.

Une fenêtre de lookback de 7 jours fournit une marge de sécurité :

{% if is_incremental() %}
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
{% endif %}

Les sessions s’étendent rarement sur plus de quelques heures, donc 7 jours capture n’importe quelle session raisonnable complètement tout en limitant le coût de retraitement.

Pour garder le modèle lisible, créez une macro pour les patterns de window répétés :

-- macros/ga4/session_first_value.sql
{% macro session_first_value(column, partition_key='session_key', order_key='event_timestamp') %}
FIRST_VALUE({{ column }} IGNORE NULLS) OVER (
PARTITION BY {{ partition_key }}
ORDER BY {{ order_key }}
)
{% endmacro %}

Utilisation dans votre modèle :

{{ session_first_value('session_traffic_source_last_click.manual_campaign.source') }} AS session_source

Pourquoi vos chiffres ne correspondent pas à l’interface GA4

Attendez-vous à 1-5% d’écart entre les requêtes BigQuery et les rapports de l’interface GA4. C’est normal et ne devrait pas déclencher d’efforts de réconciliation poussés.

Plusieurs facteurs contribuent à l’écart. L’interface GA4 utilise HyperLogLog++ pour le comptage probabiliste, qui échange la précision parfaite contre la vitesse. BigQuery retourne des comptes exacts. Pour les grands datasets, la différence est notable.

Le behavioral modeling du Consent Mode présente un écart plus important. GA4 modélise le comportement des utilisateurs qui refusent le tracking, estimant les conversions et l’engagement basé sur les patterns des utilisateurs consentants. Ces données modélisées apparaissent dans les rapports GA4 mais n’atteignent jamais les exports BigQuery.

Google Signals permet la déduplication cross-device des utilisateurs dans l’interface GA4 pour les utilisateurs connectés à leur compte Google. BigQuery voit chaque appareil comme un user_pseudo_id séparé, donc la même personne sur téléphone et desktop compte comme deux utilisateurs.

Les délais de traitement des données signifient que les événements arrivent jusqu’à 72 heures après leur occurrence. Comparer des données de moins de 72 heures montrera des écarts.

L’approche pratique : documentez la variance attendue, comparez des données de plus de 72 heures, et utilisez APPROX_COUNT_DISTINCT() quand vous devez reproduire le comportement de comptage de GA4. Une réconciliation parfaite n’est pas possible étant donné les différences architecturales.

Considérations de performance

Le partition pruning a le plus grand impact sur le coût et la vitesse des requêtes. Filtrez toujours sur _TABLE_SUFFIX dans votre modèle base ou requête source :

WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))

Cela indique à BigQuery de ne scanner que les partitions de dates spécifiées. Sans ce filtre, chaque requête scanne tout votre historique d’événements. Sur une propriété volumineuse, c’est la différence entre scanner 5 Go et 500 Go (une différence de coût de 100x).

Le clustering par session_key optimise l’exécution des window functions. BigQuery organise physiquement les données pour que les événements de la même session soient stockés ensemble, minimisant les I/O lors des opérations partition-by.

Les window functions ajoutent du coût de calcul, mais vous pouvez le minimiser en combinant les windows avec des clauses PARTITION BY et ORDER BY identiques. BigQuery peut calculer plusieurs window functions en un seul passage sur les données quand la spécification de frame correspond :

-- Ceux-ci se calculent en un seul passage
FIRST_VALUE(source IGNORE NULLS) OVER w AS session_source,
FIRST_VALUE(medium IGNORE NULLS) OVER w AS session_medium,
FIRST_VALUE(campaign IGNORE NULLS) OVER w AS session_campaign,
MIN(event_timestamp) OVER w AS session_start_ts
-- Définir la window une seule fois
WINDOW w AS (PARTITION BY session_key ORDER BY event_timestamp)

Cette syntaxe de window nommée garde le modèle lisible tout en assurant une exécution optimale.

Des événements enrichis à la mart de sessions

La table d’événements sessionisés rend la dérivation d’une mart au grain session triviale. Tout le contexte session existe déjà sur chaque ligne, donc l’agrégation ne nécessite aucune jointure :

-- models/marts/mrt__ga4__sessions.sql
SELECT
session_key,
-- Dimensions (constantes par session, utiliser ANY_VALUE)
ANY_VALUE(user_pseudo_id) AS user_pseudo_id,
ANY_VALUE(session_source) AS session__source,
ANY_VALUE(session_medium) AS session__medium,
ANY_VALUE(session_campaign) AS session__campaign,
ANY_VALUE(landing_page) AS session__landing_page,
ANY_VALUE(session_device_category) AS session__device_category,
ANY_VALUE(session_country) AS session__country,
-- Timestamps
MIN(event_timestamp) AS session__started_at,
MAX(event_timestamp) AS session__ended_at,
-- Métriques d'engagement
COUNT(*) AS session__events,
COUNTIF(event_name = 'page_view') AS session__page_views,
COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')) AS session__unique_pages,
MAX((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged')) = '1' AS session__is_engaged,
-- Conversions
COUNTIF(event_name = 'purchase') AS session__purchases,
SUM(IF(event_name = 'purchase', ecommerce.purchase_revenue, 0)) AS session__total_revenue
FROM {{ ref('int__ga4__events_sessionized') }}
GROUP BY session_key

Cette mart peut être une table pour la performance des dashboards ou une vue pour des données toujours fraîches. Dans les deux cas, la logique reste simple et facile à maintenir.

La puissance du pattern devient claire quand les besoins changent. Besoin d’ajouter une nouvelle métrique au niveau session ? Ajoutez-la au SELECT de la mart. Besoin de changer comment les sessions sont identifiées ou attribuées ? Changez le modèle intermediate une fois, et la mart hérite automatiquement du correctif. Une seule source de vérité, plusieurs formes de sortie.

Le modèle d’événements sessionisés complet

Voici l’implémentation complète rassemblant tous les patterns discutés :

-- models/intermediate/int__ga4__events_sessionized.sql
{{
config(
materialized='incremental',
incremental_strategy='insert_overwrite',
partition_by={
'field': 'event_date',
'data_type': 'date',
'granularity': 'day'
},
cluster_by=['session_key']
)
}}
WITH events AS (
SELECT
event_date,
event_timestamp,
event_name,
user_pseudo_id,
event_params,
user_properties,
ecommerce,
items,
device,
geo,
traffic_source,
collected_traffic_source,
session_traffic_source_last_click,
-- Champs d'ordonnancement batch pour le séquencement déterministe des événements
batch_page_id,
batch_ordering_id,
batch_event_index,
-- Extraire ga_session_id une fois pour réutilisation
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
FROM {{ ref('base__ga4__events') }}
{% if is_incremental() %}
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
{% endif %}
),
with_session_key AS (
SELECT
*,
CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING)) AS session_key
FROM events
WHERE ga_session_id IS NOT NULL
),
sessionized AS (
SELECT
-- Champs originaux de l'événement
event_date,
event_timestamp,
event_name,
user_pseudo_id,
ga_session_id,
session_key,
batch_page_id,
batch_ordering_id,
batch_event_index,
event_params,
user_properties,
ecommerce,
items,
device,
geo,
traffic_source,
collected_traffic_source,
session_traffic_source_last_click,
-- Timestamps de session
MIN(event_timestamp) OVER w AS session__started_at,
MAX(event_timestamp) OVER w AS session__ended_at,
-- Attribution de session
COALESCE(
FIRST_VALUE(session_traffic_source_last_click.manual_campaign.source IGNORE NULLS) OVER w_ordered,
'(direct)'
) AS session__source,
COALESCE(
FIRST_VALUE(session_traffic_source_last_click.manual_campaign.medium IGNORE NULLS) OVER w_ordered,
'(none)'
) AS session__medium,
FIRST_VALUE(session_traffic_source_last_click.manual_campaign.campaign_name IGNORE NULLS) OVER w_ordered AS session__campaign,
-- Landing page
FIRST_VALUE(
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')
IGNORE NULLS
) OVER w_ordered AS session__landing_page,
-- Device et geo
FIRST_VALUE(device.category IGNORE NULLS) OVER w_ordered AS session__device_category,
FIRST_VALUE(geo.country IGNORE NULLS) OVER w_ordered AS session__country,
-- Positionnel : numérotation des événements
ROW_NUMBER() OVER w_ordered AS event__number_in_session,
COUNT(*) OVER w AS session__events,
-- Positionnel : temps écoulé
event_timestamp - MIN(event_timestamp) OVER w AS event__microseconds_since_session_start
FROM with_session_key
WINDOW
w AS (PARTITION BY session_key),
w_ordered AS (PARTITION BY session_key ORDER BY event_timestamp, batch_page_id, batch_ordering_id, batch_event_index)
)
SELECT
*,
-- Flags positionnels dérivés
event__number_in_session = 1 AS event__is_session_start,
event__number_in_session = session__events AS event__is_session_end,
event__microseconds_since_session_start / 1000000 AS event__seconds_since_session_start
FROM sessionized

Testez le modèle avec ces tests de schéma :

models/intermediate/schema.yml
models:
- name: int__ga4__events_sessionized
columns:
- name: session_key
tests:
- not_null
- name: event_timestamp
tests:
- not_null
- name: event__number_in_session
tests:
- not_null
- dbt_utils.accepted_range:
min_value: 1
- name: session__source
tests:
- not_null

Conclusion

La table d’événements sessionisés que vous avez construite ici devient la fondation de tout le reste. Une mart au grain session pour les dashboards n’est qu’un GROUP BY sur la clé de session. L’analyse de la séquence d’événements avant conversion utilise les données de position et timing déjà présentes sur chaque ligne. Filtrer sur les sessions d’une campagne spécifique est une clause WHERE.

Cela fonctionne parce que vous ajoutez le contexte session aux événements plutôt que d’agréger les événements en sessions. Vous conservez le détail et gagnez la flexibilité de le découper selon ce que la question exige.

Une limitation demeure : ces sessions sont liées à l’appareil. Quand quelqu’un navigue sur son téléphone puis achète sur son laptop, vous voyez deux sessions séparées de deux « utilisateurs » différents sans connexion entre eux. Le prochain article traite du user stitching, connectant l’historique de navigation anonyme aux identités connues quand les utilisateurs s’authentifient.