Hey GB team struggling to setup a metric for bounce rate eng GrowthBook Users #ask-questions

Hey GB team, struggling to setup a metric for boun...

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

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

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

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)

freezing-ram-13953

03/01/2024, 4:26 PM

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'

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”!

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
  )

bright-psychiatrist-91661

04/11/2024, 8:41 PM

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.

24 Views

Open in Slack

Previous Next