BigQuery中关联APP数据与广告归因数据并前向填充的方案咨询

问题背景 #

现有两类不同来源的数据需要合并，用于计算广告活动的投入产出及相关统计指标：

• APP用户数据：主键为用户ID+UTC日期，仅记录用户当日打开APP的对应数据，包含收入（Revenue）指标
• 广告活动归因数据：主键为用户ID+广告活动，单个用户可对应多条广告归因记录，包含广告归因时间戳Attribution_Timestamp字段

直接左连接无法实现按归因时间的前向匹配，比如ID为1的用户2021-01-09归因到广告活动B后，2021-01-10的APP数据无法匹配到对应活动，需更合理的关联逻辑。

数据样例 #

APP用户数据样例 #

SELECT 
  *
FROM UNNEST(ARRAY<STRUCT<ID INT64, UTC_Date DATE, Revenue FLOAT64>>
[(1, DATE('2021-01-01'), 0),
(1, DATE('2021-01-05'), 5),
(1, DATE('2021-01-10'), 0),
(2, DATE('2021-01-03'), 10),
(2, DATE('2021-01-08'), 0),
(2, DATE('2021-01-09'), 0)])

广告归因数据样例 #

SELECT 
  *
FROM UNNEST(ARRAY<STRUCT<ID INT64, Attribution_Timestamp Timestamp, campaign_name STRING>>
[(1, TIMESTAMP('2021-01-01 09:54:31'), "A"),
(1, TIMESTAMP('2021-01-09 22:32:51'), "B"),
(2, TIMESTAMP('2021-01-03 19:12:11'), "A")])

期望输出结果 #

SELECT 
  *
FROM UNNEST(ARRAY<STRUCT<ID INT64, UTC_Date DATE, Revenue FLOAT64, campaign_name STRING>>
[(1, DATE('2021-01-01'), 0, "A"),
(1, DATE('2021-01-05'), 5, "A"),
(1, DATE('2021-01-10'), 0, "B"),
(2, DATE('2021-01-03'), 10, "A"),
(2, DATE('2021-01-08'), 0, "A"),
(2, DATE('2021-01-09'), 0, "A")])

解决方案 #

可使用窗口函数+非等值连接的组合实现，逻辑是先对每个用户的APP数据匹配所有早于等于当前UTC日期的广告归因记录，再取最新的那条归因对应的活动即可，参考SQL如下（和你提供的BigQuery语法完全兼容）：

WITH app_data AS (
  -- 此处替换为你的APP用户数据表
  SELECT 
    *
  FROM UNNEST(ARRAY<STRUCT<ID INT64, UTC_Date DATE, Revenue FLOAT64>>
  [(1, DATE('2021-01-01'), 0),
  (1, DATE('2021-01-05'), 5),
  (1, DATE('2021-01-10'), 0),
  (2, DATE('2021-01-03'), 10),
  (2, DATE('2021-01-08'), 0),
  (2, DATE('2021-01-09'), 0)])
),
attribution_data AS (
  -- 此处替换为你的广告归因数据表
  SELECT 
    *,
    DATE(Attribution_Timestamp) AS Attribution_Date
  FROM UNNEST(ARRAY<STRUCT<ID INT64, Attribution_Timestamp Timestamp, campaign_name STRING>>
  [(1, TIMESTAMP('2021-01-01 09:54:31'), "A"),
  (1, TIMESTAMP('2021-01-09 22:32:51'), "B"),
  (2, TIMESTAMP('2021-01-03 19:12:11'), "A")])
)
SELECT 
  ID,
  UTC_Date,
  Revenue,
  campaign_name
FROM (
  SELECT
    a.*,
    b.campaign_name,
    ROW_NUMBER() OVER (PARTITION BY a.ID, a.UTC_Date ORDER BY b.Attribution_Timestamp DESC) AS rn
  FROM app_data a
  LEFT JOIN attribution_data b
  ON a.ID = b.ID
  AND b.Attribution_Date <= a.UTC_Date
)
WHERE rn = 1

该方案可以实现你需要的前向填充效果，无论单个用户对应多少条归因记录，都能自动将最新的归因活动匹配到后续所有APP收入数据中。

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