【数据挖掘】中小企业代理会计识别模型

浏览: 1527

1.背景与目标

    目前企业用户中有大量的中小型企业受制于人员和成本压力并没有专职的会计人员,代理记账公司或者兼职会计成为首要选择。企业客户是公司的主要客户,而企业客户中有大量的企业选择代理会计,而选择代理会计后企业就很少缴纳服务费,造成公司盈利性收支减少;同时针对代理会计,公司财务软件销售人员可对目标人群进行宣传和产品推广,提高财务软件销售收入和交易达成率。

Image1.png

     分析过程中两个关键分析点为:①源数据清洗过程中采用四分位法找出噪点数据的特征,主要根据登录IP筛选②建模分析过程主要使用到了相关性分析来降维和朴素贝叶斯模型验证

2.数据来源

     目前针对代理会计模型,潜在的可用数据有:

     用户基本信息表:KHGL_JBXX  

     数据字典:KHBH 客户编号,KHMC 客户名称,KHSH 客户税号,LXRDH 联系人电话

     Image2.png

     一体化客户端登录信息表:SY_YTH_CLIENT_USERINFO

     数据字典:KHSH 操作客户税号,KHSHS 历史税号集,CREATETIME 登录时间,CLIENTIP 登录IP

     Image3.png

     来电记录表:FWGL_TEL_RECORD

     数据字典:KHBH 客户编号,KHSH 客户税号,GDDH 固定电话,YDDH 移动电话,LDHM 来电号码,LDSJ 来电时间

     Image4.png

3.数据处理

     在数据预处理过程中,对一些严重影响数据质量的噪点数据进行排除,对不适合部分字段进行转换。通过数据清洗、异常值处理、数据过滤、数据规范等一系列处理得到更有助于分析和预测的数据。

     预处理过程如下:

     ①、数据清洗:先去重日志信息,再排除掉税局大厅和公司服务站电脑登录产生的一体化日志信息;排除掉来电记录中来电号码为空的记录;数据取一个申报周期(3个月)

create EXTERNAL table sy_yth_client_userinfo_distinct(khsh string,khshs string,createtime timestamp,clientip string)
insert overwrite table sy_yth_client_userinfo_distinct
select s.khsh,s.khshs,s.createtime,s.clientip from (select  t.khsh,t.khshs,t.createtime,t.clientip,row_number() over(partition by t.khsh,t.khshs,t.clientip,cast(t.createtime as date)) as ranks
from sy_yth_client_userinfo t where t.khsh<>'null' and t.khsh<>t.khshs and t.khshs<>'null'
and cast(t.createtime as date) between
       cast('2016-09-01' as date) and
       cast('2016-11-30' as date)) s where s.ranks=1
create EXTERNAL table sy_yth_client_ip_counts(clientip string,days int,counts int,avgcount float)
insert overwrite table sy_yth_client_ip_counts
select ss.clientip,count(ss.ddate) as days,sum(ss.counts) as counts,sum(ss.counts)/count(ss.ddate) as avgcount from (
select a.clientip,cast(a.createtime as date) as ddate,count(distinct a.khsh) as counts
  from sy_yth_client_userinfo_distinct  a
 group by a.clientip,cast(a.createtime as date)
 ) ss group by ss.clientip
create EXTERNAL table sy_yth_client_ip_dcounts(clientip string,d_counts int)
insert overwrite table sy_yth_client_ip_dcounts
select  t.clientip,count(distinct t.khsh) as d_counts
from sy_yth_client_userinfo t where t.khsh<>'null' and t.khsh<>t.khshs and t.khshs<>'null'
and cast(t.createtime as date) between
       cast('2016-09-01' as date) and
       cast('2016-11-30' as date)
       group by t.clientip
create EXTERNAL table sy_yth_client_ip_prop(clientip string,ftimes int,ttimes int,prop float)
insert overwrite table sy_yth_client_ip_prop
select s.clientip,
sum(case when if((s.dhour<8 and s.dminute<0) or (s.dhour>17 and s.dminute>30),'F','T')=='F' then 1 else 0 end) ftimes,
sum(case when if((s.dhour<8 and s.dminute<0) or (s.dhour>17 and s.dminute>30),'F','T')=='T' then 1 else 0 end) ttimes,
round(sum(case when if((s.dhour<8 and s.dminute<0) or (s.dhour>17 and s.dminute>30),'F','T')=='F' then 1 else 0 end)/(
sum(case when if((s.dhour<8 and s.dminute<0) or (s.dhour>17 and s.dminute>30),'F','T')=='T' then 1 else 0 end) +
sum(case when if((s.dhour<8 and s.dminute<0) or (s.dhour>17 and s.dminute>30),'F','T')=='F' then 1 else 0 end) ),4) prop  from
(select t.*,hour(cast(t.createtime as string)) dhour,minute(cast(t.createtime as string)) dminute from sy_yth_client_userinfo_distinct t) s
group by s.clientip
select t1.clientip,t1.days,t1.counts,t1.avgcount,t2.d_counts,t3.ftimes,t3.ttimes,t3.prop from sy_yth_client_ip_counts t1 left join sy_yth_client_ip_dcounts t2 on t1.clientip=t2.clientip left join sy_yth_client_ip_prop t3 on t1.clientip=t3.clientip

R语言函数summary()得到相关统计值:

>  setwd("E://R")
〉 getwd()
> data1=read.csv("hivedata.csv",header=F)
> summary(data1[,2])

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's

  1.000   1.000   1.000   1.857   2.000  68.000     426

> summary(data1[,4])

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's

  1.000   1.000   1.000   2.494   2.000 869.100     426

> summary(data1[,5])

     Min.   1st Qu.    Median      Mean   3rd Qu.      Max.      NA's

    1.000     1.000     1.000     4.343     3.000 12500.000       426

根据业务了解到,为筛选出税务大厅电脑和服务站电脑登录的记录,极值:x>上四分位+3IQR

得到需要排除的IP为:登录天数days>5, 平均每天登录税号个数avgcounts>5, 登录不同的税号数d_counts>6,非工作时间登录次数ftimes=0

清洗结果1、一体化客户端登录信息中间表sy_yth_client_userinfo_mid

create EXTERNAL table sy_yth_client_userinfo_mid(khsh string,khshs string,createtime timestamp,clientip string,worktime string);
insert overwrite table sy_yth_client_userinfo_mid
select tt.khsh,tt.khshs,tt.createtime,tt.clientip,if((hour(cast(tt.createtime as string))<8 and minute(cast(tt.createtime as string))<0) or (hour(cast(tt.createtime as string))>17 and minute(cast(tt.createtime as string))>30),'F','T') as  worktime from (
select t.khsh,t.khshs,t.createtime,t.clientip,row_number() over(partition by t.khsh,t.khshs,t.clientip,cast(t.createtime as date)) as ranks from  sy_yth_client_userinfo t where not exists(select 1 from
(select t1.clientip,t1.days,t1.counts,t1.avgcount,t2.d_counts,t3.ftimes,t3.ttimes,t3.prop from sy_yth_client_ip_counts t1 left join sy_yth_client_ip_dcounts t2 on t1.clientip=t2.clientip left join sy_yth_client_ip_prop t3 on t1.clientip=t3.clientip) s where s.clientip=t.clientip
and s.days>5 and s.avgcount>5 and s.d_counts>6 and s.ftimes=0
) and cast(t.createtime as date) between
       cast('2016-09-01' as date) and
       cast('2016-11-30' as date)
       and t.khsh<>'null' and t.khshs<>'null' and t.khsh<>t.khshs) tt where tt.ranks=1

     ②、数据转换:一体化客户端登陆时间并没有直接的挖掘价值,需要转换成是否在工作时间登陆维度;

     ③、本次挖掘为分类任务,需要区分训练数据集和验证数据集,根据前期处理结果得到29万左右的代理会计与公司全部94万客户的占比约为1:3,所以在代理会计结果数据集和原始样本数据中分别随机抽取10万和30万数据作为验证数据,其他数据为训练数据。

     29万代理会计:

select s.sh,s.nums from
(select t.sh,t.nums,row_number() over(partition by t.sh order by t.nums desc) num from sy_yth_client_dlkj_all t where t.sh<>'') s where s.num=1 and s.nums>1 order by s.nums desc

     根据已有的数据资源和代理会计的特征进行分析,从三个大维度即:客户端登录独立性、来电号码独立性、联系人独立性来细分客户,标识出由代理会计代理记账的公司可信度。根据需求变量和数据处理后,筛选出几个更加细化的维度作为判断是否代理会计的核心维度,再对数据进行标准化,并采用朴素贝叶斯模型进行分析。

     其中核心维度指标及描述如下:

     ①、NUMS  关联税号个数,通过税号集关联出的不同税号个数,去掉自己    

select t.mkhsh khsh,count(t.sh) nums from sy_yth_client_khsh2khsh_m t group by t.mkhsh

     ②、LXR_NUM 共用联系人税号个数,同一个联系人电话对应的税号个数 

 select t.lxrdh,count(t.khsh) lxr_num from khgl_jbxx t group by t.lxrdh

     ③、TIMES 出现在税号集中的次数(被关联出的次数)     

select t.sh khsh,count(t.sh) times from sy_yth_client_khsh2khsh_m t group by t.sh

     ④、MEMORY_SUM 登录过的电脑内存之和     

     ⑤、TEL_TIMES 电话记录数

 select t.khbh,count(1) tel_times from fwgl_tel_record t group by t.khbh          

     ⑥、TEL_NUM 来电号码个数     

select t.khbh,count(distinct t.ldhm) tel_num from fwgl_tel_record t group by t.khbh

     ⑦、TEL_KHSHS来电号码咨询过的税号总数

create EXTERNAL table sy_index_dlkj_tel(khbh string,tel_khshs int);
insert overwrite table sy_index_dlkj_tel
select tt.khbh,sum(s.tel_num) tel_khshs from (select distinct t.khbh,t.ldhm from fwgl_tel_record t) tt left join (select t.ldhm,count(distinct t.khbh) tel_num from fwgl_tel_record t group by t.ldhm) s on tt.ldhm=s.ldhm group by tt.khbh

     ⑧、TIMES_WORKTIME工作时间登录次数    

     ⑨、TIMES_NOWORKTIME 非工作时间登录次数

select t.khsh,sum(case when t.worktime='T' then 1 else 0 end ) TIMES_WORKTIME,sum(case when t.worktime='T' then 0 else 1 end ) TIMES_NOWORKTIME from sy_yth_client_userinfo_mid t group by t.khsh

     ⑩、IS_AGENT是否代理会计

维度指标合并后的数据:

create EXTERNAL table sy_index_dlkj(khbh string,khsh string,lxrdh string,nums int,lxr_num int,times int,tel_times int,tel_num int,times_worktime int,times_noworktime int,is_agent string);
insert overwrite table sy_index_dlkj
select a.khbh,a.khsh,a.lxrdh,s1.nums,s2.lxr_num,s3.times,s4.tel_times,s5.tel_num,s6.TIMES_WORKTIME,s6.TIMES_NOWORKTIME,if(s8.nums>1,'T','F') IS_AGENT from khgl_jbxx a
left join (select t.mkhsh khsh,count(t.sh) nums from sy_yth_client_khsh2khsh_m t group by t.mkhsh) s1 on a.khsh=s1.khsh
left join (select t.lxrdh,count(t.khsh) lxr_num from khgl_jbxx t group by t.lxrdh) s2 on a.lxrdh=s2.lxrdh
left join (select t.sh khsh,count(t.sh) times from sy_yth_client_khsh2khsh_m t group by t.sh) s3 on a.khsh=s3.khsh
left join ( select t.khbh,count(1) tel_times from fwgl_tel_record t group by t.khbh) s4 on a.khbh = s4.khbh
left join (select t.khbh,count(distinct t.ldhm) tel_num from fwgl_tel_record t group by t.khbh) s5 on a.khbh=s5.khbh
left join (select t.khsh,sum(case when t.worktime='T' then 1 else 0 end ) TIMES_WORKTIME,sum(case when t.worktime='T' then 0 else 1 end ) TIMES_NOWORKTIME from sy_yth_client_userinfo_mid t group by t.khsh) s6
on a.khsh=s6.khsh
left join (select s.sh,s.nums from
(select t.sh,t.nums,row_number() over(partition by t.sh order by t.nums desc) num from sy_yth_client_dlkj_all t where t.sh<>'') s where s.num=1 and s.nums>1 order by s.nums desc
) s8 on a.khsh=s8.sh
select t.*,s.tel_khshs from sy_index_dlkj t left join sy_index_dlkj_tel s on t.khbh=s.khbh

4.建模流程

Image9.png

(备注:由于虚拟机电脑配置问题,此图采用ethink数据挖掘案例集中的流程,非常感谢ethink官方http://www.ethinkbi.com/提供的产品体验机会)

     ①、选择数据输入,加载原始数据

     ②、相关性分析,去除相关性很低的维度,降维操作

     ③、配置是否代理会计为分析目标

     ④、配置朴素贝叶斯模型

     ⑤、配置验证数据和预测模型

     ⑥、数据结果保存到数据库

备注:该分析过程只是拿来和大家分享,模型中还存在诸多问题,比如多种分析方法的尝试,找到最优的模型;并未给出最后预测模型结果的展现。略去了代理会计确认的步骤。代码过多。


写在最后:欢迎大家一起交流与学习,文章内容若有不对,请多多指教,谢谢!

个人微信weixin.png

推荐 1
本文由 w卫东 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录注册