拓客流失节点分析-计算逻辑设计.md 18.7 KB

Edit Raw Blame History


拓客流失节点分析 - 计算逻辑设计
📊 数据概览
数据库统计（全量数据）

拓客记录总数: 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）
定义: 拓客后没有邀约记录的客户

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


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）
定义: 有邀约记录但没有预约记录的客户

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


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）
定义: 有预约记录但没有耗卡记录的客户

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


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）
定义: 有耗卡记录但没有开单记录的客户

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


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：拓客未邀约
流失数量 = COUNT(DISTINCT 拓客会员ID) - COUNT(DISTINCT 邀约会员ID)
流失率 = 流失数量 / 拓客人数 × 100%

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

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

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


📊 完整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方式（更高效）
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（用于测试）
测试单个活动的流失节点
-- 测试活动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%


数据验证说明

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


📝 总结
关键发现

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

推荐方案

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

下一步

使用实际数据验证SQL查询
优化查询性能
实现前端可视化
添加明细列表功能