10 KiB
10 KiB
DWS 汇总层口径定义
本文档定义 dws 模式下各汇总指标的业务口径、计算规则和数据来源。
所有指标均基于 DWD 明细层数据聚合生成。
各任务的详细实现（数据来源表、输出字段、核心逻辑）请参阅 dws_tasks.md。
1. 助教业绩
1.1 助教日报（dws_assistant_daily_detail）
- 目标表：
dws.dws_assistant_daily_detail - 数据来源：
dwd_assistant_service_log、dwd_assistant_service_log_ex（提供is_trash标记）、dim_assistant（SCD2） - 粒度：门店 × 助教 × 日期
- 核心指标：服务次数（总/基础课/附加课/包厢课）、计费秒数与小时数、台账金额、去重客户数与台桌数、废除统计
- 课程类型分类：通过
cfg_skill_type映射skill_id→BASE/BONUS/ROOM - 助教等级：SCD2 as-of 取值，取统计日当日生效的等级版本
1.2 助教月报（dws_assistant_monthly_summary）
- 目标表：
dws.dws_assistant_monthly_summary - 数据来源：
dws_assistant_daily_detail聚合 +dwd_assistant_service_log（月度去重）+dim_assistant+cfg_performance_tier - 粒度：门店 × 助教 × 年月
- 核心指标：月度累计服务次数/时长/金额、有效业绩小时数（
total_hours - trashed_hours）、绩效档位匹配、排名（考虑并列） - 新入职判断：入职日期在当月 1 日后即视为新入职，档位匹配按日均折算 30 天
- 月度去重客户/台桌：从 DWD 直接去重，避免日度求和失真
1.3 助教客户统计（dws_assistant_customer_stats）
- 目标表：
dws.dws_assistant_customer_stats - 数据来源：
dwd_assistant_service_log、dim_member、dim_assistant - 粒度：门店 × 助教 × 会员
- 核心指标：全量累计（首次/最近服务日期、累计次数/时长/金额）、6 个滚动窗口（7/10/15/30/60/90 天）、活跃度判定
- 散客排除：
member_id为 0 或 None 不进入统计 - HAVING 过滤：仅保留最近 90 天内有服务记录的助教-客户对
1.4 助教财务分析（dws_assistant_finance_analysis）
- 目标表：
dws.dws_assistant_finance_analysis - 数据来源：
dwd_assistant_service_log、cfg_skill_type、dws_assistant_salary_calc、dws_assistant_daily_detail - 粒度：门店 × 助教 × 日期
- 核心指标：日度收入（总/基础课/附加课/包厢课）、日均成本（
gross_salary / work_days）、毛利润与毛利率 - 依赖：
DWS_ASSISTANT_SALARY和DWS_ASSISTANT_DAILY的输出
1.5 助教订单流水贡献（dws_assistant_order_contribution）
- 目标表：
dws.dws_assistant_order_contribution - 数据来源：
dwd_settlement_head、dwd_table_fee_log、dwd_assistant_service_log - 粒度：门店 × 助教 × 日期
- 核心指标（四项统计）：
order_gross_revenue：订单总流水（台费 + 酒水食品 + 所有助教服务费）order_net_revenue：订单净流水（总流水 - 所有助教服务分成）time_weighted_revenue：时效贡献流水（按服务时长占比分摊）time_weighted_net_revenue：时效净贡献（时效贡献 - 个人服务分成）
- 超休/打赏课特殊处理：
course_type = BONUS的助教不参与订单级分摊 - 依赖：
DWD_LOAD_FROM_ODS
2. 薪酬计算
2.1 助教薪酬（dws_assistant_salary_calc）
- 目标表：
dws.dws_assistant_salary_calc - 数据来源：
dws_assistant_monthly_summary、dws_assistant_recharge_commission、cfg_performance_tier、cfg_assistant_level_price、cfg_bonus_rules - 粒度：门店 × 助教 × 结算月份
- 核心公式：
- 应发工资 = 课时收入 + 奖金合计
- 基础课收入 =
base_hours × (base_course_price - base_deduction) - 附加课收入 =
bonus_hours × bonus_course_price × (1 - bonus_deduction_ratio) - 包厢课收入 =
room_hours × (room_course_price - base_deduction) - 奖金合计 = 冲刺奖金 + Top3 排名奖金 + 充值提成 + 其他奖金
- 等级定价：SCD2 按月份取历史生效值
- 运行调度：默认仅月初前 5 天运行
3. 财务日报
3.1 财务日报汇总（dws_finance_daily_summary）
- 目标表：
dws.dws_finance_daily_summary - 数据来源：
dwd_settlement_head、dwd_groupbuy_redemption、dwd_recharge_order、dwd_member_balance_change - 粒度：门店 × 日期
- 核心指标：发生额（正价）、优惠合计（团购/会员/赠送卡/手动/抹零）、确认收入、现金流入/流出/净变动、卡消费、充值统计（首充/续充）、订单统计
- 确认收入 = 发生额 - 优惠合计
- 金额字段统一
NUMERIC(12,2)，货币单位人民币（CNY）
3.2 收入结构（dws_finance_income_structure）
- 目标表：
dws.dws_finance_income_structure - 数据来源：
dwd_settlement_head、dwd_table_fee_log、dwd_assistant_service_log、dim_table、cfg_area_category - 粒度：门店 × 日期 × 结构类型 × 分类代码
- 两种分析维度：按收入类型（
INCOME_TYPE：台费/商品/助教基础课/助教附加课）、按区域（AREA：通过cfg_area_category映射）
3.3 折扣明细（dws_finance_discount_detail）
- 目标表：
dws.dws_finance_discount_detail - 数据来源：
dwd_settlement_head、dwd_groupbuy_redemption、dwd_member_balance_change - 粒度：门店 × 日期 × 折扣类型
- 折扣类型：
GROUPBUY/VIP/ROUNDING/GIFT_CARD_TABLE/GIFT_CARD_DRINK/GIFT_CARD_COUPON/BIG_CUSTOMER/OTHER
3.4 充值汇总（dws_finance_recharge_summary）
- 目标表：
dws.dws_finance_recharge_summary - 数据来源：
dwd_recharge_order、dim_member_card_account - 粒度：门店 × 日期
- 核心指标：充值笔数/总额（现金+赠送）、首充/续充拆分、去重会员数、全店卡余额快照
4. 会员分析
4.1 会员消费汇总（dws_member_consumption_summary）
- 目标表：
dws.dws_member_consumption_summary - 数据来源：
dwd_settlement_head、dim_member（SCD2）、dim_member_card_account（SCD2） - 粒度：门店 × 会员
- 核心指标：全量累计消费、6 个滚动窗口（7/10/15/30/60/90 天）的到店次数与消费金额、卡余额（现金卡/赠送卡）、活跃度、客户分层
- 客户分层规则：高价值（90 天 ≥ 3 次且 ≥ 1000 元）→ 中等（30 天内有消费）→ 低活跃（90 天内有但 30 天内无）→ 流失
- 散客排除：
member_id为 0 或 None 不进入统计
4.2 会员到访明细（dws_member_visit_detail）
- 目标表：
dws.dws_member_visit_detail - 数据来源：
dwd_settlement_head、dwd_assistant_service_log、dwd_table_fee_log、dim_member、dim_table、cfg_area_category - 粒度：门店 × 会员 × 结账单
- 核心指标：消费金额拆分（台费/商品/助教）、支付方式拆分（现金/储值卡/赠送卡/团购券）、台桌使用时长、助教服务明细（JSON）
5. 订单汇总
5.1 订单汇总宽表（dws_order_summary）
- 目标表：
dws.dws_order_summary - 数据来源：
dwd_settlement_head、dwd_table_fee_log、dwd_assistant_service_log、dwd_store_goods_sale、dwd_groupbuy_redemption、dwd_refund/dwd_refund_ex - 粒度：门店 × 结账单
- 核心指标：费用明细（台费/助教/商品/团购）、优惠、金额汇总、支付方式、台账流水、有效消费、退款与净收入
- 通过 6 个 CTE 多表合并，金额优先取明细表聚合值，回退到结账单头表汇总字段
6. 库存汇总
6.1 库存日度汇总（dws_goods_stock_daily_summary）
- 目标表：
dws.dws_goods_stock_daily_summary - 数据来源：
dwd_goods_stock_summary - 粒度：门店 × 日期 × 商品
- 更新策略：upsert（ON CONFLICT DO UPDATE）
- 核心逻辑：按
fetched_at日期分组，数值指标取 SUM，期初/期末取当日首/末条记录
6.2 库存周度汇总（dws_goods_stock_weekly_summary）
- 目标表：
dws.dws_goods_stock_weekly_summary - 数据来源：
dwd_goods_stock_summary - 粒度：门店 × ISO 周 × 商品
- 更新策略：upsert（ON CONFLICT DO UPDATE）
- 核心逻辑：按 ISO 周分组，
stat_date= 该周周一
6.3 库存月度汇总（dws_goods_stock_monthly_summary）
- 目标表：
dws.dws_goods_stock_monthly_summary - 数据来源：
dwd_goods_stock_summary - 粒度：门店 × 自然月 × 商品
- 更新策略：upsert（ON CONFLICT DO UPDATE）
- 核心逻辑：按自然月分组，
stat_date= 该月第一天
7. 自定义指数算法
指数算法的详细计算流程、参数与归一化方法请参阅 index_tasks.md。
以下为各指数对应的汇总表概览：
7.1 回流指数 — WBI（dws_member_winback_index）
- 目标表：
dws.dws_member_winback_index - 粒度：门店 × 会员
- 任务代码：
DWS_WINBACK_INDEX - 依赖：
DWS_MEMBER_VISIT、DWS_MEMBER_CONSUMPTION
7.2 新客转化指数 — NCI（dws_member_newconv_index）
- 目标表：
dws.dws_member_newconv_index - 粒度：门店 × 会员
- 任务代码：
DWS_NEWCONV_INDEX - 依赖：
DWS_MEMBER_VISIT、DWS_MEMBER_CONSUMPTION
7.3 关系指数 — RS（dws_member_assistant_relation_index）
- 目标表：
dws.dws_relation_index - 粒度：门店 × 会员 × 助教
- 任务代码：
DWS_RELATION_INDEX - 依赖：
DWS_ASSISTANT_DAILY
7.4 消费力指数 — SPI（dws_member_spending_power_index）
- 目标表：
dws.dws_member_spending_power_index - 粒度：门店 × 会员
- 任务代码：
DWS_SPENDING_POWER_INDEX - 依赖：
DWS_MEMBER_CONSUMPTION
7.5 人工台账 — ML（dws_ml_manual_order_source / dws_ml_manual_order_alloc）
- 宽表：
dws.dws_ml_manual_order_source - 窄表：
dws.dws_ml_manual_order_alloc - 粒度：门店 × 订单 × 助教
- 任务代码：
DWS_ML_MANUAL_IMPORT
7.6 指数百分位历史（dws_index_percentile_history）
- 目标表：
dws.dws_index_percentile_history - 粒度：门店 × 指数类型 × 日期
维护约定
- 新增或修改 DWS 指标时，须同步更新本文档对应章节
- 计算公式应包含：输入字段、聚合方式、过滤条件、精度/舍入规则
- 金额字段统一
NUMERIC(12,2)，货币单位人民币（CNY）