业务02:金融行业中的贷后管理

在上一期（业务01：金融业务中的Tableau分析框架），喜乐君描述了金融贷款管理中的宏观业务流程，并介绍了“业务-数据-分析”的三层结构。本期计划讲解其中最为生涩的“贷后逾期管理”，总结自己近年来的所学所知，并以Tableau的方式完整展现。

一、贷后账户管理的基本业务与数据框架

这里的贷后管理，指金融机构对其贷款账户的余额管理。假设张三和李四在机构A开设了贷款账户，并分别办理了120元贷款业务，并约定12期还款，那么系统中就要分别为他们建立逻辑上的数据档案。借款人的借款（从机构角度就是贷款）业务，可以对应“账户借款信息表”。同时，账户获得贷款之后，就要按照约定按期还款，对应“账户还款明细表”。

这个业务场景可以理解为“账户余额管理”的过程，其中又分为放款和余额还款两个小部分。按照之前“业务-数据-分析”的三层结构，喜乐君把这个小的业务场景可以勾勒如下所示。数据是业务的记录，也是分析的起点。

在机构的数据库设计中，数据表的复杂度远远超过我们常人的想象。巨型的ERP系统和MES系统动辄几百万张数据表；即使习以为常的便利店的背后，也需要近千张数据库表的支撑。这里是数据库工程师和IT人员的专业领地，绝非业务人员所能轻易进入——不管再过多少年，业务人员都不可跨越这道高墙。

和数据库工程师、IT人员不同，业务管理者和业务分析师面对的是业务流程，他们所希望看到的是对应完整业务流程的整合表——这就是IT工程师构建数据宽表或者数据模型的数仓工作。在上面的三层结构中，我们很难看到这个过程的影子，这也超过了喜乐君目前及未来的专业范围。

接下来，本文从已经整合完毕的数据开始，讲解放款、借款的逻辑，并深入讲解逾期指标的分析过程。

二、基于实例数据的“数据与业务”说明

业务分析通常是基于IT提供的数据表开始的，这里借用了从公开网络中获得的两个完整数据表。本文所采用的是拍拍贷数据，包含了成交时间从2015年1月1日到2017年1月30日的328553的记录，这里的P2P贷款业务，可以理解为无抵押信用贷款。数据集包含LC.csv（借款账户数据）和LP.csv（借款账户还款计划和还款记录表）数据。

银行贷款的对象是客户，客户是借款人；银行贷款对应客户借款，一次借贷对应一个账户，银行的余额管理、逾期追踪就按照账户为对象管理，这也是接下来分析的重点。一个人可以有多个账户（如同一个人有多个银行卡），每个账户对应单独的还款计划。

1、LC客户借款信息表

对应“LC客户借款表”。

1.1 理解当下数据表的结构和业务场景

这个数据表包含当前的借款明细、借款人信息表（通常包含绝对不变的属性字段如性别，和相对不变的属性如年龄、评级、档案信息）。这里使用tableau Prep builder快速预览数据，包括14万行明细，如图所示。

在查看业务对应的数据表时，有几个关键：

• 识别唯一字段（IT称之为主键，PK=Primary Key），业务分析中喜乐君称之为“行级别字段”。要用业务中有意义的字段来描述，从而准确的理解对应的业务场景，而非“流水号”或者“主键”等字段。
• 区分描述性的字段和量化性的字段，从而用一句话完成的描述业务。几乎所有业务，都可以用一句话衡量“谁who、在何时when、于何地where、向谁whom、以何种方式how、予以何物what，并以多组数据量化此行为how much”。

能精确地识别数据表的唯一字段，找到每一行对应的业务场景，就能建立对数据表的基本理解，确保从业务到数据、从数据到业务映射逻辑的完整性和准确性。

在上面的数据表中，Listing ID字段是唯一的，可以理解为一个贷款账户。这里默认没有贷款方，也没有业务员等信息，因此每一行对应的业务场景可以简化为“（某个银行）在借款日期给客户的贷款交易（交易构成账户），借款金额多少、利率几成、期限多久等”，更多的字段用于描述客户特征（评级、年龄、性别、认证信息等），历史的交易数据，也是对客户特征的延伸补充。

可以把核心字段（账户、借款日期、金额等）之外的字段都可以理解为补充性字段，它们从哪里来？为什么还会有“历史成功借款金额”等聚合数据出现在账户数据表中呢？这就需要进一步理解数据表的构成和来源。

1.2 理解数据表中数据表的来源和业务意义

如果要更进一步，就要理解数据表中字段的大致来源。比如对账户的描述中，性别、年龄、教育水平等相对不变的属性字段，可以认为来自一个用户基本信息数据表；而各类认证信息，则可以理解为来自用户的认证档案表，它们都是在“客户”的层次上的连接join。

特别注意，这个数据后面有几列度量字段，体现了借款人的历史交易信息，包括“历史成功借款次数”“历史成功借款金额”“总待还本金”“历史正常还款期数”“历史逾期期数”，这些聚合值来自于更低的层次“每个借款人、每笔交易清单”数据。由于“客户*账户”层次数据表和“客户信息”层次数据表不在同一个层次，要在相同的层次完成聚合，它们在本质上是先聚合再连接join的过程。这个过程无法在Tableau Desktop中完成，而言借助于Tableau Prep Builder等ETL工具，或者在SQL中借助于Group by聚合和join完成。

2、LP客户的账户还款明细表

借款一旦放款，就会进入定期按时还款阶段，对于贷款机构可以称之为“账户余额管理”。机构希望客户按时定期还款、不要提前结清、更不要逾期不还，因为提前结清影响利润，逾期不还还要介入催收、撇账、不良资产处置等处理，不仅没有利润还要浪费资源。

因此，简单的数据表应该覆盖账户每一期的还款时间、逾期状态、还款本金等数据，必要时增加提前结清（EPO）、撇账（WO）、理赔等对非正常账户的相关字段。

在实例数据中，这里覆盖了账户每期的还款情况，包括还款状态、还款金额等。如图所示。

“大数据分析是多维度、结构化的分析”。基于上述已有的数据字段，就可以完成多个分析场景，比如：

• 不同状态的账户数占比（状态只有五类）
• 不同期数的账户数分布（期数是连续的）
• 不同时间的逾期账户数和金额（增加了状态筛选）

而如果借款人的账户信息表，则可以加入年龄、性别等其他属性字段。稍后会在分析阶段使用Tableau Desktop完成。

三、业务与分析指标

每个行业都有自己通用的特征标记和分析指标体系，他们来自于具体的业务场景，又升华了业务理解。按照“第三字段分类”的理解，典型的计算字段要么属于行级别的业务字段（如账龄、逾期状态），要么属于问题层面的分析字段（比如逾期比率、EPO提前结清比率）。

本文的重点是逾期分析，这里以账龄和逾期状态介绍基于数据明细的特征标签；以C- M1为代表介绍分析层面的业务指标。

1、账户账龄与逾期状态

先假设一个六期账户，账户2月5日借款，“还款期限”=6月，随着时间，账户的“月龄”（如同人的“年龄”）就会增加，称之为“账龄”。正常情况下，客户可以按期逐笔换钱，也可能逾期延迟还款，或者压根中止不还；当然也可能提前结清。不同情形，数据表都会做出对应的记录。

先看逾期。假定客户在第3期还款时出现了延迟，4月5日的还款日拖延到4月10日才还款。以4月5日为坐标点，之前是“未逾期状态”（或者说逾期天数=0），之后到4月10日为“逾期状态”（逾期天数分别为1～5），4月11日之后又回到“未逾期状态”（逾期天数=0）。分析不仅要刻画它们每天的状态，还要刻画前后的变化（状态迁徙）。如下图所示。

当然，逾期也可能一直逾期不还，所以“逾期天数”是可以无限累加的。比如回望2015年某个逾期账户，至今随着日期每天+1.

另外，还有一种非正常还款情形：提前结清EPO（Early Pay Off），在最终到期日之前，借款人一次性偿还所有借款本息。虽无逾期风险，但会损失利润；毕竟贷款业务中的部分利润来自于贷款余额的利息收入。

如图所示，标记了几个重要的时间点。逾期分析不仅要考虑各自的逾期天数，还要结合所在的账龄期间，比如DPD7+@MOB0～6，代表账龄MOB0～6期内发生的逾期天数大于等于7天的情形。

这里把上述几个关键概念罗列如下：

• 账龄MOB（Month of Book），指账户自放款起息日开始逐月的期间，简称为MOB1～6或者M1～6.
• 逾期天数DPD（days past due），从应还未还之日开始计算到某个时间点的天数间隔。通常有两种情形：延期支付，和逾期未付。不同的子情形又会对应不同的分析需求，比如短逾分析、首逾分析、逾期MOB分析、逾期迁徙分析等。
  • 账户短逾后延期偿还，逾期天数=还款日期-应还款日期；
  • 账户至今未未还，那么逾期天数 = 统计日期（通常是今天或各月期末）-应还款日期。

• 逾期期数Bucket：基于逾期天数的二次标记，通常未逾期标记为“M0”，逾期天数1～29天标记为M1，之后按照30天分组，或者判断，比如M2代表逾期天数30～59天的账户，M6+代表逾期天数180天以上的账户。高级用户可以用除法进位取整数自动完成标签分类。这里使用Tableau的逻辑如下：

IF [还款日期]-[到期日期] <=0 THEN ‘M0’
ELSE ‘M’+str(CEILING(([还款日期]-[到期日期])/30))
END

@tableau Desktop

这里以LP数据为例，如下所示，喜乐君列举了几个典型账户，并按照上述的逻辑为“账户还款状态”和“账户逾期状态”增加了描述标签。标签应该尽可能简单，诸如EPO@MOB3（即在账龄3期提前结清）这种组合标签，应该在视图中完成，而非在行级别完成。

2、基于上述标签的简单可视化

简单的可视化来自于既定维度和自定义标签的组合，比如不同逾期状态的账户分布、不同还款状态的账户占比等。注意，由于一个账户随着时间会有多个逾期状态和还款状态，所以这里的状态分析要么以单日或者单月的角度查看（比如下图中展示了2017年2月10日的数据，注意数据是不完整的，并非每个账户在当日都有对应），要么就以对比的视角查看不同状态随着时间的变化，即迁徙分析。

3、逾期的账户迁徙分析

讲了这么多，本文的重点是想介绍迁徙分析的逻辑和计算。

首先，迁徙一定是两个日期阶段的对比视角，比如本月期初账户状态正常、当下逾期的账户数，通常以C- M1+来代表（这里的C代表账户期初状态是未逾期状态，M1+代表当下看逾期天数大于1天）。

其次，迁徙只有在较大的尺度上才有意义。业务员每天的账户非常有效，客户迁徙就会剧烈波动，可能仅有的两个账户一个逾期，C- M1+%就是50%，没有逾期就是0%，此时更无法进一步计算C- M3%等多阶段迁徙指标。因此，通常从高级别的机构、销售渠道、客户分类等视角来看客户迁徙。

另外，账户的迁徙状态有多个起点、多个方向。如图所示，以7月31日为本月期初状态，账户可能从M0未逾期向高阶迁徙到M1状态，M1可以高阶迁徙到M2状态，也可能从M1回流到M0状态（客户延迟支付了分期的贷款）。

之前，喜乐君用“逾期天数”（dpd）来标记了逾期的状态分类，每一期的到期日期和还款日期的间隔，就是逾期天数。

那站在2017年2月20日的视角上看，期初（这里取1月31日）状态为M0未逾期，当前状态为M1（逾期天数1～29天），就代表C- M1%的迁徙比率，它代表每100个正常账户，有多少转入了M1状态。

此时，我们需要精确的指导两个日期的所有账户的状态，因此这个计算需要建立在切片的数据表基础上——账户的余额信息表，应该记录每天的账户状态，并为之标记逾期状态。数据样式可以如下：

数据日期	账户ID	逾期天数	逾期状态	……
2017/7/20	001	4	M1
2017/7/20	002	1	M1	……
2017/7/20	003	0	M0
2017/6/30	001	0	M0
2017/6/30	002	0	M0
2017/6/30	003	0	M0

在这里，账户001和002的迁徙状态是从C- M1的，而003账户则没有变化。这里分子和分母都要依赖于筛选，而且筛选的条件皆然不同，因此，筛选器不能增加到全局中，只能通过写入计算创建辅助列的方式实现。如下：

C-M1% =

SUM( IIF( [数据日期]= #2017-7-20# and [逾期状态]=’M1′, 1,null ) ) /

SUM( IIF( [数据日期]= #2017-6-30# and [逾期状态]=’M0′, 1,null ) )

这是基于切片的余额数据完成C- M1的简单方法。当然，在具体实操中，通常使用系统中的最后日期代替这里的2017年7月20日，从而实时查看。

last date = { [数据日期] }

期初日期 = [last date] – DAY([last date])

为了减轻分析的难度，有时候iT工程师会协助把上述的很多聚合在SQL中准备，聚合到月份的层次。

下一篇，笔者会寻找类似数据结构的数据，完成迁徙分析的可视化，并分析背后的计算逻辑和业务场景。

备注//本文对逾期分析的展开不够。还需进一步了解专业知识后修改。

参考文章：

https://zhuanlan.zhihu.com/p/136208249

喜乐君@Aug 21～23, 2021