企业官网建设流程全解析

业务方跑过来的时候，说辞特别简单：“帮我拉一批用户，就是那种一看到打折就买的人，还有那种买东西前必须刷半天评论的人。”听起来像是个取数需求，但落到数仓这边，这其实是两个完整的分析主题——促销敏感度和评论敏感度。名字听起来不复杂，真正做起来之后才发现，从口径定义到模型设计再到调度回刷，每一步都有不少坑。这篇就把当时从需求拆解到数仓落地、再到最终上线的完整过程记录下来，给后面接类似“数仓建模”需求的同学一个参考。

1. 业务侧的一句话需求，拆开是三张口径问题

1.1 “哪些用户爱买打折货”背后的经营意图

业务方一开始说自己要“促销敏感用户名单”，可如果只取一份“促销订单占比最高的用户”，其实解决不了任何经营问题。我建议先倒推回去：有了这份名单，运营打算怎么用。

聊完之后发现，真实使用场景有三个：大促前定向发券、清库存时圈选人群、以及评估促销活动是否把新客转化成老客。三个场景对“促销敏感度”的定义要求完全不同。

• 大促前发券，看重的是用户对折扣力度的反应速度，也就是“给多少优惠会触发购买”，偏向价格弹性。
• 清库存圈人，看重的是用户近期有没有频繁购买高折扣商品，偏向促销参与频次。
• 新老客转化评估，看重的是用户平时购买正价商品的比例，如果原本买正价的人突然只买促销款，那可能是客群质量下降的信号。

这三种诉求如果共用一套指标，就会出现业务方拿着同一张表，各说各话的情况。所以在数仓建模之前，第一步不是建表，而是把“促销敏感度”拆成至少三个可量化口径：促销参与深度、折扣响应弹性和正价购买稳定性。这三个口径可以共存于一张宽表里，但绝对不能混成一个数值。

1.2 “哪些用户看评论就下单”对应的行为链路

评论敏感度比促销敏感度更绕。用户“看评论”这个行为在埋点里好识别，但“因为看了评论才下单”这个因果关系，埋点永远直接给不出来。

业务方最初的提法是“找出喜欢看评论的人”。这其实是一个行为统计问题：统计评论详情页的浏览PV、UV就行。但后来运营的真实意图浮出水面，他们想优化商品详情页的评价模块，同时给“容易被中差评劝退”的用户做定向安抚。这就涉及两个不同概念：

• 评论查看偏好：用户下单前是否经常浏览评论区域，反映的是购物习惯。
• 评论购买影响度：用户看完评论后的转化率变化，反映的是评论对决策的影响力。

这两个概念在数仓里的处理方式完全不同。前者是行为事实的简单汇总，后者本质上是一个“增益分析”问题，需要用转化率对比来实现。把这两个口径在建模阶段就分开设计，后面会省掉非常多返工。

2. 促销敏感度与评论敏感度的口径重建

2.1 促销敏感度：不只看订单占比，更要看折扣弹性

口径搭建时，我们最终确定了一套“主指标+辅助指标”的组合，避免单一指标失真。

第一个核心指标是“促销订单占比”，也就是近90天内带有促销标识的订单数占总下单数的比例。它能综合反映用户对促销的整体依赖度，计算简单、业务方容易理解，适合做用户筛选的第一道门槛。

第二个核心指标是“折扣响应弹性”，这个稍微复杂一些。我们不是直接看用户买了多少折扣商品，而是看用户在“有促销”和“没促销”两个状态下的下单间隔和消费金额差异。例如：

折扣响应弹性 = 近90天促销期日均下单量 / 近90天非促销期日均下单量

这个比值越大，说明该用户一旦遇到促销，购买行为会被明显放大，是典型的价格敏感型用户。

因为只有一个比值还不够，数据里会出现一种特殊用户：非促销期完全不买、促销期疯狂下单。这种用户的弹性极高，但长期营销价值有限，单纯靠促销维系。所以还要配合第三个指标“正价购买占比”，用来识别用户是否具备非促销场景下的自然购买力。

最终我们给出的促销敏感度分层不是用某一个公式算总分，而是用组合条件划分：

这套口径的优势在于，每一层都对应用户的一种经营策略：强敏感用户可以做唤醒和清仓触达，中敏感用户可以日常发券促转化，弱敏感用户则减少促销打扰、重点做新品推荐。

2.2 评论敏感度：用行为增量来量化“被影响”

评论敏感度的口径，我们在第一版方案里走了弯路。当时想直接从评论的正负向情感出发，统计用户浏览过的评论情感分布，再结合是否下单来判断敏感度。后来发现这个逻辑有个漏洞：浏览好评多就下单、浏览差评多就放弃，这只是心理上的“常理”，在数据上缺乏对照。

后来改成“行为增量法”，思路是用用户自己的转化基线做参照：

评论影响系数 = 浏览评论详情后的下单转化率 / 未浏览评论详情时的下单转化率

如果这个系数明显大于1，说明评论内容对用户的购买决策有正面拉动；如果小于1，说明评论内容反而在劝退用户；接近1则说明用户虽然看了评论，但评论几乎不影响决策——这类用户下单可能主要受价格、品牌或习惯驱动。

为了避免极短时间窗口带来的数据抖动，我们只统计近90天内行为次数大于等于5次的用户，低于这个门槛的用户统一归入“评论观察不足”分组，不进入敏感度策略。

最终的评论敏感度分层：

“负向高敏感”虽然在名单里人数通常不多，但价值非常大，因为这类用户在下单前极易被差评或中评劝退。识别出这批人之后，可以针对性地在评价展示策略上做调整，或者由客服在人机交互环节做主动干预。

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写得多好，而是最前面那句“业务方到底要什么”有没有被反复追问清楚。促销敏感度光是口径定义，我们就和运营开了三轮会，每一轮都会推翻一些初始假设。数仓建模最大的成本是返工成本，而返工几乎都是口径没有对齐造成的。所以接到类似需求时，建议不要急着建表，先把自己当成业务分析师，把“然后呢”“用在哪里”“策略是什么”这几个问题问完，再回来做模型设计和底层建设。这个时间花得非常值得。