BigQuery + GA4: Create GA4 Metrics using SQL in BigQuery — Part II

Photo by Myriam Jessier on Unsplash

This is Part 2 of BigQuery + GA4: Create GA4 Metrics using SQL in BigQuery. In this, we’ll try to understand some advanced GA4 metrics and how we can write them in SQL.

6. Engaged Sessions

An engaged session is a session that lasts longer than 10 seconds, has a conversion event, or has at least 2 pageviews on the website. In bigQuery, we get a parameter session_engaged, which has a binary value. 1 if it is an engaged session, 0 otherwise. We now need to get the number of sessions which has the value of session_engaged as 1.

SELECT count(
      DISTINCT concat(user_pseudo_id, 
                         (SELECT value.int_value FROM UNNEST(event_params) 
                          WHERE key = "ga_session_id")
                      )
              ) as engaged_sessions

FROM `<account_id>.analytics_12345.events_*`

WHERE 
        (SELECT value.int_value 
        FROM UNNEST(event_params) 
        WHERE key = "session_engaged") = 1

7. Engagement Rate

Engagement rate can be defined as the number of engaged sessions divided by the total number of sessions. We discussed above how can we get the engaged sessions, and in Part 1 of this, we calculated the number of sessions. Let's use those to get the engagement rate.

--CTE for engaged sessions
with engaged_sessions_cte as (
SELECT count(
      DISTINCT concat(user_pseudo_id, 
                         (SELECT value.int_value FROM UNNEST(event_params) 
                          WHERE key = "ga_session_id")
                      )) as engaged_sessions
FROM `<account_id>.analytics_12345.events_*`

WHERE 
        (select value.int_value 
        FROM UNNEST(event_params) 
        WHERE key = "session_engaged") = 1
),
 
--CTE for number of sessions
num_of_sessions_cte as(
  SELECT count(
      DISTINCT concat(user_pseudo_id, 
                         (SELECT value.int_value FROM UNNEST(event_params) 
                          WHERE key = "ga_session_id")
                      )
            ) as num_of_sessions 
FROM `<account_id>.analytics_12345.events_*`
)


--Calculating the engagement rate upto 2 decimal places
SELECT 
ROUND((eng_sessions.engaged_sessions/sessions.num_of_sessions)*100,2)
FROM
num_of_sessions_cte AS sessions, engaged_sessions_cte AS eng_sessions

8. Conversion Rate

There are two types of conversion rates in GA4- User Conversion Rate and Session Conversion Rate.

8.1 User Conversion Rate tells about the percentage of users who triggered a conversion event. This is calculated by the number of unique users who triggered a conversion divided by the total number of unique users.

Since bigQuery doesn't have a column that tells whether the event is a conversion in GA4 or not, we need to do some manual effort here. In the WHERE clause, we need to add all the events that are conversions in GA4 with an OR condition.

--CTE for converted users
with converted_users as(
SELECT count(
      DISTINCT user_pseudo_id) as num_of_converted_users

FROM `<account_id>.analytics_12345.events_*`

WHERE
--Event names which are conversions in GA4 interface 
event_name = 'contact_us_form_submit'
OR
event_name = 'click_to_call'
OR
event_name = 'abc'
),

--CTE for total users
total_users as(
 SELECT count(
      DISTINCT user_pseudo_id) as total_users

FROM `<account_id>.analytics_12345.events_*`
 )

SELECT 
ROUND((converted_users.num_of_converted_users/total_users.total_users)*100,2)
FROM
converted_users, total_users

8.2 Session Conversion Rate is the percentage of sessions in which a conversion event is triggered. This is calculated by the number of unique sessions in which a conversion was triggered divided by the total number of unique sessions.

The query will be similar to the user conversion rate, only this time we’ll be using sessions instead of users.

--CTE for converted sessions
with converted_sessions as(
SELECT count(
      DISTINCT concat(user_pseudo_id, 
                         (SELECT value.int_value FROM UNNEST(event_params) 
                          WHERE key = "ga_session_id")
                      )
            ) as num_of_converted_sessions

FROM `<account_id>.analytics_12345.events_*`

WHERE
--Event names which are conversions in GA4 interface 
event_name = 'contact_us_form_submit'
OR
event_name = 'click_to_call'
OR
event_name = 'abc'
),

--CTE for total sessions
total_sessions as(
 SELECT count(
      DISTINCT concat(user_pseudo_id, 
                         (SELECT value.int_value FROM UNNEST(event_params) 
                          WHERE key = "ga_session_id")
                      )
            ) as total_sessions 
FROM `<account_id>.analytics_12345.events_*`
 )


SELECT 
ROUND((converted_sessions.num_of_converted_sessions/total_sessions.total_sessionss)*100,2)
FROM
converted_sessions, total_sessions

9. Landing Page

This particular dimensional value is not available directly in bigQuery, and hence we need to write SQL to fetch this.

We have a parameter with the name entrances in bigQuery, which is a binary value. 1, if the page is a landing page and 0 otherwise.

SELECT 
      (SELECT value.string_value 
        FROM UNNEST(event_params) 
        WHERE key="page_location")

FROM `<account_id>.analytics_12345.events_*`

WHERE
      (SELECT value.int_value 
      FROM UNNEST(event_params) 
      WHERE key="entrances") = 1

Thank you!

Write me at diksha.madan112@gmail.com or ping me on Linkedin: https://www.linkedin.com/in/diksha-madan/

Signing off.