CODEWITHSUNDEEP
google-bigquerylooker-studio
Zak Smith-Sanga
Zak Smith-Sanga

Reputation: 53

Use DataStudio to specify the date range for a custom query in BigQuery, where the date range influences operators in the query

I currently have a DataStudio dashboard connected to a BigQuery custom query.

That BQ query has a hardcoded date range and the status of one of the columns (New_or_Relicensed) can change dynamically for a row, based on the dates specified in the range. I would like to be able to alter that range from DataStudio.

I have tried:

Here is the query I have in BQ: 

SELECT t0.New_Or_Relicensed, t0.Title_Category FROM (WITH
  report_range AS
  (
    SELECT
      TIMESTAMP '2019-06-24 00:00:00' AS start_date,
      TIMESTAMP '2019-06-30 00:00:00' AS end_date
  )
SELECT
  schedules.schedule_entry_id AS Schedule_Entry_ID,
  schedules.schedule_entry_starts_at AS Put_Up,
  schedules.schedule_entry_ends_at AS Take_Down,
  schedule_entries_metadata.contract AS Schedule_Entry_Contract,
  schedules.platform_id AS Platform_ID,
  platforms.platform_name AS Platform_Name,
  titles_metadata.title_id AS Title_ID,
  titles_metadata.name AS Title_Name,
  titles_metadata.category AS Title_Category,
  IF (other_schedules.schedule_entry_id IS NULL, "new", "relicensed") AS New_Or_Relicensed
FROM
  report_range, client.schedule_entries AS schedules
JOIN client.schedule_entries_metadata 
  ON schedule_entries_metadata.schedule_entry_id = schedules.schedule_entry_id 
JOIN
  client.platforms
  ON schedules.platform_id = platforms.platform_id
JOIN
  client.titles_metadata
  ON schedules.title_id = titles_metadata.title_id
LEFT OUTER JOIN
  client.schedule_entries AS other_schedules
  ON schedules.platform_id = other_schedules.platform_id
  AND other_schedules.schedule_entry_ends_at < report_range.start_date
  AND schedules.title_id = other_schedules.title_id
WHERE
  ((schedules.schedule_entry_starts_at >= report_range.start_date AND
   schedules.schedule_entry_starts_at <= report_range.end_date) OR
  (schedules.schedule_entry_ends_at >= report_range.start_date AND
   schedules.schedule_entry_ends_at <= report_range.end_date))
) AS t0 LIMIT 100;

Essentially - I would like to be able to set the start_date and end_date from google data studio, and have those dates incorporated into the report_range that then influences the operations in the rest of the query (that assign a schedule entry as new or relicensed).

Upvotes: 1

Views: 10633

Answers (2)

oneflash
oneflash

Reputation: 21

I had a similar issue where I wanted to incorporate a 30 day look back before the start (@ds_start_date). In this case I was using Google Analytics UA session data and using table suffix in my where clause. I was able to calculate a date RELATIVE to the built in data studio "string" dates by using the following: ...

WHERE
        _table_suffix BETWEEN
        CAST(FORMAT_DATE('%Y%m%d', DATE_SUB (PARSE_DATE('%Y%m%d',@DS_START_DATE), INTERVAL 30 DAY)) AS STRING)
        AND
        CAST(FORMAT_DATE('%Y%m%d', DATE_SUB (PARSE_DATE('%Y%m%d',@DS_END_DATE), INTERVAL 0 DAY)) AS STRING)

Upvotes: 0

John Dow
John Dow

Reputation: 806

Have you looked at using the Custom Query interface of the BigQuery connector in Data Studio to define start_date and end_date as parameters as part of a filter.

Your query would need a little re-work... 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:

enter image description here

Resources:
Introducing BigQuery parameters in Data Studio https://www.blog.google/products/marketingplatform/analytics/introducing-bigquery-parameters-data-studio/

Running parameterized queries
https://cloud.google.com/bigquery/docs/parameterized-queries

Upvotes: 8

Related Questions