Patterns en aval du modèle publicitaire unifié

Trois patterns en aval deviennent pratiques une fois qu’un modèle publicitaire unifié multi-plateformes est opérationnel : le ROAS mixte, le suivi du rythme budgétaire, et la préparation des données pour la modélisation du mix media. Chacun répond à une question qui requiert des données centralisées multi-plateformes.

ROAS mixte

Le ROAS mixte est le revenu total divisé par les dépenses publicitaires totales sur tous les canaux. C’est la mesure la plus honnête de l’efficacité marketing que vous puissiez construire, et elle contourne entièrement les désaccords d’attribution.

Après iOS 14, la précision de l’attribution par canal est devenue peu fiable. Les chiffres de conversion de Meta ont chuté. Ceux de Google n’ont pas beaucoup changé. LinkedIn a continué avec sa fenêtre de 90 jours. Argumenter sur le canal qui avait “réellement” généré quelle conversion est devenu de plus en plus improductif. Le ROAS mixte adopte une approche différente : au lieu d’essayer d’attribuer du crédit par canal, regardez le ratio global et optimisez en conséquence.

-- mrt__marketing__blended_roas.sql
WITH daily_spend AS (
    SELECT
        date_day,
        SUM(campaign__spend) AS total_spend
    FROM {{ ref('mrt__marketing__campaign_report') }}
    GROUP BY 1
),

daily_revenue AS (
    SELECT
        DATE(order__created_at) AS date_day,
        SUM(order__revenue) AS total_revenue
    FROM {{ ref('int__order__enriched') }}
    WHERE order__is_completed = TRUE
    GROUP BY 1
)

SELECT
    ds.date_day,
    ds.total_spend,
    COALESCE(dr.total_revenue, 0) AS total_revenue,
    SAFE_DIVIDE(dr.total_revenue, ds.total_spend) AS blended_roas,
    -- ROAS glissant sur 7 et 30 jours pour l'analyse de tendances
    SAFE_DIVIDE(
        SUM(dr.total_revenue) OVER (ORDER BY ds.date_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),
        SUM(ds.total_spend) OVER (ORDER BY ds.date_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
    ) AS blended_roas_7d,
    SAFE_DIVIDE(
        SUM(dr.total_revenue) OVER (ORDER BY ds.date_day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),
        SUM(ds.total_spend) OVER (ORDER BY ds.date_day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
    ) AS blended_roas_30d
FROM daily_spend ds
LEFT JOIN daily_revenue dr USING (date_day)
ORDER BY ds.date_day

Le ROAS glissant sur 7 et 30 jours lisse la volatilité quotidienne et donne à l’équipe marketing une tendance significative. Une mauvaise journée ne signale pas un problème. Une tendance baissière sur 30 jours, si.

Un principe important : les données d’attribution spécifiques à une plateforme ne doivent jamais être comparées directement. Chaque plateforme applique sa propre logique d’attribution et sur-estimera toujours ses contributions. Les métriques mixtes et l’attribution basée sur les UTM sont plus fiables que d’essayer de réconcilier ce que chaque plateforme dit avoir généré.

Suivi du rythme budgétaire

Le suivi du rythme budgétaire compare les dépenses depuis le début du mois par rapport aux budgets mensuels avec des projections de taux de consommation quotidien. Quand les dépenses sont en avance de 20 % sur le rythme prévu, l’équipe media peut ralentir les campagnes avant la fin du mois plutôt que de découvrir le dépassement dans la prochaine facture.

Construisez-le comme un modèle dbt reliant les dépenses unifiées à un fichier de budget seed :

platform,campaign_group,month,budget_amount
google_ads,brand,2026-03-01,25000
google_ads,performance,2026-03-01,50000
facebook_ads,prospecting,2026-03-01,30000
facebook_ads,retargeting,2026-03-01,15000
linkedin_ads,brand-awareness,2026-03-01,10000

-- mrt__marketing__budget_pacing.sql
WITH monthly_spend AS (
    SELECT
        DATE_TRUNC(date_day, MONTH) AS month_start,
        platform,
        SUM(campaign__spend) AS mtd_spend,
        COUNT(DISTINCT date_day) AS days_elapsed
    FROM {{ ref('mrt__marketing__campaign_report') }}
    WHERE DATE_TRUNC(date_day, MONTH) = DATE_TRUNC(CURRENT_DATE(), MONTH)
    GROUP BY 1, 2
),

budgets AS (
    SELECT
        CAST(month AS DATE) AS month_start,
        platform,
        SUM(budget_amount) AS monthly_budget
    FROM {{ ref('monthly_ad_budgets') }}
    GROUP BY 1, 2
),

days_in_month AS (
    SELECT DATE_DIFF(
        DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH),
        DATE_TRUNC(CURRENT_DATE(), MONTH),
        DAY
    ) AS total_days
)

SELECT
    ms.month_start,
    ms.platform,
    ms.mtd_spend,
    b.monthly_budget,
    ms.days_elapsed,
    dim.total_days,
    -- Métriques de rythme
    SAFE_DIVIDE(ms.mtd_spend, b.monthly_budget) AS budget_utilization_pct,
    SAFE_DIVIDE(ms.mtd_spend, ms.days_elapsed) AS daily_burn_rate,
    SAFE_DIVIDE(b.monthly_budget, dim.total_days) AS target_daily_rate,
    -- Dépenses projetées en fin de mois au taux actuel
    SAFE_DIVIDE(ms.mtd_spend, ms.days_elapsed) * dim.total_days AS projected_month_end_spend,
    -- Indicateur de sur-/sous-rythme
    CASE
        WHEN SAFE_DIVIDE(ms.mtd_spend, ms.days_elapsed) * dim.total_days > b.monthly_budget * 1.10
            THEN 'over_pacing'
        WHEN SAFE_DIVIDE(ms.mtd_spend, ms.days_elapsed) * dim.total_days < b.monthly_budget * 0.90
            THEN 'under_pacing'
        ELSE 'on_track'
    END AS pacing_status
FROM monthly_spend ms
LEFT JOIN budgets b
    ON ms.month_start = b.month_start
    AND ms.platform = b.platform
CROSS JOIN days_in_month dim

L’indicateur pacing_status est la sortie clé. Connectez-le à une alerte Slack ou à un filtre de tableau de bord pour que l’équipe d’achat media voit over_pacing immédiatement. Le seuil de 10 % pour le sur-/sous-rythme est un point de départ — réduisez-le pour les plateformes avec des budgets plus importants où les dépassements sont plus coûteux.

Le fichier de budget seed nécessite une maintenance manuelle. Certaines équipes automatisent cela en extrayant les données budgétaires depuis une feuille de calcul via un connecteur Google Sheets ou depuis l’API d’un outil de planification. L’important est que les données budgétaires vivent aux côtés des données de dépenses dans l’entrepôt, où la comparaison peut être automatisée.

Préparation des données pour la modélisation du mix media

La modélisation du mix media (MMM) est l’approche statistique pour mesurer l’efficacité marketing au niveau des canaux, en tenant compte des facteurs externes que les modèles d’attribution manquent. Des outils comme Robyn de Meta et PyMC-Marketing ont rendu la MMM accessible aux équipes plus petites, mais ils nécessitent des données quotidiennes dans une structure spécifique.

Votre modèle publicitaire unifié produit déjà la majorité de ce dont la MMM a besoin :

Champ requis	Source
Date	`date_day` du modèle unifié
Canal	`platform` du modèle unifié
Dépenses	`campaign__spend` agrégé par jour × canal
Revenu	Revenu quotidien total depuis le CRM/e-commerce
Facteurs externes	Saisonnalité, jours fériés, activité des concurrents

L’écart entre ce que le modèle unifié fournit et ce que les outils MMM requièrent est le revenu “non tracké” — la différence entre le revenu quotidien total et la somme du revenu tracké par canal. Cet écart est l’opportunité du modèle MMM : il représente l’effet de la notoriété de marque, de la recherche organique, du bouche-à-oreille, et d’autres canaux non mesurés.

-- mrt__marketing__mmm_input.sql
WITH daily_channel_spend AS (
    SELECT
        date_day,
        platform AS channel,
        SUM(campaign__spend) AS spend,
        SUM(campaign__impressions) AS impressions,
        SUM(campaign__clicks) AS clicks
    FROM {{ ref('mrt__marketing__campaign_report') }}
    GROUP BY 1, 2
),

daily_revenue AS (
    SELECT
        DATE(order__created_at) AS date_day,
        SUM(order__revenue) AS total_revenue
    FROM {{ ref('int__order__enriched') }}
    WHERE order__is_completed = TRUE
    GROUP BY 1
),

tracked_revenue AS (
    SELECT
        date_day,
        SUM(attributed_revenue) AS tracked_revenue
    FROM {{ ref('mrt__attribution__channel_daily') }}
    GROUP BY 1
)

SELECT
    dcs.date_day,
    dcs.channel,
    dcs.spend,
    dcs.impressions,
    dcs.clicks,
    dr.total_revenue,
    tr.tracked_revenue,
    dr.total_revenue - COALESCE(tr.tracked_revenue, 0) AS untracked_revenue
FROM daily_channel_spend dcs
LEFT JOIN daily_revenue dr ON dcs.date_day = dr.date_day
LEFT JOIN tracked_revenue tr ON dcs.date_day = tr.date_day

La colonne untracked_revenue devient une entrée critique pour la MMM. Si elle représente constamment 40 % du revenu total, cela vous indique que près de la moitié de votre revenu provient de sources que l’attribution par canal ne peut pas mesurer — équité de marque, croissance organique, ou canaux que vous ne suivez pas. La MMM tente de décomposer cet écart en ses composantes.

Relation avec le modèle unifié

Sans le modèle unifié, chacune de ces analyses nécessite une collecte manuelle de données à travers plusieurs tableaux de bord de plateformes. Avec lui, ce sont des modèles dbt qui s’exécutent automatiquement et restent à jour.

La fondation consiste en cinq métriques comparables, des noms de campagnes standardisés et des tests de réconciliation des dépenses.