本科毕业论文---基于数据挖掘的国际贸易客户流失的预测分析.doc

基于数据挖掘的国际贸易客户流失的预测分析
目 录
1 前言………………………………………………………………………………2
1.1 设计背景………………………………………………………………………2
1.2 数据挖掘简介…………………………………………………………………2
1.3 决策树ID3和C4.5算法简介……………………………………………… 3
2 客户数据训练集的预处理………………………………………………………3
2.1 选择训练集……………………………………………………………………4
2.2 去除训练集的冗余数据………………………………………………………4
2.3 训练集中连续值的离散化……………………………………………………4
3 结合信息增益和信息增益率进行决策树归纳…………………………………6
3.1计算对D中元组分类所需的期望信息………………………………………6
3.2计算每个属性的期望信息需求（即信息熵）………………………………7
3.3 计算每个属性的信息增益率………………………………………………14
4 构建决策树……………………………………………………………………16
4.1 构建根节点决策树…………………………………………………………16
4.2 构建最终决策树……………………………………………………………17
5 客户群流失的预测和分析……………………………………………………17
6 总结……………………………………………………………………………18
结束语……………………………………………………………………………18
致谢………………………………………………………………………………18
参考文献…………………………………………………………………………18
附录………………………………………………………………………………19
基于数据挖掘的国际贸易客户流失的预测分析

摘要：企业客户流失是一个企业最大的损失，但大部分企业并未找到一个切实有效的方法来解决这个问题，文章从数据挖掘角度，利用关联规则对国际贸易客户流失的预测进行一些分析，找出流失率比较高的并有价值的客户群体的特点，为企业提供一些具体策略来减少客户流失，最终获得较大的经济效益！
关键词：数据挖掘 客户流失 决策树 ID3算法 c4.5算法 离散化
Abstract：Loss of business customers are the biggest loss of an enterprise, but most enterprises have Nt found an effective way to solve this problem, data mining article from the point of view, the use of association rules on international trade the loss of customers to conduct some analysis of the prediction to identify the loss of relatively high rate and valuable characteristics of client groups, providing a number of specific strategies to reduce the loss of customers, and ultimately the ecoNmic benefits of a larger.
Keywords: Data Mining,The loss of customers, Decision Tree, ID3 algorithm,c4.5 algorithm, Discretization
2

1 前言
1.1 设计背景
随着中国加入世贸组织，互联网信息化高速发展带来的机遇，有着越来越多的企业或者个人）参加到国际贸易当中并从中获利，在营销和推广的手段的多元化的同时，客户群成了一个非常不稳定的群体，商人无非重视的是个利字，那么这些客户群很有可能会流向竞争对手那里，那么如何提升公司客户群的忠诚度就成为了首要解决的问题，为了满足外贸企业和外贸soho对客户流的需求以及掌控，由于数据量的复杂，也就非常有必要开发一个基于数据挖掘的国际贸易客户流失数据预测处理系统来来管理客户数据和预防客户的流失，这样不仅可以及时检测到客户的流失动向和原因，而且也为外贸企业外贸soho提供了更加快捷的方式来尽量减少客户端流失。
1.2 数据挖掘简介
数据挖掘(data mining)是从大量的、不完全的、有噪声的、模糊的、随机的数据中提取隐含在其中的、人们事先不知道的、但又是潜在有用的信息和知识的过程。随着信息技术的高速发展，人们积累的数据量急剧增长，动辄以tb计，如何从海量的数据中提取有用的知识成为当务之急。数据挖掘就是为顺应这种需要应运而生发展起来的数据处理技术，是知识发现(kNwledge discovery in database)的关键步骤.
1.3 决策树ID3与C4.5算法简介
ID3算法是J.Ross Quinlan开发的一种数据挖掘[1]决策树算法[2]【3】，后来C4.5（ID3的后继），成为新的监督学习算法，两者都采用贪心非回溯的方法，其中决策树以自顶向下递归的分治方法，从训练元祖集和它们的相关联的类标号开始构造决策树，随着树的构建，训练集递归地划分成较小的子集，并分别引进信息增益和信息增益率来进行构造决策树。
2 客户数据训练集的预处理
由于数据挖掘的对象是大量的数据，非常的庞乱繁杂，所以非常有必要将这些数据进行预处理[4] [5]，以适用于挖掘，这需要三个步骤
2.1选择训练集
将不相关的数据剔除掉，只保留一些与挖掘有关的，比如姓名 电话号码，地址，取值太多，如果进行概化，将大于概化阀值，所以予以删除，还有对以前输入的数据不完整的，不规范的，也不予以选取，将予以剔除，还有一些属性关联比较密切的[6]，可以删除个别，只留下代表属性。
2.2 去除训练集的冗余数据
从逻辑上判断一些属性对训练集类属性结果不相关的，还有一些属性关联比较密切的，可以删除掉一个，从而极大的提高计算的效率和准确度，因为一些不相关的属性可能导致数据挖掘的准确度下降，以表一为例，成功率基本上都大于90%，但是跟数据挖掘的结果没有必然联系，将可能导致数据挖掘的准确度下降比如交易次数，交易总额，所以予以删除，另外支付宝担保交易付款和银行转帐付款关联性大于90%,将支付宝担保交易属性也予以删除。
2.3 训练集中连续值的离散化
将连续的数值进行离散化处理，如年龄分为三类：
A1:<20 A2: 20—30 A3 : >30
可以将订购款式分为四类，
B1:板鞋 B2: 帆布鞋 B3 :跑鞋 B4 综合各种款式
属性年龄简称A 订购款式简称B
批发简称C 学生简称 D 银行汇款简称E
结合上面归纳，给出转化后的训练集表一 ，并给出相关的数据库图表，见图一，二

编号
年龄A
订购款式B
批发C
学生D
银行转帐E
流失
1
A2
B1
Y
N
Y
N
2
A2
B3
N
Y
Y
Y
3
A3
B1
N
N
Y
N
4
A1
B2
N
Y
Y
N
5
A3
B2
N
Y
N
N
6
A1
B2
N
Y
N
N
7
A3
B4
Y
N
Y
N
8
A3
B3
Y
N
Y
N
9
A1
B2
N
Y
N
Y
10
A3
B4
Y
N
Y
N
11
A2
B2
N
Y
Y
N
12
A1
B4
Y
N
Y
Y
13
A2
B2
N
Y
Y
Y
14
A1
B1
N
Y
N
N
15
A2
B1
Y
Y
Y
N
16
A3
B3
N
N
N
Y
17
A2
B3
Y
N
Y
Y
18
A1
B4
N
N
Y
Y
19
A2
B4
Y
N
Y
Y
20
A3
B1
N
N
N
Y
表一 经过预处理对客户数据训练集

图一 创建的数据库 tradedb

图二 数据库中的trade 表数据

3 结合信息增益和信息增益率进行决策树归纳
3.1计算对D中元组分类所需的期望信息
表一给出了一个类标记的元组的训练集D，每个属性都是离散值（连续值属性已经被离散化），类标号属性“是否流失”有两类，（即m=2），设类c1对应Y 类c2对应N，类C1有11个元组，类C2有9个元组，由D中的元组创建（根）节点N，为了找出这些元组的分裂准则，必须计算每个属性的信息增益， 计算对D中元组分类所需的期望信息（即信息熵）：
Info（D）= - log2（Pi）
其中Pi是D中的任意元组属于Ci 的概率，并用|Ci，D|/|D|估计，
使用vb.Net 构造的函数代码如下：
Public Function tradefuc(ByVal a, ByVal b) As String
Dim k As String
k = a + b
tradefuc = -a / k * Log(a / k) / Log(2) - b / k * Log(b / k) / Log(2)
If a = 0 Then
tradefuc = -b / k * Log(b / k) / Log(2)
End If
If b = 0 Then
tradefuc = -a / k * Log(a / k) / Log(2)
End If
End Function
计算对D中元组分类所需的期望信息：
Info（D）= - log2（Pi）=0.992774453987808
源代码如下
'客户流失
Dim t1 As Double
Dim sCon1 As String = "Select Count(*) From trade where 流失= 'Y'"
sCom.Connection = sCon
sCon.Open()
sCom.CommandText = sCon1
t1 = sCom.ExecuteScalar
Dim t2 As Double
Dim sCon2 As String = "Select Count(*) From trade where 流失= 'N'"
sCom.Connection = sCon
sCom.CommandText = sCon2
t2 = sCom.ExecuteScalar
'计算训练集D中元组分类的期望信息
Dim I As Double
I = tradefuc(t1, t2)
计算如图三

图三 计算Info（D）的值
3.2 计算每个属性的期望信息需求（即信息熵）：
从属性age开始，查询age各个属性对应的流失是否个数，并计算它的信息增益
Info年龄（D）=0.00196975696587331
'年龄
Dim age1 As Double
Dim ageCon1 As String
ageCon1 = "Select Count(*) from trade where 年龄='A1' "
sCom.Connection = sCon
sCom.CommandText = ageCon1
age1 = sCom.ExecuteScalar

Dim age11 As Double
Dim ageCon11 As String
ageCon11 = "Select Count(*) from trade where 年龄='A1' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = ageCon11
age11 = sCom.ExecuteScalar

Dim age12 As Double
Dim ageCon12 As String
ageCon12 = "Select Count(*) from trade where 年龄='A1' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = ageCon12
age12 = sCom.ExecuteScalar

Dim age2 As Double
Dim ageCon2 As String
ageCon2 = "Select Count(*) from trade where 年龄='A2' "
sCom.Connection = sCon
sCom.CommandText = ageCon2
age2 = sCom.ExecuteScalar

Dim age21 As Double
Dim ageCon21 As String
ageCon21 = "Select Count(*) from trade where 年龄='A2' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = ageCon21
age21 = sCom.ExecuteScalar

Dim age22 As Double
Dim ageCon22 As String
ageCon22 = "Select Count(*) from trade where 年龄='A2' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = ageCon22
age22 = sCom.ExecuteScalar

Dim age3 As Double
Dim ageCon3 As String
ageCon3 = "Select Count(*) from trade where 年龄='A3' "
sCom.Connection = sCon
sCom.CommandText = ageCon3
age3 = sCom.ExecuteScalar

Dim age31 As Double
Dim ageCon31 As String
ageCon31 = "Select Count(*) from trade where 年龄='A3' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = ageCon31
age31 = sCom.ExecuteScalar
Dim age32 As Double
Dim ageCon32 As String
ageCon32 = "Select Count(*) from trade where 年龄='A3' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = ageCon32
age32 = sCom.ExecuteScalar

'计算年龄的信息增益
Dim Iage1 As Double
Iage1 = tradefuc(age11, age12)
Dim Iage2 As Double
Iage2 = tradefuc(age21, age22)
Dim Iage3 As Double
Iage3 = tradefuc(age31, age32)
Dim Iage As Double
Iage = age1 / t * Iage1 + age2 / t * Iage2 + age3 / t * Iage3
Dim Gainage As Double
Gainage = I - Iage

类似的，Gain（订购款式）的代码如下

'订购款式
Dim dg1 As Double
Dim dgCon1 As String
dgCon1 = "Select Count(*) from trade where 订购款式='B1' "
sCom.Connection = sCon
sCom.CommandText = dgCon1
dg1 = sCom.ExecuteScalar

Dim dg11 As Double
Dim dgCon11 As String
dgCon11 = "Select Count(*) from trade where 订购款式='B1' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = dgCon11
dg11 = sCom.ExecuteScalar

Dim dg12 As Double
Dim dgCon12 As String
dgCon12 = "Select Count(*) from trade where 订购款式='B1' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = dgCon12
dg12 = sCom.ExecuteScalar

Dim dg2 As Double
Dim dgCon2 As String
dgCon2 = "Select Count(*) from trade where 订购款式='B2' "
sCom.Connection = sCon
sCom.CommandText = dgCon2
age2 = sCom.ExecuteScalar

Dim dg21 As Double
Dim dgCon21 As String
dgCon21 = "Select Count(*) from trade where 订购款式='B2' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = dgCon21
dg21 = sCom.ExecuteScalar

Dim dg22 As Double
Dim dgCon22 As String
dgCon22 = "Select Count(*) from trade where 订购款式='B2' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = dgCon22
dg22 = sCom.ExecuteScalar

Dim dg3 As Double
Dim dgCon3 As String
dgCon3 = "Select Count(*) from trade where 订购款式='B3' "
sCom.Connection = sCon
sCom.CommandText = dgCon3
dg3 = sCom.ExecuteScalar

Dim dg31 As Double
Dim dgCon31 As String
dgCon31 = "Select Count(*) from trade where 订购款式='B3' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = dgCon31
dg31 = sCom.ExecuteScalar

Dim dg32 As Double
Dim dgCon32 As String
dgCon32 = "Select Count(*) from trade where 订购款式='B3' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = dgCon32
dg32 = sCom.ExecuteScalar

Dim dg4 As Double
Dim dgCon4 As String
dgCon4 = "Select Count(*) from trade where 订购款式='B4' "
sCom.Connection = sCon
sCom.CommandText = dgCon4
dg4 = sCom.ExecuteScalar

Dim dg41 As Double
Dim dgCon41 As String
dgCon41 = "Select Count(*) from trade where 订购款式='B4' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = dgCon41
dg41 = sCom.ExecuteScalar

Dim dg42 As Double
Dim dgCon42 As String
dgCon42 = "Select Count(*) from trade where 订购款式='B4' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = dgCon42
dg42 = sCom.ExecuteScalar

'计算订购款式的信息增益

Dim Idg1 As Double
Idg1 = tradefuc(dg11, dg12)
Dim Idg2 As Double
Idg2 = tradefuc(dg21, dg22)
Dim Idg3 As Double
Idg3 = tradefuc(dg31, dg32)
Dim Idg4 As Double
Idg4 = tradefuc(dg41, dg42)
Dim Idg As Double
Idg = age1 / t * Idg1 + dg2 / t * Idg2 + dg3 / t * Idg3 + dg4 / t * Idg4
Dim Gaindg As Double
Gaindg = I - Idg

Gain（批发）的源代码如下
'批发
Dim pifa1 As Double
Dim pifaCon1 As String
pifaCon1 = "Select Count(*) from trade where 批发='Y' "
sCom.Connection = sCon
sCom.CommandText = pifaCon1
pifa1 = sCom.ExecuteScalar

Dim pifa11 As Double
Dim pifaCon11 As String
pifaCon11 = "Select Count(*) from trade where 批发='Y' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = pifaCon11
pifa11 = sCom.ExecuteScalar

Dim pifa12 As Double
Dim pifaCon12 As String
pifaCon12 = "Select Count(*) from trade where 批发='Y' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = pifaCon12
pifa12 = sCom.ExecuteScalar

Dim pifa2 As Double
Dim pifaCon2 As String
pifaCon2 = "Select Count(*) from trade where 批发='N' "
sCom.Connection = sCon
sCom.CommandText = pifaCon2
pifa2 = sCom.ExecuteScalar

Dim pifa21 As Double
Dim pifaCon21 As String
pifaCon21 = "Select Count(*) from trade where 批发='N' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = pifaCon21
pifa21 = sCom.ExecuteScalar

Dim pifa22 As Double
Dim pifaCon22 As String
pifaCon22 = "Select Count(*) from trade where 批发='N' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = pifaCon22
pifa22 = sCom.ExecuteScalar

'计算批发的信息增益

Dim Ipifa1 As Double
Ipifa1 = tradefuc(pifa11, pifa12)
Dim Ipifa2 As Double
Ipifa2 = tradefuc(pifa21, pifa22)

Dim Ipifa As Double
Ipifa = pifa1 / t * Ipifa1 + pifa2 / t * Ipifa2
Dim Gainpifa As Double
Gainpifa = I - Ipifa
Gain（学生）的源代码如下
'学生
Dim student1 As Double
Dim studentCon1 As String
studentCon1 = "Select Count(*) from trade where 学生='Y' "
sCom.Connection = sCon
sCom.CommandText = studentCon1
student1 = sCom.ExecuteScalar

Dim student11 As Double
Dim studentCon11 As String
studentCon11 = "Select Count(*) from trade where 学生='Y' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = studentCon11
student11 = sCom.ExecuteScalar

Dim student12 As Double
Dim studentCon12 As String
studentCon12 = "Select Count(*) from trade where 学生='Y' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = studentCon12
student12 = sCom.ExecuteScalar

Dim student2 As Double
Dim studentCon2 As String
studentCon2 = "Select Count(*) from trade where 学生='N' "
sCom.Connection = sCon
sCom.CommandText = studentCon2
student2 = sCom.ExecuteScalar

Dim student21 As Double
Dim studentCon21 As String
studentCon21 = "Select Count(*) from trade where 学生='N' and 流失='Y' "
sCom.Connection = sCon
sCom.CommandText = studentCon21
student21 = sCom.ExecuteScalar

Dim student22 As Double
Dim studentCon22 As String
studentCon22 = "Select Count(*) from trade where 学生='N' and 流失='N' "
sCom.Connection = sCon
sCom.CommandText = studentCon22
student22 = sCom.ExecuteScalar

'计算学生的信息增益

Dim Istudent1 As Double
Istudent1 = tradefuc(student11, student12)
Dim Istudent2 As Double
Istudent2 = tradefuc(student21, student22)

Dim Istudent As Double
Istudent = student1 / t * Istudent1 + student2 / t * Istudent2
Dim Gainstudent As Double
Gainstudent = I - Istudent

Gain（银行转帐）的源代码实现
'支付方式--银行转帐
Dim bank1 As Double
Dim bankCon1 As String
bankCon1 = "Select Count(*) from trade where 银行='Y' "
sCom.Connection = sCon
sCom.CommandText = bankCon1
bank1 = sCom.ExecuteScalar

Dim bank11 As Double
Dim bankCon11 As String
bankCon11 = "Select Count(*) from trade where 银行='Y' and 流失='Y' "
s