dbt Ad Reporting Patterns

dbt is the standard transformation layer for advertising data. Both Fivetran’s pre-built package and custom implementations follow well-established modeling patterns.

The dbt_ad_reporting Package

Fivetran’s dbt_ad_reporting package (v2.4.0, January 2026) covers 11 ad platforms and produces unified models at account, campaign, ad group, ad, keyword, search query, and URL levels. It normalizes five core metrics across all platforms: clicks, impressions, spend, conversions, and conversions_value. A platform column identifies each row’s source.

The package follows the three-layer architecture applied specifically to advertising data:

1. Source packages handle type casting and aliasing — making raw Fivetran tables queryable with consistent column names
2. Platform packages join dimensions with metrics and handle platform-specific conversion logic (flattening Meta’s action arrays, converting Google’s micros, remapping LinkedIn’s inverted hierarchy)
3. The roll-up layer UNIONs everything into unified models where every platform speaks the same language

This layering means you can use individual platform packages independently if you only need Google Ads data, or use the full roll-up package for cross-platform reporting.

Building Cross-Platform Models Without Fivetran

If you’re not using Fivetran as your extraction tool, you can replicate the same pattern with custom models. The core idea: normalize each platform independently, then UNION them into a single unified model.

-- 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') }}

The critical work happens in the intermediate layer, where each platform’s quirks get normalized before the UNION.

Platform-Specific Normalization

Each platform needs its own intermediate model that handles the data engineering pain specific to that platform’s API output.

Google Ads normalization:

• Divide cost by 1,000,000 (micros to currency units)
• Handle Performance Max campaigns that only report at campaign level
• Map Google’s campaign types to a standardized enum

Meta Ads normalization:

• Flatten the nested actions JSON array into individual columns
• Extract specific action types (purchases, leads, link clicks) from the array
• Handle the June 2025 attribution split between on-Meta and off-Meta events

LinkedIn Ads normalization:

• Remap LinkedIn’s inverted hierarchy names (“Campaign” → “Ad Group”, “Campaign Group” → “Campaign”)
• Handle the different conversion tracking methodology

The goal of the intermediate layer: by the time data reaches the UNION in the mart, every platform looks identical. Same column names, same data types, same units, same grain.

Handling Platform-Specific Metrics

Not every metric exists on every platform. LinkedIn has social actions (likes, shares, follows). Meta has ThruPlays and video completion rates. Google has Quality Score.

The right pattern: keep platform-specific metrics in platform-level models rather than polluting the unified view with NULLs. Your mrt__marketing__ad_performance_daily UNION model contains only the five universal metrics. If someone needs LinkedIn social engagement data, they query mrt__marketing__linkedin__campaign_performance directly.

This keeps the unified model clean and queryable for the 90% of questions that are cross-platform (“how much did we spend?”), while preserving platform-specific depth for the 10% that need it.

Reconciliation Testing

One testing pattern that saves real pain: reconciliation tests that verify your unified spend matches the sum of individual platform spends. This is a singular test that catches transformation bugs, missing data, and pipeline drift.

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

Allow 1-3% variance for timezone-related discrepancies, but anything larger signals a pipeline bug. Run this test in CI and on every production dbt run.

A second reconciliation pattern compares your warehouse totals against platform UI totals for a recent period. This is a manual check, not an automated test, but it builds confidence that your pipeline is capturing the full picture. Document the expected variance range and investigate anything outside it.

The Mart Model Structure

A mature advertising data mart typically includes these models:

The cross-platform models use the UNION pattern. The platform-specific models reference the intermediate layer directly and include the full set of platform-specific metrics.

Integration with Attribution

The unified ad performance model provides the spend side of the equation. The revenue side comes from your CRM, ecommerce platform, or attribution models. Joining these two gives you true ROAS — not the self-reported ROAS from each platform, but your own calculation using consistent revenue data across all channels.

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

Centralizing ad data in the warehouse enables true cross-channel ROAS from a single query.