超市销售数据分析

yangll_pd

已于 2022-05-15 21:59:58 修改

阅读量5.8k

点赞数 8

文章标签：数据分析 mysql python

于 2022-05-15 10:11:25 首次发布

本文链接：https://blog.csdn.net/weixin_64723763/article/details/124770660

版权

一、项目背景与方法

1. 背景描述

近年来，随着新零售业的快速发展，消费者购买商品时有了更多的对比和选择，导致超市行业的竞争日益激烈，利润空间不断压缩。超市的经营管理产生了大量数据，对这些数据进行分析，可以提升超市的竞争力，为超市的运营及经营策略调整提供重要依据。

2. 数据来源及介绍

ModelWhale平台上泰迪杯”数据分析大赛A题超市销售数据分析。为某超市 2015 年 1 月 1 日至 4 月 30 的经营数据。

| 列名 | 说明 |
| ------- | -------|
| 顾客编码 | 整数类型 |
| 大类编码 | 整数类型 |
| 大类名称 | 字符串 |
| 中类编码 | 整数类型 |
| 中类名称 | 字符串 |
| 小类编码 | 整数类型 |
| 小类名称 | 字符串 |
| 中类编码 | 整数类型 |
| 销售日期 | 时间类型 |
| 销售月份 | 时间类型 |
| 商品编码 | 字符串 |
| 规格型号 | 字符串 |
| 商品类型 | 字符串 |
| 单位 | 字符串 |
| 销售数量 | 整数类型 |
| 销售金额 | 浮点类型 |
| 商品单价 | 浮点类型 |
| 是否促销 | 否 |

3. 问题描述

* 对销售数据进行统计分析
* 分析顾客的消费行为。
* 研究促销对销售的影响。

4. 技术方法

（1）使用SQL进行数据加工，筛选
（2）使用python完成可视化

二、数据导入

1. 使用Navicat导入原始数据

键过程如下：

成功将全部42816条数据导入

2. 导入相关库

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import warnings
import plotly
import kaleido
import plotly.graph_objects as go
from IPython.display import HTML

#解决中文字体不显示，正负号不显示问题
plt.rcParams['font.family'] = ['sans-serif']
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

%matplotlib widget

warnings.filterwarnings('ignore')

三、数据清洗

1. 删除重复数据

(1) 查看是否有重复值

SELECT (SELECT count(*) FROM salesdata)-(SELECT count(*) FROM (SELECT DISTINCT * FROM salesdata) AS A) as  重复记录数量

从查询结果可知，数据中有3重复记录。

(2) 删除重复数据

CREATE table salesdatas SELECT DISTINCT * FROM salesdata;
DROP TABLE salesdata
ALTER TABLE salesdatas RENAME TO salesdata

(3) 得到42813条无重复记录

SELECT count(*) FROM salesdata

2. 缺失值查询与处理

(1) 查看是否有缺失值(规格型号字段中有空格形式的数据，暂时按照非空值处理)

SELECT COUNT(顾客编号),COUNT(大类编码),COUNT(大类名称),COUNT(中类编码),COUNT(中类名称),COUNT(小类编码),
count(小类名称),count(销售日期),count(商品编码),count(规格型号),count(商品类型),count(单位),
count(销售数量),count(销售金额),count(商品单价),count(是否促销)
FROM salesdata

从查询结果来看，销售数量字段有两个缺失值

(2) 删除销售字段为空的记录

DELETE FROM salesdata WHERE 销售数量 is null

(3) 再次查看是否有缺失值

SELECT COUNT(顾客编号),COUNT(大类编码),COUNT(大类名称),COUNT(中类编码),COUNT(中类名称),COUNT(小类编码),
count(小类名称),count(销售日期),count(商品编码),count(规格型号),count(商品类型),count(单位),
count(销售数量),count(销售金额),count(商品单价),count(是否促销)
FROM salesdata

3. 异常值查询与处理

销售日期：2015年2月29日，两条记录
(1) 删除异常数据

DELETE FROM salesdata WHERE 销售日期='20150229';

(2) 查看数值型数据的最小值

SELECT min(顾客编号),min(大类编码),min(中类编码),min(小类编码),
min(销售数量),min(销售金额),min(商品单价)
FROM salesdata

从查询结果可以看出，销售金额和销售数量有负值

(3) 查看销售数量小于零的记录数量及占比

SELECT count(1) as 销售数量小于零的记录数量 ,count(1)/(select count(1) FROM salesdata ) as 销售数量小于零的记录占比
FROM salesdata where 销售数量<0

(3) 查看销售金额小于零的记录数量及占比

SELECT count(1) as 销售金额小于零的记录数量 ,count(1)/(select count(1) FROM salesdata ) as 销售金额小于零的记录占比
FROM salesdata where 销售金额<0

(4) 异常数据占约0.2，删除销售数量和销售金额小于0的异常值

DELETE FROM salesdata WHERE 销售数量<0;
DELETE FROM salesdata WHERE 销售金额<0;

4. 日期数据格式转换

(1) 删除多余数据：销售月份

ALTER TABLE salesdata DROP COLUMN 销售月份;

(2) 修改销售日期的为时间格式

ALTER TABLE salesdata MODIFY COLUMN 销售日期 date;

(3) 查看清洗后的数据条目数:42721

SELECT count(1) FROM salesdata；

四、基于商品维度分析

1. 日销售规律探索

每日顾客总数
每日销售总数
每日销售金额总数

（1）筛选日销售数据。注意：一天同一个客户的所有购买记录算作一个购物篮（一个销售单）

SELECT 销售日期 ,count(distinct 顾客编号) 顾客人数,
    sum(销售数量) 商品销量,sum(销售金额) 销售金额
FROM salesdata
GROUP BY 销售日期

（2）日销售规律可视化

#将sql加工的数据保存并可视化
df = pd.read_clipboard()
df.to_csv('./data1.csv',index=False)
data1 = pd.read_csv('./data1.csv').set_index('销售日期')
def figure1(data):
    fig = plt.figure(figsize=(8,6))
    axe = fig.add_subplot(111)
    axe.set_title('日销售规律图')
    axe.set_ylabel('数量')
    axe.plot(data.index,data['销售金额'],'b-',alpha=0.7,label='销售金额')
    axe.plot(data.index,data['商品销量'],'m-',alpha=0.7,label='商品销量')  
    plt.legend()
    axe1 = axe.twinx()
    axe1.set_ylabel('顾客人数')
    axe1.plot(data.index,data['顾客人数'],'r-',alpha=0.7,label='顾客人数') 
    plt.legend(loc=7)
    plt.xticks(range(1,116)[::15],data.index.to_list()[::15])
figure1(data1)

（3）异常数据点筛选

SELECT *
FROM
        (
        SELECT 销售日期 ,count(distinct 顾客编号) 顾客人数,
                sum(销售数量) 商品销量,sum(销售金额) 销售金额
        FROM salesdata
        GROUP BY 销售日期
        ) as a
WHERE 顾客人数<20

异常数据为2015年2月4日，当天周三，不是法定假日，却只有一个顾客

2. 周销售规律探索

（1）筛选周销售数据
#从日销售规律可以看出，节假日对销量影响比较大，为了探究日常每周销售规律，需剔除节假日的影响

SELECT dayofweek(销售日期) as 周,sum(顾客人数) 顾客人数,
    sum(商品销量) 商品销量,sum(销售金额) 销售金额
FROM(
        SELECT 销售日期 ,count(distinct 顾客编号) 顾客人数,
                sum(销售数量) 商品销量,sum(销售金额) 销售金额
        FROM salesdata
        GROUP BY 销售日期
        ) as a
WHERE 销售日期 not in ('2015-1-1','2015-2-11','2015-2-12','2015-2-13','2015-2-14','2015-2-15','2015-2-16'
                   ,'2015-2-17','2015-2-18','2015-2-19','2015-2-20','2015-2-21','2015-2-22','2015-2-23'
                   ,'2015-2-24','2015-3-5','2015-3-8','2015-4-5')
GROUP BY 周;

（2）可视化周销售规律

df = pd.read_clipboard()
#df.to_csv('./data2.csv',index=False)
data2 = pd.read_csv('./data2.csv').set_index('星期')
def figure(data):
    fig = plt.figure()
    axe = fig.add_subplot(111)
    axe.set_title('周销售规律图')
    axe.set_ylabel('数量')
    axe.plot(data.index,data['销售金额'],'b-',alpha=0.7,label='销售金额')
    axe.plot(data.index,data['商品销量'],'m-',alpha=0.7,label='商品销量')  
    plt.legend()
    axe1 = axe.twinx()
    axe1.set_ylabel('销售单数量')
    axe1.plot(data.index,data['顾客人数'],'r-',alpha=0.7,label='销售单数量') 
    plt.legend(loc=7)
figure(data2)

(3) 结论：

周三的销量最好，可用来进行促销活动，或者设定会员日
周四相对最差，可以挑选销售最差的周四送货、盘点货架等，也可通过设定会员日、促销等手段改善销售业绩

3. 贡献度分析

累计贡献率（%）=（累计销售收入/销售总收入）*100%

(1) 数据筛选

SELECT 大类名称,销售金额, @累计金额:=(销售金额+@累计金额), round(@累计金额/(SELECT sum(销售金额) FROM salesdata),2) 累计贡献率
FROM (
        SELECT 大类名称,sum(销售金额) 销售金额
        FROM salesdata
        GROUP BY 大类名称
        ORDER BY 销售金额 DESC
) as a ,(SELECT @累计金额:=0) as b

（2）可视化

df = pd.read_clipboard()
df.to_csv('./data5.csv',index=False)
data5 = pd.read_csv('./data5.csv')
line = go.Scatter(x=data5['大类名称'],y=data5['累计贡献率'],name='累计贡献率',yaxis='y2',
                  marker=dict(size=8,color='blue'))
bar = go.Bar(x=data5['大类名称'],y=data5['销售金额'],text=data5['销售金额'],
             textposition='outside',name='销售金额',marker_color='rgb(200, 18, 100)')
layout = go.Layout(title = '产品贡献度分析',title_x=0.5,
                   xaxis_title = '大类名称',yaxis_title = '销售金额',yaxis2=dict(title='累计贡献率',overlaying='y',side='right'))

fig = go.Figure([line,bar],layout=layout)
fig.add_annotation(text="78%",x="酒饮", y=61213, arrowhead=1, showarrow=True,arrowcolor='red')
HTML(fig.to_html())

（3）结论：日配、蔬果、休闲、粮油、酒饮这五大类商品贡献了将近80%的销售额

4. 人气商品分析

指定商品购物篮系数=某段时间含指定商品购物篮的销售总数/某段时间含指定商品购物篮总数

指定商品的人气指数=某段时间含指定商品购物篮的销售总数/某段时间购物篮总数

购物篮系数：顾客平均购买数量

销售单总量（购物篮数量）：每日同一个客户购买的所有商品为一个销售单

(1)从大类的角度来分析人气类别
#大类的总数：15

SELECT count(distinct 大类编码 ),count(DISTINCT 大类名称)
FROM salesdata

(2) 筛选并构建数据表

CREATE VIEW view1
AS
SELECT 大类名称,sum(含指定大类的销售数量) 含指定大类的销售数量,sum(含指定大类的销售单数) 含指定大类的销售单数,
			round(sum(含指定大类的销售数量)/sum(含指定大类的销售单数),4) as 购物篮系数,
			round(sum(含指定大类的销售数量)/( SELECT count(顾客编号)  总购物篮数
											FROM(
												SELECT 顾客编号,销售日期
												FROM salesdata
												GROUP BY 顾客编号,销售日期
											    ORDER BY 顾客编号,销售日期
											    ) as b)
											,4
						) as 人气指数
FROM(
		SELECT 顾客编号,销售日期,大类名称,avg(含指定大类的销售数量) 含指定大类的销售数量,
						avg(含指定大类的销售单数) 含指定大类的销售单数
		FROM
				(SELECT 顾客编号,销售日期,大类名称,
							sum(销售数量) over(PARTITION by 顾客编号,销售日期 ) 含指定大类的销售数量,
							dense_rank() over (PARTITION by 顾客编号,销售日期 ) 含指定大类的销售单数
				FROM salesdata
				ORDER BY 顾客编号,销售日期
				) as aa
		GROUP BY 顾客编号,销售日期,大类名称
) as a 
GROUP BY 大类名称
ORDER BY 人气指数 DESC

（3）可视化数据

df = pd.read_clipboard()
df.to_csv('./data3.csv',index=False)
data3 = pd.read_csv('./data3.csv')
def figure3(data):
    fig = plt.figure()
    axe = fig.add_subplot(111)
    axe.set_title('大类的购物篮数量-购物篮系数四象限图')
    axe.set_ylabel('含指定大类的销售单数')
    axe.set_xlabel('购物篮系数')
    axe.scatter(data['购物篮系数'],data['含指定大类的销售单数']) 
    plt.axhline(y=data['含指定大类的销售单数'].mean(),ls="-",color="#d62728",linewidth=1)#y=0        表示水平线过y=0，ls设置线条类型，linewidth设置线条粗细
    plt.axvline(x=data['购物篮系数'].mean(),ls="-",color="#d62728",linewidth=1)
    plt.annotate('红线为均值线',xy=(1,50),xytext=(1.2,50),xycoords='data',
               )

# 第一象限（右上角）：购物篮系数和购物篮数量都比较高，属于人气商品
# 第二象限（左上角）：购物篮数量比较高，但购物篮系数低于平均值，这部分商品需要解决的是如果提高它们的关联销售
# 第三象限（左下角）：购物篮系数和购物篮数量都比较低，基本属于边缘商品，本身卖的不好也与其他商品关联度不高
# 第四象限（右下角）：购物篮系数比较高，购物篮数量低于平均值，这部分商品需要解决的是如果提高它们的购物篮数量

（4）处于各象限的类别筛选

#处于第一象限的大类名单:粮油,日配
SELECT 大类名称 
FROM view1
WHERE 含指定大类的销售单数>=(SELECT avg(含指定大类的销售单数) FROM view1)
AND 购物篮系数>=(SELECT avg(购物篮系数) FROM view1)
#处于第二象限的大类名单:蔬果,休闲,洗化
SELECT 大类名称 
FROM view1
WHERE 含指定大类的销售单数>=(SELECT avg(含指定大类的销售单数) FROM view1)
AND 购物篮系数<(SELECT avg(购物篮系数) FROM view1)
#处于第三象限的大类名单:肉禽,熟食,水产,家电,烘焙
SELECT 大类名称 
FROM view1
WHERE 含指定大类的销售单数<(SELECT avg(含指定大类的销售单数) FROM view1)
AND 购物篮系数<(SELECT avg(购物篮系数) FROM view1)
#处于第四象限的大类名单:酒饮,冲调,家居,针织,文体
SELECT 大类名称 
FROM view1
WHERE 含指定大类的销售单数<(SELECT avg(含指定大类的销售单数) FROM view1)
AND 购物篮系数>=(SELECT avg(购物篮系数) FROM view1)

（5）挑选销量前5的大类分析其人气指数：指定商品人气指数=某段时间含指定商品购物篮的销售总数/某段时间的购物篮总数

CREATE VIEW view2
AS
SELECT *
FROM (
		SELECT 大类名称,销售数量,row_number()over(ORDER BY 销售数量 DESC) as rank1
		FROM 
		(
				SELECT 大类名称,sum(销售数量) 销售数量
				FROM salesdata
				GROUP BY 大类名称
		) as a
) as tempp
WHERE rank1<=5

SELECT view1.大类名称,购物篮系数,人气指数,销售数量
FROM view1
JOIN view2
ON view1.大类名称=view2.大类名称

5. 商品的关联程度分析

品类间的关联
* 选择销量前5的大类进行关联度分析:日配蔬果粮油休闲酒饮

#支持度、可信度、提升度
# 支持度：同时包含A和B的交易/总交易*100%
# 可信度：同时包含A和B的交易/包含A的总交易*100%
# 提升度：可信度/商品B在总交易中出现的概率

（1）创建临时表view3，计算销量前5的商品大类关联交易数量

CREATE VIEW view3
AS
SELECT 商品大类,sum(总交易数) 总交易数,sum(蔬果) 同时购买蔬果,sum(日配) 同时购买日配,
sum(粮油) 同时购买粮油,sum(休闲) 同时购买休闲,
sum(酒饮) 同时购买酒饮
FROM(
		SELECT  顾客编号,销售日期,
		'蔬果' as 商品大类,1 as 总交易数,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'蔬果')<>0 then 1 else 0 end) 蔬果  ,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'蔬果')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'日配')<>0 then 1 else 0 end)日配,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'蔬果')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'粮油')<>0 then 1 else 0 end)粮油,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'蔬果')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'休闲')<>0 then 1 else 0 end)休闲,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'蔬果')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'酒饮')<>0 then 1 else 0 end)酒饮
		FROM salesdata
		GROUP BY 顾客编号,销售日期
) as a 
GROUP BY 商品大类
UNION 
SELECT 商品大类,sum(总交易数) 总交易数,sum(蔬果) 蔬果,sum(日配) 日配,
sum(粮油) 粮油,sum(休闲) 休闲,
sum(酒饮) 酒饮
FROM(
		SELECT  顾客编号,销售日期,
		'日配' as 商品大类,1 as 总交易数,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'日配')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'蔬果')<>0 then 1 else 0 end) 蔬果,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'日配')<>0 then 1 else 0 end) 日配  ,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'日配')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'粮油')<>0 then 1 else 0 end) 粮油,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'日配')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'休闲')<>0 then 1 else 0 end) 休闲,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'日配')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'酒饮')<>0 then 1 else 0 end) 酒饮
		FROM salesdata
		GROUP BY 顾客编号,销售日期
) as a 
GROUP BY 商品大类
UNION 
SELECT 商品大类,sum(总交易数) 总交易数,sum(蔬果) 蔬果,sum(日配) 日配,
sum(粮油) 粮油,sum(休闲) 休闲,
sum(酒饮) 酒饮
FROM(
		SELECT  顾客编号,销售日期,
		'粮油' as 商品大类,1 as 总交易数,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'粮油')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'蔬果')<>0 then 1 else 0 end) 蔬果,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'粮油')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'日配')<>0 then 1 else 0 end) 日配,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'粮油')<>0 then 1 else 0 end) 粮油  ,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'粮油')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'休闲')<>0 then 1 else 0 end) 休闲,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'粮油')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'酒饮')<>0 then 1 else 0 end) 酒饮
		FROM salesdata
		GROUP BY 顾客编号,销售日期
) as a 
GROUP BY 商品大类
UNION 
SELECT 商品大类,sum(总交易数) 总交易数,sum(蔬果) 蔬果,sum(日配) 日配,
sum(粮油) 粮油,sum(休闲) 休闲,
sum(酒饮) 酒饮
FROM(
		SELECT  顾客编号,销售日期,
		'休闲' as 商品大类,1 as 总交易数,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'休闲')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'蔬果')<>0 then 1 else 0 end) 蔬果,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'休闲')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'日配')<>0 then 1 else 0 end) 日配,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'休闲')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'粮油')<>0 then 1 else 0 end) 粮油,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'休闲')<>0 then 1 else 0 end) 休闲  ,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'休闲')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'酒饮')<>0 then 1 else 0 end) 酒饮
		FROM salesdata
		GROUP BY 顾客编号,销售日期
) as a 
GROUP BY 商品大类
UNION 
SELECT 商品大类,sum(总交易数) 总交易数,sum(蔬果) 蔬果,sum(日配) 日配,
sum(粮油) 粮油,sum(休闲) 休闲,
sum(酒饮) 酒饮
FROM(
		SELECT  顾客编号,销售日期,
		'酒饮' as 商品大类,1 as 总交易数,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'酒饮')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'蔬果')<>0 then 1 else 0 end) 蔬果,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'酒饮')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'日配')<>0 then 1 else 0 end) 日配,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'酒饮')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'粮油')<>0 then 1 else 0 end) 粮油,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'酒饮')<>0 and instr(GROUP_CONCAT(DISTINCT 大类名称),'休闲')<>0 then 1 else 0 end) 休闲,
		(case when instr(GROUP_CONCAT(DISTINCT 大类名称),'酒饮')<>0 then 1 else 0 end) 酒饮 
		FROM salesdata
		GROUP BY 顾客编号,销售日期
) as a 
GROUP BY 商品大类

（2）创建临时表view4，计算销量前5的商品大类在总交易中出现的的概率

CREATE VIEW view4
AS
SELECT 商品大类,(case when  商品大类='蔬果' then 同时购买蔬果/总交易数
					when 商品大类='日配' then 同时购买日配/总交易数
					when 商品大类='粮油' then 同时购买粮油/总交易数
					when 商品大类='休闲' then 同时购买休闲/总交易数
					when 商品大类='酒饮' then  同时购买酒饮/总交易数 
					end ) 各商品在总交易中出现的概率
FROM view3;

（3）筛选并生成支持度表

SELECT 商品大类,同时购买蔬果/总交易数 同时购买蔬果,同时购买日配/总交易数 同时购买日配 ,同时购买粮油/总交易数 同时购买粮油,
同时购买休闲/总交易数 同时购买休闲, 同时购买酒饮/总交易数 同时购买酒饮
FROM view3

（4）筛选并生成可信度表

CREATE VIEW kexingdu
AS
SELECT 商品大类,
	(case when 商品大类='蔬果' then 1
	      when 商品大类='日配' then 同时购买蔬果/同时购买日配
				when 商品大类='粮油' then 同时购买蔬果/同时购买粮油
				when 商品大类='休闲' then 同时购买蔬果/同时购买休闲
				when 商品大类='酒饮' then 同时购买蔬果/同时购买酒饮
end ) 同时购买蔬果,
(case when 商品大类='日配' then 1 
	      when 商品大类='蔬果' then 同时购买日配/同时购买蔬果
				when 商品大类='粮油' then 同时购买日配/同时购买粮油
				when 商品大类='休闲' then 同时购买日配/同时购买休闲
				when 商品大类='酒饮' then 同时购买日配/同时购买酒饮
end ) 同时购买日配,
(case when 商品大类='粮油' then 1
	      when 商品大类='蔬果' then 同时购买粮油/同时购买蔬果
				when 商品大类='日配' then 同时购买粮油/同时购买日配
				when 商品大类='休闲' then 同时购买粮油/同时购买休闲
				when 商品大类='酒饮' then 同时购买粮油/同时购买酒饮
end ) 同时购买粮油,
(case when 商品大类='休闲' then 1 
	      when 商品大类='蔬果' then 同时购买休闲/同时购买蔬果
				when 商品大类='粮油' then 同时购买休闲/同时购买粮油
				when 商品大类='日配' then 同时购买休闲/同时购买日配
				when 商品大类='酒饮' then 同时购买休闲/同时购买酒饮
end ) 同时购买休闲,
(case when 商品大类='酒饮' then 1 
	      when 商品大类='蔬果' then 同时购买酒饮/同时购买蔬果
				when 商品大类='粮油' then 同时购买酒饮/同时购买粮油
				when 商品大类='休闲' then 同时购买酒饮/同时购买休闲
				when 商品大类='日配' then 同时购买酒饮/同时购买日配
end ) 同时购买酒饮
FROM view3;

（5）筛选并生成提升度表

SELECT 商品大类, 同时购买蔬果/(SELECT 各商品在总交易中出现的概率 FROM view4 WHERE 商品大类='蔬果' ) as 同时购买蔬果,
 同时购买日配/(SELECT 各商品在总交易中出现的概率 FROM view4 WHERE 商品大类='日配' ) as 同时购买日配,
 同时购买粮油/(SELECT 各商品在总交易中出现的概率 FROM view4 WHERE 商品大类='粮油' ) as 同时购买粮油,
 同时购买休闲/(SELECT 各商品在总交易中出现的概率 FROM view4 WHERE 商品大类='休闲' ) as 同时购买休闲,
 同时购买酒饮/(SELECT 各商品在总交易中出现的概率 FROM view4 WHERE 商品大类='酒饮' ) as 同时购买酒饮
FROM kexingdu

（6）结论：综合支持度、可信度、提升度，销量前五的大类中酒饮和休闲的关联性强，可考虑捆绑销售，或者在货架陈列时放在一起

五、 RFM模型分析

R：客户最近一次消费距离当前日期天数（当前日期为数据中的最晚日期）
F：客户在某段时间的消费频率
M：客户在某段时间的消费总金额

（1）提取数据中的最晚日期:2015-04-30

SELECT max(`销售日期`) FROM salesdata

（2）创建RFM表

CREATE VIEW RFMtable
AS
SELECT 顾客编号,TIMESTAMPDIFF(day,max(日期),(SELECT max(销售日期) FROM salesdata)) R,
count(日期) F,SUM(M) M
FROM(
		SELECT `顾客编号`,`销售日期`,max(销售日期) 日期,sum(`销售金额`) M
		FROM salesdata
		GROUP BY `顾客编号`,`销售日期`
		ORDER BY `顾客编号`,`销售日期`
) as a 
GROUP BY 顾客编号

（2）建立打分标准

给每个客户的RFM值打分,分组，并统计各类价值用户的平均r,f,m
r_s:R值大于等于R的均值，计为1，小于R的均值，计为2
f_s:F值大于等于F的均值，计为2，小于F的均值，计为1
m_s:M值大于等于M的均值，计为2，小于M的均值，计为1

（3）用户细化并分类统计

SELECT label ,count(顾客编号) 人数,round(std(R),2) 平均R,round(std(F),2) 平均F,round(std(M),2) 平均M
FROM (
		SELECT *,(case when concat(r_s,f_s,m_s)='111' then '一般挽留客户'
									 when concat(r_s,f_s,m_s)='112' then '重要挽留客户'
									 when concat(r_s,f_s,m_s)='121' then '一般保持客户'
									 when concat(r_s,f_s,m_s)='122' then '重要保持客户'
									 when concat(r_s,f_s,m_s)='211' then '一般发展客户'
									 when concat(r_s,f_s,m_s)='212' then '重要发展客户'
									 when concat(r_s,f_s,m_s)='221' then '一般价值客户'
									 when concat(r_s,f_s,m_s)='222' then '重要价值客户'
						end ) label
		FROM(
				SELECT *,( case when R>=(SELECT std(R) FROM RFMtable) THEN 1 else 2 END) r_s,
				( case when F>=(SELECT std(F) FROM RFMtable) THEN 2 else 1 END) f_s,
				( case when M>=(SELECT std(M) FROM RFMtable) THEN 2 else 1 END) m_s
				FROM RFMtable
		) as a 
) as b
GROUP BY label

（4）结论：

对不同价值用户采用不同的营销策略，比如，重要价值客户：提供高品质的会员服务、一般挽留客户：增加推送，激活用户

六、促销分析

（1）筛选每次购物是否购买促销品，并对客户进行分类

CREATE VIEW cuxiao_kehu
AS
SELECT 顾客编号,消费次数,购买过促销消费占比,
	 (case when 购买过促销消费占比=0 then '不喜欢促销品'
	 when 购买过促销消费占比>0 and 购买过促销消费占比<0.5  then '喜欢促销品'
	 when 购买过促销消费占比>=0.5  then '非常喜欢促销品'
	 end ) 喜欢促销品的程度
FROM(
		SELECT 顾客编号,count(销售日期) 消费次数 ,round(sum(case when 是否购买促销<>0 then 1 else 0 end )/count(销售日期),2) 购买过促销消费占比
		FROM (
				SELECT`顾客编号`,`销售日期`,
				sum(case when 是否促销='是' then 1 else 0 end) 是否购买促销
				FROM salesdata
				GROUP BY `顾客编号`,`销售日期`
				ORDER BY  `顾客编号`,`销售日期`
		) as a 
		GROUP BY 顾客编号
) as b

（2）统计对促销喜欢程度不同的客户数量

SELECT 喜欢促销品的程度,count(顾客编号)
FROM cuxiao_kehu
GROUP BY  喜欢促销品的程度

（3）筛选每天曾参加促销小类的占比与整体的销售金额的关系数据

SELECT 销售日期,round(sum(case when 是否促销='是' then 1 else 0 end )/count(小类名称),2) 每日促销的小类种类占比,sum(销售金额) 销售金额
FROM(
		SELECT 销售日期,小类名称,是否促销,sum(销售金额) 销售金额
		FROM salesdata
		GROUP BY 销售日期,小类名称,是否促销
) as a
GROUP BY 销售日期

（4）可视化

df = pd.read_clipboard()
df.to_csv('./data4.csv',index=False)
data4 = pd.read_csv('./data4.csv').set_index('销售日期')
fig = plt.figure()
axe = fig.add_subplot(111)
axe.plot(data4.index,data4['销售金额'],label='销售金额')
axe.set_ylabel('销售金额')
plt.legend(loc=3)
axe1 = axe.twinx()
axe1.set_ylabel('每日促销的小类种类占比')
axe1.plot(data4.index,data4['每日促销的小类种类占比'],'r--',label='每日促销的小类种类占比')
plt.xticks(data4.index[::15])
plt.legend()

（5）结论：销售金额在一定程度上与促销有关

yangll_pd

关注

8
点赞
踩
87

收藏

觉得还不错? 一键收藏
2
评论
超市销售数据分析

一、项目背景与方法1. 背景描述近年来，随着新零售业的快速发展，消费者购买商品时有了更多的对比和选择，导致超市行业的竞争日益激烈，利润空间不断压缩。超市的经营管理产生了大量数据，对这些数据进行分析，可以提升超市的竞争力，为超市的运营及经营策略调整提供重要依据。2. 数据来源及介绍ModelWhale平台上泰迪杯”数据分析大赛A题超市销售数据分析。某超市 2015 年 1 月 1 日至 4 月 30 的经营数据。
复制链接

扫一扫