![\"An](\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/diagrams\/zustandsfeld-en.png\")
Where it sits<\/h2>\n
The record is 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.<\/p>\n Two filters that read as opposites are not opposites. 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.<\/p>\n 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.<\/p>\n Because the state is per event rather than per property, it can be crossed with anything else on the row.<\/p>\n 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.<\/p>\n 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.<\/p>\n And the export schema grows. Fields have been added to the events table before and will be again, which is the argument against The export records the consent state that applied when each event was written, in a nested record beside the event. The field is a string rather than a boolean, it can be null, and a consent rate computed without allowing for that will be wrong in a direction that flatters.<\/p>\n","protected":false},"author":1,"featured_media":12935,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[91333,91041,91258],"class_list":["post-12890","post","type-post","status-publish","format-standard","hentry","category-digital-analytics","tag-bigquery","tag-consent-mode-v2","tag-google-analytics-4"],"yoast_head":"\nprivacy_info<\/code>, 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.<\/p>\nSELECT\n event_date,\n privacy_info.analytics_storage AS analytics,\n privacy_info.ads_storage AS ads,\n COUNT(*) AS ereignisse\nFROM `projekt.analytics_XXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'\nGROUP BY 1, 2, 3\nORDER BY 1, 4 DESC\n\n-- run this before writing any filter. The point is to see\n-- how many rows fall into the third bucket, because that\n-- number decides whether the trap below matters at all<\/code><\/pre>\nThe trap<\/h2>\n
analytics_storage = 'No'<\/code> selects events that carry an explicit refusal. analytics_storage != 'Yes'<\/code> 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.<\/p>\nWhat this makes possible<\/h2>\n
by page which pages get refused most often\n usually the ones where the banner competes\n with content for the same screen area\n\nby device mobile and desktop rates differ enough that\n an overall figure describes neither\n\nby source traffic arriving from a link inside an app\n behaves differently from a direct visit\n\nby hour a deployment that breaks the banner shows up\n as a step change at a particular hour, which\n a daily figure smooths away entirely<\/code><\/pre>\nTwo things worth knowing about the row<\/h2>\n
SELECT *<\/code> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"