Dynamic date range on Google Analytics data in BigQuery

You have your Google Analytics data flowing into BigQuery, now you want to build some reports, or at least run queries on top of it. One of the first thing’s you’ll want to do it query across a date range.

As GA tables are nicely date stamped it’s fairly straightforward to work out how to run a query for yesterday, or last week using comma separated tables. However, what if you want to go a step further and feed this data into a visualization or DataStudio report? You want the data to update every day, but you don’t want to have to edit your query every day. You want a rolling date range!

The LegacySQL approach

In Legacy SQL this was easy to achieve with the TABLE_DATE_RANGE function, as seen in the example below:


SELECT date, totals.visits FROM (TABLE_DATE_RANGE([xxx:xxx.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))

This query injects a timestamp into the table name, in the same format as the GA tables, so is able to dynamically deliver the last 7 days of data.

If you wanted to include the current day also, you can adjust the Legacy SQL query to collect data from the intraday table too. See the below example taken from the BigQuery Cookbook:


SELECT date, SUM(totals.visits) visits, FROM (TABLE_DATE_RANGE([xxx.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -6, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))), (TABLE_DATE_RANGE([73156703.ga_sessions_intraday_], DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP())) GROUP BY date

StandardSQL

That’s all fine, but what if you’re trying to leave Legacy SQL behind, and have a need to replicate this in Standard SQL? Unfortunately TABLE_DATE_RANGE is unique to Legacy SQL and simply does not exist in Standard SQL.

The best replacement I have found to date is _TABLE_SUFFIX, it can be implemented in a similar way, to feed back data from a dynamic date range:


SELECT Date, totals.transactions FROM `xxx.xxx.ga_sessions_20*` AS t WHERE parse_date('%y%m%d', _table_suffix) between DATE_sub(current_date(), interval 7 day) and DATE_sub(current_date(), interval 1 day)

Note that the addition of the 20 before the wildcard is required to make this work correctly.

That’s it, rolling date ranges using Google Analytics data in BigQuery!

Note: project names have been removed from the above queries, you’ll need to replace xxx with your own details to make this sample code work on your data.

Leave a Reply