Skip to content

Instantly share code, notes, and snippets.

@markrittman

markrittman/ga4_visitor_journey.sql

Last active July 11, 2023 10:01
Download ZIP

Revisions

  1. markrittman renamed this gist Jul 17, 2021. 1 changed file with 0 additions and 0 deletions.
    0 visitor_journey.sql → ga4_visitor_journey.sql
    View file
    File renamed without changes.
  2. markrittman created this gist Jul 17, 2021.
    44 changes: 44 additions & 0 deletions visitor_journey.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,44 @@
    SELECT
    user_pseudo_id,
    geo.city,
    TIMESTAMP_MICROS(MIN(event_timestamp)) AS session_start_ts,
    traffic_source.name AS channel,
    traffic_source.medium AS medium,
    traffic_source.source AS source,
    (
    SELECT
    value.int_value
    FROM
    UNNEST(event_params)
    WHERE
    key = 'ga_session_number') AS session_number,
    COUNT((
    SELECT
    value.string_value
    FROM
    UNNEST(event_params)
    WHERE
    event_name = 'page_view'
    AND key = 'page_title')) AS user_pages_count,
    STRING_AGG(DISTINCT (
    SELECT
    value.string_value
    FROM
    UNNEST(event_params)
    WHERE
    event_name = 'page_view'
    AND key = 'page_title' )) AS distinct_pages,
    STRING_AGG((
    SELECT
    value.string_value
    FROM
    UNNEST(event_params)
    WHERE
    event_name = 'page_view'
    AND key = 'page_title'
    ORDER BY
    event_timestamp)) AS page_journey
    FROM
    `ra-development.analytics_277223877.events_*`
    GROUP BY
    1,2,4,5,6,7