How To Import Data From Google Universal Analytics Into BigQuery

Google Universal Analytics, or simply Google Analytics, will be sunsetting by 1st July 2023 to pave the way to the new Google Analytics 4 version. And to help you migrate and keep all of your previous analytics data, your best option could be to import your data from Universal Analytics into BigQuery.

For almost a decade, Google Analytics has served us well. However, the needs of users have since grown and necessitated a complete makeover for the platform. From the user interface, data models, features, and tracking, almost everything in GA4 has been changed, improved, or optimized for better experiences or performance.

The change brought many concerns, including the differences between Google Universal Analytics vs Google Analytics 4, metric comparison between UA and GA4, how to prepare for the new version, and more. And so, today, we will address another critical concern — how to export your historical data from UA to Google BigQuery.

So, let’s dive in!

Options to export your Google Universal Analytics data into BigQuery

After 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.

To import your Google Analytics data into BigQuery, you have the following options:

  • Export your reports as CSV, TSV, XLSX, PDF, or Google Sheets and take them to Google BigQuery.
  • If you are a Google Analytics 360 customer, you can use a native Google Analytics connector to export your data to BigQuery
  • Leverage BigQuery data pipeline connectors like Dataslayer to take your UA data to BigQuery seamlessly. This option is what we will discuss today in this article.

How to import key data in Google Universal Analytics to BigQuery with Dataslayer

One of the easiest and fastest ways to bring your Universal Analytics data to BigQuery is to use the Dataslayer BigQuery connector for Google Universal Analytics. With this connector, you can import all your data to BigQuery in seconds without writing even a single line of code. 

If you have millions of rows and want to combine sources, you can also use our platform with BigQuery to analyze millions of rows from different data sources, and combine all of them using BigQuery. This way, you can visualize or create reports using Looker Studio or other reporting platforms that you love.

So, how do you do it? Here’s the process to do it:

  1. Log in to the Dataslayer web interface to transfer your data easily from Google Analytics API to BigQuery.
Signup/Login window
  1. On your dashboard, click New Transfer, and Sign In to your BigQuery account.
  2. Once you’ve signed in successfully to BigQuery, click Transfer Data.
Adding new BigQuery data transfer
  1. Next, select Google Analytics as your data source on the side pane. 

Note: You can also bring data to BigQuery from the other 45+ data sources.

  1. Choose the Date and Time Range for your UA data import. 
  2. Select your preferred Dimensions and Metrics.
  3. On the Destination, fill in the Transfer Name, select a BigQuery project & Dataset, the database name, and the Write Mode for your data. Ensure that you select Append for Write Mode to ensure that you keep your historical data without replacing them with new refreshes or updates.
Configuring Destination for BigQuery transfers
  1. Next, create a Schedule or a transfer frequency to import, update, or refresh your data to your BigQuery Dataset. 
  2. Finally, click Save & Refresh Now to start your transfer. And that’s it!
How to transfer your Google Universal Analytics data into BigQuery using

Benefits of importing your Google Analytics historical data to BigQuery

Beyond keeping your historical data, and before we conclude, what are the other benefits of keeping your Universal Analytics data in BigQuery? Let’s check them out below:

  1. Google BigQuery can store your data from several other sources, including GA4, which can help you compare your Universal Analytics metrics against the new GA4 metrics, as well as other critical metrics for your company.
  2. GA4 does not backfill your data with UA metrics, and so, to avoid losing your important analytics data and reports, Google BigQuery is the best option to save your data.
  3. Not losing your past analytics data allows you to perform historical and long-term trend analysis to inform most of your business reports and decisions.
  4. Google BigQuery makes it easier to send your data to other powerful BI tools like Looker Studio or Power BI to help you create interactive reports for your data.

The best metrics and dimensions to import from Google Universal Analytics to BigQuery

Analytics data is not just the number of people visiting your site — it also includes crucial insights about your audience, who are your customers or prospects. So, even as you migrate from Google Universal Analytics to Google Analytics 4, what metrics and dimensions are the most important to export for your future references or analysis? 

If you are not sure which metrics or dimensions to export to BigQuery or want to expand your knowledge, this section will discuss all the crucial metrics that you must import to help you grow your business. 

While the list below does not provide an exhaustive list of all the metrics and dimensions, it provides a good start to help you focus on the right data. So, let’s get started.

Traffic Metrics 

One of the basic metrics that you should be interested in exporting to BigQuery to keep track of is how your website traffic. Google Universal Analytics analyses your traffic data fundamentally through the total number of visitors (sessions) or the number of unique visitors (users).

So, consider these metrics as they will keep a record of how your website acquires new visitors through the sessions metric or retains a loyal band of visitors through the users’ metrics.

Traffic sources

Learning which channels visitors are entering your site is another critical piece of information that you must not lose for your site. Understanding whether you received more traffic through organic search, direct traffic, referrals, or advertising campaigns will always remain relevant to inform your future efforts to improve your site’s traffic. 

Bounce Rate

Another key metric that you should consider exporting is information about the visitors who landed on particular pages of your website and departed right away. Probably, analyzing your bounce rates will help you understand the possible issues on your page that could be responsible for the bounces and prop you to keep identifying ways to keep your visitors engaged on your site.

Conversion Metrics

Keeping records of how your website visitors take specific measurable actions, like buying your products, filling out forms, downloading a resource, etc., is also crucial for the growth of your company. Conversions in Google Universal Analytics are known as Goals. 

The more you understand how visitors accomplish your desired goals, the more you can create even better campaigns to grow your ROI.

Landing Pages

Understanding your most popular pages where most of your audience lands will also be important to help you keep historical trends of your site. You should review the data about these pages to understand how they are impacting your audience as they travel down your sales funnel. You can also use this info to set up conversion-friendly elements on these pages to ensure that you are making good progress toward your goals.

Exit Pages

While landing pages are the most popular pages of your website where your audience often lands, the exit pages are where those pages where most of your audience terminates their journey on your website. Getting these insights could help you improve things and keep measuring them in the future to see changes. The metrics for exit pages are relevant, especially when your landing pages are also your exit pages, meaning that users frequently do not find what they expected on your site.

Content Metrics

Almost all of your audience will visit your site to find some information. And if your content makes it challenging for them to get the info, they will probably not stay on your site for long and may never return. Importing your UA content metrics to BigQuery will allow you to compare your future performance to your past performance and lets you see more growth opportunities.

Thus, you will understand which content engages your audience the most and increases your chances of converting them.

Note: Dataslayer provides over 200 metrics and dimensions to help you gather all your Universal Analytics historical data.

Save your Google Analytics Historical Data in BigQuery

Switching to the new Google Analytics 4 may be intimidating or challenging. However, keeping your historical data should not bother you. Exporting all your Universal Analytics data is now possible with a few clicks. 

Today, you can use Dataslayer for BigQuery to export your Google Analytics historical performance of your website as you prepare for GA4.