GA4 Data-Quality Auditor: the BigQuery checks to run before you trust the numbers - The Vault

Every GA4 BigQuery report is only as good as the export underneath it, and the export can be quietly broken for months before anyone notices, a tag firing without a session ID, a consent-mode gap dumping traffic into unassigned, a checkout step leaking an email into the page URL. These five checks run against the raw export schema and surface the problems before you build a dashboard on top of them.

Before you start

The export dataset is named analytics_ followed by your property ID, and each daily table is events_YYYYMMDD. Replace analytics_XXXXXXXXX in every query below with your dataset, and adjust the _TABLE_SUFFIX date range.
No GA4 export yet, or want to try these first? Google publishes a public obfuscated ecommerce sample at bigquery-public-data.ga4_obfuscated_sample_ecommerce, and the last query below runs against it directly.
Every query is a read-only SELECT. Nothing here writes back to BigQuery or to GA4.

The checks

Unassigned traffic share, sessions with no usable source, medium or click ID.

SQL

-- Unassigned traffic: sessions with no usable source, medium or gclid on the session-start event
SELECT
  COUNT(*) AS unassigned_sessions
FROM `analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260618' AND '20260718'
  AND event_name = 'session_start'
  AND (collected_traffic_source.manual_source IS NULL OR collected_traffic_source.manual_source = '(not set)')
  AND collected_traffic_source.gclid IS NULL
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') IS NULL

Missing required params, events firing without a session ID, page location or engagement flag.

SQL

-- Missing required params: events firing without ga_session_id, page_location or session_engaged
SELECT
  event_name,
  COUNTIF(NOT EXISTS (SELECT 1 FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id')) AS missing_session_id,
  COUNTIF(NOT EXISTS (SELECT 1 FROM UNNEST(event_params) ep WHERE ep.key = 'page_location')) AS missing_page_location,
  COUNTIF(NOT EXISTS (SELECT 1 FROM UNNEST(event_params) ep WHERE ep.key = 'session_engaged')) AS missing_session_engaged,
  COUNT(*) AS total_events
FROM `analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260618' AND '20260718'
GROUP BY event_name
ORDER BY total_events DESC

Possible PII in page URLs, emails or common identifier keys leaking into the URL itself.

SQL

-- Possible PII in page URLs: emails or common identifier keys appearing in the page_location string
SELECT
  event_date,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  COUNT(*) AS occurrences
FROM `analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260618' AND '20260718'
  AND (
    REGEXP_CONTAINS((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}')
    OR REGEXP_CONTAINS((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r'[?&](email|phone|token)=')
  )
GROUP BY event_date, page_location
ORDER BY occurrences DESC

Duplicate events, the same bundle firing more than once at the same timestamp.

SQL

-- Duplicate events: the same bundle sequence and timestamp appearing more than once
SELECT
  event_bundle_sequence_id,
  event_timestamp,
  event_name,
  COUNT(*) AS duplicate_count
FROM `analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260618' AND '20260718'
GROUP BY event_bundle_sequence_id, event_timestamp, event_name
HAVING duplicate_count > 1
ORDER BY duplicate_count DESC

Broken purchase revenue, purchase events recorded with no revenue attached.

SQL

-- Purchase revenue integrity: purchase events with a null or zero revenue figure
SELECT
  event_date,
  COUNT(*) AS broken_purchases
FROM `analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260618' AND '20260718'
  AND event_name = 'purchase'
  AND (ecommerce.purchase_revenue IS NULL OR ecommerce.purchase_revenue = 0)
GROUP BY event_date
ORDER BY event_date

Zero-setup variant, the unassigned-traffic check run against Google's public sample dataset.

SQL

-- Public-dataset variant: an unassigned-traffic check against Google's public GA4 sample, runnable with zero setup.
-- The sample dataset (late 2020 to early 2021) predates the collected_traffic_source field, so this variant reads the
-- traffic_source struct the sample does carry. On your own current export, prefer the collected_traffic_source query above.
SELECT
  COUNT(*) AS unassigned_sessions
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
  AND event_name = 'session_start'
  AND (
    traffic_source.source IS NULL
    OR traffic_source.source = '(direct)'
    OR traffic_source.source = '(not set)'
  )

Reading the output

An unassigned share creeping past ten percent usually points at a consent-mode or tag-firing gap, not a genuinely untrackable audience. Missing ga_session_id on a meaningful share of events breaks session-scoped reporting site-wide, chase that one first. A non-zero PII count is the most urgent: a checkout or login flow that puts an email in the query string needs a code fix, not a data-layer patch, because the export has already captured it by the time you see this row. Duplicate events inflate every downstream count, and broken purchase revenue quietly understates return on ad spend in exactly the reports a client is most likely to scrutinise.

Worth knowing

These are read-only SELECTs and touch nothing in your GA4 property or your BigQuery project beyond the query cost of scanning the date range you set, keep that range tight on a large property to control cost. The regular expressions in the PII check are deliberately conservative, treat a hit as a lead to investigate, not proof of a breach, and never paste a query result containing a live email into a shared document.