So lets cut down the title into Bite-sized chunks, okay?
What is Google Bigquery?
Google BigQuery is a warehouse for analytics data. It’s a place where you can:
House your data for $0.02 per gigabyte (equivalent of 256 MP3 files).
Query your data for $5.00 per 5 terabytes of queries (about 1 million 5-minute songs).
Create nice (and free!) dashboards in Google Data Studio (which is what this article about).
Basically, it can store data – a lot of data. But then, even Google Analytics is dedicated to collecting and linking business data. Then, what is the difference?
With Google Analytics, you can control only the data collection and analysis.However, with Bigquery, you can have more control over every stage of the analytics infrastructure.
So, why are there custom queries in Bigquery, which are connected to Google Data Studio?
Basically, Bigquery contains a huge amount of data sets stored in tables. In order to get access to the data, queries are built. There are standard queries to take out sets of data, but sometimes it is imperative that a custom query be designed.
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 parameter 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
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:
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 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, name 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.
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:
+61413785621