Measure Your Real Consent Rate in the GA4 BigQuery Export

Your banner proudly reports a 72 % consent rate. Your GA4 BigQuery export tells a completely different story. And the export is the one that is right. Everyone talks about Consent Mode as a checkbox in GTM, almost nobody explains how to read consent in the raw data, where there is no marketing left, just rows and fields. That is exactly what I am going to show you: where consent hides in the GA4 BigQuery export, and how, in three copy-paste SQL queries, to compute your real consent rate by page, device and country, quantify the traffic you never see, and catch a broken banner before your conversions tank.

The timing is not accidental. Since 15 June 2026, ad_storage alone governs what flows to Google Ads, Google has been using the IP address since 3 August, and the Digital Omnibus six-month rule locks refusals in for longer. In other words, every point of consent you lose now costs more than it used to, and the first thing to do before deciding anything is to quantify what you actually lose. Not what your banner tool is willing to tell you.

What your CMP measures, and what GA4 actually sees

Your consent platform (CMP) counts its own clicks: how many visitors accepted, declined or ignored the banner. That is a useful number, but it is a banner-side number. It says nothing about what truly reached GA4 once Consent Mode did its work, the tags that fired (or not), the pings that were sent (or not). In between sit JavaScript, trigger rules, ad blockers, and visitors who leave before clicking anything. The CMP number and the number GA4 sees are not the same number, and they often diverge by ten to twenty points.

Why trust BigQuery over the GA4 interface? Because the interface and the API apply modeling: Google statistically reconstructs part of the behavior of non-consenting visitors. The BigQuery export models nothing. It holds the raw events, exactly as they arrived. It is blunt, it is incomplete by design, but it is ground truth. When you want to know who actually consented, BigQuery is the one that settles it.

Where consent hides: the privacy_info field

In the GA4 BigQuery export, consent lives in a RECORD field called privacy_info, present on every event row. It holds three sub-fields:

First trap to avoid: these are strings, not booleans. You write where privacy_info.analytics_storage = 'Yes', never = true. The value "Yes" means consent granted, "No" means consent denied, and null means no Consent Mode is implemented or the state is unknown. If all you see is null, your Consent Mode is not wired into the signal, and the rest of this article only helps once that is fixed.

Let me be honest about uses_transient_token: Google does not give it a clear definition in its documentation. It relates to the ephemeral tokens used for modeling in advanced mode. In practice I do not use it to drive decisions: I rely on analytics_storage and ads_storage, which are more than enough for everything that follows.

Basic or Advanced: the setting that changes everything

Here is the point most articles skip, and it is the one that decides whether you can measure anything at all. Export behavior depends entirely on which Consent Mode you run.

In Basic mode, tags are blocked until consent is granted. As a result, a visitor who refuses sends nothing at all, and no row exists for them in BigQuery. Your export only contains consenters, so analytics_storage is always "Yes". The direct and counterintuitive consequence: in Basic mode you cannot compute your refusal rate from BigQuery, because the refusals are simply absent from the data.

In Advanced mode it is the opposite. Tags still load and send a cookieless ping even without consent. Those rows exist in the export, with analytics_storage = 'No', but their identifiers are stripped: user_pseudo_id and ga_session_id are null. So you can count them as events, but not as users or sessions, since there is no identifier left to group them by.

Hence a free thirty-second diagnostic: if your export holds no analytics_storage = 'No' rows, you are either in Basic mode or running no advanced Consent Mode, and you have a blind spot on every refusal. If that foundation is not solid on your side, sort it out first with Consent Mode v2 in GA4, then come back here. And if the BigQuery export is not plumbed into your property yet, it all starts with the GA4 BigQuery export guide.

Query 1: your consent rate by day, device and country

Let us get concrete. This query computes the analytics consent rate based on page_view events, to limit bias (a consenter generates far more events than a refusal, so counting every event inflates the rate artificially).

SELECT
  PARSE_DATE('%Y%m%d', event_date)                 AS day,
  device.category                                  AS device,
  geo.country                                      AS country,
  COUNTIF(privacy_info.analytics_storage = 'Yes')  AS consented_views,
  COUNTIF(privacy_info.analytics_storage = 'No')   AS refused_views,
  ROUND(
    SAFE_DIVIDE(
      COUNTIF(privacy_info.analytics_storage = 'Yes'),
      COUNTIF(privacy_info.analytics_storage IN ('Yes', 'No'))
    ) * 100, 1
  )                                                AS consent_rate_pct
FROM `your-project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261010'
  AND event_name = 'page_view'
GROUP BY day, device, country
ORDER BY day DESC, consented_views DESC;

Two details matter. The _TABLE_SUFFIX filter is not cosmetic: without it, BigQuery scans every daily table since the beginning, and the bill climbs fast. Always bound the date range. And SAFE_DIVIDE avoids a division by zero on a day with no data, instead of blowing up the query.

Read the output by segment, never as a global average. A 70 % rate on desktop and a 45 % rate on mobile is not a 58 % rate, it is two different problems. The gaps by country and device are often where the real issues hide: an unreadable banner on small screens, a missing translation, an unlucky button position.

Query 2: the invisible volume

How much traffic actually slips past you? This query counts events by consent state and measures the share with no identifier, that is, the cookieless pings of advanced mode.

SELECT
  privacy_info.analytics_storage                   AS consent,
  COUNT(*)                                          AS events,
  COUNTIF(user_pseudo_id IS NULL)                   AS no_user_id,
  COUNTIF(
    (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'ga_session_id') IS NULL
  )                                                 AS no_session_id
FROM `your-project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261010'
GROUP BY consent
ORDER BY events DESC;

The consent = 'No' row is your ghost population: real visitors, who interacted, but whom you can neither identify, nor follow, nor attribute. In advanced mode, it is also the raw material Google uses to model your conversions in the interface. The bigger that row, the wider the gap between your GA4 interface and your BigQuery, and the more your decisions based on the GA4 report alone rest on reconstructed data.

Query 3: the broken-banner alert

The painful scenario: a CMP deploy that breaks the integration, a tag that stops pushing the consent state, a layout change that buries the accept button. Your consent rate collapses overnight, and you find out three weeks later once conversions have already dropped. This query compares each day to its trailing 7-day average to raise the alarm early.

WITH daily AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS day,
    SAFE_DIVIDE(
      COUNTIF(privacy_info.analytics_storage = 'Yes'),
      COUNTIF(privacy_info.analytics_storage IN ('Yes', 'No'))
    ) AS rate
  FROM `your-project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260911' AND '20261010'
    AND event_name = 'page_view'
  GROUP BY day
)
SELECT
  day,
  ROUND(rate * 100, 1) AS rate_pct,
  ROUND(AVG(rate) OVER (
    ORDER BY day ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
  ) * 100, 1) AS avg_7d_pct,
  ROUND((rate - AVG(rate) OVER (
    ORDER BY day ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
  )) * 100, 1) AS delta_points
FROM daily
ORDER BY day DESC;

Read the delta_points column. A swing of -2 or -3 points is normal noise. A swing of -15 points from one day to the next is a broken banner until proven otherwise, and your alert trigger. Wire this query into a scheduled query and a notification, and you go from late discovery to detection within twenty-four hours.

Interpret, and above all decide

Numbers are useless if you stop there. Three situations, three decisions.

If your consent rate is healthy and stable, your job is to protect the consented share: check that the signal flows cleanly, watch query 3, and optimize match quality server-side. If your rate is low and most of your traffic tips into 'No', the real question is no longer technical but strategic: is your purpose truly advertising, or just audience measurement? In the second case, consent-exempt measurement can hand you back all the lost volume. And if you lose so much signal that GA4 barely teaches you anything anymore, it is time to look elsewhere: other measurement tools simply do not have this banner problem.

Whichever direction you take, you now start from a real number rather than an impression. To go further with the export, I gathered 10 BigQuery queries for GA4 that complement these ones.

FAQ

Why is my user_pseudo_id null on some rows? Because you run advanced Consent Mode and those rows are cookieless pings from non-consenting visitors. GA4 collects the event but strips the identifiers. It is normal, and it is exactly what lets you count them as volume without tracking them as users.

Can I filter out non-consenters with where privacy_info.analytics_storage = 'Yes'? Yes, but know that you introduce bias: consent varies by device, country and page, so your consented sample is not representative of the whole. That is acceptable for behavioral analysis, not for volume counting.

Does GA4 modeling show up in BigQuery? No. Modeling applies in the interface and the API, never in the BigQuery export. That is why your BigQuery totals are often lower than the interface, and also why BigQuery is the most reliable source for measuring raw consent.

My export has no 'No' rows, is that a bug? No, it is a symptom. You are very likely in Basic Consent Mode (where refusals send nothing) or running no Consent Mode. Either way, your refusals are invisible in BigQuery, and you cannot measure your refusal rate until you move to advanced mode.

How do I keep these queries cheap? Always bound _TABLE_SUFFIX to the useful date range. That is the number-one lever, because BigQuery bills on data scanned, and an unbounded query reads your entire history.

My take as a practitioner

If I had to sum it up: stop steering your consent from your CMP dashboard. That figure measures clicks on a banner, not what your analytics sees. The only truth that matters for your decisions is the one in the raw export, because that is what determines what Google can model, what your conversions are worth, and what you actually lose. Move to advanced Consent Mode if you have not already, if only to make your refusals visible and measurable. Run query 1 once to see where you stand, schedule query 3 so you never again discover a broken banner three weeks late, and use query 2 to put a number, in euros of lost signal, on the trade-off waiting for you. Consent has become a data problem before it is a compliance one. So handle it with data.