The queries below can be used to recreate essential Google Analytics 4 (GA4) user metrics within BigQuery. These metrics provide valuable insights into your user base and their interactions with your website or app.
Jump to:
- Total users
- Active users
- New users
- Returning users – Coming Soon
- User engagement – Coming Soon
- Average engagement time per session – Coming Soon
- Total purchasers – Coming Soon
- User conversion rate – Coming Soon
Important Note: In all the queries provided below, make sure to replace the placeholders within the WHERE clause with the specific values for the table you are working with. For example, if you want to extract data for January 31st 2021 from the e-commerce sample dataset, use the following values:
- <project> should be replaced with bigquery-public-data
- <dataset> should be replaced with ga4_obfuscated_sample_ecommerce
- <table> should be replaced with events_20210131
Ensure that you substitute these placeholders with your actual project, dataset, and table names when executing the queries.
Calculate total users
Each user tracked by GA4 is assigned a unique pseudonymous ID, which is stored in the user_pseudo_id field. So to calculate Total users, we need to do a count of distinct user_pseudo_id.
SELECT
COUNT(DISTINCT user_pseudo_id) AS total_users
FROM `<project>.<dataset>.<table>`Calculate active users
To calculate Active users we will again count distinct user_pseudo_id, but only when the engagement_time_msec is greater than 0 or the session_engaged parameter = 1.
SELECT
COUNT(DISTINCT CASE WHEN (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') > 0 OR (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1' THEN user_pseudo_id END) AS active_users
FROM `<project>.<dataset>.<table>`Calculate new users
New users is calculated as a count of distinct user_pseudo_id where the event_name = “first_visit” or “first_open”.
SELECT
COUNT(DISTINCT CASE WHEN event_name IN ('first_visit', 'first_open') THEN user_pseudo_id END) AS new_users
FROM `<project>.<dataset>.<table>`