Dynamic Dates in Custom Queries (Data Studio + BigQuery)

Dynamic Dates in Custom Queries (Data Studio + BigQuery)

I’m going to show you how to get dates from DataStudio’s date picker into your BiqQuery custom queries so you never have to manually change a date again.

First of all you need a date picker element in your report, and a widget which uses BiqQuery as a data source. In my example I’ve got a single metric displaying sessions, which currently doesn’t update when I change the date picker.

To make this happen, open the data source and edit your custom query.

This sample query calculates sessions. I’m using the between command to select only the GA export tables who suffix is between my start and end date, and therefore only counting sessions between those dates.

Replace the hardcoded dates with the @DS_START_DATE and @DS_END_DATE parameters. These are automatically populated whenever the date picker is changed.

Note that the parameters values are strings. If your query requires a date object or a string format other than year, month, day, you’ll have to use a combination of the PARSE_DATE and FORMAT_DATE function to convert them.

Save your query, and return to the dashboard. And that’s it! When you change your date picker, you’re query will re-run using the dates selected.

Ed Brocklebank (aka Metric Mogul) is an analytics and digital marketing consultant. He helps business of all sizes become more data-driven through measurement, strategy and activation. He works as a Strategic Analytics Director at Jellyfish in London, as well as delivering training on behalf of Google and formerly General Assembly.