Unified Marketing Intelligence on Google Cloud

Problem

An e-commerce retailer on Google Cloud has five data sources that do not talk to each other. They are a GA4 stream, daily competitor files, social and ad APIs, search data and the enterprise warehouse. Marketing reconciles CSV exports by hand, and a question such as “did our price or the competitor’s cause yesterday’s conversion drop?” takes two days. The company wants one platform for four consumers. These are Power BI for about 400 users, an LLM assistant with agents, machine-learning models and audience exports to ad platforms.

Approach

The binding goal is a single source of truth. Power BI, the LLM and the agents must give the same number for the same question. Cadence differs: GA4 should be queryable within minutes, competitor files arrive daily, and social APIs revise up to seven days of history. Cost is the third concern, because four consumer groups can drive the BigQuery bill. The design layers the data, publishes through a gate, and binds every consumer to one metric contract.

Architecture

• Ingestion: a server-side collector and Pub/Sub for GA4, Cloud Composer for batch files, and a Cloud Run connector for APIs. The warehouse is read through authorised views, not copied.
• Layers: raw, staging, conformed keys, then certified marts, built with Dataform SQL and tests.
• Publish gate: marts are built as candidates, assertions run, and a view swap makes them live. A late batch never shows half-loaded data.
• Serving: metrics are defined once in Git. A metric API runs them, and Power BI models are generated from the same definition.
• LLM and agents: the model translates a question into metric, filter and date range. The number comes from the API. Agents propose actions into an approval queue.

Decisions and trade-offs

• No raw SQL for consumers. Text-to-SQL is flexible but inconsistent: two queries for one question give two numbers. Once an answer disagrees with Power BI, trust is gone. A question without a certified metric is refused and goes to the metric backlog. The same-number guarantee comes from architecture, not prompting. The cost is a metric contract that needs owners; ownership is the biggest risk.
• View swap instead of a second engine. In BigQuery a view replacement is atomic and needs no copy. If a batch is late, yesterday’s numbers stay with yesterday’s stamp, so nobody sees half a day. GA4 gets a provisional streaming view with a freshness flag. The cost is that certified marts run one day behind.
• Agents propose, they do not apply. The approval service calls the Ads API, not the agent; the agent’s service account lacks that right. Each run has a spend change limit, and every action keeps a rollback record. The cost: every new kind of action needs its own approval flow.
• Google Cloud lock-in, on purpose. The warehouse is already in BigQuery and GA4 lands there natively. Moving it would add cost and delay with no business value. I spend the portability budget where value builds up: key mappings, the metric contract and agent tool definitions live in Git. If the warehouse moves, the metric API points to another engine and consumers see the same interface.
• Read the warehouse, do not copy it. Authorised views read the same rows and add no load to the warehouse. The data team keeps ownership. The cost is schema change risk, so the service level with that team is the most fragile part of the contract.
• Cost follows data volume, not users. Power BI runs in import mode, so 400 users’ clicks never reach BigQuery. The metric API caches by metric, filter and publish ID, and the result cannot change until the next publish. Each consumer group has its own project, reservation and quota, with a budget alert at 80%.

What I would revisit

• Server-side collector. Without one, GA4 falls back to the hourly intraday export and the two-minute target fails.
• Competitor SKU coverage. I assumed 90%. Categories under 95% answer with a coverage warning. If coverage is low, mapping quality comes first, not architecture.
• Primary BI tool. Power BI and Looker Studio can both be supported, but certification work and cost double.
• Warehouse team service level. How much notice comes before an authorised view’s schema changes?

Outcome notes (design assumptions, not measurements)

This is a design exercise, not a delivered platform. The output is a four-page diagram set, a one-page whiteboard version and a 16-page Turkish PDF. The metric list and its owners (50 assumed) are still open.

Sources

• Architecture PDF (Turkish, 16 pages).
• draw.io diagrams (English labels): four-page set and a one-page 30-minute version.