How to export Google Universal Analytics raw data to BigQuery?

Before Google Analytics 4 was released, only GA 360 users were able to send raw events from Google Analytics to BigQuery. However, GA 360 is not free, starting from $70,000 per year. Thus, not all businesses were able to use the raw data exploring feature.

If you’re using GA4, you can follow Google’s official documentation on how to set up BigQuery Export. However, Google still hasn’t provided free options to export raw data from Universal Analytics to BigQuery or any other database.

What if you don’t have plans to migrate to GA4 and want to get the benefits of BigQuery raw data? You can solve this problem by using Google Tag Manager (GTM) server-side tagging. We created this how-to article to help you with this.

How do we send GA events from the browser to the BigQuery table

1. The gtag.js library sends the Google Analytics event to the Google Tag Manager Server container.

2. The Universal Analytics client receives the request and triggers the JSON HTTP Request Tag.

3. The JSON HTTP Request Tag then sends the data to the URL of the Request To Google Cloud Storage function.

4. The Request To Google Cloud Storage function saves the data on your Google Cloud Platform (GCP) Storage bucket.

5. Finally, you can utilize the BigQuery GCP table type to perform data querying and analysis on the stored data.

   
   

Things that you should know about this flow before you start to go thru how-to

1. BigQuery can use Google Cloud Storage (GCS) bucket as a table. More information about this feature can be found in the official documentation.

2. You can store up to 5 GB of data on GCS for free. One GA request is about 1 KB, so that’s 5,242,880 requests. More details about GCS pricing can be found on the pricing page.

3. The first 2 million requests to Cloud Functions are free, so that’s more than enough for our task. However, you need to check usage and pricing if you already use Cloud Functions.

We decided to divide this how-to into two parts. In the first part, we will tell you how to save data that came in request on GCS. In the second part, we will tell you how to query this data with BigQuery. We hope this helps, and that you will get the desired result faster.

How-to store data from Google Analytics clients inside Google Cloud Storage

By following this guide, you can store data from any GTM Server Side client, not just Google Analytics. Here are the steps involved:

1. Begin by creating a Google Cloud Storage bucket to hold your raw event data. Search for the Storage product in the GCP search prompt and click on "Create Bucket."

2. Choose a name for your bucket, click "Continue," and select the "us-central1" region. Leave the other options with their default values.

3. Now, create a Google Cloud function that will receive data from the Google Tag Manager Server Side Container. Go to the Google Cloud Developer Console, navigate to the Cloud Function section, and click "Create Function."

4. Provide a name for the function and select the "Allow unauthenticated invocations" authentication option. Copy the "Trigger URL" as you will need it in the next steps. Click the "Next" button at the bottom of the page.

5. Set the "Entry point" field to "init." Then, copy the content of a specific file and paste it into the code window. In the code, replace 'your-bucket-name' with the name of the bucket you created earlier and change 'very-secret-random-key' to a randomly generated key that you will need in the future. The runtime should be set to Node.js 10. Additionally, edit the package.json file as instructed.

6. Once you've made these changes, click on the deploy button. It may take a few minutes for Google Cloud to create the function.

7. Now, it's time to create a tag within the GTM Server container. If you don't have a Google Tag Manager Server-Side Container, follow the provided instructions for creating one. Make sure you have the JSON HTTP request tag template installed in your GTM Container. If not, follow the instructions for adding it. Create a new JSON HTTP request tag and fill the "Destination URL" with the URL you obtained when creating the Cloud Function in Step 4. Add your secret key to the end of the URL like this: "?key=your-secret-key". Don't forget to add the trigger you use to determine Universal Analytics.

8. Publish the GTM Server container changes and generate test requests to the GA Tag. You can simply open your website. If everything was done correctly, you should see files being generated in the Google Cloud Storage bucket.

Congratulations! If you have successfully set up the storage of raw events on Google Cloud Storage, you have completed the most challenging part. Now, let's move on to a few more steps before you can start querying the data.

How to query data to Google Cloud Storage using BigQuery

1. Go to the BigQuery product in the GCP Console and click the "Create dataset" button.

2. Leave all options at their defaults and just fill in the name field.

3. Now let's create a table in this dataset. Click "Create table". In the source selector, choose "Google Cloud Storage". The "Select file from GCS bucket" field should look like this: your-bucket-name/*.json. Set the table type to "External table". Choose any table name you want. Check the "Auto detect" option. At the end of this form, click the "Create table" button.

4. Now you can query your data. Click the "Query table data" button and set "*" as the field name. Run the query. If you did everything right, you will see raw GA data in the query results.

This can become a huge hassle for people who are doing it for the first time. Hence, we at Analytics Safe created a service that allows you to back up your Universal Analytics data without any struggle! All you have to do is select the package which best suits your requirements and sit back!

Analytics Safe is the best option for you as it is affordable, easy to use, and offers a number of features that make it a good choice for businesses of all sizes.

Here are the key benefits of using Analytics Safe:

• Privacy: At Analytics Safe, we highly value your privacy and prioritize the security of your data. We have strict policies in place to ensure that your data remains confidential and is not sold or shared with any third parties. Your trust is important to us, and we are committed to keeping your information safe.

  
  

• Convenience: With Analytics Safe, managing your data backup becomes a breeze. Our platform is designed to be user-friendly and intuitive, making it easy for you to navigate and perform necessary tasks. We understand that data management can be complex, but our features and tools simplify the process, saving you time and effort. Say goodbye to the challenges associated with data backup.
  
  

• Affordability: We believe that data backup should be accessible to businesses of all sizes, which is why we offer affordable pricing plans. Whether you are a small startup or a large enterprise, our pricing options are designed to fit your budget. We want to ensure that you can protect your valuable data without breaking the bank. Choose the plan that best suits your needs and enjoy the benefits of Analytics Safe at a cost-effective price.

By choosing Analytics Safe, you can rest assured that your data is secure, enjoy the convenience of a user-friendly platform, and take advantage of affordable pricing options. Let us handle your data backup needs, so you can focus on what matters most - growing your business. You can read more about us by clicking here. And if you would like to jump on a call to discuss more about our service, feel free to schedule a call by clicking here.