BigQuery SQL & GA4: How to Query Multiple Tables Using _TABLE_SUFFIX

Select From All Tables

SELECT * FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`  

Select From Specific Dates

SELECT *
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX between '20230101' and '20230111'

Select Only Yesterday’s Data

SELECT *
FROM `raw.analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Note: sometimes yesterday’s data may not always be fully exported yet from GA4 and loaded into your raw table. So you can do a “2 day delay load” by selecting only the data from 2 days ago:

Select Data From 2 Days Ago

SELECT *
FROM `raw.analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY))