Scattered sources
Different structures, histories, and metric definitions.
MARKETING DATA ENGINEERING
Marketing data in one model.Ready for reporting.
I build BigQuery warehouses that bring marketing sources into documented models, with scheduled loads and validated tables for dashboards and analysis. It is the least visible part of reporting, and the one that holds it up.
Typical sources: paid media, web analytics, and CRM.
THE REPORTING FOUNDATION
SOURCES
01Google Ads
02Meta Ads
03GA4 / CRM
BigQueryClean · Model · Validate
Documented model
REPORTING TABLES
Campaigns
Performance
01 / THE PROBLEM
Too much time goes into preparing the data.
When campaign, CRM, and analytics data live in different platforms and files, every report starts with cleaning, joining, and checking the same information again.
Manual preparation
Files and transformations have to be rebuilt or adjusted every reporting cycle.
Hard-to-explain differences
Numbers are difficult to trust when the source and calculation logic are unclear.
02 / WHAT I BUILD
A foundation that can be queried, reloaded, and explained.
Every table has a reason to exist: it feeds a report or an analysis with defined sources, data rules, and level of detail.
Validated with data, not just a working connection.
Before I consider a table done, I check coverage, quality rules, and differences from the source.
01
Source integrations & loads
Each source connected, with its historical coverage and refresh frequency documented.
02
Warehouse & data model
Tables, identifiers, and relationships organized so the data can be queried at a consistent level of detail.
03
Analysis & reporting tables
Tables or views with documented dimensions, metrics, and calculation rules for each use case.
04
Quality controls & operations
Validation checks, issue logs, and documentation for reviewing updates and handling exceptions.
The focus here is the data foundation. How I turn those tables into automated reports is covered in Reporting Automation.
03 / INTERACTIVE DEMO
A reliable report starts here.
A file can arrive twice, contain a correction or include incomplete data. See what the workflow does before updating the report.
Every test starts with the same report: 4 campaigns · USD 3,00001 / Choose what arrives
A few seconds · skip to the result anytime
- ReceiveKeep the source
- CheckStandardize and verify
- UpdateNo duplicate campaigns
USD 5,200File total, before validation
- Google Ads
- Meta Ads
- LinkedIn Ads
This sum includes repeated rows; it is not the actual spend.
USD 3,000Recorded spend before processing
- Campaigns
- 4
- Clicks
- 3,800
6Rows received
—Valid unique rows
—Repeated rows ignored
—Held for review
View the 4 campaigns and the rules
One row per platform, account, campaign and day. Google Ads amounts arrive in micros: they are standardized to USD, without currency conversion.
| Source / campaign | Spend | Clicks | What changed |
|---|---|---|---|
| Google AdsG-01 · Brand search | USD 1,200 | 1,200 | Current record |
| Meta AdsM-01 · Prospecting | USD 1,000 | 2,000 | Current record |
| Google AdsG-01 · Generic search | USD 500 | 450 | Current record |
| LinkedIn AdsL-01 · Fund information | USD 300 | 150 | Current record |
| Report total | USD 3,000 | 3,800 | 4 campaigns |
Copies of the same key are ignored. A correction replaces the previous version; it is never added as another campaign.
04 / HOW I WORK
A clear process. Numbers that reconcile or are explained.
Three steps. The data model, the controls, and how the workflow runs are all documented.
- 01
Define and connect
I review sources, accounts, historical coverage, and update frequency. I configure extraction and keep every record traceable to its source.
- 02
Build and validate
I organize the tables, document metrics and check results against each source.
- 03
Document and run
The result: reporting-ready tables, automated checks and a guide for running the workflow and reviewing exceptions.
05 / CORE TOOLS
The tools I work with.
BigQuery and SQL for the model, Python for loads and checks, and Data Studio to review the output.
- Google Ads
- Meta
- GA4
- BigQuery
- SQL
- Python
- Data Studio
- dbt / Dataform
06 / TECHNICAL QUESTIONS
Questions about the method.
Before building, I write down the sources, available history, level of detail, refresh frequency, and validation criteria.
01How do you avoid duplicates when a source resends data?
Each table has a key defined by its grain: platform, account, campaign, and day. Loads merge on that key, so a reload creates no new rows and a correction updates the existing record. That is what the demo shows.
02What happens to a record that fails the rules?
It stays out of the reporting table and is logged as an exception, with its reason. It is not turned into zero or silently dropped: it is reviewed, fixed at the source, and picked up by the next load.
03How do you check that the numbers match the platforms?
I compare totals by period, account, and metric against the source, using the same time zone and currency. Expected differences, such as attribution windows or late-arriving data, are documented rather than forced to match.
04Why not connect the dashboard directly to each platform?
It works for a quick view, but every report ends up repeating the same logic and definitions drift apart. With an intermediate model in BigQuery, metrics are calculated once, history is kept, and every dashboard or analysis starts from the same tables.
CONTACT
Interested in the topic?
Data modeling, loads, and data quality.
If you work with marketing data and want to compare approaches, message me.
Message me on LinkedIn