# 拓客流失节点分析 - 计算逻辑设计

## 📊 数据概览

### 数据库统计（全量数据）
- **拓客记录总数**: 4,666 条
- **邀约记录总数**: 16,350 条（去重后 8,834 个唯一会员）
- **预约记录总数**: 12,545 条（去重后 4,811 个唯一会员）
- **到店会员数**: 10,105 个（去重）
- **开单会员数**: 20,347 个（去重）

### 预约状态分布
- **已确认**: 9,359 条（74.6%）
- **已预约**: 2,377 条（18.9%）
- **已取消**: 809 条（6.4%）

### 关联字段使用情况
- **预约记录中 F_InviteId**: 1,783 / 12,545 = **14.2%**（有邀约关联）
- **开单记录中 F_AppointmentId**: 762 / 91,532 = **0.8%**（有预约关联）
- **耗卡记录中 F_AppointmentId**: 6,732 / 38,504 = **17.5%**（有预约关联）

### 示例活动数据（活动ID: 742707446677505285）
- **拓客人数**: 1,330
- **邀约人数**: 771（57.97%）
- **预约人数**: 404（30.38%）
- **到店人数**: 689（51.80%）
- **开单人数**: 701（52.71%）

---

## 🔄 转化链路定义

### 完整转化链路
```
拓客 (Expansion)
  ↓
邀约 (Invite)
  ↓
预约 (Appointment)
  ↓
到店 (Visit) - 通过耗卡记录判断
  ↓
开单 (Billing)
```

### 各节点定义

#### 1. 拓客节点
- **数据来源**: `lq_tkjlb`
- **判断标准**: 存在拓客记录
- **统计维度**: 按 `F_MemberId` 去重
- **时间字段**: `F_ExpansionTime`

#### 2. 邀约节点
- **数据来源**: `lq_yaoyjl`
- **判断标准**: 存在邀约记录
- **统计维度**: 按 `yykh`（邀约客户ID）去重
- **时间字段**: `yysj`（邀约时间）
- **关联关系**: `lq_tkjlb.F_MemberId = lq_yaoyjl.yykh`

#### 3. 预约节点
- **数据来源**: `lq_yyjl`
- **判断标准**: 存在预约记录（不考虑状态）
- **统计维度**: 按 `gk`（顾客ID）去重
- **时间字段**: `yysj`（预约时间）
- **关联关系**: 
  - 方式1：`lq_yaoyjl.F_Id = lq_yyjl.F_InviteId`（仅14.2%有关联）
  - 方式2：`lq_yaoyjl.yykh = lq_yyjl.gk`（通过会员ID关联，推荐使用）

#### 4. 到店节点
- **数据来源**: `lq_xh_hyhk`（耗卡记录）
- **判断标准**: 存在有效耗卡记录（`F_IsEffective = 1`）
- **统计维度**: 按 `hyzh`（会员账号）去重
- **时间字段**: `hksj`（耗卡时间）
- **关联关系**: `lq_tkjlb.F_MemberId = lq_xh_hyhk.hyzh`
- **注意**: 到店判断基于耗卡记录，不是预约状态

#### 5. 开单节点
- **数据来源**: `lq_kd_kdjlb`
- **判断标准**: 存在有效开单记录（`F_IsEffective = 1` 且 `sfyj > 0`）
- **统计维度**: 按 `kdhy`（开单会员ID）去重
- **时间字段**: `kdrq`（开单日期）
- **关联关系**: `lq_tkjlb.F_MemberId = lq_kd_kdjlb.kdhy`

---

## 📉 流失节点计算逻辑

### 流失节点定义

流失节点是指客户在转化链路中，从某个节点开始没有进入下一个节点。

### 各流失节点计算

#### 1. 拓客未邀约（流失节点1）
**定义**: 拓客后没有邀约记录的客户

**计算公式**:
```sql
流失数量 = 拓客人数 - 邀约人数
流失率 = (拓客人数 - 邀约人数) / 拓客人数 × 100%
```

**SQL示例**:
```sql
SELECT 
    COUNT(DISTINCT tk.F_MemberId) as expansion_count,
    COUNT(DISTINCT yy.yykh) as invite_count,
    COUNT(DISTINCT tk.F_MemberId) - COUNT(DISTINCT yy.yykh) as loss_count_1,
    ROUND((COUNT(DISTINCT tk.F_MemberId) - COUNT(DISTINCT yy.yykh)) * 100.0 / COUNT(DISTINCT tk.F_MemberId), 2) as loss_rate_1
FROM lq_tkjlb tk
LEFT JOIN lq_yaoyjl yy ON yy.yykh = tk.F_MemberId
WHERE tk.F_EventId = @eventId
    AND tk.F_ExpansionTime >= @startTime
    AND tk.F_ExpansionTime <= @endTime
```

**示例数据**:
- 拓客人数: 1,330
- 邀约人数: 771
- 流失数量: 1,330 - 771 = **559**
- 流失率: 559 / 1,330 × 100% = **42.03%**

---

#### 2. 邀约未预约（流失节点2）
**定义**: 有邀约记录但没有预约记录的客户

**计算公式**:
```sql
流失数量 = 邀约人数 - 预约人数
流失率 = (邀约人数 - 预约人数) / 邀约人数 × 100%
```

**SQL示例**:
```sql
SELECT 
    COUNT(DISTINCT yy.yykh) as invite_count,
    COUNT(DISTINCT yyjl.gk) as appointment_count,
    COUNT(DISTINCT yy.yykh) - COUNT(DISTINCT yyjl.gk) as loss_count_2,
    ROUND((COUNT(DISTINCT yy.yykh) - COUNT(DISTINCT yyjl.gk)) * 100.0 / COUNT(DISTINCT yy.yykh), 2) as loss_rate_2
FROM lq_yaoyjl yy
INNER JOIN lq_tkjlb tk ON yy.yykh = tk.F_MemberId
LEFT JOIN lq_yyjl yyjl ON yyjl.gk = yy.yykh
WHERE tk.F_EventId = @eventId
    AND yy.yysj >= @startTime
    AND yy.yysj <= @endTime
```

**示例数据**:
- 邀约人数: 771
- 预约人数: 404
- 流失数量: 771 - 404 = **367**
- 流失率: 367 / 771 × 100% = **47.60%**

**注意**: 
- 预约状态不考虑（已确认、已预约、已取消都算预约）
- 如果使用 `F_InviteId` 关联，只有14.2%的数据能关联上，建议使用会员ID关联

---

#### 3. 预约未到店（流失节点3）
**定义**: 有预约记录但没有耗卡记录的客户

**计算公式**:
```sql
流失数量 = 预约人数 - 到店人数
流失率 = (预约人数 - 到店人数) / 预约人数 × 100%
```

**SQL示例**:
```sql
SELECT 
    COUNT(DISTINCT yyjl.gk) as appointment_count,
    COUNT(DISTINCT hk.hyzh) as visit_count,
    COUNT(DISTINCT yyjl.gk) - COUNT(DISTINCT hk.hyzh) as loss_count_3,
    ROUND((COUNT(DISTINCT yyjl.gk) - COUNT(DISTINCT hk.hyzh)) * 100.0 / COUNT(DISTINCT yyjl.gk), 2) as loss_rate_3
FROM lq_yyjl yyjl
INNER JOIN lq_tkjlb tk ON yyjl.gk = tk.F_MemberId
LEFT JOIN lq_xh_hyhk hk ON hk.hyzh = yyjl.gk AND hk.F_IsEffective = 1
WHERE tk.F_EventId = @eventId
    AND yyjl.yysj >= @startTime
    AND yyjl.yysj <= @endTime
```

**示例数据**:
- 预约人数: 404
- 到店人数: 689（注意：到店人数可能大于预约人数，因为有些客户可能直接到店没有预约）
- 流失数量: 需要重新计算（见下文说明）

**重要说明**:
- 到店判断基于耗卡记录，不是预约状态
- 可能存在"直接到店"的情况（没有预约但有耗卡）
- 流失计算应该是：预约了但没有到店的客户

---

#### 4. 到店未开单（流失节点4）
**定义**: 有耗卡记录但没有开单记录的客户

**计算公式**:
```sql
流失数量 = 到店人数 - 开单人数
流失率 = (到店人数 - 开单人数) / 到店人数 × 100%
```

**SQL示例**:
```sql
SELECT 
    COUNT(DISTINCT hk.hyzh) as visit_count,
    COUNT(DISTINCT kd.kdhy) as billing_count,
    COUNT(DISTINCT hk.hyzh) - COUNT(DISTINCT kd.kdhy) as loss_count_4,
    ROUND((COUNT(DISTINCT hk.hyzh) - COUNT(DISTINCT kd.kdhy)) * 100.0 / COUNT(DISTINCT hk.hyzh), 2) as loss_rate_4
FROM lq_xh_hyhk hk
INNER JOIN lq_tkjlb tk ON hk.hyzh = tk.F_MemberId
LEFT JOIN lq_kd_kdjlb kd ON kd.kdhy = hk.hyzh AND kd.F_IsEffective = 1
WHERE tk.F_EventId = @eventId
    AND hk.F_IsEffective = 1
    AND hk.hksj >= @startTime
    AND hk.hksj <= @endTime
```

**示例数据**:
- 到店人数: 689
- 开单人数: 701（注意：开单人数可能大于到店人数，因为有些客户可能直接开单没有耗卡）
- 流失数量: 需要重新计算（见下文说明）

---

## ⚠️ 数据异常情况分析

### 发现的问题

#### 1. 到店人数 > 预约人数
**现象**: 示例活动中，到店人数（689）大于预约人数（404）

**可能原因**:
- 客户直接到店，没有预约记录
- 预约记录不完整
- 时间范围不一致

**处理建议**:
- 流失节点3（预约未到店）应该计算：**预约了但没有到店的客户**
- 公式：`预约人数 - (预约人数 ∩ 到店人数)`
- 需要计算交集，而不是简单的减法

#### 2. 开单人数 > 到店人数
**现象**: 示例活动中，开单人数（701）大于到店人数（689）

**可能原因**:
- 客户直接开单，没有耗卡记录
- 耗卡记录不完整
- 时间范围不一致

**处理建议**:
- 流失节点4（到店未开单）应该计算：**到店了但没有开单的客户**
- 公式：`到店人数 - (到店人数 ∩ 开单人数)`
- 需要计算交集，而不是简单的减法

#### 3. 关联字段使用率低
**问题**:
- `F_InviteId` 使用率只有14.2%
- `F_AppointmentId` 在开单记录中使用率只有0.8%

**处理建议**:
- 优先使用会员ID关联（`F_MemberId`）
- 关联字段作为辅助判断
- 需要处理历史数据缺失的情况

---

## 🔧 修正后的计算逻辑

### 正确的流失节点计算

#### 流失节点1：拓客未邀约
```sql
流失数量 = COUNT(DISTINCT 拓客会员ID) - COUNT(DISTINCT 邀约会员ID)
流失率 = 流失数量 / 拓客人数 × 100%
```

#### 流失节点2：邀约未预约
```sql
流失数量 = COUNT(DISTINCT 邀约会员ID) - COUNT(DISTINCT 预约会员ID)
流失率 = 流失数量 / 邀约人数 × 100%
```

#### 流失节点3：预约未到店
```sql
-- 需要计算交集
预约且到店人数 = COUNT(DISTINCT CASE WHEN 有预约 AND 有耗卡 THEN 会员ID END)
流失数量 = COUNT(DISTINCT 预约会员ID) - 预约且到店人数
流失率 = 流失数量 / 预约人数 × 100%
```

#### 流失节点4：到店未开单
```sql
-- 需要计算交集
到店且开单人数 = COUNT(DISTINCT CASE WHEN 有耗卡 AND 有开单 THEN 会员ID END)
流失数量 = COUNT(DISTINCT 到店会员ID) - 到店且开单人数
流失率 = 流失数量 / 到店人数 × 100%
```

---

## 📊 完整SQL查询示例

### 流失节点分析完整查询

```sql
-- 流失节点分析（按活动和时间范围）
WITH expansion_data AS (
    -- 拓客数据
    SELECT DISTINCT tk.F_MemberId as member_id
    FROM lq_tkjlb tk
    WHERE tk.F_EventId = @eventId
        AND tk.F_ExpansionTime >= @startTime
        AND tk.F_ExpansionTime <= @endTime
),
invite_data AS (
    -- 邀约数据（关联拓客）
    SELECT DISTINCT yy.yykh as member_id
    FROM lq_yaoyjl yy
    INNER JOIN lq_tkjlb tk ON yy.yykh = tk.F_MemberId
    WHERE tk.F_EventId = @eventId
        AND yy.yysj >= @startTime
        AND yy.yysj <= @endTime
),
appointment_data AS (
    -- 预约数据（关联拓客）
    SELECT DISTINCT yyjl.gk as member_id
    FROM lq_yyjl yyjl
    INNER JOIN lq_tkjlb tk ON yyjl.gk = tk.F_MemberId
    WHERE tk.F_EventId = @eventId
        AND yyjl.yysj >= @startTime
        AND yyjl.yysj <= @endTime
),
visit_data AS (
    -- 到店数据（关联拓客，基于耗卡记录）
    SELECT DISTINCT hk.hyzh as member_id
    FROM lq_xh_hyhk hk
    INNER JOIN lq_tkjlb tk ON hk.hyzh = tk.F_MemberId
    WHERE tk.F_EventId = @eventId
        AND hk.F_IsEffective = 1
        AND hk.hksj >= @startTime
        AND hk.hksj <= @endTime
),
billing_data AS (
    -- 开单数据（关联拓客）
    SELECT DISTINCT kd.kdhy as member_id
    FROM lq_kd_kdjlb kd
    INNER JOIN lq_tkjlb tk ON kd.kdhy = tk.F_MemberId
    WHERE tk.F_EventId = @eventId
        AND kd.F_IsEffective = 1
        AND kd.kdrq >= @startTime
        AND kd.kdrq <= @endTime
)
SELECT 
    (SELECT COUNT(*) FROM expansion_data) as expansion_count,
    (SELECT COUNT(*) FROM invite_data) as invite_count,
    (SELECT COUNT(*) FROM appointment_data) as appointment_count,
    (SELECT COUNT(*) FROM visit_data) as visit_count,
    (SELECT COUNT(*) FROM billing_data) as billing_count,
    -- 流失节点1：拓客未邀约
    (SELECT COUNT(*) FROM expansion_data) - (SELECT COUNT(*) FROM invite_data) as loss_1_count,
    ROUND(((SELECT COUNT(*) FROM expansion_data) - (SELECT COUNT(*) FROM invite_data)) * 100.0 / 
        NULLIF((SELECT COUNT(*) FROM expansion_data), 0), 2) as loss_1_rate,
    -- 流失节点2：邀约未预约
    (SELECT COUNT(*) FROM invite_data) - (SELECT COUNT(*) FROM appointment_data) as loss_2_count,
    ROUND(((SELECT COUNT(*) FROM invite_data) - (SELECT COUNT(*) FROM appointment_data)) * 100.0 / 
        NULLIF((SELECT COUNT(*) FROM invite_data), 0), 2) as loss_2_rate,
    -- 流失节点3：预约未到店（需要计算交集）
    (SELECT COUNT(*) FROM appointment_data) - 
    (SELECT COUNT(*) FROM appointment_data a WHERE EXISTS (SELECT 1 FROM visit_data v WHERE v.member_id = a.member_id)) as loss_3_count,
    ROUND(((SELECT COUNT(*) FROM appointment_data) - 
        (SELECT COUNT(*) FROM appointment_data a WHERE EXISTS (SELECT 1 FROM visit_data v WHERE v.member_id = a.member_id))) * 100.0 / 
        NULLIF((SELECT COUNT(*) FROM appointment_data), 0), 2) as loss_3_rate,
    -- 流失节点4：到店未开单（需要计算交集）
    (SELECT COUNT(*) FROM visit_data) - 
    (SELECT COUNT(*) FROM visit_data v WHERE EXISTS (SELECT 1 FROM billing_data b WHERE b.member_id = v.member_id)) as loss_4_count,
    ROUND(((SELECT COUNT(*) FROM visit_data) - 
        (SELECT COUNT(*) FROM visit_data v WHERE EXISTS (SELECT 1 FROM billing_data b WHERE b.member_id = v.member_id))) * 100.0 / 
        NULLIF((SELECT COUNT(*) FROM visit_data), 0), 2) as loss_4_rate
```

---

## 🎯 优化后的计算逻辑（推荐）

### 使用LEFT JOIN方式（更高效）

```sql
SELECT 
    -- 各节点人数
    COUNT(DISTINCT tk.F_MemberId) as expansion_count,
    COUNT(DISTINCT CASE WHEN yy.F_Id IS NOT NULL THEN tk.F_MemberId END) as invite_count,
    COUNT(DISTINCT CASE WHEN yyjl.F_Id IS NOT NULL THEN tk.F_MemberId END) as appointment_count,
    COUNT(DISTINCT CASE WHEN hk.F_Id IS NOT NULL THEN tk.F_MemberId END) as visit_count,
    COUNT(DISTINCT CASE WHEN kd.F_Id IS NOT NULL THEN tk.F_MemberId END) as billing_count,
    
    -- 流失节点1：拓客未邀约
    COUNT(DISTINCT CASE WHEN yy.F_Id IS NULL THEN tk.F_MemberId END) as loss_1_count,
    ROUND(COUNT(DISTINCT CASE WHEN yy.F_Id IS NULL THEN tk.F_MemberId END) * 100.0 / 
        NULLIF(COUNT(DISTINCT tk.F_MemberId), 0), 2) as loss_1_rate,
    
    -- 流失节点2：邀约未预约
    COUNT(DISTINCT CASE WHEN yy.F_Id IS NOT NULL AND yyjl.F_Id IS NULL THEN tk.F_MemberId END) as loss_2_count,
    ROUND(COUNT(DISTINCT CASE WHEN yy.F_Id IS NOT NULL AND yyjl.F_Id IS NULL THEN tk.F_MemberId END) * 100.0 / 
        NULLIF(COUNT(DISTINCT CASE WHEN yy.F_Id IS NOT NULL THEN tk.F_MemberId END), 0), 2) as loss_2_rate,
    
    -- 流失节点3：预约未到店
    COUNT(DISTINCT CASE WHEN yyjl.F_Id IS NOT NULL AND hk.F_Id IS NULL THEN tk.F_MemberId END) as loss_3_count,
    ROUND(COUNT(DISTINCT CASE WHEN yyjl.F_Id IS NOT NULL AND hk.F_Id IS NULL THEN tk.F_MemberId END) * 100.0 / 
        NULLIF(COUNT(DISTINCT CASE WHEN yyjl.F_Id IS NOT NULL THEN tk.F_MemberId END), 0), 2) as loss_3_rate,
    
    -- 流失节点4：到店未开单
    COUNT(DISTINCT CASE WHEN hk.F_Id IS NOT NULL AND kd.F_Id IS NULL THEN tk.F_MemberId END) as loss_4_count,
    ROUND(COUNT(DISTINCT CASE WHEN hk.F_Id IS NOT NULL AND kd.F_Id IS NULL THEN tk.F_MemberId END) * 100.0 / 
        NULLIF(COUNT(DISTINCT CASE WHEN hk.F_Id IS NOT NULL THEN tk.F_MemberId END), 0), 2) as loss_4_rate

FROM lq_tkjlb tk
LEFT JOIN lq_yaoyjl yy ON yy.yykh = tk.F_MemberId
    AND yy.yysj >= @startTime AND yy.yysj <= @endTime
LEFT JOIN lq_yyjl yyjl ON yyjl.gk = tk.F_MemberId
    AND yyjl.yysj >= @startTime AND yyjl.yysj <= @endTime
LEFT JOIN lq_xh_hyhk hk ON hk.hyzh = tk.F_MemberId
    AND hk.F_IsEffective = 1
    AND hk.hksj >= @startTime AND hk.hksj <= @endTime
LEFT JOIN lq_kd_kdjlb kd ON kd.kdhy = tk.F_MemberId
    AND kd.F_IsEffective = 1
    AND kd.kdrq >= @startTime AND kd.kdrq <= @endTime
WHERE tk.F_EventId = @eventId
    AND tk.F_ExpansionTime >= @startTime
    AND tk.F_ExpansionTime <= @endTime
```

---

## 📈 可视化建议

### 1. 流失节点漏斗图
- 显示各节点人数和流失数量
- 用不同颜色标识流失节点
- 显示流失率和转化率

### 2. 流失节点统计卡片
- 4个卡片分别显示4个流失节点
- 每个卡片显示：流失数量、流失率、占比

### 3. 流失节点明细列表
- 显示各流失节点的客户明细
- 支持导出Excel
- 支持按门店、人员筛选

---

## ⚠️ 注意事项

### 1. 时间范围一致性
- 所有节点的时间范围应该一致
- 建议使用拓客时间作为基准时间范围
- 其他节点的时间应该在拓客时间之后

### 2. 数据去重
- 所有统计都按会员ID去重
- 避免重复计算

### 3. 关联关系
- 优先使用会员ID关联（`F_MemberId`）
- 关联字段（`F_InviteId`、`F_AppointmentId`）作为辅助
- 处理历史数据缺失的情况

### 4. 数据异常处理
- 处理到店人数 > 预约人数的情况
- 处理开单人数 > 到店人数的情况
- 使用交集计算，而不是简单减法

### 5. 性能优化
- 使用索引优化查询（`F_MemberId`、`F_EventId`、`F_IsEffective`）
- 使用LEFT JOIN避免子查询
- 大数据量时考虑分页或缓存

---

## 🔍 验证SQL（用于测试）

### 测试单个活动的流失节点

```sql
-- 测试活动ID: 742707446677505285
SET @eventId = '742707446677505285';
SET @startTime = '2025-10-01 00:00:00';
SET @endTime = '2025-10-31 23:59:59';

-- 执行上述优化后的SQL查询
```

### 实际验证结果（活动ID: 742707446677505285）

#### 各节点人数
- **拓客人数**: 1,330
- **邀约人数**: 771（57.97%）
- **预约人数**: 404（30.38%）
- **到店人数**: 689（51.80%）
- **开单人数**: 701（52.71%）

#### 流失节点统计
- **流失节点1（拓客未邀约）**: 559人（42.03%）
  - 计算：1,330 - 771 = 559
  - 流失率：559 / 1,330 × 100% = 42.03%

- **流失节点2（邀约未预约）**: 476人（61.74%）
  - 计算：771 - 404 = 367（理论值），但实际查询为476
  - 说明：存在邀约了但没有预约记录的客户
  - 流失率：476 / 771 × 100% = 61.74%

- **流失节点3（预约未到店）**: 59人（14.60%）
  - 计算：404 - (404 ∩ 689) = 59
  - 说明：预约了但没有到店（耗卡）的客户
  - 流失率：59 / 404 × 100% = 14.60%

- **流失节点4（到店未开单）**: 0人（0%）
  - 计算：689 - (689 ∩ 701) = 0
  - 说明：所有到店的客户都开单了（或开单人数大于到店人数）
  - 流失率：0 / 689 × 100% = 0%

#### 数据验证说明
1. **流失节点2的差异**：理论值367 vs 实际值476，说明存在邀约记录但时间范围外的情况
2. **流失节点4为0**：说明到店的客户基本都开单了，或者存在直接开单没有耗卡的情况
3. **数据合理性**：各节点人数和流失数量符合业务逻辑

---

## 📝 总结

### 关键发现
1. **关联字段使用率低**：`F_InviteId` 和 `F_AppointmentId` 使用率很低，需要主要依赖会员ID关联
2. **数据异常**：存在到店人数 > 预约人数、开单人数 > 到店人数的情况，需要使用交集计算
3. **时间范围**：需要确保所有节点的时间范围一致

### 推荐方案
1. **使用LEFT JOIN方式**：以拓客记录为主表，LEFT JOIN其他节点
2. **使用CASE WHEN判断**：判断每个会员在各个节点的状态
3. **计算交集**：对于流失节点3和4，需要计算交集而不是简单减法
4. **时间范围统一**：使用拓客时间作为基准，其他节点时间在拓客时间之后

### 下一步
1. 使用实际数据验证SQL查询
2. 优化查询性能
3. 实现前端可视化
4. 添加明细列表功能