Hey there, We are looking to add a metric (using GA4/ Bigquery) for

Exit %

Does anyone have advice? I would love to use fact tables, but I think this may be easier to implement as sql

(fact tables are SQL)

Yes, sorry What I mean is that I am sturggling with exactly what data makes sense to add.

WITH session_events AS (
  SELECT
    TIMESTAMP_MICROS(event_timestamp) AS timestamp,
    user_id,
    user_pseudo_id AS anonymous_id,
    event_name,
    geo.country,
    CASE device.category 
      WHEN 'tablet' THEN 'mobile' 
      WHEN 'smart-tv' THEN 'desktop' 
      ELSE device.category 
    END AS device_category,
    traffic_source.source,
    traffic_source.medium,
    traffic_source.name AS campaign,
    REGEXP_EXTRACT(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), 
      r'http[s]?:\/\/?[^\/\s]+\/([^?]*)'
    ) AS page_path,
    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING) AS session_id,
    event_value_in_usd,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') / 1000 AS engagement_time,
    (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'customer_type') AS customer_type,
    ROW_NUMBER() OVER (
      PARTITION BY user_pseudo_id, session_id 
      ORDER BY event_timestamp DESC
    ) AS session_rank,
    MAX(event_timestamp) OVER (PARTITION BY user_pseudo_id) AS last_event_timestamp -- Get user's latest event
  FROM
    `ga4-explorations.analytics_383872824.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"}}'))
)
SELECT 
  *,
  CASE 
    WHEN session_rank = 1 
      AND event_name = 'page_view' 
      AND TIMESTAMP_MICROS(last_event_timestamp) < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 MINUTE) 
    THEN TRUE 
    ELSE FALSE 
  END AS is_exit
FROM session_events
ORDER BY timestamp DESC;

ya, exit rate may require a self-join to get the last page views

Also I should clarify that the debate is fact tables vs a targeted legacy sql metric

oh, i see

Thanks! I'll poke around a bit. I suspect the fact table will work for us, I just was looking to make sure this wasn't solved by someone in a clearier way

I see, perhaps others here can answer then

Jarred, how do you define Exit %?

To be honest, this is a vague ask from my side. Our head of SEO wrote the issue asking for this metric. However, I am looking basically for "bounce rate". The experiments we plan to run are changing some marketing text and see not only if users stay longer (already have this metric) but also if users don't leave. I guess one way to proxy the bounce rate would be the average page views

Yeah, so if you want to define bounce rate as only looking at one page, then there's a pretty easy way to do it with a page view fact table.

Thanks that's much cleaner

Let me know how it goes!

I really appreciate you all taking a look. iFixit has been singing your praises

Nice :)

Totally! At this point we are using most if not all the features of fact tables / metrics. I had just forgotten about being clever with users with a single row

It's the User Filter feature that really makes it possible, since it lets you filter AFTER counting/aggregating by user.

I reached out for a Rubber duck and you helped so much. I figured I was going about it the wrong way

big fans of ifixit too