ServicesAboutNotesContact Get in touch →
EN FR
Note

Advertising Data in the Warehouse

Hub note for the complete guide to centralizing advertising data — from the measurement problem through extraction, pipeline challenges, and dbt transformation patterns

Planted Last tended
bigquerydbtgoogle adsdata engineeringanalytics

This hub covers centralizing advertising data in a warehouse: from the measurement problem and API landscape through extraction, pipeline engineering, and dbt transformation patterns. Platforms apply different attribution windows and counting methods; the warehouse is the only environment where all channels can be compared on a consistent methodology.

Prerequisites

  • Familiarity with SQL and your warehouse (BigQuery, Snowflake, or Databricks)
  • Basic understanding of dbt’s three-layer architecture
  • At least one ad platform you want to centralize

Reading Order

  1. Ad Platform Attribution Bias — Why platforms overcount conversions, the walled-garden incentive structure, and what becomes possible when ad data is in the warehouse.

  2. Ad Platform API Landscape — Engineering characteristics of each major ad platform API: Google Ads, Meta, LinkedIn, Microsoft, TikTok, Pinterest, Twitter/X.

  3. Ad Data Extraction Tools — Managed ELT (Fivetran, Airbyte Cloud), open-source (dlt, Airbyte OSS, Meltano), and native integrations (BigQuery Data Transfer Service). Selection criteria: team size, budget, platform count.

  4. Ad Pipeline Engineering Challenges — API rate limits, breaking schema changes, attribution window normalization, currency and timezone handling, privacy compliance.

  5. dbt Ad Reporting Patterns — Transformation layer: dbt_ad_reporting package, custom cross-platform UNION models, platform-specific normalization, reconciliation testing, attribution model integration.

  6. Ad Platform Metric Divergence — Impression counting standards, click definitions, attribution window differences, timezone pre-aggregation, and hierarchy mapping across platforms.

  7. Cross-Platform Ad Metric Comparability — The five metrics that can be compared across platforms (clicks, impressions, spend, conversions, conversions_value), passthrough variables, and conversion configuration.

  8. Campaign Naming and UTM Standardization — Campaign naming conventions, regex parsing in dbt, seed file overrides, UTM hygiene rules.

  9. Cross-Platform Ad Testing Patterns — Source freshness, spend reconciliation, grain testing, statistical anomaly detection, manual reconciliation.

  10. Unified Ad Model Downstream Patterns — Blended ROAS, budget pacing, and Marketing Mix Modeling data preparation after unification.

  • SQL Attribution Patterns — Once ad data is in the warehouse, attribution models assign conversion credit across touchpoints
  • BigQuery Architecture for Analytics Engineers — Understanding your warehouse’s cost model helps optimize ad data pipelines that scan large volumes daily
  • Attribution Analysis Hub — The full attribution journey from SQL implementation through dashboard design and incrementality testing