Unified Ad Model Downstream Patterns

-- 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,
    -- Rolling 7-day and 30-day ROAS for trend analysis
    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

-- 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,
    -- Pacing metrics
    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,
    -- Projected month-end spend at current burn rate
    SAFE_DIVIDE(ms.mtd_spend, ms.days_elapsed) * dim.total_days AS projected_month_end_spend,
    -- Over/under pacing flag
    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

-- 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