{"id":12890,"date":"2026-09-05T06:30:00","date_gmt":"2026-09-05T04:30:00","guid":{"rendered":"https:\/\/www.lukaswojcik.com\/blog\/?p=12890"},"modified":"2026-10-24T04:15:29","modified_gmt":"2026-10-24T02:15:29","slug":"reading-the-per-event-consent-state-from-the-ga4-bigquery-export","status":"publish","type":"post","link":"https:\/\/www.lukaswojcik.com\/blog\/en\/digital-analytics\/reading-the-per-event-consent-state-from-the-ga4-bigquery-export\/","title":{"rendered":"Reading the Per-Event Consent State From the GA4 BigQuery Export"},"content":{"rendered":"

In the GA4 BigQuery export, privacy_info<\/code> contains storage-state information for an exported event. This supports technical checks of consent signals. It does not replace CMP records or establish a particular choice made on a banner.<\/p>\n

The common SQL mistake: analytics_storage != 'Yes'<\/code> excludes NULL values. A comparison with NULL does not produce true; WHERE<\/code> retains only true conditions.<\/p>\n

1. Inspect the raw values first<\/h2>\n

analytics_storage<\/code> and ads_storage<\/code> are strings. Google documents Yes<\/code>, No<\/code> and Unset<\/code>. An actual SQL NULL remains separately identifiable as a missing value. 'Unset'<\/code> and NULL are not interchangeable in comparisons. Dot notation accesses each field within the record.<\/p>\n

Replace the project, dataset and example dates before execution. Grouping first exposes all existing combinations without classifying unknown values as refusals.<\/p>\n

SELECT\n  event_date,\n  privacy_info.analytics_storage AS analytics_storage,\n  privacy_info.ads_storage AS ads_storage,\n  COUNT(*) AS event_count\nFROM `project_id.analytics_XXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'\nGROUP BY 1, 2, 3\nORDER BY event_date, event_count DESC;\n<\/code><\/pre>\n
\"Distinguish
Distinguish Yes, No, Unset and SQL NULL. Only true comparisons pass a WHERE filter.<\/figcaption><\/figure>\n
2. Demonstrate the NULL trap with four values<\/h2>\n
This small GoogleSQL example needs no Analytics data. It distinguishes explicit No<\/code>, not Yes<\/code>, and not Yes<\/code> including NULL.<\/p>\n
WITH sample AS (\n  SELECT state\n  FROM UNNEST(ARRAY<STRING>['Yes', 'No', 'Unset', NULL]) AS state\n)\nSELECT state,\n  state = 'No' AS equals_no,\n  state != 'Yes' AS not_yes,\n  state IS DISTINCT FROM 'Yes' AS not_yes_including_null\nFROM sample;\n<\/code><\/pre>\n
For No<\/code> and Unset<\/code>, != 'Yes'<\/code> is true. For NULL, the result is NULL, so a matching WHERE<\/code> filter removes the row. IS DISTINCT FROM 'Yes'<\/code> includes NULL. Its technical meaning is \u201cnot Yes, including missing,\u201d not \u201cexplicit refusal.\u201d<\/p>\n
Even No<\/code> does not establish a click on \u201cReject\u201d: a denied storage default can apply before a banner decision. The field alone cannot explain the cause of unknown values.<\/p>\n
3. Make the denominator explicit<\/h2>\n
The next query separates known, unset, missing and unexpected values. SAFE_DIVIDE<\/code> returns NULL for a zero denominator instead of failing. The shares range from 0 to 1; percentage display needs suitable formatting.<\/p>\n
WITH source AS (\n  SELECT privacy_info.analytics_storage AS state\n  FROM `project_id.analytics_XXXXXX.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'\n)\nSELECT\n  COUNT(*) AS exported_events,\n  COUNTIF(state = 'Yes') AS yes_events,\n  COUNTIF(state = 'No') AS no_events,\n  COUNTIF(state = 'Unset') AS unset_events,\n  COUNTIF(state IS NULL) AS null_events,\n  COUNTIF(state NOT IN ('Yes', 'No', 'Unset')) AS other_events,\n  SAFE_DIVIDE(COUNTIF(state = 'Yes'), COUNT(*)) AS yes_share_all_events,\n  SAFE_DIVIDE(\n    COUNTIF(state = 'Yes'),\n    COUNTIF(state IN ('Yes', 'No'))\n  ) AS yes_share_known_events\nFROM source;\n<\/code><\/pre>\n
With one event each for Yes<\/code>, No<\/code>, Unset<\/code> and NULL, the Yes share of all events is 25%. Among the two known states, it is 50%. Both calculations can be correct while answering different questions. Unknown-value shares must remain visible alongside them.<\/p>\n
Both results are event-weighted<\/strong>: a visit producing many events contributes more than a visit producing few. Under basic consent mode, visits without permission can be absent from the export entirely. A banner-decision rate therefore needs a suitably defined CMP dataset, including period, counting unit and treatment of repeated decisions.<\/p>\n
4. Investigate patterns<\/h2>\n