A lot of Google Analytics 4 dimensions and metrics can be derived straight from the tables without performing calculations on the data. With the help of
parse for dates or timestamps we can do the trick.
Other dimensions and metrics that you might need are a bit harder to access. You will have to (re)calculate them, which can require some serious SQL-skills.
While designing the course Query GA4 Data In Google BigQuery I've learned a lot about best practices to calculate dimensions and metrics. To share this knowledge with you I will provide a combined query for default dimensions and metrics, and a single example query for every non-default dimension or metric.
As always, please let me know if you have any feedback or suggestions to improve the quality of this content.
Default device dimensions
Default app dimensions
Default web dimensions
Default stream & platform dimensions
-- commentsin the example query below for names and definitions and copy the part you need from the
selectclause. Make sure that you also add any additional conditions (i.e.
order by) that are necessary to calculate the results correctly. E.g, the query below is ungrouped, so every row corresponds to an event and may contain duplicate rows.
select -- device.category (dimension | the device category (mobile, tablet, desktop)) device.category as device_category, -- device.mobile_brand_name (dimension | the device brand name) device.mobile_brand_name, -- device.mobile_model_name (dimension | the device model name) device.mobile_model_name, -- device.mobile_marketing_name (dimension | the device marketing name) device.mobile_marketing_name, -- device.mobile_os_hardware_model (dimension | the device model information retrieved directly from the operating system) device.mobile_os_hardware_model, -- device.operating_system (dimension | the operating system of the device) device.operating_system, -- device.operating_system_version (dimension | the os version) device.operating_system_version, -- device.vendor_id (dimension | idfv (present only if idfa is not collected)) device.vendor_id, -- device.advertising_id (dimension | advertising id/idfa) device.advertising_id, -- device.language (dimension | the os language) device.language, -- device.is_limited_ad_tracking (dimension | the device's limit ad tracking setting) device.is_limited_ad_tracking, -- device.time_zone_offset_seconds (dimension | the offset from gmt in seconds) device.time_zone_offset_seconds, -- device.browser (dimension | the browser in which the user viewed content) device.browser, -- device.browser_version (dimension | the version of the browser in which the user viewed content) device.browser_version, -- device.web_info.browser (dimension | the browser in which the user viewed content) device.web_info.browser as web_browser, -- device.web_info.browser_version (dimension | the version of the browser in which the user viewed content) device.web_info.browser_version as web_browser_version, -- device.web_info.hostname (dimension | the hostname associated with the logged event) device.web_info.hostname, -- app_info.id (dimension | the package name or bundle id of the app) app_info.id, -- app_info.version (dimension | the app's versionname (android) or short bundle version) app_info.version, -- app_info.install_store (dimension | the store that installed the app) app_info.install_store, -- app_info.firebase_app_id (dimension | the firebase app id associated with the app) app_info.firebase_app_id, -- app_info.install_source (dimension | the source that installed the app) app_info.install_source, -- stream_id (dimension | the numeric id of the stream) stream_id, -- platform (dimension | web or app platform) platform from -- change this to your google analytics 4 export location in bigquery `ga4bigquery.analytics_250794857.events_*` where -- define static and/or dynamic start and end date _table_suffix between '20220901' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))