How to Calculate GA4 Sessions Only When Consent Is Granted Using BigQuery SQL

When Consent Mode is enabled in GA4, your session reporting gets a bit trickier — especially if you’re working with raw event data in BigQuery. If a user hasn’t granted analytics consent (analytics_storage = 'granted'), GA4 won’t assign session or user IDs. That means no session continuity and no proper attribution.

But if you’re exporting your GA4 data to BigQuery, you can still accurately calculate sessions only when users have granted consent. Here’s how.

BigQuery SQL: Sessions Where analytics_storage = 'granted'

This query helps you isolate sessions that are fully trackable, with session IDs and user identifiers present. These are only possible when analytics consent has been granted.

-- GA4 Sessions Only When Consent Granted
SELECT
  user_pseudo_id,
  event_bundle_sequence_id,
  event_timestamp,
  event_date,
  event_name,
  MIN(CASE 
        WHEN event_name = 'session_start' THEN event_timestamp 
      END) AS session_start_ts,
  MAX(ga_session_id) AS session_id,
  MAX(ga_session_number) AS session_number,
  COUNT(DISTINCT IF(event_name = 'session_start', ga_session_id, NULL)) AS session_count
FROM
  `your_project_id.your_dataset_id.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250701' AND '20250727' -- Adjust date range
  AND event_name IN ('session_start', 'page_view', 'user_engagement')
  AND EXISTS (
    SELECT 1
    FROM UNNEST(event_params) AS ep
    WHERE ep.key = 'analytics_storage'
      AND ep.value.string_value = 'granted'
  )
  AND EXISTS (
    SELECT 1
    FROM UNNEST(event_params) AS ep
    WHERE ep.key = 'ga_session_id'
  )
GROUP BY
  user_pseudo_id,
  event_bundle_sequence_id,
  event_timestamp,
  event_date,
  event_name
ORDER BY
  session_start_ts

What This Query Does

• Filters for Consent: It includes only events where analytics_storage = 'granted', meaning users explicitly accepted analytics tracking.
• Captures Real Sessions: It looks for the session_start event, which marks the beginning of a session in GA4.
• Ensures Identifiable Data: It excludes anonymous or cookieless pings by requiring ga_session_id to be present.
• Supports Multi-Day Analysis: _TABLE_SUFFIX lets you scan multiple daily export tables over a date range.

⚠️ Note: Depending on your consent implementation, analytics_storage might be stored in user_properties instead of event_params. Adjust accordingly.