目录
赛题
C题 大型百货商场会员画像描绘
在零售行业中,会员价值体现在持续不断地为零售运营商带来稳定的销售额和利润,同时也为零售运营商策略的制定提供数据支持。零售行业会采取各种不同方法来吸引更多的人成为会员,并且尽可能提高会员的忠诚度。当前电商的发展使商场会员不断流失,给零售运营商带来了严重损失。此时,运营商需要有针对性地实施营销策略来加强与会员的良好关系。比如,商家针对会员采取一系列的促销活动,以此来维系会员的忠诚度。有人认为对老会员的维系成本太高,事实上,发展新会员的资金投入远比采取一定措施来维系现有会员要高。完善会员画像描绘,加强对现有会员的精细化管理,定期向其推送产品和服务,与会员建立稳定的关系是实体零售行业得以更好发展的有效途径。
附件中的数据给出了某大型百货商场会员的相关信息:附件1是会员信息数据;附件2是近几年的销售流水表;附件3是会员消费明细表;附件4是商品信息表,一般来说,商品价格越高,盈利越高;附件5是数据字典。请建立数学模型解决以下问题:
(1) 分析该商场会员的消费特征,比较会员与非会员群体的差异,并说明会员群体给商场带来的价值。
(2) 针对会员的消费情况建立能够刻画每一位会员购买力的数学模型,以便能够对每个会员的价值进行识别。
(3) 作为零售行业的重要资源,会员具有生命周期(会员从入会到退出的整个过程),会员的状态(比如活跃和非活跃)也会发生变化。试在某个时间窗口,建立会员生命周期和状态划分的数学模型,使商场管理者能够更有效地对会员进行管理。
(4) 建立数学模型计算会员生命周期中非活跃会员的激活率,即从非活跃会员转化为活跃会员的可能性,并从实际销售数据出发,确定激活率和商场促销活动之间的关系模型。
(5) 连带消费是购物中心经营的核心,如果商家将策划某次促销活动,如何根据会员的喜好和商品的连带率来策划此次促销活动?
摘要
一、问题的重述
二、模型假设
三、变量说明
![](https://img-blog.csdnimg.cn/8741739721194481af59a1d3afddfec9.png)
四、模型的建立与求解
4.1 数据预处理
4.2 问题一的模型建立与求解
4.2.1 建模思路
![](https://img-blog.csdnimg.cn/5c373af8e28d4cafa41a214d3189281c.png)
4.2.2 模型建立
![](https://img-blog.csdnimg.cn/20a4860c14f943fe801738e49460d045.png)
4.2.3 模型的求解与结果分析
![](https://img-blog.csdnimg.cn/9f380374bf87458a828d12e83baa6521.png)
![](https://img-blog.csdnimg.cn/84b0cdb2a9374eeba2ab8f58f45f67e5.png)
![](https://img-blog.csdnimg.cn/c7cebafac2d943c1802cd6ba3fd1ff2a.png)
![](https://img-blog.csdnimg.cn/47483ba0d78c4783bf8551cc40305689.png)
![](https://img-blog.csdnimg.cn/90aeef4dfb6a4e25b8ede58310fcc2d3.png)
4.3 问题二的模型建立与求解
4.3.1 建模思路
4.3.2 模型建立
![](https://img-blog.csdnimg.cn/00da536613a844e7b16d24f2df0ee513.png)
![](https://img-blog.csdnimg.cn/32fc0d261500428f84cc2ced5a7e4f96.png)
![](https://img-blog.csdnimg.cn/af9356ae3f974d128ead68392ef053e4.png)
![](https://img-blog.csdnimg.cn/7ba5fecca3b240a2ad91c08f4639946a.png)
![](https://img-blog.csdnimg.cn/06235e904b2747b98edda7b4e42022c0.png)
![](https://img-blog.csdnimg.cn/777a4cfc1a084edc947d01ec9a76983f.png)
![](https://img-blog.csdnimg.cn/29257bb92fe64749b23be17056ea0907.png)
![](https://img-blog.csdnimg.cn/7bb88db2e890498fa2fee0e1a054780e.png)
4.3.3 模型的求解与结果分析
![](https://img-blog.csdnimg.cn/7cfc0206a1814ba481706d22c4eebc17.png)
![](https://img-blog.csdnimg.cn/0ca6a2b9f70a4be79a4ce32b4764ac50.png)
由此可认为第一层子目标各项判断无逻辑错误,即
![](https://img-blog.csdnimg.cn/73210420c1e647bdb8a8a19453951e9f.png)
![](https://img-blog.csdnimg.cn/9dd00b7e45ca467fac008db937134fbc.png)
4.4 问题三的模型建立与求解
4.4.1 建模思路
4.4.2 模型建立
4.4.3 模型的求解和结果分析
![](https://img-blog.csdnimg.cn/91dbe88741674315b7f1425bd3aadcdc.png)
4.5 问题四的模型建立与求解
4.5.1 建模思路
4.5.2 模型建立
![](https://img-blog.csdnimg.cn/c66c50c45d384b26bfe8bebc3fa0efe5.png)
4.5.3 模型的求解和结果分析
![](https://img-blog.csdnimg.cn/7ff516e5ce434c6080156d5ee55bc582.png)
![](https://img-blog.csdnimg.cn/e12b7fb9fb1d4568bd14fa023e52fb55.png)
![](https://img-blog.csdnimg.cn/3cbc9588dd6744218f04b73df60a3079.png)
4.6 问题五的模型建立与求解
4.6.1 建模思路
4.6.2 模型建立
4.6.3 模型的求解和结果分析
![](https://img-blog.csdnimg.cn/8a3a350aeb0844079224fbec54a7ea02.png)
![](https://img-blog.csdnimg.cn/c69f0a8c7be647a7b5ce911918bd610c.png)
五、模型的评价与推广
5.1 模型的评价
5.1.1 模型的优点
5.1.2 模型的缺点
5.1.3 模型的改进
5.2 模型的推广
六、参考文献
代码实现
源程序 1:
use Contest; ---------------------------创建总表----------------------------- select sd.djh,sd.spbm,sd.sj,sd.sl,sd.je,sd.dtime,sd.syjh,vsd.kh as
vsd_kh,vsd.spmc,vsd.jf,vsd.gzbm,vsd.gzmc,vi.kh as vi_kh,vi.csny as vi_csny,vi.xb as vi_xb,vi.djsj as
vi_disj
into total
from sale_detail sd
left join vip_sale_detail vsd
on (sd.djh=vsd.djh and sd.dtime=vsd.dtime and sd.je=vsd.je and sd.sj=vsd.sj and sd.sl=vsd.sl and
sd.spbm=vsd.spbm and sd.syjh=vsd.syjh)
left join vip_information vi on vsd.kh=vi.kh
;---------------------创建该商场会员消费明细表------------------- select * into mall_vip_sale_detail
from total
where vsd_kh is not null and vi_kh is not null
;--------------------创建该商场非会员消费明细表------------------- select * into mall_notvip_sale_detail
from total
where vsd_kh is null and vi_kh is null
;--------------------创建以会员消费时间为主的,该商场会员消费明细表------------------- select
vsd.kh,vsd.dtime,vsd.spbm,vsd.sl,vsd.sj,vsd.je,vsd.spmc,vsd.jf,vsd.syjh,vsd.djh,vsd.gzbm,vsd.gzmc,vi. kh as vi_kh,vi.csny,vi.xb ,vi.djsj
into vtime_mall_vip_sale_detail
from vip_sale_detail vsd,vip_information vi
where vsd.kh=vi.kh
----------------------------第一问----------------------------------------
--------------------------购买力维度-------------------------------------- select vi_kh as 会员卡号,count(vi_kh) as 会员消费宗数,SUM(je) as 会员消费总金额,SUM(sl) as
会员购买数量
from vtime_mall_vip_sale_detail --会员
group by vi_kh;
--------------------------时间偏好维度--------------------------------------
--创建截取时间的临时表
if object_id('tempdb..#cut_time') is not null
Begin
drop table #cut_time
End
select CONVERT(char(10),dtime,120) as cut_time,* into #cut_time
from vtime_mall_vip_sale_detail tmvsd
;--2015 冬
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2015-01-01' and '2015-02-28'; --2015 春
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2015-03-01' and '2015-05-31'; --2015 夏
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2015-06-01' and '2015-08-31'; --2015 秋
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2015-09-01' and '2015-11-30'; --2016 冬
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2015-12-01' and '2016-02-29'; --2016 春
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2016-03-01' and '2016-05-31'; --2016 夏
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2016-06-01' and '2016-08-31'; --2016 秋
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2016-09-01' and '2016-11-30'; --2017 冬
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2016-12-01' and '2017-02-28'; --2017 春
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2017-03-01' and '2017-05-31'; --2017 夏
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2017-06-01' and '2017-08-31'; --2017 秋
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2017-09-01' and '2017-11-30'; --2018 冬
select COUNT(distinct vi_kh) as 人数,COUNT(vi_kh) as 次数
from #cut_time ct
where ct.cut_time between '2017-12-01' and '2018-02-28'; ----------------------特价商品消费占比---------------------------- select SUM(je) as 商品消费总金额 from vtime_mall_vip_sale_detail;
select SUM(je) as 特价商品消费总金额
from vtime_mall_vip_sale_detail vmvsd
left join product_information pdi on vmvsd.spbm=pdi.spbm
where pdi.splm like '%特%' or pdi.splm like '%折%' or pdi.splm like '%促%' ---------------------高价商品消费占比-----------------------------
--售价为以上为高价商品
select SUM(je) as 高价商品消费总金额
from vtime_mall_vip_sale_detail vmvsd
where sj>=5000
--------------------该商场会员与非会员的消费差异----------------------------------
--------------------------购买力维度--------------------------------------
--创建该商场每日非会员消费金额临时表
if object_id('tempdb..#notvip_total_je') is not null
Begin
drop table #notvip_total_je
End
select CONVERT(char(10),dtime,120) as date,SUM(je) as 非会员消费总金额 into #notvip_total_je
from total t
where
vsd_kh is null and t.vi_kh is null --限定非会员
group by CONVERT(char(10),dtime,120)
order by 1
;--创建该商场会员消费金额临时表
if object_id('tempdb..#vip_total_je') is not null
Begin
drop table #vip_total_je
End
select CONVERT(char(10),t.dtime,120) as 日期,SUM(je) as 会员消费总金额 into #vip_total_je
from total t
where
t.vsd_kh is not null and t.vi_kh is not null --限定会员
group by CONVERT(char(10),dtime,120)
order by 1
;--提取该商场会员、非会员消费总金额
select * from #notvip_total_je a left join #vip_total_je b on a.date=b.日期; --提取会员、非会员消费总金额
select SUM(je) as 会员消费总金额 from mall_vip_sale_detail; --会员
select SUM(je) as 非会员消费总金额 from mall_notvip_sale_detail; --非会员
--会员、非会员的总购买数量
select SUM(sl) as 会员总购买数量 from mall_vip_sale_detail; --会员
select SUM(sl) as 非会员总购买数量 from mall_notvip_sale_detail; --非会员
-----------------------------第二问-----------------------
-------------------------查询 RFM 模型中所需数据------------
---创建最后一次购买日期临时表
if object_id('tempdb..#last_date') is not null
Begin
drop table #last_date
End
;
select
vi_kh,MAX(dtime) as last_date into #last_date
from vtime_mall_vip_sale_detail
group by vi_kh
;--创建 RFMT 模型临时表
if object_id('tempdb..#RFMT') is not null
Begin
drop table #RFMT
End
;
select
distinct vmvsd.vi_kh as 会员卡号,
ld.last_date, datediff(day,last_date,'2018-1-15') as R, COUNT(vmvsd.vi_kh) as F, sum(je) as M, MAX(je) as T
into #RFMT
from vtime_mall_vip_sale_detail vmvsd,#last_date ld
where vmvsd.vi_kh=ld.vi_kh
group by vmvsd.vi_kh,last_date
;--提取没有消费记录的会员卡号
select vi.kh,vi.csny,vi.xb,vi.djsj
from vip_information vi
left join vtime_mall_vip_sale_detail vmvsd on vi.kh=vmvsd.vi_kh
where vmvsd.vi_kh is null -----------------------------第三问-----------------------------------
--创建 RFMT 模型临时表
if object_id('tempdb..#frofire') is not null
Begin
drop table #frofire
End
;
select vmvsd.kh,SUM(pdi.sj-pdi.hsjj) as 总利润 into #frofire
from vtime_mall_vip_sale_detail vmvsd
left join product_information pdi on vmvsd.spbm=pdi.spbm
group by vmvsd.kh
select * from #RFMT a left join #frofire b on a.会员卡号=b.kh
----------------------------第四问------------------------
--创建特价商品临时表
if object_id('tempdb..#vip_discount_product_je') is not null
Begin
drop table #vip_discount_product_je
End
;
select vmvsd.kh,SUM(je) as 特价商品消费总金额 into #vip_discount_product_je
from vtime_mall_vip_sale_detail vmvsd
left join product_information pdi on vmvsd.spbm=pdi.spbm
where pdi.splm like '%特%' or pdi.splm like '%折%' or pdi.splm like '%促%' group by vmvsd.kh
;--创建会员特价商品消费金额表
if object_id('tempdb..#vtotal_discount_product_je') is not null
Begin
drop table #vtotal_discount_product_je
End
;--查询会员消费总金额
select vmvsd.kh,SUM(je) as 会员消费总金额,ISNULL(vdpj.特价商品消费总金额,0) as 特价商品
消费总金额
into #vtotal_discount_product_je
from vtime_mall_vip_sale_detail vmvsd
left join #vip_discount_product_je vdpj on vmvsd.kh=vdpj.kh
group by vmvsd.kh,vdpj.特价商品消费总金额
;
select a.*,b.特价商品消费总金额,b.会员消费总金额
from #RFMT a left join #vtotal_discount_product_je b on a.会员卡号=b.kh
;
select jhl.*,vl.会员生命周期 from jihuolv jhl left join vip_life vl on jhl.会员卡号=vl.会员卡号
where vl.会员生命周期 <> '活跃会员' ;--------------------第五问---------------------------
--获取单据号
select distinct djh from vtime_mall_vip_sale_detail ; --获取商品编码
select distinct spbm from vtime_mall_vip_sale_detail ;
select djh,spbm from vtime_mall_vip_sale_detail;
源程序 2:
/*多元线性回归分析*/
proc reg data =liushi;
var jihuo_rate cuxiao_rate F R M T;
model jihuo_rate = cuxiao_rate F R M T;
run;
/*因子分析*/
proc factor data=A out=B Nfactor=3 method=prin priors=one
rotate=varimax simple p=0.8 score outstat=C;
var R F M T;
run;
proc score data=A
score=C
out=D;
var R F M T;
run;