案例背景
借貸業(yè)務(wù)是大多數(shù)銀行重要的資金來(lái)源肢娘，通過(guò)資金的流動(dòng)賺取利潤(rùn)，例如將吸引用戶辦理定期存款，將存款轉(zhuǎn)化為貸款出借給需求方賺取利息作為利潤(rùn)韭邓。提高存貸款流動(dòng)性即為銀行開展業(yè)務(wù)的基本邏輯。
本案例中的銀行為Thera Bank祠汇，該銀行大多數(shù)業(yè)務(wù)為儲(chǔ)蓄業(yè)務(wù)仍秤，為了將存款用戶更多地轉(zhuǎn)化為貸款客戶，銀行去年為存量客戶開展了一項(xiàng)推廣活動(dòng)可很，有部分客戶增加了貸款業(yè)務(wù)诗力。本數(shù)據(jù)集記錄的就是參與了此次活動(dòng)的用戶信息
該類分析能夠幫助零售營(yíng)銷部門制定精準(zhǔn)營(yíng)銷策略，以盡可能少的資源觸達(dá)客戶提高獲客成功率。該部門希望識(shí)別出更有可能購(gòu)買貸款的潛在客戶苇本，提高轉(zhuǎn)化成功率袜茧，同時(shí)降低廣告費(fèi)用。
使用mysql讀取csv數(shù)據(jù)
READ DATA LOCAL INFILE 'E:\bank_personal_loan_modelling.csv' INTO TABLE bank.loan FIELDS TERMINATED BY ',';
Q1.本次推廣活動(dòng)的效果如何
SELECT
personal_loan,
COUNT(personal_loan) AS Count,
CONCAT(ROUND(COUNT(personal_loan)/(SELECT COUNT(*) FROM loan)*100,1),'%') AS Percentage
FROM loan
GROUP BY personal_loan;
在對(duì)于參與本次活動(dòng)的5000名用戶中瓣窄，共有480名用戶被轉(zhuǎn)化為貸款對(duì)象笛厦，占整體的9.6%，接下來(lái)的分析都圍繞著這480名貸款用戶展開
Q2.申請(qǐng)貸款的用戶年齡分布是什么樣的
SELECT MIN(age), MAX(age) FROM loan; # Check the upper bound and lower bound of age
ALTER TABLE loan ADD COLUMN age_group VARCHAR(255); # adding column
UPDATE loan SET age_group = CASE # binning
WHEN age >=20 AND Age <=29 THEN '20-30'
WHEN Age >=30 AND Age <=39 THEN '30-40'
WHEN Age >=40 AND Age <=49 THEN '40-50'
WHEN Age >=50 AND Age <=59 THEN '50-60'
else '60-70'
END;
SELECT
age_group AS Age_Group,
COUNT(personal_loan) AS Counts,
CONCAT(ROUND(COUNT(personal_loan)/(SELECT COUNT(*) FROM loan WHERE personal_loan = 1)*100,1),'%') AS Percentage
FROM loan
WHERE personal_loan = 1
GROUP BY age_group WITH ROLLUP;
在這里為了增強(qiáng)查詢語(yǔ)句的可讀性俺夕，首先通過(guò)ALTER和UPDATE語(yǔ)句增加一欄對(duì)年齡進(jìn)行分組裳凸，而后展開分析。
不難看出30-60歲這一區(qū)間占據(jù)了貸款人數(shù)的70%以上,這部分人群收入穩(wěn)定償付能力較強(qiáng)
Q3. 貸款用戶的收入狀況
SELECT MAX(income), MIN(income) FROM loan; # Check the lower and upper bound of income
ALTER TABLE loan ADD COLUMN income_group VARCHAR(255);
UPDATE loan SET income_group = 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-180000'
ELSE '>=180000'
END;
SELECT
income_group AS Income_group,
COUNT(personal_loan) AS Counts,
CONCAT(ROUND(COUNT(personal_loan)/(SELECT COUNT(*) FROM loan WHERE personal_loan = 1)*100,1),'%') AS Percentage
FROM loan
WHERE personal_loan = 1
GROUP BY income_group WITH ROLLUP;
貸款客戶只要集中在100,000-180,000之間劝贸，收入較低的用戶可能沒達(dá)到授信標(biāo)準(zhǔn)姨谷，而收入較高的群體可能不需要個(gè)人貸款
Q4.貸款用戶的家庭人口特征如何
SELECT MIN(family), MAX(family) FROM loan;
SELECT
family AS Family_size,
COUNT(*) AS Counts,
SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END) AS Personal_loan_counts,
ROUND((SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END)/COUNT(*)*100),1) AS Percentage
FROM loan
WHERE family = 1
UNION
SELECT
family AS Family_size,
COUNT(*) AS Counts,
SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END) AS Personal_loan_counts,
ROUND((SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END)/COUNT(*)*100),1) AS Percentage
FROM loan
WHERE family = 2
UNION
SELECT
family AS Family_size,
COUNT(*) AS Counts,
SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END) AS Personal_loan_counts,
ROUND((SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END)/COUNT(*)*100),1) AS Percentage
FROM loan
WHERE family = 3
UNION
SELECT
family AS Family_size,
COUNT(*) AS Counts,
SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END) AS Personal_loan_counts,
ROUND((SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END)/COUNT(*)*100),1) AS Percentage
FROM loan
WHERE family = 4;
這里通過(guò)UNION語(yǔ)句連接多個(gè)列數(shù)相同的行記錄
總體來(lái)看，5000名客戶中單身人士稍多一些映九，但組間差距不是太大梦湘。3口和4口之家的貸款人數(shù)比例較高，可能是為解決平時(shí)生活開支而申請(qǐng)
Q5.每月信用卡消費(fèi)情況
SELECT MIN(ccavg), MAX(ccavg) FROM loan;
ALTER TABLE loan ADD COLUMN spending_group VARCHAR(255);
UPDATE loan SET spending_group = CASE
WHEN CCAvg >=0 AND CCAvg <1 THEN'0-1000'
WHEN CCAvg >=1 AND CCAvg <2 THEN'1000-2000'
WHEN CCAvg >=2 AND CCAvg <3 THEN'2000-3000'
WHEN CCAvg >=3 AND CCAvg <4 THEN'3000-4000'
WHEN CCAvg >=4 AND CCAvg <5 THEN'4000-5000'
WHEN CCAvg >=5 AND CCAvg <6 THEN'5000-6000'
WHEN CCAvg >=6 AND CCAvg <7 THEN'6000-7000'
WHEN CCAvg >=7 AND CCAvg <8 THEN'7000-8000'
WHEN CCAvg >=8 AND CCAvg <9 THEN'8000-9000'
ELSE '9000-10000'
END;
SELECT
spending_group,
COUNT(*) AS Counts,
SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END) AS Personal_loan_counts,
CONCAT(ROUND((SUM(CASE WHEN personal_loan=1 THEN 1 ELSE 0 END)/COUNT(*)*100),2),'%') AS Percentage
FROM loan
GROUP BY spending_group;
信用卡消費(fèi)額在5000元以上的貸款意愿更為強(qiáng)烈
Q6. 貸款用戶是否原本有房屋在抵押
SELECT
(SELECT COUNT(*) FROM loan WHERE personal_loan=1) AS Personal_loan,
SUM(CASE WHEN mortgage > 0 THEN 1 ELSE 0 END) AS Mortage_count,
CONCAT(ROUND(SUM(CASE WHEN mortgage > 0 THEN 1 ELSE 0 END)/(SELECT COUNT(*) FROM loan WHERE personal_loan=1) *100,1),'%') AS Mortgage_percentage,
MAX(mortgage)*1000 AS MAX_mortgage,
ROUND(AVG(mortgage)*1000) AS AVG_mortgage
FROM loan
WHERE personal_loan = 1 AND mortgage <> 0;
在貸款用戶中件甥，有房屋抵押的占35%捌议，最大抵押價(jià)值617000，均值為288131
Q7. 根據(jù)房屋抵押價(jià)值對(duì)用戶分類
SELECT MIN(mortgage), MAX(mortgage) FROM loan;
ALTER TABLE loan ADD COLUMN mortgage_group VARCHAR(255);
UPDATE loan SET mortgage_group = CASE
WHEN mortgage = 0 THEN 0
WHEN mortgage >=1 AND mortgage <100 THEN '1000-100000'
WHEN mortgage >=100 AND mortgage <200 THEN '100000*200000'
WHEN mortgage >=200 AND mortgage <300 THEN '200000-300000'
WHEN mortgage >=300 AND mortgage <400 THEN '300000-400000'
WHEN mortgage >= 400 AND mortgage <500 THEN '400000-500000'
WHEN mortgage >= 500 AND mortgage <600 THEN '500000-600000'
ELSE '>600000'
END;
SELECT
mortgage_group,
COUNT(*) AS Mortgage_count,
SUM(personal_loan) Personal_loan_count,
CONCAT(ROUND(SUM(personal_loan)/COUNT(*)*100,1),'%') AS percentage
FROM loan
GROUP BY mortgage_group;
在已經(jīng)辦理了房屋抵押的用戶中引有，房屋抵押價(jià)值在300,000以上的辦理個(gè)人貸款的意愿更高
