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)
-- 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
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
-- 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
Enjoying this post? Subscribe to the GA4BigQuery newsletter!
You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.