https://www.growthbook.io/ logo
#announcements
Title
# announcements
t

thankful-minister-34216

09/18/2023, 10:25 PM
Hello, what is the best way to implement Bounce rate as a metric for a standard GA4 / BigQuery connection?
Growthbook team - any advice here? I don't see any detail in the documentation... Surely bounce rate is something that is a fundamental metric for any A/B testing platform
b

brief-honey-45610

09/19/2023, 9:15 PM
Hi Tim, thanks for reaching out! I'm new to the GrowthBook team and am working on getting an answer for you about this
t

thankful-minister-34216

09/19/2023, 9:22 PM
Thank you @brief-honey-45610! Appreciate the update
b

brief-honey-45610

09/19/2023, 9:43 PM
Hi Tim, do you have a dedicated sessions table? Or are you trying to calculate Bounce Rate from a raw event stream?
The most straightforward approach would be to create a dedicated sessions table with duration, number of page views, etc.
t

thankful-minister-34216

09/19/2023, 9:44 PM
No, I only have the raw event stream currently. However I'm open to setting up additional tables, but not sure what the best approach is. If there is a recommendation that would be a big help! And I imagine having a documentation page for this eventually as I can't be the only person looking to use Bounce rate for A/B testing metrics
b

brief-honey-45610

09/19/2023, 9:44 PM
Indeed, you are not the only person who has asked about this recently 😉 Adding this to our docs is definitely on our list
t

thankful-minister-34216

09/19/2023, 9:45 PM
Makes sense! If you have a recommendation then I'm all ears. Or if you need somebody to be a guinea pig to try out some things I'm open to that too 🙂
b

brief-honey-45610

09/19/2023, 9:46 PM
The most straightforward approach would be to create a dedicated sessions table with duration, number of page views, etc. Then, to calculate bounce rate, I would group by session id and get a count for number of pages and filter the rows to only include bounces (when numPageViews < 2).
t

thankful-minister-34216

09/19/2023, 9:46 PM
I'm not very familiar with the GA / BQ connection. Would the sessions table be something set up in GA? Or would it be something like a view / stored procedure within BigQuery?
b

brief-honey-45610

09/19/2023, 9:48 PM
Let me ask about that
👍 1
t

thankful-minister-34216

09/20/2023, 4:26 PM
@brief-honey-45610 any update on this?
b

brief-honey-45610

09/20/2023, 4:49 PM
Hi Tim. I spoke with Jeremy and he suggested doing it all within GrowthBook using a custom SQL query for that particular metric (instead of a view or stored procedure in BigQuery).
t

thankful-minister-34216

09/20/2023, 5:23 PM
Thanks @brief-honey-45610! Would it be possible to get an example of a query like that? I'm not sure what that would look like
b

brief-honey-45610

09/20/2023, 7:25 PM
Hi Tim, I'll ask and see if anyone has an example handy. Since I recently joined the team I haven't made one for myself yet.
t

thankful-minister-34216

09/20/2023, 7:26 PM
no worries! Any guidance is appreciated - and hopefully can be reused for documentation in the future 😉
Hi @brief-honey-45610 - any luck with this query?
@brief-honey-45610 - checking in here again! 🙂
b

brief-honey-45610

09/22/2023, 6:07 PM
Hi Tim, I apologize for the delay. Thanks for hanging in there. Our Engineering team has been busy the past few days. I’ll need to come up with the SQL on my own. I should be able to do this later today
t

thankful-minister-34216

09/22/2023, 6:36 PM
Ok, thank you!
b

brief-honey-45610

09/23/2023, 1:36 AM
Hi Tim, I was able to find a SQL snippet for Bounce Rate. I'll paste it below.
Copy code
-- Site-Wide - Bounce Rate Metric
WITH __pageviews as (
  SELECT
    user_id as user_id,
    user_id as anonymous_id,
    received_at as timestamp,
    path as url
  FROM
    pages
),
__users as (
  -- Users visiting specific pages
  SELECT
    p.anonymous_id as user_id,
    MIN(p.timestamp) as actual_start,
    MIN(p.timestamp) + INTERVAL '72 hours' as conversion_end,
    MIN(p.timestamp) - INTERVAL '30 minutes' as session_start
  FROM
    __pageviews p
  WHERE
    p.timestamp >= '2021-10-27 00:00:00'
    AND p.timestamp <= '2022-01-25 00:00:00'
  GROUP BY
    p.anonymous_id
),
__metric as (
  -- Metric (Bounce Rate)
  SELECT
    m.anonymous_id as user_id,
    1 as value,
    m.date_end as actual_start,
    m.date_end + INTERVAL '72 hours' as conversion_end,
    m.date_end - INTERVAL '30 minutes' as session_start
  FROM
    sessions m
  WHERE
    m.num_pages = '1'
    AND m.date_end >= '2021-10-27 02:42:21'
    AND m.date_end <= '2022-01-28 03:12:21'
),
__distinctUsers as (
  SELECT
    u.user_id,
    MIN(u.conversion_end) as conversion_end,
    MIN(u.session_start) as session_start,
    MIN(u.actual_start) as actual_start
  FROM
    __users u
  GROUP BY
    u.user_id
),
__userMetric as (
  -- Add in the aggregate metric value for each user
  SELECT
    1 as value
  FROM
    __distinctUsers d
    JOIN __metric m ON (m.user_id = d.user_id)
  WHERE
    m.actual_start >= d.session_start
    AND m.actual_start <= d.conversion_end
  GROUP BY
    d.user_id
),
__overallUsers as (
  -- Number of users overall
  SELECT
    COUNT(*) as users
  FROM
    __distinctUsers
),
__overall as (
  SELECT
    COUNT(*) as count,
    AVG(value:: float) as mean,
    STDDEV(value) as stddev
  from
    __userMetric
),
__userMetricDates as (
  -- Add in the aggregate metric value for each user
  SELECT
    date_trunc('day', d.actual_start) as date,
    1 as value
  FROM
    __distinctUsers d
    JOIN __metric m ON (m.user_id = d.user_id)
  WHERE
    m.actual_start >= d.session_start
    AND m.actual_start <= d.conversion_end
  GROUP BY
    date_trunc('day', d.actual_start),
    d.user_id
),
__usersByDate as (
  -- Number of users by date
  SELECT
    date_trunc('day', actual_start) as date,
    COUNT(*) as users
  FROM
    __distinctUsers
  GROUP BY
    date_trunc('day', actual_start)
),
__byDateOverall as (
  SELECT
    date,
    COUNT(*) as count,
    AVG(value:: float) as mean,
    STDDEV(value) as stddev
  FROM
    __userMetricDates d
  GROUP BY
    date
)
SELECT
null as
  date,
  o.*,
  u.users
FROM
  __overall o
  JOIN __overallUsers u ON (1 = 1)
UNION ALL
SELECT
  o.*,
  u.users
FROM
  __byDateOverall o
  JOIN __usersByDate u ON (o.date = u.date)
ORDER BY
  date ASC
This comes from the Example App we offer once you're logged into the GrowthBook dashboard.
Clicking on the "Bounce Rate" link in the page shown in the screenshot above will take you to the detail page for that metric. Click on the teal "View Queries" button and a modal will appear with the SQL code for that metric.
t

thankful-minister-34216

09/27/2023, 7:05 PM
Hi @brief-honey-45610 Thanks for sending this, I'll look at this query. However I don't have the Example App as an option on my GrowthBook. do I need to do something to enable it?
@brief-honey-45610 It looks like this is querying from a table called
sessions
. Not sure if this was not clear but that is the table I'm trying to figure out how to create based on the GA Bigquery event data:
@brief-honey-45610 I was able to find another resource which helped compose this. For reference, he's a bounce metric ready for use in growthbook:
Copy code
SELECT
    user_id,
    user_pseudo_id AS anonymous_id,
    MIN(TIMESTAMP_MICROS(event_timestamp)) as timestamp,
    concat(user_pseudo_id,(select value.int_value FROM unnest(event_params) WHERE key = 'ga_session_id')) as session_id,
    countif(event_name = 'page_view') as views,
FROM
  *** your table ***
WHERE
    ((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}')
    OR (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
group by
  user_id,
  user_pseudo_id,
  session_id
HAVING views = 1
❤️ 1
17 Views