Google BigQuery

Overview

Google BigQuery is Google’s fully managed, petabyte scale, low-cost enterprise data warehouse for analytics. Here are some things you can expect from the Google BigQuery/Grow integration.

NOTE: This data source is only available for accounts on the Professional plan. If you would like to upgrade your plan, talk to your Grow account manager or visit your account's billing page.

How to Connect

Before you get started you will need your Google BigQuery Project ID, Client Email and Private Key or you can upload a JSON file with your project ID, email, and private key.

1. Log in to your Grow account and click Add Metric. Select Google BigQuery from the list of data sources, then press the blue connect button.
2. The most straightforward way to connect your BigQuery account is to click “Fill in manually” on the authorization page. You’ll need the BigQuery project ID you want to connect, the email address associated with the BigQuery account, and your private key.
3. If you don’t know what your private key is or how to get it, Google has a helpful article on how to do so. You have to do a bit of scrolling through the page to get to what you need, so we’ve copied the important stuff here:

To generate a private key in JSON or PKCS12 format in Google BigQuery:

1. Open the list of credentials in the Google Cloud Platform Console.
2. Click Create credentials.
3. Select Service account key. A Create service account key window opens.
4. Click the drop-down box below Service account, then click New service account.
5. Enter a name for the service account in Name.
6. Use the default Service account ID or generate a different one.
7. Select the Key type: JSON or P12.
8. Click Create. A Service account created window is displayed and the private key for the Key type you selected is downloaded automatically. If you selected a P12 key, the private key's password ("notasecret") is displayed.
9. Highlight and copy your private key.

You can then come back to Grow and paste the key into the Private Key field. You'll need to also enter your email associated with BigQuery and your Project ID.

Alternatively, if you have the know-how and would prefer to upload a JSON file with your project ID, email, and private key, that will work as well.

When you've entered in this information, press the Submit button.

And that’s it! You are good to go.

Endpoints

Below is a list of the reports you can pull from BigQuery, along with a description of the fields available with each report.

• Datasets: All datasets associated with projects under the connected BigQuery account, including kind, dataset ID, dataset reference, and project ID.
• Tables: All tables for the selected dataset, including kind, table ID, project, dataset, table name, type, and unix epoch creation time.
• Table Data: All table data for the selected dataset and table. The fields returned here will vary based on the specifics of the datasets created with BigQuery.
• Custom SQL Query: Write custom SQL queries to hone in on specific fields when building a metric.

FAQs + Tips and Tricks

Unix Epoch Dates

Some datatable information fields include creation dates in unix epoch format. This will just look like a string of random numbers. Luckily, there’s a quick way to fix that, either through Grow’s table date format function in the edit metric screen, or with a relatively simple SQL query.