4 Ways to Export GA4 Data

James Standen GA4

Google Analytics 4 (GA4) has a completely new data model, a completely new user interface, a new Data API, and a new BigQuery Export that is now available to everyone. This means organizations moving to GA4 will need to build new dashboards and reports to measure the performance of their GA4 properties. But which data source should you use, and how do you export GA4 data?  

How to export GA4 data

The good news is, there are 4 ways to export GA4 data, so Google is offering lots of flexibility for those who want to build their own custom reports.

The bad news is, they don't always match!  We'll get to that later... 

In this post I’ll be looking at the following 4 ways we can access and export data from Google Analytics 4;

  1. The Reports tab in the GA4 web interface 
  2. The Explorer tab in the GA4 web interface
  3. Using the Analytics Data API in your own scripts or through a third party app
  4. Querying against the [GA4] BigQuery export tables in the BigQuery console or a third party app

Each of the methods has their positives and negatives and give very different levels of access to your data.  They all have limits, and some of those limitations depend on if you are using Analytics 360 or the free version of GA4.  You can also push the limits if you use certain special techniques to get more of your data.

Comparing all 4 ways of exporting GA4 data

This table will give you an overview of all the pros and cons of the different methods, and I’ve cross referenced more detailed Google documentation where you can read more.  There are also a list of links with more comments at the end of the post.

Reports Explorer Data API BigQuery Export
What is it? Web UI. A set of standard reports Web UI. A pivot table like interface to build queries A REST api (various client libraries also available) to allow code to be written to request GA4 data. By configuring BigQuery linking in a property, tables will be created for each day with one row for each event
Dimensions and Metrics Available All Only some (1) 1- GA4 Explorer does not have all the dimensions and metrics found in GA4 Reporting UI, plus there are cases where the data returned won’t match Limited but includes custom dims and metrics and some demographic data. (2) 2- GA4 Data API only has some of the dimensions and metrics that are available in the Exporer user interface in GA4 - they can be found here Raw data (3) 3 - GA4 BigQuery export contains raw data for events- this is the data model that describes the data exported into BigQuery. Each GA4 property is linked to a BigQuery DataSet that has the name analytics_x where “x” is the property ID. Every day, a new table is created. Data in the tables will be updated for up to 72 hours, so it's important to reload data from these tables for at least this long to ensure the “golden” data version is used in any reporting tables and your data warehouse. - calculated dims and metrics must be created with SQL query and calculations.
Google Signals Data Yes with thresholds (8). 8 GA4 Data thresholds- data is often not included to prevent being able to infer the identity of individual users based on demographics, interests etc. This also means that event counts will often not match- due to this, and to differences in how Google calculates counts in signals data. Yes with thresholds (8). 8 GA4 Data thresholds- data is often not included to prevent being able to infer the identity of individual users based on demographics, interests etc. This also means that event counts will often not match- due to this, and to differences in how Google calculates counts in signals data. Yes with thresholds (8). 8 GA4 Data thresholds- data is often not included to prevent being able to infer the identity of individual users based on demographics, interests etc. This also means that event counts will often not match- due to this, and to differences in how Google calculates counts in signals data. Not available
Data Format Web UI, export to multiple flat tables in a single CSV/Sheet Web UI, export to flat table with header, totals as first row. JSON (Typically converted to standard flat table by code/tool) (6) 6 GA4 Data API Response JSON structure Nested BigQuery table that can require specific SQL to un-nest for analysis (3) 3 - GA4 BigQuery export contains raw data for events- this is the data model that describes the data exported into BigQuery. Each GA4 property is linked to a BigQuery DataSet that has the name analytics_x where “x” is the property ID. Every day, a new table is created. Data in the tables will be updated for up to 72 hours, so it's important to reload data from these tables for at least this long to ensure the “golden” data version is used in any reporting tables and your data warehouse.
Data Volume Varies on the report, small volumes based on whats being shown in UI, for many reports the row limit is 50,000 (10) 10 Row limits- for many reports, there is a limit of 50,000 for certain aggregate tables- if the unique values on a given day exceed this, then excess rows are rolled up under (Other). This means that the values for dimensional breakdowns are approximate- some of the values shown might also have been characterized as (Other). 500 rows in UI, 50,000 row Limit for export, manual only (download data or export to Google Sheets) No specific limit, but large extractions can require date partitioning (multiple calls) and might take a while. Can be very slow when querying via tools like Data Studio with multiple properties and large data.(10) 10 Row limits- for many reports, there is a limit of 50,000 for certain aggregate tables- if the unique values on a given day exceed this, then excess rows are rolled up under (Other). This means that the values for dimensional breakdowns are approximate- some of the values shown might also have been characterized as (Other). Millions, even Billions. 1M events a day for free version, no limit for 360. No limit for size of results from underlying data- It’s BigQuery so it’s fast and big.
Multiple properties?1 Property at a time only1 Property at a time onlyMultiple API calls can get data that can then be combinedMultiple tables in multiple data sets can be combined via SQL
Sampling Standard Reports are not sampled. For non-standard, the Free version starts sampling at 10 Million events, for 360 it starts at 1 Billion events limit. Never Sampled but can simply return an error requiring a smaller date range/simpler query. No sampling, limit of 1M events a day for free version, no limit for 360.
(Other) “(Other)” category can often be very large, distorting top ten lists (9) 9 “(Other)”- In a number of cases “(Other)” can be a real pain! Reporting in particular will return “(Other)”. Explorer, on the other hand, does not return other so as long as you are not at the sampling limits this is one option. The key for these cardinality limits is to not store too many unique values if you don’t have to. For example, clean out URL parameters from page path, don’t save your own customerID into a custom event or some other unique timestamp- these will all increase how often you see “(Other)” even if you don’t include the specific field in your query, as its the cardinality of the underlying table that matters. No “(Other)” “(Other)” category can often be very large, distorting top ten lists (9) 9 “(Other)”- In a number of cases “(Other)” can be a real pain! Reporting in particular will return “(Other)”. Explorer, on the other hand, does not return other so as long as you are not at the sampling limits this is one option. The key for these cardinality limits is to not store too many unique values if you don’t have to. For example, clean out URL parameters from page path, don’t save your own customerID into a custom event or some other unique timestamp- these will all increase how often you see “(Other)” even if you don’t include the specific field in your query, as its the cardinality of the underlying table that matters. No “(Other)”- raw data is present in BigQuery table
SegmentsNo segments but comparisons let you build sets with up to 5 conditions Can be created in Explorer UINone No segments included but can be created in BigQuery SQL queries
History AvailableFor non-standard reports, limited to data retention (Max 14 Months)Limited to data retention (Max 14 months) for non-standardLimited to data retention (Max 14 months) for non-standardAll time from when you activate the linking, no data retention limit, but no back filling- so turn it on ASAP (4) 4 GA4 BigQuery Export Setup. Its important to setup your BigQuery export as soon as possible as there is no data backfill- you will only have data starting from the time you have turned it on forward. To keep a table for every day, and have access to processed data, be sure to select both the Daily and the Streaming export of data.
Automation Manual Manual Can be automated Can be automated
Quota No Quota No Quota API Tokens. Every query consumes quota tokens, with an hourly and daily limit. Free version limits are 25,000 a day, 5000 an hour, 360 version is 250,000 a day 50000 an hour. (5) 5 GA4 Data API Quota. No Quota, 1 Tb of queries per month and 10 Gb of storage free, after that BigQuery pricing applies. (7) 7 BigQuery Pricing
When to use it? Small sites, custom reporting within GA4 web UI. Lots of pre-made reports. In-depth path analysis, exploring users, paths, etc. where data limits permit. Reporting outside of GA4 web, but small to mid-sized data. Relatively simple queries (no segments). Signals data. Serious analysis. Mid to large amounts of traffic. Analytics360. Automated reporting to self-serve tools (Data Studio, Tableau, Power BI, etc.).

As you can see from the above table, there are a lot of differences between the different sources, and each has their own uses.  Some information is only available in some sources, and some information is only available for a limited time in certain sources.  

For example, the Explorer and BigQuery export allow you to create segments, but Reports and the Data API do not.  While the Data API contains demographic information, the BigQuery export does not.  

This means that those doing serious analysis of their GA4 data will need to seriously consider which data sources they will use. 

What is the best way to get export data from GA4?

In terms of automated data integration, the best way to export GA4 data is to use both the Data API and the BigQuery Export to create a data-mart for reporting and analysis.

Mid-sized / medium traffic properties will start to struggle with the web UI and Data API, often encountering the dreaded "(other)" as a summary category for their data. When large enough, it can render the report unusable. 

Export data from GA4

For large / high traffic properties there's no question - the BigQuery Export together with the Data API are the only way to get useful data as the limits of the web UI make the reports and explorer unusable. If you have large data volumes and lots of unique values (high cardinality) then you need to use the BigQuery export to get around the limitations of the others.

That said, given the complexity of the Data API, it makes little sense for each organization to code their own solution and maintain it.  

And while the BigQuery Export is excellent, it is a pay-per-use service meaning that you don't want analysts querying over the raw tables in reporting tools like Data Studio, Tableau, and Power BI. Instead you want a solution that will create cost efficient report tables. 

Using Analytics Canvas to improve GA4 data integration

As Google Analytics Trusted Testers for over a decade, and with deep experience in GA4 data extraction, we've gone beyond traditional data loading that you'll find in other tools. 

The Google Analytics 4 features built into Analytics Canvas are designed to let analysts access GA4 data without having to write code and without having to learn complex data staging techniques.  Canvas makes it easy to extract all possible data without the help of data engineers.

These techniques not only save time, they save significant bytes processed against the raw BigQuery tables! 

Extract GA4 data from the Analytics Data API with Analytics Canvas

Using Analytics Canvas to export GA4 data

Canvas connects to the Analytics Data API and offers the following enhancements:

  • a visual user interface for selecting Properties, Dimensions, Metrics, and Meta Data for your query
  • the ability to query multiple GA4 properties from multiple Google accounts at once
  • a testing tool to validate your query before running it 

The visual query builder makes it easy for any user to quickly define their custom query and land data within minutes. 

how to export data from ga4 to bigquery

Canvas uses advanced techniques behind the scenes so analysts can use a simple wizard to define and run a query. Canvas manages partitions, incremental loads, and within the sophisticated token and quota system to return tens or even hundreds of millions of rows from the Data API. 

Extract GA4 data from BigQuery with Analytics Canvas

All analysts working with GA4 data will benefit from having access to the BigQuery export.  In fact for many analysts it is the only way to get the right level of detail and accuracy.  

Google Analytics data export

Using the BigQuery export you have access to raw data, allowing you to develop simple queries for 'upsampled data' (data that doesn't include "(Other)" in the results).  

The BigQuery export also allows you to create your own segments, something that is not offered in the Analytics Data API.

Canvas connects to the Google BigQuery API so that you can make SQL queries on your GA4 data, and offers the following enhancements:

  • allows you to use Date Parameters to query over the sharded GA4 tables efficiently 
  • lands data from the GA4 raw tables into Smart Tables that are date partitioned, versioned, and linked to the workflows they're used in
  • performs incremental loads against the BigQuery GA4 export into your GA4 Smart Tables, saving up to 94% in query bytes compared to queries over the raw tables

Canvas provides so much more than an environment for making GA4 queries and exporting results!

Enhanced GA4 data workflows with Analytics Canvas

Enhanced GA4 data workflows

When using either the BigQuery GA4 export tables or the Analytics Data API table, Canvas offers the analyst additional support that includes:

  • storage of the report table in "Smart Tables" within Analytics Canvas Online
  • incremental loads on queries for more efficient processing and faster reporting
  • profiling tools to inspect data for quality issues
  • data-prep tools to clean data and prepare report tables for analysis
  • analysis tools to study data and generate insights 
  • automation tools to keep GA4 data workflows up to date

All of the tools required to get your GA4 data ready for reporting are at your disposal within Analytics Canvas.  

No need for external storage in a data warehouse (though you can export your data there), no need to worry about row limits, API limits and quotas, developing date partitioned tables, etc. etc.  

Canvas provides data prep tools so that you can focus on data analysis and generating insights!

Wrapping Up

While you can export GA4 data using the web data export, the Data API, or BigQuery, the easiest and most efficient way to do it is with Analytics Canvas.

Analytics Canvas specializes in GA4 data extraction using both the API and the BigQuery export. By providing "data engineering in a box", Canvas ensures that analysts and business users can access, process, and analyze data without the skills of a developer or data engineer. 

The GA4 connector for Analytics Canvas has been built by data engineers with over a decade of experience working with GA APIs.  With our best-in class connector, you can create reports for both GA4 Standard and GA4 360 accounts, or download your data in bulk.

Unlike expensive ETL tools that run for thousands a month, you can enjoy all the benefits of our Data Prep software and have access to a best-in-class GA4 data connectors. Choose from our Starter, Pro, Premium or Enterprise plans based on the needs of your business. 

Next Steps

Whenever you’re ready… here are 3 ways Canvas can help you with your GA4 reporting challenges:

  1. Extract data from all your properties using the API or BigQuery without writing code
  2. Profile, analyse, and prepare data for reporting  
  3. Maintain your GA4 data warehouse within Analytics Canvas Online or your own DB

Ready for the next step?

  • Start an instant 30 day risk-free trial. No credit card or sales call required. 
  • Schedule a demo for you and your team.
  • Contact us to discuss plans and pricing or activate your subscription 

Wondering if Canvas is right for you? Check out the related articles to learn more about our Data Studio Partner connector. 

Notes and Links from Table

1 - GA4 Explorer does not have all the dimensions and metrics found in GA4 Reporting UI, plus there are cases where the data returned won’t match - https://support.google.com/analytics/answer/9371379 

2 - GA4 Data API only has some of the dimensions and metrics that are available in the Exporer user interface in GA4 - they can be found here- https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema

3 - GA4 BigQuery export contains raw data for events- this is the data model that describes the data exported into BigQuery.  Each GA4 property is linked to a BigQuery DataSet that has the name analytics_x where “x” is the property ID.  Every day, a new table is created.  Data in the tables will be updated for up to 72 hours, so it's important to reload data from these tables for at least this long to ensure the “golden” data version is used in any reporting tables and your data warehouse.  https://support.google.com/analytics/answer/7029846

4 - GA4 BigQuery Export Setup.  Its important to setup your BigQuery export as soon as possible as there is no data backfill- you will only have data starting from the time you have turned it on forward.  To keep a table for every day, and have access to processed data, be sure to select both the Daily and the Streaming export of data.   https://support.google.com/analytics/answer/9823238

5 - GA4 Data API Quota.   https://developers.google.com/analytics/devguides/reporting/data/v1/quotas

6 - GA4 Data API Response JSON structure https://developers.google.com/analytics/devguides/reporting/data/v1/rest/v1beta/RunReportResponse

7 - BigQuery Pricing https://cloud.google.com/bigquery/pricing

8 - GA4 Data thresholds- data is often not included to prevent being able to infer the identity of individual users based on demographics, interests etc. This also means that event counts will often not match- due to this, and to differences in how Google calculates counts in signals data.  https://support.google.com/analytics/answer/9383630?hl=en

9 - In a number of cases “(Other)” can be a real pain!  Reporting in particular will return “(Other)”.   Explorer, on the other hand, does not return other so as long as you are not at the sampling limits this is one option.  The key for these cardinality limits is to not store too many unique values if you don’t have to.  For example, clean out URL parameters from page path,  don’t save your own customerID into a custom event or some other unique timestamp- these will all increase how often you see “(Other)” even if you don’t include the specific field in your query, as its the cardinality of the underlying table that matters. https://support.google.com/analytics/answer/9309767?hl=en

10 - For many reports, there is a limit of 50,000 for certain aggregate tables- if the unique values on a given day exceed this, then excess rows are rolled up under (Other).  This means that the values for dimensional breakdowns are approximate- some of the values shown might also have been characterized as (Other). https://support.google.com/analytics/answer/10702008