Reading the Per-Event Consent State From the GA4 BigQuery Export

Consent shows up in GA4 reports as an aggregate, if at all. In the BigQuery export it shows up per event, in a nested record written alongside every row, holding the state that applied at the moment that particular event was recorded.

That is a considerably better object to work with, and it comes with one trap that produces a wrong number rather than an error.

Where it sits

The record is privacy_info, and it holds the storage states as strings rather than booleans. Being nested, it needs dotted access; being strings, it needs quoted comparison; and being nullable, it needs a decision about what an absent value means before any rate is computed.

SELECT
  event_date,
  privacy_info.analytics_storage AS analytics,
  privacy_info.ads_storage       AS ads,
  COUNT(*)                       AS ereignisse
FROM `projekt.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'
GROUP BY 1, 2, 3
ORDER BY 1, 4 DESC

-- run this before writing any filter. The point is to see
-- how many rows fall into the third bucket, because that
-- number decides whether the trap below matters at all

Running the grouping first rather than the filter is the whole discipline here. It takes one query, it costs almost nothing on a single month, and it answers the question that every subsequent query depends on.

The trap

Two filters that read as opposites are not opposites. analytics_storage = 'No' selects events that carry an explicit refusal. analytics_storage != 'Yes' selects refusals plus everything with no recorded state, because in SQL a comparison against null yields null rather than true – so the first filter silently drops the null rows and the second keeps them.

A consent rate built on the first will be higher than one built on the second, on the same data, for the same day. Neither is obviously wrong from the outside; both return a plausible percentage. Which one is correct depends on what the nulls actually are, and that is a question about the implementation rather than about SQL.

Nulls generally mean no consent signal reached the tag when the event fired – a hit before the banner resolved, a page where the consent platform failed to load, a server-side event constructed without the state being passed through. Those are three different situations with three different meanings, and lumping them in with explicit refusals overstates refusal.

What this makes possible

Because the state is per event rather than per property, it can be crossed with anything else on the row.

by page          which pages get refused most often
                 usually the ones where the banner competes
                 with content for the same screen area

by device        mobile and desktop rates differ enough that
                 an overall figure describes neither

by source        traffic arriving from a link inside an app
                 behaves differently from a direct visit

by hour          a deployment that breaks the banner shows up
                 as a step change at a particular hour, which
                 a daily figure smooths away entirely

The per-page cut is the one that repays the effort fastest, because it turns a number nobody can act on into a list of specific pages.

Two things worth knowing about the row

A single visit can contain events in more than one state. Someone who accepts a banner halfway through a session leaves a trail of denied events followed by granted ones under the same pseudo identifier, which is correct and which quietly breaks any query assuming one state per user.

And the export schema grows. Fields have been added to the events table before and will be again, which is the argument against SELECT * in anything downstream: a new column arriving unannounced is harmless to a query that names its columns and is a broken load job for one that does not.