English
Deutsch
PolskiReading the Per-Event Consent State From the GA4 BigQuery Export
Contents
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 allRunning 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 entirelyThe 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.
Questions and answers
Why does an export sometimes not contain a single refusal?
Usually because Consent Mode runs in its basic form. There the tag only loads after consent; anyone who refuses sends nothing, and the export holds only events with consent granted or with no recorded state. A consent rate cannot be built from such an export; it has to come from the records of the consent platform instead.
How can the nulls be assigned to the three situations the article names?
Through the other fields on the same row, because each of the three situations leaves its own pattern:
- Hits before the banner resolved cluster at the start of a session. With the events of each session ordered by event_timestamp, the nulls come first, followed by events with a recorded state.
- A page where the consent platform failed to load shows up in the cut by page or by hour: nulls concentrated on particular pages or page templates, or a step change from the time of a deployment.
- Server-side events built without the state being passed through can be recognised by their event name if only the server sends them; they are then null throughout, regardless of page and time of day.
For the query itself it helps to keep the third bucket visible rather than leaving it to the comparison: COALESCE(privacy_info.analytics_storage, 'empty') turns the null into a value of its own that appears explicitly in every grouping and every filter. The decision about what “empty” means is then made once, in one place, rather than silently in every WHERE clause.
Why does the query restrict by _TABLE_SUFFIX rather than by event_date alone?
For two reasons. First, BigQuery bills on-demand queries by the data they read, and only a filter on _TABLE_SUFFIX determines which daily tables are read at all; a filter on event_date alone first reads every table the wildcard matches and discards rows only afterwards.
Second, events_* also matches the streaming export tables, events_intraday_…, whose suffix begins with intraday_. A range of date values such as ‘20260801’ to ‘20260831’ excludes them. If a period reaches up to the current day, a filter on event_date alone reads their provisional rows as well.
Related articles
