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

  • Measuring Page Views and Exits in Google Big Query with GA4 Integration

    Posted by Levi on 14 May 2022 at 11:57 am

    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
    FROM
      MY_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 DESC
    

    Would 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  
    
    Samuel replied 1 year, 3 months ago 3 Members · 2 Replies
  • 2 Replies
  • Oliver

    Member
    6 April 2023 at 8:45 pm

    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.

  • Samuel

    Member
    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:

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

Log in to reply.