Data Studio
6.1K members online now
6.1K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

How to use dynamic date range in bigQuery custom query ressource ?

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi there,

 

I'm searching for the way to use dynamic setted date range in bigQuery custom queries with TABLE_DATE_RANGE to avoid too large data consumption in join instructions.

 

Do you know if there are some ways to write query like that :

SELECT
*
FROM (TABLE_DATE_RANGE([project:dataset.table], TIMESTAMP({DATE_BEGIN}), TIMESTAMP({DATE_END})))

 

I was unable to find documentation about that.

 

Thanks a lot for your help.

How to use dynamic date range in bigQuery custom query ressource ?

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Hi @Thierry L

 

You can use the following query line for a rolling last 7 days, edited to suit your requirements:

 

FROM (TABLE_DATE_RANGE([***.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))

 

Hope that helps,

Ben

How to use dynamic date range in bigQuery custom query ressource ?

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi,

 

I have not found an answer to your question in the forum. And I'm having the same problem too.

So, I logged in issue into Bug Tracking: https://issuetracker.google.com/issues/68494871

 

If you are still interested in this feature - please, rate it. And hope we will see it implemented Smiley Happy

 

Best regards,

Artem

How to use dynamic date range in bigQuery custom query ressource ?

Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

For anyone interested in the original problem, I have published a more detailed guide on dynamic date ranges in BigQuery on my blog:

 

http://singularbean.co.uk/dynamic-date-range-on-google-analytics-data-in-bigquery/

 

Ben