157 lines
12 KiB
Markdown
157 lines
12 KiB
Markdown
# 需求文档：SPI 消费力指数（Spending Power Index）
|
||
|
||
## 简介
|
||
|
||
SPI（Spending Power Index，消费力指数）是 NeoZQYY 指数体系的新增客户级指数，用于评估会员在门店场景内的综合消费力层级。SPI 基于消费水平（Level）、消费速度（Speed）、消费稳定性（Stability）三个子分加权合成，与现有 NCI/WBI/RS/OS/MS/ML 指数协同使用，为运营人员提供客户分层和资源分配依据。
|
||
|
||
## 术语表
|
||
|
||
- **SPI_Task**：`SpendingPowerIndexTask`，负责计算 SPI 指数的 ETL 任务
|
||
- **BaseIndexTask**：指数算法任务基类，提供展示分映射（Winsorize → 压缩 → MinMax 0-10 → EWMA 平滑）
|
||
- **cfg_index_parameters**：`dws.cfg_index_parameters` 表，按 `index_type` + `param_name` 存储指数算法参数
|
||
- **dws_member_spending_power_index**：SPI 指数结果表，存储会员级消费力评分
|
||
- **Raw_Score**：原始评分，由算法直接计算得出的未归一化分数
|
||
- **Display_Score**：展示分，Raw_Score 经 P5/P95 Winsorize → 可选压缩 → MinMax 映射到 [0, 10] 的归一化分数
|
||
- **Level_Sub_Score**：消费水平子分，衡量客户消费金额层级与客单水平
|
||
- **Speed_Sub_Score**：消费速度子分，衡量近期消费推进速度与节奏变化
|
||
- **Stability_Sub_Score**：消费稳定性子分，衡量消费行为的时间覆盖稳定性
|
||
- **Winsorize**：分位截断，将值限制在 [P5, P95] 范围内以消除极端值影响
|
||
- **EWMA**：指数加权移动平均（Exponential Weighted Moving Average），用于平滑分位点避免批次间波动
|
||
- **log1p**：`ln(1 + x)` 压缩变换，用于处理长尾分布
|
||
- **settle_type**：结算类型，1=台桌结账，3=商城订单，5=充值订单
|
||
- **task_registry**：`orchestration/task_registry.py`，ETL 任务注册表
|
||
- **delete-before-insert**：按门店全量刷新策略，先删除该门店所有旧记录再插入新记录
|
||
|
||
## 需求
|
||
|
||
### 需求 1：SPI 结果表创建
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要创建 SPI 指数结果表，以便存储会员级消费力评分及中间特征。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE SPI_Task SHALL 将结果写入 `dws.dws_member_spending_power_index` 表，主键为 `(site_id, member_id)`
|
||
2. THE dws_member_spending_power_index 表 SHALL 包含基础特征字段：`spend_30`、`spend_90`、`recharge_90`、`orders_30`、`orders_90`、`visit_days_30`、`visit_days_90`、`avg_ticket_90`、`active_weeks_90`、`daily_spend_ewma_90`
|
||
3. THE dws_member_spending_power_index 表 SHALL 包含子分字段：`score_level_raw`、`score_level_display`、`score_speed_raw`、`score_speed_display`、`score_stability_raw`、`score_stability_display`
|
||
4. THE dws_member_spending_power_index 表 SHALL 包含总分字段：`raw_score`（SPI 原始分）和 `display_score`（SPI 展示分，numeric(5,2)）
|
||
5. THE dws_member_spending_power_index 表 SHALL 包含元数据字段：`calc_time`、`created_at`、`updated_at`
|
||
6. THE 开发者 SHALL 编写迁移脚本 `db/etl_feiqiu/migrations/<日期>_create_dws_member_spending_power_index.sql`，在测试库 test_etl_feiqiu 中执行建表
|
||
7. WHEN DDL 在测试库执行成功后，THE 开发者 SHALL 运行 `python scripts/ops/gen_consolidated_ddl.py` 从测试库导出最新 DDL，自动合并到 `docs/database/ddl/etl_feiqiu__dws.sql`
|
||
|
||
### 需求 2：SPI 基础特征提取
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要从 DWD 层提取会员消费和充值数据，以便计算 SPI 所需的基础特征。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE SPI_Task SHALL 从 `dwd.dwd_settlement_head` 提取近 90 天消费订单（settle_type IN (1, 3)），聚合为客户级特征
|
||
2. THE SPI_Task SHALL 从 `dwd.dwd_recharge_order` 提取近 90 天充值订单（settle_type = 5），聚合为客户级充值特征
|
||
3. THE SPI_Task SHALL 计算以下基础特征：`spend_30`（近30天消费总额）、`spend_90`（近90天消费总额）、`recharge_90`（近90天充值总额）、`orders_30`（近30天消费笔数）、`orders_90`（近90天消费笔数）、`visit_days_30`（近30天消费日数，按天去重）、`visit_days_90`（近90天消费日数，按天去重）
|
||
4. THE SPI_Task SHALL 计算 `avg_ticket_90 = spend_90 / max(orders_90, 1)`
|
||
5. THE SPI_Task SHALL 计算 `active_weeks_90`（近90天有消费的自然周数，最多13周）
|
||
6. THE SPI_Task SHALL 对近90天日消费序列计算 EWMA 得到 `daily_spend_ewma_90`，平滑系数从 cfg_index_parameters 读取
|
||
|
||
### 需求 3：Level 子分计算
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要实现消费水平（Level）子分算法，以便衡量客户的消费金额层级。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE SPI_Task SHALL 按以下公式计算 Level 子分：`L = w_s30 × ln(1 + spend_30/M30) + w_s90 × ln(1 + spend_90/M90) + w_ticket × ln(1 + avg_ticket_90/T0) + w_r90 × ln(1 + recharge_90/R90)`
|
||
2. THE SPI_Task SHALL 从 cfg_index_parameters 读取 Level 子分的权重参数（`w_level_spend_30`、`w_level_spend_90`、`w_level_ticket_90`、`w_level_recharge_90`）和金额压缩基数（`amount_base_spend_30`、`amount_base_spend_90`、`amount_base_ticket_90`、`amount_base_recharge_90`）
|
||
3. WHEN 所有消费和充值金额均为 0 时，THE SPI_Task SHALL 将 Level 子分 Raw 设为 0.0
|
||
|
||
### 需求 4：Speed 子分计算
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要实现消费速度（Speed）子分算法，以便衡量客户近期消费推进速度。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE SPI_Task SHALL 计算绝对速度：`V_abs = ln(1 + spend_30 / (max(visit_days_30, 1) × V0))`
|
||
2. THE SPI_Task SHALL 计算相对速度：`V_rel = ln((v_30 + ε) / (v_90 + ε))`，其中 `v_30 = spend_30 / 30`，`v_90 = spend_90 / 90`，`ε` 为防除零小量
|
||
3. THE SPI_Task SHALL 计算 EWMA 速度：`V_ewma = ln(1 + daily_spend_ewma_90 / E0)`
|
||
4. THE SPI_Task SHALL 按以下公式合成 Speed 子分：`S = w_abs × V_abs + w_rel × max(0, V_rel) + w_ewma × V_ewma`
|
||
5. THE SPI_Task SHALL 仅对加速（`V_rel > 0`）加分，不对减速直接扣分（通过 `max(0, V_rel)` 实现）
|
||
|
||
### 需求 5：Stability 子分计算
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要实现消费稳定性（Stability）子分算法，以便识别稳定高消费与偶发冲高。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE SPI_Task SHALL 使用近 90 天数据计算稳定性，窗口固定为 90 天
|
||
2. THE SPI_Task SHALL 按周覆盖率计算稳定性：`P = active_weeks_90 / 13`（近90天共约13个自然周）
|
||
3. WHEN cfg_index_parameters 中 `use_stability = 0` 时，THE SPI_Task SHALL 将 Stability 子分权重视为 0，跳过稳定性计算
|
||
4. THE Stability_Sub_Score SHALL 的取值范围为 [0, 1]
|
||
|
||
### 需求 6：SPI 总分合成与展示分映射
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要将三个子分加权合成 SPI 总分并映射为展示分，以便业务人员直观理解客户消费力层级。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE SPI_Task SHALL 按以下公式计算 SPI 总分：`SPI_raw = w_L × L + w_S × S + w_P × P`，默认权重 `w_L=0.60`、`w_S=0.30`、`w_P=0.10`
|
||
2. THE SPI_Task SHALL 复用 BaseIndexTask 的 `batch_normalize_to_display` 方法将 Raw_Score 映射为 Display_Score（0-10 分）
|
||
3. THE SPI_Task SHALL 对 Level、Speed、Stability 三个子分分别独立映射为展示分（0-10 分）
|
||
4. THE SPI_Task SHALL 支持通过 cfg_index_parameters 配置压缩模式（`compression_mode`：0=无压缩，1=log1p，2=asinh）
|
||
5. THE SPI_Task SHALL 支持通过 cfg_index_parameters 配置 EWMA 分位平滑（`use_smoothing`、`ewma_alpha`）
|
||
6. THE Display_Score SHALL 保留 2 位小数，取值范围为 [0.00, 10.00]
|
||
|
||
### 需求 7：SPI 配置参数管理
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要在 cfg_index_parameters 中注册 SPI 的全部默认参数，以便算法参数可配置、可追溯。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE 种子数据脚本 SHALL 在 cfg_index_parameters 中插入 `index_type='SPI'` 的全部参数，包括窗口参数、金额压缩基数、子分权重、总分权重、映射与平滑参数
|
||
2. THE SPI_Task SHALL 通过 BaseIndexTask 的 `load_index_parameters(index_type='SPI')` 加载参数
|
||
3. IF cfg_index_parameters 中缺少某个 SPI 参数，THEN THE SPI_Task SHALL 使用 DEFAULT_PARAMS 字典中定义的默认值
|
||
4. THE 种子数据脚本 SHALL 追加到 `db/etl_feiqiu/seeds/seed_index_parameters.sql`
|
||
|
||
### 需求 8：金额压缩基数校准
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要提供金额压缩基数的校准机制，以便各门店的 SPI 评分能适配不同的消费水平分布。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE SPI_Task SHALL 在首次执行或参数缺失时，支持从门店历史数据自动计算金额压缩基数的建议值
|
||
2. THE SPI_Task SHALL 以近 90 天消费数据的中位数作为各金额压缩基数的默认校准值：`amount_base_spend_30` 取近30天消费中位数、`amount_base_spend_90` 取近90天消费中位数、`amount_base_ticket_90` 取90天客单中位数、`amount_base_recharge_90` 取90天充值中位数、`amount_base_speed_abs` 取每消费日平均消费中位数、`amount_base_ewma_90` 取日消费 EWMA 中位数
|
||
3. IF cfg_index_parameters 中已存在对应的金额压缩基数参数，THEN THE SPI_Task SHALL 优先使用配置表中的值而非自动校准值
|
||
4. THE SPI_Task SHALL 在日志中输出实际使用的金额压缩基数值，便于运营人员审查和手动调优
|
||
5. THE 种子数据脚本 SHALL 为金额压缩基数提供合理的初始默认值（基于典型台球门店消费水平）
|
||
|
||
### 需求 9：SPI 任务注册与执行
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要将 SPI 任务注册到 task_registry 并实现完整的执行流程，以便通过调度器触发计算。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE SPI_Task SHALL 以任务代码 `DWS_SPENDING_POWER_INDEX` 注册到 task_registry，`layer="INDEX"`，`requires_db_config=False`
|
||
2. THE SPI_Task SHALL 声明依赖 `depends_on=["DWS_MEMBER_CONSUMPTION"]`
|
||
3. THE SPI_Task SHALL 采用 delete-before-insert 策略：先按 `site_id` 删除旧记录，再批量插入新记录
|
||
4. WHEN 门店无任何消费或充值数据时，THE SPI_Task SHALL 返回 `{'status': 'skipped', 'reason': 'no_data'}` 并跳过计算
|
||
5. THE SPI_Task SHALL 在执行完成后保存分位点历史到 `dws_index_percentile_history` 表（index_type='SPI'）
|
||
|
||
### 需求 10：SPI 算法正确性测试
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要通过属性测试（hypothesis）验证 SPI 算法的正确性，以便确保计算逻辑符合 PRD 定义。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE 属性测试 SHALL 验证：对于任意非负消费/充值金额，SPI_raw 为非负值
|
||
2. THE 属性测试 SHALL 验证：在其他条件不变时，增加 spend_30 或 spend_90 不会导致 Level 子分下降（单调性）
|
||
3. THE 属性测试 SHALL 验证：在其他条件不变时，增加 spend_30 不会导致 Speed 子分下降（单调性）
|
||
4. THE 属性测试 SHALL 验证：Stability 子分取值范围为 [0, 1]
|
||
5. THE 属性测试 SHALL 验证：Display_Score 取值范围为 [0.00, 10.00]
|
||
6. THE 属性测试 SHALL 验证：SPI 总分权重 `w_L + w_S + w_P` 之和为 1.0（权重归一化）
|
||
|
||
### 需求 11：文档更新
|
||
|
||
**用户故事：** 作为 ETL 开发者，我需要更新相关文档，以便团队成员了解 SPI 的表结构、算法逻辑和使用方式。
|
||
|
||
#### 验收标准
|
||
|
||
1. THE 开发者 SHALL 编写数据库手册文档 `docs/database/BD_Manual_dws_member_spending_power_index.md`，包含表结构、字段说明、索引、验证 SQL
|
||
2. THE 开发者 SHALL 更新 ETL 任务文档 `apps/etl/connectors/feiqiu/docs/etl_tasks/index_tasks.md`，新增 SPI 任务章节
|
||
3. THE 文档 SHALL 包含 SPI 算法公式、参数清单、数据来源、计算流程说明
|