SQL数据分析-识别潜在贷款客户群

前言

Thera Bank是一所拥有不断增长的客户群的银行。这些客户主要为拥有不同规模存款的储蓄客户，而借贷客户的数量很少。银行有意扩大贷款客户群来带动借贷业务，将储蓄用户转换为贷款用户，通过贷款利息来赚取更多的利润。该银行去年为储蓄客户开展的一项活动显示，成功率超过9%。这激励了零售市场部设计活动，以便更好地瞄准市场，以最低的预算提高成功率。

该部门希望能构建数据模型来帮助他们识别愿意贷款的潜在客户。以此，能够提高客户转化率并减少活动成本。

一、 提出问题

1. 上次的活动推广效果如何？

2. 什么样的客户更容易转换为贷款用户？

3. 客户贷款情况和年龄、收入有着什么样的关系？

二、 理解数据

数据来源： https://www. kaggle.com/itsmesunil/b ank-loan-modelling

数据集大小：5000条记录，14个字段

为了方便理解，将字段翻译为中文：id、age(年龄)、experience(工作经验)、income(收入)、zipcode(邮政编码)、family(家庭人数)、ccavg(每月信用卡消费金额)、education(教育水平)、mortgage(抵押)、personal_loan(个人贷款业务)、securities_account(银行证券账户)、cd_account(银行存款账户)、online(银行网上设施)、credit_card(银行信用卡)。

这14个字段一共有四种数据类型：

数据清洗

将表格导入至MYSQL中，设置表名为loan。本表数据规整，只需要把个别数据类型修改即可。

选中表loan右击设计表，可以查看字段类型都是varchar，需要通过alter语句修改字段age、experience、income、family、mortgage的数据类型。

输入以下语句：

ALTER TABLE data CHANGE Personal_Loan Personal_Loan INTEGER;

ALTER TABLE data CHANGE Securities_Account Securities_Account INTEGER;

ALTER TABLE data CHANGE CD_Account CD_Account INTEGER;

ALTER TABLE data CHANGE Online Online INTEGER;

ALTER TABLE data CHANGE CreditCard CreditCard INTEGER;

ALTER TABLE data CHANGE Age Age INTEGER;

ALTER TABLE data CHANGE Experience Experience INTEGER;

ALTER TABLE data CHANGE Income Income INTEGER;

ALTER TABLE data CHANGE Mortgage Mortgage INTEGER;

ALTER TABLE data CHANGE Family Family INTEGER;

SHOW CREATE TABLE loan;

输入语句后运行，运行结果显示修改成功。

四、数据建模及分析

1. 活动推广结果分析

通过sql语句筛选出办理和未办理的客户数量占比

SELECT personal_loan as '办理个人贷款业务',
COUNT(personal_loan) as '数量', 
CONCAT(round((count(personal_loan)/(select count(*) FROM loan))*100,2),'%') as '占比'
FROM loan
GROUP BY personal_loan;

运行结果：

根据结果可以得出，通过上次举办的活动，5000名客户中有480名办理了个人贷款业务，占比为9.6%，说明活动推广是有效的，可以提高一定得客户转换率。

2.存款用户会转化为贷款用户的特征

(1) 家庭人口特征

输入以下sql语句：

SELECT family as '家庭人数', count(family) as '客户数量',
SUM(CASE WHEN personal_loan=1 then 1 else 0 end) as '办理贷款业务数量',
CONCAT(round(SUM(CASE WHEN personal_loan=1 then 1 else 0 end)/count(family)*100,2),'%') as '占比'
From loan 
GROUP BY family 
ORDER BY family ;

运行结果：

从结果可以看到不同家庭人数的客户办理贷款业务的转化率，家庭人数为1的客户办理贷款业务的转化率是7.27%，家庭人数为2的转化率为8.18%，家庭人数为3和4的转化率分别为13.17%和10.97%。可见，家庭人数为3-4的客户转化率大于9%，即有孩子的家庭更有意愿办理贷款业务。

(2) 教育水平特征

输入以下sql语句：

SELECT education as '受教育程度',count(education) as '客户数量',
SUM(CASE WHEN personal_loan=1 then 1 else 0 end) as '办理贷款业务数量',
CONCAT(round(SUM(CASE WHEN personal_loan=1 then 1 else 0 end)/count(education)*100,2),'%') as '占比'
From loan 
GROUP BY education 
ORDER BY education ;

运行结果：

由图可见：毕业生和职业人员的转化率为12.97%和13.66%，均高于9%。说明，教育水平越高，越能接受超前消费观念，贷款的意愿也越高。

(3) 每月信用卡使用额特征

5000名客户中，每个信用卡消费额度最低为0美元，最高为10000美元。我们将每月消费金额划分为5个区间来进行分析，分别为【0,2000），【2000,4000）,【4000,6000）,【6000,8000】。

输入以下语句：

SELECT CASE WHEN  CCAvg<2 THEN '[0,2000)'
WHEN CCAvg >=2 AND CCAvg<4 THEN '[2000,4000)'
WHEN CCAvg >=4 AND CCAvg<6 THEN '[4000,6000)' 
WHEN CCAvg >=6 AND CCAvg<8 THEN '[6000,8000)' ELSE '[8000,10000]'
END AS '银行卡消费区间',
count(CCAvg) AS '人数',
SUM(CASE WHEN personal_loan=1 then 1 else 0 end) as '办理贷款业务数量',
CONCAT(round(SUM(CASE WHEN personal_loan=1 then 1 else 0 end)/count(CCAvg) * 100,2),'%')as '占比'
From loan
GROUP BY CASE WHEN  CCAvg<2 THEN '[0,2000)' 
WHEN CCAvg >=2 AND CCAvg<4 THEN '[2000,4000)' 
WHEN CCAvg >=4 AND CCAvg<6 THEN '[4000,6000)' 
WHEN CCAvg >=6 AND CCAvg<8 THEN '[6000,8000)' ELSE '[8000,10000]'
ORDER BY CCAvg;

得到运行结果：

由图可知，信用卡消费在4000美元以上的贷款意愿越高。

(4) 是否有抵押特征

输入以下sql语句：

select count(Mortgage) AS '有抵押总人数',
sum(case when personal_loan=1 then 1 else 0 end) AS '办理贷款业务数量', 
CONCAT(ROUND(sum(case when personal_loan=1 then 1 else 0 end)/count(Mortgage)*100,2),'%') AS '占比' 
From loan where Mortgage<>0;

运行结果：

无抵押运行结果：

这两个结果相差无几故是否有抵押无法作为影响客户贷款意愿的指标。

3. 款情况和年龄、收入有着什么样的关系

(1) 随着年龄的增长，贷款情况如何

将年龄分区间，此处按10岁分一个区间，根据筛选的结果知道年龄的最小值为26，最大值为65。据此，将年龄分为以下几个区间：[20,30),[30,40),[40,50),[50,60),[60,70)。

输入以下sql语句：

select case when age >=20 and age <30 then '20~30'
when age >=30 and age <40 then '30~40'
when age >=40 and age <50 then '40~50'
when age >=50 and age <60 then '50~60'
else '>=60' 
end AS '年龄阶段',
count(*) from loan
WHERE personal_loan=1
group by case when age >=20 and age <30 then '20~30'
when age >=30 and age <40 then '30~40'
when age >=40 and age <50 then '40~50'
when age >=50 and age <60 then '50~60'
else '>=60' 
order by count(*);

运行结果：

从上图可以看出30-60之间，贷款用户所占比列最高，达到76%。过了60岁后，贷款需要则会开始下降。

(2) 贷款随着收入增加的变化情况:

输入以下sql语句：

select case when Income >=60 and Income <100 then '60000~100000'
when Income >=100 and Income <140 then '100000~140000'
when Income >=140 and Income <180 then '140000~1800000'
else '>=180000' 
end AS '年收入区间层',
count(*) AS '数量'
from loan
WHERE personal_loan=1