Patterns de reporting publicitaire avec dbt

dbt est la couche de transformation standard pour les données publicitaires. Le package Fivetran préconstruit et les implémentations personnalisées suivent des patterns de modélisation bien établis.

Le package dbt_ad_reporting

Le package dbt_ad_reporting de Fivetran (v2.4.0, janvier 2026) couvre 11 plateformes publicitaires et produit des modèles unifiés aux niveaux compte, campagne, groupe d’annonces, annonce, mot-clé, requête de recherche, et URL. Il normalise cinq métriques principales à travers toutes les plateformes : clics, impressions, dépenses, conversions et conversions_value. Une colonne platform identifie la source de chaque ligne.

Le package suit l’architecture trois couches appliquée spécifiquement aux données publicitaires :

1. Les packages source gèrent le transtypage et l’aliasing — rendant les tables brutes Fivetran interrogeables avec des noms de colonnes cohérents
2. Les packages plateformes joignent les dimensions avec les métriques et gèrent la logique de conversion spécifique à chaque plateforme (aplatissement des tableaux d’actions Meta, conversion des micros Google, remappage de la hiérarchie inversée LinkedIn)
3. La couche de consolidation réunit tout via UNION dans des modèles unifiés où chaque plateforme parle le même langage

Cette organisation en couches signifie que les packages de plateformes individuels peuvent être utilisés indépendamment si seules les données Google Ads sont nécessaires, ou que le package de consolidation complet peut être utilisé pour le reporting cross-plateforme.

Construire des modèles cross-plateformes sans Fivetran

Si Fivetran n’est pas l’outil d’extraction, le même pattern peut être répliqué avec des modèles personnalisés. L’idée centrale : normaliser chaque plateforme indépendamment, puis les réunir via UNION dans un modèle unifié.

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

UNION ALL

SELECT
    date_day,
    'facebook_ads' AS platform,
    campaign_id,
    campaign_name,
    impressions,
    clicks,
    spend,
    conversions,
    conversions_value
FROM {{ ref('int__facebook_ads__campaign_report') }}

Le travail critique se fait dans la couche intermédiaire, où les particularités de chaque plateforme sont normalisées avant le UNION.

Normalisation spécifique aux plateformes

Chaque plateforme a besoin de son propre modèle intermédiaire qui gère les difficultés d’ingénierie de données spécifiques à la sortie API de cette plateforme.

Normalisation Google Ads :

• Diviser le coût par 1 000 000 (micros vers unités monétaires)
• Gérer les campagnes Performance Max qui ne rapportent qu’au niveau campagne
• Mapper les types de campagnes Google vers un enum standardisé

Normalisation Meta Ads :

• Aplatir le tableau JSON actions imbriqué en colonnes individuelles
• Extraire des types d’actions spécifiques (achats, prospects, clics sur liens) du tableau
• Gérer la répartition de juin 2025 entre les événements on-Meta et off-Meta

Normalisation LinkedIn Ads :

• Remapper les noms de la hiérarchie inversée de LinkedIn (“Campaign” → “Ad Group”, “Campaign Group” → “Campaign”)
• Gérer la méthodologie différente de suivi des conversions

L’objectif de la couche intermédiaire : au moment où les données atteignent le UNION dans le mart, chaque plateforme est identique. Mêmes noms de colonnes, mêmes types de données, mêmes unités, même granularité.

Gérer les métriques spécifiques aux plateformes

Toutes les métriques n’existent pas sur toutes les plateformes. LinkedIn a les actions sociales (j’aime, partages, abonnements). Meta a les ThruPlays et les taux de completion de vidéo. Google a le Quality Score.

Le bon pattern : garder les métriques spécifiques aux plateformes dans les modèles au niveau plateforme plutôt que de polluer la vue unifiée avec des NULLs. Le modèle UNION mrt__marketing__ad_performance_daily ne contient que les cinq métriques universelles. Si quelqu’un a besoin des données d’engagement social LinkedIn, il interroge directement mrt__marketing__linkedin__campaign_performance.

Cela maintient le modèle unifié propre et interrogeable pour les 90 % de questions qui sont cross-plateformes (“combien avons-nous dépensé ?”), tout en préservant la profondeur spécifique à chaque plateforme pour les 10 % qui en ont besoin.

Tests de réconciliation

Un pattern de test qui évite de véritables problèmes : des tests de réconciliation qui vérifient que les dépenses unifiées correspondent à la somme des dépenses individuelles par plateforme. Il s’agit d’un test singulier qui détecte les bugs de transformation, les données manquantes et la dérive du pipeline.

-- tests/assert_unified_spend_matches_platforms.sql
WITH unified AS (
    SELECT SUM(spend) AS total_spend
    FROM {{ ref('mrt__marketing__ad_performance_daily') }}
    WHERE date_day >= CURRENT_DATE() - 7
),
platforms AS (
    SELECT SUM(spend) AS total_spend
    FROM (
        SELECT spend FROM {{ ref('int__google_ads__campaign_report') }}
        WHERE date_day >= CURRENT_DATE() - 7
        UNION ALL
        SELECT spend FROM {{ ref('int__facebook_ads__campaign_report') }}
        WHERE date_day >= CURRENT_DATE() - 7
    )
)
SELECT
    unified.total_spend AS unified_total,
    platforms.total_spend AS platform_total,
    ABS(unified.total_spend - platforms.total_spend) AS difference
FROM unified
CROSS JOIN platforms
WHERE ABS(unified.total_spend - platforms.total_spend) / NULLIF(platforms.total_spend, 0) > 0.03

Autoriser une variance de 1 à 3 % pour les écarts liés aux fuseaux horaires, mais tout ce qui dépasse signale un bug de pipeline. Exécuter ce test en CI et à chaque run dbt de production.

Un second pattern de réconciliation compare les totaux de l’entrepôt avec les totaux de l’interface des plateformes pour une période récente. C’est une vérification manuelle, pas un test automatisé, mais elle renforce la confiance que le pipeline capture l’image complète. Documenter la plage de variance attendue et investiguer tout ce qui en sort.

La structure du modèle mart

Un mart de données publicitaires mature inclut typiquement ces modèles :

Les modèles cross-plateformes utilisent le pattern UNION. Les modèles spécifiques aux plateformes référencent directement la couche intermédiaire et incluent l’ensemble complet des métriques spécifiques à chaque plateforme.

Intégration avec l’attribution

Le modèle de performance publicitaire unifié fournit le côté dépenses de l’équation. Le côté revenus provient du CRM, de la plateforme e-commerce, ou des modèles d’attribution. La jointure de ces deux éléments donne un vrai ROAS — pas le ROAS auto-déclaré de chaque plateforme, mais un calcul propre utilisant des données de revenus cohérentes à travers tous les canaux.

-- mrt__marketing__channel_roas.sql
SELECT
    ad.date_day,
    ad.platform,
    SUM(ad.spend) AS total_spend,
    SUM(attr.attributed_revenue) AS total_revenue,
    SAFE_DIVIDE(SUM(attr.attributed_revenue), SUM(ad.spend)) AS roas
FROM {{ ref('mrt__marketing__ad_performance_daily') }} ad
LEFT JOIN {{ ref('mrt__attribution__channel_daily') }} attr
    ON ad.date_day = attr.date_day
    AND ad.platform = attr.channel
GROUP BY 1, 2

Centraliser les données publicitaires dans l’entrepôt permet un vrai ROAS cross-canal depuis une seule requête.