[GA4] Compare Analytics reports and data exported to BigQuery

If you export Analytics data to BigQuery, you may occasionally notice some differences between GA4 reports and BigQuery. Troubleshoot the common causes of discrepancies, fix them, and accurately compare your data.

In this article:

• Check the link between Analytics and BigQuery
• Make sure settings match
  • Set reporting identity to Device ID
  • Verify that time zones match
  • Check if any data streams or events are excluded from export
• Compare Analytics and BigQuery data
  • Find the total event count in Analytics
  • Find the total event count in BigQuery

Check the link between Analytics and BigQuery

To make sure that you're looking at the right data, verify that Analytics and BigQuery are linked and that the BigQuery project is correct.

1. In Admin, under Product links, click BigQuery links.
2. Find the row for the link and ensure the project ID matches the BigQuery project where you want to compare data.

If you don't see a row for a link, follow the instructions to set up BigQuery export.

Make sure settings match

Some data discrepancies between Analytics and BigQuery are normal and due to differences between the data available to each system. To find out if the differences indicate a potential problem, check these settings before comparing data.

Set reporting identity to Device ID

GA4 has four reporting identity spaces available for reporting. However, GA4 only exports data based on Device ID to BigQuery. If you have another reporting identity set in GA4, the comparison with BigQuery will be inaccurate.

Change your reporting ID to Device ID when comparing data, and then change back to another reporting identity after you finish. The option you choose does not affect data collection or processing. You can switch between the options at any time without making any permanent impact on data.

Set the reporting identity for your property.

Verify that time zones match

If BigQuery and Analytics are set to different time zones, you might be comparing data from different time periods and not realize it. Make sure that the time zone of exported data in BigQuery is the same as the property time zone in Google Analytics.

Check your time zone in Analytics

1. In Admin, under Property, click Property details.
2. Find your Reporting time zone.

Check your time zone in BigQuery

1. Open the BigQuery project connected to your Analytics account.
2. Select the analytics dataset.
3. Select events_ table.
4. Go to the Details tab.
5. Under Table info, find the Created timestamp. The time zone is listed at the end of the timestamp. For example, in the timestamp Apr 23, 2023, 12:44:54 PM UTC-7 "UTC-7" represents the time zone.

Check if any data streams or events are excluded from export

1. In Admin, under Product links, click BigQuery links.
2. Click the row for the linked BigQuery project.
3. Under Data configurations, select Configure data streams and events.
4. Under Data streams to export, check if all the data streams in the property are being exported. If not, note which ones are missing.
5. Under Events to exclude, check if any events are excluded from the export. If any events are excluded, note them.

If any data streams or events are excluded, you must create an exploration with a matching filter in GA4 to compare data.

Compare Analytics and BigQuery data

Compare the total number of event rows exported to BigQuery on a single day to the total number of events in GA4 on the same date.

A discrepancy of 2-5% between the total event count in Analytics and BigQuery is expected.

Find the total event count in Analytics

1. Sign in to Google Analytics.
2. From the left menu, select Reports .
3. On the left, click Engagement > Events.
4. Adjust the date range for the report to the day you want to compare.
5. In the Event count column, find the total event count above the first row.

If any data streams or events are excluded from your report, you must create an exploration with a matching filter to find the event count.

Create an exploration with filters to find the event count in Analytics.

1. Sign in to Google Analytics.
2. On the left, click Explore.

3. At the top of the screen, select the Free form template.
4. Adjust the date range for the exploration to the day you want to compare.
5. Under Dimensions select Event name and Stream name.
6. Under Metrics add Event count.
7. Drag Event name under Rows.
8. Drag Event count under Values.
9. Under Tab settings, find Filters and click Drop or select dimension or metric.
   1. If you need to include only a specific data stream:
      1. Select Stream name.
      2. Set the filter match type to Contains.
      3. Enter your stream name and click Apply.
   2. If you need to exclude events:
      1. Select Event name.
      2. Set the filter match type to does not exactly match.
      3. Enter your event name and click Apply.
10. In the Event count column, find the total event count above the first row.

Find the total event count in BigQuery

1. Open the BigQuery project connected to your Analytics account.
2. Select the analytics dataset.
3. Select the events_ table.
4. Go to the Details tab.
5. Under Storage Info, find Number of rows.

If the total number of rows in BigQuery matches the total event count in GA4 after you've followed the steps on this page to verify the link and check that the settings match, then the exported data is accurate.

If you still find a data discrepancy when running a query and comparing it to GA4 reporting, check the query to make sure that the correct columns are used to compare the data.

Remember to change your reporting ID back after you finish comparing data if you temporarily switched it to Device ID.