基于MYSQL对消费金融公司及对手内部数据的分析

背景介绍：

公司主要做银行消费金融（以下称为甲方）的逾期催款业务，只做M1（逾期10-30天）和M2时段（逾期30-60天），其中M1有5家竞争对手（加上我们共6家），M2有4家竞争对手（加上我们共4家）。因为业务的特殊性，我们的业务员都是用甲方的系统作业，而且我们只能看到自己公司的数据，其他渠道数据几乎没有。因为每一天都有拉数据的习惯，偶然有一天发现甲方业务系统有全部渠道业务员的数据，虽然数据指标不多，而且没有没有太深入的数据，但是带着问题深入挖掘后得出几个方面的数据。

甲方渠道采用的是“大鱼吃小鱼”的策略，排名前面的渠道会吃掉后面渠道的案件，后面提到的那两家被淘就是渠道这种情况。所以了解整体数据的情况，尤其是甲方未公布的数据，对我们次月的人员规划及策略调整都有很大的帮助。

（注：1.数据已经过处理；2.对手以下统称位渠道；3.甲方也有参与业务，以下统称为内催渠道）

文章思路：提出问题——理解数据——数据预处理——解决问题——得出结论

一 提出问题

1.1 甲方情况：

甲方整体情况如何？

甲方各时段情况，案件量，占比 ，作业人数，人均案件量等是？

甲方时段各渠道情况数据，与实际是否一致？

1.2 渠道对手分析

对手实力如何，策略方面有何可借鉴的？

1.3 自身分析

优势如何，占比情况如何？

二 理解数据

日期：数据获取的日期，只是一天，因为渠道分案后基本上一个月都不会有大的变化，所以这里日期作用不大

ID：以甲方标准统一命名的员工ID，唯一值且规范可做主键

编号：以各家渠道首字母+编号命名的员工ID，可用于区分竞争对手

组编号：开始我不明白有什么意思，但是深入挖掘后发现可以用来区分时段

权重：一般最高权重是10，用于给员工分配案件量的比重，但是A对手10的案件量和B对手的案件量补一刀相等，取决于案件总数和作业人数

件数存量：员工的在案数

三：数据预处理

3.1 对“编号”字段处理，新增“渠道”字段

前面说到编号用于区分竞争对手，而且是以各家渠道首字母+编号命名的员工ID。每家渠道都是两个字的，所以取编号的前2位构建新指标来观察

# 创建testData对手标签 视图
CREATE VIEW testData渠道标签
AS
SELECT *,LEFT(编号,2) 渠道标签 FROM testData

观察发现标签数据还是不规范，进一步转化成“渠道”指标（为保护隐私不只转化为大写不转中文）

# 创建testData渠道视图，添加“渠道”类别
-- DROP VIEW IF EXISTS testData渠道
CREATE VIEW testData渠道
AS
SELECT *,
    (CASE WHEN 渠道标签 = 't1' OR 渠道标签 = 'tl' THEN 'TL'
                WHEN 渠道标签 = 'qw' THEN 'QW'
                WHEN 渠道标签 = 'hd' THEN 'HD'
                WHEN 渠道标签 = 'zj' THEN 'ZJ'
                WHEN 渠道标签 = 'yz' THEN 'YZ'
                WHEN 渠道标签 = 'hf' THEN 'HF'
                WHEN 渠道标签 = 'hj' THEN 'HJ'
                WHEN 渠道标签 = 'yn' THEN 'YN'
                WHEN 渠道标签 = 'lx' THEN 'LX'
              ELSE 'NC' END) AS 渠道
    FROM testData渠道标签
异常值：1.在平常业务中我有发现有tl写成t1(L小写成数字1）
              2.前面说甲方也占据一家渠道，经过与经理的沟通讨论，得知甲方内部有很多分类，所以统称为'NC'

按渠道分组统计，查看是否还有没有整理的渠道标签

观察发现渠道已分类好，但是发现两家被淘汰的渠道账号还存在，直接删除可以解决，但是会不会存在部分离职的人员账号未关的情况呢？创建渠道占比（件数存量除以权重）视图来查看一下

创建testData渠道占比视图

CREATE VIEW testData渠道占比

AS

SELECT *,ROUND((件数存量/权重),1) 渠道占比 FROM testData渠道 ORDER BY 渠道占比;

过滤掉渠道占比小于1异常数据，再次查看渠道分类数据

3.2 对“组编号”字段处理，新增“时段”字段

开始不明白这个组编号有什么用，但是我在分M1时段来查看渠道数量的时候发现组编号中含有”PEQ1"的都是代表M1时段，含有“PEQ2"都是代表M2时段。前面也说到甲方内侧的分类很多，加上与经理沟通讨论后得知，其余都可以归类为M3（逾期90-120天）

创建testData时段视图，处理时段分类标签
DROP VIEW IF EXISTS testData时段
CREATE VIEW testData时段
AS
SELECT *,
    (CASE WHEN 组编号 LIKE '%PEQ1%' THEN 'M1'
                WHEN 组编号 LIKE '%PEQ2%' THEN 'M2'
              ELSE 'M3' END) AS 时段
    FROM testData渠道占比
    WHERE 渠道占比 >1  # 过滤掉渠道占比小于1异常数据

查看清洗后的5行数据

四 解决问题

甲方分析

4.1 甲方整体情况

SELECT COUNT(件数存量)作业人数,
             SUM(件数存量) 总案件数,
             ROUND(SUM(件数存量)/COUNT(件数存量),0) 人均案件量
    FROM testData时段

甲方在催总案件185333件，总共作业人数1094人，人均在催案件量169.

4.2 甲方时段整体情况

SELECT 时段, 
        SUM(件数存量) 总案件数,
        CONCAT(ROUND((SUM(件数存量)/(SELECT SUM(件数存量) FROM testData时段)*100),1),'%')时段占比,
        COUNT(件数存量)作业人数,
        ROUND(SUM(件数存量)/COUNT(件数存量),0) 人均案件量,
        MAX(件数存量) 最大案件数,
        MIN(件数存量) 最小案件数
    FROM testData时段
    GROUP BY 时段 
    ORDER BY 时段,总案件数 DESC

通过上面分析知道甲方有3个在催时段M1,M2和M3（M3不做深入分析）。

案件数和占比：M1占比38.4%在催案件7.1万，M2占比29.4%在催5.5万，M3占比32.1%在催6万。

作业人数和人均案件量：根据业务和数据经验可知，逾期天数越大回款难度就越大，而且逾期金额越大。所以人均案件量分配时M1<M2<M3是符合逻辑的。而且M1高效的在催案件是100-120左右，M2是150-180左右，基本也符合。

最大最小案件：M1最大案件是1468，很明显是一个异常值，后续需深入分析；其余小案件可能跟案件权重或者渠道的总案件有关（业务相关性比较强，不做深入分析）。

查看对甲方各时段下的渠道数

4.3 甲方时段各渠道的整体情况

4.3.1 查看甲方各时段的渠道数

SELECT 渠道,
    SUM(CASE WHEN 时段 = 'M1' THEN 1 ELSE 0 END) 'M1',
    SUM(CASE WHEN 时段 = 'M2' THEN 1 ELSE 0 END) 'M2'
FROM testData时段 
GROUP BY 渠道

可以看出M1和M2共有8家渠道，M1时段有6家渠道，M2时段有5家渠道，其中QW,TL,NC3家两个时段的业务都有做，符合实际情况。

4.3.2 查看M2时段下各渠道的整体情况

SELECT 渠道,
        SUM(件数存量) 渠道总案件,
        CONCAT(ROUND((SUM(件数存量)/(SELECT SUM(件数存量) FROM testData时段 WHERE 时段 = 'M2')*100),0),'%') 渠道占比,
        COUNT(件数存量) 作业人数,
        ROUND(AVG(件数存量),0) 平均值,
        MAX(件数存量) 最大值,
        MIN(件数存量) 最小值
    FROM testData时段 WHERE 时段 = 'M2'
    GROUP BY 渠道
    ORDER BY 渠道总案件 DESC

M2时段有5家渠道，其中NC（甲方内催），QW，TL这3家案件占比达到95%，这3家也在做M1时段业绩，是长期的竞争&合作关系。

人均案件量：两家新的渠道（HF和HJ）还是偏少，前面说到甲方采用的是“大鱼吃小鱼”的策略，两家渠道即有可能是采取以人多精打的策略把排名先做起来。

最大最小案件：QW和HF的差值比较大，可能存在着分案或调整权重的可能。查看M2时段各渠道的权重情况

可以看到调整权重的只有HF，另一家是我们TL。其中调整权重是我们的一个重要的策略，每个月主管或者我都会根据业绩情况对权重进行调整，基本也是符合大鱼吃小鱼的策略。HF一家新的渠道，17个作业人员分4个渠道，说明他们很讲究策略，而且对案件的研究是比较深的（在另一个案例中也证明了我的推测）。

疑问点是为何QW没有权重分案，大小值却差别如此大？分别对其升序降序查看数据

可以看到权重都是10的QW，最大最小案件量分布的如何均匀。根据研究的进案规则可知正常情况是不会出现这种情况的，所以极大的可能是分案，将业绩差的员工案件分给好的员工。其实这种思路和调整权重是一样的，但是它有一个弊端就是会重复计算分案户数，这是甲方特殊的计算逻辑的原因，这是经过反复验证过的。

对手分析

前面也有提到，M2时段中3家老渠道NC（甲方内崔）,QW,TL案件占比高达95%的占比，HF和HJ两家是新增的渠道，目前占比较少。整体实力来看NC>QW>TL>HF>HJ，其中QW和HF需要重点关注。

4.4 通过异常数据分析对手策略

M1时段QW有一个异常值，分别对其降序升序查看数据

可以看到数据异常只有一个最大值1468，再查看是否是权重调整所造成的

和M2时段一样，QW没有进行权重调整，但是有一个账户存在大量的账户，经过以往对此份数据的观察得知这不是偶然。在与经理讨论过程中，偶然想到他们会不会是调整一批特殊的案子（可能是大额）到一个账户里，给一个组打呢。后面经过分析，基本吻合，但是没有百分之百的把握，毕竟人家也不会告诉我们。

后来我们根据这个策略组织一个大额组实验了一个月，发现效果不好，而且会重复计算委案。后面我们改进了这个策略，改成激励的方式，但是我们的方向也是大额，这个策略的调整不仅活跃的氛围，还调动了员工积极性，更重要的是提高了业绩。粗略算过，同比情况下，有激励比没有激励能提高5%。

自身分析

4.5 自身优劣势分析

通过分析得知，在案件占比方面，M2占比24%，排名第三，作业人数80人，排名第三；人均案件量166排名第二，均不算占优势。

但是在策略方便还是有优势的，通过业绩排名可以判断（近两年接近一半的第一是我们拿下的），占比稍低是其他原因。

结论

甲方分析

1.甲方整体情况：甲方在催总案件185333件，总共作业人数1094人，人均在催案件量169.

2.甲方时段情况：3个在催时段，分别是M1占比38.4%在催案件7.1万，M2占比29.4%在催5.5万，M3占比32.1%在催6万。 

3.甲方时段各渠道情况（主要分析M2）：

        (1) 共有8家渠道，M1时段有6家渠道，M2时段有5家渠道，其中QW,TL,NC3家两个时段的业务都有做；

        (2) M2时段有5家渠道，其中NC（甲方内催），QW，TL这3家案件占比达到95%，也在做M1时段。

对手分析

M2时段中3家老渠道NC（甲方内崔）,QW,TL案件占比高达95%的占比，HF和HJ两家是新增的渠道，目前占比较少。

4.策略方面：

        (1)M1时段QW有可能存在特殊组（可能是大额组）情况，专门负责特殊案件处理；

        (2)M2时段HF有调整权重的策略，案件规律研究的很透彻，即有可能在关键时刻利用案件规律提升业绩。

自身分析

5.自身优劣势分析

      劣势：案件占比处于第三，在3家大渠道中稍微落后；

      优势：策略上有优势，主要在权重调整及重点方向的（比如大额等）激励上，业绩排名可以体现。

分析思路：主要是从甲方/对手/自身三个角度分析，分析过程从整体到局部拆解。

本篇主要是根据甲方系统异常得到的一份以编号为主的数据进行的简要分析，主要以分析M2时段为主，M1为辅，M3不做分析。

说明：基于保密性数据已做处理，而且很多结论未做说明。此外还有很多研究之后得出的进案规律，分案策略，月初策略，月中策略，月末策略，特殊日期策略等都未说明。

未经同意，禁止使用！！！