04. How to create a data source with a custom query in Google Data Studio?

In the article How to use a custom query to access your BigQuery data? you have learned how to create a custom query. In this tutorial we will now show how to use this custom query to create a data source in the Google Data Studio. This data source can then be used to create interesting reports.

Parameterize Custom Query

Google Data Studio supports the concept of parameterized custom queries. Parameterized queries have the advantage that they are more efficient than general queries, since only the data that is really necessary is queried. In an SQL query, placeholders are set which are then filled with values at runtime. A parameter could be a date field, for example. If the user changes a date filter in a report, the correct values are inserted into the query when the query is run.

In the previous article, step 1 explains how to query a fact table and filter it by partitioning key. At this point you will insert the parameters.

From:

SELECT * FROM `ingenious-169318.insights_999.finance_conversion`
WHERE DATE(trackedAt) between
  '2020-01-01' and
  '2020-01-01'
LIMIT 100;

To:

SELECT * FROM `ingenious-169318.insights_999.finance_conversion`
WHERE DATE(trackedAt) between
  parse_date('%Y%m%d', @DS_START_DATE) and
  parse_date('%Y%m%d', @DS_END_DATE);

The WHERE part of the SQL query is filtered by the field "trackedAt". This is the partitioning key. This value must lie between @DS_START_DATE and @DS_END_DATE. These two parameters are placeholders used by Google Data Studio. But also other analysis software uses exactly these placeholders for time periods. Furthermore, it is important that the LIMIT statement is removed.

Create a data source in Google Data Studio

Navigate to Google Data Studio to the “Data sources”. Here you can create a new data source.

You select the BigQuery Connector from the Google Connectors. In the next step, select "Custom Query" on the left-hand side and choose the billing project. This is the project over which the costs for the queries are billed. The Custom SQL Query is entered in the field on the right side. As you can see, the query contains the period placeholders. Now you only have to select "Enable date parameters". Then you have to click on "Connect" in the upper right corner.

More information can be found in the official Google Data Studio documentation.
https://support.google.com/datastudio/answer/6370296

The next step is to configure the fields that the SQL statement queries to work with Google Data Studio. In most cases you can work with the automatic configuration. This configuration can also be adjusted later. You can directly create a report with the data source by clicking the "Create Report" button in the upper right area.

More information can be found in the official Google Data Studio documentation.
https://support.google.com/datastudio/answer/6402048