GA4, BigQuery and CRM: A Practical Marketing Architecture

GA4, BigQuery, and a CRM solve different parts of marketing measurement. GA4 records event activity, the CRM records lifecycle and commercial outcomes, and BigQuery gives a team a place to join and test those datasets.

The architecture works when each system keeps a clear responsibility. It fails when a dashboard, a CRM field, or an ad platform silently becomes the source of truth for facts it does not own.

This guide shows how to connect the three systems around a controlled click-to-revenue contract. It includes a synthetic data model, a reconciliation query template, and a discrepancy runbook. The SQL is an example for adaptation, not a FunnelSheet production query.

Key Takeaways

• GA4 is useful for event activity and journey context; it is not automatically the authority for closed revenue.
• The CRM should own lifecycle stages, deal state, and the commercial outcome fields it controls.
• BigQuery is useful for joining late-arriving events, campaign context, CRM records, and revenue.
• A join is not proof of attribution. Define identifiers, time windows, consent rules, and reconciliation tolerances first.

Table of Contents

• What does each system own?
• What identifiers connect GA4, BigQuery, and the CRM?
• What should the data flow look like?
• How should GA4 data enter BigQuery?
• How do you model a lead-to-revenue join?
• What does a reconciliation query test?
• How should you investigate discrepancies?
• When is this architecture worth the effort?
• Frequently asked questions

What does each system own?

Start with ownership before writing an integration. A source system can provide useful evidence without owning every interpretation made from it.

The exact boundary depends on the business and its policies, but it should be documented. For example, GA4 may show that a generate_lead event fired. The CRM may show that a lead later became qualified. BigQuery can show whether both records can be connected and where the timing differs.

The Marketing Measurement and Data Engineering service explains the wider pipeline. This article narrows the design to the three-system join that teams commonly need first.

What identifiers connect GA4, BigQuery, and the CRM?

Attribution is a data-linking problem before it is a model-selection problem. Define which identifiers are created, where they are stored, and when they may be absent.

Do not use a field merely because it exists in every system. Test whether its value is stable, unique enough for its job, available at the required boundary, and permitted under the site’s data policy.

Minimum join contract

For a lead-generation funnel, record at least:

• event_id for the event that represents the submitted lead.
• lead_id for the CRM lead or contact.
• deal_id when the lead becomes an opportunity or deal.
• Approved campaign context such as source, medium, and campaign.
• Click identifier when available and allowed.
• Event, lead, stage, and outcome timestamps with timezone rules.
• Revenue amount and currency from the system that owns the commercial outcome.
• Consent or data-use state needed by each downstream destination.

An email address may help with a controlled match, but it should not be the only attribution key. It can be missing, changed, duplicated, or subject to different handling rules.

What should the data flow look like?

Use a one-way flow for each responsibility and make the joins visible:

tagged campaign URL
  -> website and approved data layer
  -> GA4 event plus lead submission
  -> CRM lead_id and lifecycle stages
  -> deal_id, outcome, amount, currency
  -> BigQuery raw sources
  -> normalized models and reconciliation tests
  -> approved reporting and ad-platform feedback

The flow has two different paths after the lead:

1. Analytics path: event activity is available for journey and interaction analysis.
2. Revenue path: CRM stages and commercial outcomes arrive as operational records, often later.

Do not force the analytics path to pretend that a form event is a closed deal. Instead, join the paths at an explicitly defined key and report the outcome stage that the business decision requires.

Raw, normalized, and decision layers

Keep three logical layers even when they share one warehouse:

If a final dashboard is wrong, the raw and normalized layers should make it possible to find whether the problem began at collection, ingestion, transformation, or interpretation.

How should GA4 data enter BigQuery?

Google documents an export from Google Analytics to BigQuery that makes raw event data available for analysis and combination with external datasets. The GA4 BigQuery export documentation distinguishes daily export tables from streaming behavior and notes that the exported data is not a verbatim copy of every report in the GA4 interface.

That distinction matters when reconciling numbers. A GA4 interface report may apply reporting identity, attribution logic, modeled data, filters, or processing rules that do not match a raw event query. The BigQuery export overview and Google’s comparison guidance should be treated as source documentation when defining the expected relationship.

Practical ingestion rules

• Store raw exports without silently overwriting source records.
• Record ingestion time separately from event time.
• Use a stable event key where the source provides one.
• Handle late-arriving events and CRM updates explicitly.
• Normalize timestamps to a declared reporting timezone while retaining the original value when useful.
• Keep source and destination names in the model so a discrepancy can be traced.
• Mark missing or consent-limited fields as missing, not as zero.
• Document whether a table is daily, intraday, incremental, or backfilled.

The warehouse is not a permission bypass. A field that should not be collected or retained cannot be made acceptable by moving it to BigQuery.

How do you model a lead-to-revenue join?

Begin with one row per business object instead of one enormous flattened table. This makes duplicate and late-update behavior easier to inspect.

campaign_context
  campaign_source
  campaign_medium
  campaign_name
  click_id
  captured_at

lead_events
  event_id
  lead_id
  event_name
  event_at
  session_id

crm_leads
  lead_id
  created_at
  lifecycle_stage
  source_fields

crm_deals
  deal_id
  lead_id
  qualified_at
  closed_at
  outcome
  amount
  currency

The model should define whether a lead can have several deals, whether campaign context is first touch, last non-direct touch, or another approved view, and how a change in CRM ownership affects attribution. Do not leave these rules to whichever SQL query was written last. When a verified outcome must return to an ad platform, connect this model to the offline conversion tracking service only after eligibility, consent, and deduplication rules are tested.

Late-arriving outcomes

A lead may be created today, qualified next week, and closed next month. A daily report that only joins same-day records will undercount the eventual outcome. Use a declared attribution and reporting window, then backfill or restate the relevant model when the CRM outcome arrives.

Keep both timestamps:

• lead_created_at answers when demand entered the CRM.
• qualified_at or closed_at answers when the business outcome occurred.

Choosing one timestamp for every report hides the difference between acquisition volume and revenue timing.

What does a reconciliation query test?

The query below is a template showing the shape of a test. Replace dataset names, fields, stage definitions, timezone logic, and attribution rules before running it. It is not a FunnelSheet production query.

-- TEMPLATE ONLY: adapt field names, stages, time window, and privacy rules.
WITH submitted_leads AS (
  SELECT
    lead_id,
    MIN(event_at) AS first_submit_at,
    COUNT(DISTINCT event_id) AS submit_event_count
  FROM `project.analytics.lead_events`
  WHERE event_name = 'lead_submitted'
  GROUP BY lead_id
),
crm_outcomes AS (
  SELECT
    lead_id,
    COUNT(DISTINCT deal_id) AS deal_count,
    MAX(IF(lifecycle_stage = 'qualified', 1, 0)) AS has_qualified_stage,
    MAX(IF(outcome = 'closed_won', 1, 0)) AS has_closed_won
  FROM `project.crm.deals`
  GROUP BY lead_id
)
SELECT
  COUNT(*) AS submitted_leads,
  COUNTIF(submit_event_count = 1) AS leads_with_one_submit,
  COUNTIF(submit_event_count > 1) AS leads_with_duplicate_submits,
  COUNTIF(COALESCE(deal_count, 0) > 0) AS leads_with_deal,
  COUNTIF(has_qualified_stage = 1) AS qualified_leads,
  COUNTIF(has_closed_won = 1) AS closed_won_leads
FROM submitted_leads
LEFT JOIN crm_outcomes USING (lead_id);

The output is a diagnostic, not an attribution model. It can reveal that one lead has several submission events, that CRM outcomes are not linked, or that the expected stage never arrives. Add campaign and revenue fields only after the identifier and uniqueness checks are understood.

Reconciliation assertions

For each metric, write the expected relationship in plain language:

• A successful test lead creates one approved submission event.
• One CRM lead maps to the intended submission record.
• A qualified CRM stage appears once in the decision model.
• Closed revenue comes from the CRM field designated as authoritative.
• A late stage update is visible after the next model refresh.
• Missing campaign context is counted separately from direct or unknown traffic.

Set tolerances based on the actual system and window. A difference is not automatically an error, but an unexplained difference is not a pass.

How should you investigate discrepancies?

Use the narrowest shared sample that can prove the handoff. Aggregate totals are useful for detection, but a single synthetic or consent-approved test record is usually faster for diagnosis.

Do not fix a discrepancy by adding an arbitrary multiplier. Preserve the raw records, state the hypothesis, and add a test that can falsify it.

When is this architecture worth the effort?

Use the smallest architecture that can answer the current decision. A warehouse is useful when the team needs one or more of these capabilities:

• Join web activity to CRM stages over a longer window.
• Reconcile multiple ad platforms, analytics streams, and CRM sources.
• Preserve raw evidence for debugging instead of relying on interface exports.
• Model late-arriving outcomes and restate historical reports.
• Test campaign, identity, and revenue fields on a schedule.
• Provide a stable data product for reporting and offline conversion feedback.

It may be unnecessary when the business has one short funnel, few sources, no delayed outcomes, and a documented manual QA process that already answers its decisions. Adding BigQuery before defining ownership creates another place for ambiguous data to accumulate.

The BigQuery marketing data warehouse service is the commercial path when the team needs implementation help. Start with the tracking and attribution audit when the main problem is that nobody can explain where a metric came from.

Frequently asked questions

Is GA4 a CRM replacement?

No. GA4 can record event activity and useful acquisition context. A CRM should own the operational lifecycle and commercial records it manages. Join them through a defined contract instead of treating one as a replacement for the other.

Is BigQuery a source of truth?

It can be the source of truth for a modeled analytical metric if the model, source lineage, update behavior, and ownership are documented. It does not automatically become authoritative merely because it contains copies of several systems.

Why do GA4 and BigQuery numbers differ?

They may use different data layers, reporting identities, attribution logic, filters, processing states, date windows, or consent-related availability. Compare the same event, property, window, and definition before diagnosing a collection failure.

How do I match a lead to a deal?

Use a stable CRM relationship such as lead_id or the documented contact-to-deal key, then test one-to-many behavior and late stage updates. Do not infer the relationship from campaign name or email alone.

Should campaign parameters be stored in the CRM?

Store the approved campaign fields needed for the business decision and define their touchpoint meaning. Do not copy every browser parameter into every system without a data classification, retention, and ownership rule.

Do I need a full multi-touch model first?

No. First make one clearly defined touchpoint and one revenue outcome traceable. A complex model built on missing IDs or duplicated events creates precise-looking uncertainty.

Conclusion

GA4, BigQuery, and CRM architecture works when the systems cooperate without pretending to be interchangeable. GA4 provides event context, the CRM provides lifecycle and revenue outcomes, and BigQuery joins and tests the evidence.

Define the identifiers, preserve raw records, model late outcomes, and reconcile a bounded sample before building a larger attribution model. That sequence turns a stack diagram into a system a team can actually trust.

Sources

• GA4 BigQuery Export, retrieved 2026-09-04.
• BigQuery export for Google Analytics, retrieved 2026-09-04.
• Compare Analytics reports and data exported to BigQuery, retrieved 2026-09-04.
• URL builders: Collect campaign data with custom URLs, retrieved 2026-09-04.
• About offline conversion imports, retrieved 2026-09-04.