如何通过SQL查询判断客户是否存在无GCLID的复购记录

我们的目标是找出曾经通过Google Ads点击（有有效GCLID记录）完成下单，后续直接访问网站无GCLID记录完成复购的客户，并按手机号统计这类复购次数。

原SQL的局限性 #

你当前的SQL仅统计了指定时间范围内有有效GCLID记录的手机号出现次数，没有关联无GCLID的复购记录，无法满足追踪复购的需求。

改进方案1：基础版（统计有GCLID记录客户的无GCLID订单数） #

以下SQL先筛选出所有有过有效GCLID记录的客户，再关联统计他们的无GCLID下单次数：

-- 先获取所有有过有效GCLID记录的客户手机号（去重）
WITH has_gclid_customers AS (
    SELECT DISTINCT Phone
    FROM `customer-data.customer_data.all`
    WHERE LENGTH(Gclid) > 55  -- 按业务规则判断有效GCLID
      AND Time BETWEEN "2022-01-01 00:00:00" AND "2023-01-01 00:00:00"
)
-- 统计这些客户的无GCLID下单次数
SELECT 
    c.Phone,
    COUNT(o.Phone) AS no_gclid_repurchase_count
FROM has_gclid_customers c
JOIN `customer-data.customer_data.all` o
    ON c.Phone = o.Phone
WHERE 
    (o.Gclid IS NULL OR LENGTH(o.Gclid) = 0)  -- 匹配无GCLID或空GCLID的记录
    AND o.Time BETWEEN "2022-01-01 00:00:00" AND "2023-01-01 00:00:00"
GROUP BY c.Phone
HAVING no_gclid_repurchase_count > 0
ORDER BY no_gclid_repurchase_count DESC;

改进方案2：精准复购版（仅统计首次广告下单后的无GCLID订单） #

如果需要严格定义“复购”——无GCLID订单发生在客户首次通过广告下单之后，可使用以下SQL：

-- 获取每个客户首次通过广告下单的时间
WITH customer_first_gclid_order AS (
    SELECT 
        Phone,
        MIN(Time) AS first_gclid_order_time
    FROM `customer-data.customer_data.all`
    WHERE LENGTH(Gclid) > 55
      AND Time BETWEEN "2022-01-01 00:00:00" AND "2023-01-01 00:00:00"
    GROUP BY Phone
)
-- 统计首次广告下单后的无GCLID复购次数
SELECT 
    c.Phone,
    COUNT(o.Phone) AS no_gclid_repurchase_count
FROM customer_first_gclid_order c
JOIN `customer-data.customer_data.all` o
    ON c.Phone = o.Phone
WHERE 
    (o.Gclid IS NULL OR LENGTH(o.Gclid) = 0)
    AND o.Time BETWEEN "2022-01-01 00:00:00" AND "2023-01-01 00:00:00"
    AND o.Time > c.first_gclid_order_time  -- 确保是广告下单后的复购
GROUP BY c.Phone
HAVING no_gclid_repurchase_count > 0
ORDER BY no_gclid_repurchase_count DESC;

逻辑说明 #

• 两个方案均基于BigQuery语法适配你的数据表结构
• 方案1快速筛选有广告记录客户的无GCLID订单，方案2更精准地匹配“复购”时间顺序
• 可根据业务对GCLID有效性的判断（如长度阈值）调整LENGTH(Gclid)的条件

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