Requêtes BigQuery GA4 : 10 recettes SQL prêtes (2026)

L’export GA4 dans BigQuery donne accès à chaque événement brut, non échantillonné et sans limite de cardinalité. C’est la donnée que l’interface ne vous montre jamais vraiment. Le problème, ce ne sont pas les requêtes BigQuery GA4 elles-mêmes : c’est le schéma. Tableaux imbriqués, aucune colonne session_id native, timestamps en microsecondes, et quatre endroits différents où peut vivre la source de trafic. La plupart des analysts abandonnent à la première requête qui renvoie un résultat faux sans erreur visible.

Ce recettier livre dix requêtes prêtes à copier, classées par question business réelle plutôt que par fonction SQL. Pour chacune : la requête, le piège de schéma qui fausse silencieusement le comptage, et l’astuce pour garder le coût quasi nul. Si vous n’avez pas encore activé l’export ni compris la structure des tables, commencez par le guide pivot, exploiter l’export GA4 dans BigQuery : il pose les bases que ce recettier suppose acquises.

Trois pièges de schéma à connaître avant toute requête

Avant la première requête, intégrez ces trois réalités. Elles expliquent 90 % des écarts entre BigQuery et l’interface GA4.

Il n’existe pas de session_id. GA4 ne stocke pas d’identifiant de session unique. Vous devez le reconstruire en concaténant user_pseudo_id avec le paramètre d’événement ga_session_id. Pris seul, ga_session_id se répète entre utilisateurs et gonfle vos comptes.

Les timestamps sont en microsecondes. event_timestamp n’est pas en secondes ni en millisecondes. Pour obtenir une date lisible, passez par TIMESTAMP_MICROS(event_timestamp). Oublier ce point produit des dates en l’an 1970 ou des calculs de durée absurdes.

La source de trafic vit à quatre endroits. C’est le piège le plus coûteux en analyses fausses. Selon la question, vous ne lisez pas le même champ.

Emplacement	Portée	Quand l’utiliser
`traffic_source`	Utilisateur, figé à la première visite	Acquisition initiale (le canal qui a fait connaître l’utilisateur)
`collected_traffic_source`	Événement, valeurs collectées avec l’event	Source brute au moment précis de l’événement
`session_traffic_source_last_click`	Session, dernier clic non direct	Aligner avec les rapports de session de l’interface GA4
`event_params` (`source`, `medium`, `campaign`)	Événement, héritage des anciennes implémentations	Comptes plus anciens, à éviter sur du neuf

Le réflexe : traffic_source pour le canal qui initie, session_traffic_source_last_click pour le canal qui clôture. Confondre les deux est l’erreur d’attribution la plus répandue sur BigQuery.

Dans toutes les requêtes ci-dessous, remplacez projet.dataset par votre identifiant réel et ajustez la fenêtre de dates _TABLE_SUFFIX.

1. Utilisateurs actifs, nouveaux et récurrents

La première question de tout reporting : combien de personnes, et combien de nouvelles.

SELECT
  COUNT(DISTINCT user_pseudo_id) AS utilisateurs_actifs,
  COUNT(DISTINCT IF(event_name = 'first_visit', user_pseudo_id, NULL)) AS nouveaux_utilisateurs,
  COUNT(DISTINCT user_pseudo_id)
    - COUNT(DISTINCT IF(event_name = 'first_visit', user_pseudo_id, NULL)) AS recurrents
FROM `projet.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260626';
-- utilisateurs_actifs: 18 240 | nouveaux: 11 905 | recurrents: 6 335

Le piège. Un nouvel utilisateur se définit par un événement first_visit survenu dans la fenêtre, pas par sa première ligne dans la table. Et user_pseudo_id est un identifiant d’appareil ou de navigateur, pas une personne : un même visiteur sur deux appareils compte double, et un cookie effacé crée un nouvel utilisateur.

Le coût. Filtrez toujours sur _TABLE_SUFFIX et ne sélectionnez que les colonnes nécessaires. Ici, BigQuery ne scanne que user_pseudo_id et event_name, pas la table entière.

2. Compter les sessions correctement

Sans session_id natif, une session se reconstruit. Voici la seule façon fiable.

SELECT
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  )) AS sessions
FROM `projet.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260626';
-- sessions: 24 117

Le piège. Ne comptez jamais ga_session_id seul : ce nombre (un timestamp de début de session) se répète d’un utilisateur à l’autre. La clé de session valide est toujours user_pseudo_id concaténé avec ga_session_id.

Le coût. Le UNNEST(event_params) dans une sous-requête corrélée reste peu coûteux car vous ne scannez qu’une colonne imbriquée. Réutilisez ce motif partout plutôt que d’aplatir toute la table.

3. Pages les plus vues et engagement

Le classique du contenu, avec une mesure d’engagement à côté du simple volume.

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
  COUNTIF(event_name = 'page_view') AS vues,
  ROUND(SUM(
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')
  ) / 1000, 0) AS secondes_engagement
FROM `projet.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260626'
GROUP BY page
ORDER BY vues DESC
LIMIT 20;

Le piège. page_location contient l’URL complète, paramètres UTM inclus. Une même page apparaît alors en dizaines de lignes. Normalisez avec REGEXP_REPLACE ou SPLIT(page, '?')[OFFSET(0)] si vous voulez agréger par chemin.

Le coût. Deux sous-requêtes sur event_params, toujours sur une seule colonne imbriquée. Le LIMIT ne réduit pas le scan : c’est le _TABLE_SUFFIX qui le fait.

4. Le canal qui initie contre le canal qui clôture

La question d’attribution qui sépare les experts des copier-coller. Ici, le dernier clic non direct au niveau session.

SELECT
  session_traffic_source_last_click.manual_campaign.source AS source,
  session_traffic_source_last_click.manual_campaign.medium AS medium,
  session_traffic_source_last_click.cross_channel_campaign.default_channel_group AS canal,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  )) AS sessions
FROM `projet.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260626'
GROUP BY 1, 2, 3
ORDER BY sessions DESC;

Le piège. Ce champ session_traffic_source_last_click n’existe que depuis juillet 2024 dans l’export : sur des dates antérieures, il sera vide. Pour le canal qui initie l’utilisateur (premier contact), lisez plutôt traffic_source.source et traffic_source.medium, figés à la première visite. Les deux répondent à des questions différentes, ne les mélangez pas dans la même colonne.

Le coût. Ces champs sont des colonnes natives (pas des arrays), donc très bon marché à lire. Aucun UNNEST requis pour la source de session.

5. Funnel e-commerce et taux d’abandon

De la vue produit à l’achat, en une requête.

SELECT
  COUNTIF(event_name = 'view_item') AS vue_produit,
  COUNTIF(event_name = 'add_to_cart') AS ajout_panier,
  COUNTIF(event_name = 'begin_checkout') AS debut_paiement,
  COUNTIF(event_name = 'purchase') AS achat,
  ROUND(SAFE_DIVIDE(
    COUNTIF(event_name = 'purchase'),
    COUNTIF(event_name = 'view_item')
  ) * 100, 1) AS taux_vue_vers_achat_pct
FROM `projet.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260626';
-- vue_produit: 52 300 | ajout_panier: 14 800 | debut_paiement: 6 020 | achat: 3 110

Le piège. Ces COUNTIF comptent des événements, pas des sessions ni des utilisateurs uniques. Un visiteur qui voit dix produits gonfle vue_produit. Pour un funnel séquentiel strict (chaque étape n’est comptée que si la précédente a eu lieu), il faut compter des sessions distinctes par étape, étape par étape.

Le coût. Une seule passe sur event_name. C’est l’une des requêtes les moins chères du recettier.

6. Revenu par source et par catégorie de produit

Le détail commercial vit dans l’array items, qu’il faut déplier.

SELECT
  session_traffic_source_last_click.manual_campaign.source AS source,
  item.item_category AS categorie,
  ROUND(SUM(item.item_revenue), 2) AS revenu,
  SUM(item.quantity) AS quantite
FROM `projet.dataset.events_*`,
  UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260626'
  AND event_name = 'purchase'
GROUP BY 1, 2
ORDER BY revenu DESC;

Le piège. items est un array : le UNNEST crée une ligne par produit. Ne sommez donc jamais ecommerce.purchase_revenue (niveau événement) après avoir déplié items, sous peine de multiplier le revenu par le nombre d’articles du panier. Pour le revenu par produit, utilisez item.item_revenue ; pour le revenu par commande, restez au niveau événement sans UNNEST.

Le coût. Le UNNEST(items) reste local à chaque ligne, le filtre event_name = 'purchase' réduit déjà fortement le volume traité.

7. Rétention et cohortes simples

Combien d’utilisateurs reviennent, N jours après leur première visite.

WITH premiere_visite AS (
  SELECT
    user_pseudo_id,
    MIN(PARSE_DATE('%Y%m%d', event_date)) AS cohorte
  FROM `projet.dataset.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260501' AND '20260626'
  GROUP BY 1
),
activite AS (
  SELECT DISTINCT
    user_pseudo_id,
    PARSE_DATE('%Y%m%d', event_date) AS jour
  FROM `projet.dataset.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260501' AND '20260626'
)
SELECT
  p.cohorte,
  DATE_DIFF(a.jour, p.cohorte, DAY) AS jour_n,
  COUNT(DISTINCT a.user_pseudo_id) AS utilisateurs
FROM premiere_visite p
JOIN activite a USING (user_pseudo_id)
GROUP BY 1, 2
ORDER BY 1, 2;

Le piège. La cohorte se base sur la première activité observée dans la fenêtre interrogée, pas sur la vraie première visite si votre historique est tronqué. Élargissez la fenêtre de la CTE premiere_visite ou la rétention sera surestimée. Et un user_pseudo_id qui change (cookies effacés) réapparaît comme un nouveau, ce qui dilue les courbes.

Le coût. Deux scans de la même plage de tables. Si vous relancez souvent, matérialisez la table d’activité quotidienne une fois, puis interrogez-la.

8. Parcours et séquence d’événements avant conversion

Le chemin réel suivi à l’intérieur des sessions qui convertissent.

SELECT
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
  STRING_AGG(event_name, ' > ' ORDER BY event_timestamp) AS parcours
FROM `projet.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260626'
GROUP BY 1, 2
HAVING parcours LIKE '%purchase%'
LIMIT 100;
-- exemple: session_start > page_view > view_item > add_to_cart > purchase

Le piège. L’ordre repose sur event_timestamp (microsecondes) : c’est fiable, sauf quand deux événements partagent exactement le même timestamp, où l’ordre devient indéterminé. Dans ce cas, départagez avec event_bundle_sequence_id. Attention aussi : STRING_AGG tronque les très longs parcours au-delà de la limite de taille de chaîne.

Le coût. Le GROUP BY sur deux clés et le LIMIT 100 n’empêchent pas le scan complet de la fenêtre : gardez la plage de dates serrée pour explorer.

9. Key events et taux de conversion par appareil et pays

Le taux de conversion segmenté, là où les écarts se cachent.

SELECT
  device.category AS appareil,
  geo.country AS pays,
  COUNTIF(event_name = 'purchase') AS conversions,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  )) AS sessions,
  ROUND(SAFE_DIVIDE(
    COUNTIF(event_name = 'purchase'),
    COUNT(DISTINCT CONCAT(
      user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    ))
  ) * 100, 2) AS taux_conversion_pct
FROM `projet.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260626'
GROUP BY 1, 2
ORDER BY conversions DESC;

Le piège. Dans GA4, les « conversions » s’appellent désormais des key events. L’export ne marque pas une colonne « ceci est un key event » : remplacez 'purchase' par les noms de vos propres key events. device.category et geo.country sont des colonnes natives, lisibles sans UNNEST.

Le coût. Les sous-requêtes sur ga_session_id sont répétées : factorisez-les via une CTE si la requête tourne souvent, pour la lisibilité plus que pour le scan.

10. Contrôle qualité : doublons et trafic non attribué

La requête que personne ne lance, et qui sauve les analyses suivantes.

SELECT
  event_name,
  COUNT(*) AS occurrences,
  COUNT(*) - COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    event_name,
    CAST(event_timestamp AS STRING),
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  )) AS doublons_estimes
FROM `projet.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260626'
GROUP BY event_name
ORDER BY doublons_estimes DESC;

Le piège. Des doublons (même utilisateur, même événement, même timestamp) trahissent souvent un double déclenchement de tag. Quant aux valeurs (not set) et au trafic unassigned, elles viennent de paramètres manquants à la collecte, pas de BigQuery. Pour les traiter à la source, croisez avec l’audit GA4 et ses 11 erreurs de configuration et avec les 7 erreurs de dataLayer qui faussent vos données.

Le coût. Une seule passe agrégée par event_name : peu chère, à programmer en contrôle hebdomadaire.

Garder le coût quasi nul

BigQuery facture à l’octet scanné, pas à la requête. Quatre réflexes suffisent à diviser la facture par dix.

Filtrez toujours sur _TABLE_SUFFIX pour ne scanner que les tables events_* de la période utile. Ne faites jamais SELECT * : chaque colonne ignorée est de l’argent économisé, et le schéma GA4 est large. Prévisualisez le volume scanné avant de lancer (l’estimateur de l’éditeur BigQuery l’affiche sans rien facturer). Enfin, pour les requêtes récurrentes, créez des vues ou des tables intermédiaires (une table de sessions, une table d’activité quotidienne) plutôt que de réagréger la donnée brute à chaque fois. Le guide pivot détaille comment construire une vue sessions réutilisable.

Recouper avec l’interface : pourquoi les chiffres diffèrent

BigQuery et l’interface GA4 ne donneront presque jamais exactement le même nombre, et c’est normal. L’interface applique de l’échantillonnage sur les grosses explorations, des seuils de confidentialité et sa propre modélisation, là où BigQuery vous livre le brut. Un écart de quelques pourcents est attendu ; un écart massif signale un problème de définition (session, key event) de votre côté.

Une cause d’écart prend de l’ampleur en 2026 : le Consent Mode. Depuis son renforcement, une part du trafic sans consentement n’est plus exportée intégralement vers BigQuery, ce qui ampute les volumes. Avant de conclure à un bug, vérifiez l’impact du consentement avec notre analyse du Consent Mode v2 dans GA4.

Aller plus loin

Vous avez désormais une boîte à outils SQL fiable, qui répond à de vraies questions sans tomber dans les erreurs de comptage classiques. La suite logique : brancher ces requêtes dans un tableau de bord automatisé avec Looker Studio et BigQuery, pour ne plus jamais les relancer à la main.

Et si vous laissez une IA générer votre SQL, ce recettier devient votre filet de sécurité : gardez ces requêtes de référence pour vérifier et corriger ce que l’assistant produit. C’est exactement la méthode décrite dans Claude Code pour le data analyst : interroger GA4 et BigQuery. La machine écrit vite ; vous, vous savez où sont les pièges de schéma.