https://www.growthbook.io/ logo
#ask-questions
Title
# ask-questions
b

bumpy-finland-5739

02/22/2024, 6:24 PM
Hey GB team, struggling to setup a metric for bounce rate / engagement rate. Using GA4 and BigQuery can anyway give me some direction or provide an example? Thanks
l

lively-van-5677

02/27/2024, 1:01 AM
I'm also trying to figure this one out. I came across these 2 posts. Maybe you will find them useful. • https://www.ga4bigquery.com/sessions-dimensions-metrics-ga4/https://www.bounteous.com/insights/2016/02/11/querying-google-analytics-data-bigquery
b

bumpy-finland-5739

02/27/2024, 2:44 PM
Thank you Jay I will take a look and continue to figure this out. If I make progress I’ll be sure to share
f

freezing-ram-13953

03/01/2024, 3:37 PM
I’m pretty new to this as well, so this may not be 100% correct, but here’s what I came up with to get the engagement time in seconds per user. This uses the
engagement_time_msec
param as per: https://support.google.com/analytics/answer/11109416?hl=en Metric type: count Title: Engaged Time - Seconds (72 hour window) Desc: Number of seconds user was active on the site after experiment exposure Metric SQL: NB: you will need to substitute
bigquery-YOURDB
and
analytics_YOURTABLE
with your db/dataset.
Copy code
SELECT
  user_id,
  user_pseudo_id as anonymous_id,
  TIMESTAMP_MICROS(event_timestamp) as timestamp,
  value_param.value.int_value as value
FROM
  `bigquery-YOURDB`.`analytics_YOURTABLE`.`events_*`,
  UNNEST(event_params) AS value_param
WHERE
  value_param.key = 'engagement_time_msec'
  AND ((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}') OR
       (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
User Value Aggregation:
Copy code
CAST(SUM(value) / 1000 AS INT64)
And here’s one for engaged sessions… Metric type: binomial Title: Engaged Sessions (72 hour window) Desc: Sessions that last longer than 10 seconds, have a conversion event, or at least 2 pageviews or screenviews Metric SQL: NB: you will need to substitute
bigquery-YOURDB
and
analytics_YOURTABLE
with your db/dataset.
Copy code
SELECT 
  user_id,
  user_pseudo_id as anonymous_id,
  TIMESTAMP_MICROS(event_timestamp) as timestamp,
FROM
  `bigquery-YOURDB`.`analytics_YOURTABLE`.`events_*`,
  UNNEST(event_params) AS value_param
WHERE
  value_param.key = 'session_engaged'
  AND value_param.value.string_value = '1'
  AND ((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}') OR
       (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
Cheers Rob Ps - A bounce is the opposite of this - ie: shorter than 10 seconds, no conversion event, and one pageview / screenview. You can change this metric to measure bounces by changing:
Copy code
AND value_param.value.string_value = '1'
to
Copy code
AND value_param.value.string_value = '0'
Obviously, if you reverse it, the GB metric goal will want to be “decrease the conversion rate”!
b

bright-psychiatrist-91661

04/11/2024, 8:38 PM
@lively-van-5677 @freezing-ram-13953 @bumpy-finland-5739 where any of you able to figure this out? I made an attempt on this by creating two metrics: 1 counting session_start and one counting bounce sessions with session_start as the denominator: session_start:
Copy code
SELECT
  user_pseudo_id AS anonymous_id,
  TIMESTAMP_MICROS(event_timestamp) AS timestamp,
  1 AS value
FROM
  `yourdb.events_*`
WHERE
  (
    (
      _TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}'
    )
    OR (
      _TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'
    )
  )
  AND event_name = 'session_start'
bounce_rate:
Copy code
SELECT
  user_pseudo_id AS anonymous_id,
  TIMESTAMP_MICROS(event_timestamp) AS timestamp,
  1 AS value
FROM
  `yourdb.events_*` AS events
WHERE
  (
    (
      _TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}'
    )
    OR (
      _TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'
    )
  )
  AND event_name = 'session_start'
  AND NOT EXISTS (
    SELECT 1
    FROM `yourdb.events_*` AS bounce_check
    WHERE bounce_check.user_pseudo_id = events.user_pseudo_id
    AND bounce_check.event_name = 'page_view'
    AND TIMESTAMP_MICROS(bounce_check.event_timestamp) > TIMESTAMP_MICROS(events.event_timestamp)
    LIMIT 1
  )
In data analysis the overall metrics seem close to what I see in GA4 but when I use these metrics on a test the numbers are very low. Needing some help here.
12 Views