Skip to main content

GOV.UK GA4 flattened tables

The GA4 flattened tables contain GOV.UK GA4 data processed into a flat format.

These tables are created from the raw data exported into BigQuery and contain most, but not all, of the same fields. This flattened dataset is easier and more efficient to query, so should be used for most analysis and reporting.

Access

Everyone with a @digital.cabinet-office.gov.uk email address (all GDS staff) has access to the data by default.

In addition, everyone who requests and is granted access to the GOV.UK GA4 data will be given access to query this flattened dataset. More information can be found in our GA access policy.

Location

This data can be found in BigQuery in the ga4-analytics-352613.flattened_dataset dataset. Each daily table is sharded in the format flattened_daily_ga_data_YYYYMMDD.

For more information on our Google Cloud Platform projects, see our GCP project documentation.

Production process

The code is exectuted via Dataform every morning at 8am UTC and it is stored in the ‘GA4 flattener’ repository.

Schema

field name type mode description
event_date STRING NULLABLE The date when the event was logged, formatted as YYYYMMDD
user_pseudo_id STRING NULLABLE The pseudonymous ID for a user on a particular client
event_name STRING NULLABLE The name of the event
event_timestamp INTEGER NULLABLE The time (in microseconds, UTC) when the event was logged
unique_session_id STRING NULLABLE The user_pseudo_id concatenated with the ga_session_id to create a unique session ID
page_title STRING NULLABLE The page title
page_location STRING NULLABLE The full URL with the protocol, hostname, page path and query string
cleaned_page_location STRING NULLABLE The page path (the page_location without the protocol, hostname, and any query string
ga_sessionid INTEGER NULLABLE A session ID generated by GA4. This corresponds to the time the session started and is not necessarily unqiue
ga_session_number INTEGER NULLABLE The number of sessions that a user has started up to the current session e.g. ‘5’ for the user’s fifth session
primary_publishing_organisation STRING NULLABLE The organisation that published the content e.g. ‘Home Office’
page_referrer STRING NULLABLE The address of the webpage where a user clicked a link that sent them to the current page
cleaned_page_referrer STRING NULLABLE The path of the page referrer (the page_referrer without the protocol, hostname, and any query string
content_id STRING NULLABLE The content attribute of the <meta name="govuk:content-id"> tag
browse_topic STRING NULLABLE The content attribute of the <meta name="govuk:section"> tag
publishing_app STRING NULLABLE The content attribute of the <meta name="govuk:publishing-application"> tag e.g. ‘collections-publisher’
public_updated_at STRING NULLABLE The date of the last public update (the ‘public_updated_at’ field within the Content API and content attribute of the <meta name="govuk:public-updated-at"> tag)
updated_at STRING NULLABLE The date of the last internal update (the ‘updated_at’ field within the Content API and content attribute of the <meta name="govuk:updated-at"> tag)
first_published_at STRING NULLABLE The original publish date (the ‘first_published_at’ field within the Content API and content attribute of the <meta name="govuk:first-published-at"> tag)
taxonomy_all_ids_DEPRECATED STRING NULLABLE The content attribute of the <meta name="govuk:taxon-ids"> tag e.g. ‘a7f3005b-a3cd-4060-a127-725accb54f2e,65a25d2c-a0e5-4283-921d-c928babfb6e4’
status_code INTEGER NULLABLE The HTTP response status code
withdrawn STRING NULLABLE Default value of ‘false’, ‘true’ if <meta name="govuk:withdrawn" content="withdrawn"> tag is present
document_type STRING NULLABLE The content attribute of the <meta name="govuk:format"> tag
history STRING NULLABLE Default value of ‘false’, ‘true’ if <meta name="govuk:content-has-history" content="true"> tag is present
taxonomy_main_id STRING NULLABLE The content attribute of the <meta name="govuk:taxon-id"> tag
taxonomy_all_DEPRECATED STRING NULLABLE The content attribute of the <meta name="govuk:taxon-slugs"> tag
taxonomy_main STRING NULLABLE The content attribute of the <meta name="govuk:taxon-slug"> tag
taxonomy_level_1 STRING NULLABLE The content attribute of the <meta name="govuk:themes"> tag
full_taxonomy STRING NULLABLE Deprecated - the taxonomy path parts, concatenated
full_taxonomy_ids STRING NULLABLE Deprecated - the taxonomy ID path parts, concatenated
rendering_app STRING NULLABLE The content attribute of the <meta name="govuk:rendering-app"> tag
organisations STRING NULLABLE Publishing organisation IDs - the content attribute of the <meta name="govuk:analytics:organisations"> tag e.g. ‘<D25>’
session_engaged STRING NULLABLE
schema_name STRING NULLABLE The content attribute of the <meta name="govuk:schema-name"> tag
method STRING NULLABLE The method with which the user clicked on the link e.g. ‘primary click’. Populated for navigation and information_click events only
engagement_time_msec INTEGER NULLABLE The time the user has been engaged in milliseconds
event_campaign STRING NULLABLE The campaign information associated with this event
content_language STRING NULLABLE The language the content is written in
type STRING NULLABLE The type of feature the user interacted with e.g. accordion, footer etc
event_source STRING NULLABLE The source information associated with this event
session_source STRING NULLABLE The source information associated with this session (the first source of the session)
link_text STRING NULLABLE The text of the link clicked (sent with navigation and information_click events)
term STRING NULLABLE
link_url STRING NULLABLE The href attribute of a link (sent with navigation and information_click events)
event_medium STRING NULLABLE The medium information associated with this event
session_medium STRING NULLABLE The medium information associated with this session (the first medium of the session)
link_domain STRING NULLABLE The destination domain of a link (sent with navigation and information_click events)
event_previous_timestamp INTEGER NULLABLE The time (in microseconds, UTC) the event was previously logged on the client
event_bundle_sequence_id INTEGER NULLABLE The sequential ID of the bundle in which these events were sent to GA4
event_server_timestamp_offset INTEGER NULLABLE Timestamp offset between collection time and upload time in microseconds
user_id STRING NULLABLE
user_first_touch_timestamp INTEGER NULLABLE The time (in microseconds) when the user first visited the site
category STRING NULLABLE The device category (mobile, tablet, desktop)
mobile_brand_name STRING NULLABLE The device brand name
mobile_model_name STRING NULLABLE The device model name
mobile_marketing_name STRING NULLABLE The device marketing name
mobile_os_hardware_model STRING NULLABLE The device model information retrieved directly from the operating system
operating_system STRING NULLABLE The operating system of the device
operating_system_version STRING NULLABLE The OS version
language STRING NULLABLE The OS language
is_limited_ad_tracking STRING NULLABLE The device’s Limit Ad Tracking setting
time_zone_offset_seconds INTEGER NULLABLE The offset from GMT in seconds
device_browser STRING NULLABLE
device_browser_version STRING NULLABLE
browser STRING NULLABLE The browser in which the user viewed content
browser_version STRING NULLABLE The version of the browser in which the user viewed content
hostname STRING NULLABLE The hostname associated with the logged event
continent STRING NULLABLE The continent from which events were reported, based on IP address e.g. ‘Europe’
country STRING NULLABLE The country from which events were reported, based on IP address e.g. ‘United Kingdom’
region STRING NULLABLE The region from which events were reported, based on IP address e.g. ‘England’
city STRING NULLABLE The city from which events were reported, based on IP address e.g. ‘Birmingham’
sub_continent STRING NULLABLE The subcontinent from which events were reported, based on IP address e.g. ‘Northern Europe’
metro STRING NULLABLE The metro from which events were reported, based on IP address e.g. ‘Midlands’
first_user_campaign STRING NULLABLE The campaign through which this user first landed on GOV.UK
first_user_medium STRING NULLABLE The medium through which this user first landed on GOV.UK
first_user_source STRING NULLABLE The source through which this user first landed on GOV.UK
stream_id STRING NULLABLE The numeric ID of the data stream from which the event originated
platform STRING NULLABLE The data stream platform (Web, IOS or Android) from which the event originated
event_dimensions_hostname STRING NULLABLE
full_link_URL STRING NULLABLE
search_sort STRING NULLABLE Where available this is the sort method e.g. ‘Relevance’
search_term STRING NULLABLE The search term used
search_results STRING NULLABLE The number of results returned by the search
ui_text STRING NULLABLE Identifying text for the specific item the user interacted with
section STRING NULLABLE Used to distinguish between multiple sections within an interaction element where the UI/Link text property is not granular enough
action STRING NULLABLE The action the user has taken e.g. ‘opened’
index INTEGER NULLABLE The index of the link that was interacted with e.g. if the third link was clicked the index would be ‘3’
index_total INTEGER NULLABLE The total number of individual links within a feature e.g. if there are five links in total this would be ‘5’
political_status STRING NULLABLE The content attribute of the <meta name="govuk:political-status"> tag
publishing_government STRING NULLABLE The content attribute of the <meta name="govuk:political-status"> tag e.g. ‘historic’. This will only be available on ‘whitehall’ pages
world_locations STRING NULLABLE The content attribute of the <meta name="govuk:analytics:world-locations"> tag
dclid STRING NULLABLE The Google Marketing Platform (GMP) identifier that was collected with the event
debug_mode INTEGER NULLABLE
engaged_session_event INTEGER NULLABLE
entrances INTEGER NULLABLE A flag to indicate an entrance, where ‘1’ indicates an entrance
firebase_conversion INTEGER NULLABLE
gclid STRING NULLABLE The Google click identifier that was collected with the event
gclsrc STRING NULLABLE
content STRING NULLABLE
campaign_id STRING NULLABLE
index_section INTEGER NULLABLE The index of the section that was interacted with e.g. if the third tab was clicked the index would be ‘3’
index_section_total INTEGER NULLABLE The total number of sections within a feature e.g. if there are five tabs in total this would be ‘5’
tool_name INTEGER NULLABLE The name of the tool (the smart answer or form)
response STRING NULLABLE The user’s reponse to a form question or error message
ab_test STRING NULLABLE The content attribute of the <meta name="govuk:ab-test"> tag
navigation_list_type STRING NULLABLE The content attribute of the <meta name="govuk:navigation-list-type"> tag
navigation_page_type STRING NULLABLE The content attribute of the <meta name="govuk:navigation-page-type"> tag
percent_scrolled INTEGER NULLABLE The percentage scroll depth the user has reached
sfmc_activity_id STRING NULLABLE
sfmc_activity_name STRING NULLABLE
sfmc_asset_id STRING NULLABLE
sfmc_channel STRING NULLABLE
sfmc_journey_id STRING NULLABLE
sfmc_journey_name STRING NULLABLE
step_navs STRING NULLABLE The content attribute of the <meta name="govuk:stepnavs"> tag
manual_campaign_id STRING NULLABLE The manual campaign id (utm_id) that was collected with the event
manual_campaign_name STRING NULLABLE The manual campaign name (utm_campaign) that was collected with the event
manual_source STRING NULLABLE The manual campaign source (utm_source) that was collected with the event. Also includes parsed parameters from referral params, not just UTM values
manual_medium STRING NULLABLE The manual campaign medium (utm_medium) that was collected with the event. Also includes parsed parameters from referral params, not just UTM values
manual_term STRING NULLABLE The manual campaign keyword/term (utm_term) that was collected with the event
is_active_user BOOLEAN NULLABLE Whether the user was active (True) or inactive (False) at any point in the calendar day
video_title STRING NULLABLE The title of the video (only sent with video type events)
video_url STRING NULLABLE The URL of the video (only sent with video type events)
video_duration INTEGER NULLABLE The length of the video in seconds (only sent with video type events)
video_current_time INTEGER NULLABLE The time through the video at the moment the event fired in seconds (only sent with video type events)
video_percent INTEGER NULLABLE The percent through the video the user was at the moment the event fired (only sent with video type events)
cookie_banner STRING NULLABLE ‘True’ when the cookie banner is displayed to the user
devolved_nations_banner STRING NULLABLE Populated when a devolved nations banner is on the page, e.g. ‘England’ when the banner says ‘Applies to England’
intervention STRING NULLABLE ‘True’ when an intervention is displayed to the user
phase_banner STRING NULLABLE Populated when a phase banner is on the page, e.g. ‘beta’ when the beta banner is shown
outbound STRING NULLABLE ‘True’ if a link clicked leads away from www.gov.uk
query_string STRING NULLABLE The query string extracted from the page location
spelling_suggestion STRING NULLABLE The spelling suggestion, if one is shown, on a search results page
outcome STRING NULLABLE The outcome reached at the end of form (only sent with form_complete events)
viewport_size STRING NULLABLE The size of the browser window, minus the scroll bars and toolbars
batch_page_id INTEGER NULLABLE
batch_ordering_id INTEGER NULLABLE
timestamp INTEGER NULLABLE The Unix timestamp in milliseconds. This is a custom dimension, pushed with every dataLayer event, unlike the default event_timestamp above
copy_length INTEGER NULLABLE The number of characters in text copied (copy events only)
item_list_name STRING NULLABLE The name of the item list - on GOV.UK, this will be the name of a finder (search/search results) page
item_id STRING NULLABLE The URL of the search result item. A maximum of 200 search result items (the first 200) will be sent on longer search results pages
item_name STRING NULLABLE
item_list_index STRING NULLABLE The index of the search result item. A maximum of 200 search result items (the first 200) will be sent on longer search results pages
item_content_id STRING NULLABLE The content ID of the search result item

Retention

At present, there is no default table expiry set up for these tables and no agreed data retention period.

Code for flattening GA4 raw data

config {
    type: "table",
    name: constants.tableName()
}

WITH 
cte1 AS ( 
    SELECT 
        event_date AS event_date, 
        user_pseudo_id, 
        event_name, 
        event_timestamp, 
        CONCAT(user_pseudo_id, ( SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS unique_session_id, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location, 
        SPLIT(SPLIT(REGEXP_REPLACE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), 'https://www.gov.uk', ''),'?')[SAFE_OFFSET(0)],'#')[SAFE_OFFSET(0)] AS cleaned_page_location, 
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_sessionid, 
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'primary_publishing_organisation') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS primary_publishing_organisation, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS page_referrer, 
        SPLIT(REGEXP_REPLACE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer'), 'https://www.gov.uk', ''),'?')[SAFE_OFFSET(0)] AS cleaned_page_referrer, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_id') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS content_id, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'browse_topic') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS browse_topic, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'publishing_app') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS publishing_app,
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'public_updated_at') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS public_updated_at, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'updated_at') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS updated_at, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'first_published_at') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_published_at, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS taxonomy_all_ids_DEPRECATED, 
        LAST_VALUE(( SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'status_code') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS status_code, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'withdrawn') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS withdrawn, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'document_type') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS document_type, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'history') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS history, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_main_id') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS taxonomy_main_id, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS taxonomy_all_DEPRECATED, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_main') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS taxonomy_main, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_level1') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS taxonomy_level_1,

        LAST_VALUE(
            (CASE WHEN (CONCAT ( (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_1') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_1') ELSE '' END ), 
            (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_2') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_2') ELSE '' END ), 
            (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_3') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_3') ELSE '' END ), 
            (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_4') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_4') ELSE '' END ), 
            (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_5') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_5') ELSE '' END ) )) = '' THEN NULL ELSE 
            (CONCAT ( (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_1') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_1') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_2') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_2') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_3') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_3') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_4') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_4') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_5') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_5') ELSE '' END ) )) 
            END) IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
            AS full_taxonomy, 

        LAST_VALUE(
            (CASE WHEN (CONCAT ( (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_1') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_1') ELSE '' END ), 
            (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_2') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_2') ELSE '' END ), 
            (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_3') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_3') ELSE '' END ), 
            (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_4') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_4') ELSE '' END ), 
            (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_5') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_5') ELSE '' END ) )) = '' THEN NULL ELSE 
            (CONCAT ( (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_1') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_1') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_2') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_2') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_3') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_3') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_4') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_4') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_5') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'taxonomy_all_ids_5') ELSE '' END ) )) 
            END) IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
            AS full_taxonomy_ids, 

        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'rendering_app') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS rendering_app, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'organisations') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS organisations, 


        IFNULL((CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = "session_engaged") AS STRING)),(SELECT value.string_value FROM UNNEST(event_params) WHERE key = "session_engaged")) AS session_engaged,
        LAST_VALUE(
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'schema_name') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, 
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
            AS schema_name, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'method') AS method, 
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec, 
        --IFNULL(LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign')) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),"(not set)") AS event_campaign, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS event_campaign,

        LAST_VALUE(
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_language') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, 
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
            AS content_language, 

        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'type') AS type, 
        --IFNULL(LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source')) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), "direct") AS event_source,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS event_source,

        IFNULL(FIRST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source')) OVER (PARTITION BY user_pseudo_id, ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),"direct") AS session_source, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_text') AS link_text, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'term') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS term, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_url') AS link_url,
        --IFNULL(LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium')) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),"(none)") AS event_medium, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS event_medium,

        IFNULL(FIRST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium')) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),"(none)") AS session_medium, 

        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') AS link_domain, 
        event_previous_timestamp, 
        event_bundle_sequence_id, 
        event_server_timestamp_offset, 
        user_id, 
        user_first_touch_timestamp, 
        device.category, 
        device.mobile_brand_name, 
        device.mobile_model_name, 
        device.mobile_marketing_name, 
        device.mobile_os_hardware_model, 
        device.operating_system, 
        device.operating_system_version, 
        device.language, 
        device.is_limited_ad_tracking, 
        device.time_zone_offset_seconds, 
        device.browser AS device_browser, 
        device.browser_version AS device_browser_version, 
        device.web_info.browser, 
        device.web_info.browser_version,
        device.web_info.hostname, 
        geo.continent, 
        geo.country, 
        geo.region, 
        geo.city, 
        geo.sub_continent, 
        geo.metro, 
        traffic_source.name AS first_user_campaign,
        traffic_source.medium AS first_user_medium, 
        traffic_source.source AS first_user_source, 
        stream_id, platform, 
        event_dimensions.hostname AS event_dimensions_hostname, 
        CASE WHEN (CONCAT ( (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_1') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_1') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_2') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_2') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_3') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_3') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_4') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_4') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_5') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_5') ELSE '' END ) )) = '' THEN NULL ELSE (CONCAT ( (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_1') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_1') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_2') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_2') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_3') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_3') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_4') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_4') ELSE '' END ), (CASE WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_5') != "" THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_path_parts_5') ELSE '' END ) )) END AS full_link_URL, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'search_sort') AS search_sort, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'search_term') AS search_term, 
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'search_results') AS search_results ,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ui_text') AS ui_text, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'section') AS section, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'action') AS action, 
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'index') AS index, 
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'index_total') AS index_total, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'political_status') AS political_status, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'publishing_government') AS publishing_government, 
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'world_locations') AS world_locations ,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'dclid') AS dclid,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'debug_mode') AS debug_mode,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engaged_session_event') AS engaged_session_event,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') AS entrances,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'firebase_conversion') AS firebase_conversion,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'gclid') AS gclid,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'gclsrc') AS gclsrc,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ignore_referrer') AS ignore_referrer, 

        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS content, 
        LAST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign_id') IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS campaign_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'index_section') AS index_section ,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'index_section_total') AS index_section_total,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'tool_name') AS tool_name,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'response') AS response,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ab_test') AS ab_test,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'navigation_list_type') AS navigation_list_type,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'navigation_page_type') AS navigation_page_type,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'percent_scrolled') AS percent_scrolled,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'sfmc_activity_id') AS sfmc_activity_id,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'sfmc_activity_name') AS sfmc_activity_name,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'sfmc_asset_id') AS sfmc_asset_id,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'sfmc_channel') AS sfmc_channel,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'sfmc_journey_id') AS sfmc_journey_id,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'sfmc_journey_name') AS sfmc_journey_name,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'step_navs') AS step_navs,
        collected_traffic_source.manual_campaign_id,
        collected_traffic_source.manual_campaign_name,
        collected_traffic_source.manual_source,
        collected_traffic_source.manual_medium,
        collected_traffic_source.manual_term,
        collected_traffic_source.manual_content,
        is_active_user,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'video_title') AS video_title,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'video_url') AS video_url,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'video_duration') AS video_duration,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'video_current_time') AS video_current_time,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'video_percent') AS video_percent,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'cookie_banner') AS cookie_banner,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'devolved_nations_banner') AS devolved_nations_banner,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'intervention') AS intervention,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'phase_banner') AS phase_banner,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'outbound') AS outbound,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'query_string') AS query_string,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'spelling_suggestion') AS spelling_suggestion,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'outcome') AS outcome,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'viewport_size') AS viewport_size,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'batch_page_id') AS batch_page_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'batch_ordering_id') AS batch_ordering_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'timestamp') AS timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'copy_length') AS copy_length


    FROM `ga4-analytics-352613.analytics_330577055.events*` 
    WHERE _table_suffix = CONCAT("_",${constants.getYesterday()})
),

cte2 AS ( 
    SELECT 
      event_date AS event_date, 
      user_pseudo_id, 
      event_name, 
      event_timestamp, 
      CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS unique_session_id, 
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number, 
      items.item_list_name 

    FROM `ga4-analytics-352613.analytics_330577055.events*`, UNNEST(items) AS items 
    WHERE _table_suffix = CONCAT("_",${constants.getYesterday()})
),

cte3 AS (
    SELECT 
        event_date AS event_date, 
        user_pseudo_id, 
        event_name, 
        event_timestamp, 
        CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS unique_session_id, 
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number, 
        items.item_id, 
        item_name, 
        items.item_list_index,
        (SELECT value.string_value FROM UNNEST(item_params) WHERE key='item_content_id') AS item_content_id

    FROM `ga4-analytics-352613.analytics_330577055.events*`, UNNEST(items) AS items 
    WHERE _table_suffix = CONCAT("_",${constants.getYesterday()})
    AND event_name = 'select_item' 
), 

cte4 AS (
    SELECT 
        a.*, 
        b.item_id, 
        b.item_name, 
        b.item_list_index ,
        b.item_content_id

    FROM cte2 A 
    LEFT JOIN cte3 B 
        ON a.event_date=b.event_date 
        AND a.user_pseudo_id=b.user_pseudo_id 
        AND a.event_name=b.event_name 
        AND a.event_timestamp=b.event_timestamp 
        AND a.unique_session_id=b.unique_session_id 
        AND a.ga_session_number=b.ga_session_number 
    GROUP BY a.event_date, A.user_pseudo_id, A.event_name, a.event_timestamp, a.unique_session_id, a.ga_session_number, a.item_list_name, b.item_id, b.item_name, b.item_list_index, b.item_content_id 
), 

final_cte AS (
    SELECT 
        a.*, 
        b.item_list_name, 
        b.item_id, 
        b.item_name, 
        b.item_list_index ,
        b.item_content_id

    FROM cte1 a 
    LEFT JOIN cte4 b 
        ON a.event_date=b.event_date 
        AND a.user_pseudo_id=b.user_pseudo_id 
        AND a.event_name=b.event_name 
        AND a.event_timestamp=b.event_timestamp 
        AND a.unique_session_id=b.unique_session_id 
        AND a.ga_session_number=b.ga_session_number) 

SELECT DISTINCT * FROM final_cte
This page was last reviewed on 23 April 2024. It needs to be reviewed again on 23 October 2024 .
This page was set to be reviewed before 23 October 2024. This might mean the content is out of date.