La plupart des analytics engineers apprennent BigQuery par essais et erreurs, en glanant des patterns au fil des réponses Stack Overflow et des codebases héritées. Ça fonctionne, mais c’est lent et coûteux.

Après avoir audité des centaines de modèles dbt et des années de travail en production sur BigQuery, j’ai identifié un canon de patterns qui distinguent le SQL fonctionnel du SQL élégant et économique. Ce sont des techniques fondamentales qui se capitalisent avec le temps — le genre de connaissances qui vous rendent plus rapide pour résoudre de nouveaux problèmes parce que vous avez internalisé les bonnes primitives.

Cet article couvre 10 patterns répartis en 5 catégories : optimisation des requêtes, gestion des données imbriquées, window functions, stratégies incrémentales dbt et analytics marketing. Chaque pattern inclut du code prêt pour la production, des anti-patterns à éviter et des benchmarks de performance réels.

Optimisation des requêtes et maîtrise des coûts

Ces trois patterns impactent directement votre facture BigQuery et la performance de vos requêtes. Maîtrisez-les en priorité.

Pattern 1 : Combo partitionnement + clustering

Le partitionnement divise votre table en segments par date, timestamp ou plage d’entiers. Le clustering trie les données au sein de ces partitions selon jusqu’à quatre colonnes. Combinés, ils permettent à BigQuery de sauter entièrement les blocs de données non pertinents, transformant les full table scans en lectures ciblées.

-- Créer une table avec les deux stratégies
CREATE TABLE `project.dataset.events`
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_name
AS
SELECT
  event_timestamp,
  user_id,
  event_name,
  event_params
FROM raw.events;

-- Imposer les filtres de partition au niveau de la table
ALTER TABLE `project.dataset.events`
SET OPTIONS (require_partition_filter = true);

-- Requête qui déclenche le pruning de partition et de cluster
SELECT user_id, event_name, COUNT(*)
FROM `project.dataset.events`
WHERE DATE(event_timestamp) = '2025-01-01'  -- Partition pruning
  AND user_id = 'abc123'                     -- Cluster pruning
GROUP BY 1, 2;

L’option require_partition_filter empêche quiconque de lancer accidentellement un full table scan. Les requêtes sans filtre de partition échouent tout simplement.

Ce qu’il faut éviter :

Utiliser des fonctions sur les colonnes de partition annule le pruning. Si votre table est partitionnée par TIMESTAMP, le filtre WHERE DATE(event_timestamp) = '2025-01-01' force un full scan car BigQuery ne peut pas évaluer la fonction au moment de la planification. Stockez plutôt une colonne DATE séparée et partitionnez dessus, ou utilisez un filtre de plage : WHERE event_timestamp >= '2025-01-01' AND event_timestamp < '2025-01-02'.

Les sous-requêtes dans les filtres de partition empêchent également le pruning. WHERE event_date = (SELECT MAX(event_date) FROM other_table) ne fera pas de pruning car BigQuery a besoin de valeurs littérales au moment de la planification. Matérialisez d’abord la date ou utilisez une variable.

Le sur-partitionnement crée ses propres problèmes. Si vos partitions contiennent moins de 1 Go chacune, l’overhead des métadonnées dépasse les bénéfices du pruning. Pour les petites tables, le clustering seul performe souvent mieux.

Impact sur la performance : Le partitionnement seul offre une réduction de coûts de 10 à 100x sur les grandes tables. Ajouter le clustering apporte une réduction supplémentaire de 2 à 10x au sein des partitions. Sur une table de 10 To avec des filtres appropriés, vous pourriez ne scanner que 10 à 100 Go.

Pattern 2 : Vues matérialisées pour les agrégations répétées

Les dashboards et rapports exécutent souvent les mêmes agrégations lourdes toutes les heures. Chaque exécution scanne les mêmes données, consomme les mêmes slots, coûte le même argent. Les vues matérialisées précalculent ces résultats et se rafraîchissent de manière incrémentale.

L’optimiseur BigQuery utilise les MV de façon transparente. Les requêtes sur la table de base sont automatiquement réécrites pour utiliser la MV quand c’est bénéfique, sans modification de code requise.

CREATE MATERIALIZED VIEW `project.dataset.mv_daily_revenue`
OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 30,
  max_staleness = INTERVAL '4' HOUR  -- Accepter l'ancienneté pour les dashboards
)
AS
SELECT
  DATE(transaction_time) AS transaction_date,
  product_category,
  SUM(amount) AS total_revenue,
  COUNT(*) AS transaction_count
FROM `project.dataset.transactions`
GROUP BY 1, 2;

L’option max_staleness est le levier de réglage clé. La définir à 4 heures indique à BigQuery « j’accepte des données vieilles de 4 heures maximum », ce qui permet à l’optimiseur d’utiliser la MV plus agressivement et réduit la fréquence de rafraîchissement. Pour les dashboards temps réel, omettez-la. Pour les rapports hebdomadaires, définissez-la en jours.

Ce qu’il faut éviter :

Les fonctions non déterministes comme CURRENT_TIMESTAMP() dans votre définition de MV bloquent le rafraîchissement incrémental. La MV revient à une recomputation complète, ce qui annule tout l’intérêt.

Les MV ne peuvent pas non plus inclure ORDER BY, LIMIT, HAVING ou des sous-requêtes dans la clause SELECT. Gardez-les simples : des agrégations sur des tables de base filtrées.

N’ignorez pas les paramètres de staleness. La valeur par défaut est toujours à jour, ce qui signifie un overhead de rafraîchissement constant. Si vos utilisateurs de dashboard tolèrent des données vieilles de 30 minutes, dites-le à BigQuery.

Impact sur la performance : Google rapporte des requêtes scannant 500 Go réduites à 2,5 Mo (une réduction de 200 000x) avec des MV bien conçues. L’accélération des requêtes varie de 5 à 100x pour les agrégations complexes. Limites : 20 MV par dataset, 100 par projet.

Pattern 3 : Sketches HyperLogLog++ pour des comptages distincts composables

Supposons que vous ayez besoin de compter les utilisateurs uniques sur des fenêtres glissantes de 7, 30 et 90 jours. Avec COUNT(DISTINCT user_id), chaque fenêtre nécessite de scanner toutes les données sous-jacentes. Triplez les fenêtres, triplez le coût.

Les sketches HyperLogLog++ résolvent ce problème. Ce sont des structures de données probabilistes qui estiment la cardinalité avec une précision d’environ 97-99 %, et elles sont additives. Précalculez des sketches quotidiens, puis fusionnez-les pour n’importe quelle plage de dates en millisecondes.

-- Étape 1 : Créer des sketches quotidiens (exécuter une fois, ajouter quotidiennement)
CREATE OR REPLACE TABLE `project.dataset.daily_user_sketches` AS
SELECT
  event_date,
  HLL_COUNT.INIT(user_id, 15) AS user_sketch  -- Précision 10–24
FROM `project.dataset.events`
GROUP BY event_date;

-- Étape 2 : Fusionner les sketches pour n'importe quelle plage de dates (instantané, quelle que soit la plage)
SELECT HLL_COUNT.MERGE(user_sketch) AS unique_users_30d
FROM `project.dataset.daily_user_sketches`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

-- Alternative plus simple pour les requêtes ponctuelles
SELECT APPROX_COUNT_DISTINCT(user_id) AS approx_users
FROM `project.dataset.events`
WHERE event_date >= '2025-01-01';

Le paramètre de précision (10-24) contrôle le compromis entre précision et taille du sketch. Une précision de 15 donne environ 0,4 % d’erreur, ce qui est suffisant pour la plupart des cas d’usage analytics. GA4 utilise en interne une précision de 14 pour les comptages d’utilisateurs et de 12 pour les sessions.

Ce qu’il faut éviter :

APPROX_COUNT_DISTINCT est pratique mais non composable. Vous ne pouvez pas additionner le comptage approximatif d’hier à celui d’aujourd’hui pour obtenir un comptage valide sur 2 jours. Pour les fenêtres glissantes, vous devez utiliser HLL_COUNT.INIT et HLL_COUNT.MERGE.

Les valeurs de précision basses (<12) quand la précision compte produiront une variance notable dans les résultats.

Impact sur la performance : Exécution 4 à 8x plus rapide qu’un COUNT(DISTINCT) exact, consommation de slots 8x moindre. Le vrai gain, c’est les fenêtres glissantes : un comptage d’utilisateurs uniques sur 90 jours qui prenait 30 secondes prend maintenant 200 millisecondes.

Maîtrise des données imbriquées

Les champs imbriqués et répétés de BigQuery sont puissants mais mal compris. Ce pattern est essentiel pour quiconque travaille avec GA4, Firebase ou des données d’événements.

Pattern 4 : Sous-requêtes UNNEST inline

GA4 et Firebase stockent les paramètres d’événements sous forme d’arrays de structs (un schéma comme event_params ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, ...>>>). L’approche naïve utilise CROSS JOIN UNNEST, ce qui explose les lignes et échoue quand des paramètres sont manquants.

L’approche correcte utilise des sous-requêtes UNNEST inline. Elles extraient les valeurs proprement, retournent NULL pour les paramètres manquants et conservent une ligne par événement.

-- La bonne méthode : sous-requêtes UNNEST inline
SELECT
  event_date,
  event_name,
  user_pseudo_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign  -- NULL si absent
FROM `project.analytics_XXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
  AND event_name = 'page_view';

Pour une utilisation répétée, encapsulez l’extraction dans une fonction temporaire :

CREATE TEMP FUNCTION GetParamString(params ANY TYPE, param_key STRING)
AS ((SELECT value.string_value FROM UNNEST(params) WHERE key = param_key));

CREATE TEMP FUNCTION GetParamInt(params ANY TYPE, param_key STRING)
AS ((SELECT value.int_value FROM UNNEST(params) WHERE key = param_key));

SELECT
  event_name,
  GetParamString(event_params, 'page_location') AS page_location,
  GetParamInt(event_params, 'ga_session_id') AS ga_session_id
FROM `project.analytics_XXXXX.events_*`
WHERE _TABLE_SUFFIX = '20250115';

Ce qu’il faut éviter :

Plusieurs instructions CROSS JOIN UNNEST pour des paramètres optionnels retourneront zéro ligne si un paramètre est manquant sur n’importe quel événement.

Oublier le filtre _TABLE_SUFFIX sur les tables shardées par date scanne l’ensemble du dataset, potentiellement des années de données. Filtrez-le toujours en premier.

Utiliser SAFE_OFFSET ou l’indexation d’array suppose que les paramètres apparaissent dans un ordre cohérent. Ce n’est pas le cas. Filtrez toujours par clé.

Impact sur la performance : Les requêtes sur données imbriquées évitent les opérations de shuffle, les rendant plus rapides que des JOINs équivalents sur des tables aplaties. Les économies de stockage atteignent 35 à 74 % par rapport aux schémas complètement dénormalisés.

Patterns de window functions

Les window functions sont l’arme secrète de BigQuery pour l’analytics. Ces deux patterns débloquent des solutions élégantes à des problèmes courants.

Pattern 5 : QUALIFY pour un filtrage élégant sur les window functions

Filtrer sur les résultats de window functions (comme « obtenir la commande la plus récente par client ») nécessite traditionnellement une sous-requête ou CTE :

-- L'ancienne méthode : wrapper en sous-requête
SELECT
  customer_id,
  order_id,
  order_date,
  order__status
FROM (
  SELECT
    customer_id,
    order_id,
    order_date,
    order__status,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM `project.dataset.base__shopify__orders`
)
WHERE rn = 1;

La clause QUALIFY de BigQuery fait cela inline :

-- La méthode élégante : QUALIFY
SELECT
  customer_id,
  order_id,
  order_date,
  order__status
FROM `project.dataset.base__shopify__orders`
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_date DESC
) = 1;

Même résultat, moins d’imbrication, plus facile à lire. QUALIFY fonctionne avec n’importe quelle window function :

-- Top 3 des produits par catégorie selon le revenu
SELECT
  product__category,
  product__name,
  product__revenue
FROM `project.dataset.mrt__sales__products`
QUALIFY RANK() OVER (PARTITION BY product__category ORDER BY product__revenue DESC) <= 3;

-- Premier et dernier événement par session
SELECT
  session_id,
  event_name,
  event_time,
  user_id
FROM `project.dataset.int__ga4__events_sessionized`
QUALIFY ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time) = 1
    OR ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time DESC) = 1;

Ce qu’il faut éviter :

Encapsuler des filtres simples sur window functions dans des CTE quand QUALIFY suffirait. Pour des transformations multi-étapes complexes, les CTE restent appropriées, mais pour un filtrage en une étape, QUALIFY est plus propre.

Impact sur la performance : Le plan d’exécution est identique à l’approche par sous-requête. QUALIFY est du pur sucre syntaxique, mais il rend votre code significativement plus lisible.

L’ordre d’exécution est : FROM → WHERE → GROUP BY → HAVING → WINDOW → QUALIFY → DISTINCT → ORDER BY → LIMIT.

Pattern 6 : Détection gap-and-island pour les séries consécutives

Identifier des séquences consécutives (séries de connexions utilisateur, uptime de production, plages de dates sans lacunes) est étonnamment délicat. Le pattern gap-and-island utilise une soustraction astucieuse de ROW_NUMBER pour grouper les valeurs consécutives.

L’insight est que soustraire un numéro de ligne séquentiel de dates consécutives produit le même résultat pour chaque groupe consécutif, ce qui devient une clé de regroupement.

WITH numbered_activity AS (
  SELECT
    user_id,
    activity_date,
    -- Soustraire le numéro de ligne de la date ; les dates consécutives donnent la même valeur
    DATE_SUB(activity_date, INTERVAL ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY activity_date
    ) DAY) AS island_id
  FROM `project.dataset.user_activity`
)

SELECT
  user_id,
  MIN(activity_date) AS streak__started_at,
  MAX(activity_date) AS streak__ended_at,
  DATE_DIFF(MAX(activity_date), MIN(activity_date), DAY) + 1 AS streak__duration_days
FROM numbered_activity
GROUP BY user_id, island_id
HAVING DATE_DIFF(MAX(activity_date), MIN(activity_date), DAY) + 1 >= 7;  -- Uniquement les séries de 7+ jours

Exemple de données montrant le calcul d’island :

Les trois premières dates forment un island (série de 3). Les deux dernières en forment un autre (série de 2).

Ce qu’il faut éviter :

Les self-joins pour la détection de séries ont une complexité O(n²). L’approche par window function est O(n).

Les CTE récursives pourraient sembler naturelles pour ce problème, mais BigQuery ne les supporte pas. Le pattern gap-and-island est la solution standard.

Cas d’usage : Séries d’achievements gaming, applications de suivi d’habitudes, calcul d’uptime SLA, consolidation de plages de dates qui se chevauchent, identification de lacunes dans les séries temporelles.

Stratégies incrémentales dbt

dbt sur BigQuery offre plusieurs stratégies incrémentales. Choisir la mauvaise signifie soit des problèmes de correction, soit des coûts 10x supérieurs au nécessaire.

Pattern 7 : insert_overwrite avec partitions statiques

La stratégie merge par défaut fonctionne en scannant l’intégralité de la table de destination pour trouver les lignes correspondantes. Pour les tables d’événements à forte insertion qui se mettent rarement à jour, c’est du gaspillage. insert_overwrite remplace des partitions entières sans correspondance de lignes requise.

{% set partitions_to_replace = [
  'current_date()',
  'date_sub(current_date(), interval 1 day)',
  'date_sub(current_date(), interval 2 day)'
] %}

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

SELECT
  DATE(event_timestamp) AS event_date,
  user_id,
  event_name,
  event_params
FROM {{ source('analytics', 'raw_events') }}
{% if is_incremental() %}
WHERE DATE(event_timestamp) IN ({{ partitions_to_replace | join(', ') }})
{% endif %}

L’option copy_partitions: true permet une optimisation à coût zéro : quand les partitions ne changent pas, BigQuery copie les métadonnées au lieu de réécrire les données.

Ce qu’il faut éviter :

Utiliser merge pour les tables d’événements et de faits qui ne se mettent jamais à jour. insert_overwrite peut être 10x+ plus rapide car il ne scanne pas la destination.

La détection dynamique de partitions sur d’énormes tables introduit de l’imprévisibilité. Les listes de partitions statiques rendent le coût et le temps d’exécution de votre pipeline déterministes.

Oublier copy_partitions: true quand c’est applicable. C’est de la performance gratuite.

Impact sur la performance : Le coût évolue linéairement avec la taille de la partition, pas la taille totale de la table. Une table de 10 To avec des partitions quotidiennes de 1 Go se traite dans le même temps qu’une table de 100 Go avec la même taille de partition. Idéal pour : flux d’événements, logs, faits immuables.

Pattern 8 : Incremental predicates pour l’optimisation MERGE

Parfois vous avez besoin de merge pour des tables avec des mises à jour, des corrections tardives ou des patterns CDC. Mais sans optimisation, merge scanne l’intégralité de la table de destination pour la correspondance de la clause ON.

Les incremental predicates ajoutent des filtres à l’instruction MERGE elle-même, permettant le partition pruning sur la table de destination.

{{
  config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    partition_by={
      'field': 'order_date',
      'data_type': 'date'
    },
    cluster_by=['order_id'],
    incremental_predicates=[
      "DBT_INTERNAL_DEST.order_date >= date_sub(current_date(), interval 14 day)"
    ]
  )
}}

SELECT
  order_id,
  customer_id,
  order__status,
  order_date,
  order__updated_at
FROM {{ source('orders', 'raw_orders') }}
{% if is_incremental() %}
WHERE order__updated_at >= (SELECT DATE_SUB(MAX(order__updated_at), INTERVAL 3 DAY) FROM {{ this }})
{% endif %}

Le prédicat DBT_INTERNAL_DEST.order_date >= date_sub(current_date(), interval 14 day) indique à BigQuery de ne scanner que les 14 derniers jours de la table de destination lors de la correspondance des lignes.

Deux optimisations se cumulent ici : le prédicat permet le partition pruning, et cluster_by=['order_id'] (clustering sur la clé unique) permet le cluster pruning au sein de ces partitions.

Ce qu’il faut éviter :

Exécuter merge sans incremental_predicates sur de grandes tables partitionnées. Vous scannez des téraoctets quand vous n’avez besoin que de gigaoctets.

Oublier de clusterer sur unique_key. L’opération merge fait la correspondance sur cette colonne, et le clustering rend cette correspondance 50 %+ plus rapide.

Définir des prédicats trop étroits et manquer les données arrivant en retard. Si les commandes peuvent être mises à jour jusqu’à 7 jours après leur création, votre prédicat a besoin d’une fenêtre d’au moins 7 jours.

Impact sur la performance : Atteint une réduction de 88 %+ des données scannées dans la table de destination. Combiné avec le clustering sur unique_key, la réduction totale du scan peut dépasser 95 %.

Patterns d’analytics marketing

Ces patterns résolvent des problèmes spécifiques aux données marketing : définitions de sessions personnalisées et modélisation d’attribution.

Pattern 9 : Sessionisation avec LAG + somme cumulative

GA4 définit les sessions avec un timeout d’inactivité de 30 minutes. Mais votre business peut avoir besoin de règles différentes : timeouts de 15 minutes pour les apps à fort engagement, coupures de session basées sur les campagnes, ou raccordement cross-device.

Ce pattern construit des sessions personnalisées à partir d’événements bruts en utilisant la détection de gaps :

WITH events_with_gaps AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    event_name,
    traffic_source.source AS traffic__source,
    traffic_source.medium AS traffic__medium,
    CASE
      WHEN LAG(event_timestamp) OVER (
        PARTITION BY user_pseudo_id ORDER BY event_timestamp
      ) IS NULL THEN 1  -- Premier événement = nouvelle session
      WHEN TIMESTAMP_DIFF(
        event_timestamp,
        LAG(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp),
        MINUTE
      ) > 30 THEN 1  -- Gap > 30 min = nouvelle session
      ELSE 0
    END AS event__is_new_session
  FROM `project.analytics_XXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
),

sessionized AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    event_name,
    traffic__source,
    traffic__medium,
    event__is_new_session,
    SUM(event__is_new_session) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp
    ) AS session_number
  FROM events_with_gaps
)

SELECT
  user_pseudo_id,
  event_timestamp,
  event_name,
  traffic__source,
  traffic__medium,
  session_number,
  CONCAT(user_pseudo_id, '_', session_number) AS custom_session_id
FROM sessionized;

Le pattern fonctionne en deux étapes : LAG identifie les frontières de session en comparant le timestamp de chaque événement à l’événement précédent, puis une somme cumulative des indicateurs de frontière crée les numéros de session.

Pour ajouter des métriques au niveau de la session, agrégez dans un CTE subséquent :

, session_metrics AS (
  SELECT
    custom_session_id,
    user_pseudo_id,
    MIN(event_timestamp) AS session__started_at,
    MAX(event_timestamp) AS session__ended_at,
    TIMESTAMP_DIFF(MAX(event_timestamp), MIN(event_timestamp), SECOND) AS session__duration_seconds,
    COUNT(*) AS session__events,
    COUNTIF(event_name = 'page_view') AS session__pageviews
  FROM sessionized
  GROUP BY custom_session_id, user_pseudo_id
)

Ce qu’il faut éviter :

Se fier uniquement à ga_session_id quand votre logique métier diffère. La définition de session de GA4 peut ne pas correspondre à vos besoins.

Des timeouts de session trop courts fragmentent les sessions légitimes ; trop longs fusionnent des visites séparées. Testez avec vos données réelles.

Extensions : Ajoutez la coupure de session basée sur les campagnes (de nouveaux paramètres UTM déclenchent une nouvelle session), le raccordement de session cross-device en utilisant user_id, ou des règles basées sur l’engagement (certains événements démarrent toujours de nouvelles sessions).

Pattern 10 : Modélisation d’attribution multi-touch

L’attribution last-click est la valeur par défaut car elle est simple. Elle est aussi fausse : elle sur-crédite les canaux bas de funnel comme la recherche branded tout en ignorant les campagnes de notoriété qui ont initié le parcours.

Ce pattern calcule quatre modèles d’attribution en une seule requête : first-touch, last-touch, linéaire et position-based.

WITH touchpoints AS (
  SELECT
    c.transaction_id,
    c.transaction__revenue,
    t.touchpoint__source,
    t.touchpoint__medium,
    t.touchpoint__campaign,
    t.touchpoint__timestamp,
    ROW_NUMBER() OVER (
      PARTITION BY c.transaction_id ORDER BY t.touchpoint__timestamp
    ) AS touch_position,
    ROW_NUMBER() OVER (
      PARTITION BY c.transaction_id ORDER BY t.touchpoint__timestamp DESC
    ) AS reverse_position,
    COUNT(*) OVER (PARTITION BY c.transaction_id) AS total_touches
  FROM `project.dataset.conversions` c
  JOIN `project.dataset.touchpoints` t
    ON c.user_id = t.user_id
    AND t.touchpoint__timestamp <= c.conversion__timestamp
    AND t.touchpoint__timestamp >= TIMESTAMP_SUB(c.conversion__timestamp, INTERVAL 30 DAY)  -- Lookback de 30 jours
)

SELECT
  touchpoint__source,
  touchpoint__medium,

  -- First-touch : 100 % de crédit à la première interaction
  SUM(CASE WHEN touch_position = 1 THEN transaction__revenue ELSE 0 END) AS attribution__first_touch_revenue,

  -- Last-touch : 100 % de crédit à la dernière interaction
  SUM(CASE WHEN reverse_position = 1 THEN transaction__revenue ELSE 0 END) AS attribution__last_touch_revenue,

  -- Linéaire : Crédit égal à tous les touchpoints
  SUM(transaction__revenue / total_touches) AS attribution__linear_revenue,

  -- Position-based : 40 % premier, 40 % dernier, 20 % répartis entre le milieu
  SUM(transaction__revenue * CASE
    WHEN total_touches = 1 THEN 1.0
    WHEN touch_position = 1 THEN 0.4
    WHEN reverse_position = 1 THEN 0.4
    WHEN total_touches = 2 THEN 0  -- Cas limite : seulement premier et dernier, pas de milieu
    ELSE 0.2 / (total_touches - 2)
  END) AS attribution__position_based_revenue

FROM touchpoints
GROUP BY touchpoint__source, touchpoint__medium
ORDER BY attribution__position_based_revenue DESC;

La fenêtre de lookback (30 jours dans cet exemple) est une logique métier cruciale. Trop courte et vous manquez les touchpoints haut de funnel. Trop longue et vous attribuez du revenu à des interactions anciennes et non pertinentes.

Ce qu’il faut éviter :

Joindre sur user_id sans bornes temporelles attribue des touchpoints vieux de mois ou d’années. Incluez toujours une fenêtre de lookback.

Oublier les cas limites. Quand total_touches = 2, il n’y a pas de milieu pour répartir les 20 %. La formule 0.2 / (total_touches - 2) divise par zéro. Gérez-le explicitement.

Notes de performance : Un seul scan avec des window functions calcule les quatre modèles. Pour les grands datasets, envisagez de pré-agréger les touchpoints à la granularité quotidienne avant la jointure de conversion.

Extensions : Attribution time-decay (les touchpoints récents reçoivent exponentiellement plus de crédit), analyse des chemins de canaux avec STRING_AGG pour voir les chemins de conversion courants, ou pondération personnalisée basée sur votre modèle business.

Référence rapide

Chaque pattern encode une façon de penser les problèmes de données. Le partition pruning vous apprend à penser à la disposition des données. Les window functions vous apprennent à penser en termes de contexte de ligne. Les sketches HLL vous apprennent que des réponses approximatives à 1 % du coût sont souvent meilleures que des réponses exactes que vous ne pouvez pas vous permettre de calculer.

Les meilleurs analytics engineers que je connais ont internalisé ces patterns si profondément qu’ils les appliquent automatiquement. Cette fluidité vient de la pratique. Choisissez un pattern que vous n’utilisez pas aujourd’hui, appliquez-le à un problème réel cette semaine, et regardez-le devenir une seconde nature.