Lead Tracker Specification¶
定义 Lead Tracker 页面（Pipeline tab + Leads tab）展示的所有数据的来源和计算方式。
Lead Tracker 有两个 tab：
- Pipeline — 按 Lead Status 分组的卡片视图，展示漏斗各阶段数量
- Leads — 从 contacts 表筛选 lifecycleStage = 'lead' 的列表视图
一、数据来源¶
| 数据 | 来源表 | 说明 |
|---|---|---|
| 9 个 Lead Status 当前值（Active + Drop-off） | contacts | WHERE lifecycleStage='lead' GROUP BY leadStatus |
| Leads 列表数据 | contacts | WHERE lifecycleStage='lead' — 所有字段直读 |
| Incoming Leads 总数 | leads | COUNT(*) |
| Excluded（Malformed / Duplicated） | leads | phone 格式无效或重复，未进入 contacts 表 |
| Valid Leads | leads - Excluded | Incoming - Malformed - Duplicated |
二、Pipeline tab¶
2.1 顶栏汇总¶
| 前端展示名 | 计算方式 | 来源表 |
|---|---|---|
| INCOMING LEADS | COUNT(*) FROM leads |
leads |
| VALID LEADS | Incoming - Excluded | leads |
| INCOMING → BOOKED | COUNT(leadStatus='booked') ÷ COUNT(*) FROM leads × 100% |
contacts + leads |
2.2 Active 行¶
直接读 contacts.leadStatus 当前值。
| 前端展示名 | 计算方式 | 来源表 |
|---|---|---|
| Active（汇总） | New + Attempted + Connected + Booked | contacts |
| New | COUNT(leadStatus='new') |
contacts |
| Attempted | COUNT(leadStatus='attempted') |
contacts |
| Connected | COUNT(leadStatus='connected') |
contacts |
| Booked | COUNT(leadStatus='booked') |
contacts |
2.3 Drop-off 行¶
直接读 contacts.leadStatus 当前值。
| 前端展示名 | 计算方式 | 来源表 |
|---|---|---|
| Drop-off（汇总） | Unreachable + Neglected + Lost Contact + Bad Timing + Not Interested | contacts |
| Unreachable | COUNT(leadStatus='unreachable') |
contacts |
| Neglected | COUNT(leadStatus='neglected') |
contacts |
| Lost Contact | COUNT(leadStatus='lost_contact') |
contacts |
| Bad Timing | COUNT(leadStatus='bad_timing') |
contacts |
| Not Interested | COUNT(leadStatus='not_interested') |
contacts |
2.4 Excluded 行¶
这些 Lead 未进入 contacts 表。
| 前端展示名 | 计算方式 | 来源表 |
|---|---|---|
| Excluded（汇总） | Malformed + Duplicated | leads |
| Malformed Number | leads 中 phone 格式无效，未创建 contact | leads |
| Duplicated Number | leads 中 phone 重复 | leads |
2.5 占比计算¶
| 占比 | 分母 | 说明 |
|---|---|---|
| Active/Drop-off 各项 "% of valid" | Valid Leads | 进入 contacts 表的有效 Lead 总数 |
| Excluded 各项 "% of incoming" | Incoming Leads | leads 表总数 |
2.6 卡片点击交互¶
Pipeline 中的每个卡片可点击，点击后跳转到 Leads tab 并自动筛选对应的 Lead Status：
| 点击卡片 | 跳转 Leads tab 筛选条件 |
|---|---|
| Active（汇总） | leadStatus IN ('new','attempted','connected','booked') |
| New | leadStatus = 'new' |
| Attempted | leadStatus = 'attempted' |
| Connected | leadStatus = 'connected' |
| Booked | leadStatus = 'booked' |
| Drop-off（汇总） | leadStatus IN ('unreachable','neglected','lost_contact','bad_timing','not_interested') |
| Unreachable | leadStatus = 'unreachable' |
| Neglected | leadStatus = 'neglected' |
| Lost Contact | leadStatus = 'lost_contact' |
| Bad Timing | leadStatus = 'bad_timing' |
| Not Interested | leadStatus = 'not_interested' |
三、Leads tab¶
Leads tab 显示从 contacts 表筛选 lifecycleStage = 'lead' 的所有客户列表。
3.1 数据来源¶
所有字段来源和展示方式与 Contacts 页面一致，详见 Contacts 数据来源。
3.2 与 Contacts 页面的区别¶
| 区别 | Leads tab | Contacts 页面 |
|---|---|---|
| 数据范围 | 仅 lifecycleStage = 'lead' |
所有 contacts（Lead + Member + Churned） |
| Stage 筛选 | 无（固定为 Lead） | 有（可选 Lead / Member / Churned） |
| Lead Status 筛选 | 有（9 个，排除 Showed/Trialed/Converted） | 有（同上） |
| 从 Pipeline 跳转 | 支持（自动带 leadStatus 筛选） | 不支持 |