Dynamic Dates in Custom Queries (Data Studio + BigQuery)
Dynamic dates from Data Studio in BiqQuery SQL.
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.
Видео Dynamic Dates in Custom Queries (Data Studio + BigQuery) канала Metric Mogul
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.
Видео Dynamic Dates in Custom Queries (Data Studio + BigQuery) канала Metric Mogul
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
Scaling Interactive and Insightful Dashboards with Data Studio and BigQuery (Cloud Next '18)Setting Targets in Google Data Studio // How to set targets and benchmarks in your reports5 Data Studio Functions for Calculated Fields you should knowGoogle Data Studio - How to create a dashboard for coronavirusMicrosoft Power BI vs. Google Data Studio | 13 Minute ComparisonModern Data Warehousing with BigQuery (Cloud Next '19)Data Warehousing With BigQuery: Best Practices (Cloud Next '19)Data Studio Update: Controls and Parameters: The first lookBigQuery Tips: Nested and Repeated Fields and How GOJEK Builds Data Warehouses (Cloud Next '19)Reimagine Data Warehousing: How The Home Depot is Using BigQuery to Scale (Cloud Next '19)Data Studio - Filters and Sorting in DataTable, Scorecard, GeoMap and ChartsGoogle BigQuery TutorialData Studio Siêu Cấp Cơ BảnHow to Use Advanced Date Ranges in Data Studio (New!)Google Data Studio Data Blending Tutorial With ExamplesGoogle Data Studio Tutorial for Beginners🔥How to use the Data Studio Case FunctionMake Your Data Work For You: Master Dashboards with Data Studio and Sheets (Google Cloud Next '17)Using Data Studio with BigQuery (Felipe Hoffa & Yufeng Guo)How to use the DATE_TRUNC Function in SQL (vs. TO_CHAR)