13 KiB
时间分层机制：需求明确“四层时间分层（近2天/近1月/近3月/全量）”，方案只写了更新频率，需补齐具体实现（分区策略/分层表或物化汇总层/定期归档与清理作业）。
DDL 完整性：补充说明中提到缺失的表（如 cfg_tier_effective_period、dws_assistant_salary_calc、dws_member_visit_detail、dws_finance_discount_detail、dws_finance_recharge_summary、dws_finance_expense_summary）需要在 schema_dws.sql 里落全；方案里写了“更新DDL”，但应明确完整DDL清单与字段级定义。
薪酬规则与生效期：档位、奖金、规则有“按月/按时间生效”的要求，方案目前只有 cfg_performance_tier/cfg_bonus_rules，需要补充生效期字段或独立“规则生效期配置表”，否则历史月份口径会错。
SCD2 / as-of 口径：助教等级是SCD2维度，历史月份不能直接用“当前等级”。方案需明确“按有效期 as-of join”的取数规则。
技能枚举规范：需求要求用 skill_id 判断基础课/附加课；方案应明确 skill_id→课程类型映射（可用配置表），避免 skill_name 漏记。
滚动区间统计：需求中明确 7/10/15/30/60/90 天窗口，方案未明确存储方式（建议在 dws_assistant_customer_stats、dws_member_consumption_summary 中直接落多窗口字段，或新增滚动汇总表）。
财务口径矩阵需全覆盖：方案已有“数据来源矩阵”，但需扩展至财务页面每一项指标（发生额/优惠拆分/确认收入/现金流/充值/平台回款/支出结构），确保每一项都有明确字段+公式+来源表。 手工导入表规范：支出/平台回款/充值提成的Excel导入要补“字段定义、时间粒度、门店维度、去重与校验规则”，否则实现阶段会反复返工。
区域/房型维表：方案已有 cfg_area_category，但需落地“具体映射规则 + 默认兜底 + 异常值处理”，并与 BD_manual_dim_table.md 一致。
更新
时间口径定义：本周/上周/本季度/上季度/最近半年不含本月 等窗口的“起止边界”为月第一天0点。周起始日为周一。
环比规则：开启对比时，是“对比上一个等长区间”相比。
有效业绩的排除规则：仅对“助教废除表”的记录进行处理排除。其影响绩效。
新入职定档规则：月1日0点之后入住的，计算为新入职。入职日以助教表入职时间为准。
Top3 奖金排名口径：按绩效总小时数。如遇并列则都算，比如2个第一，则记为2个第一，一个第三。
充值提成规则：比例/阶梯/时间口径缺失：通过手动导入表格，表格中会明确月份，提成关联充值订单金额和助教获得的提成金额。
大客户优惠/其他优惠划分规则：目前需要抽样分析。 平台回款/服务费口径：明确导入数据字段包含：回款金额、佣金、服务费、回款日期、平台类型、订单关联键。
散客处理：member_id=0 的客户是散客。不进入客户维度统计。
门店/租户范围：现在只有一个门店，一个租户。
我想让你帮我基于DWS层的数据（也可使用DWD层），设计2个指数算法：
- 客户召回指数：根据客户的到店时间，计算由助教或工作人员进行召回的必要性和紧急程度。算法不仅尊重每个客户的到店周期习惯，还要对新客户和刚充值客户进行一定的召回倾向。
- 客户与助教亲密程度：根据单个客户与单个助教发生的服务关系，为助教的约课精力分配，约课成功率进行推算参考。计算2个对象的亲密程度。附加课（激励/超休）权重是基础课的1.5倍。重要的指标包括服务频次，为其充值（服务开始到结束后的1个小时内发生的充值即算做为其充值。此逻辑仅在指数算法有效），最后一次服务发生到现在的间隔等。次重要的是每次服务的时长（同一个客人，对某一个助教的服务，间隔小于4小时，则算作同次服务）。若一段时间内频次，频率出现激增，则加重权重。 注意：指数算法需要符合人性直觉，对时间间隔周期敏感。指数会周期更新（1小时到1天不等，根据实际业务进行调整），计算的分数会直接覆盖旧分数，是一个动态的实时的分数。我建议算法没有最高分，是一个线性的分数，更新完一轮后，将最高分映射为满分上限10.0分。在0.0-10.0区间内，映射所有分数。
参考资料如下，告诉我你的实施计划。
指数算法方案（假设所有输入特征已具备）
统一约定：以“天”为时间单位；回溯窗口最多 60 天；指数每轮重算并覆盖旧分数。
所有指数先计算 Raw Score（无上限），再映射为 Display Score（0.0–10.0） 用于展示与排序。
0. 通用函数与参数
0.1 时间衰减函数（半衰期模型）
设事件距今天数为 (d \ge 0)，半衰期为 (h>0)（天）：
[ \mathrm{decay}(d;h)=\exp\left(-\ln(2)\cdot \frac{d}{h}\right) ]
解释：当 (d=h) 时权重衰减到 0.5；越近权重越大，符合“近两周更重要”的直觉。
0.2 更新窗口
- 回溯：最近 (W=60) 天内的到店/服务/充值事件
- 近期重点：半衰期通常取 (7\sim 14) 天
1) 客户召回指数（Recall Index, RI）
1.1 目标
衡量“是否需要召回”及“紧急程度”。
同时尊重客户个人到店周期，并对 新客户、刚充值客户 增加召回倾向。
1.2 假设输入特征（概念层，不细化字段）
对每个客户 (c)，假设已具备：
- (t): 距离最近一次到店（或最近一次服务结束）已过去的天数
- (\mu): 客户过去 60 天到店间隔的“典型周期”（建议中位数）
- (\sigma): 客户到店间隔波动尺度（建议 MAD / IQR 等稳健尺度）
- (d_{\text{first}}): 距离首访的天数（若无首访则视为很大）
- (d_{\text{re}}): 距离最近一次充值的天数（若无充值则视为很大）
- (n_{14}, n_{60}): 近 14 天/60 天到店（或会话）次数
注：若 (t>60)，可按 (t=60) 截断用于衰减计算，避免“太久不来”占用资源。
1.3 参数（可调默认值）
- (\sigma_0=2)：波动下限（天），避免 (\sigma) 过小导致超期过敏
- 新客半衰期 (h_{\text{new}}=7)
- 刚充值半衰期 (h_{\text{re}}=10)
- 召回各分量权重：
- (w_{\text{over}}=3.0)（超期紧急性，主导）
- (w_{\text{new}}=1.0)
- (w_{\text{re}}=1.0)
- (w_{\text{hot}}=1.0)（近期活跃后断档）
- 防除零：(\epsilon=10^{-6})
1.4 计算步骤（Raw Score）
(1) 超期紧急性（尊重个人周期）
先做稳健标准化超期量： [ \sigma'=\max(\sigma,\sigma_0) ] [ z=\max\left(0,\frac{t-\mu}{\sigma'}\right) ] 将其映射到 (0\sim 1) 的“超期强度”（越超期越接近 1）： [ \mathrm{overdue}=1-\exp(-z) ]
(2) 新客户召回倾向（快衰减）
设“新客”条件为 (d_{\text{first}}\le 60)： [ \mathrm{new_bonus}= \begin{cases} \mathrm{decay}(d_{\text{first}};h_{\text{new}}), & d_{\text{first}}\le 60\ 0, & \text{否则} \end{cases} ]
(3) 刚充值召回倾向（快衰减）
设“近期充值”条件为 (d_{\text{re}}\le 60)： [ \mathrm{re_bonus}= \begin{cases} \mathrm{decay}(d_{\text{re}};h_{\text{re}}), & d_{\text{re}}\le 60\ 0, & \text{否则} \end{cases} ]
(4) 近期活跃后断档加重（“热了又断”更值得召回）
定义短期/长期活跃率： [ r_{14}=\frac{n_{14}}{14},\quad r_{60}=\frac{n_{60}+1}{60} ] 活跃比： [ \mathrm{hot_ratio}=\frac{r_{14}}{r_{60}+\epsilon} ] 将“高于常态”的部分做对数压缩（避免爆炸）： [ \mathrm{hot_drop}=\max\left(0,\ln\left(1+(\mathrm{hot_ratio}-1)\right)\right) ]
(5) 汇总 Raw Score（无上限）
[ RI_{\text{raw}}= w_{\text{over}}\cdot \mathrm{overdue} +w_{\text{new}}\cdot \mathrm{new_bonus} +w_{\text{re}}\cdot \mathrm{re_bonus} +w_{\text{hot}}\cdot \mathrm{hot_drop} ]
2) 客户-助教亲密指数（Intimacy Index, II）
2.1 目标
衡量“客户 (c) 与助教 (a) 的关系强度与近期温度”，用于：
- 助教约课精力分配
- 约课成功率的先验参考
强调：
- 附加课权重 = 基础课的 1.5 倍
- 指标重要性：频次、归因充值、最近一次间隔 > 时长
- 若短期频率激增，权重要加重
2.2 假设输入特征（概念层）
对每个客户-助教对 ((c,a))，假设已具备（近 60 天）：
- 会话集合 (i\in S)，每个会话有：
- 距今天数 (\Delta d_i)
- 时长（分钟）(\mathrm{dur}_i)
- 课型权重 (\tau_i\in{1.0,1.5})（基础/附加）
- 最近一次会话距今天数：(d_{\text{last}})
- 归因充值集合 (j\in R)，每笔充值有：
- 金额 (\mathrm{amt}_j)
- 距今天数 (\Delta d^{(r)}_j)
2.3 参数（可调默认值）
- 会话衰减半衰期 (h_{\text{sess}}=14)
- 最近一次衰减半衰期 (h_{\text{last}}=10)
- 充值衰减半衰期 (h_{\text{pay}}=21)
- 金额压缩基准 (A_0=500)（选门店常见充值档位）
- Burst（激增）检测半衰期：
- 短期 (h_{\text{short}}=7)
- 长期 (h_{\text{long}}=30)
- 汇总权重：
- (w_F=2.0)（频次）
- (w_R=1.5)（最近一次）
- (w_M=2.0)（归因充值）
- (w_D=0.5)（时长）
- 激增放大系数 (\gamma=0.6)
- 防除零：(\epsilon=10^{-6})
2.4 计算步骤（Raw Score）
(1) 频次强度（课型加权 + 近因加权）
[ F=\sum_{i\in S}\tau_i\cdot \mathrm{decay}(\Delta d_i;h_{\text{sess}}) ]
(2) 最近一次温度（单独建模，直觉更强）
[ R=\mathrm{decay}(d_{\text{last}};h_{\text{last}}) ]
(3) 归因充值强度（金额压缩 + 时间衰减）
金额压缩采用对数（抑制长尾）： [ m(\mathrm{amt})=\ln\left(1+\frac{\mathrm{amt}}{A_0}\right) ] [ M=\sum_{j\in R} m(\mathrm{amt}_j)\cdot \mathrm{decay}(\Delta d^{(r)}j;h{\text{pay}}) ]
(4) 时长贡献（次要：温和加分，避免一局超长碾压）
[ D=\sum_{i\in S}\sqrt{\frac{\mathrm{dur}i}{60}}\cdot \tau_i\cdot \mathrm{decay}(\Delta d_i;h{\text{sess}}) ]
(5) 频率激增（Burst）放大
分别计算短期/长期的“近因频次”： [ F_{\text{short}}=\sum_{i\in S}\tau_i\cdot \mathrm{decay}(\Delta d_i;h_{\text{short}}) ] [ F_{\text{long}}=\sum_{i\in S}\tau_i\cdot \mathrm{decay}(\Delta d_i;h_{\text{long}}) ] 激增幅度（仅放大“高于常态”的部分，并做对数压缩）： [ \mathrm{burst}=\max\left(0,\ln\left(1+\left(\frac{F_{\text{short}}}{F_{\text{long}}+\epsilon}-1\right)\right)\right) ] 放大因子： [ \mathrm{mult}=1+\gamma\cdot \mathrm{burst} ]
(6) 汇总 Raw Score（无上限）
[ II_{\text{raw}}=\left(w_F F + w_R R + w_M M + w_D D\right)\cdot \mathrm{mult} ]
3) 统一的 0.0–10.0 映射方案（稳健替代“按最大值等比”）
目标：既能保持“高分更紧急/更亲密”的排序，又不被极端离群点劫持整体区间。
该映射对 RI 与 II 通用，分别在各自对象集合上计算分位点。
3.1 每轮映射步骤（推荐：分位截断 + 可选压缩 + MinMax）
对某个指数的全体 Raw 分数集合 ({x_k})（如所有客户的 (RI_{\text{raw}}) 或所有 pair 的 (II_{\text{raw}})）：
- 计算稳健锚点分位数（建议）：
- 下锚：(Q_L = P05(x))（5 分位）
- 上锚：(Q_U = P95(x))（95 分位）
-
分位截断（Winsorize）： [ x'_k = \min\left(\max(x_k,Q_L),Q_U\right) ]
-
（可选）非线性压缩（当仍呈长尾时启用；II 通常更适合启用）
两种任选一种即可：
- [ g(x)=\ln(1+x) ]
- [ g(x)=\mathrm{asinh}(x) ] 得到： [ y_k=g(x'_k) ]
- 映射到 ([0,10])： [ \text{score}_k= 10\cdot \frac{y_k-\min(y)}{\max(y)-\min(y)+\epsilon} ]
3.2 防抖（可选但强烈建议）：分位点做 EWMA 平滑
若你发现“每轮分布轻微变化导致全员分数跳动”，可对 (Q_L, Q_U) 做平滑：
[ Q_{U,t}=(1-\alpha)Q_{U,t-1}+\alpha Q_{U,t}^{\text{now}},\quad \alpha=0.2 ] [ Q_{L,t}=(1-\alpha)Q_{L,t-1}+\alpha Q_{L,t}^{\text{now}} ]
更新频率越高（例如每小时），越推荐启用平滑；每日更新可不启用或取更大 (\alpha)。
3.3 极端边界处理
- 若出现 (\max(y)-\min(y)) 很小（几乎全员相同），则直接置：
- (\text{score}=5.0) 或按业务设定为 0.0/固定值
- 对 (t>60) 的客户，可视业务做“召回上限策略”：例如将其召回分数封顶在 8~9（避免永远占据第一优先级），但 Raw 分数仍可保留用于分析。
4) 实施建议（不涉及字段级）
- 先按默认参数跑 1~2 周，观察：
- 召回指数 TopN 是否符合店内直觉（人工抽检）
- 亲密指数在助教维度是否形成合理“主客群”
- 分数跨轮次是否抖动（决定是否启用分位点 EWMA）
- 权重调整优先级：
- 映射稳定性（分位截断阈值、是否启用压缩/平滑）
- RI：(w_{\text{over}}) 与 (h_{\text{new}},h_{\text{re}})
- II：(w_F,w_M) 与 (h_{\text{sess}},h_{\text{pay}})，以及 (\gamma)