Marketing data

Cross-platform advertising data turned into a daily automated pipeline

Google Ads, Meta Ads, creative, and lead data now enter one BigQuery architecture, so analysis no longer starts with manual exports.

Sector: Marketing data
Period: In production
Our role: Data architecture, API integration, and cloud orchestration

The problem

Advertising performance was split across platforms with different accounts, fields, refresh patterns, and attribution definitions. Manual exports could produce a temporary report, but they were difficult to rerun reliably. A missing day could come from an API, permission, schedule, or schema issue with no clear signal as to which one failed.

Constraints

Google Ads and Meta Ads APIs use different authentication models, quotas, data structures, and update timing.
Tokens expire or change, while platform fields can evolve outside a shared release cycle.
The public case cannot disclose real spend, client names, campaigns, or creative assets.

What we did

Preserve the source before shaping analysis

Platform data first lands in traceable source structures in BigQuery, then a transformation layer aligns shared dimensions and metrics.

Why not the other path We did not compress everything into one generic report during extraction because it would erase platform-specific meaning and the basis for later recalculation.

Treat orchestration as an observable system

Cloud Workflows coordinates daily runs and separates platform extraction, creative processing, and lead retrieval into identifiable steps.

Why not the other path We did not put every task into one long-running process because a single failure would be harder to locate and more expensive to rerun.

Design writes to be repeatable

Updates are bounded by source and date ranges so rerunning the same batch produces a consistent result.

Why not the other path We did not assume success and append forever, because recovery after a temporary API failure would create duplicates or gaps.

Separate access failures from data failures

The workflow preserves distinct signals for authentication, quota, and schema problems, reducing the time needed to identify the source.

Why not the other path We did not handle every API error as the same retryable event because an expired permission will not repair itself with time.

Outcomes

Google Ads and Meta Ads data enter BigQuery on a daily schedule.
Cross-platform analysis starts from one query layer instead of a collection of manual exports.
Creative processing and lead retrieval run inside a traceable cloud workflow.

Technology

Google Ads API
Meta Marketing API
BigQuery
Cloud Workflows

Looking back

If we started again, we would define data-freshness expectations and schema contracts earlier. Data arriving is not the same as data being usable; delays, backfills, and platform field changes should be visible before they reach a report.