Intégration dbt pour Google Ads DTS

DTS livre des tables Google Ads brutes. La couche dbt gère les transformations nécessaires pour produire un reporting utilisable : filtrage du type de clic, conversion des micros, JOIN attributs/stats, et configuration de la stratégie incrémentale adaptée au comportement de remplacement de partitions de DTS.

Le point de départ : le package dbt de Fivetran

Même si vous chargez via DTS plutôt que Fivetran, le package dbt_google_ads de Fivetran vaut la peine d’être étudié. Il produit 29 modèles matérialisés couvrant les niveaux compte, campagne, groupe d’annonces, mot-clé, annonce, URL et rapport de recherche. Les modèles source du package peuvent être adaptés pour pointer vers des tables DTS avec un mapping de schéma mineur — la logique de transformation est solide même si les hypothèses sources doivent être ajustées.

Pour les équipes qui ne sont pas sur Fivetran, le package sert d’implémentation de référence : voilà à quoi ressemble un projet Google Ads dbt de niveau production, voilà quelles tables joindre avec lesquelles, et voilà quelles métriques sont fiables.

Si vous utilisez déjà Fivetran pour d’autres connecteurs et envisagez de passer Google Ads à DTS pour économiser de l’argent, faites d’abord les calculs. Le package de Fivetran élimine une grande partie du travail décrit ci-dessous.

Configuration des sources

Commencez par déclarer les tables DTS comme sources dbt. Vous référencerez les tables préfixées p_ — les versions partitionnées qui permettent un filtrage efficace. Si votre Customer ID change ou si vous ajoutez des comptes MCC, la mise à jour du YAML source est le seul changement nécessaire :

version: 2

sources:
  - name: google_ads
    database: "{{ env_var('GCP_PROJECT') }}"
    schema: google_ads_CUSTOMERID  # Remplacer par votre dataset réel
    tables:
      - name: p_CampaignBasicStats_CUSTOMERID
        identifier: p_CampaignBasicStats_CUSTOMERID
      - name: p_Campaign_CUSTOMERID
        identifier: p_Campaign_CUSTOMERID
      - name: p_AdGroupBasicStats_CUSTOMERID
        identifier: p_AdGroupBasicStats_CUSTOMERID
      - name: p_AdGroup_CUSTOMERID
        identifier: p_AdGroup_CUSTOMERID

Le nom du dataset inclut par défaut le Customer ID (google_ads_1234567890). Pour les configurations MCC où plusieurs comptes atterrissent dans le même dataset, le Customer ID est intégré dans les données elles-mêmes plutôt que dans le nom de la table.

La stratégie incrémentale

DTS rafraîchit les données en remplaçant des partitions de dates entières à chaque exécution. Quand DTS s’exécute pour aujourd’hui, il écrase les partitions d’aujourd’hui dans toutes les tables. Ce comportement a une implication directe sur la configuration des modèles dbt incrémentaux sur les données DTS : vous avez besoin d’une stratégie qui respecte le remplacement de partitions plutôt que d’ajouter de nouvelles lignes.

Sur BigQuery, insert_overwrite est le bon choix :

{{ config(
  materialized='incremental',
  partition_by={"field": "_DATA_DATE", "data_type": "date"},
  incremental_strategy='insert_overwrite'
) }}

Cela remplace les partitions du modèle dbt à chaque exécution, correspondant au comportement de DTS. Si DTS a mis à jour les 7 derniers jours de données, votre modèle dbt remplace les mêmes 7 jours. Pas de doublons, pas d’accumulation de données obsolètes.

L’alternative — merge avec un unique_key — fonctionne mais est moins efficace pour ce pattern. DTS remplace des partitions entières ; un merge effectuerait une comparaison ligne par ligne sur potentiellement des millions de lignes alors que le remplacement de partitions est tout ce dont vous avez besoin.

La fenêtre de lookback pour les conversions

Les conversions Google Ads arrivent en retard. Un clic se produit aujourd’hui ; la conversion issue de ce clic peut être attribuée en retour 7, 14 ou 30 jours plus tard lorsque la fenêtre de conversion se ferme. DTS gère cela en incluant une fenêtre de rafraîchissement configurable (voir Google Ads BigQuery DTS Setup), mais vos modèles dbt doivent correspondre à ce comportement pour éviter des chiffres de conversion silencieusement obsolètes.

Si votre fenêtre de rafraîchissement DTS est de 30 jours et que votre modèle dbt incrémental ne traite que les 7 derniers jours, vous manquerez les mises à jour de conversion que DTS a apportées aux données plus anciennes. Le correctif est de faire correspondre votre lookback dbt à votre fenêtre de rafraîchissement DTS :

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

Cela signifie que chaque exécution dbt retraite les 30 derniers jours de données. Sur BigQuery avec insert_overwrite, les partitions remplacées ne créent pas de doublons — BigQuery échange la partition de manière atomique. Le compromis est le coût des requêtes contre la précision des données. Pour les données publicitaires où la précision des conversions affecte directement les décisions d’optimisation, la précision l’emporte.

La fenêtre de 30 jours est le bon défaut pour la plupart des comptes. Les conversions Google Ads peuvent techniquement se mettre à jour rétroactivement jusqu’à 90 jours, mais la plupart des changements se stabilisent dans les 30 jours. Si vous avez des événements de conversion à entonnoir plus long (courant en B2B), étendez à 60 ou 90 jours et documentez pourquoi.

C’est une application spécifique du pattern de fenêtre de lookback général pour les modèles incrémentaux. Le détail spécifique à Google Ads est que votre fenêtre de lookback doit correspondre exactement à votre fenêtre de rafraîchissement DTS — sinon vous payez pour que DTS mette à jour des données que vos modèles dbt ne récupéreront pas.

Le modèle de base complet

En combinant tout : un modèle de base qui gère le piège d’impressions ClickType, la conversion des micros, le JOIN entre les tables attributs et stats, et la stratégie incrémentale :

-- models/base/google_ads/base__google_ads__campaign_stats.sql
{{ config(
  materialized='incremental',
  partition_by={"field": "date_day", "data_type": "date"},
  incremental_strategy='insert_overwrite'
) }}

SELECT
  cs._DATA_DATE AS date_day,
  c.campaign_name,
  c.campaign_status,
  cs.campaign_id,
  -- Impressions : filtrer sur URL_CLICKS pour éviter la duplication ClickType
  SUM(CASE WHEN cs.segments_click_type = 'URL_CLICKS'
      THEN cs.metrics_impressions ELSE 0 END) AS impressions,
  -- Clics : additionner sur tous les types de clics
  SUM(cs.metrics_clicks) AS clicks,
  SUM(cs.metrics_interactions) AS interactions,
  -- Coût : diviser par 1 000 000 pour convertir les micros en devise
  SUM(cs.metrics_cost_micros) / 1000000 AS cost,
  SUM(cs.metrics_conversions) AS conversions,
  SUM(cs.metrics_conversions_value) AS conversions_value,
  SAFE_DIVIDE(
    SUM(cs.metrics_conversions_value),
    SUM(cs.metrics_cost_micros) / 1000000
  ) AS roas
FROM `{{ source('google_ads', 'p_CampaignBasicStats_CUSTOMERID') }}` cs
-- JOIN avec la table attributs pour les noms lisibles
LEFT JOIN `{{ source('google_ads', 'p_Campaign_CUSTOMERID') }}` c
  ON cs.campaign_id = c.campaign_id
  -- Filtre _LATEST_DATE : obtenir le nom de campagne actuel, pas les noms historiques
  AND c._DATA_DATE = c._LATEST_DATE
{% if is_incremental() %}
WHERE cs._DATA_DATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
{% endif %}
GROUP BY 1, 2, 3, 4

Quelques éléments de ce modèle méritent d’être explicités :

c._DATA_DATE = c._LATEST_DATE — les tables attributs sont aussi partitionnées par date, avec des valeurs changeant au fur et à mesure que les campagnes sont renommées ou reconfigurées. La pseudo-colonne _LATEST_DATE filtre sur l’enregistrement d’attribut le plus récent sans que vous ayez besoin de connaître explicitement la date d’aujourd’hui. Sans cela, les campagnes qui ont été renommées afficheront plusieurs lignes (une par nom).
Le SAFE_DIVIDE pour le ROAS — prévient les erreurs de division par zéro pour les campagnes avec zéro dépense.
Le GROUP BY sur les attributs de campagne — puisque vous joignez une table de dimension, vous devez grouper par les attributs pour éviter l’effet de ventilateur.

Reporting cross-plateformes

Si Google Ads est l’une de plusieurs plateformes publicitaires que vous intégrez dans un modèle unifié, la couche intermédiaire est l’endroit où la normalisation spécifique à la plateforme se produit avant le UNION. Le modèle de base ci-dessus gère les particularités spécifiques à DTS. Un modèle intermédiaire normalise vers le schéma partagé que dbt_ad_reporting ou votre modèle unifié personnalisé attend :

-- models/intermediate/google_ads/int__google_ads__campaign_report.sql
SELECT
  date_day,
  'google_ads' AS platform,
  campaign_id,
  campaign_name,
  impressions,
  clicks,
  cost AS spend,
  conversions,
  conversions_value
FROM {{ ref('base__google_ads__campaign_stats') }}

C’est le modèle qui alimente votre UNION mart cross-plateformes. Au moment où les données atteignent le UNION, toutes les plateformes se ressemblent — mêmes noms de colonnes, mêmes types de données, mêmes unités. Toute la gestion spécifique à DTS est encapsulée dans la couche base et invisible pour les consommateurs en aval.