Google Data Studio: how to create time series chart with custom Big Query query

2
votes

I have a Data Studio report with a Time Series added. The data source is from a custom query using the Big Query connector:

select user_dim.app_info.app_version, count(1) as count 
from [my_app_domain_ANDROID.app_events_20160929] 
group by 1

According to the Data Studio documentation at: https://support.google.com/360suite/datastudio/answer/6370296?hl=en

BigQuery supports querying across multiple tables, where each table has a single day of data. The tables have the format of YYYYMMDD. When Data Studio encounters a table that has the format of YYYYMMDD, the table will be marked as a multi-day table and only the name prefix_YYYYMMDD will be displayed in the table select.

When a chart is created to visualize this table, Data Studio will automatically create a default date range of the last 28 days, and properly query the last 28 tables. You can configure this setting by editing the report, selecting the chart, then adjust the Date Range properties in the chart's

However, in the Time Series Properties DATA tab, there no no valid "Time Dimension" to select. According to the documentation, I should not need to select a Time Dimension. It should query the right table automatically.

enter image description here

Something I am not understanding yet?

1
time-seriesgoogle-bigquerygoogle-datagoogle-data-studio

1 Answers

4
votes

There are 2 issues with the query in the question:

  1. To get a time series, you'll need to add a time based column to the custom query.

For example:

SELECT created_at, COUNT(*) c
FROM [githubarchive:day.20160930]
WHERE type='WatchEvent'
GROUP BY 1
  1. Data Studio won't do the 28 day expansion with custom queries. To get the expansion featured in the documentation, you need to point to an actual table (and Data Studio will figure out the prefix and date expansion).

I left a working example at: