How to query multiple GA4 'events_' tables with '_table_suffix' in BigQuery

Every day of Google Analytics 4 data in BigQuery is stored in a separate table. If you only need data from one day (let's say January 3rd, 2021) the from clause in your query will look something like this:

select
    *
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_20210103`

But in most cases you will need to query a larger period of time. Enter _table_suffix. More details on the general usage here, but to be able to query multiple tables of GA4 data you only need to look at the example queries I present you here.

Note that you can use static dates, dynamic dates for a rolling period (the last 90 days) or combine static and dynamic dates. It is also possible to include the intraday table in your query.

All tables in data set

This is the easiest one. Just use the asterisk as a wildcard to match any table in the data set that starts with events_. Note: this will also include the intraday table (i.e. events_intraday_20210103, more on this below).

select
    *
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`

Static date range

When you perform an analysis on a static data range you should use fixed start and end dates. In this example we select January 3rd 2021 to February 3rd 2021.

select
    *
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    _table_suffix between '20210103' and '20210203'

Dynamic date range

In this example we select a rolling last 30 day period: today minus 30 days until and including yesterday.

select
    *
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 30 day))
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

Combination of static and dynamic dates

Usually I prefer a combination of a fixed start date and a dynamic end date (in this case: today minus one).

If new data is added to our data set it will automatically included in our query. In this example we select the data range January 3rd 2021 up to and including yesterday. BigQuery will figure out if the tables actually exist and will only return data for tables that match the conditions and do exist.

select
    *
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    _table_suffix between '20210103' 
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

Intraday tables

For every Google Analytics view that is exported to BigQuery, an events_intraday_ table will be exported multiple times a day as well. Notice the different format for historical data (yesterday and beyond):

events_yyyymmdd

versus intraday:

events_intraday_yyyymmdd

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 events_ and contains a date with the format yyyymmdd.

select
    *
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    regexp_extract(_table_suffix, r'[0-9]+') between format_date('%Y%m%d',date_sub(current_date(), interval 3 day))
    and format_date('%Y%m%d', current_date())

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

Now it's your turn!

I hope you've enjoyed this and feel a bit more confident to utilise your own Google Analytics data in BigQuery. Drop a line in the comments if you have any questions, feedback or suggestions related to this article.