关于金融风险数据的ETL到分析

关于金融风险数据的ETL到分析（上）

文章主要讲述风险数据的获取、处理、可视化以及最后的分析，本文主要围绕风险指标包括：风险vintage、首逾率（催回率、滚动率、迁徙率、用户画像、截面逾期率等后陆续更新）。

一、数据提取背景与工具

1.数据来源主要包含：业务数据，埋点数据，爬虫数据等；
2.数据抽取：apache-flume、apache-sqoop、ETLX；
3.数据清洗：apache-hive、apache-flink、apache-spark、presto
4.数据展示与交付：tableau、前端、excel
5.脚本语言：PYTHON
6.调度工具：airflow（详细介绍见文章：[link] https://blog.youkuaiyun.com/haofangasd/article/details/83045432）
数据存储： Hadoop、S3、阿里云

二、指标之vintage

vintage，字面含义为美酒，酿酒。
字面个人见解：
美酒来源于几大关键要素：优质的原料 、 充裕的时间、精细的管控
几个关键要素也直接或间接说明了风险数据vintage的由来与体现：
1.优质的原料：充足的资产产生的大量的数据源
2.充裕的时间：资产的好坏体现只有在时间见证下才能完全表现出来，各个时间状态都有精确记录
3.精细的管控：放款对象的把控、服务的全面以及促收的严谨都会对数据表

1.那么vintage究竟是什么？

简单概述就是用过程来体现资金恶化规模情况，人员恶化规模情况，或者是优化情况，是过程体现的一个指标，也是同种时期比较资产或者对象优劣的方法。

2.明白了vintage是什么，那么vintage的口径是什么呢？

vintage是风险数据的核心，各种金融机构口径大同小异，无外乎维度的粗细，对象的差异，下面会从两个面详细说明：
1.对象：每月放款资金（每月放款笔数类似）
资金维度：利率区间、期数、放款金额区间、放款资方等
用户维度：年龄、地域、学历、性别等
指标：各逾期时长逾期率、各逾期时长逾期金额

2.对象：每月授信用户
资金维度：利率区间、期数、放款金额区间、放款资方等
用户维度：年龄、地域、学历、性别等
指标：各逾期时长逾期率、各逾期时长逾期金额、各逾期时长人数占比、各逾期时长人数等

以上为最为常见、最为重要几个对象，其他还有很多，不一一列举

3.vintage的ETL过程

（1）E（extract抽取，简介，后续指标方式相同，主要介绍TL过程）

离线数据（T+1）通过每日凌晨调度全量（少数增量）抽取mysql（业务库）数据至数仓ods层

（2）T （transfer转化清洗）

1）ods层数据经过异常数据清洗、敏感数据脱敏等一系列数据处理进入到dwd层，本层未加入任何业务逻辑；
2）dwd层数据经过相应业务逻辑、多表关联等获取基本粒度的数据信息到dws层，本层也会相应的设计多种业务模块数据（如获客、交易、用户经营、风险等等），到此各公司处理方法大同小异；
3）此点着重说明vintage转化方法，后续几项指标只介绍此点。vintage口径以最简单的每月放款资金不分维度，指标为逾期一天及以上为例：

表一：

create table default.vintage_test (
  order_number string comment '订单号',
  uid string comment 'uid',
  create_order_date date comment '订单创建日',
  principal decimal(20, 4) comment '订单本金',
  fee decimal(20, 4) comment '订单利息',
  over_status string comment '逾期状态',
  left_principal decimal(20, 4) comment '剩余订单本金',
  left_fee decimal(20, 4) comment '剩余订单利息',
  order_status string comment '订单状态',
  business_type string comment '业务类型',
  bank string comment '资金方'
) comment '订单表' partitioned by (day string) stored as orc

表介绍：
该订单表为每日全量表，记录订单相关信息，字段应有更多，此处仅为举例需要建立
字段说明：
create_order_date：订单创建日期，即起息日，订单生效时间；
over_status：逾期状态，M0-M7等，即M：month，即逾期1-7个月，此处可为自然月，也可为30天，看建立表逻辑而定，区分可见over_day逾期天数；
order_status:订单状态，分公司而定，该状态区分成功或失败订单

获取vintage代码（HQL）：

select
  a.loan_mth,
  a.day,
  a.mob_month,
  b.sum_principal,
  a.left_principal_gte1
from
  (select
    day,
    substring(create_order_date,1,7) as loan_mth, -- 放款月
    months_between(to_date(day),to_date(substring(create_order_date,1,7))) as mob_month, -- 观测月与放款月月份差，即mob月
    sum(case when over_status >= 'M1' then left_principal else 0 end) as left_principal_gte1 -- 逾期1天及以上金额/逾期M1及以上金额
  from
    default.vintage_test
  where
    date_add(day,1) = 1  -- 每月月末表现情况
    and order_status in () -- 取成功订单
    and create_order_date > '' -- 业务开始时间，可用来排除测试数据
    and business_type in () -- 可用来看哪部分业务数据
  group by 
    day,
    substring(create_order_date,1,7)
  ) a 
left join 
  (
  select
    substring(create_order_date,1,7) as loan_mth,
    sum(principal) as sum_principal -- 放款额
    from
    default.vintage_test
  where
    day = ''  -- 当前分区放款数据
    and order_status in () -- 取成功订单
    and create_order_date > '' -- 业务开始时间，可用来排除测试数据
    and business_type in () -- 可用来看哪部分业务数据
  group by 
    substring(create_order_date,1,7)
  ) b 
  on a.loan_mth = b.loan_mth

主要核心逻辑如上，其他对象与维度大同小异，不一一介绍

（3）L （load 加载，可视化）

以下数据设计公司数据，已做遮掩，主要用到tableau与Excel，主要给大家看到展现数据表两种形式：
形式一，tableau+mob：（最标准与常用）

形式二，Excel+观测月：

两种形式形式都有在用，相比之下个人更倾向于mob形式，感觉会更利于分析，就以上两种可有如下风险可视化图做展现：
在这里插入图片描述

(4)分析（主要讲解分析方法）

1.分析规模：各个月份放款规模、人数规模等变化；
2.分析风险变化：
（1）自身风险变化，每月风险值变化，从每月变化趋势分析资产变化情况；
（2）同期风险值对比，横轴对应的是相应放款月的mob月，可以对比各资产发展到同一时期变化情况，可以对比分析出各月资产情况；
几大主要关键点：
收敛点：资产处于收敛平稳，到最后的接近坏账的风险值
变化率：资产逾期变化速率，说明内外部情况影响情况
初期变化值：间接看出首逾（下文有介绍）、用户资质情况
tips：分析主要结合业务情况，从图表结合可以得出很对规律和结论，便于作出决策，便于针对各风险点作出管控，数据的分析，也是我们存储数据、清洗数据的最终目的！

三、指标之首逾率

1.首逾率究竟是什么？

顾名思义，首逾率，即首期逾期率，一般对客户放款客户会分为多期还款，首逾就是对于客户来说第一期的逾期率，

2.口径是什么呢？

各金融机构对于首逾的定义口径有很多，但是个人总结归纳下来，较为准确口径如下：
首逾率 = 首期逾期金额（含曾经逾期）/首期应还金额 （金额口径）
首逾率 = 首期逾期笔数（含曾经逾期）/首期应还笔数 （订单笔数口径）
具体维度可分：用户画像维度（地区、年龄、性别等等）、订单维度（期数、利率等等）
当然还有用户口径，此处不一一列举

3.首逾率的ETL过程（E过程同上）

（1）T

数据清洗常规步骤略，详情见vintage，此处只说明核心逻辑：
同vintage相似，各公司数仓表设计都有所差异，本处只以自身所处公司为例说明，同上订单表类似，另有
计划表如下：

create table default.stage_plan_test 
 (
  order_number string comment '订单号',
  stager_number string comment '计划号',
  uid string comment 'uid',
  stager_number_no int comment '期数',
  stager_number_type int comment '期数类型',
  stage_interest_date date comment '计划起息日',
  repayment_date date comment '计划到期日',
  repayment_time timestamp comment '计划还款时间',
  stage_principal decimal(20, 4) comment '计划应还本金',
  stage_fee decimal(20, 4) comment '计划应还利息',
  stage_over_status string comment '计划逾期状态',
  stage_left_principal decimal(20, 4) comment '计划剩余订单本金',
  stage_left_fee decimal(20, 4) comment '计划剩余订单利息',
  stage_status string comment '计划状态',
  business_type string comment '业务类型',
  bank string comment '资金方'
) comment '计划表' partitioned by (day string) stored as orc

表介绍：
订单表和计划表不同，订单表粒度到订单，而计划表详细记录了订单以下所有计划粒度的基本信息，这里详细说明，订单生成的时候，同时生成了多笔客户还款分期计划，具体和订单期数对应。

tips：此处建议大家在数仓建模进行表设计时不仅带有基本信息和实时切片信息，也要设计好往期变化信息，如历史最大逾期状态，历史某逾期状态的最大逾期值等，这些都有利于后期数据的分析，由于忽略这一点，以下逻辑取为最复杂的，大家可借鉴。

还款表如下：

create table default.stage_repayment_test 
 (
  order_number string comment '订单号',
  stager_number string comment '计划号',
  uid string comment 'uid',
  stage_interest_date date comment '计划起息日',
  repayment_date date comment '到期日',
  repayment_time timestamp comment '还款时间',
  repayment_prcinple decimal(20, 4) comment'还款本金',
  repayment_fee decimal(20, 4) comment'还款利息',
  stage_status string comment '计划状态',
  business_type string comment '业务类型',
  bank string comment '资金方'
) comment '还款流水表' partitioned by (day string) stored as orc

表介绍：
还款流水表，记录每一笔还款流水

获取首逾率代码（HQL）：

select
-- 首逾
a.rep_mth,
sum(coalesce(a.over_principal,0)+coalesce(b.repayment_prcinple,0))/sum(a.stage_principal) fst_ovd_rat_prc 
from
(
select
 substring(stage_interest_date,,1,7) rep_mth, -- 各到期月
  sum(stage_principal) stage_principal , -- 放款月放款额
  sum(case when stage_over_status <> 'M0' then stage_left_principal else 0 end) over_principal -- 截止目前仍旧逾期金额
from
  default.stage_plan_test
where
  day = '' -- 观测月
  and stager_number_no = 1 -- 首期
  and stage_status in () -- 计划状态，筛选出有效计划
  and business_type in () -- 业务类型
) a  
left join 
(
substring(b.stage_interest_date,1,7) rep_mth, -- 各到期月
sum(case when ovd_d1 >= 1 then a.repayment_prcinple else 0 end)  lft_d1 -- 曾经逾期金额
from
(select
  stage_interest_date,
  uid,
  datediff(to_date(repayment_time),to_date(repayment_date))  ovd_d1, -- 还款记录逾期归属
  repayment_prcinple
from
  dbank.loan_f_stage_repayment 
where
  day = ''
  and substring(repayment_time,1,10) <= day
  and stager_number_no = 1 
  and  business_type in ()) a 
) on a.rep_mth = b.rep_mth 

以上是最简单核心的代码逻辑，具体可据此扩充各维度、对象等等

（2）L （load 加载，可视化）

(4)分析

以上数据为真实数据，由图可以看出各到期月首逾变动较大，主要因为上图分获客渠道逾产品的口径，变动性较大；
分析点：
1.首逾率分布值：可看出整体资产风险情况；
2.变化率：资产逾期变化速率，说明内外部情况影响情况
此项指标可以取每月末观测各到期月逾期率，结合vintage分析资产前期质量状况

以上两个指标的理解仅代表个人理解，希望大家批评指正，后续的指标也持续更新中，对于风险数据的理解不同角度、业务知识理解程度、数据敏感情况都会对分析有影响，任何一项指标都不仅仅表面数值，所以对数据的体会需要不停学习，这也是ETL工程师需要掌握的另一面重要的技能，和大家共勉。