GA4BigQuery

Marketers often ask the big question: “Which channel really drives conversions?”
With GA4 and BigQuery, you can move beyond guesswork and analyze attribution models directly in your data warehouse. In this post, we’ll show how to implement last-click attribution using SQL and compare it with GA4’s data-driven attribution (DDA) tables. This helps you evaluate channels side by side and optimize campaign spend more effectively.

Step 1. Last-Click Attribution in BigQuery

Let’s start with a simple last-click model. This approach gives credit to the last touchpoint (source/medium or campaign) before conversion.

WITH user_paths AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    traffic_source.source AS source,
    traffic_source.medium AS medium,
    traffic_source.name AS campaign,
    event_name
  FROM
    `project.dataset.events_*`
  WHERE
    event_name IN ("session_start", "purchase")
)

, ordered_events AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    source,
    medium,
    campaign,
    event_name,
    ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC) AS rn
  FROM user_paths
)

SELECT
  user_pseudo_id,
  source,
  medium,
  campaign
FROM ordered_events
WHERE event_name = "purchase"
  AND rn = 1

How this works

• Collects all events for users, keeping traffic source info.
• Orders events per user by timestamp.
• Picks the last non-direct touchpoint before purchase.

Reference: GA4 traffic_source documentation.

Step 2. Data-Driven Attribution (GA4 Exported Tables)

If you have Attribution Modeling enabled in GA4, BigQuery automatically exports model comparison tables (attribution_*).

You can query data-driven attribution (DDA) like this:

SELECT
  event_date,
  traffic_source.source,
  traffic_source.medium,
  traffic_source.name AS campaign,
  SUM(conversion_credit) AS credited_conversions
FROM
  `project.dataset.attribution_*`
WHERE
  attribution_model = "DATA_DRIVEN"
  AND event_name = "purchase"
GROUP BY
  event_date, source, medium, campaign
ORDER BY credited_conversions DESC

How this works

• Pulls GA4’s machine-learning attribution data.
• conversion_credit distributes value across multiple touchpoints.
• You can compare by campaign or channel to see GA4’s perspective.

Reference: GA4 Attribution Models.

Step 3. Comparing Last Click vs Data-Driven

Once you have both queries, you can:

• Join tables to see where attribution differs.
• Identify channels under-valued by last-click (e.g., display, social).
• Reallocate spend to campaigns that drive assisted conversions.

For example, social may rarely be last-click but contribute significantly in DDA.

Practical Applications

• Campaign Optimization: Adjust bids/spend to channels with stronger assisted roles.
• Stakeholder Reporting: Show both last-click (traditional) and DDA (modern).
• Experimentation: Run media tests to validate attribution differences.

TL;DR:

• Last-click = simple, easy to query, but biased toward bottom-funnel channels.
• DDA = GA4’s smarter model that redistributes credit using ML.
• Use both in BigQuery to compare and make smarter campaign decisions.