Learn business growth with Google Analytics 4 Forums Google Analytics 4 Measuring Page Views and Exits in Google Big Query with GA4 Integration Reply To: Measuring Page Views and Exits in Google Big Query with GA4 Integration

  • Samuel

    8 May 2023 at 10:20 pm

    To count the instances where the last event of a session occurred on a specific page (a.k.a “Exits”), you can make use of the user_pseudo_id and event_timestamp fields to find the last event in each session. The approach involves grouping by user_pseudo_id and then ordering matters by event_timestamp to find the last event for each user.

    You would then join this result back to your main table to retrieve the page_path of these last events and count the occurrences for each. The query could look something like this:

    WITH last_event AS (
    SELECT user_pseudo_id, MAX(event_timestamp) as last_event_timestamp
    FROM MY_ga4_dataset.events_*
    GROUP BY user_pseudo_id

    SELECT event_params.value.string_value AS page_path, COUNT(*) AS exits
    FROM MY_ga4_dataset.events_*
    JOIN last_event
    ON events.user_pseudo_id = last_event.user_pseudo_id AND events.event_timestamp = last_event.last_event_timestamp
    WHERE event_name = ‘page_view’ AND event_params.key = ‘page_location’
    GROUP BY page_path
    ORDER BY exits DESC

    In this query, the last_event subquery finds the timestamp of each user’s last event. In the main query, you join the last_event subquery to your main table to find those instances where the main table’s event_timestamp matches the last_event_timestamp from the subquery, indicating it is the last event in the session. You filter for 'page_view' and 'page_location' accordingly.

    Please remember to replace MY_ga4_dataset.events_* with your actual dataset name. Also, ensure the dates are suitable in events_* part if you are querying over a certain date range.