【SQLPlanet】基于迁徙率等指标浅析拍拍贷逾期数据（未完待续）

1、背景介绍

拍拍贷是一家金融科技公司，2007年成立于上海，并在2017年11月10日成功于美国纽交所上市。根据官方消息，截至2018年9月30日，拍拍贷累计成交额已突破1300亿，15-29天及30-59天的逾期率分别为0.83%和1.21%，自2015年，拍拍贷推出魔镜系统用于对每一笔借款进行风险评估，通过对借款人资质审核并预测未来逾期概率，对相应风险进行定价，基于大数法则保证整体的可观收益。

本文将从整体业务及通过迁徙率等指标进行分析，从数据中走进拍拍贷，了解拍拍贷平台在业务中出现过的问题及采取的信贷策略。

2、数据处理

2.1数据来源

数据来自拍拍贷真实业务数据（点击查看）从2015-01-01到2017-01-30的所有信用标的10%sample样本。

LCIS表中标的特征表共有37个字段，包括借款人ID，借款金额，期限，利率，年龄等，详见下图。Listingid为主键。其中历史信用记录相关字段均是拍拍贷内部的记录，即只有在拍拍贷有过历史借款的用户才有这部分记录，首次借款用户历史信用记录各项均默认为0。

2.2数据清洗

1. 将LCSI表中的数据导入navicat premium。
2. 更改原数据中ListingId和recorddate两个字段的列名及数据格式。

ALTER TABLE lcis CHANGE ListingId 用户编号 VARCHAR(10);

ALTER TABLE lcis CHANGE recorddate 记录日期 VARCHAR (10);

3. 数据重复值和异常值检查。检查发现记录日期是2016/9/30的数据有重复；另外，除5个记录日期外，存在非标准日期数据；后续均需删除。通过和前文列示的数据字典进行对比，排查其他字段的异常值、空值。（仅举例部分字段）

SELECT 记录日期,COUNT(*) AS 记录数量, COUNT(DISTINCT 用户编号) AS 不重复记录
FROM lcis
GROUP BY 记录日期
ORDER BY COUNT(*) DESC;

4. 为后续数据处理做准备，创建一张新表。添加逾期量化状态列和逾期账期列。

CREATE TABLE lcis_new AS SELECT DISTINCT
	lcis.*, 
	( 
	CASE WHEN 标当前状态 = '逾期中' THEN 1 ELSE 0 END
	) AS 逾期量化状态,
	(
	CASE WHEN 标当前状态 = '逾期中' THEN
	CONCAT('M',FLOOR(标当前逾期天数 / 30) + 1)
	ELSE 'C' END
	) AS 逾期账期
FROM
	lcis;

5. 在新表中剔除异常值和重复值

DELETE FROM lcis_new
WHERE (手机认证 NOT IN ('成功认证','未成功认证'))
  OR (户口认证 NOT IN ('成功认证','未成功认证'))
  OR (记录日期 NOT IN ('2016/9/30','2016/10/31','2016/11/30','2016/12/31','2017/2/28'))
  OR (标当前状态 NOT IN ('正常还款中','逾期中','已还清'));

6. 检查新表重复数据情况

SELECT 记录日期,COUNT(*) AS 记录数量, COUNT(DISTINCT 用户编号) AS 不重复记录 
FROM lcis_new
GROUP BY 记录日期
ORDER BY COUNT(*) DESC;

至此，已完成数据的初步处理工作，共得到有效数据291230条。

2.3数据初步探索

在数据处理中，可看到所有数据记录时间可分为16年9月-12月及17年2月底这5个时间段。接下来以最新记录日期即17年2月底的数据为样本，根据借款时间作为分组条件，初步观察15年1月至17年2月每月的逾期占比等情况。

SELECT
(CASE WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=1 THEN '15年1月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=2 THEN '15年2月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=3 THEN '15年3月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=4 THEN '15年4月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=5 THEN '15年5月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=6 THEN '15年6月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=7 THEN '15年7月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=8 THEN '15年8月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=9 THEN '15年9月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=10 THEN '15年10月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=11 THEN '15年11月'
	  WHEN YEAR(借款成功日期)=2015 AND MONTH(借款成功日期)=12 THEN '15年12月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=1 THEN '16年1月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=2 THEN '16年2月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=3 THEN '16年3月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=4 THEN '16年4月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=5 THEN '16年5月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=6 THEN '16年6月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=7 THEN '16年7月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=8 THEN '16年8月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=9 THEN '16年9月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=10 THEN '16年10月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=11 THEN '16年11月'
	  WHEN YEAR(借款成功日期)=2016 AND MONTH(借款成功日期)=12 THEN '16年12月'
	  WHEN YEAR(借款成功日期)=2017 AND MONTH(借款成功日期)=1 THEN '17年1月'
	  WHEN YEAR(借款成功日期)=2017 AND MONTH(借款成功日期)=2 THEN '17年2月'
	  END) AS 借款月份,
	SUM(借款金额) AS 总金额,ROUND(SUM(借款金额)/COUNT(借款金额),0) AS 平均借款金额,
	COUNT(借款金额) AS 用户数量,SUM(逾期量化状态),CONCAT(ROUND(SUM(逾期量化状态)*100/COUNT(逾期量化状态),2),'%') AS 逾期占比,
	CONCAT(ROUND(COUNT(逾期量化状态)*100/(SELECT COUNT(逾期量化状态) FROM lcis_new WHERE 记录日期 = '2017/2/28'),2),'%') AS 用户占比
FROM lcis_new
WHERE 记录日期 = '2017/2/28'
GROUP BY 借款月份 WITH ROLLUP;

从下表大致可以看出15年11月之前的逾期占比及用户占比大幅低于其他时间段整体数据，出现该现象的情况很有可能是因为平台对此段时间之前的逾期数据都计提坏账并核销，而非其他时间段经营管理不善所致。

这里需要重点指出的是逾期率并非越低越好，对于平台而言，合理的风险定价及扣除坏账后的真实收益更为重要。针对高风险用户核定较低定价而对低风险则给定了较高定价会分别损伤出借人和借款人利益，最终导致平台业务难以为继。

现在我们已经知道从17年观测时间观察15年数据，对风险控制情况的了解意义不大（数据受计提坏账核销污染）。因此，后文选取进行分析的数据以借款时间是2016年为基准展开阐述。

3、数据分析

3.1业务数据概览

为更好的理解平台业务数据，从2017年2月28日记录的数据中，提取2016年度业务数据，分别为放贷总金额、用户数量及逾期数量3个字段，计算平均额度、逾期用户占比情况、用户占比及实际用户累计占比这4个指标，具体含义如下：

• 平均额度：即额度总金额/用户数量，反映用户额度核给情况，一般受授信政策影响较大，若判定用户资质好，一般额度核给也会相对高。
• 逾期用户占比：逾期用户数量/用户数量，判断在某一时点下，整体逾期情况。
• 用户占比：即当月用户数量/当年度用户总数量。
• 实际用户累计占比：即前N个月用户占比累加。

取数SQL和结果如下图：

SELECT 
(CASE WHEN MONTH(借款成功日期) = 1 THEN '1月'
	  WHEN MONTH(借款成功日期) = 2 THEN '2月'
	  WHEN MONTH(借款成功日期) = 3 THEN '3月'
	  WHEN MONTH(借款成功日期) = 4 THEN '4月'
	  WHEN MONTH(借款成功日期) = 5 THEN '5月'
	  WHEN MONTH(借款成功日期) = 6 THEN '6月'
	  WHEN MONTH(借款成功日期) = 7 THEN '7月'
	  WHEN MONTH(借款成功日期) = 8 THEN '8月'
	  WHEN MONTH(借款成功日期) = 9 THEN '9月'
	  WHEN MONTH(借款成功日期) = 10 THEN '10月'
	  WHEN MONTH(借款成功日期) = 11 THEN '11月'
	  WHEN MONTH(借款成功日期) = 12 THEN '12月'
	  END) AS 借款月份,
ROUND(SUM(借款金额)/10000,0) AS 总借款金额（万元）,COUNT(用户编号) AS 用户数（个）,SUM(逾期量化状态) AS 逾期数（个）,
ROUND(SUM(借款金额)/COUNT(用户编号),0) AS 平均额度（元）,
CONCAT(ROUND(SUM(逾期量化状态)*100/COUNT(用户编号),2),'%') AS 逾期用户占比,
CONCAT(ROUND(COUNT(用户编号)*100/(SELECT COUNT(用户编号) FROM lcis_new_2016),2),'%') AS 用户占比
FROM lcis_new_2016
GROUP BY 借款月份 WITH ROLLUP;

将结果数据导出并整理为下表，从表中的条形图可以得出，平均额度整体呈现出先下降后上升的趋势，逾期用户占比整体呈下降趋势，用户占比及累计占比均呈上升趋势。

同时，2016年总借款金额与平均额度走势较为接近，从下图可以看出1月-3月借款资金整体下降，4月-8月保持稳定的较低水平，9月-12月新增放量显著。

将新增借款用户数的年度平均增长速度与实际增长速度对比如下图所示，1月-3月较正常时序落后，4月-8月基本持平，9月-12月放量显著。

从以上数据规律中，很容易将2016年分为三大阶段，分别为1月-3月，4月-8月及9月-12月，初步猜测是由于拍拍贷在此期间调整信贷策略所致。为证实猜测，提取用户在这3个阶段的初始评级和逾期情况等相关数据，并进行分析。

SELECT (CASE
		WHEN MONTH(借款成功日期) IN (1,2,3) THEN '第一阶段'
		WHEN MONTH(借款成功日期) IN (4,5,6,7,8) THEN '第二阶段'
		WHEN MONTH(借款成功日期) IN (9,10,11,12) THEN '第三阶段'
		END) AS 借款月份,
初始评级,COUNT(用户编号) AS 用户人数,SUM(逾期量化状态) AS 逾期人数,
CONCAT(ROUND(COUNT(用户编号)*100/(SELECT COUNT(用户编号) FROM lcis_new_2016),2) ,'%') AS 用户占比
FROM lcis_new_2016
GROUP BY 借款月份,初始评级
ORDER BY 借款月份,初始评级;

导出结果数据，并将三个阶段用户群评级情况绘制成下图所示，可以看出：

• 第一阶段：主力军的初始评级C(54%)，剩余依次是评级B(19%)、AA（11%）、D（7%）、A（6%）次之，共五大类客群；
• 第二阶段：主力军变更至初始评级为B（48%）的用户，剩余依次是初始评级C（25%）、A（14%）、D（7%）、AA（5%）共五大类客群；
• 第三阶段：初始评级为C的客户与B的客户各占半壁江山（各占近42%），剩余依次是初始评级A（9%）、D（6%），主要客群精简至四大类。

客群变化如此之大，足以可见拍拍贷平台撮合用户借贷的偏好的确发生了变更。前文已经提到，逾期率并非越低越好，对于平台而言，合理的风险定价及扣除坏账后的真实收益更为重要，因此，平台需要做的是在风险承受范围内，权衡收益的得失。

那么，这三个阶段客群变更对业务整体逾期情况影响如何呢？