业务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关联

特别注意,这个数据后面有几列度量字段,体现了借款人的历史交易信息,包括“历史成功借款次数”“历史成功借款金额”“总待还本金”“历史正常还款期数”“历史逾期期数”,这些聚合值来自于更低的层次“每个借款人、每笔交易清单”数据。由于“客户*账户”层次数据表和“客户信息”层次数据表不在同一个层次,要在相同的层次完成聚合,它们在本质上是先聚合再连接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/200014M1
2017/7/200021M1……
2017/7/200030M0
2017/6/300010M0
2017/6/300020M0
2017/6/300030M0

在这里,账户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

发布者:喜乐君

喜乐君 | Tableau Partner,Tableau Desktop and Server QA Certification

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

您正在使用您的 WordPress.com 账号评论。 注销 /  更改 )

Google photo

您正在使用您的 Google 账号评论。 注销 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

%d 博主赞过: