How to query an intraday table (and combine it with daily tables)

For every Google Analytics view that is exported to BigQuery, a ga_sessions_intraday_ table will be exported multiple times a day as well. Let’s see how this works:

Within each dataset, a table is imported for each day of export. Daily tables have the format “ga_sessions_YYYYMMDD”. Intraday data is imported approximately three times a day. Intraday tables have the format “ga_sessions_intraday_YYYYMMDD”. During the same day, each import of intraday data overwrites the previous import in the same table. When the daily import is complete, the intraday table from the previous day is deleted. For the current day, until the first intraday import, there is no intraday table. If an intraday-table write fails, then the previous day’s intraday table is preserved. Data for the current day is not final until the daily import is complete. You may notice differences between intraday and daily data based on active user sessions that cross the time boundary of last intraday import. (source)

Example query

-- this query will return no intraday data as there is no intraday data available in the sample data set

select
  _table_suffix as table_suffix,
  count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as sessions
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_intraday_20160801`
where
  totals.visits = 1
group by
  table_suffix

Combine intraday table with daily tables

The easiest way to include intraday data in your query as well as historical data is use the wildcard in combination with a wider _table_suffix filter. With this condition it will include any table in the data set that starts with ga_sessions_ and contains a date with the format YYYYMMDD.

In this case we'll use a dynamic end date, like described in this tutorial.

Note: make sure your data set doesn’t contain any other tables with a title that starts with ga_sessions_!

Example query

-- this query will return no intraday data as there is no intraday data available in the sample data set

select
  _table_suffix as table_suffix,
  count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as sessions
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where
  regexp_extract(_table_suffix,'[0-9]+') between '20160801'
  and format_date('%Y%m%d', current_date())
  and totals.visits = 1
group by
  table_suffix
order by
  table_suffix desc