Join 2,600+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks
Already have an account? Sign inA 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 unnest
and 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 geo dimensions
- geo.continent
- geo.sub_continent
- geo.country
- geo.region
- geo.city
- geo.metro
-- comments
in the example query below for names and definitions and copy the part you need from the select
clause. Make sure that you also add any additional conditions (i.e. with
, from
, where
, group by
, having
and 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
-- geo.continent (dimension | the continent from which events were reported, based on ip address)
geo.continent,
-- geo.sub_continent (dimension | the subcontinent from which events were reported, based on ip address)
geo.sub_continent,
-- geo.country (dimension | the country from which events were reported, based on ip address)
geo.country,
-- geo.region (dimension | the region from which events were reported, based on ip address)
geo.region,
-- geo.city (dimension | the city from which events were reported, based on ip address)
geo.city,
-- geo.metro (dimension | the metro from which events were reported, based on ip address)
geo.metro
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))