生成私域用户沉默预警名单筛选规则
每次改沉默标准都要找开发重写，运营自己不会写逻辑表达式，临时提需耽误活动节奏。
有人这样说「这波沉默用户预警得赶在明天活动前跑出来，不然错过触达窗口就亏大了。」
能替你选的都选好了。不想动，直接点下面「交给 AI 办」，点一下就开始做；想改，就点一下要的那个。
已替你选好想改就点一下
更多选项（一般不用动）
只有你知道的不填就先空着，成品里留【待填】
还想怎么改可以不选
开工前请确认
高级选项一般不用动 · 这件事通常 1 分钟左右做好
示例成品全文按上面这组示例选项真跑出来的
默认假设：
- 用户主表：
scrm_user - 行为表：
scrm_user_behavior send_group=主动发群，click_link=点击链接- 无点击记录按 999 天计算，便于满足“≥30 天”判定
如果没有独立用户主表，把 FROM scrm_user u 替换为 FROM (SELECT DISTINCT user_id FROM scrm_user_behavior) u。
WITH send_agg AS (
SELECT
user_id,
count(*) AS send_msg_cnt_30d,
max(behavior_time) AS last_send_time
FROM scrm_user_behavior
WHERE behavior_type = 'send_group'
AND behavior_time >= today() - 30
GROUP BY user_id
),
click_agg AS (
SELECT
user_id,
count(*) AS click_cnt,
max(behavior_time) AS last_click_time
FROM scrm_user_behavior
WHERE behavior_type = 'click_link'
GROUP BY user_id
),
merged AS (
SELECT
u.user_id,
ifNull(s.send_msg_cnt_30d, 0) AS send_msg_cnt_30d,
if(ifNull(c.click_cnt, 0) = 0,
999,
dateDiff('day', toDate(c.last_click_time), today())) AS last_click_days,
CASE
WHEN s.last_send_time IS NULL AND c.last_click_time IS NULL THEN NULL
WHEN s.last_send_time IS NULL THEN c.last_click_time
WHEN c.last_click_time IS NULL THEN s.last_send_time
WHEN s.last_send_time >= c.last_click_time THEN s.last_send_time
ELSE c.last_click_time
END AS last_active_time
FROM scrm_user u
LEFT JOIN send_agg s ON s.user_id = u.user_id
LEFT JOIN click_agg c ON c.user_id = u.user_id
)
SELECT
user_id,
CASE
WHEN send_msg_cnt_30d <= 1 AND last_click_days >= 30 THEN '高沉默'
WHEN send_msg_cnt_30d <= 1 OR last_click_days >= 30 THEN '中沉默'
ELSE '正常'
END AS silence_level,
last_active_time,
CASE
WHEN send_msg_cnt_30d <= 1 AND last_click_days >= 30 THEN '强唤醒-发券+短信'
WHEN send_msg_cnt_30d <= 1 THEN '轻唤醒-社群内容触达'
WHEN last_click_days >= 30 THEN '轻唤醒-链接召回'
ELSE '无需动作'
END AS action_tag
FROM merged
WHERE send_msg_cnt_30d <= 1 OR last_click_days >= 30
ORDER BY last_active_time ASC NULLS FIRST