如何在GA4 - bigquery导出中获取“当前会话”的流量源数据？

Question

一个月前，我将我的GA4帐户与一个BigQuery项目连接起来。

对于原始数据，我想获得各种数据&洞察力，包括GA目前提供的许多特性。其中之一是转换路径。我想看看我的用户使用转换路径是从哪个媒体/源中经过的。为此，流量源或媒体/活动/源值是必需的。尽管如此，的帮助页面指出，流量源是“首次获得用户的流量源的名称”。https://support.google.com/firebase/answer/7029846?hl=en

因此，对于每个用户，流量源似乎总是相同的，我无法跟踪进行转换的媒体/源用户的路径。我还尝试在大查询模式的page_location列中提取event_param的utm代码，但似乎不正确。

例如，第一个图像是GA4转换路径中最后一个介质的值计数。第二个是从BigQuery数据的page_location/page_referrer中提取的最后一个介质的值计数。期间相同，因此转换的总数是相同的。不过，媒体的数量不同。

GA4转换路径中最后介质的值计数

BigQuery数据的引用程序

我的问题是如何在BigQuery?的原始数据中获取每个会话的流量源。

任何想法或线索都将不胜感激。非常感谢。

Answer 1

来自GA4导出的数据是基于事件的，而不是基于会话的，因此您想要做的是将事件数据滚动到会话内容中。

此查询将执行以下操作：

SELECT
    user_pseudo_id,
    TIMESTAMP_MICROS(event_timestamp) AS session_start_ts,
    CAST(LEAD(TIMESTAMP_MICROS(event_timestamp),1) OVER (PARTITION BY CONCAT(user_pseudo_id)
      ORDER BY
        event_timestamp) AS timestamp) AS session_end_ts,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS referrer,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page_path,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_title') AS landing_page_title,
    traffic_source.name,
    traffic_source.medium,
    traffic_source.source,
    CASE
    WHEN device.category = "desktop" THEN "desktop"
    WHEN device.category = "tablet" AND app_info.id IS NULL THEN "tablet-web"
    WHEN device.category = "mobile" AND app_info.id IS NULL THEN "mobile-web"
    WHEN device.category = "tablet" AND app_info.id IS NOT NULL THEN "tablet-app"
    WHEN device.category = "mobile" AND app_info.id IS NOT NULL THEN "mobile-app"
    END AS device,
    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.vendor_id,
    device.advertising_id,
    device.language,
    device.is_limited_ad_tracking,
    device.time_zone_offset_seconds,
    device.browser,
    device.browser_version,
    device.web_info.browser,
    device.web_info.browser_version,
    device.web_info.hostname
  FROM
    `[my_project].analytics_[my_id].events*` s -- modify to your project
  WHERE
    event_name = 'session_start'
    order by 1,2
    
    LIMIT 500

这段代码的全部功劳都在下面的关于Rittman的文章中，我认为当您从GA4导出到BigQuery：https://rittmananalytics.com/blog/2021/7/25/event-based-analytics-and-bigquery-export-comes-to-google-analytics-4-how-does-it-worknbsp-and-whats-thenbspcatch时，这将是一个非常有益的阅读。

本

Answer 2

如果你还在寻找答案，下面是我所做的：

select
event_date,
user_pseudo_id,

(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') as page_referrer,


    CONCAT(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id,
    CASE
    WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') IS NULL THEN '(direct)'
    ELSE ((select value.string_value from unnest(event_params) where key = 'medium' )) 
    END as session_medium,
    CASE
    WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') IS NULL THEN '(none)'
    ELSE ((select value.string_value from unnest(event_params) where key = 'source')) 
    END as session_source,
    CASE
    WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') IS NULL THEN '(none)'
    ELSE ((select value.string_value from unnest(event_params) where key = 'campaign')) 
    END as session_campaign,
    CASE
    WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content') IS NULL THEN '(none)'
    ELSE((select value.string_value from unnest(event_params) where key = 'content')) 
    END as session_content,
    
   
from
`ga4.analytics_308612563.events_*`
where event_name = "page_view"
and (select value.int_value from unnest(event_params) where key = 'entrances' ) = 1

因此，基本上您是通过筛选入口= 1来查找第一个页面视图。对于我的用例，我使用这个查询作为CTE，然后对其他我正在寻找的事件执行另一个CTE。THen基于user_pseudo_id和ga_session_id将会话id上的两个临时表连接起来。