python电子商务网页数据采集 python 电子商务

本文结合mysql和python在数据分析中的优势，通过python连接mysql，对巴西电子商务展开数据分析工作，本文结构如下：

一、提出问题

二、获取、理解数据

1、获取数据

import pandas as pd
data=pd.read_csv(r"E:dataorders-mergedorders_merged.csv",dtype="object",encoding="UTF-8")
data.head()
data.shape

数据共有96478行，38列。

2、理解数据

数据来源：这是在Olist商店下订单的巴西电子商务公共数据集。 该数据集包含2016年至2018年在巴西多个市场上制作的10万张订单的信息。 它的功能允许从多个维度查看订单：从订单状态，价格，付款和货运绩效到客户位置，产品属性以及最终由客户撰写的评论。 我们还发布了将巴西邮政编码与经/纬度坐标相关联的地理位置数据集。这是真实的商业数据，已被匿名化，并且在评论文本中对公司和合作伙伴的引用已替换为《权力的游戏》大房子的名称。

数据来源：

Ecommerce_orderswww.kaggle.com

数据字段说明：

https://www.kaggle.com/olistbr/brazilian-ecommercewww.kaggle.com

字段说明：

product_id：商品ID

1. seller_id：商家ID
2. order_id：订单ID
3. customer_id：订单对应的用户ID。订单数据集的键，每个订单都有一个唯一的customer_id。
4. order_status：订单状态
5. order_purchase_timestamp：下单时间
6. order_approved_at：付款审批时间
7. order_delivered_carrier_date：订单过账日期
8. order_delivered_customer_date：客户实际订单交货日期
9. order_estimated_delivery_date：订单预计交货日期
10. customer_unique_id：用户ID
11. customer_zip_code_prefix：客户邮政编码前5位
12. customer_city：客户所在城市
13. customer_state：客户所在的州
14. review_id：评论ID
15. review_score：评价得分，客户在满意度调查中给出的注释范围为1到5。
16. review_comment_title：评论标题（葡萄牙语）
17. review_comment_message：评论内容（葡萄牙语）
18. review_creation_date：发出满意度调查日期
19. review_answer_timestamp：客户满意度回复日期
20. payment_sequential：付款顺序，客户可以使用多种付款方式付款。 如果他这样做，将创建一个顺序以容纳所有付款。
21. payment_type：付款方式
22. payment_installments：客户选择的分期付款数量
23. payment_value：交易金额
24. order_item_id：序号，用于标识同一订单中包含的商品数量。
25. price：商品价格
26. freight_value：运费，物品运费价值物品（如果订单包含多个物品，则运费价值将在物品之间分配）
27. seller_zip_code_prefix：卖家邮政编码前5位
28. seller_city：卖家所在城市
29. seller_state：卖家所在州
30. product_category_name：类别名称
31. product_name_lenght：产品名称长度
32. product_description_lenght：产品说明长度
33. product_photos_qty：产品照片数量
34. product_weight_g：产品重量单位g
35. product_length_cm：产品长度单位cm
36. product_height_cm：产品高度单位cm
37. product_width_cm：产品宽度单位cm

三、数据清洗

1、选取子集

根据需要分析的问题，此处我们选择在原数据集中选取需要的数据。具体如下：product_id，seller_id，order_id，customer_unique_id，order_purchase_timestamp，order_delivered_customer_date，order_estimated_delivery_date，customer_city，customer_state，seller_city，seller_state，review_score，review_creation_date，review_answer_timestamp，payment_type，payment_installments，payment_value，product_category_name

代码如下：

#选取子集
new_data=data[["product_id","seller_id","order_id","customer_unique_id","order_purchase_timestamp","order_delivered_customer_date",
               "order_estimated_delivery_date","customer_city","customer_state","seller_city","seller_state","review_score",
               "review_creation_date","review_answer_timestamp","payment_type","payment_installments","payment_value","product_category_name"]]
new_data.head()

2、去除重复值

new_data=new_data.drop_duplicates()
data.info()

去除重复值后，剩余96478条数据，数据并无重复数据。

3、缺失值处理

根据去重结果，product_category_name缺失数据相对其他缺失数据较多，但是与整体数据体谅相比较少，此处缺失值处理方式选择删除。

#去除缺失值
new_data=new_data.dropna(how="any")
new_data.info()

去除缺失值后，数据剩余95113条。

4、数据排序

此处我们按用户下单日期对数据集进行排序。

#数据排序
new_data=new_data.sort_values(by="order_purchase_timestamp")
new_data.head()

5、数据类型转换

python将清洗过的最新数据导出为CSV文件。

#输出为为文件，导入SQL分析
new_data.to_csv(r"E:dataorders-mergednew_orders_merged.csv")

SQL导入python导出的清洗后的最新CSV数据，数据类型均为varchar类型。

• 对order_estimated_delivery_date时间数据的时分秒部分均是00:00:00，结合分析需求，我们以天为单位，对order_estimated_delivery_date，order_delivered_customer_date转换为日期date类型。
• 对review_creation_date时间数据时分秒均是00:00:00，结合分析需求，我们以时为单位，对review_creation_date，review_answer_timestamp转为标准时间datetime格式即可。
• 对数值性数据，转换为整型和浮点型数据。

对时间数据进行类型转换

#时间数据类型转换
ALTER TABLE new_orders_merged MODIFY order_purchase_timestamp datetime DEFAULT NULL;
ALTER TABLE new_orders_merged MODIFY order_delivered_customer_date date DEFAULT NULL;
ALTER TABLE new_orders_merged MODIFY order_estimated_delivery_date date DEFAULT NULL;
ALTER TABLE new_orders_merged MODIFY review_creation_date datetime DEFAULT NULL;
ALTER TABLE new_orders_merged MODIFY review_answer_timestamp datetime DEFAULT NULL;

对数值数据进行类型转换

ALTER TABLE new_orders_merged MODIFY review_score INT DEFAULT NULL;
ALTER TABLE new_orders_merged MODIFY payment_installments INT DEFAULT NULL;
ALTER TABLE new_orders_merged MODIFY payment_value FLOAT DEFAULT NULL;

6、异常值处理

6.1 数据本身异常值

python导入pymysql包：

#导入连接mysql的数据包
import pymysql

连接SQL数据库读取数据：

#连接数据表
conn=pymysql.connect(host="localhost",port=3306,user="root",password="123456",db="olist",charset="utf8")
query="SELECT * FROM new_orders_merged"
sql_data=pd.read_sql(query,conn)
sql_data.head()

数据整体情况

可以看到数据集为95113条，与处理前python导出时的数据大小相同，无丢失，且数据类型已修改完成。

查询异常值，使用describe()方法

根据数据定义，用户付款分析payment_installments最小应该为1期，即不分期付款直接付清，交易金额payment_value应该大于0，因此需要对异常值进行处理。

我们通过SQL查询异常值的数据信息：

SELECT COUNT(payment_installments) FROM new_orders_merged
WHERE payment_installments=0;

SELECT COUNT(payment_value) FROM new_orders_merged
WHERE payment_value=0;

可以看出用户付款分析payment_installments异常值共有2条，交易金额payment_value异常值共有3条，由于异常值数据体量较小，此处通过SQL对异常值处理方式选择删除。

DELETE FROM new_orders_merged WHERE payment_value=0;
DELETE FROM new_orders_merged WHERE payment_installments=0;

python验证处理后的数据情况：

可见异常数据已经删除完毕，现有数据集大小：

异常值处理后，还剩数据95108条

7、拆分数据

根据此次数据分析需求，我们需要从客户下单时间order_purchase_timestamp拆分分别获取下单日期（order_purchase_date）和下单的时段（order_purchase_hour）。

ALTER TABLE new_orders_merged ADD order_purchase_date date DEFAULT NULL;
ALTER TABLE new_orders_merged ADD order_purchase_hour VARCHAR(40) DEFAULT NULL;

拆分获取时段数据：

UPDATE new_orders_merged SET order_purchase_hour = SUBSTRING(order_purchase_timestamp FROM 12 FOR 2);

转换数据类型获取日期数据：

UPDATE new_orders_merged SET n_date = DATE_FORMAT(order_purchase_timestamp,"%Y-%m-%d");

拆分数据后，通过python查询可以发现数据已经添加成功：

四、分析建模

建模前对数据的整体概览：查看数据分布是否异常。

SELECT DATE_FORMAT(order_purchase_timestamp,"%Y-%m") AS "年月", COUNT(DISTINCT(order_id)) AS "订单量",
ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY 年月
ORDER BY 年月;

可以看出2016至2017年1月份的数据过于偏小，因此需要对这部分数据进行舍去，根据分析需求，我们选取一年的数据进行分析（2017年8月至2018年7月），对其余数据进行删除处理。

DELETE FROM new_orders_merged 
WHERE order_purchase_date NOT BETWEEN "2017-08-01" AND "2018-07-31";

经过抽取的后的数据如下：

1、商品总数指标

1.1 SKU数

SELECT COUNT(DISTINCT(product_id)) AS "SKU数" FROM new_orders_merged;

sku数共24044个

1.2 SPU数

SELECT COUNT(DISTINCT(product_category_name)) AS "SPU数" FROM new_orders_merged;

SPU数73个

1.3 各SPU的SKU数及占比

SELECT SPU,SKU数,
CONCAT(SKU数/(SELECT SUM(SKU数) FROM (SELECT COUNT(DISTINCT(product_id)) AS "SKU数" FROM new_orders_merged) AS a)*100,"%") AS "SKU占比"
FROM 
(SELECT product_category_name AS "SPU",COUNT(DISTINCT(product_id)) AS "SKU数" 
FROM new_orders_merged
GROUP BY product_category_name
ORDER BY SKU数 DESC) AS b;

python可视化展示

• 导入可视化数据包matplotlib

#导入数据可视化包
import matplotlib.pyplot as plt
%matplotlib inline
%config InlineBackend.figure_format="svg"

• 创建mysql数据库操作游标（很重要！！！）

#使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()

• 连线mysql读取数据

# 定义要执行的SQL语句
sql = """
SELECT product_category_name AS "SPU",COUNT(DISTINCT(product_id)) AS "SKU数" 
FROM new_orders_merged
GROUP BY product_category_name
ORDER BY SKU数 DESC;
"""
# 执行SQL语句
cursor.execute(sql)
# 获取全部查询数据
ret = cursor.fetchall()
ret

• 数据类型转换：python执行mysql语句获取的数据类型为元组类型，要转换为DataFrame类型需要先转换为list类型，再转换为DataFrame类型。

#转换为列表
ret_List=[]
for each in ret:
    ret_List.append(each)
ret_List

#转换为DataFrame
ret_data=pd.DataFrame(ret_List,index=None,columns=["SPU","SKU_num"])
ret_data=ret_data.set_index("SPU")
# ret_data=ret_data.reset_index() #索引转回为列数据
ret_data

• python可视化展示

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘图
plt.bar(range(len(ret_data.index)),ret_data.values,label="SKU数")
# ret_data.plot(kind="bar",label="SKU数")
#添加轴标签
plt.xlabel("SPU")
plt.ylabel("SKU数")
#添加x轴刻度
plt.xticks(range(len(ret_data.index))[::2],ret_data.index[::2],rotation=90)
#添加其他图例
plt.legend(loc="best")
plt.grid(alpha=0.8,axis="y",linestyle="--",linewidth=1.5)
plt.title("SPU_SKU数")
plt.show()

可以看出有很多品类的SKU数较少，可能导致产品不够丰富，无法满足用户的选择需求。

2、销售类指标

2.1 前十大销售品类及占比

SELECT SPU,交易额,
CONCAT(ROUND(交易额/(SELECT SUM(payment_value) FROM new_orders_merged)*100,4),"%") AS "交易额占比"
FROM
(SELECT product_category_name AS "SPU",SUM(payment_value) AS "交易额" 
FROM new_orders_merged 
GROUP BY product_category_name
ORDER BY 交易额 DESC) AS a
LIMIT 10;

python可视化展示

读取数据：

#执行的sql语句
sql="""SELECT SPU,交易额,
CONCAT(ROUND(交易额/(SELECT SUM(payment_value) FROM new_orders_merged)*100,4),"%") AS "交易额占比"
FROM
(SELECT product_category_name AS "SPU",SUM(payment_value) AS "交易额" 
FROM new_orders_merged 
GROUP BY product_category_name
ORDER BY 交易额 DESC) AS a
LIMIT 10;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
sale_Ten = cursor.fetchall()
sale_Ten

数据类型转换

#转换为列表
sale_Ten_List = list(sale_Ten)
#转换为DataFrame
sale_Ten_Data = pd.DataFrame(sale_Ten_List,columns=["SPU","交易额","占比"])
sale_Ten_Data=sale_Ten_Data.set_index("SPU")
sale_Ten_Data

可视化：

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘图
# sale_Ten_Data.plot(kind="bar",label="交易额")
plt.bar(range(len(sale_Ten_Data.index)),sale_Ten_Data["交易额"],width=0.5,label="交易额")
#添加轴标签
plt.xlabel("SPU")
plt.ylabel("交易额")
#添加轴刻度
plt.xticks(range(len(sale_Ten_Data.index)),sale_Ten_Data.index,rotation=60,fontsize=10)
#添加其他标签
plt.legend(loc="best")
plt.title("交易额前十的品类")
plt.show()

2.2 每个品类销售排名前三的产品

SELECT * FROM
(SELECT product_category_name,product_id,交易额,dense_rank() over (PARTITION BY product_category_name ORDER BY 交易额 DESC) AS rank_num
FROM 
(SELECT product_category_name,product_id,SUM(payment_value) AS "交易额"
FROM new_orders_merged GROUP BY product_category_name,product_id) AS a) AS b
WHERE rank_num<=3;

2.3 各产品销量分布情况

SELECT product_id,COUNT(order_id)AS "销量" FROM new_orders_merged
GROUP BY product_id ORDER BY 销量 DESC;

python可视化

读取数据：

#要执行的sql语句
sql="""SELECT product_id,COUNT(order_id)AS "销量" FROM new_orders_merged
GROUP BY product_id ORDER BY 销量 DESC;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
product_sale_num = cursor.fetchall()
product_sale_num

数据类型转换：

#转化类型
#转换为list类型
product_sale_num_List = list(product_sale_num)
#转换为DataFrame类型
product_sale_num_Data = pd.DataFrame(product_sale_num_List,columns=["product_id","销量"])
product_sale_num_Data = product_sale_num_Data.set_index("product_id")
product_sale_num_Data.head()

数据可视化：

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘图
plt.plot(range(len(product_sale_num_Data.index)),product_sale_num_Data.values,label="销量")
#设置轴标签
plt.xlabel("商品")
plt.ylabel("销量")
#设置轴刻度
plt.xticks(range(len(product_sale_num_Data.index))[::1000],product_sale_num_Data.index,rotation=90)
#设置其他图例
plt.legend(loc="best")
plt.title("各产品销量分布情况")
plt.show()

可以看出除了头部爆品，其余产品的销量均较小，网站产品未能很好的满足用户的需求。

2.4 各时段用户下单及交易额的分布情况

SELECT DATE_FORMAT(order_purchase_timestamp,"%H") AS "小时", COUNT(DISTINCT(order_id)) AS "订单量",
ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY 小时
ORDER BY 小时;

python可视化：

读取数据：

#要执行的sql语句
sql="""SELECT DATE_FORMAT(order_purchase_timestamp,"%H") AS "小时", COUNT(DISTINCT(order_id)) AS "订单量",
ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY 小时
ORDER BY 小时;"""
#执行sql语句
cursor.execute(sql)
#读取数据
hour_sale=cursor.fetchall()
hour_sale

数据类型转换：

#转换数据类型
#转换为list类型
hour_sale_List = list(hour_sale)
#转换为DataFrame类型
hour_sale_Data=pd.DataFrame(hour_sale_List,columns=["hour","订单量","交易额"])
hour_sale_Data=hour_sale_Data.set_index("hour")
hour_sale_Data

数据可视化：

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘制主图
plt.bar(range(len(hour_sale_Data.index)),hour_sale_Data["订单量"],width=0.3,label="订单量")
#设置轴标签
plt.xlabel("时段")
plt.ylabel("订单量")
#设置图例
plt.legend(loc="upper left")
'设置次坐标轴'
plt.twinx()
#对次坐标图横坐标进行偏移
x=[]
for i in range(len(hour_sale_Data.index)):
    i=i+0.3
    x.append(i)
#绘制次坐标轴图
"""plt.bar(x,hour_sale_Data["交易额"],color="orange",width=0.3,label="交易额")
#绘制轴刻度
#编辑轴刻度文本
xticks=["{}时".format(i) for i in hour_sale_Data.index]
#设置轴刻度位置
xi=[]
for i in range(len(hour_sale_Data.index)):
    i=i+0.15
    xi.append(i)
plt.xticks(xi,xticks,rotation=0)""" #次坐标轴为柱状图
plt.plot(range(len(hour_sale_Data.index)),hour_sale_Data["交易额"],color="orange",linewidth=2,label="交易额")
#编辑轴刻度文本
xticks=["{}时".format(i) for i in hour_sale_Data.index]
#添加轴刻度
plt.xticks(range(len(hour_sale_Data.index)),xticks,rotation=0)
#添加轴标签
plt.ylabel("交易额")
plt.legend(loc="upper right")
#设置图标题
plt.title("各时段用户下单及交易额的分布情况",fontsize=15)
plt.show()

可以看出用户下单时间主要集中在09：00-23：00，巴西用户随时随地网购，似乎并未受到上班时间的限制。

2.5 用户下单数及交易额随时间的分布情况

• 2.5.1 用户下单数及交易额每月分布情况

SELECT DATE_FORMAT(order_purchase_timestamp,"%m") AS "月", COUNT(DISTINCT(order_id)) AS "订单量",
ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY 月
ORDER BY 月;

python可视化

读取数据：

#用户下单数及交易额随月份的变化
#sql语句
sql="""SELECT DATE_FORMAT(order_purchase_timestamp,"%m") AS "月", COUNT(DISTINCT(order_id)) AS "订单量",
ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY 月
ORDER BY 月;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
month_Sale = cursor.fetchall()
month_Sale

转换数据类型：

#转换类型
#转换为list类型
month_Sale_List=list(month_Sale)
#转换为DataFrame类型
month_Sale_Data = pd.DataFrame(month_Sale_List,columns=["month","订单量","交易额"])
month_Sale_Data = month_Sale_Data.set_index("month")
month_Sale_Data

可视化：

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘主图
plt.bar(range(len(month_Sale_Data.index)),month_Sale_Data["订单量"],width=0.3,label="订单量")
#添加标签
plt.xlabel("月份")
plt.ylabel("订单量")
#设置图例
plt.legend(loc="upper left")
"增加次坐标轴"
plt.twinx()
#绘制次坐标轴图
plt.plot(range(len(month_Sale_Data.index)),month_Sale_Data["交易额"],color="red",linewidth=2,label="交易额")
#绘制轴标签
plt.ylabel("交易额")
#绘制轴刻度
xticks=["{}月".format(i) for i in month_Sale_Data.index]
plt.xticks(range(len(month_Sale_Data)),xticks,rotation=0) #次坐标折线图
"""#调整次坐标图横坐标
x=[]
for i in range(len(month_Sale_Data.index)):
    i=i+0.3
    x.append(i)
#绘图
plt.bar(x,month_Sale_Data["交易额"],width=0.3,color="orange",label="交易额")
#绘制轴标签
"x轴标签内容"
xticks=["{}月".format(i) for i in month_Sale_Data.index]
"x轴标签坐标，偏移0.15"
x_i=[]
for i in range(len(month_Sale_Data.index)):
    i=i+0.15
    x_i.append(i)
"添加x轴刻度"
plt.xticks(x_i,xticks,rotation=0)""" #次坐标轴为柱状图
#设置图列
plt.legend(loc="upper right")
#设置图标题
plt.title("用户月下单量与交易额分布情况",fontsize=15)
plt.show()

可以看出用户网购高峰期是11月份，低谷是8月份。

• 2.5.2 用户下单随日期的变化

读取数据：

#要执行的sql语句
sql="""SELECT DATE_FORMAT(order_purchase_timestamp,"%Y-%m-%d") AS "时间", COUNT(DISTINCT(order_id)) AS "订单量",
ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY 时间
ORDER BY 时间;"""
#执行sql语句
cursor.execute(sql)
#去读数据
time_Sale=cursor.fetchall()
time_Sale

数据类型转换：

#转换数据类型
#转换为list类型
time_Sale_List=list(time_Sale)
#转换为DataFrame类型
time_Sale_Data=pd.DataFrame(time_Sale_List,columns=["time","订单量","交易额"])
time_Sale_Data=time_Sale_Data.set_index("time")
time_Sale_Data.head()

可视化：

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘制主图
plt.plot(range(len(time_Sale_Data.index)),time_Sale_Data["订单量"],linewidth=2,color="blue",label="订单量")
#绘制轴标签
plt.xlabel("时间")
plt.ylabel("订单量")
#图例
plt.legend(loc="upper left")
"设置次坐标轴"
plt.twinx()
#绘图
plt.plot(range(len(time_Sale_Data.index)),time_Sale_Data["交易额"],linewidth=2,color="orange",label="交易额")
#设置轴坐标
plt.ylabel("交易额")
#设置x轴刻度
plt.xticks(range(len(time_Sale_Data.index))[::60],time_Sale_Data.index[::60],rotation=0)
#设置图例
plt.legend(loc="upper right")
#设置图标题
plt.title("用户下单量/交易额随时间的分布情况",fontsize=15)
plt.show()

根据数据显示，2017-11-24这一天的订单量暴增至一千多单，经查询2017年11月24日非当地节日，也未有重大庆祝事件，可能是网站出现了爆款情况，具体原因暂未知。

2.6 客单价

SELECT ROUND(SUM(payment_value)/COUNT(DISTINCT(customer_unique_id)),2) AS "客单价"
FROM new_orders_merged;

客单价162.65雷亚尔，根据2020年3月6日汇率，约为245.52人民币。

2.7 订单平均交付时长

SELECT ROUND(SUM(TIMESTAMPDIFF(day,order_purchase_date,order_delivered_customer_date))/
COUNT(DISTINCT(order_id)),2) AS "订单平均交付时长" 
FROM new_orders_merged;

订单平均交付时长：12.9天，巴西网购的平均交付时间过长。

2.8 订单延期交付率

SELECT CONCAT(ROUND(SUM(CASE WHEN 延期时长>0 THEN 1 ELSE 0 END)/COUNT(延期时长)*100,2),"%") AS "订单延期交付率" 
FROM
(SELECT TIMESTAMPDIFF(day,order_estimated_delivery_date,order_delivered_customer_date) AS "延期时长"
FROM new_orders_merged) as a;

延期交付率：7.61%。

2.9 延期交付订单平均延期时长

SELECT ROUND(SUM(延期时长)/COUNT(延期时长),2)AS "延期交付订单平均延期时长" 
FROM
(SELECT TIMESTAMPDIFF(day,order_estimated_delivery_date,order_delivered_customer_date) AS "延期时长"
FROM new_orders_merged) as a
WHERE 延期时长>0;

延期订单平均延期时长未10.46天。

2.10 用户付款方式偏好

SELECT payment_type,COUNT(payment_type) AS "用户数"
FROM new_orders_merged
GROUP BY payment_type;

python可视化

读取数据：

#sql语句
sql="""SELECT payment_type,COUNT(payment_type) AS "用户数"
FROM new_orders_merged
GROUP BY payment_type;"""
#运营sql语句
cursor.execute(sql)
#读取全部数据
pay_type = cursor.fetchall()
pay_type

数据类型转换：

#转换数据类型
#转换为list数据类型
pay_type_List=list(pay_type)
#转换为DataFrame类型
pay_type_Data=pd.DataFrame(pay_type_List,columns=["payment_type","用户数"])
pay_type_Data = pay_type_Data.set_index("payment_type")
pay_type_Data = pay_type_Data.sort_values(by="用户数",ascending=False)
pay_type_Data

可视化：

#绘图
#创建画板
plt.figure(figsize=(8,5),dpi=80)
#绘图
plt.pie(pay_type_Data.values,labels=pay_type_Data.index,autopct="%1.1f%%",shadow=False,startangle=30,pctdistance=0.6)
#确保饼图输出为圆形
plt.axis("equal") 
#添加图例
plt.legend(loc="best")
#添加图标题
plt.title("用户付款方式偏好",fontsize=15)
plt.show()

巴西用户网购付款方式首选信用卡credit_card，占比高达76.1%，其次为线下交易方式boleto，占比19.7%，代币券voucher和借记卡debit_card分别占比2.7%和1.5%。

3、会员类指标

3.1 复购率

SELECT COUNT(DISTINCT(customer_unique_id)) AS "下单用户数",SUM(CASE WHEN 下单量>1 THEN 1 ELSE 0 END) AS "复购用户数",
CONCAT(ROUND(SUM(CASE WHEN 下单量>1 THEN 1 ELSE 0 END)/COUNT(DISTINCT(customer_unique_id))*100,2),"%") AS "复购率" 
FROM
(SELECT customer_unique_id,COUNT(DISTINCT(order_id)) AS "下单量"
FROM new_orders_merged GROUP BY customer_unique_id) AS a;

巴西电子商务复购率为2.58%，复购率有待提高。

3.2 平均下单数

SELECT COUNT(DISTINCT(order_id))/COUNT(DISTINCT(customer_unique_id)) AS "平均下单数"
FROM new_orders_merged;

平均下单数为1.0284。

4、风控类指标

4.1 买家各评分占比

SELECT review_score,用户数,
CONCAT(ROUND(用户数/(SELECT COUNT(review_score) FROM new_orders_merged)*100,2),"%") AS "占比"
FROM
(SELECT review_score,COUNT(review_score) AS "用户数"
FROM new_orders_merged 
GROUP BY review_score) AS a
ORDER BY 用户数 DESC;

python可视化

读取数据：

#要执行的sql语句
sql="""SELECT review_score,COUNT(review_score) AS "用户数"
FROM new_orders_merged 
GROUP BY review_score
ORDER BY 用户数 DESC;"""
#执行sql语句
cursor.execute(sql)
#读取数据
score = cursor.fetchall()
score

数据类型转换：

#转换数据类型
#转换为list类型
score_List=list(score)
#转换为DataFrame类型
score_Data=pd.DataFrame(score_List,columns=["score","用户数"])
score_Data=score_Data.set_index("score").sort_values(by="用户数",ascending=False)
score_Data

可视化：

#绘图
#创建画板
plt.figure(figsize=(8,5),dpi=80)
#轴标签文本转化
x_label = []
for i in score_Data.index:
    i=str(i)+"分"
    x_label.append(i)
#绘图
plt.pie(score_Data.values,labels=x_label,labeldistance=1.05,shadow=False,startangle=90,autopct="%1.1f%%",pctdistance=0.8)
#添加axis参数
plt.axis("equal")
#添加图例
plt.legend(loc="best")
#添加图标题
plt.title("用户评分占比",fontsize=15)
plt.show()

评分5分占比最高，为58.3%，4分占比19.5%，总占比为77.8%，用户对网购满意度较高，但是评分为1分的占10.7%，还具有一定的提升空间。

4.2 买家好评率（1-2分：差评，3分：中评，4-5分：好评）

SELECT SUM(CASE WHEN review_score>3 THEN 1 ELSE 0 end) AS "好评数",
CONCAT(ROUND(SUM(CASE WHEN review_score>3 THEN 1 ELSE 0 end)/COUNT(review_score)*100,2),"%") AS "好评率"
FROM new_orders_merged;

买家好评率77.87%

4.3 各时段买家评价数的分布

SELECT DATE_FORMAT(review_answer_timestamp,"%H") AS "小时",COUNT(review_score) AS "评论数"
FROM new_orders_merged GROUP BY 小时 ORDER BY 小时;

python可视化

读取数据：

#sql语句
sql="""SELECT DATE_FORMAT(review_answer_timestamp,"%H") AS "小时",COUNT(review_score) AS "评论数"
FROM new_orders_merged GROUP BY 小时 ORDER BY 小时;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
time_review=cursor.fetchall()
time_review

数据类型转换：

#转换数据类型
#转换为list类型
time_review_List=list(time_review)
#转换为DataFrame类型
time_review_Data = pd.DataFrame(time_review_List,columns=["hour","评论数"])
time_review_Data=time_review_Data.set_index("hour")
time_review_Data

可视化：

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘图
plt.plot(range(len(time_review_Data.index)),time_review_Data.values,linewidth=2,label="评论数")
#绘制轴标签
plt.xlabel("小时")
plt.ylabel("评论数")
#绘制x轴刻度
xticks=["{}时".format(i) for i in time_review_Data.index]
plt.xticks(range(len(time_review_Data.index)),xticks,rotation=45)
#绘制图例
plt.legend(loc="best")
#绘制图标题
plt.title("各时段买家评价数的分布",fontsize=15)

可以看出晚上21时至第二天凌晨1时以及白天10时至12时时用户评论高峰期。

4.4 评价调查发出至用户评价的平均时间间隔

SELECT SUM(TIMESTAMPDIFF(hour,review_creation_date,review_answer_timestamp))/
COUNT(review_answer_timestamp) AS "评价调查发出至用户评价的平均时间间隔"
FROM new_orders_merged;

评价调查发出至用户评价的平均时间间隔为72小时，即2天。

5、帕累托分析法

5.1 各城市/州交易额贡献分布

• 5.1.1 各城市交易额贡献分布

SELECT customer_city,ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY customer_city ORDER BY 交易额 DESC;

python可视化

备注：由于城市数量过多，电脑配置有限，python可视化难以显示，因此根据数据特点，对数据较小的点进行了舍去，这里我们选择一年交易额大于20000雷亚尔的城市进行分析。

读取数据：

#城市交易额贡献
#sql语句
sql="""SELECT customer_city,ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY customer_city HAVING 交易额 >20000 ORDER BY 交易额 DESC;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
city_pay = cursor.fetchall()
city_pay

转换类型：

#转换数据类型
#转换为list类型
city_pay_List = list(city_pay)
#转换为DataFrame类型
city_pay_Data=pd.DataFrame(city_pay_List,columns=["city","交易额"])
city_pay_Data=city_pay_Data.set_index("city").sort_values(by="交易额",ascending=False)
city_pay_Data

可视化：

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘图
plt.bar(range(len(city_pay_Data.index)),city_pay_Data.values,width=0.5,label="交易额")
#设置轴标签
plt.ylabel("交易额")
#设置图例
plt.legend(loc="upper left")
"设置次坐标"
plt.twinx()
"创建累计占比"
p=city_pay_Data["交易额"].cumsum()/city_pay_Data["交易额"].sum()
#找到累计占比超过80%的节点
key = p[p>0.8].index[0]
#找到累计占比超过80%的节点位置
key_num=city_pay_Data.index.tolist().index(key)
print("超过80%累计交易额的点是：",key)
print("超过80%累计交易额的节点值索引值是：",key_num)
#核心城市
key_city = city_pay_Data[:key]
print("核心城市：",key_city)

#绘制曲线
# plt.plot(style="--o",secondary_y=True) #secondary_y=True y轴副坐标
p.plot(color = 'orange',style = '-o',linewidth=1) #次坐标曲线
#绘制80%标识线
plt.axvline(key_num,hold=None,color="red",linestyle="--")
plt.text(key_num+1,p[key]-0.05,"累计占比为：%.3f%%"%(p[key]*100),color="red")
#绘制轴标签
plt.ylabel("交易额比例")
plt.title("各城市交易额贡献占比",fontsize=15)
plt.show()

结果如下：

超过80%累计交易额的点是： piracicaba
超过80%累计交易额的节点值索引值是： 33
核心城市：                               交易额
city                             
sao paulo              1531329.41
rio de janeiro          802307.86
belo horizonte          299552.02
brasilia                255171.57
curitiba                179300.68
campinas                154822.67
salvador                150951.19
porto alegre            150342.53
guarulhos               110530.75
niteroi                 105132.02
goiania                  85768.77
santos                   85744.16
sao bernardo do campo    84497.26
fortaleza                83754.21
recife                   82655.79
santo andre              76970.47
florianopolis            71775.73
osasco                   67984.06
jundiai                  64643.57
belem                    64301.12
sao jose dos campos      64032.25
sorocaba                 62869.59
nova iguacu              54543.44
ribeirao preto           52107.93
sao luis                 50374.72
juiz de fora             48993.62
vitoria                  48126.76
joao pessoa              47364.22
vila velha               44678.60
barueri                  43837.33
contagem                 42991.52
teresina                 42377.39
londrina                 41898.81
piracicaba               41367.40

• 5.1.2 各州交易额贡献分布

SELECT customer_state,ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY customer_state ORDER BY 交易额 DESC;

python可视化

读取数据：

#州交易额贡献
#sql语句
sql="""SELECT customer_state,ROUND(SUM(payment_value),2) AS "交易额"
FROM new_orders_merged
GROUP BY customer_state ORDER BY 交易额 DESC;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
state_pay=cursor.fetchall()
state_pay

转换数据类型：

#转换数据类型
#转为list类型
state_pay_List=list(state_pay)
#转为DataFrame类型
state_pay_Data = pd.DataFrame(state_pay_List,columns=["state","交易额"])
state_pay_Data=state_pay_Data.set_index("state")
state_pay_Data

可视化：

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘图
plt.bar(range(len(state_pay_Data.index)),state_pay_Data.values,label="交易额")
#设置轴标签
plt.ylabel("交易额")
#设置图例
plt.legend(loc="center right")
#设置x轴刻度
plt.xticks(range(len(state_pay_Data.index)),state_pay_Data.index,rotation=0)
"绘制累计曲线"
plt.twinx()
#累计百分比
p=state_pay_Data["交易额"].cumsum()/state_pay_Data["交易额"].sum()
#找出累计80%的点
key=p[p>0.8].index[0]
#找到点对应的位置
key_num=state_pay_Data.index.tolist().index(key)
#输出结果
print("累计超过80%的州：",key)
print("累计超过80%的州的索引位置：",key_num)
print("---------------------------------")
#显示核心州
key_state=state_pay_Data[:key]
print("核心州：",key_state)
#绘制曲线
p.plot(color = 'orange',style = '-o',linewidth=1) #次坐标曲线
#设置80%标识线
plt.axvline(key_num,hold=None,color="red",linestyle="--")
#设置80%文本
plt.text(key_num+0.2,p[key]-0.02,"累计占比为:%.2f%%"%(p[key]*100),color="red")
#设置轴标签
plt.ylabel("交易额占比")
#这只图标题
plt.title("州交易额贡献分布情况",fontsize=15)
plt.show()

结果如下：

累计超过80%的州： SC
累计超过80%的州的索引位置： 6
---------------------------------
核心州：               交易额
state            
SP     4190703.53
RJ     1488399.77
MG     1343329.08
RS      618864.31
PR      568421.79
BA      434518.40
SC      433494.03

5.2 各城市/州的卖家数量分布

• 5.2.1 各城市卖家数量分布

SELECT seller_city,COUNT(DISTINCT(seller_id)) AS "卖家数"
FROM new_orders_merged
GROUP BY seller_city ORDER BY 卖家数 DESC;

python可视化

备注：由于城市数量过多，电脑配置有限，python可视化难以显示，因此根据数据特点，对数据较小的点进行了舍去，这里我们选择一年卖家数量啊大于等于20家以上的城市进行分析。

读取数据：

#各城市的卖家数量分布
#sql语句
sql="""SELECT seller_city,COUNT(DISTINCT(seller_id)) AS "卖家数"
FROM new_orders_merged
GROUP BY seller_city HAVING 卖家数>19 ORDER BY 卖家数 DESC;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
city_Seller = cursor.fetchall()
city_Seller

转换数据类型：

#转换数据类型
#转换为list类型
city_Seller_List=list(city_Seller)
#转换为DataFrame类型
city_Seller_Data=pd.DataFrame(city_Seller_List,columns=["city","卖家数"])
city_Seller_Data=city_Seller_Data.set_index("city")
city_Seller_Data

可视化：

#绘图
#创建画板
fig=plt.figure(figsize=(10,5),dpi=80)
ax1=plt.subplot(1,1,1)
#绘图
city_Seller_Data.plot(ax=ax1,kind="bar",label="卖家数")
#设置轴坐标
plt.ylabel("卖家数")
plt.xlabel("城市")
#设置图例
plt.legend(loc="center right")
#设置x轴刻度
plt.xticks(range(len(city_Seller_Data.index)),city_Seller_Data.index,rotation=90)
"设置次坐标轴"
plt.twinx()
#计算累计占比
p=city_Seller_Data["卖家数"].cumsum()/city_Seller_Data["卖家数"].sum()
#找出累计80%的点
key=p[p>0.8].index[0]
#找出累计80%点的位置
key_num=city_Seller_Data.index.tolist().index(key)
print("累计80%的点：",key)
print("累计80%的点的位置：",key_num)
print("------------------------------")
key_seller_city = city_Seller_Data[:key]
print("卖家核心城市：",key_seller_city)
#绘曲线图
p.plot(color="orange",style="-o",linewidth=1)
#绘制80%标准线
plt.axvline(key_num,hold=None,color="red",linestyle="--")
#添加标记文本
plt.text(key_num+0.1,p[key]-0.03,"累计占比为：%.2f%%"%(p[key]*100),color="red")
#设置轴坐标
plt.ylabel("卖家数累计占比")
#设置图标题
plt.title("各城市卖家分布占比",fontsize=15)
plt.show()

结果如下：

累计80%的点： santo andre
累计80%的点的位置： 7
------------------------------
卖家核心城市：                 卖家数
city               
sao paulo       530
curitiba        100
rio de janeiro   71
belo horizonte   49
ribeirao preto   44
ibitinga         43
guarulhos        41
santo andre      37

• 5.2.2 各州卖家数量分布

SELECT seller_state,COUNT(DISTINCT(seller_id)) AS "卖家数"
FROM new_orders_merged
GROUP BY seller_state ORDER BY 卖家数 DESC;

python可视化

读取数据：

#各州的卖家数量分布
#sql预计
sql="""SELECT seller_state,COUNT(DISTINCT(seller_id)) AS "卖家数"
FROM new_orders_merged
GROUP BY seller_state ORDER BY 卖家数 DESC;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
state_seller = cursor.fetchall()
state_seller

数据类型转换

#转换数据类型
#转换list类型
state_seller_List=list(state_seller)
#转换为DataFrame类型
state_seller_Data=pd.DataFrame(state_pay_List,columns=["state","卖家数"])
state_seller_Data=state_seller_Data.set_index("state")
state_seller_Data

可视化：

#绘图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘主图
plt.bar(range(len(state_seller_Data.index)),state_seller_Data.values,label="卖家数")
#设置轴标签
plt.ylabel("卖家数")
plt.xlabel("州")
#设置x轴刻度
plt.xticks(range(len(state_seller_Data.index)),state_seller_Data.index,rotation=0)
"设置次坐标"
plt.twinx()
#求算累计百分比
p=state_seller_Data["卖家数"].cumsum()/state_seller_Data["卖家数"].sum()
#求出累计80%的点
key=p[p>0.8].index[0]
#求出累计80%的点的位置
key_num=state_seller_Data.index.tolist().index(key)
#打印结果
print("累计80%的州：",key)
print("累计80%的州的位置：",key_num)
print("--------------------------------")
#重要的州
key_state = state_seller_Data[:key]
print("卖家主要集中的州：",key_state)

#绘制曲线图
p.plot(color="orange",style="-o",linewidth=1)
#绘制80%标准线
plt.axvline(key_num,hold=None,color="red",linestyle="--")
#添加文本
plt.text(key_num+0.1,p[key]-0.02,"累计百分比：%.2f%%"%(p[key]*100),color="red")
#设置轴标签
plt.ylabel("卖家数累计占比")
#设置图表题
plt.title("卖家州分布占比情况",fontsize=15)
plt.show()

结果如下：

累计80%的州： SC
累计80%的州的位置： 6
--------------------------------
卖家主要集中的州：               卖家数
state            
SP     4190703.53
RJ     1488399.77
MG     1343329.08
RS      618864.31
PR      568421.79
BA      434518.40
SC      433494.03

6、RFM分析模型

6.1 客户划分

• 6.1.1 Recent（以数据最大日期加1天为计算日期）

SELECT order_purchase_date,
TIMESTAMPDIFF(day,order_purchase_date,(SELECT DATE_ADD(MAX(order_purchase_date),INTERVAL 1 day) FROM new_orders_merged)) AS "recent"
FROM new_orders_merged ORDER BY recent;

6.1.2 Frequency

SELECT customer_unique_id,COUNT(DISTINCT(order_id)) AS "frequency"
FROM new_orders_merged 
GROUP BY customer_unique_id ORDER BY frequency DESC;

6.1.3 Money

SELECT customer_unique_id,ROUND(SUM(payment_value),2) AS "Money"
FROM new_orders_merged 
GROUP BY customer_unique_id ORDER BY Money DESC;

6.1.4 RFM综合评分及分类

• 创建RFM得分视图

#创建RFM视图
CREATE VIEW RFM_data AS
SELECT a.customer_unique_id,a.customer_state,recent,frequency,money FROM
(SELECT customer_unique_id,customer_state,
TIMESTAMPDIFF(day,order_purchase_date,(SELECT DATE_ADD(MAX(order_purchase_date),INTERVAL 1 day) FROM new_orders_merged)) AS "recent"
FROM new_orders_merged) AS a
JOIN
(SELECT customer_unique_id,COUNT(DISTINCT(order_id)) AS "frequency"
FROM new_orders_merged 
GROUP BY customer_unique_id) AS b
ON a.customer_unique_id=b.customer_unique_id
JOIN
(SELECT customer_unique_id,ROUND(SUM(payment_value),2) AS "money"
FROM new_orders_merged 
GROUP BY customer_unique_id) AS c
ON a.customer_unique_id = c.customer_unique_id;

• 客户综合评分：

SELECT customer_unique_id,customer_state,
(CASE WHEN recent>(SELECT AVG(recent) FROM RFM_data) THEN 1 ELSE 0 END) AS "R",
(CASE WHEN frequency>(SELECT AVG(frequency) FROM RFM_data) THEN 1 ELSE 0 END) AS "F",
(CASE WHEN money>(SELECT AVG(money) FROM RFM_data) THEN 1 ELSE 0 END) AS "M"
FROM RFM_data;

• 客户分类：

SELECT customer_unique_id,customer_state,R,F,M,
(CASE WHEN R=1 AND F=1 AND M=1 THEN "重要价值客户"
WHEN R=1 AND F=0 AND M=1 THEN "重要发展客户"
WHEN R=0 AND F=1 AND M=1 THEN "重要保持客户"
WHEN R=0 AND F=0 AND M=1 THEN "重要挽留客户"
WHEN R=1 AND F=1 AND M=0 THEN "一般价值客户"
WHEN R=1 AND F=0 AND M=0 THEN "一般发展客户"
WHEN R=0 AND F=1 AND M=0 THEN "一般保持客户"
WHEN R=0 AND F=0 AND M=0 THEN "一般挽留客户" END) AS "用户分类"
FROM
(SELECT customer_unique_id,customer_state,
(CASE WHEN recent>(SELECT AVG(recent) FROM RFM_data) THEN 1 ELSE 0 END) AS "R",
(CASE WHEN frequency>(SELECT AVG(frequency) FROM RFM_data) THEN 1 ELSE 0 END) AS "F",
(CASE WHEN money>(SELECT AVG(money) FROM RFM_data) THEN 1 ELSE 0 END) AS "M"
FROM RFM_data) AS a;

6.2 各类客户占比及分布

在6.1的基础上创建RFM分类结果的视图

CREATE VIEW RFM_result AS
SELECT customer_unique_id,customer_state,
(CASE WHEN recent>(SELECT AVG(recent) FROM RFM_data) THEN 1 ELSE 0 END) AS "R",
(CASE WHEN frequency>(SELECT AVG(frequency) FROM RFM_data) THEN 1 ELSE 0 END) AS "F",
(CASE WHEN money>(SELECT AVG(money) FROM RFM_data) THEN 1 ELSE 0 END) AS "M"
FROM RFM_data;

6.2.1 重要价值客户占比及分布

• 重要价值客户占比

SELECT SUM(CASE WHEN (R=1 AND F=1 AND M=1) THEN 1 ELSE 0 END) AS "重要价值用户数",
CONCAT(ROUND(SUM(CASE WHEN (R=1 AND F=1 AND M=1) THEN 1 ELSE 0 END)/
COUNT(customer_unique_id)*100,2),"%") AS "占比"
FROM rfm_result;

重要价值客户占比1.85%。

• 重要价值客户分布

SELECT customer_state,SUM(CASE WHEN (R=1 AND F=1 AND M=1) THEN 1 ELSE 0 END) AS"重要价值用户数"
FROM rfm_result GROUP BY customer_state ORDER BY 重要价值用户数 DESC;

6.2.2 重要发展用户占比及分布

• 重要发展用户占比

SELECT SUM(CASE WHEN (R=1 AND F=0 AND M=1) THEN 1 ELSE 0 END) AS "重要发展用户数",
CONCAT(ROUND(SUM(CASE WHEN (R=1 AND F=0 AND M=1) THEN 1 ELSE 0 END)/
COUNT(customer_unique_id)*100,2),"%") AS "占比"
FROM rfm_result;

重要发展用户占比12.23%。

• 重要发展用户分布

SELECT customer_state,SUM(CASE WHEN (R=1 AND F=0 AND M=1) THEN 1 ELSE 0 END) AS"重要发展用户数"
FROM rfm_result GROUP BY customer_state ORDER BY 重要发展用户数 DESC;

6.2.3 重要保持用户占比及分布

• 重要保持用户占比

SELECT SUM(CASE WHEN (R=0 AND F=1 AND M=1) THEN 1 ELSE 0 END) AS "重要保持用户数",
CONCAT(ROUND(SUM(CASE WHEN (R=0 AND F=1 AND M=1) THEN 1 ELSE 0 END)/
COUNT(customer_unique_id)*100,2),"%") AS "占比"
FROM rfm_result;

重要保持hu 用户占比1.71%。

• 重要保持用户分布

SELECT customer_state,SUM(CASE WHEN (R=0 AND F=1 AND M=1) THEN 1 ELSE 0 END) AS"重要保持用户数"
FROM rfm_result GROUP BY customer_state ORDER BY 重要保持用户数 DESC;

6.2.4 重要挽留用户占比及分布

• 重要挽留用户占比

SELECT SUM(CASE WHEN (R=0 AND F=0 AND M=1) THEN 1 ELSE 0 END) AS "重要挽留用户数",
CONCAT(ROUND(SUM(CASE WHEN (R=0 AND F=0 AND M=1) THEN 1 ELSE 0 END)/
COUNT(customer_unique_id)*100,2),"%") AS "占比"
FROM rfm_result;

重要挽留用户占比13.74%。

• 重要挽留用户分布

SELECT customer_state,SUM(CASE WHEN (R=0 AND F=0 AND M=1) THEN 1 ELSE 0 END) AS"重要挽留用户数"
FROM rfm_result GROUP BY customer_state ORDER BY 重要挽留用户数 DESC;

各州重要客户分布情况

SELECT customer_state,
SUM(CASE WHEN (R=1 AND F=1 AND M=1) THEN 1 ELSE 0 END) AS"重要价值用户数",
SUM(CASE WHEN (R=1 AND F=0 AND M=1) THEN 1 ELSE 0 END) AS"重要发展用户数",
SUM(CASE WHEN (R=0 AND F=1 AND M=1) THEN 1 ELSE 0 END) AS "重要保持用户数",
SUM(CASE WHEN (R=0 AND F=0 AND M=1) THEN 1 ELSE 0 END) AS"重要挽留用户数",
COUNT(customer_state) AS "总数"
FROM rfm_result GROUP BY customer_state 
ORDER BY 总数 DESC;

python可视化

读取数据：

#sql语句
sql="""SELECT customer_state,
SUM(CASE WHEN (R=1 AND F=1 AND M=1) THEN 1 ELSE 0 END) AS"重要价值用户数",
SUM(CASE WHEN (R=1 AND F=0 AND M=1) THEN 1 ELSE 0 END) AS"重要发展用户数",
SUM(CASE WHEN (R=0 AND F=1 AND M=1) THEN 1 ELSE 0 END) AS "重要保持用户数",
SUM(CASE WHEN (R=0 AND F=0 AND M=1) THEN 1 ELSE 0 END) AS"重要挽留用户数",
COUNT(customer_state) AS "总数"
FROM rfm_result GROUP BY customer_state 
ORDER BY 总数 DESC;"""
#执行sql语句
cursor.execute(sql)
#获取全部查询数据
rfm_Tup=cursor.fetchall()
rfm_Tup

数据类型转换

#转换为列表
rfm_List = []
for each in rfm_Tup:
    rfm_List.append(each)
#     rfm_Tup=list(rfm_Tup)
#     for i in rfm_Tup:
#         if i not in rfm_List: #for 循环去重
#             rfm_List.append(i)
rfm_List

#转换为DataFrame
rfm_Data=pd.DataFrame(rfm_List,columns=["customer_state","value_customer","develop_customer","keep_customer","save_customer","total_num"])
rfm_Data=rfm_Data.set_index("customer_state")
rfm_Data

可视化：

#绘图
#设置画板
plt.figure(figsize=(11,5),dpi=80)
#绘图
"设置x轴坐标"
x=range(0,len(rfm_Data)*3,3)
#绘制重要价值客户柱状图
plt.bar(x,rfm_Data["value_customer"],width=0.5,label="重要价值客户")
"设置重要发展客户柱状图横坐标"
x1=[]
for i in x:
    i=i+0.5
    x1.append(i)
#绘制重要发展客户柱状图
plt.bar(x1,rfm_Data["develop_customer"],width=0.5,label="重要发展客户")
"设置重要保持客户柱状图横坐标"
x2=[]
for i in x1:
    i=i+0.5
    x2.append(i)
#绘制重要保持客户柱状图
plt.bar(x2,rfm_Data["keep_customer"],width=0.5,label="重要保持客户")
"设置重要挽留客户柱状图横坐标"
x3=[]
for i in x2:
    i=i+0.5
    x3.append(i)
#绘制重要挽留客户柱状图
plt.bar(x3,rfm_Data["save_customer"],width=0.5,label="重要挽留客户")
"设置x轴刻度值"
x_a=[]
for i in x:
    i=i+0.75
    x_a.append(i)
plt.xticks(x_a,rfm_Data.index,rotation=0)
"设置轴标签"
plt.xlabel("州")
plt.ylabel("客户数")
"""设置图例
参数 bbox_to_anchor 被赋予的二元组中，
num1 用于控制 legend 的左右移动，值越大越向右边移动，
num2 用于控制 legend 的上下移动，值越大，越向上移动。用于微调图例的位置"""
plt.legend(loc="upper right",bbox_to_anchor=(1,0.9))
"设置标题"
plt.title("各州重要客户数分布情况",fontsize=15)
"添加次坐标轴"
plt.twinx()
"计算重要客户数累计占比"
p=rfm_Data["total_num"].cumsum()/rfm_Data["total_num"].sum()
"找出累计80%的点"
key=p[p>0.8].index[0]
"找出累计80%的位置"
key_num=rfm_Data.index.tolist().index(key)
"打印结果"
print("累计80%占比的州：",key)
print("累计80%占比的州的位置：",key_num)
print("----------------------------------")
"重要的核心州"
key_important_state = rfm_Data["total_num"][:key]
print("重要的核心州：",key_important_state)
"绘制曲线图"
plt.plot(x_a,p,color="orange",marker="o",linestyle="--",linewidth=1)
"绘制80%标识线"
plt.axvline(key_num*3+0.75,hold=None,color="red",linestyle="--")
"显示文本"
plt.text(key_num*3+0.75+0.4,p[key]-0.02,"累计百分比：%.2f%%"%(p[key]*100),color="red")
"设置次坐标轴标签"
plt.ylabel("累计百分比")
plt.show()

结果如下：

累计80%占比的州： SC
累计80%占比的州的位置： 5
----------------------------------
重要的核心州： customer_state
SP    29926
RJ     9038
MG     8346
RS     3887
PR     3557
SC     2592
Name: total_num, dtype: int64

7、回归分析

7.1 收货时长与用户评分的关系

SELECT TIMESTAMPDIFF(day,order_purchase_date,order_delivered_customer_date) AS "收货时长",
review_score AS "评分" FROM new_orders_merged;

python回归分析

读取数据：

#sql语句
sql="""SELECT TIMESTAMPDIFF(day,order_purchase_date,order_delivered_customer_date) AS "收货时长",
review_score AS "评分" FROM new_orders_merged;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
order_re=cursor.fetchall()
order_re

数据类型转换

#转换数据类型
#转换为list类型
re_List = []
for each in order_re:
    re_List.append(each)
re_List
#转换为DataFrame类型
order_review_Data = pd.DataFrame(re_List,columns=["收货时长","评分"])
order_review_Data.head()

提取特征和标签：

#提取特征与标签
#提取特征feature
exam_X=order_review_Data.loc[:,"收货时长"]
#提取标签labels
exam_Y = order_review_Data.loc[:,"评分"]

绘制散点图：

#绘制散点图
#创建画板
plt.figure(figsize=(10,5),dpi=80)
#绘图
plt.scatter(exam_X,exam_Y,color="blue",label="收货时长与评分关系")
#添加轴标签
plt.xlabel("收货时长")
plt.ylabel("评分")
plt.show()

解决方案：

CSDN-专业IT技术社区-登录blog.csdn.net

查看相关系数：

#查看相关系数
relate_Df = order_review_Data.corr()
relate_Df

相关系数分类：

可看出收货时长与评分具有中等相关关系。

拆分训练数据和测试数据

#可以看出收货时长与评分具有中等相关关系
"拆分数据集，获取训练数据和测试数据"
#导入相关sklearn包
from sklearn.model_selection import train_test_split
#建立测试数据和训练数据
X_train,X_test,y_train,y_test=train_test_split(exam_X,exam_Y,test_size=0.2)
#查看拆分后的数据大小
print("原始数据特征：",exam_X.shape,"训练数据特征：",X_train.shape,"测试数据特征：",X_test.shape)
print("原始数据标签：",exam_Y.shape,"训练数据标签：",y_train.shape,"测试数据标签：",y_test.shape)

训练模型

#训练模型
#导入线性回归分析包
from sklearn.linear_model import LinearRegression
#创建模型：线性回归
model=LinearRegression()
#转换数据
"""sklearn要求输入的特征数据必须是数组类型，因此需要对数据进行转换"""
X_train = X_train.reshape(-1,1)
X_test = X_test.reshape(-1,1)
#训练模型
model.fit(X_train,y_train)

直接导入训练数据会报错，sklearn要求输入的特征必须时数组类型，因此需要对数据进行转换，使用推荐的reshape()函数进行转换。

转换后结果如下：

求最佳拟合线：

#根据模型获得最佳拟合曲线
"""y=a+bx
截距intercept：a
斜率/回归系数：b
"""
#截距
a=model.intercept_
#回归系数
b=model.coef_
print("最佳拟合曲线：","y=",a,"+",b,"x")

评估模型：

#评估模型准确度
model.score(X_test,y_test)

模型评估得分并不高，导致用户满意度较低原因除了收货时长，还有其他因素需进一步挖掘。

7.2 收货时长与下单数的关系

SELECT TIMESTAMPDIFF(day,order_purchase_date,order_delivered_customer_date) AS "收货时长",
COUNT(DISTINCT(order_id)) AS "下单数" FROM new_orders_merged 
GROUP BY customer_unique_id;

python回归分析

读取数据：

#要执行的sql语句
sql="""SELECT TIMESTAMPDIFF(day,order_purchase_date,order_delivered_customer_date) AS "收货时长",
COUNT(DISTINCT(order_id)) AS "下单数" FROM new_orders_merged 
GROUP BY customer_unique_id;"""
#执行sql语句
cursor.execute(sql)
#读取全部数据
order_sale = cursor.fetchall()
order_sale

数据类型转换：

#转换数据类型
#转换为list类型
order_sale_List=[]
for i in order_sale:
    order_sale_List.append(i)
#转换为DataFrame类型
order_sale_Data = pd.DataFrame(order_sale_List,columns=["收货时长","下单数"])
order_sale_Data.head()

查看相关性系数：

#查看相关系数
order_sale_Data.corr()

可以看出相关系数为-0.003188，收货时长与下单数无相关性。

五、结论建议

结论：

1、 商品总数指标

• sku数共24044个，SPU数73个，很多品类的SKU数较少，可能导致产品不够丰富，无法满足用户的选择需求。

2、销售类指标

• 销售前十的品类：beleza_saude，relogios_presentes，cama_mesa_banho，esporte_lazer8，informatica_acessorios，moveis_decoracao，utilidades_domesticas，automotivo，cool_stuff，ferramentas_jardim。
• 产品销量比较集中，头部效应明显，其余产品的销量均较小，网站产品未能很好的满足用户的需求。畅销的前十名产品如下：aca2eb7d00ea1a7b8ebd4e68314663af，422879e10f46682990de24d770e7f83d，53b36df67ebb7c41585e8d54d6772e08，d1c427060a0f73f6b889a5c7c61f2ac4，389d119b48cf3043d311335e499d9c6b，368c6c730842d78016ad823897a372db，3dd2a17168ec895c781a9191c1e95ad7，53759a2ecddad2bb87a079a1f1519f73，99a4788cb24856965c36a24e339b6058，2b4609f8948be18874494203496bc318。
• 用户下单时间主要集中在09：00-23：00，巴西用户随时随地网购，似乎并未受到上班时间的限制。
• 用户网购低谷是8-10月份，高峰期出现在11月份，用户网购有明显的起伏波动，2017-11-24这一天的订单量暴增至一千多单，经查询2017年11月24日非当地节日，也未有重大庆祝事件，可能是网站出现了爆款情况，具体原因暂未知。
• 客单价162.65雷亚尔，根据2020年3月6日汇率，约为245.52人民币，订单平均交付时长：12.9天，巴西网购的平均交付时间过长，延期交付率：7.61%，延期订单平均延期时长为10.46天。
• 巴西用户网购付款方式首选信用卡credit_card，占比高达76.1%，其次为线下交易方式boleto，占比19.7%，代币券voucher和借记卡debit_card分别占比2.7%和1.5%。

3、会员类指标

• 巴西电子商务复购率为2.58%，复购率有待提高，平均下单数为1.0284。

4、风控类指标

• 评分5分占比最高，为58.3%，4分占比19.5%，以4-5分为好评，好评率高达77.8%，用户对网购满意度较高，但是评分为1分的占10.7%，还具有一定的提升空间。
• 用户评论在晚上21时至第二天凌晨1时以及白天10时至12时时用户评论高峰期。
• 评价调查发出至用户评价的平均时间间隔为72小时，即2天。

5、帕累托分析法

• 贡献交易额的核心城市如下：sao paulo，rio de janeiro，belo horizonte，brasilia，curitiba，campinas，salvador，porto alegre，guarulhos，niteroi，goiania，santos，sao bernardo do campo，fortaleza，recife，santo andre，florianopolis，osasco，jundiai，belem，sao jose dos campos，sorocaba，nova iguacu，ribeirao preto，sao luis，juiz de fora，vitoria，joao pessoa，vila velha，barueri，contagem，teresina，londrina，piracicaba。
• 贡献交易额的核心州如下：SP，RJ，MG，RS，PR，BA，SC。
• 卖家核心的城市如下：sao paulo，curitiba，rio de janeiro，belo horizonte，ribeirao preto，ibitinga，guarulhos，santo andre。
• 卖家核心的州如下：SP，RJ，MG，RS，PR，BA，SC。

6、RFM分析模型

• 重要价值客户占比1.85%，重要发展用户占比12.23%，重要保持hu 用户占比1.71%，重要挽留用户占比13.74%。
• 重要客户的核心州如下：SP，RJ，MG，RS，PR，SC。

7、回归分析

• 收货时长与用户评分呈负相关关系，负相关系数-0.367597，最佳拟合曲线： y=4.77233836363 + [-0.05101799] x，模型评估得分0.128分，得分并不高，导致用户满意度较低原因除了收货时长，还有其他因素需进一步挖掘。
• 收货时长与下单数相关系数为-0.003188，无明显相关关系，收货时长与下单数无相关性。

建议：

1、发展商家，增加SKU数，根据并根据用户需求合理调整SPU，提高产品丰富度，以满足用户购物需求，

2、对需求旺盛的品类保持并进一步丰富，同时对畅销的头部产品进行分析，发掘用户需求特性，对商城的产品进行结构性优化。

3、在购买旺盛的月份以及每天当中的时段，可以采取折扣券，包邮等优惠形式吸引用户消费。

4、通过产品组合，满减等措施提高客单价。

5、与用户使用较多的付款方式对应的第三方支付机构洽谈，或自己申请开展支付业务，提高支付速度，简化用户网购流程，降低用户网购门槛，同时缩短网购交付时长。

6、物流建设，特别是人口多消费需求高的城市或州同时交付延期率高的，应加快物流发展速度，物流是巴西的一大发展机遇，同时物流建设反过来又会促进电子商务的进一步发展扩大。

7、提高复购率，用户对反馈评分整体较高，对产品较为认可，因此可以对消费客户进行短信、邮件或电话追回，用户第一次购物时给与积分、奖励券等形式发放给用户。

8、奖励用户评价反馈，并对用户评价内容较好的设为精品评价置顶，优先展示给用户。

9、交易额排前的核心城市和州需重点维护，同时对排后的城市和州进行重点发掘，对不同重要程度的城市和州采取不同的营销策略。

10、对卖家核心的城市和州加快物流建设，打造电子商务的核心区域，同时在卖家集中的区域进一步完善供应链建设，降低产品成本，提高电子商务的价格优势。

11、对重要价值用户、重要发展用户、重要维持用户，重要挽留用户进行差异化精准营销。