如何获取会话中页面浏览对应的最近后续转化时间戳

我需要给page_view事件分配pseudo_identifier字段，取值为当前会话中该页面浏览之后最近的转化事件的event_timestamp。但当前SQL在单个会话存在多个转化事件时，每个pageview会对应每条转化时间戳生成重复行，且部分is_before_conversion_event字段逻辑不符合预期。需要修改SQL实现：每个pageview仅返回会话内最近的后续转化时间戳；若页面浏览后无后续转化或会话本身无转化，该字段返回null。

原SQL代码 #

with conversion_events as (
 select 
 session_key,
 event_name, 
 event_timestamp
 FROM `lunar-brace-354914.dev_ga4_main.final_ga4__events`
 WHERE event_date_dt between "2023-10-18" AND "2023-10-20"
 AND is_conversion_event = 1
), 
pageviews as (
 select 
 session_key, 
 event_key,
 event_timestamp, 
 page_location, 
 FROM `lunar-brace-354914.dev_ga4_main.final_ga4__events`
 WHERE event_date_dt between "2023-10-18" AND "2023-10-20"
 AND event_name = "page_view"
), 
pageviews_before_conversion as (
 select
 pageviews.session_key,
 pageviews.event_key,
 pageviews.event_timestamp,
 LEAD(conversion_events.event_timestamp) OVER (PARTITION BY pageviews.session_key ORDER BY pageviews.event_timestamp) as pseudo_identifier,
 pageviews.page_location,
 (case when (pageviews.event_timestamp < conversion_events.event_timestamp) then true else false end) as is_before_conversion_event
from pageviews
full outer join conversion_events on conversion_events.session_key = pageviews.session_key
)
select * from pageviews_before_conversion where session_key = "RV4IkjWU8LLWyGm1UCjTMg=="

解决方案 #

原问题核心是FULL OUTER JOIN导致每个pageview与会话内所有转化事件关联，生成重复行。以下提供两种高效修改方案：

方案1：GROUP BY + LEFT JOIN（推荐，性能更优） #

通过关联会话内晚于当前pageview的转化事件，用MIN()直接取最近的转化时间戳，再聚合去重：

WITH conversion_events AS (
 SELECT 
  session_key,
  event_timestamp
 FROM `lunar-brace-354914.dev_ga4_main.final_ga4__events`
 WHERE event_date_dt BETWEEN "2023-10-18" AND "2023-10-20"
   AND is_conversion_event = 1
),
pageviews AS (
 SELECT 
  session_key, 
  event_key,
  event_timestamp AS pageview_timestamp, 
  page_location
 FROM `lunar-brace-354914.dev_ga4_main.final_ga4__events`
 WHERE event_date_dt BETWEEN "2023-10-18" AND "2023-10-20"
   AND event_name = "page_view"
)
SELECT
 p.session_key,
 p.event_key,
 p.pageview_timestamp,
 -- 取当前pageview之后最近的转化时间戳，无则返回null
 MIN(c.event_timestamp) AS pseudo_identifier,
 p.page_location,
 -- 直接通过pseudo_identifier是否非空判断是否存在后续转化
 CASE WHEN MIN(c.event_timestamp) IS NOT NULL THEN TRUE ELSE FALSE END AS is_before_conversion_event
FROM pageviews p
LEFT JOIN conversion_events c
 ON p.session_key = c.session_key
 AND c.event_timestamp > p.pageview_timestamp
GROUP BY p.session_key, p.event_key, p.pageview_timestamp, p.page_location
WHERE p.session_key = "RV4IkjWU8LLWyGm1UCjTMg=="
ORDER BY p.pageview_timestamp;

方案2：窗口函数 + DISTINCT #

利用窗口函数FIRST_VALUE()筛选出当前pageview之后的第一个转化时间戳，再去重：

WITH conversion_events AS (
 SELECT 
  session_key,
  event_timestamp
 FROM `lunar-brace-354914.dev_ga4_main.final_ga4__events`
 WHERE event_date_dt BETWEEN "2023-10-18" AND "2023-10-20"
   AND is_conversion_event = 1
),
pageviews AS (
 SELECT 
  session_key, 
  event_key,
  event_timestamp AS pageview_timestamp, 
  page_location
 FROM `lunar-brace-354914.dev_ga4_main.final_ga4__events`
 WHERE event_date_dt BETWEEN "2023-10-18" AND "2023-10-20"
   AND event_name = "page_view"
),
pageviews_with_conversions AS (
 SELECT
  p.session_key,
  p.event_key,
  p.pageview_timestamp,
  p.page_location,
  -- 按会话分组，筛选出当前pageview之后的第一个转化时间戳
  FIRST_VALUE(c.event_timestamp) OVER(
   PARTITION BY p.session_key 
   ORDER BY CASE WHEN c.event_timestamp > p.pageview_timestamp THEN c.event_timestamp ELSE NULL END
   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS pseudo_identifier
 FROM pageviews p
 LEFT JOIN conversion_events c
  ON p.session_key = c.session_key
)
SELECT DISTINCT
 session_key,
 event_key,
 pageview_timestamp,
 pseudo_identifier,
 page_location,
 CASE WHEN pseudo_identifier IS NOT NULL THEN TRUE ELSE FALSE END AS is_before_conversion_event
FROM pageviews_with_conversions
WHERE session_key = "RV4IkjWU8LLWyGm1UCjTMg=="
ORDER BY pageview_timestamp;

关键修改点 #

• 移除原SQL中导致重复行的FULL OUTER JOIN，改用LEFT JOIN仅关联晚于当前pageview的转化事件
• 通过MIN()或FIRST_VALUE()精准获取最近的后续转化时间戳
• 优化is_before_conversion_event字段逻辑，直接基于pseudo_identifier是否非空判断，避免原逻辑的错误

内容的提问来源于stack exchange，提问作者jp0008