业务方跑过来的时候说辞特别简单“帮我拉一批用户就是那种一看到打折就买的人还有那种买东西前必须刷半天评论的人。”听起来像是个取数需求但落到数仓这边这其实是两个完整的分析主题——促销敏感度和评论敏感度。名字听起来不复杂真正做起来之后才发现从口径定义到模型设计再到调度回刷每一步都有不少坑。这篇就把当时从需求拆解到数仓落地、再到最终上线的完整过程记录下来给后面接类似“数仓建模”需求的同学一个参考。1. 业务侧的一句话需求拆开是三张口径问题1.1 “哪些用户爱买打折货”背后的经营意图业务方一开始说自己要“促销敏感用户名单”可如果只取一份“促销订单占比最高的用户”其实解决不了任何经营问题。我建议先倒推回去有了这份名单运营打算怎么用。聊完之后发现真实使用场景有三个大促前定向发券、清库存时圈选人群、以及评估促销活动是否把新客转化成老客。三个场景对“促销敏感度”的定义要求完全不同。大促前发券看重的是用户对折扣力度的反应速度也就是“给多少优惠会触发购买”偏向价格弹性。清库存圈人看重的是用户近期有没有频繁购买高折扣商品偏向促销参与频次。新老客转化评估看重的是用户平时购买正价商品的比例如果原本买正价的人突然只买促销款那可能是客群质量下降的信号。这三种诉求如果共用一套指标就会出现业务方拿着同一张表各说各话的情况。所以在数仓建模之前第一步不是建表而是把“促销敏感度”拆成至少三个可量化口径促销参与深度、折扣响应弹性和正价购买稳定性。这三个口径可以共存于一张宽表里但绝对不能混成一个数值。1.2 “哪些用户看评论就下单”对应的行为链路评论敏感度比促销敏感度更绕。用户“看评论”这个行为在埋点里好识别但“因为看了评论才下单”这个因果关系埋点永远直接给不出来。业务方最初的提法是“找出喜欢看评论的人”。这其实是一个行为统计问题统计评论详情页的浏览PV、UV就行。但后来运营的真实意图浮出水面他们想优化商品详情页的评价模块同时给“容易被中差评劝退”的用户做定向安抚。这就涉及两个不同概念评论查看偏好用户下单前是否经常浏览评论区域反映的是购物习惯。评论购买影响度用户看完评论后的转化率变化反映的是评论对决策的影响力。这两个概念在数仓里的处理方式完全不同。前者是行为事实的简单汇总后者本质上是一个“增益分析”问题需要用转化率对比来实现。把这两个口径在建模阶段就分开设计后面会省掉非常多返工。2. 促销敏感度与评论敏感度的口径重建2.1 促销敏感度不只看订单占比更要看折扣弹性口径搭建时我们最终确定了一套“主指标辅助指标”的组合避免单一指标失真。第一个核心指标是“促销订单占比”也就是近90天内带有促销标识的订单数占总下单数的比例。它能综合反映用户对促销的整体依赖度计算简单、业务方容易理解适合做用户筛选的第一道门槛。第二个核心指标是“折扣响应弹性”这个稍微复杂一些。我们不是直接看用户买了多少折扣商品而是看用户在“有促销”和“没促销”两个状态下的下单间隔和消费金额差异。例如折扣响应弹性 近90天促销期日均下单量 / 近90天非促销期日均下单量这个比值越大说明该用户一旦遇到促销购买行为会被明显放大是典型的价格敏感型用户。因为只有一个比值还不够数据里会出现一种特殊用户非促销期完全不买、促销期疯狂下单。这种用户的弹性极高但长期营销价值有限单纯靠促销维系。所以还要配合第三个指标“正价购买占比”用来识别用户是否具备非促销场景下的自然购买力。最终我们给出的促销敏感度分层不是用某一个公式算总分而是用组合条件划分促销敏感度层级判定条件强敏感促销订单占比 ≥ 60%且折扣响应弹性 ≥ 2.0中敏感促销订单占比 40%~60%或弹性 1.5~2.0弱敏感促销订单占比 40%且正价购买占比 ≥ 60%这套口径的优势在于每一层都对应用户的一种经营策略强敏感用户可以做唤醒和清仓触达中敏感用户可以日常发券促转化弱敏感用户则减少促销打扰、重点做新品推荐。2.2 评论敏感度用行为增量来量化“被影响”评论敏感度的口径我们在第一版方案里走了弯路。当时想直接从评论的正负向情感出发统计用户浏览过的评论情感分布再结合是否下单来判断敏感度。后来发现这个逻辑有个漏洞浏览好评多就下单、浏览差评多就放弃这只是心理上的“常理”在数据上缺乏对照。后来改成“行为增量法”思路是用用户自己的转化基线做参照评论影响系数 浏览评论详情后的下单转化率 / 未浏览评论详情时的下单转化率如果这个系数明显大于1说明评论内容对用户的购买决策有正面拉动如果小于1说明评论内容反而在劝退用户接近1则说明用户虽然看了评论但评论几乎不影响决策——这类用户下单可能主要受价格、品牌或习惯驱动。为了避免极短时间窗口带来的数据抖动我们只统计近90天内行为次数大于等于5次的用户低于这个门槛的用户统一归入“评论观察不足”分组不进入敏感度策略。最终的评论敏感度分层评论敏感度层级判定条件正向高敏感评论影响系数 ≥ 1.3负向高敏感评论影响系数 ≤ 0.7弱敏感评论影响系数在 0.7~1.3 之间“负向高敏感”虽然在名单里人数通常不多但价值非常大因为这类用户在下单前极易被差评或中评劝退。识别出这批人之后可以针对性地在评价展示策略上做调整或者由客服在人机交互环节做主动干预。3. 数仓建模从ODS到ADS的落表设计3.1 敏感度主题域的分层规划这套需求虽然最终落到两张应用表上但中间至少要经过ODS、DWD、DWS、ADS四个层级。有些团队图省事用SQL在临时表上直接算完就交给业务短期看效率很高但一旦口径调整或者数据回溯整个链路就全乱了。我们采用的分层方式如下ODS层原样接入订单表、订单明细表、促销活动表、用户行为日志表、商品评论表不做过多的清洗只做增量分区。DWD层完成业务过程事实建模统一字段命名比如订单事实表统一带上is_promo_order标识、promo_type促销类型、discount_amt优惠金额行为日志表拆解出behavior_type和item_id。DWS层按用户维度做汇总将近90天窗口内的促销参与KPI、评论行为KPI沉淀成用户宽表对应dws_user_sensitivity_nd。ADS层面向应用输出生成带分群标签的用户列表对应ads_user_promo_sensitivity和ads_user_review_sensitivity。这样分层最大的好处是ADS层的逻辑可以迭代得很快。比如运营突然说“敏感度阈值调一调”只需要改ADS层的WHERE条件DWS层完全不用动如果DWS层要调整口径ODS和DWD层仍然可以保持稳定不影响底层数据建设。3.2 核心事实表与维表设计要点DWD层的核心事实表围绕三个业务过程展开订单事实表是促销敏感度的基础。字段上必须把促销标识拆细不能只给一个is_promo布尔值。建议保留promo_type字段用字典值区分商品直降、满减、优惠券、秒杀、拼团等类型。为什么必须拆细因为不同类型促销的用户心智完全不同满减用户往往是被凑单驱动秒杀用户是被稀缺感驱动优惠券用户才是真正的价格敏感人群。如果混在一起后面对营销触达渠道的设计就没有参考依据。行为日志事实表是评论敏感度的基础。需要区分商品详情页浏览、评论区域浏览、加购、下单四个行为节点并且要记录对应的item_id、session_id、behavior_time。其中“评论区域浏览”这个行为通常埋点名称不同有的叫review_click有的叫comment_uv清洗阶段必须统一映射。评论事实表用于补充评论本身的情感信息。核心字段包括comment_id、item_id、rating_score、comment_content、comment_tag。如果评论内容已经接了NLP情感打分就保留sentiment_score字段如果还没有初期可以直接用评分分数代替。维表设计上除了常规的用户维和商品维我强烈建议增加一个活动维表。活动维表记录每个促销活动的开始时间、结束时间、活动类型、优惠力度、适用类目。没有这张表DWD层订单事实表里的promo_type只能是个干巴巴的编码没法回答“用户明显对美妆类的大额券敏感、对食品类的满减不敏感”这类深度问题。3.3 调度链路与回刷机制调度链路可以按照日批的方式设计每天凌晨按T-1的全量近90天数据进行重算。这里要注意敏感度指标天然带有滚动窗口特性与普通日报次日累计的方式不同窗口会随着时间自然平移。我们遇到的实际问题是“跨月历史修改促销标识”。促销活动已经结束了一个月但活动结算时才发现某批商品被错误打标导致DWD层订单事实表的部分记录需要更新。这时如果DWS层只依赖当天重算历史分区会被污染。解决方案是建立一套“可回刷”机制DWS层的用户宽表按自然日分区每次调度不仅写当天分区还会基于变更后的原始明细重刷受影响用户近90天所有分区。具体实现上需要维护一张“受影响用户清单表”记录哪些用户在哪个时间段内有订单被修正调度系统根据这张表触发定点回刷。这个机制早期一定要做好否则后面每次涉及口径修正都得全量跑一遍几十亿行的明细表资源成本和组织成本都会很难受。4. 关键计算逻辑的SQL实现思路4.1 促销敏感度的滚动窗口计算DWS层的用户促销指标宽表核心逻辑可以概括为“按用户在90天滚动窗口内聚合”但要加分区间维度。我们实际运行的简化版SQL大致如下-- 基于DWD层订单事实表计算用户近90天促销参与指标 WITH user_order AS ( SELECT user_id, COUNT(order_id) AS total_orders, SUM(CASE WHEN is_promo_order 1 THEN 1 ELSE 0 END) AS promo_orders, SUM(CASE WHEN is_promo_order 0 THEN 1 ELSE 0 END) AS normal_orders, SUM(order_amount) AS total_gmv, SUM(CASE WHEN is_promo_order 1 THEN order_amount ELSE 0 END) AS promo_gmv, SUM(discount_amt) AS total_discount, SUM(CASE WHEN is_promo_order 1 THEN discount_amt ELSE 0 END) AS promo_discount FROM dwd_trade_order_fact WHERE dt DATE_SUB(CURRENT_DATE, 90) AND order_status NOT IN (CANCELED, CLOSED) GROUP BY user_id ), -- 计算促销期与非促销期的日均下单量 user_daily AS ( SELECT user_id, SUM(CASE WHEN is_promo_order 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(DISTINCT CASE WHEN is_promo_order 1 THEN dt END), 0) AS promo_day_orders, SUM(CASE WHEN is_promo_order 0 THEN 1 ELSE 0 END) / NULLIF(COUNT(DISTINCT CASE WHEN is_promo_order 0 THEN dt END), 0) AS normal_day_orders FROM dwd_trade_order_fact WHERE dt DATE_SUB(CURRENT_DATE, 90) AND order_status NOT IN (CANCELED, CLOSED) GROUP BY user_id ) SELECT a.user_id, a.total_orders, a.promo_orders, ROUND(a.promo_orders / a.total_orders, 4) AS promo_order_ratio, ROUND(a.promo_gmv / a.total_gmv, 4) AS promo_gmv_ratio, ROUND(b.promo_day_orders / NULLIF(b.normal_day_orders, 0), 4) AS discount_elasticity, ROUND(1 - a.total_discount / NULLIF(a.total_gmv, 0), 4) AS normal_price_ratio FROM user_order a LEFT JOIN user_daily b ON a.user_id b.user_id这段SQL的关键点在于“促销期日均下单量”的分子分母。分子是促销订单总量分母是促销有下单的日子数而不是总天数。这样才能真正刻画用户在一个促销日里的购买爆发力。如果不除以活跃天而直接除以90那些只在两三场大促下单的用户会被严重稀释算出来的弹性系数全部挤在一起毫无区分度。ADS层的加标签逻辑就是基于上面宽表做CASE WHEN分层再把分层结果写回名单表。日常运营只需要查询ADS表不需要再触碰明细数据。4.2 评论敏感度的行为增益计算评论敏感度的核心计算是构建“浏览评论后转化率”和“未浏览评论转化率”的对照。我们处理的方式是把用户-商品的浏览会话按行为序列切分。由于一个用户在同一个商品上有多次浏览会话直接判断“这个订单之前有没有看过评论”会出现时间序列错乱的问题。我们采用以下逻辑每次商品详情浏览会话内如果用户在浏览评论区域之后同一会话内又发生了加购或下单则记为一次“评论正向转化”。一个更加完整的SQL思路如下-- 行为日志中标记是否浏览过评论区域 WITH behavior_with_flag AS ( SELECT user_id, item_id, session_id, behavior_type, ts, MAX(CASE WHEN behavior_type REVIEW_VIEW THEN 1 ELSE 0 END) OVER (PARTITION BY user_id, item_id, session_id) AS has_review_view FROM dwd_user_behavior_log WHERE dt DATE_SUB(CURRENT_DATE, 90) ), -- 按照会话聚合出两种转化率 session_base AS ( SELECT user_id, session_id, item_id, MAX(has_review_view) AS has_review_view, MAX(CASE WHEN behavior_type ADD_CART THEN 1 ELSE 0 END) AS is_add_cart, MAX(CASE WHEN behavior_type ORDER THEN 1 ELSE 0 END) AS is_order FROM behavior_with_flag GROUP BY user_id, session_id, item_id ) SELECT user_id, COUNT(*) AS session_cnt, SUM(CASE WHEN has_review_view 1 AND is_order 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN has_review_view 1 THEN 1 ELSE 0 END), 0) AS conv_with_review, SUM(CASE WHEN has_review_view 0 AND is_order 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN has_review_view 0 THEN 1 ELSE 0 END), 0) AS conv_without_review, ROUND( SUM(CASE WHEN has_review_view 1 AND is_order 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN has_review_view 1 THEN 1 ELSE 0 END), 0) / NULLIF( SUM(CASE WHEN has_review_view 0 AND is_order 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN has_review_view 0 THEN 1 ELSE 0 END), 0) , 0) , 4) AS review_influence FROM session_base GROUP BY user_id HAVING COUNT(*) 5这里有一个容易踩的细节不能直接做“用户-商品”级别的聚合因为同一个用户在不同商品上可能有的看了评论、有的没看只有在会话级别做对照才是干净的行为增益。用户可能对A商品不敏感但对B商品敏感直接在用户维度聚合会把这种差异抹平。如果想要更精细的结果可以把这个逻辑下沉到“用户-商品类目”维度单独计算用户在某类目下的评论敏感度再通过加权汇总得到用户整体敏感度。这在计算资源充足时推荐做营销精度会明显提升。4.3 基于敏感度矩阵的用户分群最终落到ADS层的用户分群把两个敏感度维度做成四象限矩阵比分别给一组名单更便于业务方理解同时可以沉淀成用户标签用户分群促销敏感度评论敏感度营销策略建议价格口碑双驱动型高高大促重点触达同步维护商品评价价格驱动型高低发券、满减、清仓优先触达口碑驱动型低高做好评价管理推荐新品和高分商品习惯忠诚型低低减少促销打扰用会员权益和复购激励这四类人群在标签表里用四段枚举值存下来业务侧查起来非常直观。实际运营过程中“价格口碑双驱动型”用户通常是最有价值的群体他们对促销有反应同时对差评零容忍一次严重的品控事件很可能直接把这批用户推向竞品。这类用户需要单独沉淀高优名单遇到异常舆情时优先做定向安抚。5. 项目落地中的踩坑复盘5.1 促销活动口径不统一导致的数据打架上线第一周最尴尬的事情出现了运营从促销系统导出的促销订单数和我们从DWD层统计出来的促销订单数对不上。排查后发现原因在于促销标识的判定源头不同。促销系统的口径是“只要订单挂了活动编号就算促销单”哪怕用户用了1元无门槛券也算而我们数仓侧更严格认为优惠金额占比小于某个阈值时对用户购买决策几乎没有影响不应该算作真正被促销驱动。两边数据不一致直接导致业务对整个数仓口径的信任度下降。后来的解决方案是在DWD层新增一个promo_strength字段把促销强度分成强、中、弱三档。订单归属促销单的判断从原本的“是否挂活动编号”改成“是否为中强度以上促销”。同时对外汇报时明确标注促销口径是“排除弱促销后的净促销订单”业务方拿我们的表去对账也需要使用同一套口径。这个字段加得很轻量但彻底解决了跨部门扯皮的问题。5.2 评论标签过度依赖NLP的问题早期做评论敏感度时我把评论情感完全交给NLP情感分析模块处理希望直接利用情感分判断“用户看到的是好评还是差评”。但实际发现NLP模型的F1分数在日常口语化评论上并不理想比如“这个价格还要什么自行车”这种反讽句式情感模型很容易判错。更麻烦的是很多商品详情页的评论区域展示会同时包含好评、中评、差评、追问等多种内容用户可能只看了前3条短评就下单了但行为日志里只会记录“评论区域被浏览”不会记录具体看了哪几条。这时如果再拿全量评论情感做加权误差会被进一步放大。所以我建议在落地评论敏感度时初期不要过度依赖NLP。先用rating_score的分布作为弱特征比如差评占比、中评占比再结合行为增益来计算敏感度。等到样本量和反馈数据足够多之后再逐步把NLP情感分引入作为模型调优的可选特征而不是唯一依据。5.3 滚动窗口回刷引发的指标波动滚动窗口类的敏感度指标有个固有特性每过一天窗口最前面的数据会被剔除最后面会新增一天数据。这会导致昨天还显示“强敏感”的用户今天可能直接降级为“中敏感”。有次大促复盘运营反馈前一天晚上看好的强敏感用户名单第二天打开时人数直接缩小了20%。原因就是窗口平移过程中大促那几天的高频订单被自然挤出了90天窗口。这也是滚动窗口模型的固有特征不是计算错误但如果不在交付时提前说明业务方很容易对数据产生误解。我的处理方式是在ADS层输出表里同时保留两份敏感度结果——90天滚动敏感度和30天短期敏感度。90天用于描摹用户相对稳定的倾向30天用于捕捉近期行为变化。业务方做长周期策略时看90天做短期触达时看30天。两张表数据量都很小维护成本可以接受。6. 上线后的业务应用与个人实操心得6.1 敏感度矩阵在营销侧的用法这套数据上线后很快被接入了三个业务场景。第一个是沉默用户唤醒。运营从“价格驱动型”人群中筛出近90天有订单但最近30天无活跃的用户定向推送大额满减券整体唤醒率比原来全量发送的对照组高出约40%。原因很好解释这批用户本来就被验证过对促销敏感给他们发券是打在需求靶点上。第二个是新品冷启动。口碑驱动型用户被用于新品体验官招募配合高分评价展示新品详情页的转化率显著高于随机用户组。这批用户对价格不敏感但对商品评价质量很在意给他们讲参数、讲品牌故事比发券更有效。第三个是商品评价优化。我们发现“负向高敏感”用户比较集中地出现在几个高频客诉品类里于是推动业务侧在这几个品类优先上线“差评申诉展示”和“问大家”模块用户在对立信息获得解释之后流失率明显回落。6.2 后续扩展思路与团队协作建议敏感度这个主题本身是可以持续深挖的第一批做完之后后续可以考虑做“类目维度的敏感度细分”。同一个用户可能对食品类促销敏感、对家电类促销无感因为两类商品的决策链路完全不同。如果DWS层按用户-一级类目维度聚合ADS层就能输出更精细的类目×敏感度标签营销策略的颗粒度还会再上一个台阶。最后再分享一个协作层面的体会这类需求真正决定成败的不是SQL写得多好而是最前面那句“业务方到底要什么”有没有被反复追问清楚。促销敏感度光是口径定义我们就和运营开了三轮会每一轮都会推翻一些初始假设。数仓建模最大的成本是返工成本而返工几乎都是口径没有对齐造成的。所以接到类似需求时建议不要急着建表先把自己当成业务分析师把“然后呢”“用在哪里”“策略是什么”这几个问题问完再回来做模型设计和底层建设。这个时间花得非常值得。 
长河君 · 2026/10/5 12:28:05 · 阅读 1.2k 数仓建模实战：促销敏感度与评论敏感度用户分群全解析
业务方跑过来的时候说辞特别简单“帮我拉一批用户就是那种一看到打折就买的人还有那种买东西前必须刷半天评论的人。”听起来像是个取数需求但落到数仓这边这其实是两个完整的分析主题——促销敏感度和评论敏感度。名字听起来不复杂真正做起来之后才发现从口径定义到模型设计再到调度回刷每一步都有不少坑。这篇就把当时从需求拆解到数仓落地、再到最终上线的完整过程记录下来给后面接类似“数仓建模”需求的同学一个参考。1. 业务侧的一句话需求拆开是三张口径问题1.1 “哪些用户爱买打折货”背后的经营意图业务方一开始说自己要“促销敏感用户名单”可如果只取一份“促销订单占比最高的用户”其实解决不了任何经营问题。我建议先倒推回去有了这份名单运营打算怎么用。聊完之后发现真实使用场景有三个大促前定向发券、清库存时圈选人群、以及评估促销活动是否把新客转化成老客。三个场景对“促销敏感度”的定义要求完全不同。大促前发券看重的是用户对折扣力度的反应速度也就是“给多少优惠会触发购买”偏向价格弹性。清库存圈人看重的是用户近期有没有频繁购买高折扣商品偏向促销参与频次。新老客转化评估看重的是用户平时购买正价商品的比例如果原本买正价的人突然只买促销款那可能是客群质量下降的信号。这三种诉求如果共用一套指标就会出现业务方拿着同一张表各说各话的情况。所以在数仓建模之前第一步不是建表而是把“促销敏感度”拆成至少三个可量化口径促销参与深度、折扣响应弹性和正价购买稳定性。这三个口径可以共存于一张宽表里但绝对不能混成一个数值。1.2 “哪些用户看评论就下单”对应的行为链路评论敏感度比促销敏感度更绕。用户“看评论”这个行为在埋点里好识别但“因为看了评论才下单”这个因果关系埋点永远直接给不出来。业务方最初的提法是“找出喜欢看评论的人”。这其实是一个行为统计问题统计评论详情页的浏览PV、UV就行。但后来运营的真实意图浮出水面他们想优化商品详情页的评价模块同时给“容易被中差评劝退”的用户做定向安抚。这就涉及两个不同概念评论查看偏好用户下单前是否经常浏览评论区域反映的是购物习惯。评论购买影响度用户看完评论后的转化率变化反映的是评论对决策的影响力。这两个概念在数仓里的处理方式完全不同。前者是行为事实的简单汇总后者本质上是一个“增益分析”问题需要用转化率对比来实现。把这两个口径在建模阶段就分开设计后面会省掉非常多返工。2. 促销敏感度与评论敏感度的口径重建2.1 促销敏感度不只看订单占比更要看折扣弹性口径搭建时我们最终确定了一套“主指标辅助指标”的组合避免单一指标失真。第一个核心指标是“促销订单占比”也就是近90天内带有促销标识的订单数占总下单数的比例。它能综合反映用户对促销的整体依赖度计算简单、业务方容易理解适合做用户筛选的第一道门槛。第二个核心指标是“折扣响应弹性”这个稍微复杂一些。我们不是直接看用户买了多少折扣商品而是看用户在“有促销”和“没促销”两个状态下的下单间隔和消费金额差异。例如折扣响应弹性 近90天促销期日均下单量 / 近90天非促销期日均下单量这个比值越大说明该用户一旦遇到促销购买行为会被明显放大是典型的价格敏感型用户。因为只有一个比值还不够数据里会出现一种特殊用户非促销期完全不买、促销期疯狂下单。这种用户的弹性极高但长期营销价值有限单纯靠促销维系。所以还要配合第三个指标“正价购买占比”用来识别用户是否具备非促销场景下的自然购买力。最终我们给出的促销敏感度分层不是用某一个公式算总分而是用组合条件划分促销敏感度层级判定条件强敏感促销订单占比 ≥ 60%且折扣响应弹性 ≥ 2.0中敏感促销订单占比 40%~60%或弹性 1.5~2.0弱敏感促销订单占比 40%且正价购买占比 ≥ 60%这套口径的优势在于每一层都对应用户的一种经营策略强敏感用户可以做唤醒和清仓触达中敏感用户可以日常发券促转化弱敏感用户则减少促销打扰、重点做新品推荐。2.2 评论敏感度用行为增量来量化“被影响”评论敏感度的口径我们在第一版方案里走了弯路。当时想直接从评论的正负向情感出发统计用户浏览过的评论情感分布再结合是否下单来判断敏感度。后来发现这个逻辑有个漏洞浏览好评多就下单、浏览差评多就放弃这只是心理上的“常理”在数据上缺乏对照。后来改成“行为增量法”思路是用用户自己的转化基线做参照评论影响系数 浏览评论详情后的下单转化率 / 未浏览评论详情时的下单转化率如果这个系数明显大于1说明评论内容对用户的购买决策有正面拉动如果小于1说明评论内容反而在劝退用户接近1则说明用户虽然看了评论但评论几乎不影响决策——这类用户下单可能主要受价格、品牌或习惯驱动。为了避免极短时间窗口带来的数据抖动我们只统计近90天内行为次数大于等于5次的用户低于这个门槛的用户统一归入“评论观察不足”分组不进入敏感度策略。最终的评论敏感度分层评论敏感度层级判定条件正向高敏感评论影响系数 ≥ 1.3负向高敏感评论影响系数 ≤ 0.7弱敏感评论影响系数在 0.7~1.3 之间“负向高敏感”虽然在名单里人数通常不多但价值非常大因为这类用户在下单前极易被差评或中评劝退。识别出这批人之后可以针对性地在评价展示策略上做调整或者由客服在人机交互环节做主动干预。3. 数仓建模从ODS到ADS的落表设计3.1 敏感度主题域的分层规划这套需求虽然最终落到两张应用表上但中间至少要经过ODS、DWD、DWS、ADS四个层级。有些团队图省事用SQL在临时表上直接算完就交给业务短期看效率很高但一旦口径调整或者数据回溯整个链路就全乱了。我们采用的分层方式如下ODS层原样接入订单表、订单明细表、促销活动表、用户行为日志表、商品评论表不做过多的清洗只做增量分区。DWD层完成业务过程事实建模统一字段命名比如订单事实表统一带上is_promo_order标识、promo_type促销类型、discount_amt优惠金额行为日志表拆解出behavior_type和item_id。DWS层按用户维度做汇总将近90天窗口内的促销参与KPI、评论行为KPI沉淀成用户宽表对应dws_user_sensitivity_nd。ADS层面向应用输出生成带分群标签的用户列表对应ads_user_promo_sensitivity和ads_user_review_sensitivity。这样分层最大的好处是ADS层的逻辑可以迭代得很快。比如运营突然说“敏感度阈值调一调”只需要改ADS层的WHERE条件DWS层完全不用动如果DWS层要调整口径ODS和DWD层仍然可以保持稳定不影响底层数据建设。3.2 核心事实表与维表设计要点DWD层的核心事实表围绕三个业务过程展开订单事实表是促销敏感度的基础。字段上必须把促销标识拆细不能只给一个is_promo布尔值。建议保留promo_type字段用字典值区分商品直降、满减、优惠券、秒杀、拼团等类型。为什么必须拆细因为不同类型促销的用户心智完全不同满减用户往往是被凑单驱动秒杀用户是被稀缺感驱动优惠券用户才是真正的价格敏感人群。如果混在一起后面对营销触达渠道的设计就没有参考依据。行为日志事实表是评论敏感度的基础。需要区分商品详情页浏览、评论区域浏览、加购、下单四个行为节点并且要记录对应的item_id、session_id、behavior_time。其中“评论区域浏览”这个行为通常埋点名称不同有的叫review_click有的叫comment_uv清洗阶段必须统一映射。评论事实表用于补充评论本身的情感信息。核心字段包括comment_id、item_id、rating_score、comment_content、comment_tag。如果评论内容已经接了NLP情感打分就保留sentiment_score字段如果还没有初期可以直接用评分分数代替。维表设计上除了常规的用户维和商品维我强烈建议增加一个活动维表。活动维表记录每个促销活动的开始时间、结束时间、活动类型、优惠力度、适用类目。没有这张表DWD层订单事实表里的promo_type只能是个干巴巴的编码没法回答“用户明显对美妆类的大额券敏感、对食品类的满减不敏感”这类深度问题。3.3 调度链路与回刷机制调度链路可以按照日批的方式设计每天凌晨按T-1的全量近90天数据进行重算。这里要注意敏感度指标天然带有滚动窗口特性与普通日报次日累计的方式不同窗口会随着时间自然平移。我们遇到的实际问题是“跨月历史修改促销标识”。促销活动已经结束了一个月但活动结算时才发现某批商品被错误打标导致DWD层订单事实表的部分记录需要更新。这时如果DWS层只依赖当天重算历史分区会被污染。解决方案是建立一套“可回刷”机制DWS层的用户宽表按自然日分区每次调度不仅写当天分区还会基于变更后的原始明细重刷受影响用户近90天所有分区。具体实现上需要维护一张“受影响用户清单表”记录哪些用户在哪个时间段内有订单被修正调度系统根据这张表触发定点回刷。这个机制早期一定要做好否则后面每次涉及口径修正都得全量跑一遍几十亿行的明细表资源成本和组织成本都会很难受。4. 关键计算逻辑的SQL实现思路4.1 促销敏感度的滚动窗口计算DWS层的用户促销指标宽表核心逻辑可以概括为“按用户在90天滚动窗口内聚合”但要加分区间维度。我们实际运行的简化版SQL大致如下-- 基于DWD层订单事实表计算用户近90天促销参与指标 WITH user_order AS ( SELECT user_id, COUNT(order_id) AS total_orders, SUM(CASE WHEN is_promo_order 1 THEN 1 ELSE 0 END) AS promo_orders, SUM(CASE WHEN is_promo_order 0 THEN 1 ELSE 0 END) AS normal_orders, SUM(order_amount) AS total_gmv, SUM(CASE WHEN is_promo_order 1 THEN order_amount ELSE 0 END) AS promo_gmv, SUM(discount_amt) AS total_discount, SUM(CASE WHEN is_promo_order 1 THEN discount_amt ELSE 0 END) AS promo_discount FROM dwd_trade_order_fact WHERE dt DATE_SUB(CURRENT_DATE, 90) AND order_status NOT IN (CANCELED, CLOSED) GROUP BY user_id ), -- 计算促销期与非促销期的日均下单量 user_daily AS ( SELECT user_id, SUM(CASE WHEN is_promo_order 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(DISTINCT CASE WHEN is_promo_order 1 THEN dt END), 0) AS promo_day_orders, SUM(CASE WHEN is_promo_order 0 THEN 1 ELSE 0 END) / NULLIF(COUNT(DISTINCT CASE WHEN is_promo_order 0 THEN dt END), 0) AS normal_day_orders FROM dwd_trade_order_fact WHERE dt DATE_SUB(CURRENT_DATE, 90) AND order_status NOT IN (CANCELED, CLOSED) GROUP BY user_id ) SELECT a.user_id, a.total_orders, a.promo_orders, ROUND(a.promo_orders / a.total_orders, 4) AS promo_order_ratio, ROUND(a.promo_gmv / a.total_gmv, 4) AS promo_gmv_ratio, ROUND(b.promo_day_orders / NULLIF(b.normal_day_orders, 0), 4) AS discount_elasticity, ROUND(1 - a.total_discount / NULLIF(a.total_gmv, 0), 4) AS normal_price_ratio FROM user_order a LEFT JOIN user_daily b ON a.user_id b.user_id这段SQL的关键点在于“促销期日均下单量”的分子分母。分子是促销订单总量分母是促销有下单的日子数而不是总天数。这样才能真正刻画用户在一个促销日里的购买爆发力。如果不除以活跃天而直接除以90那些只在两三场大促下单的用户会被严重稀释算出来的弹性系数全部挤在一起毫无区分度。ADS层的加标签逻辑就是基于上面宽表做CASE WHEN分层再把分层结果写回名单表。日常运营只需要查询ADS表不需要再触碰明细数据。4.2 评论敏感度的行为增益计算评论敏感度的核心计算是构建“浏览评论后转化率”和“未浏览评论转化率”的对照。我们处理的方式是把用户-商品的浏览会话按行为序列切分。由于一个用户在同一个商品上有多次浏览会话直接判断“这个订单之前有没有看过评论”会出现时间序列错乱的问题。我们采用以下逻辑每次商品详情浏览会话内如果用户在浏览评论区域之后同一会话内又发生了加购或下单则记为一次“评论正向转化”。一个更加完整的SQL思路如下-- 行为日志中标记是否浏览过评论区域 WITH behavior_with_flag AS ( SELECT user_id, item_id, session_id, behavior_type, ts, MAX(CASE WHEN behavior_type REVIEW_VIEW THEN 1 ELSE 0 END) OVER (PARTITION BY user_id, item_id, session_id) AS has_review_view FROM dwd_user_behavior_log WHERE dt DATE_SUB(CURRENT_DATE, 90) ), -- 按照会话聚合出两种转化率 session_base AS ( SELECT user_id, session_id, item_id, MAX(has_review_view) AS has_review_view, MAX(CASE WHEN behavior_type ADD_CART THEN 1 ELSE 0 END) AS is_add_cart, MAX(CASE WHEN behavior_type ORDER THEN 1 ELSE 0 END) AS is_order FROM behavior_with_flag GROUP BY user_id, session_id, item_id ) SELECT user_id, COUNT(*) AS session_cnt, SUM(CASE WHEN has_review_view 1 AND is_order 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN has_review_view 1 THEN 1 ELSE 0 END), 0) AS conv_with_review, SUM(CASE WHEN has_review_view 0 AND is_order 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN has_review_view 0 THEN 1 ELSE 0 END), 0) AS conv_without_review, ROUND( SUM(CASE WHEN has_review_view 1 AND is_order 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN has_review_view 1 THEN 1 ELSE 0 END), 0) / NULLIF( SUM(CASE WHEN has_review_view 0 AND is_order 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN has_review_view 0 THEN 1 ELSE 0 END), 0) , 0) , 4) AS review_influence FROM session_base GROUP BY user_id HAVING COUNT(*) 5这里有一个容易踩的细节不能直接做“用户-商品”级别的聚合因为同一个用户在不同商品上可能有的看了评论、有的没看只有在会话级别做对照才是干净的行为增益。用户可能对A商品不敏感但对B商品敏感直接在用户维度聚合会把这种差异抹平。如果想要更精细的结果可以把这个逻辑下沉到“用户-商品类目”维度单独计算用户在某类目下的评论敏感度再通过加权汇总得到用户整体敏感度。这在计算资源充足时推荐做营销精度会明显提升。4.3 基于敏感度矩阵的用户分群最终落到ADS层的用户分群把两个敏感度维度做成四象限矩阵比分别给一组名单更便于业务方理解同时可以沉淀成用户标签用户分群促销敏感度评论敏感度营销策略建议价格口碑双驱动型高高大促重点触达同步维护商品评价价格驱动型高低发券、满减、清仓优先触达口碑驱动型低高做好评价管理推荐新品和高分商品习惯忠诚型低低减少促销打扰用会员权益和复购激励这四类人群在标签表里用四段枚举值存下来业务侧查起来非常直观。实际运营过程中“价格口碑双驱动型”用户通常是最有价值的群体他们对促销有反应同时对差评零容忍一次严重的品控事件很可能直接把这批用户推向竞品。这类用户需要单独沉淀高优名单遇到异常舆情时优先做定向安抚。5. 项目落地中的踩坑复盘5.1 促销活动口径不统一导致的数据打架上线第一周最尴尬的事情出现了运营从促销系统导出的促销订单数和我们从DWD层统计出来的促销订单数对不上。排查后发现原因在于促销标识的判定源头不同。促销系统的口径是“只要订单挂了活动编号就算促销单”哪怕用户用了1元无门槛券也算而我们数仓侧更严格认为优惠金额占比小于某个阈值时对用户购买决策几乎没有影响不应该算作真正被促销驱动。两边数据不一致直接导致业务对整个数仓口径的信任度下降。后来的解决方案是在DWD层新增一个promo_strength字段把促销强度分成强、中、弱三档。订单归属促销单的判断从原本的“是否挂活动编号”改成“是否为中强度以上促销”。同时对外汇报时明确标注促销口径是“排除弱促销后的净促销订单”业务方拿我们的表去对账也需要使用同一套口径。这个字段加得很轻量但彻底解决了跨部门扯皮的问题。5.2 评论标签过度依赖NLP的问题早期做评论敏感度时我把评论情感完全交给NLP情感分析模块处理希望直接利用情感分判断“用户看到的是好评还是差评”。但实际发现NLP模型的F1分数在日常口语化评论上并不理想比如“这个价格还要什么自行车”这种反讽句式情感模型很容易判错。更麻烦的是很多商品详情页的评论区域展示会同时包含好评、中评、差评、追问等多种内容用户可能只看了前3条短评就下单了但行为日志里只会记录“评论区域被浏览”不会记录具体看了哪几条。这时如果再拿全量评论情感做加权误差会被进一步放大。所以我建议在落地评论敏感度时初期不要过度依赖NLP。先用rating_score的分布作为弱特征比如差评占比、中评占比再结合行为增益来计算敏感度。等到样本量和反馈数据足够多之后再逐步把NLP情感分引入作为模型调优的可选特征而不是唯一依据。5.3 滚动窗口回刷引发的指标波动滚动窗口类的敏感度指标有个固有特性每过一天窗口最前面的数据会被剔除最后面会新增一天数据。这会导致昨天还显示“强敏感”的用户今天可能直接降级为“中敏感”。有次大促复盘运营反馈前一天晚上看好的强敏感用户名单第二天打开时人数直接缩小了20%。原因就是窗口平移过程中大促那几天的高频订单被自然挤出了90天窗口。这也是滚动窗口模型的固有特征不是计算错误但如果不在交付时提前说明业务方很容易对数据产生误解。我的处理方式是在ADS层输出表里同时保留两份敏感度结果——90天滚动敏感度和30天短期敏感度。90天用于描摹用户相对稳定的倾向30天用于捕捉近期行为变化。业务方做长周期策略时看90天做短期触达时看30天。两张表数据量都很小维护成本可以接受。6. 上线后的业务应用与个人实操心得6.1 敏感度矩阵在营销侧的用法这套数据上线后很快被接入了三个业务场景。第一个是沉默用户唤醒。运营从“价格驱动型”人群中筛出近90天有订单但最近30天无活跃的用户定向推送大额满减券整体唤醒率比原来全量发送的对照组高出约40%。原因很好解释这批用户本来就被验证过对促销敏感给他们发券是打在需求靶点上。第二个是新品冷启动。口碑驱动型用户被用于新品体验官招募配合高分评价展示新品详情页的转化率显著高于随机用户组。这批用户对价格不敏感但对商品评价质量很在意给他们讲参数、讲品牌故事比发券更有效。第三个是商品评价优化。我们发现“负向高敏感”用户比较集中地出现在几个高频客诉品类里于是推动业务侧在这几个品类优先上线“差评申诉展示”和“问大家”模块用户在对立信息获得解释之后流失率明显回落。6.2 后续扩展思路与团队协作建议敏感度这个主题本身是可以持续深挖的第一批做完之后后续可以考虑做“类目维度的敏感度细分”。同一个用户可能对食品类促销敏感、对家电类促销无感因为两类商品的决策链路完全不同。如果DWS层按用户-一级类目维度聚合ADS层就能输出更精细的类目×敏感度标签营销策略的颗粒度还会再上一个台阶。最后再分享一个协作层面的体会这类需求真正决定成败的不是SQL写得多好而是最前面那句“业务方到底要什么”有没有被反复追问清楚。促销敏感度光是口径定义我们就和运营开了三轮会每一轮都会推翻一些初始假设。数仓建模最大的成本是返工成本而返工几乎都是口径没有对齐造成的。所以接到类似需求时建议不要急着建表先把自己当成业务分析师把“然后呢”“用在哪里”“策略是什么”这几个问题问完再回来做模型设计和底层建设。这个时间花得非常值得。