Marketing data platform

Problem

Several advertising and web sources had to land in one place a client team could actually use: Google Ads, Meta Ads, GA4, custom APIs, and a connector layer (Windsor). The question was never “can we ingest the API.” It was whether spend, clicks, sessions, and operational events could be modeled at a grain someone could defend in a weekly review.

Constraints

• Clients cannot be named. This write-up is the architecture, not a case-study trophy with logos.
• Several source contracts, each with its own freshness, identity, and breakage pattern.
• Reporting has a calendar. A model that is “eventually right” after three restatements is not useful on Monday morning.
• Cost is part of the design. Full refreshes of ad history look simple and become the most expensive object in the project.

Architecture

Ingestion lands raw payloads. Bronze keeps them recoverable. Silver normalizes entities and dates. Gold is what analytics is allowed to query. Serving is the reporting layer on top of Gold — not a second copy of the source system.

Decisions and trade-offs

• Medallion over a single wide table. A “campaigns plus sessions plus everything” grain looks convenient and silently fans out. Grain first; then incrementalize.
• Dataform in BigQuery as the transformation contract: incremental models, assertions, and historical backfills as first-class work, not a weekend script.
• Connectors are not the model. Windsor / native connectors get data in. They do not decide what a row means.
• Assertions over dashboard folklore. If spend in Gold cannot reconcile to the ad UI for a day, I do not add another metric. I fix the claim.

Implementation

Work I owned or co-owned on this surface: ingestion from the sources above, Bronze → Silver → Gold modeling, enrichment, incremental builds, and keeping analytics serving aligned with those models. Exact client topology stays unpublished.

Result

A warehouse path that a reporting layer can trust without joining raw API extracts in the BI tool. I am not publishing lift percentages or spend under management. Those numbers belong to the client.

What I learned

Paid media and GA4 punish fuzzy grain faster than an internal ops table does. Restatements, late conversions, and account-level vs campaign-level identities are the job. Cadence follows the decision (weekly planning vs in-day ops), not a default every 15 minutes.