freedakgmail
153 lines
6.8 KiB
Markdown
153 lines
6.8 KiB
Markdown
# 需要补充的数据清单
|
||
|
||
> 日期：2026-08-02
|
||
> 基于数据库实际表结构和行数验证
|
||
|
||
---
|
||
|
||
## 一、可从现有数据自动ETL生成（无需人工录入）
|
||
|
||
### 1. `dim_member` — 会员主数据（空表 → 可从bill_fact聚合）
|
||
|
||
| 字段 | 来源 | 生成方式 |
|
||
|------|------|----------|
|
||
| `member_id` | `bill_fact.member_id` | DISTINCT 提取8.3万个会员ID |
|
||
| `register_store` | `bill_fact.store_code` | 该会员首笔消费的门店 |
|
||
| `register_date` | `bill_fact.opened_at` | 该会员首笔消费日期 |
|
||
| `register_channel` | `bill_fact` 各平台收入字段 | 首笔消费的支付渠道判断 |
|
||
| `member_level` | `bill_fact.member_level` | 取最新等级（需标准化：1-7 → 普通/银卡/金卡/钻石） |
|
||
| `total_orders` | `bill_fact` | 按member_id COUNT(DISTINCT bill_no) |
|
||
| `total_revenue` | `bill_fact.received_total` | 按member_id SUM |
|
||
| `last_order_date` | `bill_fact.opened_at` | 按member_id MAX |
|
||
| `status` | 计算 | 活跃(30天内有消费)/沉睡(90天内)/流失(>90天) |
|
||
|
||
### 2. `dim_employee` — 员工主数据（空表 → 可从salary/attendance聚合）
|
||
|
||
| 字段 | 来源 | 生成方式 |
|
||
|------|------|----------|
|
||
| `employee_id` | `salary_detail_records.employee_code` | DISTINCT 提取3,326个员工编码 |
|
||
| `employee_name` | 需人工补录 | 薪资表无姓名字段 |
|
||
| `position` | `salary_detail_records.position` | 直接取（262种岗位需标准化） |
|
||
| `store_code` | `salary_detail_records.org_level3` | 需映射org_level3到门店编码 |
|
||
| `hire_date` | `salary_detail_records.hire_date` | 直接取（text格式需转date） |
|
||
| `leave_date` | `salary_detail_records.leave_date` | 直接取（非空则为离职） |
|
||
| `status` | 计算 | 在职(hire_date有值且leave_date为空)/离职 |
|
||
|
||
### 3. `dim_sku` — SKU主数据（空表 → 可从dish_sales_details聚合）
|
||
|
||
| 字段 | 来源 | 生成方式 |
|
||
|------|------|----------|
|
||
| `sku_code` | 需人工生成 | 当前无SKU编码体系，需建立编码规则 |
|
||
| `standard_name` | `dish_sales_details.dish_name` | DISTINCT 提取1,745个菜品名 |
|
||
| `category_l1` | `dish_sales_details.category_level1` | 直接取（53个一级分类） |
|
||
| `category_l2` | `dish_sales_details.category_level2` | 直接取（59个二级分类） |
|
||
| `abc_class` | `mv_dish_sku_abc_monthly.abc_class` | 取最新月度分类 |
|
||
| `status` | 需人工判断 | 在售/停用/季节停 |
|
||
| `tags` | 需人工标记 | 新品/季节品/区域品/战略品 |
|
||
|
||
### 4. `dim_channel` — 渠道主数据（空表 → 可从bill_fact字段推断）
|
||
|
||
| 字段 | 来源 | 生成方式 |
|
||
|------|------|----------|
|
||
| `channel_code` | 人工定义 | 堂食/美团外卖/淘宝外卖/京东外卖/支付宝/微信/现金/银联/抖音 |
|
||
| `channel_name` | 人工定义 | 对应中文名 |
|
||
| `channel_group` | 人工定义 | 堂食/外卖/支付 |
|
||
| `commission_rate` | 需人工录入 | 各平台佣金率% |
|
||
|
||
---
|
||
|
||
## 二、需人工补录的数据
|
||
|
||
### 5. `dim_store` 补充字段（91行已有，但关键字段为空）
|
||
|
||
| 字段 | 现状 | 需补录内容 |
|
||
|------|------|------------|
|
||
| `open_date` | 大部分为空 | 91家店的开业日期（需查档案） |
|
||
| `area_sqm` | 大部分为空 | 91家店的营业面积（㎡） |
|
||
| `seat_count` | 大部分为空 | 91家店的座位数 |
|
||
| `business_area` | 大部分为空 | 所在商圈名称 |
|
||
| `region` | 87/91有值 | 4家缺失区域需补录 |
|
||
|
||
### 6. `dim_promotion` — 活动主数据（空表，需人工录入）
|
||
|
||
| 字段 | 需录入内容 |
|
||
|------|------------|
|
||
| `promotion_id` | 活动编码（如 P001-P031） |
|
||
| `promotion_name` | 31种营销方案名称（从bill_fact.marketing_plan提取） |
|
||
| `start_date` / `end_date` | 每个活动的起止日期 |
|
||
| `platform_bear` | 平台承担金额或比例 |
|
||
| `company_bear` | 公司承担金额或比例 |
|
||
| `store_bear` | 门店承担金额或比例 |
|
||
| `target_audience` | 新客/老客/全客 |
|
||
| `budget` | 活动预算 |
|
||
|
||
### 7. `member_level` 标准化映射
|
||
|
||
当前 `bill_fact.member_level` 值为：`1, 2, 3, 4, 5, 6, 7, LV6, LV7, 空`，需统一映射：
|
||
|
||
| 当前值 | 标准化 |
|
||
|--------|--------|
|
||
| 1 | 普通 |
|
||
| 2 | 银卡 |
|
||
| 3 | 银卡 |
|
||
| 4 | 金卡 |
|
||
| 5 | 金卡 |
|
||
| 6 | 钻石 |
|
||
| 7 | 钻石 |
|
||
| LV6 | 钻石 |
|
||
| LV7 | 钻石 |
|
||
|
||
### 8. `position` 岗位标准化映射
|
||
|
||
当前 `salary_detail_records.position` 有262种不同值，需映射到标准岗位：
|
||
|
||
| 标准岗位 | 可能的原始值 |
|
||
|----------|-------------|
|
||
| 店长 | 店长、门店经理、店长助理 |
|
||
| 厨师长 | 厨师长、后厨主管 |
|
||
| 厨师 | 厨师、拉面师、炒菜师、配菜 |
|
||
| 服务员 | 服务员、前厅、迎宾 |
|
||
| 收银员 | 收银员、出纳 |
|
||
| 配送员 | 配送、骑手 |
|
||
| 其他 | 其他所有岗位 |
|
||
|
||
---
|
||
|
||
## 三、需新建数据源（无法从现有系统获取）
|
||
|
||
| 数据项 | 需要内容 | 获取方式 | 优先级 |
|
||
|--------|----------|----------|--------|
|
||
| **现金流数据** | 应收账款、应付账款、租金支付周期、供应商账期 | 接入财务系统（金蝶/用友等） | 高 |
|
||
| **评价/口碑数据** | 美团/大众点评评分、差评内容、NPS | 美团商家API或爬虫 | 高 |
|
||
| **投诉记录** | 投诉时间、类型、处理人、处理时效、满意度 | 接入客服系统或新建录入表 | 中 |
|
||
| **SOP检查记录** | 出餐时间、卫生检查、服务标准达标率 | 新建检查录入表（可做小程序） | 中 |
|
||
| **食品安全检查** | 检查项、结果、问题、整改跟踪 | 新建录入表或接入监管系统 | 中 |
|
||
| **培训记录** | 培训课程、参训人、时间、考核成绩 | 接入培训系统或新建录入表 | 低 |
|
||
| **竞品数据** | 周边竞品价格、菜单、客流 | 爬虫或第三方数据服务 | 低 |
|
||
| **天气数据** | 每日天气、温度 | `dim_calendar`已有字段，接入天气API填充 | 低 |
|
||
|
||
---
|
||
|
||
## 四、优先级排序：先补什么
|
||
|
||
### 立即可做（ETL脚本，1-3天）
|
||
|
||
1. **填充 `dim_member`** — 从 `bill_fact` 聚合，解锁会员LTV/分层/活跃度分析
|
||
2. **填充 `dim_employee`** — 从 `salary_detail_records` 聚合，解锁人才流动/绩效分析
|
||
3. **填充 `dim_channel`** — 人工定义9个渠道，完善渠道分析
|
||
4. **标准化 `member_level`** — 编写映射SQL，统一会员等级
|
||
5. **标准化 `position`** — 编写映射SQL，统一岗位分类
|
||
|
||
### 需人工录入（1-2周）
|
||
|
||
6. **补录 `dim_store.open_date`** — 91家店开业日期，查档案补录
|
||
7. **补录 `dim_store.area_sqm` / `seat_count`** — 91家店面积和座位数
|
||
8. **录入 `dim_promotion`** — 31种营销方案的元数据
|
||
9. **建立 `dim_sku` 编码体系** — 1,745个菜品的SKU编码和状态标记
|
||
|
||
### 需外部接入（长期）
|
||
|
||
10. **财务系统数据** — 现金流分析
|
||
11. **评价平台API** — 口碑监控
|
||
12. **客服/检查系统** — 投诉/SOP/食安分析
|