Follow me on LinkedIn - AI, GA4, BigQuery

Google Advanced Consent Mode can corrupt your GA4 BigQuery Export if you don’t deal with cookieless pings.

When you use Google Advanced Consent Mode, the data dfiscrepancy between your GA4 property and GA4 BigQuery export increases considerably, and you start collecting junk data in BigQuery.

When Advanced Consent Mode is implemented, there is a notable difference between what you see in your GA4 reports and what is available in your BigQuery export data tables.

For example, you could see far more conversions and purchase events in BigQuery than in your GA4 property.

This happens because,

By default, the GA4 BigQuery export does not fully honour the Google Advanced Consent Mode and continues to import event data (some but not all) from your GA4 property even if users decline consent.

GA4 BigQuery export includes the cookieless pings collected by GA4 when user consent is not given.

These “cookieless pings” in GA4 from unconsented users contain limited, anonymized event data. They generally include:

  • Event names and details (page views, clicks, etc.).
  • Limited device and browser information.
  • IP-based geolocation (anonymized).
  • Consent status.
  • Events without personal identifiers (such as cookies, device IDs, user IDs, client IDs, advertising IDs, etc).
  • Data related to anonymized sessions (Session start and end, Session metadata etc). But without consistent identifiers, it’s difficult to link events within a session.

All of this data is pretty much useless to analyze.

For example,

1) The ‘user_pseudo_id’ for unconsented users in GA4 “cookieless pings” is either missing or different for each session, making it impossible to track the same user across multiple sessions or to link events to a single user over time.

Without personal identifiers, tracking individual user journeys or behaviors across sessions is almost impossible.

2) Without identifying users, you can’t reliably attribute conversions, purchases, or events to specific campaigns, ads, or referral sources.

3) Without consistent identifiers for unconsented users, the data becomes fragmented and unreliable at the user level. You lose the ability to track users across different devices and platforms. Each session or even each event might appear as a unique user, severely distorting user-based metrics.

4) Without identifiers, it isn’t easy to deduplicate events, leading to potential over-reporting of user actions, making the data less reliable.

When the data is not reliable either at the user, session or event level, it is pretty much junk data.

There might be a few cases where some high-level or aggregated insights can be squeezed out.

But by and large, it is junk data.

So what can you do?

If you use Google Advanced Consent Mode, consent signal values (yes, no, null) – which reflect the user’s consent status – are often included in the BigQuery export.

#1 You can use ‘privacy_info.analytics_storage’ and ‘privacy_info.ads_storage’ fields to filter out events based on user consent for analytics and ads tracking.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

SELECT

  event_date,

  event_name,

  privacy_info.analytics_storage,

  privacy_info.ads_storage,

  COUNT(*) AS event_count

FROM

  `<Use your table id_*>`

WHERE

  _TABLE_SUFFIX BETWEEN

    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))

    AND

    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

  AND (privacy_info.analytics_storage = 'Yes' OR privacy_info.ads_storage = 'Yes')

GROUP BY

  1, 2, 3, 4

ORDER BY

  event_date DESC, event_count DESC

Note: The values of both the ‘privacy_info.analytics_storage’ and ‘privacy_info.ads_storage’ fields are case-sensitive. So ‘yes’ is different from ‘Yes’, ‘no’ is different from ‘No’ and ‘null’ is different from ‘NULL’.

#2 You can use the consent signals to filter out data from users who haven’t provided consent, which is likely the source of anonymized or incomplete data.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

SELECT

  event_date,

  event_name,

  privacy_info.analytics_storage,

  privacy_info.ads_storage,

  COUNT(*) AS event_count

FROM

  `<Use your table id_*>`

WHERE

  _TABLE_SUFFIX BETWEEN

    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))

    AND

    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

  AND (privacy_info.analytics_storage IS null OR privacy_info.analytics_storage = 'No')

  AND (privacy_info.ads_storage IS null OR privacy_info.ads_storage = 'No')

GROUP BY

  1, 2, 3, 4

ORDER BY

  event_date DESC, event_count DESC

#3 Since the junk data you see likely comes from sessions or events without user identifiers, you can filter out rows that lack these identifiers.

#4 You can create custom views or tables in BigQuery that exclude unconsented data by default.

This way, you always work with consented data in your analysis.

The SQL below creates a new view containing only consented events data:

1

2

3

4

5

6

7

8

9

10

CREATE OR REPLACE VIEW `dbrt-ga4.analytics_207472454.consented_events` AS

SELECT

  *

FROM

  `<Use your table id_*>`

WHERE

  (_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))

  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE()))

  AND (privacy_info.analytics_storage = 'Yes'

  OR privacy_info.ads_storage = 'Yes')

consented events ga4

Following is the SQL example code to create a materialized table to store the consent data

1

2

3

4

5

6

7

8

9

10

CREATE OR REPLACE TABLE `dbrt-ga4.analytics_207472454.consented_events_table` AS

SELECT

  *

FROM

  `dbrt-ga4.analytics_207472454.events_*`

WHERE

  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))

  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

  AND (privacy_info.analytics_storage = 'Yes'

  OR privacy_info.ads_storage = 'Yes')

The Google Advanced Consent Mode is not fully GDPR compliant because BigQuery collects event data (some but not all) from your GA4 property even if users decline consent.

There are three categories of data under GDPR that everyone should be aware of:

#1 Consented data – the user gave you permission to process the data.

#2 Unconsented data – the user did not give you permission to process the data. Even if the user disables the cookie consent banner on your website or does not participate in providing consent, it is still unconsented data. Lack of consent status means no consent.

#3 Data you are allowed to process under legitimate interest – no consent required.

There should not be any overlap between these three categories as far as compliance is concerned. This is very important piece of information.

For example, If you are asking for consent, then it means without consent, you are not allowed to process the data under legitimate interest.

So if a user denies consent, you then can not continue to process unconsented data under legitimate interest.

If an organization seeks consent for data processing, that that action in itself implies that consent is the appropriate legal basis.

There needs to be a clear distinction between consented data, unconsented data and the data you are allowed to process under legitimate interest.

Anonymized and encrypted unconsented data is still unconsented data.

Even if the data has been anonymized and/or encrypted to protect user privacy, the core issue remains: users did not provide explicit consent for the collection, use, or analysis of their data.

It is a clear-cut violation of GDPR. Play with unconsented data at your own risk.

Don’t use Google Consent Advanced Mode if you are a business operating outside the European Economic Area (EEA) and/or don’t target users in the EEA. This will help you minimize data collection issues in GA4 and BigQuery.

Google Consent Mode (both basic and advanced) are not mandatory for businesses operating outside the EEA and /or not targeting users in the EEA.

So, if you are an American business serving only USA-based customers, you don’t have to set up a consent mode to comply with GDPR or respect user privacy.

All you need is a cookie consent banner that tracks or does not track based on user consent choice.

Google entice users with modelled data if they set up consent mode. However, most GA4 properties never qualify for modelled data.

You don’t need consent mode if you know that your property will not qualify for modelled data.

So, what’s the downside of setting up Consent Mode?

Your GA4 property becomes more prone to various hard to fix data collection issues from (not set) to unassigned traffic.

When you use Google Advanced Consent Mode, the data discrepancy between GA4 and GA4 BigQuery export increases considerably, and you start collecting “junk data” in BigQuery.

So what happens when the consent mode is not implemented and a user denies consent?

Without Consent Mode, your website’s cookie banner will still block Google tags from firing if a user denies consent. This means no data is sent to Google platforms (GA4, Google Ads) for that user.

You are essentially in an “all or nothing” situation regarding data collection.

But when you set up consent mode, you are half in and half out regarding data collection.

You are collecting full data from some users, partial or modelled data from others, and no data from some, depending on their consent choices and the specific implementation of Consent Mode, which skews your analytics data for good.

With Consent Mode, the data collected is highly variable, inconsistent and unpredictable. You also see a lot more data discrepancy between GA4 and other platforms.

Here is how you can quickly determine if consent mode is implemented or whether it is basic or advanced in GA4 BigQuery.

The values of both ‘privacy_info.analytics_storage‘ and ‘privacy_info. ads_storage‘ fields should be ‘null’ for all events if consent mode is not implemented.

ga4 bigquery no consent mode

1) The values of the ‘privacy_info.analytics_storage’ field should only contain ‘Yes’.

2) The values of the ‘privacy_info. ads_storage‘ field can contain ‘Yes’ and ‘No’ values.

3) The values of both ‘privacy_info.analytics_storage’ and ‘privacy_info.ads_storage’ fields should NOT be ‘null’.

ga4 bigquery basic consent mode

If the Advanced Consent Mode is implemented, the values of both ‘privacy_info.analytics_storage‘ and ‘privacy_info. ads_storage‘ fields could be ‘Yes’, ‘No’ or ‘null’.

ga4 bigquery advanced consent mode

Calculate the values of the ‘Analytics Storage’ column by extracting the values from the ‘privacy_info. analytics_storage’ top level field.

Calculate the values of the ‘Ads Storage’ column by extracting the values from the ‘privacy_info. ads_storage’ top level field.

Calculate the values of ‘Events Total’ by counting the number of rows for each unique combination of analytics_storage and ads_storage values.

Caveat: Case Sensitivity Can Skew GA4 BigQuery Results.

In GA4 BigQuery, ‘yes’ is different from ‘Yes’, ‘no’ is different from ‘No’ and ‘null’ is different from ‘NULL’ and here lies the BIG problem.

The case sensitivity in GA4 BigQuery can cause significant issues when querying data. This problem extends to all text fields in the GA4 BigQuery export.

For example, the values of both the ‘privacy_info.analytics_storage’ and ‘privacy_info.ads_storage’ fields are case-sensitive.

So ‘yes’ is different from ‘Yes’, ‘no’ is different from ‘No’ and ‘null’ is different from ‘NULL’.

Due to this case sensitivity, when querying these fields in BigQuery, it is essential to match the exact casing of the stored values to retrieve accurate results.

For example, if you want to filter for consent denied, you must use ‘No’ with an uppercase ‘N’, not ‘no’.

Similarly, if you want to filter for consent granted, you must use ‘Yes’ with an uppercase ‘Y’, not ‘yes’.

if you want to filter for consent granted

Failing to match the case exactly will result in incomplete or incorrect data retrieval.

  1. Web Analytics Career Path – How to Become a Web Analyst.
  2. GA4 Form Interactions Tracking – Enhanced measurement.
  3. How to track form submissions in Google Analytics 4.
  4. How to send data from Google Search Console to BigQuery.
  5. How to fix duplicate events in Google Analytics 4 (GA4).
  6. How to use two Google Analytics codes on one page.
  7. How to import GA4 Conversions into Google Ads.
  8. What are predictive metrics in Google Analytics 4 (GA4).
  9. Google Tag Manager Event Tracking Tutorial.
  10. How to use Google Analytics 4 with iframe.
  11. Why GA4 Audiences not showing in Google Ads.
  12. Google Analytics 4 Scroll Tracking Tutorial.
  13. Why Google Ads and Google Analytics data don’t match & how to fix it.
  14. Google Analytics 4 Calculated Metrics with Examples.
  15. How to view subdomain traffic in Google Analytics 4.
  16. Google Analytics 4 Cookieless Tracking Setup.
  17. Using Funnel Exploration Report in Google Analytics 4.
  18. Google Advanced Consent Mode and GA4 BigQuery Export.
  19. Which Conversion Window to use in Google Analytics 4.
  20. Tracking single page apps in Google Analytics 4.