How to store your Google Universal Analytics data in BigQuery

Afraid of losing your historical Google Universal Analytics data when it goes away in July 2023? Don't worry anymore! Thanks to Dataslayer you can store all your historical UA data in a data warehouse such as BigQuery without the need of knowing any coding language!

Follow the step-by-step guide with us by registering for free here and enjoy all our BigQuery product capabilities!

When importing historical data from Google Universal Analytics into BigQuery, it's important to consider the specific use cases and analysis you want to perform. Here are some general recommendations for data to store in BigQuery:

User-level data: This includes information about individual users, such as their demographics, location, and behavior on your website. This data can be used to segment users and understand their behavior.
Session-level data: This includes information about individual sessions, such as the date and time, the number of pageviews, the session duration, and the bounce rate. This data can be used to understand how users interact with your website.
Hit-level data: This includes information about individual hits, such as the pageviews, events, and e-commerce transactions. This data can be used to understand the user journey on your website, and analyze the performance of your marketing campaigns.
Custom dimensions and metrics: These are user-defined fields that can be used to collect additional data about your users and their behavior. This data can be used to segment users and analyze their behavior in more detail.
Cost data: If you are running Google Ads campaigns and linking your GA and Ads accounts, it's important to import cost data to be able to analyze the performance of your campaigns and the ROI.

It's also important to consider the volume of data you will be storing in BigQuery, and the costs associated with storing that data in the Google Cloud Console.

Ok, got it, but how can I start importing this data in BigQuery?

To start importing historical data from Google Analytics into BigQuery using Dataslayer is very easy. Here's the importing process:

In Dataslayer:

Login in the Dataslayer website and go to the BigQuery product section.



Click on New Transfer and start configuring the first table you are going to import.

The best practice here is to have as many tables as data you want to store in BigQuery. i.e. let's configure one transfer for user-level data, other one for session-level data, etc. You can have an unlimited number of transfers.



As you can see, you have the Origin at the left of the screen and the Destination at the right. Login in the Google Analytics data source as this is going to be the Origin of the data. Then login in your BigQuery account as it's going to be the Destination.



Configure the data extraction in Dataslayer to extract the data you want to import, for example, the data for a specific time frame, a specific view, or a specific set of metrics and dimensions.

In order to avoid incompatibility errors in your transfer, you can consult this article where we recommend different compatible transfer options. These metrics and dimensions tables are optimized to show different categories of data and contain all compatible fields.

Once you've configured the query, you are ready to upload your historical data from Google Analytics and load it into BigQuery. Once you've uploaded the historical data, you can set up a schedule to periodically extract data and update it in BigQuery.





Out of Dataslayer:

Once the data is loaded in BigQuery, you can run SQL queries on it to perform advanced analysis, join it with other data sources, or create visualizations.

It's also important to note that Google Analytics data is stored in BigQuery in a nested format, which can make the querying process more complex.

Ok... But now how can be this data useful when I start using the new Google Analytics 4 integration?

Merging data from Google Analytics 4 with data stored in BigQuery can be done by joining the tables on a common field, such as the client ID or user ID. Here's one possible process:

Extract the data from Google Analytics 4 using the Dataslayer for BigQuery product to load it into BigQuery.
Once you've created a table in BigQuery that contains the data from Google Analytics 4 with Dataslayer, make sure it has a common field with the existing data in BigQuery.
Use SQL JOIN statements to combine the data from Google Analytics 4 with the existing data in BigQuery. This can be done by joining the tables on a common field, such as the client ID or user ID.
Once the data is merged, you can use SQL queries to analyze the data and create visualizations.

If you are using Google Analytics 4 and Universal Analytics together, you can use the GA4 client ID or user ID as the common field to join the data.

It's also important to consider the data structure and format of the data you are merging, and to clean and transform the data as needed to ensure it can be correctly joined.

Still have doubts about UA, GA4 or how to store its data in BigQuery? Contact us through our live chat on our website or via email.

Updated on: 09/04/2024