1. 这不是一张普通表格而是一张“贷款生命体征监测仪”你手头那张标着“Vintage Analysis”的Excel表大概率正安静地躺在风控部门共享盘某个叫“历史报表”的文件夹里更新日期停留在上个月底。它被当成常规月报附件发给管理层但没人真正盯着它看——直到某天逾期率突然跳升3个百分点大家才翻出这张表对着不同账龄段的回收率曲线反复比对试图从密密麻麻的数字里揪出那个“异常点”。我做过7年信贷风控系统搭建也带过4届风控新人最常听到的困惑是“Vintage表到底怎么看为什么我们建模时用它汇报时却只提一个‘M3逾期率’”这根本不是一张静态报表。它是一张动态的、按时间切片的“贷款生命周期心电图”。每一行代表一个放款月份比如2023年1月发放的所有贷款每一列代表放款后第N个月的资产表现M1、M2、M3…直至M24单元格里的数字不是冰冷的回收率或逾期率而是这批贷款在真实市场环境中的“生存概率”。它能告诉你2022年Q4批量化放款的客群在进入M6后回收率断崖式下跌不是因为催收团队懈怠而是这批客群在放款时就已埋下风险隐患——他们的多头借贷行为在征信报告中已有3次以上查询记录但当时模型未将其纳入强特征。关键词“信贷风控”“Vintage分析表”“数据建模”“风险洞察”不是并列关系而是因果链条数据建模是骨架Vintage表是血液风险洞察是神经反射。没有建模支撑的Vintage分析是经验主义的玄学没有Vintage验证的模型是空中楼阁而缺乏风险洞察的分析则只是把数字从A表复制到B表。这篇文章不教你怎么点开Excel做透视表而是带你亲手拆解这张表的底层逻辑它怎么从原始还款流水里长出来为什么必须用“放款月账龄”二维结构如何从一条下降曲线里预判三个月后的坏账洪峰我将用某消费金融公司的真实案例已脱敏还原整个过程——从SQL脚本怎么写到业务负责人看到哪一行会立刻叫停新客策略。2. Vintage分析表的本质一场与时间赛跑的归因实验2.1 为什么不能直接用“当前逾期率”代替Vintage新手最容易犯的错误是把风控日报里的“全量M3逾期率”当成决策依据。假设今天是2024年6月30日你看到全量贷款中M3逾期率为5.2%。这个数字看似权威实则充满陷阱混杂效应它把2023年1月放的款已进入M18、2023年12月放的款刚满M3、2024年5月放的款实际只有M1全部揉在一起计算。就像把刚出生的婴儿和80岁的老人放在一起统计“平均血压”数值毫无可比性。滞后幻觉M3逾期率反映的是3个月前放款的质量而业务部门此刻最需要知道的是“今天新批的这批客户未来会不会爆雷”。等M3数据出来坏账可能已发生补救窗口早已关闭。归因失效当M3逾期率从4.8%升至5.2%你无法判断是新客质量恶化还是老客遭遇失业潮抑或是催收策略调整导致回收延迟。三者对后续动作的应对完全相反。提示Vintage分析的核心价值是把“时间”作为唯一变量进行隔离控制。它强制要求你回答“同一时间放款的这批人在不同账龄阶段的表现如何变化”——这才是归因分析的黄金标准。2.2 Vintage表的数学本质条件生存函数的离散化表达别被“函数”吓到。用生活场景解释假设你开了一家奶茶店每天记录当天卖出的珍珠奶茶数量。如果只看“今日销量”你无法判断是天气热导致销量高还是新推出的杨枝甘露带动了整体销售。但如果你把每天卖出的奶茶按“制作时间”分组早班做的、午班做的、晚班做的再追踪每组奶茶在制作后第1小时、第2小时、第3小时的售罄率就能发现晚班做的奶茶在第2小时售罄率骤降——说明夜班员工打包手法有问题导致奶茶易漏。Vintage表就是这个逻辑的金融版“放款月份” 制作时间批次控制放款时点的宏观环境、审批政策、渠道质量“账龄” 时间维度控制贷款所处生命周期阶段单元格数值 条件生存概率例如202301批次贷款在M6时仍有92.3%的本金未逾期其数学表达为S(t|T) P(贷款存活至账龄t | 放款时间为T)其中T是放款月如202301t是账龄如6。这个条件概率剥离了时间混杂让风险信号变得纯粹。2.3 为什么必须用“放款月账龄”二维结构一维报表为何失败曾有家银行尝试用一维时间序列替代Vintage表横轴是自然月202301, 202302…纵轴是当月M3逾期率。结果发现曲线剧烈波动业务方质疑“风控模型是不是坏了”。真相是202303月的M3逾期率实际对应202212月放款的客群而202304月的M3逾期率对应202301月放款的客群。这两批客群的获客渠道完全不同前者主攻线下地推后者上线短视频投放风险特征天然差异巨大。一维表把不同“出生证”的孩子硬塞进同一个成长档案必然失真。二维结构的不可替代性在于纵向对比同一放款月不同账龄观察风险演进路径。健康客群的逾期率应呈“缓坡式上升”若在M4-M5出现陡升说明存在隐藏风险点如共债集中爆发。横向对比同一账龄不同放款月评估策略迭代效果。比如所有批次在M3的逾期率均在3.5%-4.0%之间但202310批次突然升至6.2%说明该月风控规则调整或渠道合作出现异常。斜向诊断对角线方向识别系统性风险。当202307M12、202308M11、202309M10…沿对角线数值集体恶化大概率是宏观经济或行业政策变化所致如某地突发疫情封控。3. 从原始数据到Vintage表四步构建法与避坑清单3.1 数据源准备三个必须校验的底层表Vintage表的准确性90%取决于源头数据质量。我见过太多团队花两周调优模型却因底层数据问题导致Vintage分析完全失效。以下是必须亲自核验的三张表表名核心字段关键校验点常见陷阱loan_master贷款主表loan_id, apply_date, disburse_date, product_code, channel_codedisburse_date必须精确到日且与核心系统放款流水一致禁止用apply_date替代某信托公司用审批通过日代替放款日导致Vintage表首月数据虚高审批通过但未放款的订单被计入repayment_schedule还款计划表loan_id, due_date, principal_due, interest_duedue_date必须为自然日不可用“放款后第N天”模糊计算需确认是否含宽限期某消金公司宽限期设为3天但计划表未标记宽限期状态导致M1逾期率被高估12%repayment_transaction还款流水表loan_id, repay_date, repay_amount, repay_type本金/利息/罚息repay_date必须为实际入账日非客户操作日需区分“部分还款”与“全额还款”某银行将客户APP端操作日误记为repay_date而资金实际T2到账造成M1回收率虚高注意务必用SQL跑一次交叉验证。例如检查loan_master中202301放款的贷款总数是否等于repayment_schedule中due_date在20230101-20230131区间内的记录数。差异超过0.5%必须溯源。3.2 账龄计算绝对不能依赖“系统自动计算”的三个理由账龄Months on Book, MOB是Vintage表的Y轴但很多团队直接调用核心系统返回的MOB字段这是重大隐患系统MOB基于放款日计算但风险暴露始于首次还款日一笔20230115放款的贷款首次还款日为20230215。若用放款日算MOB20230215当日MOB1但此时客户尚未产生任何还款行为谈不上“逾期”。正确做法是MOB FLOOR((current_date - first_due_date) / 30)以首次应还日为起点。系统MOB不处理展期、减免等特殊状态客户申请展期后系统MOB可能重置为1但Vintage分析需保持原始账龄连续性否则M6数据会消失。MOB单位必须统一为“整月”不能出现M1.5、M2.3等小数。实践中采用“滚动月”算法MOB YEAR(current_date)*12 MONTH(current_date) - (YEAR(first_due_date)*12 MONTH(first_due_date))确保跨年计算准确。实操SQL片段以MySQL为例-- 计算每笔贷款在指定观测日如20240630的账龄 SELECT l.loan_id, l.disburse_date, MIN(r.due_date) as first_due_date, -- 获取首次应还日 FLOOR(DATEDIFF(2024-06-30, MIN(r.due_date)) / 30) as mob_calc, -- 关键用年月差法避免月末误差 (YEAR(2024-06-30) * 12 MONTH(2024-06-30)) - (YEAR(MIN(r.due_date)) * 12 MONTH(MIN(r.due_date))) as mob_month FROM loan_master l JOIN repayment_schedule r ON l.loan_id r.loan_id WHERE l.disburse_date 2023-01-01 GROUP BY l.loan_id, l.disburse_date;3.3 Vintage矩阵生成从“宽表”到“二维透视”的关键转换生成Vintage表最易卡壳的环节是把单条贷款记录映射到二维矩阵。常见错误是用Excel手动拖拽或写复杂嵌套SQL。高效方案是分两步走第一步构建“贷款-账龄-状态”宽表对每笔贷款生成其在每个账龄点的状态快照。例如202301放款的贷款在M1202302状态为“正常”M2202303状态为“逾期”M3202304状态为“核销”。SQL核心逻辑-- 生成贷款在各账龄点的状态以M3为例 SELECT l.loan_id, l.disburse_month as vintage_month, 3 as mob, -- 目标账龄 CASE WHEN SUM(CASE WHEN r.due_date 2023-04-30 AND r.repay_date 2023-04-30 THEN r.principal_due ELSE 0 END) 0.01 * l.principal_amount THEN overdue WHEN SUM(CASE WHEN r.due_date 2023-04-30 AND r.repay_date IS NULL THEN r.principal_due ELSE 0 END) 0.95 * l.principal_amount THEN written_off ELSE performing END as status FROM loan_master l LEFT JOIN repayment_schedule r ON l.loan_id r.loan_id WHERE l.disburse_month 202301 GROUP BY l.loan_id, l.disburse_month;第二步透视聚合生成最终矩阵用数据库自带PIVOT功能或Python pandas的pivot_table。关键参数index: vintage_month放款月columns: mob账龄values: 状态指标如逾期本金占比、回收率实操心得不要一次性生成0-36个月全量账龄先聚焦0-12个月覆盖90%风险暴露期待流程跑通再扩展。某团队曾因生成36个月矩阵导致内存溢出排查3天才发现是某批贷款due_date为空值被SQL默认填充为0000-00-00计算账龄时产生负数。3.4 核心指标定义拒绝“黑箱公式”每个数字必须可追溯Vintage表中常见的指标必须明确定义计算逻辑避免业务方质疑。以下是经实战验证的黄金标准指标名称计算公式分子分母说明业务含义Mx逾期率Mx账龄下逾期本金总额/该Vintage批次初始放款本金总额逾期本金应还本金-已还本金不含罚息分母用放款日本金非合同本金衡量该批次在Mx时点的风险暴露程度Mx回收率Mx账龄下累计已还本金/该Vintage批次初始放款本金总额累计已还本金所有账龄≤Mx的还款本金总和衡量该批次在Mx时点的资金回笼效率Mx滚动逾期率Mx账龄下逾期本金/Mx-1账龄下未逾期本金分母为上一期未逾期本金非初始本金反映风险劣化速度比绝对逾期率更敏感特别注意“逾期”必须定义为“本金逾期”而非“本息逾期”。某汽车金融公司曾用本息逾期率导致M1数据虚高——客户常延迟还利息但按时还本金实际风险远低于数据呈现。4. 从数字到决策Vintage分析的三层穿透式解读法4.1 第一层识别异常点——用“三线交叉法”定位问题批次拿到Vintage表别急着看数字。先画三条基准线绿色健康线历史均值±1个标准差如M6逾期率长期在3.2%-4.1%之间黄色预警线历史均值1.5个标准差红色熔断线历史均值2个标准差然后执行“三线交叉扫描”纵向扫描找单一批次内哪一账龄点突破红色线。例如202310批次在M4突破红色线达7.8%而其他批次M4均在4.5%以下 → 锁定202310批次为问题源。横向扫描找同一账龄点哪些批次集体突破黄色线。例如所有2023年Q4批次在M3均超5.0% → 怀疑Q4风控策略或渠道问题。斜向扫描沿对角线如202307-M12, 202308-M11…看是否形成“下滑斜线”。若连续5个点下降大概率是外部冲击如某地房地产政策收紧影响装修贷客群。案例某现金贷公司发现202309批次在M2突然跃升至8.3%红线上但M1仅2.1%绿线内。纵向看异常横向看孤立。立即调取该批次客群画像发现73%来自某第三方导流平台该平台当月修改了用户授权协议导致大量客户在授信环节隐瞒多头借贷信息。风控部当天即暂停该渠道合作。4.2 第二层归因分析——用“双维度切片法”锁定根因找到问题批次后必须穿透到具体原因。绝不能停留在“这批人质量差”的结论。采用双维度切片维度一产品维度信用贷/分期贷/抵押贷维度二渠道维度自营APP/微信小程序/线下门店/第三方平台制作交叉表例如202309批次的M2逾期率分布渠道\产品信用贷分期贷抵押贷自营APP1.8%2.3%0.5%第三方平台12.7%9.4%—线下门店3.1%4.2%1.2%结论清晰问题集中在第三方平台的信用贷产品。进一步切片该渠道的获客时间——发现9月20日后接入的新API接口其传入的用户设备指纹重复率高达47%正常5%证实存在羊毛党批量注册。归因完成行动指令明确下线该API接口追回已放款中设备指纹异常的237笔贷款。4.3 第三层预测推演——用“曲线拟合压力测试”预判风险Vintage表的价值不仅在于诊断过去更在于预测未来。对健康批次用指数衰减模型拟合回收曲线Cumulative_Recovery(t) A * (1 - e^(-kt))其中t为账龄A为理论回收上限通常95%-98%k为衰减系数反映回收速度。当某批次拟合R²0.92或k值较历史均值下降30%即触发预警。例如202312批次拟合得k0.18历史均值0.25意味着回收速度变慢未来坏账可能增加。更进一步做压力测试情景1温和假设M6-M12回收率比历史均值低5个百分点 → 预测最终回收率下降至89.2%情景2严峻假设M4起逾期率持续攀升按当前斜率外推 → M12时逾期率将达18.7%远超拨备覆盖率阈值实操技巧用Excel的FORECAST.ETS函数比手动拟合更高效。输入历史回收率序列M1-M6设定目标账龄M12函数自动输出预测值及置信区间。某城商行用此法提前45天预判某区域房贷风险及时调整该区域LTV上限。5. 常见问题与排查技巧实录那些没写在手册里的坑5.1 问题1Vintage表显示“某批次M0逾期率100%”但实际从未放款现象202311批次在M0放款当月逾期率显示为100%而业务确认该月无放款。排查路径检查loan_master中202311放款的贷款disburse_date是否为2023-11-01查repayment_schedule这些贷款的due_date是否为2023-11-01首次还款日不可能等于放款日发现due_date被错误设置为放款日导致系统判定“到期未还” → M0即逾期。根因还款计划生成逻辑缺陷未按产品规则设置宽限期信用贷宽限期3天但代码写死为0。修复修正还款计划生成脚本增加宽限期配置表。5.2 问题2不同账龄点的回收率之和超过100%现象202301批次M1回收率35%M2回收率42%M3回收率38%累加达115%。排查路径检查回收率计算公式是否用了SUM(已还本金)/初始本金发现SQL中未去重同一笔还款被多次计入不同账龄如客户在M2还款但系统同时计入M1和M2的还款流水。根因还款流水表设计缺陷repay_date未与due_date严格绑定导致“一笔还款多归属”。修复在聚合时增加DISTINCT去重或重构还款流水关联逻辑。5.3 问题3Vintage表在M12后数据突然归零现象所有批次在M12之后的回收率、逾期率均为0。排查路径检查repayment_scheduleM12之后的due_date是否存在发现部分贷款的还款计划只生成了12期但实际合同期为24期。根因核心系统还款计划生成模块有BUG对分期贷产品默认只生成12期计划。修复升级核心系统补丁或临时用存储过程补全缺失期数。5.4 问题4业务方质疑“Vintage表太滞后等看到M3数据时坏账已发生”回应策略短期提供“准实时Vintage”替代方案。用M0-M2数据机器学习模型预测M3。例如用XGBoost训练特征包括放款时多头查询次数、收入证明类型、设备GPS定位稳定性、首次APP登录距放款时长。某机构实测预测M3逾期率AUC达0.82提前30天预警准确率76%。长期推动建立“前置风控仪表盘”。在放款决策页嵌入Vintage类比指标如“同类渠道近3月M3逾期率均值”让审批员在放款瞬间看到历史参照系。最后分享一个血泪教训某次版本升级后Vintage表M1数据全部消失。排查48小时发现是数据库字符集从utf8mb4改为utf8导致disburse_month字段varchar类型中202301被截断为20230。解决方案永远在关键字段上加CHECK约束CHECK (disburse_month REGEXP ^[0-9]{6}$)。
Vintage分析表：信贷风控中的贷款生命周期心电图
1. 这不是一张普通表格而是一张“贷款生命体征监测仪”你手头那张标着“Vintage Analysis”的Excel表大概率正安静地躺在风控部门共享盘某个叫“历史报表”的文件夹里更新日期停留在上个月底。它被当成常规月报附件发给管理层但没人真正盯着它看——直到某天逾期率突然跳升3个百分点大家才翻出这张表对着不同账龄段的回收率曲线反复比对试图从密密麻麻的数字里揪出那个“异常点”。我做过7年信贷风控系统搭建也带过4届风控新人最常听到的困惑是“Vintage表到底怎么看为什么我们建模时用它汇报时却只提一个‘M3逾期率’”这根本不是一张静态报表。它是一张动态的、按时间切片的“贷款生命周期心电图”。每一行代表一个放款月份比如2023年1月发放的所有贷款每一列代表放款后第N个月的资产表现M1、M2、M3…直至M24单元格里的数字不是冰冷的回收率或逾期率而是这批贷款在真实市场环境中的“生存概率”。它能告诉你2022年Q4批量化放款的客群在进入M6后回收率断崖式下跌不是因为催收团队懈怠而是这批客群在放款时就已埋下风险隐患——他们的多头借贷行为在征信报告中已有3次以上查询记录但当时模型未将其纳入强特征。关键词“信贷风控”“Vintage分析表”“数据建模”“风险洞察”不是并列关系而是因果链条数据建模是骨架Vintage表是血液风险洞察是神经反射。没有建模支撑的Vintage分析是经验主义的玄学没有Vintage验证的模型是空中楼阁而缺乏风险洞察的分析则只是把数字从A表复制到B表。这篇文章不教你怎么点开Excel做透视表而是带你亲手拆解这张表的底层逻辑它怎么从原始还款流水里长出来为什么必须用“放款月账龄”二维结构如何从一条下降曲线里预判三个月后的坏账洪峰我将用某消费金融公司的真实案例已脱敏还原整个过程——从SQL脚本怎么写到业务负责人看到哪一行会立刻叫停新客策略。2. Vintage分析表的本质一场与时间赛跑的归因实验2.1 为什么不能直接用“当前逾期率”代替Vintage新手最容易犯的错误是把风控日报里的“全量M3逾期率”当成决策依据。假设今天是2024年6月30日你看到全量贷款中M3逾期率为5.2%。这个数字看似权威实则充满陷阱混杂效应它把2023年1月放的款已进入M18、2023年12月放的款刚满M3、2024年5月放的款实际只有M1全部揉在一起计算。就像把刚出生的婴儿和80岁的老人放在一起统计“平均血压”数值毫无可比性。滞后幻觉M3逾期率反映的是3个月前放款的质量而业务部门此刻最需要知道的是“今天新批的这批客户未来会不会爆雷”。等M3数据出来坏账可能已发生补救窗口早已关闭。归因失效当M3逾期率从4.8%升至5.2%你无法判断是新客质量恶化还是老客遭遇失业潮抑或是催收策略调整导致回收延迟。三者对后续动作的应对完全相反。提示Vintage分析的核心价值是把“时间”作为唯一变量进行隔离控制。它强制要求你回答“同一时间放款的这批人在不同账龄阶段的表现如何变化”——这才是归因分析的黄金标准。2.2 Vintage表的数学本质条件生存函数的离散化表达别被“函数”吓到。用生活场景解释假设你开了一家奶茶店每天记录当天卖出的珍珠奶茶数量。如果只看“今日销量”你无法判断是天气热导致销量高还是新推出的杨枝甘露带动了整体销售。但如果你把每天卖出的奶茶按“制作时间”分组早班做的、午班做的、晚班做的再追踪每组奶茶在制作后第1小时、第2小时、第3小时的售罄率就能发现晚班做的奶茶在第2小时售罄率骤降——说明夜班员工打包手法有问题导致奶茶易漏。Vintage表就是这个逻辑的金融版“放款月份” 制作时间批次控制放款时点的宏观环境、审批政策、渠道质量“账龄” 时间维度控制贷款所处生命周期阶段单元格数值 条件生存概率例如202301批次贷款在M6时仍有92.3%的本金未逾期其数学表达为S(t|T) P(贷款存活至账龄t | 放款时间为T)其中T是放款月如202301t是账龄如6。这个条件概率剥离了时间混杂让风险信号变得纯粹。2.3 为什么必须用“放款月账龄”二维结构一维报表为何失败曾有家银行尝试用一维时间序列替代Vintage表横轴是自然月202301, 202302…纵轴是当月M3逾期率。结果发现曲线剧烈波动业务方质疑“风控模型是不是坏了”。真相是202303月的M3逾期率实际对应202212月放款的客群而202304月的M3逾期率对应202301月放款的客群。这两批客群的获客渠道完全不同前者主攻线下地推后者上线短视频投放风险特征天然差异巨大。一维表把不同“出生证”的孩子硬塞进同一个成长档案必然失真。二维结构的不可替代性在于纵向对比同一放款月不同账龄观察风险演进路径。健康客群的逾期率应呈“缓坡式上升”若在M4-M5出现陡升说明存在隐藏风险点如共债集中爆发。横向对比同一账龄不同放款月评估策略迭代效果。比如所有批次在M3的逾期率均在3.5%-4.0%之间但202310批次突然升至6.2%说明该月风控规则调整或渠道合作出现异常。斜向诊断对角线方向识别系统性风险。当202307M12、202308M11、202309M10…沿对角线数值集体恶化大概率是宏观经济或行业政策变化所致如某地突发疫情封控。3. 从原始数据到Vintage表四步构建法与避坑清单3.1 数据源准备三个必须校验的底层表Vintage表的准确性90%取决于源头数据质量。我见过太多团队花两周调优模型却因底层数据问题导致Vintage分析完全失效。以下是必须亲自核验的三张表表名核心字段关键校验点常见陷阱loan_master贷款主表loan_id, apply_date, disburse_date, product_code, channel_codedisburse_date必须精确到日且与核心系统放款流水一致禁止用apply_date替代某信托公司用审批通过日代替放款日导致Vintage表首月数据虚高审批通过但未放款的订单被计入repayment_schedule还款计划表loan_id, due_date, principal_due, interest_duedue_date必须为自然日不可用“放款后第N天”模糊计算需确认是否含宽限期某消金公司宽限期设为3天但计划表未标记宽限期状态导致M1逾期率被高估12%repayment_transaction还款流水表loan_id, repay_date, repay_amount, repay_type本金/利息/罚息repay_date必须为实际入账日非客户操作日需区分“部分还款”与“全额还款”某银行将客户APP端操作日误记为repay_date而资金实际T2到账造成M1回收率虚高注意务必用SQL跑一次交叉验证。例如检查loan_master中202301放款的贷款总数是否等于repayment_schedule中due_date在20230101-20230131区间内的记录数。差异超过0.5%必须溯源。3.2 账龄计算绝对不能依赖“系统自动计算”的三个理由账龄Months on Book, MOB是Vintage表的Y轴但很多团队直接调用核心系统返回的MOB字段这是重大隐患系统MOB基于放款日计算但风险暴露始于首次还款日一笔20230115放款的贷款首次还款日为20230215。若用放款日算MOB20230215当日MOB1但此时客户尚未产生任何还款行为谈不上“逾期”。正确做法是MOB FLOOR((current_date - first_due_date) / 30)以首次应还日为起点。系统MOB不处理展期、减免等特殊状态客户申请展期后系统MOB可能重置为1但Vintage分析需保持原始账龄连续性否则M6数据会消失。MOB单位必须统一为“整月”不能出现M1.5、M2.3等小数。实践中采用“滚动月”算法MOB YEAR(current_date)*12 MONTH(current_date) - (YEAR(first_due_date)*12 MONTH(first_due_date))确保跨年计算准确。实操SQL片段以MySQL为例-- 计算每笔贷款在指定观测日如20240630的账龄 SELECT l.loan_id, l.disburse_date, MIN(r.due_date) as first_due_date, -- 获取首次应还日 FLOOR(DATEDIFF(2024-06-30, MIN(r.due_date)) / 30) as mob_calc, -- 关键用年月差法避免月末误差 (YEAR(2024-06-30) * 12 MONTH(2024-06-30)) - (YEAR(MIN(r.due_date)) * 12 MONTH(MIN(r.due_date))) as mob_month FROM loan_master l JOIN repayment_schedule r ON l.loan_id r.loan_id WHERE l.disburse_date 2023-01-01 GROUP BY l.loan_id, l.disburse_date;3.3 Vintage矩阵生成从“宽表”到“二维透视”的关键转换生成Vintage表最易卡壳的环节是把单条贷款记录映射到二维矩阵。常见错误是用Excel手动拖拽或写复杂嵌套SQL。高效方案是分两步走第一步构建“贷款-账龄-状态”宽表对每笔贷款生成其在每个账龄点的状态快照。例如202301放款的贷款在M1202302状态为“正常”M2202303状态为“逾期”M3202304状态为“核销”。SQL核心逻辑-- 生成贷款在各账龄点的状态以M3为例 SELECT l.loan_id, l.disburse_month as vintage_month, 3 as mob, -- 目标账龄 CASE WHEN SUM(CASE WHEN r.due_date 2023-04-30 AND r.repay_date 2023-04-30 THEN r.principal_due ELSE 0 END) 0.01 * l.principal_amount THEN overdue WHEN SUM(CASE WHEN r.due_date 2023-04-30 AND r.repay_date IS NULL THEN r.principal_due ELSE 0 END) 0.95 * l.principal_amount THEN written_off ELSE performing END as status FROM loan_master l LEFT JOIN repayment_schedule r ON l.loan_id r.loan_id WHERE l.disburse_month 202301 GROUP BY l.loan_id, l.disburse_month;第二步透视聚合生成最终矩阵用数据库自带PIVOT功能或Python pandas的pivot_table。关键参数index: vintage_month放款月columns: mob账龄values: 状态指标如逾期本金占比、回收率实操心得不要一次性生成0-36个月全量账龄先聚焦0-12个月覆盖90%风险暴露期待流程跑通再扩展。某团队曾因生成36个月矩阵导致内存溢出排查3天才发现是某批贷款due_date为空值被SQL默认填充为0000-00-00计算账龄时产生负数。3.4 核心指标定义拒绝“黑箱公式”每个数字必须可追溯Vintage表中常见的指标必须明确定义计算逻辑避免业务方质疑。以下是经实战验证的黄金标准指标名称计算公式分子分母说明业务含义Mx逾期率Mx账龄下逾期本金总额/该Vintage批次初始放款本金总额逾期本金应还本金-已还本金不含罚息分母用放款日本金非合同本金衡量该批次在Mx时点的风险暴露程度Mx回收率Mx账龄下累计已还本金/该Vintage批次初始放款本金总额累计已还本金所有账龄≤Mx的还款本金总和衡量该批次在Mx时点的资金回笼效率Mx滚动逾期率Mx账龄下逾期本金/Mx-1账龄下未逾期本金分母为上一期未逾期本金非初始本金反映风险劣化速度比绝对逾期率更敏感特别注意“逾期”必须定义为“本金逾期”而非“本息逾期”。某汽车金融公司曾用本息逾期率导致M1数据虚高——客户常延迟还利息但按时还本金实际风险远低于数据呈现。4. 从数字到决策Vintage分析的三层穿透式解读法4.1 第一层识别异常点——用“三线交叉法”定位问题批次拿到Vintage表别急着看数字。先画三条基准线绿色健康线历史均值±1个标准差如M6逾期率长期在3.2%-4.1%之间黄色预警线历史均值1.5个标准差红色熔断线历史均值2个标准差然后执行“三线交叉扫描”纵向扫描找单一批次内哪一账龄点突破红色线。例如202310批次在M4突破红色线达7.8%而其他批次M4均在4.5%以下 → 锁定202310批次为问题源。横向扫描找同一账龄点哪些批次集体突破黄色线。例如所有2023年Q4批次在M3均超5.0% → 怀疑Q4风控策略或渠道问题。斜向扫描沿对角线如202307-M12, 202308-M11…看是否形成“下滑斜线”。若连续5个点下降大概率是外部冲击如某地房地产政策收紧影响装修贷客群。案例某现金贷公司发现202309批次在M2突然跃升至8.3%红线上但M1仅2.1%绿线内。纵向看异常横向看孤立。立即调取该批次客群画像发现73%来自某第三方导流平台该平台当月修改了用户授权协议导致大量客户在授信环节隐瞒多头借贷信息。风控部当天即暂停该渠道合作。4.2 第二层归因分析——用“双维度切片法”锁定根因找到问题批次后必须穿透到具体原因。绝不能停留在“这批人质量差”的结论。采用双维度切片维度一产品维度信用贷/分期贷/抵押贷维度二渠道维度自营APP/微信小程序/线下门店/第三方平台制作交叉表例如202309批次的M2逾期率分布渠道\产品信用贷分期贷抵押贷自营APP1.8%2.3%0.5%第三方平台12.7%9.4%—线下门店3.1%4.2%1.2%结论清晰问题集中在第三方平台的信用贷产品。进一步切片该渠道的获客时间——发现9月20日后接入的新API接口其传入的用户设备指纹重复率高达47%正常5%证实存在羊毛党批量注册。归因完成行动指令明确下线该API接口追回已放款中设备指纹异常的237笔贷款。4.3 第三层预测推演——用“曲线拟合压力测试”预判风险Vintage表的价值不仅在于诊断过去更在于预测未来。对健康批次用指数衰减模型拟合回收曲线Cumulative_Recovery(t) A * (1 - e^(-kt))其中t为账龄A为理论回收上限通常95%-98%k为衰减系数反映回收速度。当某批次拟合R²0.92或k值较历史均值下降30%即触发预警。例如202312批次拟合得k0.18历史均值0.25意味着回收速度变慢未来坏账可能增加。更进一步做压力测试情景1温和假设M6-M12回收率比历史均值低5个百分点 → 预测最终回收率下降至89.2%情景2严峻假设M4起逾期率持续攀升按当前斜率外推 → M12时逾期率将达18.7%远超拨备覆盖率阈值实操技巧用Excel的FORECAST.ETS函数比手动拟合更高效。输入历史回收率序列M1-M6设定目标账龄M12函数自动输出预测值及置信区间。某城商行用此法提前45天预判某区域房贷风险及时调整该区域LTV上限。5. 常见问题与排查技巧实录那些没写在手册里的坑5.1 问题1Vintage表显示“某批次M0逾期率100%”但实际从未放款现象202311批次在M0放款当月逾期率显示为100%而业务确认该月无放款。排查路径检查loan_master中202311放款的贷款disburse_date是否为2023-11-01查repayment_schedule这些贷款的due_date是否为2023-11-01首次还款日不可能等于放款日发现due_date被错误设置为放款日导致系统判定“到期未还” → M0即逾期。根因还款计划生成逻辑缺陷未按产品规则设置宽限期信用贷宽限期3天但代码写死为0。修复修正还款计划生成脚本增加宽限期配置表。5.2 问题2不同账龄点的回收率之和超过100%现象202301批次M1回收率35%M2回收率42%M3回收率38%累加达115%。排查路径检查回收率计算公式是否用了SUM(已还本金)/初始本金发现SQL中未去重同一笔还款被多次计入不同账龄如客户在M2还款但系统同时计入M1和M2的还款流水。根因还款流水表设计缺陷repay_date未与due_date严格绑定导致“一笔还款多归属”。修复在聚合时增加DISTINCT去重或重构还款流水关联逻辑。5.3 问题3Vintage表在M12后数据突然归零现象所有批次在M12之后的回收率、逾期率均为0。排查路径检查repayment_scheduleM12之后的due_date是否存在发现部分贷款的还款计划只生成了12期但实际合同期为24期。根因核心系统还款计划生成模块有BUG对分期贷产品默认只生成12期计划。修复升级核心系统补丁或临时用存储过程补全缺失期数。5.4 问题4业务方质疑“Vintage表太滞后等看到M3数据时坏账已发生”回应策略短期提供“准实时Vintage”替代方案。用M0-M2数据机器学习模型预测M3。例如用XGBoost训练特征包括放款时多头查询次数、收入证明类型、设备GPS定位稳定性、首次APP登录距放款时长。某机构实测预测M3逾期率AUC达0.82提前30天预警准确率76%。长期推动建立“前置风控仪表盘”。在放款决策页嵌入Vintage类比指标如“同类渠道近3月M3逾期率均值”让审批员在放款瞬间看到历史参照系。最后分享一个血泪教训某次版本升级后Vintage表M1数据全部消失。排查48小时发现是数据库字符集从utf8mb4改为utf8导致disburse_month字段varchar类型中202301被截断为20230。解决方案永远在关键字段上加CHECK约束CHECK (disburse_month REGEXP ^[0-9]{6}$)。
