Connect Google Analytics to Google BigQuery: Saving your historical data

After nine years of dedicated service, Google Analytics— or Universal Analytics—will finally step down and give the crown to its successor—Google Analytics 4.

Google Analytics has been around for 15 years, and for the very first time, Google gives the platform a complete makeover. This time we’re not only talking about a brand new UI, but the data model, features, and tracking will also be different.

In this post, we’ll focus on one of the most-asked questions:

How can you save historical data from Universal Analytics?

Note: Throughout the post, we’ll refer to Google Analytics as Universal Analytics and Google Analytics 4 as GA4 to avoid confusion.

Skip ahead

• Universal Analytics sunsets
• Supermetrics Google Analytics connector for BigQuery
• Export your Universal Analytics data to BigQuery

If you want to learn more about GA4, check out our GA4 Knowledge Center for tutorial videos, templates, support articles, and more.

The sunset of Universal Analytics and what it means for you

Originally, Universal Analytics was built around third-party cookies and web tracking. However, in today’s digital landscape, besides websites, users can easily find and buy products from mobile apps. Additionally, they also want better privacy policies and more control over their data. As a result, Google introduced GA4 as a new measurement approach in the post-cookie world.

According to the latest update from Google, Universal Analytics will stop processing new data on July 1, 2023. After this date, your Universal Analytics properties won’t collect any data.

If you haven’t started using GA4, now’s the time. The sooner you start setting up your property, the more historical and user data you can accumulate in GA4.

After the sunset, you can still see your old data and report in Universal Analytics for another six months. That’s why you should export important data and reports if you want to keep a record of your website performance over time.

One simple way to export your Universal Analytics data is using Supermetrics and Google BigQuery. Having your historical data in BigQuery helps you:

• Eliminate the risks of losing important data and reports while setting up your GA4 property
• Centralize your Universal Analytics data with other marketing data
• Feed the data to a BI tool like Google Data Studio or Power BI for data visualization
• Perform historical and long-term trend analysis
• Benchmark the metrics against the new ones in GA4

Supermetrics Google Analytics connector for Google BigQuery

If you’re looking to export data from Universal Analytics to Google BigQuery, you basically have three options:

• Export your reports in the following formats: CSV, TSV, XLSX, Google Sheets, or PDF
• Use the native Google Analytics connectors. However, this option is only available for Google Analytics 360 users
• Use a data pipeline provider like Supermetrics to export your data through the Google Analytics API

Here’s how you can benefit from using Supermetrics.

Multi-account reporting

Since the data blending feature in Google Analytics allows you to blend data from only five accounts, multi-account reporting can be troublesome if you’re managing multiple accounts. However, with Supermetrics, you can easily add multiple views or segments to your report.

Get in-depth insights

Supermetrics lets you pull 463 metrics and 618 dimensions directly from the Google Analytics API. You can easily create custom reports and add different segments to get a more granular view of your website performance.

Additionally, you can also find more advanced dimensions that aren’t available in the native Google Analytics reports, such as product category, page directory, device, etc.

Avoid data sampling

If you accumulate a lot of data in Universal Analytics, Google may sample a part of your data to create reports. As a result, you may miss important insights about your website performance.

Supermetrics uses data partitioning to avoid data sampling. This means when pulling data from the Google Analytics API, we break up the data into small datasets before generating your reports. You can get unsampled data without breaking a sweat.

Gain a complete understanding of your marketing performance

Besides Universal Analytics GA4, Supermetrics also pulls data from 100+ marketing platforms, including:

• Web analytics: Adobe Analytics
• Organic and paid social: Facebook, Instagram, TikTok, etc.
• SEO: Google Search Console, Semrush, Ahrefs, etc.
• Sales: HubSpot, Salesforce, etc.
• Email: Mailchimp, Klaviyo, etc.

and many more!

You can easily combine your web analytics data with data from other channels to get a holistic view of your marketing performance.

How to export your Universal Analytics data to Google BigQuery

In this example, we use Universal Analytics, but you can follow the same process for any connectors.

To get started, make sure you set up your Google Cloud Platform account. 

First, create a BigQuery project to store the data. Open the BigQuery page and select ‘New project’.

Fill in your project name, organization, and location. Then click ‘Create’.

To connect Google Analytics data to your project, navigate to ‘Marketplace’ on the left-side menu.

In the marketplace, search for a connector called ‘Google Analytics by Supermetrics’ → ‘Enroll’ → ‘Configure transfer.’

Select ‘Google Analytics by Supermetrics’ as the source. Then fill in the following fields to connect the Google Analytics API to your BigQuery project.

• Display name
• Schedule options
• Dataset
• Refresh window (optional)
• Notification options (optional)

Once you’re done, click ‘Connect source’.

On the pop-up window, authenticate your Google account. Choose which schema, accounts, and segments you want to pull data from.

• Schema: choose the schema you want to transfer. Typically, the ‘Standard’ schema is a good starting point if you want to save your data.
• Accounts: you can choose the Universal Analytics property from which you want to pull data.
• Segments: add a segment if needed.

Then, click ’Save’. Click ‘Schedule backfill’ and choose ‘Run for a data range’ to schedule a backfill with this configuration.

Pro tip: To avoid surpassing the daily API limit in Universal Analytics, we recommend that you break up your queries into manageable chunks. Depending on how big your dataset is, you can backfill your data one month or one quarter at a time.

After setting up your date range, click ‘Ok’. When you refresh the page, you’ll see the status of your transfers.

Go to the ‘SQL workplace’ and select your project to see your data.

For example, if you want to see your content data, click ‘GA_CONTENT_’. You’ll see the description of the schema. You can also see the preview of your data, such as page title, exit time, page load time, etc.

And that’s how you can save your historical data from Universal Analytics.

Keep calm and save your historical data

While the idea of switching to a brand new platform can be intimidating and uncomfortable, don’t panic. As long as you export your data from Universal Analytics to Google BigQuery, you can save the historical performance of your website and be ready well ready for GA4.

If you need help moving your web analytics data to your favorite reportings and analytics tools, start a 14-day free Supermetrics trial.