Part2-某零售公司年度销售情况分析项目

此部分用于展示某零售公司2015年销售情况分析项目的数据分析部分。

该篇文章目录如下：

1. 数据建模
   1.1 加载数据
   1.2 创建关系
   1.3 整理模型
2. 确定分析思路
   2.1 整理分析思路
   2.2 确定分析步骤
3. 模型分析
   3.1 确定分析指标
   3.2 根据RFM模型对客户分组
   3.3 根据订单销售额对订单分组
   3.4 根据订单准时情况对订单分组
   3.5 销售人员排名
4. 模型整理
   4.1 创建层次结构
   4.2 创建KPI考核指标
   4.3 按列排序
   4.4 隐藏列

一. 数据建模

1. 加载数据

• 销售记录：通过Power Query查询加载到模型。
• 其他Excel文件：通过Power Pivot加载到模型。

2. 创建关系

通过Power Pivot 关系图视图创建关系如下，红框中的2015年销售人员任务额和日期表两张表原始数据中并不具有包含匹配值的列，通过在2015年销售人员任务额中增加列来创建关系。

3. 整理模型

3.1 将日期表标记为日期表

3.2 建立事实表

建立事实表：将之后生成的度量值都放在事实表中，使模型结构更加清晰。

二、确定分析思路

1. 整理分析思路

该数据分析项目从人、货、场三个方面对销售情况进行分析，具体见下图。

2. 确定分析步骤

• 确定分析指标
• 根据RFM模型对客户分组
• 根据订单销售额对订单分组
• 根据订单准时情况对订单分组
• 确定销售人员排名

三、模型分析

1. 确定分析指标

根据分析思路，在模型事实表中创建指标的度量值。

度量值	公式
总销售额	SUMX('销售记录','销售记录'[单价]*'销售记录'[数量])
2014年销售额	CALCULATE([总销售额],'日期表'[年份]=2014)
2015年销售额	CALCULATE([总销售额],'日期表'[年份]=2015)
2015年增长销售额	[2015年销售额]-[2014年销售额]
总任务额	SUM('2015年销售人员任务额'[任务额])
任务额完成率	DIVIDE([2015年销售额],[总任务额])
年累计YTD	TOTALYTD([总销售额],'日期表'[日期])
季度累计QTD	TOTALQTD([总销售额],'日期表'[日期])
月累计MTD	TOTALMTD([总销售额],'日期表'[日期])
去年销售额	CALCULATE([总销售额],DATEADD('日期表'[日期],-1,YEAR))
同比YOY	DIVIDE([总销售额]-[去年销售额],[去年销售额])
上月销售额	CALCULATE([总销售额],DATEADD('日期表'[日期],-1,MONTH))
环比MOM	DIVIDE([总销售额]-[上月销售额],[上月销售额])
去年YTD销售额	CALCULATE([年累计YTD],DATEADD('日期表'[日期],-1,YEAR))
YTDYOY	DIVIDE([年累计YTD]-[去年YTD销售额],[去年YTD销售额])
总客户数	COUNTA('客户'[客户ID])
已购买客户数	DISTINCTCOUNT('销售记录'[客户ID])
客户渗透率	DIVIDE([已购买客户数],[总客户数])
客单价	DIVIDE([总销售额],[已购买客户数])
总订单数	DISTINCTCOUNT('销售记录'[订单编号])
总销售量	SUM('销售记录'[数量])
平均订单数量	DIVIDE([总销售量],[总订单数])
平均订单金额	DIVIDE([总销售额],[总订单数])
总销售人员数	COUNTA('销售人员架构'[销售ID])
人效	DIVIDE([总销售额],[总销售人员数])

2. 根据RFM模型对客户分组

2.1 分组依据

• 使用RFM模型对客户分组的依据见下表。

• R F M 三个指标表达的含义见下表。

度量值	含义	判断标准
R	最近一次消费	最近一次消费比平均最近一次消费近则R为近，反之为远
F	消费频率	消费频率比平均消费频率高则F为高，反之为低
M	消费金额	消费金额比平均消费金额高则M为高，反之为低

• 将RFM定义表格加载到Power Pivot模型中，创建近度计算列和R F M三个度量值。

度量值	所属表	公式
近度	销售记录	TODAY()-'销售记录'[下单日期]
R1	事实表	MIN('销售记录'[近度])
F1	事实表	DISTINCTCOUNT('销售记录'[订单编号])
M1	事实表	DIVIDE([总销售额],[F1])

2.2 建立链接回表

• 通过SUMMARIZE函数对于销售记录按照客户ID 和客户名称进行聚合，拿到每个客户ID和客户名称对应的R F M值。将表加载回Power Pivot模型中，命名为链接回表-RFM。

• 增加计算列将RFM数值进行转换

计算列	所属表	公式
R值	链接回表-RFM	IF('链接回表-RFM'[R]<AVERAGE('链接回表-RFM'[R]),"近","远")
F值	链接回表-RFM	=IF('链接回表-RFM'[F]>AVERAGE('链接回表-RFM'[F]),"高","低")
M值	链接回表-RFM	=IF('链接回表-RFM'[M]>AVERAGE('链接回表-RFM'[M]),"高","低")
RFM辅助列	链接回表-RFM	='链接回表-RFM'[R值]&'链接回表-RFM'[F值]&'链接回表-RFM'[M值]
RFM辅助列	RFM定义	='RFM定义'[R]&'RFM定义'[F]&'RFM定义'[M]

2.3 更新模型关系

新的模型关系见下图。

2.4 判断客户类型

• 销售记录增加计算列客户RFM类型：使用RELATED函数将RFM定义（关系的一端）中的客户类型列拿到销售记录（关系的多端）中。
• 销售记录增加计算列是否是重要客户：使用IF函数进行判断，如果客户RFM类型列值为重要价值客户、重要保持客户、重要发展客户或者重要挽留客户，那么是否是重要客户列取值为重要客户，否则为一般客户。

计算列	所属表	公式
客户RFM类型	销售记录	RELATED('RFM定义'[客户类型])
是否是重要客户	销售记录	=IF('销售记录'[客户RFM类型]="重要价值客户"//'销售记录'[客户RFM类型]="重要保持客户"//'销售记录'[客户RFM类型]="重要发展客户"//'销售记录'[客户RFM类型]="重要挽留客户","重要客户","一般客户")

2.5 增加客户分类的度量值

度量值	所属表	公式
重要客户数量	事实表	CALCULATE([已购买客户数],'销售记录'[是否是重要客户]="重要客户")
重要客户占比	事实表	DIVIDE([重要客户数量],[总客户数])

3. 根据订单销售额对订单分组

3.1 分组依据

• 订单分组依据见下图。

• 将订单分组依据表格加载到Power Pivot模型中。

3.2 建立链接回表

• 通过SUMMARIZE函数对于销售记录按照订单编号进行聚合，拿到每个订单编号对应的订单销售额。
• 将表加载回Power Pivot模型中，命名为链接回表-订单。

3.3 更新模型关系

新的模型关系见下图。

3.4 判断订单类型

• 链接回表-订单增加计算列订单分类：根据每个订单编号对应的订单销售额所在的区间返回相应的订单分类
• 链接回表-订单增加计算列是否是大单：如果订单销售额大于2万，即为大单
• 销售记录增加计算列是否是大单：使用RELATED函数将是否是大单从链接回表-订单（关系的一端）拿到销售记录（关系的多端）中。

计算列	所属表	公式
订单分类	链接回路-订单	IFERROR(CALCULATE(VALUES('订单分组依据'[订单分类]),FILTER('订单分组依据','事实表'[总销售额]>='订单分组依据'[最小值]&&'事实表'[总销售额]<'订单分组依据'[最大值])),"错误的分类")
是否是大单	链接回路-订单	IF('链接回表-订单'[订单分类]=">10万"//'链接回表-订单'[订单分类]="5万-10万"//'链接回表-订单'[订单分类]="2万-5万","大单","普通订单")
是否是大单	销售记录	=RELATED('链接回表-订单'[是否是大单])

3.5 增加订单分类的度量值

度量值	所属表	公式
大单数量	事实表	CALCULATE([总订单数],'销售记录'[是否是大单]="大单")
大单占比	事实表	DIVIDE([大单数量],[总订单数])

4. 根据订单准时情况对订单分组

4.1 分组依据

• 根据订单准时情况对订单分组的依据见下表。

订单分类	分类依据
准时订单	每个订单编号对应的实际送货日期都小于预计送货日期
不准时订单	每个订单编号对应的实际送货日期有大于预计送货日期的情况

• 创建提前送货时间计算列和最小提前送货时间度量值

度量值	所属表	公式
提前送货时间	销售记录	'销售记录'[预计送货日期]-'销售记录'[实际送货日期]
最小提前送货时间	事实表	MIN('销售记录'[提前送货时间])

4.2 更新链接回表

• 更新链接回表链接回表-订单。
• 在Power Pivot模型中刷新。

4.3 判断订单类型

• 链接回表-订单增加计算列是否是准时订单：如果最小提前送货时间>0就是准时订单，否则是不准时订单
• 销售记录增加计算列是否是准时订单：使用RELATED函数将是否是准时订单从链接回表-订单（关系的一端）拿到销售记录（关系的多端）中。

计算列	所属表	公式
是否是准时订单	链接回表-订单	IF('链接回表-订单'[最小平均送货时间]>0,"准时订单","不准时订单")
是否是准时订单	销售记录	RELATED('链接回表-订单'[是否是准时订单])

4.4 增加订单分类的度量值

度量值	所属表	公式
准时交货订单数	事实表	CALCULATE([总订单数],'销售记录'[是否是准时订单]="准时订单")
准时交货率	事实表	DIVIDE([准时交货订单数],[总订单数])

5. 销售人员排名

5.1 排名依据

• 通过权重求和进行排名

考核指标	权重
任务额完成率	0.6
客户渗透率	0.2
大单占比	0.1
重要客户占比	0.1

• 增加任务额完成率、客户渗透率、大单占比、重要客户占比计算列。

计算列	所属表	公式
任务额完成率2	销售人员架构	'事实表'[任务额完成率]
客户渗透率2	销售人员架构	'事实表'[客户渗透率]
大单占比2	销售人员架构	'销售人员架构'[大单占比2]
重要客户占比2	销售人员架构	'事实表'[重要客户占比]

5.2 判断销售人员排名

• 增加销售代表排名分数、销售代表排名计算列

计算列	所属表	公式
销售代表排名分数	销售人员架构	RANK.EQ('销售人员架构'[任务完成率],'销售人员架构'[任务完成率])0.6+RANK.EQ('销售人员架构'[客户渗透率2],'销售人员架构'[客户渗透率2])0.2+RANK.EQ('销售人员架构'[大单占比2],'销售人员架构'[大单占比2])0.1+RANK.EQ('销售人员架构'[重要客户占比2],'销售人员架构'[重要客户占比2])0.1
销售代表排名	销售人员架构	RANK.EQ('销售人员架构'[销售代表排名分数],'销售人员架构'[销售代表排名分数],ASC)

四、模型整理

1. 创建层级结构

产品分类表中加入产品层级结构，便于后续进行钻取分析。

2 . 创建KPI考核指标

将任务额完成率设置为KPI，定义目标值为绝对值1。>1为优秀，0.95-1为良好，<0.95不合格。

3. 按列排序

• 增加两个计算列月份2、季度2

计算列	所属表	公式
月份2	日期表	MONTH('日期表'[日期])
季度2	日期表	SWITCH('日期表'[月份2],1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4)

• 将月份设置为按照月份2排序，季度设置为按照季度2排序。

4. 隐藏列

隐藏不需要的列，最终的模型关系图视图见下图。

该项目数据分析部分结束。请阅读该项目其他数据分析步骤的文章。

• Part1-某零售公司2015年销售情况分析项目 (分析设计、数据收集、数据处理)
• Part3-某零售公司2015年销售情况分析项目 (数据可视化、数据结论)