How to Export Data from Google Analytics to BigQuery on a Schedule
Where do you store your Google Analytics historical data? Some users export GA reports as CSV files – quite a troublesome and inefficient method. Others pull data from Google Analytics to Google Sheets and keep it there. It’s a good option for relatively medium amounts of data (thousands of rows). But if your analytics amounts to hundreds of thousands or even millions of records, a data warehouse, such as BigQuery, should be the option to consider. Besides, you can connect Google Analytics to BigQuery without any coding. This is what we’ll explain in this blog post.
How to add Google Analytics to BigQuery
Basically, you have the following options to integrate GA with BQ:
- Use the native BigQuery integration if you are a Google Analytics 360 user 😕
- Use the native BigQuery integration for your Google Analytics 4 Properties 😑
- Automate Google Analytics export to BigQuery via Google Sheets 😏
- Code-based BigQuery integration using the Google Analytics API and Python (or another programming language) 😟
- Manually export reports from Google Analytics and manually import them into BigQuery 😶
We believe that we can exclude the last option from the list. As for the rest, we’ll cover them below.
Google Analytics BigQuery export for GA4 and GA360
From July 1, 2023, Universal analytics or Google Analytics 3 property will no longer be available. In this guide, we’re explaining the ways to export data from GA3 property. However, if you need to export data from GA4, check out our dedicated blog posts GA4 to BigQuery.
Previously, a native BigQuery linking was a privilege only for GA360 users. But now, this feature is available for regular Google Analytics users, who can integrate their GA 4 properties with BigQuery. To set up a BigQuery link, you’ll need to complete the following steps:
- Select a project on a Google Cloud Platform and enable the BigQuery API (if you create a new project, the BigQuery API will be enabled automatically).
- Connect Google Analytics 4 property to BigQuery.
Create a BigQuery project
This section is fully covered in our BigQuery Tutorial, so check it out if needed.
Note: Before setting up the linking, make sure that the email address used to sign in to Google Analytics has Edit permission for the Analytics property and Owner access to the BigQuery project.
Enable the BigQuery API to pull Google Analytics data into BigQuery
If you have an existing project without the BigQuery API enabled, here is how you can do this:
In the Google Cloud Console, go to the project selector.
Select your project, go to the Dashboard of the APIs & Services menu, and click “Enable APIs and Services“.
Enter “BigQuery” into the search bar and select the BigQuery API.
On the following page, click “Enable” to enable the API.
Connect Google Analytics 4 property to BigQuery
- Go to Admin => choose the GA 4 property => click “BigQuery Linking“
- Click the “Link” button.
- Choose a BigQuery project (you can choose the project that you have access to).
- Select a Google Cloud region for your data when you set up an export. Click “Next“.
- Select data streams and frequency for exports:
- Daily – once a day
- Streaming – within a few minutes of event arrival
Click “Next“.
- Review your configuration and click “Submit“.
Congratulations! Your BigQuery link is created.
Now your Google Analytics data will be exported to BigQuery according to the selected frequency.
Google Analytics underlies Firebase Analytics, which you can also export to BigQuery. Read how to do this in Firebase to BigQuery.
Exporting Google 360 Analytics into BigQuery
Google Analytics 360 (premium version) also provides a native BigQuery integration. The flow for linking Google Analytics 360 to BigQuery is the same as for GA4:
- Select/create a project on the Google Cloud Platform and then enable the BigQuery API.
- Link a Google Analytics view to BigQuery.
Of course, users who pay for GA 360 are interested in the additional features available rather than just linking Google Analytics with BigQuery. For example, if you are using Google Analytics 360, you can export session IDs within the clickstream data.
But the price is always a key criterion when making a choice. We’ll talk about it a bit later.
Google Analytics data in BigQuery on a schedule
This option of linking GA to BigQuery includes a mediator – Google Sheets. Here is how the flow looks:
- First, the data goes from Google Analytics to Google Sheets.
- Second, the data goes from Google Sheets to BigQuery.
The best thing is that you can easily automate both steps and the exports will be done on a schedule.
Export Google Analytics to Google Sheets
The best way for exporting Google Analytics to Google Sheets is using a dedicated add-on.
It’s a free solution to export reports from Google Analytics automatically on a schedule. We’ve already blogged about it in our Google Analytics to Google Sheets tutorial.
In short, you’ll need to complete the following steps to connect GA to GSheets:
- Install the Google Analytics add-on from the Google Workspace Marketplace.
- Create a report
- Name your report
- Select a view
- Specify configuration options: metrics, dimensions, filters, segments, as well as limit and sort order
- Customize the schedule for your report
Here is an example of three reports created and scheduled in Google Sheets:
Your Google Analytics data will land in Google Sheets according to the specified schedule. Now, you can automate the export of this data to BigQuery.
Google Analytics to Bigquery integration via Google Sheets
To connect your Google Sheets spreadsheet to a BigQuery table, you need to use Coupler.io. It’s a solution for exporting data from different apps (Pipedrive, Airtable, etc.) and sources (CSV, Excel, etc.) to Google Sheets and BigQuery on a schedule. This means that you can export data from Google Sheets to BigQuery and from BigQuery to Google Sheets automatically.
You need to sign in to Coupler.io, click “Add importer“, name it, and complete these three steps:
Set up Source (Google Sheets)
- Choose Google Sheets as the source. Click “Continue“.
- Connect your Google account or choose one from the list if you’ve used Coupler.io before. Click “Continue“.
- Choose the spreadsheet, as well as the sheet(s), to export data from. Click “Continue“.
- Optionally, you can specify the range in the selected sheets. Click “Proceed to Destination Settings“.
Set up Destination (BigQuery)
- Choose BigQuery as the destination. Click “Continue“.
- Connect your BigQuery account or choose one from the list if you’ve used Coupler.io before. For this, you’ll need to add your .json key file (here are the instructions of how to get one).
- Enter the names of the BigQuery dataset and table that will be receiving data from Google Sheets. Click “Continue“.
- Choose the import mode:
- Replace – all data in the table will be replaced with the newly imported data
- Append – newly imported data will be placed below the existing data in the table
- Click “Continue“.
Set up Schedule
You’ll need to customize the schedule for your automatic exports of Google Analytics data from Google Sheets to BigQuery.
Click “Save and Run“, and there you go! Welcome your Google Analytics data in BigQuery in transit through Google Sheets.
Note: Make sure you synchronize your schedules for exporting data from GA to Google Sheets and from Google Sheets to BigQuery. We recommend you have at least one hour of time buffer between them.
How to get .json key file in BigQuery
- In the Google Cloud Platform Console, go to Navigation menu => IAM & Admin => Service Accounts.
- Click Create Service Account.
It’s better to create a new account even if you have one, since you’ll need to provide specific Service account permissions.
- Add the name of your Service Account (required), then click Create.
- On the Service account permissions page, add two roles:
- BigQuery Data Editor – access to view datasets and all of their contents
- BigQuery Job User – access to run jobs
Click Continue, then click Done.
- We can now get to the creation of a private key. Click on the three dots of your service account and choose Manage keys.
- Click Add Key and select Create new key.
- Select JSON as the Key type and click Create. Your private key will be downloaded as a JSON file to your computer.
Now you can select this .json key file to connect BigQuery to Google Sheets. Click Save once you’ve selected it.
Should you export Google Analytics data to BigQuery using Python?
Most of our blog posts, except for Python to Google Sheets, have the no-code approach in mind. This means that we explain how you can do things using the no-code tools and workarounds.
From our perspective, the solutions described above are rather efficient to get the job done – exporting GA to BigQuery. So, you can avoid the programming approach for this. However, using Python can be much more efficient in terms of dependency. On the other hand, you’ll need to spend time on learning the language, its syntax and logic to deliver an actionable code.
How much will the Google Analytics BigQuery integration cost?
First, you need to understand the costs for storing and querying data in BigQuery. They will differ based on your region. For example, here are the current storing prices (at the time of this article) for US (multi-region):
Export of GA4 to BigQuery is free of charge for the BigQuery sandbox usage limits: 10 GB of active storage and 1 TB of processed query data per month. To exceed the sandbox limits, you’ll need to enable billing for your project.
BigQuery linking is included in Google Analytics 360, the cost of which starts at $150,000/year. 😲 Yeah, it’s that pricey!
As for the option Google Analytics => Google Sheets => BigQuery, the Google Analytics add-on is free, while Coupler.io provides multiple subscription plans and a free 14 trial.
Do you want to have a ready-to-use Google Analytics BigQuery connector?
Coupler.io provides a number of BigQuery integrations. For example, you can export your data from Trello, Jira, and other apps to BigQuery without the need to use Google Sheets as a mediator.
If you want to have a straight Google Analytics to BigQuery connector, let us know about it by filling out this form. Our team keeps working on new sources, as well as destinations, to make the product better. If you are interested in visualizing your data, see our Google Analytics 4 to Looker Studio guide. Good luck with your data!
