Build a GA4 and BigQuery warehouse with an event contract, tested transformations, identity limits, cost controls and decision-ready marts for teams.

Short answer: answer: Build the warehouse in five layers: raw GA4 export, an event contract, tested canonical events, decision-specific marts and a reconciliation ledger. Do not let analysts query raw tables for recurring decisions until event meaning, currency, identity and late-arrival rules are documented. A useful first release answers one named budget or content decision and passes six QA tests; it is not a collection of every possible metric.

The obvious answer—link GA4 to BigQuery and start writing SQL—creates storage, not measurement. Google's export gives event-level rows and nested fields, but it does not decide whether a purchase is valid, which identity is acceptable, or how a marketer should act.

Teams also expect the GA4 interface and BigQuery to match exactly. They may not: reporting identity, consent modeling, thresholds, attribution, processing time and query logic can differ. A warehouse should explain those differences rather than quietly force one number to resemble another.

The Event-to-Decision Pipeline

CDM's Event-to-Decision Pipeline is a five-gate design. Data may advance only when the definition and checks for its current gate are explicit. Our position is deliberately strict: a warehouse is useful only when event definitions, identity limits and decision outputs are agreed before queries proliferate. More SQL written against ambiguous events increases confidence faster than it increases truth.

Gate 1: preserve the raw export

Enable the GA4 BigQuery link and keep the export tables unmodified. Google documents daily events_YYYYMMDD tables and, when streaming is enabled, intraday tables that are removed after the daily table completes. Daily tables can be updated for up to three days as late events arrive. Therefore, schedule a rolling rebuild of at least the previous three event dates rather than treating yesterday as immutable.

Store operational metadata separately: property ID, source table suffix, load timestamp, job ID and row count. Restrict access to identifiers and sensitive parameters. Partition downstream tables by event date and cluster only on fields used repeatedly, such as event name or pseudonymous user ID. Preview bytes before large queries and set project budgets or alerts; BigQuery bills storage and, under on-demand analysis, bytes processed.

Gate 2: write the event contract

The contract states what a row means before transformation. For every decision event, record:

  • event name, business definition and exact firing condition;
  • required parameters, types, allowed values and currency convention;
  • client or server origin and deduplication key;
  • consent behavior, known exclusions and responsible owner;
  • effective date, version and retirement rule;
  • destination decision and acceptable freshness.

For purchase, decide whether it fires after payment authorization, capture or order creation. Record how retries, test orders, refunds, tax and shipping behave. GA4's flexible event model cannot resolve those business choices for you.

Run a contract query daily. A required transaction_id should have a near-zero missing rate; currencies should come from an approved set; a transaction should not appear as two purchase events unless the contract deliberately permits revisions. Set thresholds from observed clean periods, not universal folklore.

Gate 3: create canonical event tables

Use a small model rather than one flattened mega-table:

  • fact_event: event date and timestamp, event name, stream, platform, user_pseudo_id, consent flags and source row key;
  • fact_purchase: transaction ID, event timestamp, shop currency, gross value, tax, shipping and validation status;
  • dim_session_source: the chosen session key and normalized source, medium and campaign fields;
  • dim_content: canonical URL, content ID, author, category and publish version;
  • identity_bridge: permitted user ID to pseudonymous ID links with first seen, last seen and provenance;
  • qa_run: test, run time, observed value, threshold, result and affected partition.

Keep unknown values unknown. user_pseudo_id represents an app-instance or browser identifier, not a durable person. user_id exists only when the implementation supplies it. Never invent a cross-device join, and never expose person-level data merely because BigQuery makes the join technically possible.

This illustrative query produces transaction-grain purchase rows. It is a template, not a claim that it has run in your project:

``sql SELECT PARSE_DATE('%Y%m%d', event_date) AS event_date, event_timestamp, user_pseudo_id, ecommerce.transaction_id, ecommerce.purchase_revenue AS gross_revenue, ecommerce.tax_value AS tax, ecommerce.shipping_value AS shipping FROM project.analytics_123456.events_* WHERE event_name = 'purchase' AND _TABLE_SUFFIX BETWEEN '20260901' AND '20260930' QUALIFY ROW_NUMBER() OVER ( PARTITION BY ecommerce.transaction_id ORDER BY event_timestamp ) = 1; ``

The deduplication rule is a CDM recommendation, not a GA4 guarantee. If legitimate transactions can share an ID, redesign the key; if later events amend value, “first row wins” is wrong.

Gate 4: publish one decision mart

A mart fixes grain, denominator and latency for one recurring decision. A content investment mart might hold one row per landing page and week with sessions, engaged sessions, qualified actions, validated transactions and net revenue from the commerce system. A creator mart might hold one row per creator, campaign and cohort month.

Definitions belong beside columns. For example:

qualified_session_rate = qualified_sessions / eligible_sessions

Define an eligible session as one with the required consent and a recognized landing page; define a qualified session as one containing an approved intent event within 30 minutes. Use SAFE_DIVIDE and return null when the denominator is zero. Null means “not estimable,” not zero performance.

The mart must include data_complete_through, contract version and reconciliation status. The dashboard can then say whether a weekly comparison is mature enough to act on.

Gate 5: prove and use the output

Run six QA tests before release:

  1. Completeness: expected dates and streams are present.
  2. Contract: required parameters meet their thresholds.
  3. Uniqueness: transaction and source-row keys obey their grain.
  4. Referential integrity: content and campaign keys resolve or enter an explicit unknown bucket.
  5. Freshness: daily and rolling three-day rebuilds completed.
  6. Reconciliation: purchase counts and values tie to the commerce ledger within a documented scope and tolerance.

Reconciliation is not forced equality. GA4 can miss unconsented or blocked events; the commerce ledger can include phone orders, refunds or timing the analytics event does not represent. Compare a closed cohort using the same timezone, currency, order status, tax and refund rules. Report the residual as value and percentage, then classify it: scope, timing, identity, implementation or unexplained.

Consider a decision example. The paid-social team appears to have a 20% higher purchase rate than creator referrals in the last seven days. The mart shows the social cohort is complete through yesterday, but creator purchases have a 9% missing transaction-ID rate and two late partitions still rebuilding.

The correct decision is not to move budget. The owner fixes the creator event and waits for the closed cohort; only then does the channel comparison qualify for action.

The reader asset: a one-page warehouse release record

Before a mart becomes production, complete this record:

  • decision and accountable decision-maker;
  • fact grain, eligibility rules and formulas;
  • source tables, contract versions and identity policy;
  • refresh schedule and complete-through rule;
  • six QA results with links to queries or job logs;
  • finance or operational reconciliation scope and residual;
  • known exclusions, privacy controls and rollback owner;
  • decision threshold, review date and change log.

The record turns a query into an auditable product. CDM recommends rejecting a release when a required field is blank, even if its dashboard looks persuasive.

Related guides

Frequently asked questions

Is the GA4 BigQuery export the same as the GA4 interface?

No. Treat the export as raw event data and the interface as a processed reporting surface with its own identity, attribution, modeling and threshold behavior. Google documents expected differences among reports, explorations, APIs and BigQuery. Match event scope, timezone, filters and date maturity before investigating a gap. Even then, consent modeling or reporting identity may prevent equality.

The caveat is that large unexplained differences still deserve investigation; “the systems differ” is not permission to ignore duplicate tags, missing parameters or broken ecommerce events.

Should every analyst query the raw GA4 export directly?

No. Analysts may explore raw data, but repeated business reporting should use governed canonical tables and decision marts. Nested parameters, late events and inconsistent definitions make independently written queries drift. A shared model also centralizes privacy controls and cost management.

The exception is investigation: an analyst may need raw rows to diagnose an anomaly or design a new metric, provided the result is labeled exploratory and does not silently replace a certified measure. Certified shared models also make review and onboarding materially faster.

How much does a GA4 BigQuery warehouse cost?

It depends on event volume, retention, transformations, query frequency, region and pricing model. Estimate monthly raw storage, transformed storage and bytes scanned by scheduled and interactive queries, then verify current Google Cloud prices for the chosen location. Partition pruning and narrow marts often matter more than clever SQL.

The caveat is that list prices and free allowances can change, and streaming or other services may add costs, so a published dollar estimate without your workload and billing configuration is not reliable.

How should we identify users across devices?

Use only identifiers you legitimately collect and are permitted to process, and preserve their provenance. user_pseudo_id is not a person; user_id is available only where your implementation sets it. Keep anonymous and known-user metrics separately until a documented identity rule joins them.

The caveat is that even a deterministic login ID does not make pre-login activity fully observable. Consent choices, deleted data, shared devices and blocked collection create gaps that should remain visible in the metric definition.

When is a GA4 day complete enough for reporting?

Define completeness operationally rather than assuming midnight closes the day. Google's daily export can receive late events for up to three days, so rebuild a rolling three-day window and expose a data_complete_through date. For urgent monitoring, use provisional data with a visible label.

The exception is a decision that tolerates revision, such as detecting a severe outage; it can use intraday data, but it should not be compared as though it were a settled finance cohort. Record the threshold in the mart, not in analyst memory.

What should reconcile to finance?

Reconcile transaction-level identifiers and scoped monetary components, not a vague dashboard total. Agree order state, event date versus settlement date, shop currency, discounts, tax, shipping, refunds, chargebacks and test orders. Then compare counts and amounts and classify every material residual. GA4 should not become the accounting ledger.

The caveat is that a marketing mart may intentionally use gross demand while finance uses recognized or settled revenue; both can be valid if their names, formulas and decision uses remain distinct. Keep both bases visible so stakeholders do not confuse them.

Next decision: How Do You Combine GA4 and Search Console Into a Content Decision Dashboard?

Related reading: How Do You Design a Looker Studio Dashboard That Changes a Decision? · How Do You Connect Marketing Decisions to Revenue in Salesforce? · How Do You Build a Customer-Question Event Taxonomy in Segment?

Sources and research notes

Limitations: CDM did not access a reader's GA4 property, BigQuery project, consent configuration or finance ledger and did not execute the illustrative SQL. Thresholds, schemas, access controls and reconciliation tolerances must be validated in that environment.

CDM Editorial

This article is editorial guidance. Apply the principles in proportion to your market, evidence, and responsibilities.