If you’re one of the many Data Studio users writing custom queries for BigQuery, you can now run parameterized queries. This provides better customization and interaction options to your users while making your reports faster.
When connecting to BigQuery from Data Studio you can use special date parameters or define your own named parameters as part of a custom query. Parameters in custom queries introduce two key benefits: queries can be dynamically updated from the report - no need to create new data sources; this works even if the report user does not have edit access to the data source. You can optimize query cost and gain dashboard performance improvements since less data is passed from BigQuery to Data Studio for parameterized queries.
Creating parameterized custom queries
Let's say you're interested in analyzing word usage by corpus for a selected set of Shakepeare's works. The following BigQuery Public Dataset, bigquery-public-data.samples.shakespeare,is available to carry out this analysis:
To allow report editors to choose which corpus to analyze from Shakespeare’s works you can use the Custom Query interface of the BigQuery connector in Data Studio to define corpus as a parameter as part of a filter. You can define the type of UI element for the parameter (e.g., text input, single select, checkbox, etc.) and provide default values.
In the following example, the corpus parameter has been defined as a single-select dropdown with Hamlet as the default value along with other works as options such as Othello, King Lear, etc.
A BigQuery custom query with a custom corpus parameter
What’s really cool is that once you’ve defined the configuration, report editors will then be able to choose a specific corpus to analyze by using the dropdown from the parameters section of the report property panel:
E.g. The corpus parameter options in the
Data Studio property panel.
Using date parameters
Prior to date parameters, custom queries for date sharded or partitioned tables could not be limited to a date range based on a report’s date control. Instead, your custom query would have to fetch all rows for all dates, leaving Data Studio to do the job of filtering for the date range selected by the report user. The result is slower and less efficient reports.
With date parameters, you can use the reserved start and end date parameters as part of a custom query. When report users select a date range for analysis the dates selected will automatically be included as part of your custom query, resulting in a much more efficient query and fetching only the rows needed for the requested date range.
The following example custom query uses the @DS_START_DATE and @DS_END_DATE parameters as part of a filter on the creation date column of a table. The records produced by the query will be limited to the date range selected by the report user, reducing the number of records returned and resulting in a faster query:
A BigQuery custom query using start and end date parameters
The standard Data Studio date settings and controls will determine the date values for your custom query. A report editor can set a default date or add a date control to a report and the start and end dates for your query will change based on the report date control.
In both cases, named and date parameters offer a more efficient way to retrieve data from a single BigQuery data source while giving your report users flexible options to analyze different data.
Try it out!
To learn more about how parameters work review data source parameters and connecting to BigQuery.
As you have a chance to experiment with parameters, send us feedback or give us a shout out at @googleanalytics.
