Tutoriel

Retrouver les sessions (direct) mal attribuées avec BigQuery

Repérer dans l'export GA4 vers BigQuery les sessions classées direct alors qu'un gclid ou un UTM était présent. Trois requêtes SQL et leur lecture.

Par Ron Kopelman Publié le 2 novembre 2026 ⏱ 1 h Niveau : Avancé
Outils BigQuery Google Analytics 4
Dans ce tutoriel
  1. Pourquoi l’interface GA4 ne suffit pas
  2. Ce que contient l’export
  3. Reconstruire la session
  4. Requête 1 : un gclid ou un UTM à l’arrivée, mais aucune source de session
  5. Requête 2 : des sessions qui démarrent sur une page interne
  6. Requête 3 : la part de sessions sans source, par landing et par heure
  7. Lire les résultats
  8. Limites

Une session classée (direct) dans GA4 n’est pas toujours du trafic qui tape l’URL : parfois un gclid ou un paramètre UTM était bien présent à l’arrivée, puis perdu en cours de route. L’interface GA4 ne permet pas de vérifier ça session par session. L’export BigQuery, lui, conserve l’URL de chaque événement et les sources collectées, à condition de reconstruire les sessions soi-même : voici la méthode que j’utilise, avec trois requêtes et leur lecture.

Pourquoi l’interface GA4 ne suffit pas

Les rapports d’acquisition de GA4 agrègent. Ils vous disent combien de sessions sont classées Direct, mais pas quelle URL d’arrivée portait un gclid ou un utm_source qui n’a pas été pris en compte. Vous ne pouvez pas croiser « landing page » et « paramètres présents à l’arrivée » sur une même session, et encore moins voir la séquence d’événements qui a conduit à la perte.

Outils BigQuery Google Analytics 4

Avec BigQuery et GA4, on regarde l’événement le plus ancien de chaque session et on dit ce qu’il contenait. C’est la seule façon, à ma connaissance, de répondre à la question « cette session aurait-elle dû être attribuée ? ».

Ce que contient l’export

L’export est une table d’événements, pas de sessions. Quatre champs comptent ici.

  • event_params : tableau clé/valeur. page_location, page_referrer et ga_session_id s’y trouvent. On les lit avec UNNEST.
  • collected_traffic_source : UTM et identifiants de clic (gclid, manual_source, manual_campaign_name, etc.). Ces valeurs ne sont renseignées que sur l’événement où elles sont apparues.
  • session_traffic_source_last_click : la source last-click attribuée à la session, pour les campagnes Google Ads, manuelles, cross-canal, SA360, DV360 et CM360, d’après la documentation Google. C’est le meilleur proxy disponible pour « la session a une source ».
  • event_timestamp : en microsecondes. Pour l’heure, il faut convertir avec TIMESTAMP_MICROS.

Attention à traffic_source : c’est la source qui a acquis l’utilisateur, figée à sa première visite. Ne l’utilisez pas pour décrire une session.

Il n’existe pas de colonne « canal de session » dans l’export. Le canal Direct de GA4 se recalcule à partir de ces champs. Nos requêtes sont donc des approximations, et je le répète dans les limites.

Reconstruire la session

Une session se définit par le couple user_pseudo_id plus ga_session_id. Le premier hit est l’événement le plus ancien de ce couple. C’est lui qui porte l’URL d’arrivée. Toutes les requêtes suivantes partent de ce principe.

Requête 1 : un gclid ou un UTM à l’arrivée, mais aucune source de session

Cette requête liste les landing pages dont la première URL contient un gclid ou un utm_source, mais dont la session n’a aucune campagne last-click.

WITH premiers_hits AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    ARRAY_AGG(
      STRUCT(
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
        session_traffic_source_last_click.manual_campaign.campaign_name AS campagne_last_click
      )
      ORDER BY event_timestamp ASC
      LIMIT 1
    )[OFFSET(0)] AS premier_hit
  FROM `projet.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
  GROUP BY user_pseudo_id, session_id
  HAVING session_id IS NOT NULL
)
SELECT
  REGEXP_EXTRACT(premier_hit.page_location, r'^https?://[^?#]+') AS landing,
  COUNT(*) AS sessions_suspectes
FROM premiers_hits
WHERE REGEXP_CONTAINS(premier_hit.page_location, r'[?&](gclid|utm_source)=')
  AND premier_hit.campagne_last_click IS NULL
GROUP BY landing
ORDER BY sessions_suspectes DESC
LIMIT 50;

Lisez le résultat landing par landing. Une landing de campagne qui apparaît ici avec un volume non négligeable est un point d’entrée à investiguer. Je ne fixe pas de seuil universel : je compare la colonne au total des sessions de la même landing, et je calibre sur une semaine de données avant de conclure.

Requête 2 : des sessions qui démarrent sur une page interne

Une session dont la première page a pour referrer votre propre domaine n’a pas démarré depuis l’extérieur. Deux explications, à départager :

  • une nouvelle session après 30 minutes d’inactivité, ce qui est normal ;
  • une session réellement initialisée tard, par exemple parce que l’utilisateur a accepté la bannière sur sa deuxième page.

Le paramètre ga_session_number aide à trier. Un utilisateur dont c’est la première session (ga_session_number = 1) et qui démarre sur une page interne ne correspond pas à une simple réouverture : c’est le signal le plus net.

WITH premieres_pages AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    ARRAY_AGG(
      STRUCT(
        (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_referrer') AS page_referrer,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS numero_session
      )
      ORDER BY event_timestamp ASC
      LIMIT 1
    )[OFFSET(0)] AS premiere_page
  FROM `projet.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
    AND event_name = 'page_view'
  GROUP BY user_pseudo_id, session_id
  HAVING session_id IS NOT NULL
)
SELECT
  REGEXP_EXTRACT(premiere_page.page_location, r'^https?://[^?#]+') AS landing,
  COUNT(*) AS sessions,
  COUNTIF(REGEXP_CONTAINS(premiere_page.page_referrer, r'^https?://(www\.)?votredomaine\.fr')) AS referrer_interne,
  COUNTIF(
    premiere_page.numero_session = 1
    AND REGEXP_CONTAINS(premiere_page.page_referrer, r'^https?://(www\.)?votredomaine\.fr')
  ) AS premiere_session_referrer_interne
FROM premieres_pages
GROUP BY landing
HAVING sessions >= 100
ORDER BY premiere_session_referrer_interne DESC
LIMIT 50;

Remplacez votredomaine.fr par votre domaine. La colonne qui compte est premiere_session_referrer_interne. Une valeur élevée sur une landing donnée signale que des premières visites commencent au milieu du parcours : c’est le motif attendu d’un consentement pris tard.

Requête 3 : la part de sessions sans source, par landing et par heure

Le motif de la bannière se voit mieux en découpant par heure. Une bannière qui se masque ou se comporte différemment à certaines heures ne se traduit pas de la même façon selon le trafic. La requête calcule, pour chaque couple landing et heure (heure de Paris), la part de sessions sans source connue.

WITH premiers_hits AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    ARRAY_AGG(
      STRUCT(
        REGEXP_EXTRACT((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r'^https?://[^?#]+') AS landing,
        EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE 'Europe/Paris') AS heure,
        (collected_traffic_source.gclid IS NULL
          AND collected_traffic_source.manual_source IS NULL
          AND session_traffic_source_last_click.manual_campaign.campaign_name IS NULL) AS sans_source
      )
      ORDER BY event_timestamp ASC
      LIMIT 1
    )[OFFSET(0)] AS h
  FROM `projet.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
    AND event_name = 'page_view'
  GROUP BY user_pseudo_id, session_id
  HAVING session_id IS NOT NULL
)
SELECT
  h.landing,
  h.heure,
  COUNT(*) AS sessions,
  COUNTIF(h.sans_source) AS sessions_sans_source,
  SAFE_DIVIDE(COUNTIF(h.sans_source), COUNT(*)) AS part_sans_source
FROM premiers_hits
GROUP BY h.landing, h.heure
HAVING sessions >= 50
ORDER BY part_sans_source DESC;

Ce n’est pas exactement « la part de Direct » de GA4, puisque la règle de classement n’est pas la même. Le signal qui m’intéresse : une landing qui présente une part anormalement haute sur des heures précises, alors que le reste de la journée est stable. Si ce motif coïncide avec des changements de bannière ou des déploiements, je cherche la cause de ce côté-là.

Lire les résultats

Je procède dans cet ordre :

  1. Requête 1 : quelles landing de campagne perdent leur source ?
  2. Requête 2 : ces landing ont-elles des premières sessions qui démarrent sur une page interne ?
  3. Requête 3 : le problème est-il constant, ou concentré sur certaines heures ?

Si les trois convergent sur une même landing, je regarde le parcours technique : redirections JavaScript, cache CDN qui retire les paramètres, configuration de la bannière. Les causes techniques sont listées dans l’article sur le pic de trafic direct. Si une seule requête ressort, je considère que le signal est faible.

Limites

  • Consentement refusé. Si les visites sans consentement n’arrivent pas dans l’export, elles n’apparaîtront jamais dans ces requêtes. Je n’ai pas confirmé dans la documentation consultée ce qui est envoyé en mode Basic ou Advanced. Vérifiez sur votre propre flux avant de conclure.
  • Approximation du canal. Nous ne reproduisons pas le classement GA4. Un écart entre nos chiffres et le rapport d’acquisition est attendu.
  • Coût. Chaque requête scanne les tables events_* sur la plage demandée. Limitez _TABLE_SUFFIX à une semaine pour tester, et ne lancez pas ces requêtes sur plusieurs mois sans avoir validé la méthode.
  • Retour d’utilisateur. GA4 peut créditer une session directe à une source ancienne si l’utilisateur est déjà connu. Ces cas faussent aussi les requêtes 1 et 3.

FAQ

Ces requêtes remplacent-elles le rapport d’acquisition ? Non. Elles servent à expliquer un écart déjà repéré. Le rapport d’acquisition reste la référence pour le suivi courant.

Faut-il matérialiser une table de sessions ? Pour un audit ponctuel, non : les requêtes ci-dessus suffisent. Pour un suivi régulier, oui. Une table de sessions dédiée évite de rejouer la reconstruction à chaque analyse et permet d’ajouter une alerte : une requête programmée dans BigQuery qui alimente la table chaque nuit, et un seuil qui déclenche un message. Quand la requête est déjà écrite, le reste relève d’un pipeline BigQuery planifié avec alertes.

Peut-on retrouver les sessions refusées ? Pas si elles n’arrivent pas dans l’export. C’est précisément le point à vérifier avant d’interpréter un résultat. Si votre implémentation envoie des pings sans consentement, ils figurent peut-être dans l’export ; sinon, le déficit est structurel et il faut le mesurer autrement, par exemple avec les taux d’interaction avec la bannière.

Sources

Ron Kopelman

Vous préférez que je le fasse ?

Je mets en place ce tutoriel sur votre site, avec recette et documentation. Cadrage gratuit de 30 minutes.

Réserver 30 minutes