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?
If you want to export your GA4 data faster, skip ahead to Analytics Canvas Online and:
- sign-up using your Google Account
- add one or more Google Analytics account authorizations
- run your queries and either export your data or preview, profile, and process it in Analytics Canvas.
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;
- Reports in the GA4 web interface
- Explorations in the GA4 web interface
- Using the Analytics Data API in your own scripts or through a third party app
- 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
The data streams are feeding data into a GA4 property. There are then 4 ways to access that data, two of which are through the web UI, one is through the Analytics Data API (which is fed by the same reporting tables as Reports), and the final way is through the raw BigQuery event tabes.
The table below 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.
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.
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
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.
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.
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, but if you prefer not to write all that SQL, read on...
Extract GA4 data from BigQuery without writing SQL
If you'd prefer not to spend countless hours developing your BigQuery cookbook and writing the complex SQL required to get high quality results, sign-up to be among the first to use our upcoming GA4 BigQuery SQL Builder. Let Canvas worry about the 'gotchas', like handling null values, dealing with the event time zone, sessions that overlap time periods, and handling multiple data types in custom metric columns.
In both cases, writing your own SQL or using the Query Builder, once the query is made, Canvas 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
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!
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.
Whenever you’re ready… here are 3 ways Canvas can help you with your GA4 reporting challenges:
- Extract data from all your properties using the API or BigQuery without writing code
- Profile, analyse, and prepare data for reporting
- 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