Chapter 16: BigQuery
16.9. Importing Google Analytics Data to BigQuery
In the previous lesson we discussed different ways of importing data into BigQuery. In this advanced lesson, we'll explore how to import your Google Analytics data into BigQuery using a combination of Universal Analytics, Google Sheets, and Looker Studio.
Using GA Add-on for Sheets
For Universal Analytics properties that don't offer free export, you can use the GA add-on for Sheets. This add-on is free and allows you to hit the API of Universal Analytics to extract data from it into a Google Sheet. This method will be useful for extracting historical data from your Universal properties and storing it alongside your G4 data.
Keep in mind that this method requires manual setup and data modeling. Additionally, it involves selective replication, meaning you need to decide which dimensions and metrics you want to extract from Google Analytics to the Google Sheet.
Limitations and Considerations
There are some limitations when using this method:
- You may be restricted by the number of dimensions (7) and metrics (9) that can be requested with a single API request.
- You might need multiple Google Sheets and API requests to extract all your GA Universal data.
- The volume of data is limited by the capacity of the Google Sheet and the runtime timeout of Google App Script (90 minutes per day).
Despite these limitations, this solution remains free. However, you will need to monitor and maintain your setup.
G4 Data in BigQuery: Automatic & Real-time
When dealing with G4 data in BigQuery, things are much simpler:
- The process is automatic
- It's close to real-time
- It's free
- Full replication is available
Whatever exists in G4 will be replicated in BigQuery almost instantly. Even some fields or properties not accessible within the G4 interface will still be pushed to BigQuery.
Overall, while importing Universal Analytics data into BigQuery requires more manual effort than working with G4 data, it can still be a helpful solution for managing historical data.
In the next lesson we will learn how to import data from Marketing Tools into BigQuery.
📩 Receive my weekly Looker Studio tips
🖇 Connect with me on LinkedIn