基于时间窗口的电子邮件营销属性

Question

希望有人能在以下方面帮助我。我有两个表，一个包含“点击的电子邮件”事件，另一个包含“转换”(=orders)。我想在BigQuery SQL中编写属性逻辑，以匹配基于以下逻辑对单击的电子邮件的转换：

匹配ID是在点击邮件后5天(120小时)内发生的"person_id"

• conversions，如果客户在一个5天的窗口内单击了两封不同的电子邮件，则该邮件是最近一次点击，最后一次是在转换被归因为收入

之前。

要知道：点击表也包含没有转换的点击，转换表也包含与电子邮件无关的转换。

希望最终结果：一个表，包含所有属性订单的计数，以及所有单击、日期和活动名称的计数。

我想，我需要做一个左连接，只获得可能与点击有关的转换，在person_id上。然而，现在我需要定义一个窗口，直到计算转换为止(5天)。也许我可以把这个写在where声明里？然后，我需要检查，如果转换的计数> 1，则只考虑“日期差异”最小的情况下的转换。

我走了多远：

SELECT
  c. person_id,
  c.campaign_name,
  c.datetime,
  s.processed_at,
  c.email,
  s.order_number,
  SUM(s.total_price) AS revenue,
  COUNT(DISTINCT s.order_number) AS orders
FROM
  `klaviyo_de_clicks` c
LEFT JOIN
  `klaviyo_de_conversions` s
ON
  c.person_id = s.person_id
GROUP BY
  1,2,3,4,5,6

谢谢你的帮忙!

Answer 1

您可以在转换之前使用union all和窗口函数获取电子邮件。

这样做的目的是将所有数据放在一个单独的“结果集”中，将点击和转换分开。然后使用窗口函数获取每次转换的前一次单击信息。

您的示例代码作为一堆列在您的问题中没有提及。但这是代码的结构：

with cc as (
      select person_id, date_time, conversion_id, null as click_id
      from conversions c
      union all
      select person_id, date_time, null, click_id
      from clicks c
     )
select cc.*,
       (click_date_time >= datetime_add(datetime, interval -5 day)) as is_click_conversion
from (select cc.*,
             max(case when click_id is not null then date_time end) over (partition by person_id order by date_time) as click_date_time,
             last_value(click_id ignore nulls) over (partition by person_id order by date_time) as prev_click_id
      from cc
     ) cc
where conversion_id is not null;

如果需要额外的列，可以使用join将它们引入。