Marketing Data Platforms
Dataform + BigQuery Marketing Analytics Warehouse
GA4, Google Ads, and CRM inputs organized into BigQuery reporting models with Dataform transformations and reconciliation checks.
Marketing Analytics Team · Solo · Selected work
My role & contribution
Built warehouse models and reporting transformations for this engagement. This is separate from the newer Meta-focused product architecture.
Selected warehouse implementation
The problem
Built marketing analytics warehouse patterns around Dataform and BigQuery. The work organized GA4, Google Ads, Salesforce CRM, and marketing inputs into modeled reporting layers with source declarations, SQLX transformations, reusable JavaScript helpers, Looker Studio/Data Studio-ready outputs, assertions, and reconciliation checks.
The existing workflow
Marketing, analytics, and CRM inputs needed a shared reporting structure. Joining them directly into dashboards made model grain and reconciliation harder to control.
System flow
01
Source declarations
GA4, Google Ads, Salesforce CRM, and marketing inputs are declared as controlled BigQuery sources.
02
Modeled layers
Dataform SQLX transforms raw inputs through bronze, silver, gold, and BI-ready layers.
03
Dashboard outputs
Looker Studio/Data Studio-facing tables expose clean campaign, pipeline, revenue, and reconciliation views.
04
Assertions
Compile checks, source counts, and reconciliation assertions catch broken joins before reporting.
Key engineering decisions
Make grain explicit
Campaign performance, CRM pipeline, and revenue inputs describe different entities. Reporting outputs need defined aggregation and joins rather than a single join of every raw source.
Keep BI outputs downstream
Cleaned entities and reporting marts provide the layer consumed by Looker Studio. Dashboard formatting does not determine the warehouse model.
Difficult problems
- Marketing, analytics, and Salesforce CRM sources needed one controlled reporting model
- Looker Studio/Data Studio dashboard tables needed controlled grain so spend, conversions, pipeline, and revenue did not duplicate during joins
- Warehouse layers needed assertions and reconciliation checks before numbers reached BI outputs
- The project needed clear documentation so the warehouse layers could be understood and maintained
Work contributed
- Organized source declarations and modeled layers in Dataform/BigQuery.
- Built reporting-ready transformations around marketing and CRM entities.
- Added assertion/reconciliation patterns and documented model structure.
Validation
- Checked source declarations and transformation compilation.
- Used assertions and reconciliation around reporting models.
- Reviewed reporting grain and source/output comparisons.
Supported outcome
- Modeled source declarations, staging layers, and BI-ready Dataform outputs in BigQuery
- Added assertion and reconciliation patterns around marketing reporting tables
- Separated raw inputs, cleaned entities, gold-layer marts, and Looker Studio/Data Studio-facing outputs
- Mapped the model structure for GA4, Google Ads, Salesforce, and order-style reporting
Technologies in context
BigQuery · Dataform · SQLX · JavaScript · GA4 · Google Ads · Salesforce · Looker Studio · Data Studio · Assertions
Related services & work
Working through a similar problem?
Share the workflow and the output your team needs. We can identify a focused starting point.
Discuss your workflow