Why Automate GA4 Session Attribution with BigQuery Scheduled Queries?

GA4’s built-in attribution reports update daily and are locked into GA4’s attribution models—primarily data-driven attribution or last click. If you want to apply your own attribution logic, compare attribution models side by side, or join session attribution data with revenue figures from your CRM, you need to work with raw data in BigQuery. But running complex attribution queries manually every morning is tedious and error-prone. BigQuery Scheduled Queries automate this entirely: define your SQL attribution logic once, schedule it to run daily, and wake up each morning to a fresh attribution table that reflects the previous day’s complete data.

This guide walks through building a first-touch and last-touch session attribution model in BigQuery using GA4 event data, then scheduling it to run automatically so your Looker Studio dashboards always display up-to-date attribution without manual intervention.

Understanding GA4 Session Attribution in BigQuery

In the GA4 BigQuery Export, session-level traffic source data is stored in the traffic_source field at the event level. This field captures the source, medium, and campaign that acquired the user (not necessarily the session). For session-level attribution you need the session_start event and the event parameters source, medium, and campaign that are attached to it. The session_start event carries the traffic source information for that specific session, making it the right event to query for session-level attribution.

The GA4 session identifier is formed by combining user_pseudo_id and the session_id event parameter. No single field in the BigQuery export serves as a standalone session key—you must concatenate these two values to uniquely identify a session across a user’s history.

Step 1: Build the Session Attribution Query

The following query creates a daily session attribution table that joins session start data with purchase revenue. It supports first-touch attribution (credit the first session a user ever had) and last-touch attribution (credit the session immediately before each purchase):

-- Last-touch session attribution for purchases
-- Run this query for a specific date range, then schedule it for daily automation

WITH sessions AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS session_source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS session_medium,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS session_campaign,
    MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start_time
  FROM
    `your_project.ga4_export.events_*`
  WHERE
    _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND event_name = 'session_start'
  GROUP BY 1, 2, 3, 4, 5
),

purchases AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id') AS purchase_session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value') AS revenue,
    TIMESTAMP_MICROS(event_timestamp) AS purchase_time
  FROM
    `your_project.ga4_export.events_*`
  WHERE
    _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND event_name = 'purchase'
)

SELECT
  p.transaction_id,
  p.revenue,
  p.purchase_time,
  s.session_source AS last_touch_source,
  s.session_medium AS last_touch_medium,
  s.session_campaign AS last_touch_campaign,
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS attribution_date
FROM
  purchases p
JOIN
  sessions s
  ON p.user_pseudo_id = s.user_pseudo_id
  AND p.purchase_session_id = s.session_id;

This query uses DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) to always process yesterday’s data—making it ideal for daily scheduling. Each run appends one day of fresh attribution data to your output table.

Step 2: Create the Destination Table

Before scheduling the query, create the destination table where results will be written. In the BigQuery console, run a one-time version of the query with a static date suffix and use “Save Results” to create the table. Alternatively, create the table manually with the schema: transaction_id (STRING), revenue (FLOAT64), purchase_time (TIMESTAMP), last_touch_source (STRING), last_touch_medium (STRING), last_touch_campaign (STRING), attribution_date (DATE). Enable table partitioning by the attribution_date column—this makes future queries on this table much more efficient and cheaper by allowing date-range pruning.

Step 3: Schedule the Query in BigQuery

In the BigQuery console query editor, paste your attribution query. Click the “Schedule” button (the clock icon above the editor). Give the schedule a name like “GA4 Daily Session Attribution.” Set the repeat frequency to “Daily” and choose a run time of 6:00 AM UTC—this gives GA4’s daily BigQuery export (which runs in the early morning UTC hours) time to complete before your attribution query runs. Set the destination dataset and table where results should be written, and choose “Append to table” as the write preference so each daily run adds rows without overwriting previous results.

Under “Advanced options,” set the destination table’s partitioning field to attribution_date if you want BigQuery to automatically manage partitions. Click “Save” to create the scheduled query. BigQuery will create a service account for the scheduled query automatically—ensure this account has read access to your GA4 export dataset and write access to the destination dataset.

Step 4: Monitor Scheduled Query Runs

In the BigQuery console, navigate to “Scheduled queries” in the left panel. You will see your new schedule listed with its next run time and status. After the first scheduled run completes, click on the schedule name to view run history. Each run shows the start time, end time, bytes processed, and whether it succeeded or failed. Set up email notifications for failures by clicking “Edit” on the schedule and enabling “Failure notifications”—this ensures you are alerted immediately if the GA4 export data is delayed or if a schema change in the export breaks your query.

Step 5: Connect to Looker Studio

With daily attribution data automatically flowing into your BigQuery table, connect it to Looker Studio for visualization. Create a new Looker Studio report, add a BigQuery data source pointing to your attribution table, and build a simple table chart with columns: attribution_date, last_touch_source, last_touch_medium, last_touch_campaign, count of transaction_id (as “Conversions”), and sum of revenue. Add a date range control and filter the data source by attribution_date to make the dashboard interactive. Now your marketing team has a daily-refreshed attribution report that shows exactly which channels drove revenue according to your custom attribution logic—not GA4’s opaque data-driven model.

Extending to Multi-Touch Attribution

The query above implements last-touch attribution, which is simple but ignores the customer journey’s full complexity. To build linear multi-touch attribution (equal credit to every session in the conversion path), modify the query to join each purchase not just to its own session but to all sessions by the same user that occurred before the purchase timestamp. Then divide the revenue equally across all contributing sessions. This requires a more complex window function approach, but the BigQuery Scheduled Query infrastructure you have built remains exactly the same—you simply update the SQL and the output table schema to include a credit_fraction column.

Conclusion

BigQuery Scheduled Queries transform one-off attribution analysis into a sustainable, automated reporting pipeline. By defining your attribution SQL once and scheduling it to run on GA4’s fresh daily data, you eliminate manual query work, ensure your dashboards are always current, and build a foundation for increasingly sophisticated attribution models. The infrastructure required—a scheduled query, a partitioned destination table, and a Looker Studio connection—takes a few hours to set up and then runs itself indefinitely with zero ongoing effort.