电商销售数据分析实例
有两份CSV表格数据【主流电商订单类API接口】，表一为部分的商城订单数据（已脱敏处理）（shape=220*16），包含了订单号、客户id、客户性别、订单创建时间、商品id、商品父类、购买数量、价格信息、是否成交代码、支付时间、订单的省份和城市代码以及购买会员等级；表二为全国省份及下辖区、城市代码及其对应的中文名称数据（shape=458*5）。数据预览如下：
| order_id | user_id | gender | 订单时间 | 商品id | 第一品类 | 购买数量 | 优惠前售价 | 优惠后售价 | 实际售价 | 是否成交 | 支付时间 | 优惠金额 | city_id | province_id | 会员等级 |
| 39858 | 72ef | 0 | 19:00:02 | 10113 | 甲类 | 1 | 36 | 31 | 31 | 0 | 19:14:51 | 2 | 1657 | 19 | 3 |
| 38196 | aadca7f | 0 | 22:52:31 | 11321 | 丙类 | 1 | 289 | 238 | 249 | 0 | 22:53:16 | 40 | 1983 | 22 | 2 |
| 38056 | sc99 | 1 | 16:38:13 | 20211 | 乙类 | 1 | 369 | 350 | 339 | 1 | 19:38:38 | 19 | 1954 | 22 | 3 |
| 38023 | hucheng | 1 | 19:34:25 | 10113 | 甲类 | 1 | 36 | 31 | 31 | 0 | 20:34:25 | 5 | 1874 | 21 | 1 |
| city | city_name | province | province_name | city_level |
| 72 | 朝阳区 | 1 | 北京 | 一线 |
| 78 | 黄浦区 | 2 | 上海 | 一线 |
| 113 | 万州区 | 4 | 重庆 | 二线 |
| 114 | 涪陵区 | 4 | 重庆 | 二线 |
| 115 | 梁平区 | 4 | 重庆 | 二线 |
1.将数据导入数据库
1.1 将订单数据及省份数据两个csv文件导入数据库，表名分别命名为order_info和city
load data infile 'D:\\MySQL\\work_order_utf8.csv' into table work.order_info character set utf8 fields terminated by ',' lines terminated by '\r\n' ignore 1 lines;load data infile 'D:\\MySQL\\work_city_utf8.csv' into table work.city character set utf8 fields terminated by ',' lines terminated by '\r\n' ignore 1 lines;
1.2【支付时间】、【订单时间】列数据由字符串改为时间格式
ALTER TABLE order_info MODIFY COLUMN 支付时间 time(0);
ALTER TABLE order_info MODIFY COLUMN 订单时间 time(0);
1.3 查看缺失值和重复值
SELECT COUNT(*),COUNT(是否成交),COUNT(支付时间),COUNT(city_id)
FROM order_info;
select * from order_info
group by order_id,user_id,gender,订单时间,商品id,第一品类,购买数量,优惠前售价,优惠后售价,实际售价,是否成交,支付时间,优惠金额,city_id,province_id,会员等级
having count(*)>1;
结果如下
可知支付时间列存在空值，且订单数据不存在重复值。
1.4 添加每个订单的总金额，及提取【支付时间】列数据的小时数据到新建的【total】、【hours】两列
ALTER TABLE order_info ADD COLUMN total int(10);
UPDATE order_info SET total = 实际售价*购买数量;
ALTER TABLE order_info ADD COLUMN hours INT(10);
UPDATE order_info SET hours=DATE_FORMAT(支付时间,'%H')
1.5将order_info和city联立并保存为表order_city
CREATE TABLE order_city
AS
SELECT * FROM (
SELECT i.order_id,user_id,gender,订单时间,商品id,第一品类,购买数量,优惠前售价,优惠后售价,实际售价,是否成交,支付时间,hours,优惠金额,total,会员等级,c.city_name,province_name,city_level
FROM order_info i
LEFT JOIN city c ON i.city_id=c.city AND i.province_id=c.province)demo;
清洗实际售价=0，支付时间为NULL和是否成交为0的数据，保存清洗后的表为order_data
CREATE TABLE order_data
AS SELECT * FROM order_city;
DELETE FROM order_data WHERE 实际售价=0 ;
DELETE FROM order_data WHERE 是否成交=0 or 支付时间 IS NULL;
SELECT * FROM order_data;2.Python连接数据库
2.1 导入依赖包
import pandas as pd import numpy as np import seaborn as sns import matplotlib.pyplot as plt import pymysql %matplotlib inline plt.style.use('ggplot') import warnings warnings.filterwarnings('ignore')from pyecharts import options as opts from pyecharts.charts import Map,Bar, Line, Page,Pie, Boxplot,Scatter from pyecharts.globals import ChartType, SymbolType,ThemeType plt.rcParams["font.sans-serif"] = ["SimHei"] plt.rcParams["font.serif"] = ["SimHei"] plt.rcParams["axes.unicode_minus"] = False
2.2 连接数据库
conn = pymysql.connect(
host='localhost', user='root',
passwd='248359', db='work', charset='utf8')
cur = conn.cursor()2.3 获取数据库数据
sql_order_info = 'SELECT * FROM order_info'
sql_order_city = 'SELECT * FROM order_city'
sql_order_data = 'SELECT * FROM order_data'
df_order_info = pd.read_sql(sql=sql_order_info,con=conn)
df_order_city = pd.read_sql(sql=sql_order_city,con=conn)
df_order_data = pd.read_sql(sql=sql_order_data,con=conn)
display(df_order_info.head(),df_order_city.head(),df_order_data.head())
三张表预览结果如下：
3.时间维度
3.1 每个时间段订单分布
sql_hours = 'SELECT hours,COUNT(order_id) FROM order_info GROUP BY hours'
sql_hours_valid = 'SELECT hours,COUNT(order_id) FROM order_data GROUP BY hours'
df_hours_valid=pd.read_sql(sql=sql_hours_valid,con=conn)
df_hours=pd.read_sql(sql=sql_hours,con=conn)
def hours():
fig = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.ROMA,chart_id=1))
.add_xaxis(list(df_hours_valid['hours']))
.add_yaxis("所有订单",list(df_hours['COUNT(order_id)'][1:]))
.add_yaxis("有效订单", list(df_hours_valid['COUNT(order_id)']), markline_opts=5000)
.set_global_opts(title_opts=opts.TitleOpts(title="每小时订单量", subtitle="所有订单和有效订单数", pos_left='5%'),
legend_opts=opts.LegendOpts(type_="scroll", pos_left="50%", orient="vertical"))#设置图例位置
)
x=['无效订单','有效订单']
y=[int(df_order_info['order_id'].count())-int(df_order_data['order_id'].count()),int(df_order_data['order_id'].count())]
pie = (
Pie(init_opts=opts.InitOpts(theme=ThemeType.ROMA))
.add('', [list(z) for z in zip(x, y)],
radius=["12%", "25%"], #控制内外半径
center=["60%", "35%"]) #饼图中心的位置
.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}%: {d}%"))
)
return fig.overlap(pie)
结果如下：
由图可知绝大部分订单主要分布在中午以及晚上19点至凌晨12点两个时间段。且订单的总转化率为59.55%。在历史数据量允许的条件下，可进一步分析各商品的订单转化率，对各商品设定合理的折扣力度进而提高订单转化率。
4.区域省份维度
4.1 订单省份分布
sql_province = 'SELECT province_name,COUNT(order_id) province_order_vol,SUM(total) FROM order_city GROUP BY province_name'
sql_province_valid = 'SELECT province_name,COUNT(order_id) province_order_vol_valid FROM order_data GROUP BY province_name'
df_province = pd.read_sql(sql=sql_province,con=conn)
df_province_valid = pd.read_sql(sql=sql_province_valid,con=conn)
df_province.iloc[0,0],df_province_valid.iloc[0,0]='未知','未知' #修改None省份数据为‘未知’
def map():
fig =(Map(init_opts=opts.InitOpts(chart_id=2))
.add("所有订单",[list(z) for z in zip(df_province['province_name'][1:].tolist(),df_province['province_order_vol'][1:].tolist())],maptype='china',is_map_symbol_show=True)
.add("有效订单",[list(z) for z in zip(df_province_valid['province_name'][1:].tolist(),df_province_valid['province_order_vol_valid'][1:].tolist())],maptype='china')
.set_global_opts(title_opts=opts.TitleOpts(title="各省订单分布"),visualmap_opts=opts.VisualMapOpts(max_=65, is_piecewise=False)) #is_piecewise=False改为分段型
)
return fig
结果如下：
由图可知订单流量主要集中在广东、四川、江西等省份，其中西北省份基本无流量导入。
4.2 各省订单流量和订单转化率以及客单价
结果如下：
可知流量前五身份分别是广东、四川、江西、湖南、山东，而客单价重庆和辽宁较高。湖北流量较高但是转化率偏低，可以针对性设置折扣力度以提高转化率。
4.3 各省有效订单帕累托图
df_province_volumn=df_province_vratio.drop([0])
df_province_volumn = df_province_volumn.sort_values(by='province_order_vol_valid',ascending=False)
df_province_volumn['vol_ratio_cumsum'] = (df_province_volumn['province_order_vol'].cumsum()/sum(df_province_volumn['province_order_vol'])).round(2)
def province_volumn():
sc = (
Scatter(init_opts=opts.InitOpts(theme=ThemeType.ROMA,chart_id=12))
.add_xaxis(list(df_province_volumn['province_name']))
.add_yaxis("有效订单量", list(df_province_volumn['province_order_vol_valid']),symbol_size=10)
.extend_axis(yaxis=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value}"), interval=0.2,splitline_opts=opts.SplitLineOpts(is_show=True)))
.set_global_opts(
# 设置x轴的label字体旋转角度
xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45)),
title_opts=opts.TitleOpts(title="各省有效订单数量和帕累托图"),
visualmap_opts=opts.VisualMapOpts(
type_='size',
max_=42,
min_=1,
pos_bottom = 50,pos_left = 10),)
)
line = (Line().
add_xaxis(list(df_province_volumn['province_name'])).
add_yaxis("累计订单占比", list(df_province_volumn['vol_ratio_cumsum']),yaxis_index=1,label_opts=opts.LabelOpts(is_show=False),
markpoint_opts=opts.MarkPointOpts(data=[opts.MarkPointItem(name="自定义标记点", coord=[list(df_province_volumn['province_name'])[5], list(df_province_volumn['vol_ratio_cumsum'])[5]], value=list(df_province_volumn['vol_ratio_cumsum'])[5])]) # 自定义显示的标签数据
))
return sc.overlap(line)
结果如下：
有效订单前六的省份累计订单数量超过了百分之八十，为业务的重点省份。
4.4 各省订单金额累计帕累托图
df_province_monetary = df_province_volumn.sort_values(by='SUM(total)',ascending=False)
df_province_monetary['SUM(total)_ratio_cumsum'] = (df_province_monetary['SUM(total)'].cumsum()/sum(df_province_monetary['SUM(total)'])).round(2)
def province_volumn_cum():
sc = (
Scatter(init_opts=opts.InitOpts(theme=ThemeType.ROMA,chart_id=12))
.add_xaxis(list(df_province_monetary['province_name']))
.add_yaxis("订单金额", list(df_province_monetary['SUM(total)']),symbol_size=10)
.extend_axis(yaxis=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value}"), interval=0.2,splitline_opts=opts.SplitLineOpts(is_show=True)))
.set_global_opts(
xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45)),
title_opts=opts.TitleOpts(title="各省订单金额累计帕累托图"),
visualmap_opts=opts.VisualMapOpts(
type_='size',
max_=18000,
min_=1,
pos_bottom = 50,pos_left = 10),)
)
line = (Line().
add_xaxis(list(df_province_monetary['province_name'])).
add_yaxis("累计金额占比", list(df_province_monetary['SUM(total)_ratio_cumsum']),yaxis_index=1,label_opts=opts.LabelOpts(is_show=False),
markpoint_opts=opts.MarkPointOpts(data=[opts.MarkPointItem(name="自定义标记点", coord=[list(df_province_monetary['province_name'])[4], list(df_province_monetary['SUM(total)_ratio_cumsum'])[4]], value=list(df_province_monetary['SUM(total)_ratio_cumsum'])[4])])
))
return sc.overlap(line)
province_volumn_cum().render_notebook()
结果如下：
可知前五大省份累计销售金额超过了总金额（GMV）的83%，是业务的核心省份，需要重点维护。
5.商品分群
5.1 商品分类销量和销售额
sql_sku_vol = 'SELECT 商品id,COUNT(order_id) sku_vol,SUM(total) sku_amount FROM order_info GROUP BY 商品id ORDER BY sku_vol'
df_sku_vol=pd.read_sql(sql=sql_sku_vol,con=conn)
#计算商品的平均订单量
sku_vol_average = df_sku_vol['sku_vol'].mean()
def sku_amount():
sku_amount = (
Scatter(init_opts=opts.InitOpts(theme=ThemeType.ROMA,chart_id=12,width="800px", height="460px"))
.add_xaxis(list(df_sku_vol['sku_vol']))
.add_yaxis("销量额", list(df_sku_vol['sku_amount']),label_opts=opts.LabelOpts(is_show=False),
markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(type_="average",name='平均销售额')]))
.set_global_opts(
xaxis_opts=opts.AxisOpts(name='销量',type_='value'), title_opts=opts.TitleOpts(title="商品销量和销售额"),
datazoom_opts=opts.DataZoomOpts(is_show=True),))
return sku_amount
sku_amount().render_notebook()
结果如下：
统计的商品销量和销售额如下：
| 商品id | sku_volumn | sku_amount |
| 10114 | 2 | 6187 |
| 30121 | 3 | 2799 |
| 10112 | 6 | 3988 |
| 20125 | 9 | 162 |
| 10125 | 9 | 12372 |
| 11321 | 9 | 3506 |
| 20122 | 10 | 1445 |
| 30113 | 10 | 2669 |
| 31235 | 17 | 829 |
| 20136 | 18 | 1866 |
| 20211 | 24 | 3472 |
| 31223 | 28 | 5591 |
| 10113 | 75 | 4062 |
各商品根据销量和销售额分成了四部分，分别是销量高销售额高、销量低销售额高、销量高销售额低、销量低销售额低四大类商品。
结合上面图表可知爆款产品sku10113销售额不多，定位是走销售量积攒人气，而sku10125、sku10114为高价值商品，占据大量的利润。还可根据商品是否为消耗品计算商品复购率。
5.2 各商品客户的性别偏好
sql_sku_gender = 'SELECT f.商品id,m.male_user,f.female_user FROM ((SELECT 商品id,COUNT(gender) female_user FROM order_info WHERE gender=0 GROUP BY 商品id) f INNER JOIN (SELECT 商品id,COUNT(gender) male_user FROM order_info WHERE gender=1 GROUP BY 商品id ) m ON f.商品id= m.商品id) ORDER BY male_user DESC'
df_sku_gender=pd.read_sql(sql=sql_sku_gender,con=conn)
def sku_gender():
sku_gender= (
Bar(init_opts=opts.InitOpts(theme=ThemeType.ROMA,chart_id=12))
.add_xaxis(list(df_sku_gender['商品id']))
.add_yaxis("男性", list(df_sku_gender['male_user']))
.add_yaxis("女性", list(df_sku_gender['female_user']))
.set_series_opts(label_opts=opts.LabelOpts(is_show=False))
.set_global_opts(title_opts=opts.TitleOpts(title="各商品客户的性别偏好"),legend_opts=opts.LegendOpts(pos_left="65%"),
#设置y轴宽分割线
yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value}"),is_scale=True,splitarea_opts=opts.SplitAreaOpts(is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)))))
x,y = ['男性','女性'],[int(df_sku_gender['male_user'].sum()),int(df_sku_gender['female_user'].sum())]
gender = (
Pie(init_opts=opts.InitOpts(theme=ThemeType.ROMA))
.add('', [list(z) for z in zip(x, y)],
radius=["12%", "25%"],
center=["60%", "35%"])
.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {d}%")))
return sku_gender.overlap(gender)
sku_gender().render_notebook()
结果如下：
客户主要为男性
6.客户分群
6.1 对客户购买频率及消费金额分群对用户购买次数、标准差及变异系数进行分析
display(df_user.mean(),df_user.std(),df_user.std()/df_user.mean())
结果如下：
由上表可知销售金额的变异系数较大，客户销售额数据之间异质性较明显，为了达到精准服务和精准推荐需要对用户进行合理分类，在数据量很大的情况下可以基于K-Means算法对客户进行聚类分析。
6.2客户购买频率及消费金额分群
sql_user = 'SELECT user_id,COUNT(order_id) user_frequency,SUM(total) user_monetary FROM order_info GROUP BY user_id ORDER BY user_frequency'
df_user = pd.read_sql(sql=sql_user,con=conn)
def user_monetary():
user_monetary = (
Scatter(init_opts=opts.InitOpts(theme=ThemeType.ROMA,chart_id=6,width="800px", height="450px"))
.add_xaxis(list(df_user['user_id']))
.add_yaxis("消费金额", list(df_user['user_monetary']),symbol_size=10,
markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(type_="average",name='平均消费金额')],))
.set_series_opts(label_opts=opts.LabelOpts(is_show=False))
.set_global_opts(
xaxis_opts=opts.AxisOpts(name = '客户'),
title_opts=opts.TitleOpts(title="客户消费金额及消费频率"),)
.extend_axis(yaxis=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value}"), interval=1))
)
user_frequency = (
Scatter(init_opts=opts.InitOpts(theme=ThemeType.ROMA))
.add_xaxis(list(df_user['user_id']))
.add_yaxis("消费次数", list(df_user['user_frequency']),symbol_size=10,yaxis_index=1,label_opts=opts.LabelOpts(is_show=False),
markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(type_="average",name='平均消费金额')],))
)
return user_monetary.overlap(user_frequency)
user_monetary().render_notebook()
结果如下：
该数据未包含日期和月份信息，故采用RFM模型进行分析时仅仅借助消费频率和消费金额进行客户分群，根据消费频率和消费金额的均值粗略分为四大类：
| 高消费金额高消费频率 | 重要价值用户 |
| 高消费金额低消费频率 | 重要发展客户 |
| 低消费金额高消费频率 | 一般发展用户 |
| 低消费金额低消费频率 | 流失客户 |
通过上述模型针对不同的客户群体采取不同的管理策略，并在促销活动中采用不同的促销策略。
7.总体概括与维度交叉分析
除了上述细分维度外，还需对总体概括进行描述统计：例如
-
订单总量
-
户数总数
-
GMV
-
销售额
更进一步的细分维度还需要进行维度交叉分析，例如省份维度与商品维度交叉分析各省份对商品的偏好程度，或者省份维度与客户维度交叉分析各省客单价情况；还比如可以对重要价值用户进一步挖掘，挖掘其账户是否为企业账户等各种分析方式。
8.输出图表报告为网页格式
page = Page()
page.add(hours(),map(),province_vratio(),province_volumn(),province_volumn_cum(),sku_amount(),sku_gender(),user_monetary())
page.render("orde_info_analysis.html")
结果如下：
202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
