Reply To: Measuring Page Views and Exits in Google Big Query with GA4 Integration

[Samuel]

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:

`sql
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.