Custom date range query for ingesting data to data studio from big query

Question

I'm trying to create a GCP billing report for my org by fetching data from bigquery to data studio. Pretty new to sql programming in general, I've used the following query to visualize data in data studio.

SELECT
  project.name AS project,
  EXTRACT(MONTH FROM usage_start_time) AS month,
  ROUND(SUM(cost), 2) AS costs,
  ROUND(SUM((SELECT SUM(amount) FROM UNNEST(credits))), 2) AS credits
FROM `bqutil.billing.billing_dashboard_export`
GROUP BY project, month
ORDER by project, month

Now, i would like to add custom date range so that viewers can use it to get required report. However, the default custom range provided in in DS does not work. I'm from system admin background pretty new to bigquery and sql in general. Any help with the query would be appreciated.

Thanks

"the default custom range provided in in DS does not work" - why does is not work? what range are you expecting to get? — Felipe Hoffa
Can you elaborate a bit more, why it doesn't work ? Have you checked this documentation's topic about setting up a Date Range in Data Studio? — Alexandre Moraes
For example, i've changed the datasource for this report here datastudio.google.com/u/0/reporting/… and when i try changing the dates, it is applicable to pie chart and the graph but not the table. Please help. Pretty new to data studio and big query. — Neji hyuga
In the "Cost by Project" page, I changed the date filter to a few different ranges and every time all the views (the table, the graph and the pie chart) were updated and had consistent data. Can you help us by replication your problem in a way we can follow through? — Francesco Galletta

Alexandre Moraes Alexandre Moraes · Accepted Answer · 2020-05-13T10:43:47

I have performed some tests and I found out the reason why, you are not being able to filter your data by date.

Since the dataset you are using is public I was able to use it to perform what you aim. I will describe my steps to achieve it:

Go to Data Studio >Create(upper left in the console)>Data source
Click on BigQuery
Click Custom query(left side of the window)
Type your query inside the query editor under Enter custom query(on the right side of the window)
On the upper right side of the screen click Connect
Click on FIELDS and check if all the fields have the appropriate type. Please double check the date field, it must be with date format
Create a table with the desired fields. In my case, I used two tables with the following characteristics:

Table 1:

Data source: BigQuery

Date range dimension:month - Please DO NOT forget this field

Dimensions: project, credits and month

Metric: SUM(costs)

1st sort: month

2nd sort: costs

And another table:

Table 2: heat map

Data source: BigQuery

Date range dimension:month - Please DO NOT forget this field

Dimensions: project

Metric: COUNT(project), AVG(cost), MIN(cost), MAX(cost)

1st sort: cost

After configuring the table as described above, click at Date range filter in the tool bar (above the canvas). Drop it at your canvas
Now change the dates accordingly, the values should change

I found out that the dates filter do no work when Date range dimension is not set to you data field in the tables you want to filter by date.

Furthermore, I have also used the following query to test the date range filter. Below is the query:

SELECT
project.name AS project,
cast(FORMAT_TIMESTAMP("%Y-%m-%d" , usage_start_time) as date) AS start_date,
ROUND(SUM(cost), 2) AS costs,
ROUND(SUM((SELECT SUM(amount) FROM UNNEST(credits))), 2) AS credits
FROM `bqutil.billing.billing_dashboard_export`
GROUP BY project, start_date
ORDER by project, start_date

Notice that I just changed the month field to start_date as YYYYMMDD.

Custom date range query for ingesting data to data studio from big query

1 Answers