-
Measuring Page Views and Exits in Google Big Query with GA4 Integration
Hey there, I’m trying to work with Google BigQuery linked to GA4 and I’m hoping to count not just the number of views, but also the “Exits”. You see, I’ve got my code all set up to count views by page path, but I’m having a bit of a tricky time defining “Exits”. To clarify, when I say “Exits”, I’m talking about instances where the last event recorded for a session happened to be on a specific screen, or said in another way, sessions that ended on a given page path.
Here’s the code I’ve been playing with:
SELECT event_params.value.string_value AS page_path, COUNT(*) AS page_views FROMMY_ga4_dataset.events_<span class="hljs-operator">*</span>, UNNEST(event_params) AS event_params WHERE _table_suffix BETWEEN '20230207' AND '20230207' AND event_name = 'page_view' AND event_params.key = 'page_location' GROUP BY page_path ORDER BY page_views DESCWould really appreciate if you could suggest a solution for this.
Oh, and if it helps, I’ve also attached my Schema details below:
fullname mode type description event_date NULLABLE STRING event_timestamp NULLABLE INTEGER event_name NULLABLE STRING event_params REPEATED RECORD event_previous_timestamp NULLABLE INTEGER event_value_in_usd NULLABLE FLOAT event_bundle_sequence_id NULLABLE INTEGER event_server_timestamp_offset NULLABLE INTEGER user_id NULLABLE STRING user_pseudo_id NULLABLE STRING privacy_info NULLABLE RECORD user_properties REPEATED RECORD user_first_touch_timestamp NULLABLE INTEGER user_ltv NULLABLE RECORD device NULLABLE RECORD geo NULLABLE RECORD app_info NULLABLE RECORD traffic_source NULLABLE RECORD stream_id NULLABLE STRING platform NULLABLE STRING event_dimensions NULLABLE RECORD ecommerce NULLABLE RECORD items REPEATED RECORD event_params.key NULLABLE STRING event_params.value NULLABLE RECORD event_params.value.string_value NULLABLE STRING event_params.value.int_value NULLABLE INTEGER event_params.value.float_value NULLABLE FLOAT event_params.value.double_value NULLABLE FLOAT privacy_info.analytics_storage NULLABLE STRING privacy_info.ads_storage NULLABLE STRING privacy_info.uses_transient_token NULLABLE STRING user_properties.key NULLABLE STRING user_properties.value NULLABLE RECORD user_properties.value.string_value NULLABLE STRING user_properties.value.int_value NULLABLE INTEGER user_properties.value.float_value NULLABLE FLOAT user_properties.value.double_value NULLABLE FLOAT user_properties.value.set_timestamp_micros NULLABLE INTEGER user_ltv.revenue NULLABLE FLOAT user_ltv.currency NULLABLE STRING device.category NULLABLE STRING device.mobile_brand_name NULLABLE STRING device.mobile_model_name NULLABLE STRING device.mobile_marketing_name NULLABLE STRING device.mobile_os_hardware_model NULLABLE STRING device.operating_system NULLABLE STRING device.operating_system_version NULLABLE STRING device.vendor_id NULLABLE STRING device.advertising_id NULLABLE STRING device.language NULLABLE STRING device.is_limited_ad_tracking NULLABLE STRING device.time_zone_offset_seconds NULLABLE INTEGER device.browser NULLABLE STRING device.browser_version NULLABLE STRING device.web_info NULLABLE RECORD device.web_info.browser NULLABLE STRING device.web_info.browser_version NULLABLE STRING device.web_info.hostname NULLABLE STRING geo.continent NULLABLE STRING geo.country NULLABLE STRING geo.region NULLABLE STRING geo.city NULLABLE STRING geo.sub_continent NULLABLE STRING geo.metro NULLABLE STRING app_info.id NULLABLE STRING app_info.version NULLABLE STRING app_info.install_store NULLABLE STRING app_info.firebase_app_id NULLABLE STRING app_info.install_source NULLABLE STRING traffic_source.name NULLABLE STRING traffic_source.medium NULLABLE STRING traffic_source.source NULLABLE STRING event_dimensions.hostname NULLABLE STRING ecommerce.total_item_quantity NULLABLE INTEGER ecommerce.purchase_revenue_in_usd NULLABLE FLOAT ecommerce.purchase_revenue NULLABLE FLOAT ecommerce.refund_value_in_usd NULLABLE FLOAT ecommerce.refund_value NULLABLE FLOAT ecommerce.shipping_value_in_usd NULLABLE FLOAT ecommerce.shipping_value NULLABLE FLOAT ecommerce.tax_value_in_usd NULLABLE FLOAT ecommerce.tax_value NULLABLE FLOAT ecommerce.unique_items NULLABLE INTEGER ecommerce.transaction_id NULLABLE STRING items.item_id NULLABLE STRING items.item_name NULLABLE STRING items.item_brand NULLABLE STRING items.item_variant NULLABLE STRING items.item_category NULLABLE STRING items.item_category2 NULLABLE STRING items.item_category3 NULLABLE STRING items.item_category4 NULLABLE STRING items.item_category5 NULLABLE STRING items.price_in_usd NULLABLE FLOAT items.price NULLABLE FLOAT items.quantity NULLABLE INTEGER items.item_revenue_in_usd NULLABLE FLOAT items.item_revenue NULLABLE FLOAT items.item_refund_in_usd NULLABLE FLOAT items.item_refund NULLABLE FLOAT items.coupon NULLABLE STRING items.affiliation NULLABLE STRING items.location_id NULLABLE STRING items.item_list_id NULLABLE STRING items.item_list_name NULLABLE STRING items.item_list_index NULLABLE STRING items.promotion_id NULLABLE STRING items.promotion_name NULLABLE STRING items.creative_name NULLABLE STRING items.creative_slot NULLABLE STRING -
2 Replies
-
Sure, the provided SQL query does two primary things – it shows the previous and next page that each user visited and it identifies the entry and exit points for each user session.
We’re sorting events by their timestamp per unique user session. For each page view event, we get the previous and next page path.
If there’s no previous page path for a page view event, it means that event is where the user entered, so we label this as ‘(entrance)’. If there’s no next page path for a page view event, it means the user exited at this event, so we label this as ‘(exit)’.
So with this query, you can identify the users’ navigation patterns and their entry and exit points in your app or website.
-
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_idandevent_timestampfields to find the last event in each session. The approach involves grouping byuser_pseudo_idand then ordering matters byevent_timestampto 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
FROMMY_ga4_dataset.events_*
GROUP BY user_pseudo_id
)SELECT event_params.value.string_value AS page_path, COUNT(*) AS exits
FROMMY_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_eventsubquery finds the timestamp of each user’s last event. In the main query, you join thelast_eventsubquery to your main table to find those instances where the main table’sevent_timestampmatches thelast_event_timestampfrom 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 inevents_*part if you are querying over a certain date range.
Log in to reply.