# 💡 Summary  # 🧩 Cues # 🪞Notes > Top 5 特征（来自 用户 hive 表/用户特征.md） ## 活跃/下单“时效性”Recency - 表：tag.ads_tagger_business_username_yhzjxdrq_dp（最近下单-大盘）、tag.ads_tagger_public_username_dspylzj7t（最近活跃-大盘）、以及分业务线活跃：火车票yhzjhyrq_hcp、国内酒店yhzjhyrq_gnjd、国际机票yhzjhyrq_gjjp、国际酒店yhzjhyrq_gjjd（用户 hive 表/用户特征.md） - 用途：衡量用户“当下有无出行意图”的强弱，是预测是否会用 AI Chat 和是否转化的首要信号。 - 做法：构造派生指标 days_since_last_active_{line}、days_since_last_order，分桶（0–3/4–7/8–30/30+ 天），对比各桶的 Chat 进入率、会话深度、7/14 天内下单率与客单。 # 历史订单规模与偏好（R/F 近似） - 表：全量与分业务线订单量 tag.ads_tagger_public_username_all_ord_num、flight_ord_num、hotel_ord_num、train_ord_num、piao_ord_num、vacation_ord_num（用户 hive 表/用户特征.md） - 用途：区分高频/低频与品类偏好，评估 AI Chat 在老练用户与轻度用户中的作用差异。 - 做法：近12个月订单量分层（0/1/2–4/5+），叠加“主偏好品类”（最大订单数的业务线），形成分群，比较各群体的 Chat 转化 uplift；可做简易 R/F 分层或倾向得分匹配做因果评估。 # 常驻地/城市与线路场景 - 表：tag.ads_tagger_flight_residence_city_alg_username（常驻城市-用户名）（用户 hive 表/用户特征.md） - 用途：不同城市级别、典型出发地/目的地对 AI 规划价值差异大（如一线城市跨省/跨国比例高）。 - 做法：映射城市能级与区域，结合会话意图城市（若有），观察“常驻地×目的地类型（周边/跨省/国际）”上的 Chat 进入和转化；定位覆盖差与内容需求差。 # 会员等级（忠诚与价值） - 表：tag.ads_tagger_membership_level（用户 hive 表/用户特征.md） - 用途：高等级用户对服务质量和效率更敏感，Chat 在该群体的满意度与留存贡献值得优先看。 - 做法：按会员等级分层，观察 Chat 使用渗透、转化率、复购间隔缩短；结合客服/售后触点对 NPS/工单率做对照。 # 人群画像（出行场景类型） - 表：亲子/情侣/商旅/银发/家庭游等多标签：tag.ads_tagger_business_username_qunaer_business_trip、qunaer_fimily_travel、qunaer_couple、qunaer_old_people、qunaer_paternity_m/ _f；补充通用标签：tag.ads_tagger_public_username_q3_18（情侣）、q3_19（亲子）、q3_20（携老）（用户 hive 表/用户特征.md） - 用途：AI 规划对多目的地/多人群体（亲子/携老/商旅）更具刚需；识别“谁最吃 Chat 的价值”。 - 做法：将多标签转成主画像（优先级规则或多热编码），比较各画像的 Chat 进入→留存→下单路径转化与平均会话长度；沉淀“画像×意图”的最佳实践话术与推荐策略。 分析思路 - 指标体系 - 渗透与参与：Chat 进入率、会话启动率、消息数/会话、有效规划完成率（生成行程/收藏/分享）。 - 业务结果：7/14/30 天下单率、GMV、客单价、搜索到下单转化。 - 体验信号：成功率（生成/改计划成功）、二跳率（点击酒店/机票/POI）、取消/改签率、后续人工客服触达率。 - 数据拼接 - 以 username 维度构建“日快照”用户特征（上述 Top5），与 Chat 会话日志、搜索/点击日志、订单事实表按日/会话粒度关联。 - 口径固定：取 T 日的用户特征快照，关联 T～T+7/14/30 天行为与转化，避免泄漏。 - 评估方法 - 分桶/分层对比 + 置信区间；对核心问（Chat 是否带来增益）用倾向得分匹配或双重差分消除选择偏差。 - Uplift 排序：在各特征层识别“高增益”人群，反推产品入口与话术策略。 口径与清洗建议 - 剔除/标注异常与风险：tag.ads_tagger_public_username_scalper_v2、tag.ads_tagger_business_username_risk（用户 hive 表/用户特征.md），避免干扰。 - 新老客补充维度：tag.ads_tagger_business_username_pingtailaoke（平台老客），用于分层阅读结果。 - 渠道控制：tag.ads_tagger_business_username_grab_progress_user（短视频引流最近7天），用于控制流量结构变化。 - 设备差异控制：tag.ads_tagger_public_username_phone_model 作为模型/分层共变项。 落地步骤（简要） - 构建用户特征日表：抽取上述 Top5 原子标签，派生分桶变量与主画像。 - 关联会话与订单：按会话日与 7/14/30 天转化口径做明细与分群聚合。 - 产出对照图表：Top5 各分层的进入率、会话深度、转化率与GMV对比；标注显著性。 - 识别高增益人群：基于 uplift/PSM 输出“优先运营”细分画像与入口建议。 ___ > 我基于 sql.md 的口径（tag.* 表均为 key/value，key 与会话表 user_id 对齐），写了一份按会员等级分层的 Hive SQL，覆盖： - Chat 渗透（活跃用户口径） - Chat 用户 7/14/30 天“下单”近似转化率（基于“最近下单日期”标签的保守估计） - Chat 用户会话深度 - 复购间隔（近似，基于“最近下单日期”） 直接修改参数 dt_start/dt_end 后可跑。 SQL -- 可选参数（按需启用） -- set hivevar:dt_start=2025-10-01; -- set hivevar:dt_end=2025-10-31; WITH params AS ( SELECT to_date('${dt_start}') AS dt_start, to_date('${dt_end}') AS dt_end ), -- 会员等级 membership AS ( SELECT key, COALESCE(value, 'UNKNOWN') AS membership_level FROM tag.ads_tagger_membership_level ), -- 风险与异常用户（排除） risk AS (SELECT key FROM tag.ads_tagger_business_username_risk), scalper AS (SELECT key FROM tag.ads_tagger_public_username_scalper_v2), -- 观察窗内“活跃”用户（作为渗透率分母），以“大盘最近活跃日期”判定 active_users AS ( SELECT dsp.key, to_date(dsp.value) AS active_dt FROM tag.ads_tagger_public_username_dspylzj7t dsp CROSS JOIN params p WHERE to_date(dsp.value) BETWEEN p.dt_start AND p.dt_end ), active_base AS ( SELECT a.key, a.active_dt FROM active_users a LEFT JOIN risk r ON a.key = r.key LEFT JOIN scalper s ON a.key = s.key WHERE r.key IS NULL AND s.key IS NULL ), -- 观察窗内 Chat 用户（作为渗透率分子 & 计算首次 Chat 日期） chat_users AS ( SELECT c.user_id AS key, MIN(to_date(c.dt)) AS first_chat_dt, -- 假设会话表有分区/字段 dt COUNT(DISTINCT c.session_id) AS chat_sessions -- 假设存在 session_id；若无可改为 COUNT(*) FROM qal_default.ods_qal_travel_plan_trip_user_conversation c CROSS JOIN params p WHERE to_date(c.dt) BETWEEN p.dt_start AND p.dt_end GROUP BY c.user_id ), -- 近似“下单”标签（最近一次下单日期；仅用于窗口内是否下单的保守判定） orders_last AS ( SELECT key, to_date(value) AS last_order_dt FROM tag.ads_tagger_business_username_yhzjxdrq_dp ), -- 将活跃与 Chat 合并，并拼会员&最近下单日期 enriched AS ( SELECT ab.key, m.membership_level, ab.active_dt, cu.first_chat_dt, cu.chat_sessions, ol.last_order_dt, CASE WHEN cu.key IS NOT NULL THEN 1 ELSE 0 END AS chat_flag, -- Chat 组：在首次 Chat 后 N 天内是否“有下单”（以最近下单日期落窗内作为保守近似） CASE WHEN cu.key IS NOT NULL AND ol.last_order_dt BETWEEN cu.first_chat_dt AND date_add(cu.first_chat_dt, 7) THEN 1 ELSE 0 END AS chat_order_7d, CASE WHEN cu.key IS NOT NULL AND ol.last_order_dt BETWEEN cu.first_chat_dt AND date_add(cu.first_chat_dt, 14) THEN 1 ELSE 0 END AS chat_order_14d, CASE WHEN cu.key IS NOT NULL AND ol.last_order_dt BETWEEN cu.first_chat_dt AND date_add(cu.first_chat_dt, 30) THEN 1 ELSE 0 END AS chat_order_30d, -- 非 Chat 组的“对照转化”（以活跃日为起点） CASE WHEN cu.key IS NULL AND ol.last_order_dt BETWEEN ab.active_dt AND date_add(ab.active_dt, 7) THEN 1 ELSE 0 END AS nonchat_order_7d, CASE WHEN cu.key IS NULL AND ol.last_order_dt BETWEEN ab.active_dt AND date_add(ab.active_dt, 14) THEN 1 ELSE 0 END AS nonchat_order_14d, CASE WHEN cu.key IS NULL AND ol.last_order_dt BETWEEN ab.active_dt AND date_add(ab.active_dt, 30) THEN 1 ELSE 0 END AS nonchat_order_30d, -- 复购间隔（近似）：以“最近下单日期”与 Chat/活跃日的相对天数估算 CASE WHEN cu.key IS NOT NULL AND ol.last_order_dt > cu.first_chat_dt THEN datediff(ol.last_order_dt, cu.first_chat_dt) END AS post_gap_days_approx, -- Chat 后到“最近一次下单”的间隔（保守，可能被更晚订单覆盖） CASE WHEN cu.key IS NOT NULL AND ol.last_order_dt <= cu.first_chat_dt THEN datediff(cu.first_chat_dt, ol.last_order_dt) END AS pre_gap_days_chat, -- Chat 前距“最近一次下单”的间隔 CASE WHEN cu.key IS NULL AND ol.last_order_dt > ab.active_dt THEN datediff(ol.last_order_dt, ab.active_dt) END AS post_gap_days_nonchat -- 非 Chat 组对照：活跃后到“最近一次下单”的间隔 FROM active_base ab LEFT JOIN chat_users cu ON ab.key = cu.key LEFT JOIN membership m ON ab.key = m.key LEFT JOIN orders_last ol ON ab.key = ol.key ) SELECT COALESCE(membership_level, 'UNKNOWN') AS membership_level, COUNT(DISTINCT key) AS active_users, COUNT(DISTINCT CASE WHEN chat_flag = 1 THEN key END) AS chat_users, ROUND(COUNT(DISTINCT CASE WHEN chat_flag = 1 THEN key END) / NULLIF(COUNT(DISTINCT key), 0), 4) AS chat_penetration, ROUND(AVG(CASE WHEN chat_flag = 1 THEN chat_sessions END), 2) AS avg_sessions_per_chat_user, -- Chat 组近似转化（基于“最近下单日期”的保守估计） ROUND(SUM(CASE WHEN chat_flag = 1 AND chat_order_7d = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN chat_flag = 1 THEN 1 ELSE 0 END), 0), 4) AS chat_order_7d_rate_tag, ROUND(SUM(CASE WHEN chat_flag = 1 AND chat_order_14d = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN chat_flag = 1 THEN 1 ELSE 0 END), 0), 4) AS chat_order_14d_rate_tag, ROUND(SUM(CASE WHEN chat_flag = 1 AND chat_order_30d = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN chat_flag = 1 THEN 1 ELSE 0 END), 0), 4) AS chat_order_30d_rate_tag, -- 非 Chat 组对照转化（以活跃日为起点） ROUND(SUM(CASE WHEN chat_flag = 0 AND nonchat_order_7d = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN chat_flag = 0 THEN 1 ELSE 0 END), 0), 4) AS nonchat_order_7d_rate_tag, ROUND(SUM(CASE WHEN chat_flag = 0 AND nonchat_order_14d = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN chat_flag = 0 THEN 1 ELSE 0 END), 0), 4) AS nonchat_order_14d_rate_tag, ROUND(SUM(CASE WHEN chat_flag = 0 AND nonchat_order_30d = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN chat_flag = 0 THEN 1 ELSE 0 END), 0), 4) AS nonchat_order_30d_rate_tag, -- 复购间隔（近似，中位数） percentile_approx(pre_gap_days_chat, 0.5) AS p50_pre_gap_days_chat, percentile_approx(post_gap_days_approx, 0.5) AS p50_post_gap_days_chat, percentile_approx(post_gap_days_nonchat, 0.5) AS p50_post_gap_days_nonchat FROM enriched GROUP BY COALESCE(membership_level, 'UNKNOWN') ORDER BY membership_level; 使用与假设 - dt 字段：会话表按 dt 分区或列保存业务日期；若字段名不同，请改为实际日期列。 - 标签口径：sql.md 指明 tag.* 为两列 key/value，无快照分区；本 SQL 将 value 以 to_date 解析。 - 渗透率分母：用“大盘最近活跃日期”在窗内的用户，按会员等级做渗透（Chat 用户/活跃用户）。 - 近似转化与复购间隔：仅基于“最近下单日期”标签判断，存在保守偏差（晚于窗口的更近一次下单会覆盖窗口内订单，产生“假阴性”）。如需精确，请改为订单事实表窗口内是否有支付成功订单。 需要我基于你们订单事实/客服NPS表（表名与字段）再加上“真实转化”和“NPS/工单率”对照吗？