备战2024数学建模国赛（模型三十二）：因子分析 优秀案例（一）基于 RFMT 模型的百货商场会员画像描

 专栏内容(赛前预售价99，比赛期间299): 2024数学建模国赛期间会发布思路、代码和优秀论文。（本专栏达不到国一的水平，适用于有一点点基础冲击省奖的同学，近两年有二十几个国二，但是达不到国一，普遍获得省奖，请勿盲目订阅）

python全套教程（一百篇博客）：从新手到掌握使用python，可以对数学建模问题进行建模分析。

35套模型算法（优秀论文示例）：马尔科夫模型、遗传算法、逻辑回归、逐步回归、蚁群算法、蒙特卡洛模拟、聚类模型、线性规划、粒子群算法、神经网络、相关系数、灰色预测、灰色关联分析、模糊综合评价、模拟退火、时间序列ARMA、方差分析支持向量机、插值、排队论、拟合模型、微分方程、层次分析法、小波分析、多元回归、图论floyd算法、图论Dijkstra模型、因子分析、动态规划、博弈论、决策树、典型相关分析、元胞自动机、主成分分析、TOPSIS法。

目录

摘要

一、问题的重述

二、模型假设

三、变量说明

四、模型的建立与求解

五、模型的评价与推广

六、参考文献

七、附录

---

摘要

        电商的快速发展给零售运营商带来了较大的冲击，为了持续获取稳定的销售额和利润，零售运营商需完成对会员的管理与维系工作。完善会员画像，加强会员管理，维持会员稳定将使得零售业更好地发展。
        本文利用该大型百货商场提供的会员信息以及消费明细，完善该商场的会员画像。本文从购买力、购买时间偏好、消费偏好三个维度分析会员的消费特征。以会员消费总金额、消费次数、商品购买数量代表会员购买力，同时按季节对会员消费行为进行分析，随后以特价商品、高价商品消费金额在会员总消费金额的占比分析会员的消费偏好。
        为进一步说明会员群体给商场带来的价值，本文对比了会员与非会员的购买力。会员群体的消费总金额和商品购买数量略低于非会员群体，原因或是非会员群体人数较多。但是绘制两类群体的日消费金额曲线后可知，与非会员群体相比，会员的单日消费总金额增幅较大。
        为刻画会员的购买力，本文建立了 RFMT 模型。分别选取会员最后一次消费的时间间隔、消费频率、总金额、单次购买最高金额作为指标，结合层次分析法得到相应指标的权重，并计算出每个会员的得分，会员得分则代表着会员的个人价值。利用 K-means 聚类的方法，根据会员得分进行聚类，得分较高的会员群体则为商场需要维护的会员群体。
        为了合理地判断会员所处的生命周期，本文利用已构建的 RFMT 模型中的相关指标，再次使用 K-means 聚类的方法对该商场的会员进行聚类，将现有会员划分为活跃会员、沉默会员、流失会员三类，以便商场管理者对会员进行管理。在会员的生命周期中，会员状态处于动态变化的过程。为了增加商场的利润，与发展新会员相比，促进非活跃会员转化为活跃会员会大大降低商场的成本。本文通过构建非活跃会员的相关指标，使用因子分析法，可计算得各非活跃会员激活率，激活率越高，则其被激活的可能性则越大。同时，本文以非活跃会员的特价商品消费金额在总消费金额中的占比作为非活跃会员对促销活动敏感度的反映，构建线性回归模型分析非活跃会员的激活率与促销活动之间的关系，结果表明，一定的促销活动有助于提升非活跃会员的激活率。
        连带消费是商场经营的核心，本文选取销售数量排名前十的商品作为最受欢迎的商品，根据会员消费明细表，利用 Matlab 软件构建商品关联表，并使用Clementine 建立商品的关联规则。商场可对热门商品及其关联商品推出相应促销活动，同时通过广告投放、邮件推送等方式对促销活动进行推广。

        关键词：会员画像；RFMT 模型；生命周期；精准化营销

一、问题的重述

        在零售行业中，会员的发展和维系对零售运营商而言至关重要，会员不但能够为运营商带来稳定的销售额和利润，而且对运营商的营销策略制定起着重要的作用。但随着电商日益发展，实体商场的会员不断流失，给零售运营商带来了严重损失，因此商家必须开展各种营销活动来吸引更多的会员以及维系旧会员。
        在传统的市场营销中，想准确知道消费者的习惯、偏好、个性等影响消费者购买行为的因素是十分困难的，企业想对消费者进行精准化营销也难以实现，因此导致企业浪费了大量的营销资源。在众多大数据工具中用户画像技术是帮助企业准确识别和分析目标客户的有效工具之一，对于零售运营商的会员发展和维系，完善会员画像描绘，加强对现有会员的精细化管理，定期向其精准推送产品和服务，与会员建立稳定的关系是实体零售行业得以更好发展的有效途径。
        在本文的研究中，完善商场会员画像是我们的研究重点，针对某大型百货商场的会员数据以及销售数据，我们需要解决以下问题：
(1) 对该商场的会员消费特征以及会员与非会员差异进行分析，并说明会员给商场带来的价值；
(2) 建立刻画会员购买力的数学模型，对会员的价值进行识别；
(3) 在某个时间窗口，建立会员生命周期和状态划分的数学模型；
(4) 计算会员生命周期中非活跃会员的激活率，并确定激活率和商场促销活动之间的关系模型；
(5) 根据会员的喜好和商品的连带率设计促销方案帮助商家策划促销活动。

二、模型假设

为了使得问题更易于理解，我们作出以下合理假设：
 假设销售数据录入系统时不存在时间差；
 假设销售流水表和会员消费明细表中的一条记录代表一次消费，即不存在同一次消费产生多条记录的情况；
 假设会员的会员卡自开卡日起，除了自行退出外不存在会员卡过期导致会员退会的情况。

三、变量说明

本文建立模型的过程中主要涉及以下变量，变量及说明如下:

四、模型的建立与求解

4.1 数据预处理
        题目提供了 5 个附件，附件中的数据给出了商场会员的相关信息：附件 1是会员信息数据；附件 2 是近几年的销售流水表；附件 3 是会员消费明细表；附件 4 是商品信息表；附件 5 是数据字典。
        对于众多的会员信息数据，我们需要对数据进行清洗整理，使用 EXCEL 和
SQL Server 软件对数据做了以下预处理：

        1 剔除数据表中的重复数据；
        2 由于我们只针对附件一中的会员进行管理，附件三中的会员消费记录存在其他分店的会员，而附件一为本商场的会员，我们将附件一与附件三的数据相关联，筛选出本商场的会员消费明细，剔除其他分店的会员消费明细；
        3 将附件一、附件二与附件三的数据相关联，分别筛选出附件二中会员与非会员的数据。
利用以上数据，对问题进行求解分析。

4.2 问题一的模型建立与求解
4.2.1 建模思路
        对于问题一，我们运用数据统计分析的方法来对会员信息进行分析。问题中需要根据会员消费明细表分析会员的消费特征，主要从三个维度来分析：购买力、时间偏好、消费偏好，具体分析指标下图所示：

从饼状图中可以看到，在所有会员中消费宗数小于 10 的会员占大多数，而
消费宗数大于 50 的会员仅占 0.9%。同样地，商品购买数量小于 10 的会员占比
最大，购买数量大于 50 的会员仅占比 1.4%。
从以上统计结果可知，无消费会员的占比最大，该商场的大部分会员都存在
开了会员卡不消费的情况；而中、低消费的会员比高消费会员多，表明该商场会
员的购买力一般在中、低消费的水平上。由于存在大量的无消费会员，该商场应
采取一系列促销活动来吸引会员消费，维系会员的忠诚度。
(2) 时间偏好
除了会员购买力能够直观地看出会员的消费特征，从时间上也能看出会员消
费的时间倾向。在时间偏好维度上，本文主要分析会员消费的季节性倾向。在分
析中，一般认为每年的 3 月-5 月为春季，6 月-8 月为夏季，9 月-11 月为秋季，
12 月-次年 2 月为冬季。根据会员消费明细数据，统计得到 2015 年-2017 年各季
节消费的会员人数情况，如图 5 所示：

明显可以看到，2015 年的秋季会员消费人数为 0，冬季的消费人数与春季和
夏季相比较少；到了 2016 年，会员在冬季的消费人数最少，而在秋季消费的人
最多；2017 年的会员消费人数在每个季节都较为平均，无明显季节性倾向。总
体来看，该商场的会员主要倾向于春季和夏季消费。
(3) 消费偏好
关注消费者对促销活动、高价商品的敏感度是分析消费者消费特征的一个很
好的方向，在商场的营业中，销售特价商品和高价商品往往是商场提高盈利的渠
道，所以可通过分析会员的特价商品和高价商品的消费情况来分析商场会员的消
费特征。根据会员的消费明细表，可知会员的特价商品和高价商品的消费情况如
表 3 所示。

在会员总消费中，特价商品消费占比 40.51%，可见会员对于促销活动的特
价商品的购买力较高，而高价商品消费占比 27.54%，表明会员对高价商品的销
售贡献度还是比较高的。特价商品的销售属于薄利多销形式，通过促销活动吸引
消费者消费；而高价商品的销售量不会像日常用品那样高，但是商品的价格越高，
商场的盈利也越高，二者的销售均能给商场带来高利润收入。
2. 会员与非会员的差异
为了进一步分析客户给商场带来的价值，本文对会员和非会员的购买力进行
了对比分析。同时，考虑到现有数据时间维度的不统一，本文以商场销售流水表
为基准，提取时间节点为 2016 年 1 月至 2017 年 9 月的会员、非会员消费明细，
开展分析。
在 完 成 数 据 表 关 联 后 ， 利 用 统 计 分 析 法 统 计 得 ， 会 员 消 费 总 金 额 为
419074889.11 元，非会员消费总金额为 507093746.29 元；会员购买商品总数量
为 330535 件，非会员购买商品总数量为 477415 件。
非会员的消费总金额略高于会员消费总金额，且非会员购买商品的总数量也
高于会员群体。针对该现象，结合日常生活中的实际情况，可能的原因是非会员群体的人数多于会员人数。

从会员与非会员的消费总金额情况中可以明显看出，在统计时间段内，大部
分会员每日消费金额比非会员每日消费金额高，会员群体的单日消费总金额增幅
较大，购买力比非会员群体高。但可以注意到在 2016 年 1 月到 2016 年 3 月这段
时间会员的每日消费总金额都非常低，相反在这段时间内非会员每日消费总金额
是最高的，即非会员群体是最活跃的。对于此现象，我们推测在该段时间内商场
会员人数处于较少阶段，商场推出一系列促销活动，增加了非会员群体的消费金
额，并借此机会发展新会员，使得非会员群体升级为会员。
4.3 问题二的模型建立与求解
4.3.1 建模思路
对于问题二，需要建立一个能够刻画会员购买力的数学模型，并通过此模型
来识别每一位会员的价值，就是要将每一位会员进行价值分析。
在众多的用户价值分析模型中，RFM 模型是衡量客户价值和增益能力的重
要工具，考虑到本文研究对象为大型百货商场，相对消费会较为高端，可以增加
一个反映会员一次性消费的最高能力的指标，故我们引入改进的 RFM 模型
--RFMT 模型，对会员购买力进行刻画，并通过 RFMT 模型的会员得分对每个会
员进行价值等级划分，最终可得知每一位会员对于商场的价值。
4.3.2 模型建立
在 RFM 模型的基础上，引入 RFMT 模型衡量会员价值和刻画会员购买力，
应用层次分析法计算 RFMT 模型每个指标的指标权重，构建指标得分规则计算
RFMT 指标的得分，最终通过 K-means 聚类法对会员群体进行价值等级分类。
1. RFMT 模型介绍 ]1[
在营销活动中，每个会员的价值因其购买能力和实际需求的不同而各不相
同，寻找一种工具来辨别会员价值至关重要。会员价值模型的建立可以对会员进
行排序分类，然后对会员进行个性化营销。
本文为会员的消费情况建立一个能够刻画每一位会员购买力的 RFMT 数学
模型，它以会员关系领域广泛用来衡量会员价值和描述会员行为的 RFM 模型为

基础，拓展而成。RFMT 模型有四个指标，指标含义如下：
 R（Recency）
R 表示会员最近一次购买商品的时间间隔天数。理论上，最近一次消费时间
越近的会员应该是比较好的会员，对提供即时的商品或是服务也最有可能会有反
应。R 指标主要刻画了会员对商场的关注程度。
 F（Frequency）
F 表示会员在限定时间内购买商品的频率，消费频率越高的会员，其满意度
和忠诚度也就越高。F 指标主要刻画了会员对商场的忠诚度。
 M（Monetary）
M 表示会员在限定时间购买商品的总金额。消费金额是所有数据库报告的支
柱，直接反应了商场的盈利情况。M 指标主要刻画了会员的购买力。
 T（Topest）
T 表示单次购买的最高金额，反映的是会员一次性消费的最高能力。
RFMT 模型以上述四个指标为替代变量，通过指标标准化和赋予权重来计算
会员价值，然后根据会员价值来进行均值聚类分析，将会员分成不同的类别，作
为商场精准营销的基础。
2. 层次分析法
(1) 层次分析法基本思想
层次分析法（Analytic Hierarchy Process，简称 AHP）是将与决策总是有关
的元素分解成目标、准则、方案等层次，在此基础之上对一些较为复杂、较为模
糊的问题进行定性和定量分析的决策方法，它特别适用于那些难于完全定量分析
的问题。
(2) 层次分析法计算权重系数
为了知道各指标体系在综合评价中的重要程度，我们对在同一层评价目标中
各个评价目标对总评价目标作用价值的大小分别赋予一定的权重系数。此处以一
级指标的权重求取为例权重计算步骤为：
a. 建立递阶层次结构模型
对总评价目标进行连续性分解以得到不同层次的评价目标，建立目标树图将
各层评价目标表示出来，如图 7 所示。

进行会员分类后，我们再对会员的类别进行会员细分群的价值得分进行排
序，使得商场能够量化各类会员的价值的差别，有助于企业制定更为可行的会员
政策。
由于受到成本的制约，商场不可能提供完全的、无差别的个性化服务，按照
总得分的排列情况，我们认为商场应该优先将资源投放到总得分较高的会员细分
群体上。
4.4 问题三的模型建立与求解
4.4.1 建模思路
商场会员从入会到退出的过程称为会员的生命周期，在整个生命周期内会员
的状态会随着会员的消费行为改变而改变，这个动态的过程对于商场对会员的管
理造成了困扰，因此我们需要建立一个模型以判别会员处于生命周期内的状态。
对于问题三，基于问题二中的 RFMT 模型，选取 R（会员最近一次购买商
品的时间间隔天数）和 F（会员在限定时间内购买商品的频率）指标作为聚类依
据，应用 K-means 聚类法对有消费记录的会员进行状态聚类，最终可知每个会员
所处的生命周期状态。
4.4.2 模型建立
为了更有效地对商场会员进行维系以及管理，该模型用于判别会员处于生命
周期内的状态。通常情况下，会员的生命周期状态分为活跃会员、沉默会员、流
失会员等。
考虑到会员的消费行为对状态的影响，在问题二的 RFMT 模型的基础上，
运用 Clementine 软件对会员的 R、F、M、T 四个指标数据进行 K-means 聚类,选
取模型中的 R 和 F 指标作为聚类依据，建立聚类模型对会员状态进行分类。
其中，R 表示会员最近一次购买商品的时间间隔天数，F 表示会员在限定时
间内购买商品的频率，M 表示会员在限定时间购买商品的总金额，T 表示单次购
买的最高金额。
K-means 聚类法的步骤与问题二中的聚类模型步骤相同，此处不再重复描述。

从 R 指标和 F 指标的聚类中心来看，聚类 1 的 R 聚类中心为 982.932，F 聚
类中心为 4.329，表示会员最近一次购买商品的时间距离数据截取时间的间隔天
数约为 983 天，购买商品的频率约为 4 次，可将此类会员划分为流失会员；聚类
2 的 R 聚类中心为 113.681，F 聚类中心为 16.458，表示会员最近一次购买商品
的时间距离数据截取时间的间隔天数约为 114 天，购买商品的频率约为 16 次，
即会员在最近三个月内有消费且消费次数约为 16 次，可将此类会员划分为活跃
会员；聚类 3 的 R 聚类中心为 447.715，F 聚类中心为 5.695，表示最近一次购买
商品的时间距离数据截取时间的间隔天数约为 447 天，购买商品的频率约为 6
次，即会员最后一次消费发生在最近的 4-6 个月内，已经沉默 3 个月以上，因此
可将此类会员划分为沉默会员。
4.5 问题四的模型建立与求解
4.5.1 建模思路
问题四中要求计算非活跃会员的激活率和确定激活率和商场促销活动之间
的关系模型。
从问题三的聚类结果中可筛选出非活跃状态的会员，给非活跃会员构建分析
指标：R、F、M、T 指标，针对非活跃会员的 RFMT 指标进行因子分析，可得
到相应指标的因子得分，以每个公因子的方差贡献率作为权重系数，对每个因子
进行加权求和，从而计算得到各非活跃会员的激活率。
对于非活跃会员激活率和商场促销活动之间的关系模型，考虑到商场促销活
动与特价商品有关，结合非活跃会员的激活率和特价商品消费总金额在商品消费
总金额中的占比，利用 SAS 软件做相关性分析，得到激活率和商场促销活动之
间的关系模型。
4.5.2 模型建立
此问题主要建立因子分析模型，其基本思想为根据相关性大小将变量进行分
类，使得同一类的变量之间相关性较高，而不同类变量之间的相关性较低，每一
类变量代表一个公共因子。
假设 ),X,,X(XX p21  为可观测随机向量，用 ),Y,,Y(YY p21  代表经标准化

连带消费是商场经营的核心，例如经典的“尿布与啤酒”的故事。连带消费是
商品与商品之间具有的某种特定的关系导致的，根据会员的消费情况，对购买的
商品进行关联规则挖掘；同时筛选出会员消费记录中的购买数量前十的商品作为
会员的喜爱商品，结合商品之间的关联规则，开展促销活动。
4.6.2 模型建立
问题五中主要的工作为对会员购买的商品进行关联规则挖掘。
关联规则挖掘过程主要包含两个阶段:第一阶段必须先从会员消费数据集中
找出所有的高频商品，第二阶段再由这些高频商品中产生关联规则。
关联规则挖掘的第一阶段必须从会员消费记录中，找出所有高频商品。高
频的意思是指某一商品出现的频率相对于所有记录而言，必须达到某一水平。一
商品出现的频率称为支持度，以一个包含 A 与 B 两个商品的 2-itemset 为例，我
们可以经由公式 1 求得包含{A,B}商品的支持度，若支持度大于等于所设定的最
小支持度门槛值时，则{A,B}称为高频商品。一个满足最小支持度的 k-itemset，
则称为高频 k-商品，一般表示为 Large k 或 Frequent k。算法并从 Large k 的项目
组中再产生 Large k+1，直到无法再找到更长的高频项目组为止。
关联规则挖掘的第二阶段是要产生关联规则。从高频商品产生关联规则，
是利用前一步骤的高频 k-商品来产生规则，在最小信赖度的条件门槛下，若一规
则所求得的信赖度满足最小信赖度，称此规则为关联规则。例如:经由高频 k-商
品{A,B}所产生的规则 AB，其信赖度可经由公式 2 求得，若信赖度大于等于最
小信赖度，则称 AB 为关联规则。
4.6.3 模型的求解和结果分析经统计，在会员消费记录中购买数量排名前十的商品为：

五、模型的评价与推广

5.1 模型的评价
5.1.1 模型的优点
1．所建立的模型公众认可度高，模型严谨，考虑的维度和因素较为全面；
2．模型的计算采用专业软件求解，例如 SQL Server 软件、Clementine 软件、
EXCEL 软件等对数据进行处理和模型求解，用于分析的数据可信度较高；
3．建立的模型能够与实际紧密联系，结合实际情况对问题进行求解，使得
模型具有很好的通用性和推广性。
5.1.2 模型的缺点
1．RFMT 模型的指标间存在一定的相关性，模型中的四个指标 R、F、M、
T 相关性较大，在价值衡量模型中出现相关性较大的指标，这样模型会显得较为
冗余。
2．RFMT 模型具有局限性，RFMT 是衡量会员的价值的代表性指标，仅从 4
个指标角度描述的主要是会员消费行为特征，但是这些指标还不能涵盖会员绝大
多数的行为特征。
3．本文用指标 R（最近一次消费的时间间隔）来描述会员对商场的关注程
度，但是更好的标准应该是会员对商场的访问频率，它可以更好地判断一个会员
对商场商品的需求程度，访问越频繁代表越有需求，但是现有数据并不支持我们
用会员的访问频率来进行分析。
5.1.3 模型的改进

1．对于 RFMT 模型冗余的问题，在后期可以对 RFMT 模型进行精简，主要
针对 F（消费频率）和 M（消费总金额）指标来进行分析。
2．在可获取数据的前提下，深入研究有哪些因素可以作为衡量会员价值的
指标因素。后期可以做模型扩展，将会员的其他特征指标加进来，比如所在区域、
工作职务、收入等个人信息，建立一个更加全面和准确的会员价值评价体系。
5.2 模型的推广
在大型百货商场的背景下，本文建立了完整的会员画像刻画模型，对商场会
员的消费行为进行特征分析，衡量会员对于商场的价值，根据会员生命周期状态
对会员的状态进行划分，使商场能够更有效地对会员进行精细化管理以及及时制
定营销策略来发展和维系会员，从而达到增益的目的。
本文建立的 RFMT 模型能够广泛应用于用户价值分析中，对于现实生活中
的百货商场会员分析也有着一定的参考和现实意义，并且可以将模型推广到 APP
运营、通信行业用户分析等研究中。

六、参考文献

[1] 王欢,祁金才,孙为平,傅伟康,刘如梦,李雅婷.基于 RFMT 模型的企业供应商评
价[J].自动化应用,2017(04):38-40.
[2] 徐静,王虹斌.基于 RFMT 的物流公司大客户营销策略研究——以南昌佳吉快
运为例[J].物流技术,2013,32(13):208-212.
[3] 王 千 , 王 成 , 冯 振 元 , 叶 金 凤 .K-means 聚 类 算 法 研 究 综 述 [J]. 电 子 设 计 工
程,2012,20(07):21-24.
[4] Anil K J. Data clustering: 50 years beyond K-Means[ J]. Pattern Recognition
Letters,2010,31(8):651-666
[5] 郝胜宇,陈静仁.大数据时代用户画像助力企业实现精准化营销[J].中国集体经
济,2016(04):61-62.
[6] 刘海,卢慧,阮金花,田丙强,胡守忠.基于“用户画像”挖掘的精准营销细分模型研
究[J].丝绸,2015,52(12):37-42+47.
[7] 谢康,吴记,肖静华.基于大数据平台的用户画像与用户行为分析[J].中国信息
化,2018(03):100-104.
[8] 赵萌,齐佳音.基于购买行为 RFM 及评论行为 RFMP 模型的客户终身价值研究
[J].统计与信息论坛,2014,29(09):91-98.
[9] 林盛,肖旭.基于 RFM 的电信客户市场细分方法[J].哈尔滨工业大学学
报,2006(05):758-760.

七、附录

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 --会员groupbyvi_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_timect
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')isnotnull
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;