In order to perform queries with Google BigQuery you will need to have Google BigQuery Job User permissions on at least one project, as well as Google BigQuery Data Viewer on the data you wish to query.
Connect Google BigQuery
Database connectors like Google BigQuery are an optional add-on, available for purchase on all plans.
Once purchased, navigate to the campaign where you'd like Google BigQuery to be integrated, then hover over the sidebar on the left and click Integrations.
Search for Google BigQuery in the search field in the upper right hand corner, then click the Google BigQuery card.
Next click Connect New Account to move on to the connection page.
From here, click Connect New Account at the bottom to authorize a new connection.
This will launch a Google authorization window. Select and authorize the account with permission and access to Google BigQuery in this window.
After authorizing the connecting you'll be brought back to the connection page where you can select the correct account from those now available.
Click the circle to the left of the account, then Connect in the bottom left to finalize the connection. With Google BigQuery connected you can start adding Google BigQuery widgets to reports and dashboards.
Use Google BigQuery widgets
First open the report or dashboard in edit mode, then scroll down until you find Google BigQuery in the widget sidebar on the right. Click Google BigQuery to expand the widget menu.
Once expanded all available widget types will be listed. Locate the widget format you want to add then click, hold, and drag the widget over to the section and release to add the widget.
Unlike other integration widgets Google BigQuery must be told via query what data to pull and display.
After adding a Google BigQuery widget the edit widget sidebar will automatically update to the Data tab where you need to add SQL, or you can click the widget then the Data tab on the right.
AgencyAnalytics uses the same query editor as Google BigQuery to simplify your usage, you'll see a familiar estimate of how many bytes will be processed when your queries run.
After entering SQL in the query field click Update Query to update the widget. The data we queried is now displayed.
If there's an issue with the SQL an error message will display above the Update Query button specifying the nature of the error.
Test Google BigQuery Widgets
Using the example SQL below you can test the process of adding and updating Google BigQuery widgets using public data.
Simple Query Example
To test a simple query, try using the SQL below that pulls the top 10 names from the public data set “usa_names” and sums the number of names. You can use this query in a stat, table, pie chart or column chart.
SELECT
name,
SUM(number) AS total
FROM
`bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY
name
ORDER BY
total DESC
LIMIT
10;
Time Series Example
To get started with time series data, try a simple query from the chicago_crime public data set. You’ll notice a few requirements in order to display an effective time series chart:
The result set MUST have a field called “date” that is of data type “DATE”. Here, we ensure the date field is of type DATE by extracting the date from the field using the “DATE” function in BigQuery.
The result set must be grouped by that same “date” field
Although not explicitly required, it's helpful to sort the result set by date so the graph doesn’t jump back and forth between different dates.
To test this out, first add an Area chart to your dashboard or report, then use the below query to display a chart of robberies per date using sample data.
SELECT
DATE(date) as date,
count(*) as Robberies
FROM
`bigquery-public-data.chicago_crime.crime`
WHERE
primary_type = 'ROBBERY'
AND
$__dateFilter(date)
GROUP BY
date
ORDER BY
date ASC;
To simplify syntax and to allow for dynamic parts, like date range filters, the query can contain macros.
$__dateFilter(dateColumn)
The above macro will be replaced by a date range filter using the specified column name. For example, dateColumn >= ‘2023-10-01 AND dateColumn <= ‘2023-10-31