BigQuery shows more purchases than Google Analytics 4

If you have linked your Google Analytics 4 property to BigQuery, you might notice discrepancies between the data you see in the GA4 UI and what is available in BigQuery. This includes e-commerce data, where you may see a higher number of purchases in BigQuery compared to GA4. Weird, right?

So, if you are wondering why BigQuery shows more purchases than Google Analytics 4, the answer is pretty straightforward: advanced consent mode. When consent for “analytics_storage” (a consent mode parameter) is not granted, purchase data is still sent to BQ.

Although these transactions may not appear in GA4 reports (because of modeling, etc.), they will still be visible in BigQuery.

Understanding advanced consent mode

To clarify, we are specifically discussing the advanced consent mode. If you have only basic consent mode enabled, then the data should match between GA4 and BigQuery. Check out this documentation from Google to learn more about consent mode.

When a user visits your site and is offered a choice to consent to certain types of data collection, including analytics cookies, they may not provide consent immediately (or ever).

Consent mode is a feature in GA4 that allows you to control how data is collected based on user consent. Implementing consent mode means that GA4 will adjust its behavior depending on what the user accepts or denies.

Implementing advanced consent mode will enable your site to still send cookieless pings even if a user denies consent to analytics cookies. Google Analytics 4 will not directly display this data, but rather, it will go through a modeling process to estimate what unconsenting users did on the site.

From a legal perspective, this is in the gray zone. But that’s a topic for another time.

What impact does consent mode have on GA4 and BQ?

Let’s explore the impact that consent mode has on GA4 and BigQuery.

Data in GA4

When consent is denied for analytics cookies, GA4 will still receive and process the data, but it may not be displayed directly in reports. As mentioned above, GA4 uses modeling to estimate and represent user behavior for users who denied consent.

Therefore, some data may be missing from the GA4 interface, specifically for those users who did not consent.

Data in BigQuery

Regardless of whether consent was given or not, data will continue to be sent to BQ from cookieless pings collected by GA4, including user_id and custom dimensions (unless you specifically configured your tags not to do so).

That’s why you have more purchase events in BigQuery.

Queries for analyzing consent mode data in BigQuery

When you have implemented consent mode, an “analytics_storage” parameter is collected from each user. This parameter will enable storage, like cookies related to your analytics.

In BigQuery, there are two queries that you can run to understand better your data and what effect consent mode is having on your data collection.

To see the transaction ID list when consent is not given, enter the query below. Some things to keep in mind:

• Replace the path in the “FROM” statement with the path to the relevant table for the GA4 property you are investigating.
• Input the date range you want to explore in the “_table_suffix” filter.
• When analytics_storage = ‘denied’, cookieless pings are sent to Google Analytics 4, as no first-party cookies will be read or written by GA4.

SELECT
  transaction_id
FROM
  `analytics-mania-ga4-bq.analytics_23456782.events_*`
WHERE
  _table_suffix between '20240701' AND '20240731'
  AND event_name = 'purchase'
  AND analytics_storage = 'denied' -- consent is not given

To see the transaction ID list when consent is given, enter the query below. Some things to keep in mind:

• When analytics_storage = granted, first-party cookies can be set, and data is sent to Google Analytics 4 and BigQuery.

SELECT
  transaction_id
FROM
  `analytics-mania-ga4-bq.analytics_23456782.events_*`
WHERE
  _table_suffix between '20240701' AND '20240731'
  AND event_name = 'purchase'
  AND analytics_storage = 'granted' -- consent is given

So, once you exclude all non-consented purchases, the numbers start to make sense again!

Example: Aligning GA4 and BQ purchase events

To show you how this works, let’s look at an example. We will create an exploration in GA4 to get the event count for the purchase event and then check to see what values we get in BigQuery for both when consent is granted and when consent is denied.

In GA4, go to Explore > Blank report.

In the Dimensions, add in the “Event name”, and for the Metrics, add in the “Event count”. Double-click on both of these to add them to the report.

In the Filters section, drag over the “Event name” and set this to exactly match(es) “purchase” and click “Apply”.

Now, we have the event count for purchases.

In BigQuery, to check the event count for the purchase event when both consent is granted and when consent is denied, we will use the query below:

SELECT
  analytics_storage, -- see the count of purchases based on granted/denied consent
  COUNT(*) as purchase_count -- count of purchase events
FROM
  `analytics-mania-ga4-bq.analytics_23456782.events_*`
WHERE
  _table_suffix between '20240701' AND '20240731'
  AND event_name = 'purchase'
GROUP BY
  analytics_storage

As you can see, the number of transactions when consent is “GRANTED” equals the number of transactions in the GA4 exploration!

Note: I used a device-based reporting identity in GA4 in the example above.

BigQuery shows more purchases than Google Analytics 4: Final Words

In conclusion, if you are using advanced consent mode, it is crucial to be aware of the discrepancies it can create between GA4 and BigQuery. When a user denies consent, GA4 will show a modeled version of the data, while BigQuery will still show the true data.

This can also apply to other events where you notice inconsistencies between Google Analytics 4 and BigQuery, so you can replace the purchase event with a different event name in BQ to see the differences in the data.

Let me know your thoughts and questions in the comments below!