BigQuery中按条件计算品类活动后累计唯一客户数

需求说明 #

按产品品类统计对应活动结束日期（end_campaign）后3个月、6个月、12个月内购买该产品的累计唯一客户（client）数。

输入表结构及数据 #

期望输出 #

用户尝试的SQL代码 #

WITH firstseen AS (   
  SELECT product, 
         client,
         case when order_date >= end_campagne then min(order_date) else null end as first_date   
  FROM sample_table   
  GROUP BY 1,2 
)

SELECT DISTINCT product, 
                COUNT(client) OVER (PARTITION by Product ORDER BY first_date) as daily_cumulative_count  
FROM firstseen 
ORDER BY 1

完善后的SQL代码 #

WITH post_campaign_first_purchase AS (
  SELECT 
    product,
    end_campaign,
    client,
    MIN(order_date) AS first_post_purchase_date -- 提取客户在活动结束后的首次购买日期
  FROM sample_table
  WHERE order_date >= end_campaign -- 仅保留活动结束及之后的订单
  GROUP BY product, end_campaign, client
)
SELECT
  product,
  end_campaign,
  -- 统计活动结束后3个月内的唯一客户数
  COUNT(DISTINCT CASE WHEN DATE_DIFF(first_post_purchase_date, end_campaign, MONTH) <= 3 THEN client END) AS cum_dist_count_3M,
  -- 统计活动结束后6个月内的唯一客户数
  COUNT(DISTINCT CASE WHEN DATE_DIFF(first_post_purchase_date, end_campaign, MONTH) <= 6 THEN client END) AS cum_dist_count_6M,
  -- 统计活动结束后12个月内的唯一客户数
  COUNT(DISTINCT CASE WHEN DATE_DIFF(first_post_purchase_date, end_campaign, MONTH) <= 12 THEN client END) AS cum_dist_count_12M
FROM post_campaign_first_purchase
GROUP BY product, end_campaign
ORDER BY product;

代码说明 #

1. CTE部分：post_campaign_first_purchase筛选出活动结束后的所有订单，对每个product-end_campaign-client组合取首次购买日期，确保同一客户仅被统计一次。
2. 主查询部分：通过CASE WHEN结合COUNT(DISTINCT)，分别判断客户首次购买日期是否在活动结束后的3/6/12个月内，统计对应周期的唯一客户数量。使用DATE_DIFF函数计算日期差，精准判断时间范围。

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