https://www.growthbook.io/
Join Slack
Hey GB team, struggling to setup a metric for bounce rate / engagement rate. Using GA4 and BigQuery ...
# ask-questions
b
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
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
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
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
@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.
106 Views
Open in Slack
PreviousNext
Powered by

GrowthBook Users

Open source platform for stress free deployments, measured impact, and smarter decisions.

Powered by