Building First-Party Multi-Touch Attribution Models in BigQuery When Native GA4 Falls Short

Why GA4's Native Attribution Falls Short

GA4 offers four built-in attribution models (first-click, last-click, linear, time-decay). They work for simple e-commerce flows where conversion happens in one session. They break down in real business scenarios.

GA4's models cannot credit offline conversions (phone calls, in-store purchases, manual CRM entries). They do not see first-party data your CRM or email platform owns. They cannot model B2B sales cycles where 12 touchpoints happen over 90 days across multiple devices and browsers. Most critically, they lack the flexibility to weight channels differently based on your actual margin, customer lifetime value, or sales cycle stage.

BigQuery solves this. You own the raw event stream. You can join GA4 data with CRM records, cost data, and revenue. You can build attribution logic that matches how your business actually converts.

What You'll Build in BigQuery

A first-party attribution pipeline that sits between raw GA4 events and your BI tool or dashboard. The pipeline will:

• Ingest GA4 events via BigQuery export and join them with CRM conversion records by user ID and timestamp
• Define custom conversion windows (e.g., 90 days for B2B, 14 days for e-commerce)
• Assign credit to each touchpoint using a model you control (linear, time-decay, position-based, or custom)
• Output a clean table where each row is a touchpoint, tagged with the conversion it contributed to and the credit allocated
• Feed that table into Looker, Data Studio, or your warehouse for reporting

Prerequisites and Data Setup

You need BigQuery access, GA4 data exported to BigQuery (automatic in GA4 projects linked to Google Cloud), and a CRM or transactional database with user IDs that match GA4's user_id or client_id. If your CRM uses different identifiers, plan a hashing or lookup table to unify them.

Export your CRM conversion events (deal closed, purchase, demo booked) into BigQuery as a separate table. Include: conversion ID, user ID, conversion timestamp, revenue, and any deal metadata (product, region, sales rep). This table is your source of truth for what actually converted.

Check that GA4 user_id is set consistently. If your site uses email login, set user_id to a hashed email. If you use CRM IDs, use those. Inconsistent user IDs will break the join. Test your user ID population on a small cohort before building the full pipeline.

Step 1: Join GA4 Events to Conversions

Write a SQL query that matches each GA4 event to its conversion. The join uses user ID and a time window.

Start with a simple LEFT JOIN. For each conversion, find all GA4 events from the same user within your conversion window (e.g., 90 days before conversion).

SELECT
  ga.event_timestamp,
  ga.event_name,
  ga.source,
  ga.medium,
  ga.campaign,
  crm.conversion_id,
  crm.conversion_timestamp,
  crm.revenue,
  TIMESTAMP_DIFF(crm.conversion_timestamp, ga.event_timestamp, DAY) as days_to_conversion
FROM `project.dataset.events` ga
LEFT JOIN `project.dataset.crm_conversions` crm
  ON ga.user_id = crm.user_id
  AND ga.event_timestamp <= crm.conversion_timestamp
  AND ga.event_timestamp > TIMESTAMP_SUB(crm.conversion_timestamp, INTERVAL 90 DAY)
WHERE ga.event_name IN ('page_view', 'click', 'form_submit')
  AND crm.conversion_id IS NOT NULL
ORDER BY crm.conversion_id, ga.event_timestamp;

This query pulls every event that occurred within 90 days before a conversion. Each row is an event-to-conversion pair. You now have the raw material for attribution.

Step 2: Define Your Attribution Model

Choose how to allocate credit. The most common models are:

• Linear: Each touchpoint gets equal credit (100% / number of touches)
• Time-decay: Recent touches get more credit; older touches get less (e.g., exponential decay)
• Position-based: First and last touch split credit (40% / 40%), middle touches share the rest (20%)
• Custom: Weight by channel, device, or campaign; e.g., paid search gets 50%, organic 30%, direct 20%

For B2B, time-decay or position-based often works well. For e-commerce, linear is simpler. Pick one; you can test others later.

Here's a time-decay model using SQL window functions. Recent events get higher credit.

WITH event_to_conversion AS (
  -- Previous query result
),
with_decay_weight AS (
  SELECT
    *,
    EXP(-0.05 * days_to_conversion) as decay_weight
  FROM event_to_conversion
),
total_weight_per_conversion AS (
  SELECT
    conversion_id,
    SUM(decay_weight) as total_weight
  FROM with_decay_weight
  GROUP BY conversion_id
)
SELECT
  conversion_id,
  event_name,
  source,
  medium,
  campaign,
  revenue,
  decay_weight / total_weight as credit_allocation,
  (revenue * decay_weight / total_weight) as attributed_revenue
FROM with_decay_weight
JOIN total_weight_per_conversion USING (conversion_id);

The EXP(-0.05 * days_to_conversion) line decays credit exponentially. Events 1 day before conversion get more credit than events 60 days before. Adjust the 0.05 coefficient to make decay faster (0.1) or slower (0.01).

Step 3: Aggregate by Channel and Campaign

Once you have per-event credit, roll it up by channel, campaign, or source. This is where you answer the business question: which channels drove revenue?

SELECT
  source,
  medium,
  campaign,
  COUNT(DISTINCT conversion_id) as conversions,
  SUM(attributed_revenue) as attributed_revenue,
  SUM(attributed_revenue) / COUNT(DISTINCT conversion_id) as revenue_per_conversion
FROM attribution_table
GROUP BY source, medium, campaign
ORDER BY attributed_revenue DESC;

This table is now your source of truth for multi-touch attribution. Plug it into Looker, Data Studio, or your BI tool. Compare the attributed revenue by channel to your actual spend to calculate return on ad spend (ROAS) or cost per acquisition (CPA).

Common Pitfalls and Fixes

User ID Mismatch

Your GA4 events have one user ID, but your CRM uses another. The join returns zero rows. Solution: Build a user mapping table that connects GA4 user IDs to CRM IDs. Hash both sides consistently (e.g., SHA256 of email) before joining.

Timestamp Misalignment

GA4 timestamps are in UTC; your CRM may be in a different timezone. Events appear to happen after the conversion. Solution: Convert all timestamps to UTC in both tables before the join. Use TIMESTAMP_ADD(crm_ts, INTERVAL -5 HOUR) to shift if needed.

Duplicate Conversions

One user converts twice. The join creates duplicate rows for each event. Solution: Add a DISTINCT on conversion ID when aggregating, or use a window function to rank conversions and filter to the first within a time window.

Attribution Inflation

You sum attributed revenue across all channels and it exceeds your actual revenue. This happens if one event is attributed to multiple conversions. Solution: Validate that total attributed revenue equals actual revenue. If not, check for overlapping conversion windows or duplicate events in your GA4 table.

Missing Offline Conversions

Phone calls or in-store sales have no GA4 event. They don't appear in your attribution table. Solution: Import offline conversions as a separate table and join them the same way. Tag them with an offline_conversion flag. Your attribution pipeline will still credit the online touchpoints that preceded the offline conversion.

Scaling the Pipeline

Start with a single attribution model and one conversion type. Once you validate the logic, expand:

• Add more conversion types (demo, trial, enterprise deal) and run separate attribution for each
• Test multiple models (linear, time-decay, position-based) side-by-side in separate columns
• Include additional dimensions (device, user cohort, landing page) in the grouping
• Join cost data and calculate true ROAS or CAC by channel

Schedule the query to run daily or weekly using BigQuery scheduled queries. Output to a new table each time. Feed the table into your BI tool via a scheduled refresh.

Integration with Existing Workflows

Your BigQuery attribution table is now a source of truth, separate from GA4's native reports. Use it alongside GA4, not instead of it. GA4 is still useful for session-level analysis, real-time dashboards, and troubleshooting. Your BigQuery model answers the harder question: which touchpoints actually drove revenue across the full customer journey?

Export the table to Google Sheets or Data Studio for stakeholder dashboards. Plug it into your marketing mix modeling (MMM) or incrementality testing workflow. Use it to inform budget allocation decisions.

What to Do Next

Start by exporting your GA4 data and CRM conversions into BigQuery (if not already done). Write a simple linear attribution query for one conversion type. Validate that the total attributed revenue matches your actual revenue. Once you trust the numbers, test a time-decay model and compare the channel rankings. Then work with a data analyst to scale the pipeline and integrate it into your reporting stack.

FAQs

People Also Ask