# BigQuery checks

Read-only queries against the GA4 daily export. Replace `example-project.analytics_123456789` with the real dataset, the dates with a completed window, and `Etc/UTC` with the property timezone. Run QC, then Q0, first: if the export is partial or a field is missing or renamed, fix that before reading any other result.

Rules for every query:

- Dry-run first with `bq query --dry_run` or the console where available. Neither Google's BigQuery MCP how-to page nor its MCP reference documented a dry-run option on 2026-10-17; check the server's tool list (`tools/list`) at run time rather than assuming either way. Without a dry run, keep the window to 7 days or less and the SELECT aggregated. The wildcard scans every matching table.
- `_TABLE_SUFFIX BETWEEN '20261001' AND '20261007'` keeps intraday tables out (their suffix starts with `intraday_`).
- The session key is `user_pseudo_id` plus the session ID. Never count sessions by the session ID alone.
- GA4 does not restart sessions at midnight. Session-level queries (Q1, Q4b, Q6) read one extra day on each side and keep only sessions whose session ID (the start time in seconds) falls inside the window.
- Read the session ID as `COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))` on key `ga_session_id`. A server sender can put it in `string_value`. The Measurement Protocol and Data Manager API name the parameter `session_id`, and the pages checked on 2026-10-17 do not say whether the export keeps that key or renames it. Q0 shows which key and value field your export uses. If server rows carry `session_id`, replace the session ID subquery in Q1 to Q3 and Q8 with the two-key version below.
- Through the BigQuery MCP server, use `execute_sql_readonly`. Google's MCP reference says it is restricted to SELECT statements. Send one statement per call. Google's how-to page limits results to 3,000 rows and processing time to three minutes by default, so aggregate in SQL.
- Google's MCP reference adds an asynchronous model. The call waits up to `timeout_ms` (default 20,000 ms on 2026-10-17); a slower query returns `job_complete: false` with a `job_id` and `location`. Poll with `get_query_results` until `job_complete` is true, and page large results with it. Do not rerun the query. `job_timeout_ms` sets the hard server-side limit, and `cancel_job` stops a query you no longer need.
- The queries filter `platform = 'WEB'`. Google's schema describes the values as Web, IOS or Android; run `SELECT DISTINCT platform` on one day first and use the spelling your export has.

Two-key session ID, for exports where server rows use `session_id`:

```sql
COALESCE(
  (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
   FROM UNNEST(event_params) WHERE key = 'ga_session_id'),
  (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
   FROM UNNEST(event_params) WHERE key = 'session_id')
)
```

## QC. Export coverage

Every verdict below assumes the export holds every day and every stream. Check that first.

```sql
SELECT
  _TABLE_SUFFIX AS day,
  COUNT(*) AS events,
  COUNTIF(privacy_info.analytics_storage IS DISTINCT FROM 'No') AS events_not_denied,
  COUNTIF(privacy_info.analytics_storage = 'No') AS events_denied,
  COUNT(DISTINCT stream_id) AS streams,
  STRING_AGG(DISTINCT stream_id ORDER BY stream_id) AS stream_ids,
  COUNTIF(event_name = 'session_start') AS session_starts,
  COUNTIF(event_name = 'purchase') AS purchases
FROM `example-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260930' AND '20261008'
GROUP BY day
ORDER BY day;
```

Then pull GA4's Event count for the same dates from the Data API (`eventCount` by `date`, no filters). Record the consent mode (none, basic or advanced), the property's reporting identity, and whether behavioural modelling is active.

Pass when all of these hold:

- Every date in the window, including the extra days the session queries read, has a row.
- `stream_ids` lists every stream in `stack.md` that the checks rely on.
- Event counts agree within an agreed tolerance (for example 2%):
  - No consent mode, or basic consent mode: compare `events` with `eventCount`.
  - Advanced consent mode: compare `events_not_denied` with `eventCount`. Google's export page says cookieless pings are present in the export, and its modelling page says that when there is not enough consented traffic to inform the model, events from users who decline consent are not reported. On a property with a real denial rate, `events` well above `eventCount` is expected and does not mean the export is partial. Keep `events_denied` as its own column in the record.
  - Advanced consent mode with modelling active: reports can include modelled data that the export never holds, and Google's reporting surfaces page says modelling can make reports and BigQuery differ. Treat an `eventCount` between `events_not_denied` and `events` as consistent with a complete export.
- The BigQuery link in GA4 Admin shows no excluded streams or events that the checks need. Google lets you exclude both, at linking time or later.
- On a standard property, daily events are not close to 1 million. Google pauses the daily export when a property consistently exceeds that limit and does not reprocess previous days; check whether the export is paused.

If any check fails, record the export as partial for those days or streams and do not use it to rule a cause out. Streaming (intraday) export is best effort and can contain gaps, so never use it for coverage.

## Q0. Confirm the field names and where the session ID lives

```sql
SELECT field_path, data_type
FROM `example-project.analytics_123456789.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE table_name = 'events_20261001'
  AND (
    field_path LIKE 'traffic_source%'
    OR field_path LIKE 'collected_traffic_source%'
    OR field_path LIKE 'session_traffic_source_last_click%'
    OR field_path LIKE 'privacy_info%'
    OR field_path IN ('ecommerce.transaction_id', 'device.web_info.hostname', 'geo.city',
                      'user_pseudo_id', 'stream_id', 'platform')
  )
ORDER BY field_path;
```

Look for `channel_group` in the output. If `session_traffic_source_last_click.cross_channel_campaign.default_channel_group` exists, you can read the channel label directly; if not, take it from the UI or Data API.

Which key and value field hold the session ID, by event:

```sql
SELECT
  p.key,
  event_name,
  COUNTIF(p.value.int_value IS NOT NULL) AS in_int_value,
  COUNTIF(p.value.string_value IS NOT NULL) AS in_string_value,
  COUNTIF(p.value.int_value IS NULL AND p.value.string_value IS NULL) AS in_neither
FROM `example-project.analytics_123456789.events_*`, UNNEST(event_params) AS p
WHERE _TABLE_SUFFIX = '20261001'
  AND p.key IN ('ga_session_id', 'session_id')
GROUP BY p.key, event_name
ORDER BY in_int_value + in_string_value DESC
LIMIT 100;
```

Reading it:

- Events with the ID in `string_value`, or under key `session_id`, come from a sender that is not the Google tag. Note them for cause E.
- Any rows under `session_id`: use the two-key session ID in Q1 to Q3 and Q8. Otherwise Q1 to Q3 report "no session ID" for those events and cause E fires for the wrong reason.
- Events with neither key do not appear here at all. Q2 lists them.

## Q1. Session inventory: `session_start`, `page_view`, first event and consent state

Answers causes C and H, and feeds Q4b and Q6. One row per pattern.

```sql
WITH ev AS (
  SELECT
    user_pseudo_id,
    (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
     FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
    event_name,
    event_timestamp,
    batch_event_index,
    device.web_info.hostname AS hostname,
    privacy_info.analytics_storage AS analytics_storage
  FROM `example-project.analytics_123456789.events_*`
  -- one extra day on each side so sessions that cross midnight keep all their events
  WHERE _TABLE_SUFFIX BETWEEN '20260930' AND '20261008'
    AND platform = 'WEB'
    AND user_pseudo_id IS NOT NULL
),
sessions AS (
  SELECT
    user_pseudo_id,
    session_id,
    ARRAY_AGG(hostname IGNORE NULLS ORDER BY event_timestamp LIMIT 1)[SAFE_OFFSET(0)] AS first_hostname,
    ARRAY_AGG(IF(event_name IN ('session_start', 'first_visit'), NULL, event_name) IGNORE NULLS
              ORDER BY event_timestamp, batch_event_index LIMIT 1)[SAFE_OFFSET(0)] AS first_event,
    ARRAY_AGG(IFNULL(analytics_storage, 'null')
              ORDER BY event_timestamp, batch_event_index LIMIT 1)[SAFE_OFFSET(0)] AS first_consent_state,
    ARRAY_AGG(IFNULL(analytics_storage, 'null')
              ORDER BY event_timestamp DESC, batch_event_index DESC LIMIT 1)[SAFE_OFFSET(0)] AS last_consent_state,
    COUNT(DISTINCT IFNULL(analytics_storage, 'null')) > 1 AS mixed_consent,
    MAX(session_number) AS session_number,
    COUNTIF(event_name IN ('purchase', 'generate_lead')) AS key_events,  -- list the property's key events
    COUNTIF(event_name = 'session_start') > 0 AS has_session_start,
    COUNTIF(event_name = 'page_view') > 0 AS has_page_view
  FROM ev
  WHERE session_id IS NOT NULL
    -- keep sessions that started inside the window, in the property timezone
    AND session_id >= UNIX_SECONDS(TIMESTAMP('2026-10-01', 'Etc/UTC'))
    AND session_id <  UNIX_SECONDS(TIMESTAMP('2026-10-08', 'Etc/UTC'))
  GROUP BY user_pseudo_id, session_id
)
SELECT
  first_hostname,
  has_session_start,
  has_page_view,
  first_event,
  COUNT(*) AS sessions,
  SUM(key_events) AS key_events
FROM sessions
GROUP BY first_hostname, has_session_start, has_page_view, first_event
ORDER BY sessions DESC
LIMIT 200;
```

Reading it:

- `has_session_start = false` with a custom `first_event`: events before the Google tag configured, or a consent sequence error (causes C, D). Run Q4a, then Q4b, to tell them apart.
- `has_page_view = false`: Landing page `(not set)` (cause H). The `first_event` column tells you which cluster: a server event, a consent event, an interaction after idle time, or a single-page-app route.
- Compare the same query for a window before the first-change date. A new pattern on one hostname points to that site's release.
- Q1 keeps only events with a pseudo ID and a session ID. Events without them are invisible here; Q2 and Q4a cover them.
- Session IDs outside the window range, such as a sender using milliseconds, drop out here. Q3 covers them.

## Q2. Events outside a session

Answers cause G.

```sql
SELECT
  event_name,
  stream_id,
  platform,
  privacy_info.analytics_storage AS analytics_storage,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NOT NULL AS has_page_location,
  COUNT(*) AS events,
  COUNT(DISTINCT user_pseudo_id) AS distinct_pseudo_ids
FROM `example-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
       FROM UNNEST(event_params) WHERE key = 'ga_session_id') IS NULL
GROUP BY event_name, stream_id, platform, analytics_storage, has_page_location
ORDER BY events DESC;
```

Reading it:

- Rows without a page location and with very few distinct pseudo IDs are almost always a backend sender (cause E).
- `No` rows with a page location: read Q4a first. Google's reporting surfaces page says the export holds advanced consent mode cookieless pings and that each session has a different `user_pseudo_id`; it does not say whether they carry a session ID.
  - If Q4a shows denied rows normally carry a session ID, sessionless `No` rows are not explained by cookieless pings. Check them as a sender or sequence defect.
  - If Q4a shows denied rows normally lack one, these rows can be consented-away pings. Test the sequence first (grant, navigate to a second page, confirm in Tag Assistant that no denied hits follow the grant) before recording them as consented-away: denied events sent after a granted choice look the same here.

## Q3. Server-sent key events: identity and session context

Answers cause E. Shown for `purchase`; change the event name for leads. Run each block below as its own call: the follow-up queries repeat the `WITH p AS (...)` clause because a CTE does not carry between statements.

```sql
WITH p AS (
  SELECT
    user_pseudo_id,
    ecommerce.transaction_id AS transaction_id,
    (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
     FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    session_traffic_source_last_click.cross_channel_campaign.source AS s_source,
    session_traffic_source_last_click.cross_channel_campaign.medium AS s_medium
  FROM `example-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
    AND event_name = 'purchase'
)
SELECT
  page_location IS NOT NULL AS sent_from_a_page,
  COUNT(*) AS events,
  COUNT(DISTINCT transaction_id) AS orders,
  COUNT(DISTINCT user_pseudo_id) AS distinct_pseudo_ids,
  ROUND(COUNT(DISTINCT user_pseudo_id) / NULLIF(COUNT(DISTINCT transaction_id), 0), 3) AS ids_per_order,
  COUNTIF(session_id IS NOT NULL) AS with_session_id,
  COUNT(DISTINCT CONCAT(IFNULL(s_source, 'null'), ' / ', IFNULL(s_medium, 'null'))) AS distinct_session_pairs
FROM p
GROUP BY sent_from_a_page;
```

The most frequent identities on the pageless rows:

```sql
WITH p AS (
  SELECT
    user_pseudo_id,
    ecommerce.transaction_id AS transaction_id
  FROM `example-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
    AND event_name = 'purchase'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NULL
)
SELECT user_pseudo_id, COUNT(DISTINCT transaction_id) AS orders
FROM p
GROUP BY user_pseudo_id
ORDER BY orders DESC
LIMIT 10;
```

The session ID length on the pageless rows:

```sql
WITH p AS (
  SELECT
    (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
     FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
  FROM `example-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
    AND event_name = 'purchase'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NULL
)
SELECT LENGTH(CAST(session_id AS STRING)) AS session_id_digits, COUNT(*) AS events
FROM p
GROUP BY session_id_digits;
```

Decision rules:

- `ids_per_order` on pageless rows is under half of `ids_per_order` on page rows, or the top identity holds more orders in the window than the order system's 99th percentile of orders per customer for the same window, or an identity is a literal such as `undefined`, `null` or `0`: the sender is not passing the browser client ID.
- `distinct_session_pairs` of 1 on pageless rows, with populated values: the non-null trap. The values are a constant, not attribution.
- `with_session_id` under 10% of pageless events: no session context; these events cannot join a session. Check Q0 first: a session ID under key `session_id` reads as missing until you use the two-key version.

Do server session IDs match a browser session? Extend the window a day earlier for the starts:

```sql
WITH p AS (
  SELECT
    user_pseudo_id,
    (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
     FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
  FROM `example-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
    AND event_name = 'purchase'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NULL
),
starts AS (
  SELECT DISTINCT
    user_pseudo_id,
    (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
     FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
  FROM `example-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260930' AND '20261007'
    AND event_name = 'session_start'
)
SELECT
  COUNT(*) AS server_purchases,
  COUNTIF(p.session_id IS NOT NULL) AS with_session_id,
  COUNTIF(s.session_id IS NOT NULL) AS joined_to_a_browser_session
FROM p
LEFT JOIN starts AS s
  ON p.user_pseudo_id = s.user_pseudo_id AND p.session_id = s.session_id;
```

Decision rule: `with_session_id` at 80% or more of `server_purchases` but `joined_to_a_browser_session` under 10% of it is a format mismatch before it is a timing problem. Check `session_id_digits` (10 digits is seconds; 13 suggests milliseconds) and compare the server `user_pseudo_id` format with browser rows.

## Q4. Consent state split

Answers cause D. Measures, rather than assumes, how each consent state looks in this export. Run Q4a before Q4b.

Q4a, event level, by day and state:

```sql
SELECT
  _TABLE_SUFFIX AS day,
  privacy_info.analytics_storage AS analytics_storage,
  COUNT(*) AS events,
  COUNTIF(event_name = 'session_start') AS session_starts,
  COUNT(DISTINCT user_pseudo_id) AS distinct_pseudo_ids,
  COUNTIF(user_pseudo_id IS NULL) AS events_without_pseudo_id,
  COUNTIF((SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
           FROM UNNEST(event_params) WHERE key = 'ga_session_id') IS NULL) AS events_without_session_id,
  COUNTIF(collected_traffic_source.manual_source IS NOT NULL) AS events_with_manual_source
FROM `example-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND platform = 'WEB'
GROUP BY day, analytics_storage
ORDER BY day, events DESC;
```

Reading it:

- Google's reporting surfaces page says each cookieless session in the export has a different `user_pseudo_id`, so expect pseudo IDs on `No` rows. Whether they carry a session ID is what this query measures.
- Denied share per day: `No` events divided by all events that day. Compute it for the baseline window too.
- `No` rows mostly without a session ID: denied events cannot form sessions in Q1, so Q4b says nothing about the `No` state. Decide the `No` half of cause D from the denied share instead. A rise after the first-change date that no CMP, banner or audience change explains points to denied events sent after a granted choice. Confirm with the sequence test in Tag Assistant (grant, navigate to a second page, confirm no hits with denied `analytics_storage` follow the grant) before calling anything consented-away.
- `No` rows mostly with a pseudo ID and a session ID: Q4b covers the `No` state.

Q4b, session level (reuse the `ev` and `sessions` CTEs from Q1, in the same statement):

```sql
SELECT
  first_consent_state,
  last_consent_state,
  mixed_consent,
  COUNT(*) AS sessions,
  COUNTIF(NOT has_session_start) AS sessions_without_session_start,
  ROUND(COUNTIF(NOT has_session_start) / COUNT(*), 3) AS share_without_session_start
FROM sessions
GROUP BY first_consent_state, last_consent_state, mixed_consent
ORDER BY sessions DESC;
```

Reading it, against the same query for a window before the first-change date. A state has a gap when its `share_without_session_start` is more than double the baseline window's share for that state.

- Gap in `Yes` sessions: the signature of `default` called where `update` belongs. The initial `session_start` is lost and every later event is granted. Check the CMP's consent call order in Tag Assistant ([cause-tree.md](cause-tree.md#d-consent-sequence-errors-consented-away-data-and-modelling)).
- Gap in `No` sessions, or in mixed sessions whose last state is `No` (only readable when Q4a shows denied rows carry session IDs): denied events sent after a granted choice, or consented-away data. Test the sequence before calling it consented-away.
- No gap in any state Q4b can see: consent is not the explanation for those states. If Q4a showed the `No` state is invisible here, the denied share and the sequence test decide it. Then go back to cause C.

## Q5. Referrals: session sources and payment returns

Answers cause F. A return from a payment page inside the session timeout normally starts no new session ([cause-tree.md](cause-tree.md#f-cross-domain-handoffs-and-payment-referrals)), so `session_start` rows alone miss most of the leak. Run both parts.

Q5a, sessions whose source is a referral:

```sql
SELECT
  LOWER(session_traffic_source_last_click.cross_channel_campaign.source) AS session_source,
  NET.REG_DOMAIN(LOWER(session_traffic_source_last_click.cross_channel_campaign.source)) AS source_domain,
  NET.REG_DOMAIN(device.web_info.hostname) AS landing_domain,
  COUNT(*) AS session_starts
FROM `example-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND event_name = 'session_start'
  AND session_traffic_source_last_click.cross_channel_campaign.medium = 'referral'
GROUP BY session_source, source_domain, landing_domain
ORDER BY session_starts DESC
LIMIT 100;
```

If `session_traffic_source_last_click` is empty in your export, use `collected_traffic_source.manual_source` and `manual_medium` on `session_start` instead, and check what referral rows look like there first.

Mark each source:

- A payment or authentication provider: unwanted referral candidate.
- A domain you own whose `source_domain` differs from `landing_domain`: cross-domain candidate; confirm the identity break with a journey walk.
- `source_domain` equal to `landing_domain`: GA4 ignores same-domain referrers, so this should not appear. If it does, check that both hosts share the Google tag configuration and cookie domain.
- An AI assistant: cause J. Anything else: a genuine referrer.

Own-domain `page_referrer` values on `session_start` are not evidence for F. They are sessions that restarted after the timeout or in a new tab.

Q5b, every event referred by a payment or authentication host:

```sql
SELECT
  NET.HOST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer')) AS referrer_host,
  event_name,
  LOWER(collected_traffic_source.manual_source) AS collected_source,
  EXISTS (SELECT 1 FROM UNNEST(event_params) WHERE key = 'ignore_referrer') AS has_ignore_referrer,
  COUNT(*) AS events
FROM `example-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND platform = 'WEB'
  AND (
    NET.REG_DOMAIN((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer'))
      IN ('example.net')  -- payment and authentication domains from stack.md
    OR NET.REG_DOMAIN(LOWER(collected_traffic_source.manual_source)) IN ('example.net')
  )
GROUP BY referrer_host, event_name, collected_source, has_ignore_referrer
ORDER BY events DESC;
```

Reading it:

- Return `page_view` rows without `ignore_referrer`: the processor can take event-scoped credit for the key event and become the user's last non-direct source. Confirm in an exploration with the key event broken down by event-scoped Source / medium.
- After the processor is listed as an unwanted referral, its return events should carry `ignore_referrer`. If your export never shows the key, check the request in Tag Assistant instead and verify at run time whether the parameter is exported.
- To find bank and card authentication hosts you have not listed, rerun the query with the domain filter removed and `event_name = 'page_view'` on the payment-return and confirmation paths, grouped by `referrer_host`. Cause F's decision rule uses that count.

## Q6. Sessions without a page view, by cluster

Answers cause H. Reuse the `ev` and `sessions` CTEs from Q1, in the same statement:

```sql
SELECT
  first_event,
  first_hostname,
  session_number > 1 AS returning,
  COUNT(*) AS sessions,
  SUM(key_events) AS key_events
FROM sessions
WHERE NOT has_page_view
GROUP BY first_event, first_hostname, returning
ORDER BY sessions DESC;
```

Returning sessions whose first event is an interaction (scroll, click, `user_engagement`) are candidates for timed-out tabs. Report a cluster as a defect when it holds more than 1% of the affected key events; otherwise record it as expected behaviour with its count.

## Q7. Raw source and medium pairs that arrived

Answers causes A, I and J. Values are lowercased because channel rules ignore case.

```sql
WITH s AS (
  SELECT
    user_pseudo_id,
    (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
     FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    LOWER(collected_traffic_source.manual_source) AS source,
    LOWER(collected_traffic_source.manual_medium) AS medium,
    LOWER(collected_traffic_source.manual_campaign_name) AS campaign,
    IFNULL(REGEXP_CONTAINS((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
                           r'[?&]utm_'), FALSE) AS utm_in_url
  FROM `example-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
    AND event_name IN ('session_start', 'page_view')
)
SELECT
  source,
  medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '.', CAST(session_id AS STRING))) AS sessions,
  COUNTIF(utm_in_url) AS rows_with_utm_in_url,
  COUNTIF(campaign IS NULL) AS rows_without_campaign,
  COUNTIF(REGEXP_CONTAINS(campaign, r'cross-network')) AS rows_cross_network_campaign,
  COUNTIF(REGEXP_CONTAINS(campaign, r'^(.*(([^a-df-z]|^)shop|shopping).*)$')) AS rows_shop_campaign
FROM s
WHERE source IS NOT NULL OR medium IS NOT NULL OR utm_in_url
GROUP BY source, medium
ORDER BY sessions DESC;
```

Feed the output to the pre-check in [channel-rules.md](channel-rules.md#pairs-that-commonly-land-in-unassigned). `manual_source` and `manual_medium` also hold values parsed from the referrer, so only pairs with `rows_with_utm_in_url` above zero are proven UTM pairs; a referrer row has no campaign by nature.

- A UTM pair from the registry with `rows_without_campaign` above zero: an incomplete link template (cause I).
- A NULL medium with UTMs in the URL, or a NULL source with a medium: a partial UTM set (cause A).
- NULL source and NULL medium with UTMs in the URL: the parameters arrived but did not parse, usually a misspelt name such as `utm_meduim` or `utm-source` (cause A).

## Q8. Find a test session

Answers step 6 of the procedure. Fill in the client ID and the UTC times recorded in the test matrix ([confirmation-tests.md](confirmation-tests.md)). Use the two-key session ID if Q0 showed `session_id`.

```sql
SELECT
  TIMESTAMP_MICROS(event_timestamp) AS event_time_utc,
  event_name,
  (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64))
   FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS page_referrer,
  EXISTS (SELECT 1 FROM UNNEST(event_params) WHERE key = 'ignore_referrer') AS has_ignore_referrer,
  privacy_info.analytics_storage AS analytics_storage,
  collected_traffic_source.manual_source AS collected_source,
  collected_traffic_source.manual_medium AS collected_medium,
  collected_traffic_source.manual_campaign_name AS collected_campaign,
  session_traffic_source_last_click.cross_channel_campaign.source AS session_source,
  session_traffic_source_last_click.cross_channel_campaign.medium AS session_medium,
  ecommerce.transaction_id AS transaction_id
FROM `example-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261006' AND '20261007'
  AND user_pseudo_id = '<client_id from the test matrix>'
  AND event_timestamp BETWEEN UNIX_MICROS(TIMESTAMP('2026-10-06 09:00:00+00'))
                          AND UNIX_MICROS(TIMESTAMP('2026-10-06 10:00:00+00'))
ORDER BY event_timestamp, batch_event_index;
```

For a denied run in advanced consent mode there is no client ID to match. Replace the `user_pseudo_id` condition with `privacy_info.analytics_storage = 'No'` and a `page_location` condition on the test landing URL, keep the time window to a few minutes around the recorded run, then follow the `user_pseudo_id` you find.

`event_timestamp` is the time GA4 received the event, in microseconds UTC. Widen the window by a few minutes if the run times were noted by hand.

## Q9. Web events from unexpected or empty hostnames

Answers cause K. Lists web events whose hostname is empty or not one of yours, by day and city.

```sql
SELECT
  _TABLE_SUFFIX AS day,
  IFNULL(device.web_info.hostname, '(empty)') AS hostname,
  geo.city AS city,
  event_name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NOT NULL AS has_page_location,
  COUNT(*) AS events,
  COUNT(DISTINCT user_pseudo_id) AS distinct_pseudo_ids
FROM `example-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND platform = 'WEB'
  AND (device.web_info.hostname IS NULL
       OR device.web_info.hostname NOT IN ('www.example.com'))  -- every hostname in stack.md
GROUP BY day, hostname, city, event_name, has_page_location
ORDER BY events DESC
LIMIT 200;
```

Reading it:

- An empty hostname with no page location and few pseudo IDs on a key event is usually a backend sender (cause E), not a bot.
- An empty hostname on `page_view` or `session_start`, or a hostname you do not own, concentrated in one city: events sent with your measurement ID from outside your site. Record the cluster with its dates (cause K) and draft a hostname data filter for the property owner.
- A hostname you own that is missing from `stack.md` (a staging or regional site): update `stack.md` first, then rerun.