1. Home
  2. Knowledge Base
  3. Universal Analytics Backup
  4. Setting Up BigQuery for use with Analytics Canvas

How to setup BigQuery for use with Analytics Canvas

This article is written in the context of setting up BigQuery for backing up Universal Analytics data using the Analytics Canvas UA Backup Utility, however the general principals apply to any use of BigQuery with Analytics Canvas. 

This article relates to the Universal Analytics Backup Utility offered by Analytics Canvas. 

Setting up your Google Cloud Account + BigQuery

Prior to exporting data to BigQuery, you must first have a Google Cloud Account.  Google offers a free trial, and a "Sandbox" mode.

If you are just experimenting with BigQuery, it is ok to use these. However, if you want to retain your data, you must have a billing account linked to your Project, or your data will expire (be deleted) after 60 days. 

⚠️

If your BigQuery dataset was created while in Sandbox mode, your data will expire after 60 days. Upgrading to a paid account will not change this setting! You must recreate the dataset, or manually change the setting on all tables.

⚠️

The UA backup utility will not export data to a dataset with a table expiry setting in place. You must recreate the dataset by deleting it and creating it again with the exact same name.

The most effective way to get started is to enroll in a free trial AND create a billing account.  

While it is possible to create an account and start with BigQuery in 'Sandbox' mode, the method outlined below will ensure success and prevent your data from being deleted after 60 days. 

Image

Add a credit card at this stage to ensure you have a billing account enabled.  The billing account is required, even if you are in a free trial, to ensure that you are not creating Datasets and Tables that are in Sandbox mode. 

While is Sandbox mode, tables will delete on a rolling 60 day basis. 

Image

If you are a business, or an agency working with commercial clients, create an Organization

Image

After adding your billing information, including adding a credit card that will not be charged until you've consumed your free credits, proceed to the GCP survey and click "Done".

Image

You will likely land on the BigQuery API screen.  Click Enable to begin using BigQuery.

Image

After enabling BigQuery, click the BigQuery button, or go directly to https://console.cloud.google.com/bigquery.  

You will be in a project labelled "My First Project".  You can either build your datasets there, or create and label a new Project

To create a new project, click the filter that says "My First Project", then click the button that says "New Project"

Image

To create a dataset, which will hold a series of related tables, such as from your Universal Analytics data backup, or your GA4 BigQuery data, click the three dots next to the Project name, then choose "Create Dataset"

Image

Give your dataset a name, select the region, then click "Create Dataset". 

The Data Location (Region) is important - it will determine where your data is stored and processed by BigQuery.  Multi-regions have a lower cost for storage and processing that specific regional locations. 

Image

That's it!  You are now ready to use your Google BigQuery Dataset!

Getting out of Sandbox Mode / Linking a Billing Account to your Project

If your Project is in Sandbox mode, by default, your data will be deleted after 60 days.  All projects that do not have a billing account associated with them will be created in the BigQuery Sandbox. 

Image

To enable a billing account and get your project out of the Sandbox, go to https://console.cloud.google.com/billing/

Image

To enable a billing account and get your project out of the Sandbox, go to https://console.cloud.google.com/billing/.  After creating a billing account (if you haven't already), go to My Projects > click the 3 dots next to the project you want to enable, then click "Change Billing".

Image

With Billing enabled for the project, go back to the Dataset in BigQuery, click on it, then select "Edit Details".

ImageImage

The project will no longer be in sandbox mode and you can safely export your data to it.

Image

Setting Permissions on your Google Cloud Projects and BigQuery Datasets

Once things are setup, in order to add permissions to Projects and Datasets so that they can be used in Applications, the logged-in user must have the appropriate permissions. 

Permissions that work include BigQuery Admin and Owner. 

Creating Datasets for use with Analytics Canvas

Once you have permissions to create a dataset, you may wish to create them for your use in Analytics Canvas, such as a Universal Analytics Backup, to hold data from other marketing projects, etc. 

Was this article helpful?

Related Tutorials/Video