Using a Google Cloud Service Account with Analytics Canvas
The recommended method for accessing Google BigQuery and Google Cloud Storage within applications is to use a Google Cloud Service Account, which belongs to an application rather to an individual user. This article shows you how to access your data in Google BigQuery using your Service Account for Analytics Canvas.
With this access, you can query your GA4 Property and any other Projects that you authorize. The billing is directed to your Analytics Canvas subscription, where it is governed by your subscription quota. As such, your fixed monthly subscription fee will never be exceeded.
Find your Analytics Canvas Service Account under Admin > Access your own BigQuery
Creating a Google Cloud Project and a BigQuery Dataset
If you're new to BigQuery, follow the steps below. If you've already setup a Project and Dataset and simply need to read from it, skip to the next section.
Projects organize Google Cloud resources, such as users, a set of APIs (including BigQuery and Cloud Storage), and billing, under a single umbrella. They contain one or more Google BigQuery Datasets, which hold one or more Tables of data.
If you are creating a datamart, a UA backup, or simply looking to write data into BigQuery, you will need a Project and a Dataset to write into.
- Login to https://console.cloud.google.com/bigquery
- Create a Google Cloud Project or select from an existing one. * (it is recommended that you enable billing before proceeding, otherwise your tables may expire after 60 days) *
- Create a Dataset to contain the data you wish to write into BigQuery
- Give your dataset a name (Dataset ID), then select a location and click "Create Dataset"
You now have a Project and Dataset where you can store data. Next is to allow applications to access your dataset, and finally you will allow Analytics Canvas to read from and / or write into the dataset.
Ensuring your Google Cloud Project is ready to be used by an Application
Certain APIs must be authorized with your Google Cloud Platform Project before you can create a Service Account and use it to access BigQuery or Google Cloud Storage.
In order to continue, you will need access to the Google Cloud Console for your organization, and the specific Project or Projects that you will be working on. Specifically, you will need the ability to grant access to Google BigQuery Projects and Datasets through BigQuery and IAM. If you do not have access, share this article with someone who does.
- To see and display your projects and the permissions associated with your Service Account, enable the Cloud Resource Manager API (click the link to open this API in your browser after logging in to the Google Cloud Console).
- If you will be connecting to BigQuery, the BigQuery API must be enabled
- If you will be connecting to Cloud Storage, you must enable the Cloud Storage API
With the above APIs enabled, you are now able to connect to the dataset with an application. Follow the steps below to access it with Analytics Canvas.
Accessing BigQuery with Analytics Canvas Online
To access your data in BigQuery, you must grant permission to the Service Account for your Analytics Canvas subscription.
You can limit access to specific Datasets or even specific tables within a BigQuery Project, and you can provide either Read-Only or Read/Write access to Datasets and Tables.
The steps outlined below show you how to read and write from a single Dataset or Table. You can either repeat the steps for each Dataset and Table you wish to access, or you can grant full permissions to the Service Account at the Project level in the IAM section of the Google Cloud Console.
There are 2 steps to this process:
1) Allow Analytics Canvas to see the Project that the Dataset is in
2) Provide Analytics Canvas with READ and / or WRITE access to the Dataset
Permission to see the BigQuery Project in Analytics Canvas
- In Analytics Canvas Online, find the Service Account under Admin > Access your own BigQuery and copy it.
- Go to https://console.cloud.google.com and login, then select the Project that contains the Dataset or Table you'd like to view in Canvas.
- From the menu on the left, go to IAM & Admin > IAM.
- On the IAM page, click +Grant Access
- Under New Principals, paste the service account address that was copied in step 1.
- Under Role, select the role that you require. BigQuery Read Session User is the lowest level of access required that allows you to see the Project, Dataset, and Tables in Analytics Canvas.
** To grant access to an entire Project instead of at the Dataset and Table level (so that you can read from the entire project and write into all datasets and tables in the same project), set the role as BigQuery Data Editor at the Project level.
Permission to R/W from the BigQuery Dataset / Table in Analytics Canvas
- Go to BigQuery and select either the Dataset or Table you wish to have access to in Analytics Canvas, then click Sharing and select Permissions.
- Add the Analytics Canvas Service account and assign it a Role.
- BigQuery Data Viewer is the lowest level of access that only allows users to Read from the Dataset or Table.
- BigQuery Data Editor is the minimum permission required to Write into a BigQuery Dataset or Table
No additional steps are required. Once you have provided the appropriate access to the Service Account for your subscription, it just takes a up to 10 minutes before you can access your BigQuery account to both read and write data using Analytics Canvas Online.
In Analytics Canvas, if you authorized a dataset that is linked to a GA4 Property, you will see the property under both the Google Analytics connector for GA4 BigQuery, where you can use a wizard to generate the SQL for your query, and under the BigQuery SQL connector where you can write your own SQL.
Troubleshooting if you can't connect
BigQuery datasets are located in specific data locations. In order to connect to a dataset, you must create your Canvas or your UA Backup in the same data location as the BigQuery dataset you're connecting to.
You can find the Data location under Dataset Info in the BigQuery Console.
You can create a Canvas in any BigQuery data location. If you don't already have the location setup, go to Admin > BigQuery Quota and Location > Add Location
Then create the Canvas in the same region as the BigQuery dataset.
Once you have enabled the APIs above and verified the data location, go back to Canvas and:
- in the main canvas, drag and drop a BigQuery block onto the Canvas, click "Refresh All" if no tables are shown.
- in the GA4 BigQuery import block, click "Refresh Properties" and "Scan" if your properties are not shown
- In the UA backup, on the Select BigQuery Dataset step, click "Refresh All" if no datasets are shown
If you cannot see the dataset, please take screenshots of your configuration after each step above and send that over to support@analyticscanvas.com.