一张万能数据集教你快速盘点零售信贷项目资产情况——零售风险政策人员必备基础技能...

    本文重点介绍一个零售风险策略分析人员常用的万能数据集，把万能数据集放到excel中，可以快速透视出常见的vintage报表、各种迁徙率趋势、余额衰减趋势、各月常规的运营指标放款趋势等，如果乐于在excel写公式，发挥余地更大，基本可以应对不少测算类的数据需求。当入职任何一个新公司或者接手一个贷款新项目后，有了这套数据集市，也可以帮助快速了解整个公司或者所在项目资产情况。

      本文也提供了一个分析思路，如果公司不具备这样的数据源集市，也可以通过一些列变量的衍生，加工出特定途径的集市，用来满足日常个性化风险测算诉求。

    文章后半部分也会简单介绍一下常见的审批集市、放款集市、还款计划等集市，以及他们所对应的报表的用途。

一、风控常用万能数据集

    从底层数据源及字段要求、加工程序、数据集、透视案例四个部分进行介绍。

1、底层数据源及字段要求：底层数据源需要记录每一笔贷款从放款当月到每一个月末统计时间（也即每个mob）的余额以及逾期的变迁情况。其中，放款当月Mob为0。案例如下：关键必要字段用绿色标出，必须具备后才能做出后续需要的数据集。在后续程序中我称为“贷后集市表”

    其他字段可根据分析诉求酌情个性化添加，比如产品的特殊信息（渠道、来源、区域、产品种类、测试标签等）、画像类信息（性别、年龄、x客群、学历、首付借等）

    给出一个详细放款案例进行展示：

    该订单从2020年9月开始放款，此后一直到最新月份2023年4月30日，数据源需要在每个mob（也即每个月末统计时点）都存储了一条该mob时点的最新余额信息，包含本月余额及逾期余额数据，以及上月余额及逾期金额情况。那么，从各个mob的数据变迁情况，便可计算笔贷款的余额以及还款趋势。

    注意：需要包含放款类的指标（如本笔放款金额、放款期限、放款利率），但这类放款信息在每个mob都是一样的，只有余额类数据才会在不同的mob出现变化。

2、统计程序：通过该程序框架可以加工出万能数据集。

也可以根据各自业务诉求，加入个性化的多维度变量或者多维画像，满足常规监控诉求。

select 
a.产品名称
,a.统计月份
,a.放款月份
,a.mob --mob账龄，放款当月mob=0
,a.放款期限
---还可以增加更多的个性化产品、画像类字段


,sum(a.放款金额) as 放款金额
,count(distinct a.放款编号) as 放款件数
,sum(a.余额) as 余额
,sum(a.正常余额M0) as M0余额
,sum(a.余额M1) as 余额M1
,sum(a.余额M2) as 余额M2
,sum(a.余额M3) as 余额M3
,sum(a.余额M4) as 余额M4
,sum(a.余额M5) as 余额M5
,sum(a.余额M6) as 余额M6
,sum(a.余额M7) as 余额M7
,sum(a.余额M8) as 余额M8  --m8+


,sum(a.上月正常余额M0) as 上月M0余额
,sum(a.上月余额M1) as 上月余额M1
,sum(a.上月余额M2) as 上月余额M2
,sum(a.上月余额M3) as 上月余额M3
,sum(a.上月余额M4) as 上月余额M4
,sum(a.上月余额M5) as 上月余额M5
,sum(a.上月余额M6) as 上月余额M6
,sum(a.上月余额M7) as 上月余额M7
,sum(a.上月余额M8) as 上月余额M8  --m8+
,count(ditinct(case when a.余额>0 then a.放款编号 else null end)) as 余额件数
,count(ditinct(case when a.正常余额M0>0 then a.放款编号 else null end)) as M0余额件数
,count(ditinct(case when a.余额M1>0 then a.放款编号 else null end)) as M1余额件数
,count(ditinct(case when a.余额M2>0 then a.放款编号 else null end)) as M2余额件数
,count(ditinct(case when a.余额M3>0 then a.放款编号 else null end)) as M3余额件数
,count(ditinct(case when a.余额M4>0 then a.放款编号 else null end)) as M4余额件数
,count(ditinct(case when a.余额M5>0 then a.放款编号 else null end)) as M5余额件数
,count(ditinct(case when a.余额M6>0 then a.放款编号 else null end)) as M6余额件数
,count(ditinct(case when a.余额M7>0 then a.放款编号 else null end)) as M7余额件数
,count(ditinct(case when a.余额M8>0 then a.放款编号 else null end)) as M8余额件数
,sum(a.余额M1+a.余额M2+a.余额M3+a.余额M4+a.余额M5+a.余额M6+a.余额M7+a.余额M8) as M1+余额
,sum(a.余额M2+a.余额M3+a.余额M4+a.余额M5+a.余额M6+a.余额M7+a.余额M8) as M2+余额
,sum(a.余额M3+a.余额M4+a.余额M5+a.余额M6+a.余额M7+a.余额M8) as M3+余额
,sum(a.余额M4+a.余额M5+a.余额M6+a.余额M7+a.余额M8) as M4+余额
,count(ditinct(case when a.余额M1+a.余额M2+a.余额M3+a.余额M4+a.余额M5+a.余额M6+a.余额M7+a.余额M8>0 then a.放款编号 else null end)) as M1+余额件数
,count(ditinct(case when a.余额M2+a.余额M3+a.余额M4+a.余额M5+a.余额M6+a.余额M7+a.余额M8>0 then a.放款编号 else null end)) as M2+余额件数
,count(ditinct(case when a.余额M3+a.余额M4+a.余额M5+a.余额M6+a.余额M7+a.余额M8>0 then a.放款编号 else null end)) as M3+余额件数
,count(ditinct(case when a.余额M4+a.余额M5+a.余额M6+a.余额M7+a.余额M8>0 then a.放款编号 else null end)) as M4+余额件数


from 贷后集市表 a
group by 
a.产品名称
,a.统计月份
,a.放款月份
,a.mob
,a.放款期限
;

3、万能数据集市：

   根据上述程序跑出来后万能数据集如下：因横屏展示不了这么多字段，

上月的M2之后的指标未全部展示出，具体参考程序：

上述原始数据集如要在excel中透视展示，需要经过一些公式加工，加工公式如下：

4、把数据集市放在excel中，设置好常见公式，就可以做成不同的透视分析

透视1：余额及迁徙率趋势

透视2：vintage，也可以根据不同画像指标，细化vintage

透视3：余额衰减趋势，计算久期等；

透视4、M0-M1，M1-M2，M2-M3、Mi/放款金额等各种需要拆解到具体的mob看趋势的指标

透视5：其他基础放款类指标，比如放款金额、放款件数、放款占比、件均、平均利率、平均期限等，注意需要限制mob，需要固定一个mob，建议为0。

    以上都是经过简单透视可随时监控的报表。通过这张万能数据集，还可以利用sumifs函数，统计出其他想要的指标，可以解决日常监控的常见风险指标诉求。

以下是其他常用的部分集市及监控诉求。

二、贷前审批数据集市及报表

    通过审批环节数据源，可方便统计申请、审批通过、详细拒绝原因、审批趋势等数据诉求。

1、底层数据源及字段要求：记录每一笔审批的详细信息。

    关键必要字段用绿色标出，必须具备后才能做出后续需要的数据集。在后续程序中我们暂称之为“审批集市表”

    其他字段可根据报表和分析诉求酌情个性化添加，比如产品的特殊信息（渠道、来源、区域、产品种类、测试标签等）、画像类信息（性别、年龄、不同客群、学历、首付借等）

分类	字段	备注
关键必要字段	申请编号	主键
关键必要字段	流程环节	区分授信、支用、提额等审批环节
关键必要字段	申请时间
关键必要字段	产品名称
关键必要字段	最终审批结果
关键必要字段	申请金额
关键必要字段	审批通过金额
关键必要字段	利率
关键必要字段	客户身份证
关键必要字段	拒绝原因详细代码	P001，P002….
关键必要字段	拒绝原因大类	A，B，C….
其他_画像信息	性别
其他_画像信息	年龄
其他_画像信息	出生年月
其他_画像信息	居住城市
其他_画像信息	申请城市
其他_画像信息	学历
其他_画像信息	客群标签
其他_画像信息	….	可以获得的客户信息都可以加工到这里，方便画像的监控
其他_产品信息	产品分类
其他_产品信息	….
其他	….

2、程序及数据源案例：

    通过该程序，可以加工出贷前数据集，放在excel中进行数据透视，查看整个项目的审批情况。也可以根据各自分析诉求，加入个性化的多维度变量或者多维画像，满足常规监控诉求。

select 
产品名称
,substr(a.申请时间,1,4)  as 年
,substr(a.申请时间,1,6)  as 月
,substr(a.申请时间,1,8)  as 日
,a.申请环节
,a.额度分组
,a.利率分组
,a.拒绝原因大类
,a.拒绝原因详细代码
----可以列出其他想统计的类别。
,count(distinct a.申请编号) as 申请件数
,count(distinct(case when a.申请结果='P' then a.申请编号 else null end)) as 通过件数
,sum(if(a.申请结果='P' ,通过金额,0)) as 通过金额
,count(distinct(case when a.拒绝原因大类 like '%A%' then a.申请编号 else null end)) as class1 ---拒绝原因大类1
---还可以列出更多想要监控的拒绝大类


,count(distinct(case when a.拒绝原因详细代码 like '%P001%' then a.申请编号 else null end)) as P001 ---P001拒绝
---还可以列出更多想要监控的拒绝原因


from 审批集市表 a
group by
产品名称
,substr(a.申请时间,1,4)  
,substr(a.申请时间,1,6) 
,substr(a.申请时间,1,8)  
,a.申请环节
,a.额度分组
,a.利率分组
,a.拒绝原因大类
,a.拒绝原因详细代码
;

3、贷前数据集

4、透视案例

案例1：某一个具体拒绝原因、拒绝原因趋势案例：

注：部分公式需要在透视表设置公式：通过率=通过件数/申请件数；

拒绝大类1%=拒绝大类1/申请件数

案例2：不同客群分布

可以详细展示利率、额度，或者画像类（比如性别、年龄、学历、模型分等）的分布、通过率、某一个具体的拒绝原因情况

三、放款环节数据集市及报表

1、底层数据源及字段要求：记录每一笔放款的详细信息。

    数据源及关键必要字段用绿色标出，必须具备后才能做出后续需要的数据集。在后续程序中暂称之为“放款集市表”

2、加工程序：

select 
产品名称
,substr(a.放款时间,1,4)  as 年
,substr(a.放款时间,1,6)  as 月
,substr(a.放款时间,1,8)  as 日
,a.利率分组
,a.放款期限
----可以列出其他想统计的类别。
,count(distinct a.放款编号) as 放款件数
,count(distinct a.身份证编号) as 放款人数
,sum(a.放款金额) as 放款金额
,sum(a.放款金额*利率) as 利率和
,sum(a.放款金额*期限) as 期限和
,sum(if(a.客群='A' ,通过金额,0)) as A客群放款金额


from 放款集市表 a
group by
产品名称
,substr(a.放款时间,1,4)  
,substr(a.放款时间,1,6) 
,substr(a.放款时间,1,8)  
,a.利率分组
,a.放款期限
;

3、数据集案例

4、透视案例

透视1：常见指标的趋势

注：部分指标需要经过透视表公式加工：件均放款金额=放款金额/放款件数；平均利率=利率和/放款金额；平均期限=期限和/放款金额

透视2：不同客群类别监控

    可以详细展示利率、额度，放款期限或者画像类（比如性别、年龄、学历、模型分等）的趋势、放款情况等

四、全流程集市表

    全流程集市表涵盖贷前审批、贷中以及余额最新状态的大全数据源。基于此数据源，可以方便计算动支率、授信后一定时间内的逾期状态等各种指标。

分类	字段	备注
关键必要字段	授信申请编号	申请环节
关键必要字段	申请时间
关键必要字段	身份证号
关键必要字段	产品名称
关键必要字段	最终审批结果
关键必要字段	申请金额
关键必要字段	授信通过金额
关键必要字段	放款编号	放款环节
关键必要字段	放款时间
关键必要字段	放款金额
关键必要字段	放款期限
关键必要字段	放款利率
关键必要字段	还款方式
关键必要字段	贷款到期日
关键必要字段	贷款余额	最新贷款情况
关键必要字段	当前逾期阶段
关键必要字段	当前逾期天数
关键必要字段	当前逾期余额
关键必要字段	正常余额
关键必要字段	是否结清
关键必要字段	结清日期
关键必要字段	当前执行期次
关键必要字段	当前统计时间
其他_画像信息	….	可以获得的客户信息都可以加工到这里，方便画像的监控

五、还款计划表

    利用还款计划表集市，可以计算首逾、流入率、历史逾期行为、历史逾期频率（比如近6个月逾期30天次数等）、近期逾期状态等一系列与逾期相关的衍生指标。