GA4BigQuery

Google Analytics 4’s Consent Mode helps websites respect user privacy by adjusting data collection based on consent. While this is essential for GDPR and CCPA compliance, it introduces a challenge: attribution gaps.

When users initially deny consent but later accept it (or give partial consent), GA4 sessions and conversions may get split or missed in the UI reports. This results in incomplete or inaccurate attribution models, complicating marketing analysis.

Why Attribution is Missing in Standard GA4 UI

• GA4 UI excludes data from users who deny consent or delay it.
• Sessions before consent are untracked or assigned temporary identifiers.
• Conversions linked to delayed consent sessions often have no associated source/medium.
• Standard GA4 attribution models therefore underreport marketing channel impact.

SQL Model: Assigning Sessions to Source/Medium After Consent

To rebuild accurate attribution, you can use BigQuery to:

• Identify sessions before and after consent
• Link delayed sessions to user identifiers (user_pseudo_id, user_id)
• Backfill source/medium from the earliest known session with consent
• Assign conversions to the proper marketing channel using modeled attribution

WITH session_data AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    event_timestamp,
    traffic_source.source,
    traffic_source.medium,
    consent_given,
    -- Assume a boolean field `consent_given` indicates consent status per event
    FIRST_VALUE(traffic_source.source) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY CASE WHEN consent_given THEN event_timestamp ELSE NULL END
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS modeled_source,
    FIRST_VALUE(traffic_source.medium) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY CASE WHEN consent_given THEN event_timestamp ELSE NULL END
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS modeled_medium
  FROM
    `your_project.your_dataset.events_*`
)

SELECT
  user_pseudo_id,
  ga_session_id,
  event_timestamp,
  COALESCE(traffic_source.source, modeled_source) AS final_source,
  COALESCE(traffic_source.medium, modeled_medium) AS final_medium
FROM
  session_data
WHERE
  event_name = 'purchase' -- or any conversion event
ORDER BY
  event_timestamp

Optional Enhancements

• Apply weighted attribution by session recency or engagement time
• Use time-decayed models to credit earlier touchpoints less
• Integrate with CRM or ad data to further enrich attribution
• Automate this as a scheduled query for daily updates

Related Posts

• 🔙 How to Track User Journeys in GA4 BigQuery with Session Stitching
• 🔜 What is traffic_source.medium in GA4 BigQuery?

TL;DR

• Consent Mode creates attribution gaps by delaying or denying tracking
• Standard GA4 UI underreports marketing source impact on conversions
• Use BigQuery SQL to backfill missing source/medium by stitching sessions
• Enhance attribution with weighted or time-decay models
• Automate and integrate for robust privacy-aware marketing insights