How to stream data from Google Analytics 4 to BigQuery
Introduction to BigQuery
BigQuery is a fully managed, enterprise-scale data warehouse, designed to allow fast queries in SQL across massive data sets.
From Google Analytics you can stream data into your own BigQuery warehouse and use this raw data set to power any Business Intelligence (BI) needs.
Any Google Analytics 4 account can export to BigQuery, but using Littledata’s GA4 tracking allows you to build even more powerful ecommerce reports
What are the advantages of BigQuery over Google Analytics?
Google Analytics can only store user-attributable data points (as opposed to aggregate metrics) for 14 months. So, if you intend to analyze marketing attribution or user journeys over more than a year, you need to export and save the data.
Think of BigQuery as a long-term data store that your brand owns — whereas you only use GA reports with Google’s consent. Since you cannot export historical event data from GA, we recommend setting up the BigQuery link now even if you have no immediate plans to use the data.
Once all the customer touch-points from GA have been stored in BigQuery — including which campaigns and content they engaged with, what products interest them, and what they purchased — analysts have maximum flexibility to use their preferred SQL queries and BI software to build reports.
If you have an existing project, you will need to enable the BigQuery API. Once you’ve done so, you should see BigQuery in the “Resources” section of Cloud Platform.
Create a Service Account for Google Analytics. From the Navigation panel on the left, go to IAM & admin > Service accounts. Verify that [email protected] has been added as a member of the project, and given the primitive role of editor. Add the account if necessary.
Sign in to Google Analytics. Use an email address that has OWNER access to the BigQuery project, and also has Editor role for the Analytics property that includes the data stream you want to link.
Go to Settings > Property Settings > BigQuery links
Then select a BigQuery project linked with the same Google account or specify a project by ID
Select data streams or exclude events from the stream by name
Select daily and streaming exports
Select the region to store the data in. Although Google Analytics will still process data in the USA we recommend storing the data in the country where your company is registered.
Confirm the settings
Check the next day that data is coming into BigQuery. It could take up to 24 hours for the data connection to set up.
Costs for data storage in BigQuery are very low. A store with 500,000 monthly users costs around $8 per month for 3 months of tracking, and a 3 year data set from a store of this size might eventually cost $40/month.
Data analysis costs are harder to predict, but for moderate usage might cost up to $50 a month.
In addition, to use the data set to generate reports in Looker Studio, Google recommends using BI Engine. Reserving the minimum of 1GB of in-memory cache for reports costs $35/month.
Can you export to data warehouses other than BigQuery?
Google BigQuery is the only data warehouse supported natively by Google Analytics, but there is an option to export the data into Google Cloud Storage and from there you can run an import into Snowflake or RedShift.
Is this a real-time data feed?
Yes, Google streams data within “a few minutes” into an intraday table in Google BigQuery. Once the daily batch export for the previous day is complete this intraday table is deleted.
Is there a limit to the data stored or processed in BigQuery?
For the free version of GA4, Google will export a maximum of 1M events per day (~30M per month). Littledata tracks around 500 events per paying customer (including events from customers who didn’t purchase). That means this limit should be enough for stores with less than 50,000 orders per month.
You can filter out events from this data stream at source. E.g. if you exclude product list events that may bring you below that limit.
Beyond that you can upgrade to Google Analytics 360, with no processing limits. BigQuery itself has no event processing limits and routinely hosts data sets many orders of magnitude larger than this.
