利用Python+SQL，用人货场的分析方法，对电商双十一促销活动进行复盘分析

---

背景

某电商公司最近举行了一场促销活动，该案例是对此次活动的一次复盘和分析。所需要用到的工具有Python + SQL。

python会用到的库有：

1. sqlalchemy
2. pandas
3. sklearn

---

一、观察数据

1.1 数据来源：

会用到数据库中的3个表的数据：

1. 商品属性表（sales_item）
2. 商品线上信息（sales_page）
3. 用户订单表（sales_order）

1.2 读取数据：

步骤：

1. import sqlalchemy
2. 写sql读取语句
3. 执行sql语句
4. 用df.info()、df.head()去观察数据

import pandas as pd
import sqlalchemy
#此处忽略数据库连接地址
engine = sqlalchemy.create_engine('xxxx')

#sql语句
sql_cmd = "select * from sales_item"

# 执行sql语句，获取数据
sales_item = pd.read_sql(sql=sql_cmd, con=engine)

sales_item.info()
sales_item.head(5)

sql_cmd = "select * from sales_page"
sales_page = pd.read_sql(sql=sql_cmd, con=engine)

sales_page.info()
sales_page.head(5)

sql_cmd = "select * from sales_order"
sales_order = pd.read_sql(sql=sql_cmd, con=engine)

sales_order.info()
sales_order.head(5)

二、分析思路

人货场

从人货场的角度去分析，其中”场“可以理解为是整理的运营情况。这里的分析不一定要按”人货场“的顺序，可以适当调节。

1. 整体情况（场）

1.1分析整体情况、并于去年的同比

2.商品情况（货）

2.1 什么商品在商品页面是吸引人的（衡量UV、加收藏、加购物车）
2.2 什么商品是用户需求多的（下单购买多）
2.3 什么商品是用户不满意的（被退回多）
2.4 什么商品能为公司带来更多利润
2.5 这些促销商品中，什么价格区间能吸引消费者下单
2.6 这些促销商品中，什么折扣区间能吸引消费者下单

3. 消费者情况（场）

3.1 哪些消费者是重要的（贡献金额较大）
3.2 哪些消费者存在一定风险（退货较多）

三、计算并分析

3.1 处理数据

先把表的属性改成中文，方便提供给其他部门做分析。

sales_item.rename(columns={
   "sale_name":"商品名",
                    "sale_price":"售卖价",
                    "tag_price":"吊牌价",
                    "discout":"折扣率",
                    "stocks":"库存量",
                    "stocks_value":"货值",
                    "cost_price":"成本价",
                    "profit_rate":"利润率",
                    "skus":"SKU"},
          inplace=True)

sales_page.rename(columns={
   "sale_name":"商品名",
                    "uvs":"UV数",
                    "collections":"收藏数",
                    "carts":"加购物车数"},
          inplace=True)


sales_order.rename(columns={
   "user_id":"用户id",
                    "buy_date":"购买日期",
                    "sale_name":"商品名",
                    "buy_cons":"购买数量",
                    "buy_price":"购买单价",
                    "cost_price":"购买金额",
                    "is_tui":"是否退货",
                    "tui_cons":"退货件数",
                    "tui_price":"退货金额"},
          inplace=True)

我们发现is_tui（是否退货）这里存的是object，检查除了“是和否”，是否还有其他字段。

sales_order["是否退货"].value_counts()

改成“1和0”，方便之后统计退货数

sales_order['是否退货']=sales_order["是否退货"].map({
   "是":1,"否":0})

3.2 聚合数据

sales_item和sales_page相连可以得到每个商品的商品属性以及在页面中的用户操作信息

再链接sales_order可以得到每个商品的订单信息

先对sales_order做统计汇总

# 统计每个商品的一个销售情况

orderGroupByItem = sales_order.groupby("商品名").agg({
   "购买数量":"sum",
                                                 "购买金额":"sum",
                                                 "退货件数":"sum",
                                                 "退货金额":"sum",
                                                 "购买单价":"mean",
                                                 "用户id":pd.Series.nunique}).reset_index()
orderGroupByItem.rename(columns={
   "购买数量":"商品销售数量",
                              "购买金额":"商品销售金额",
                              "是否退货":"商品退货数量",
                              "退货金额":"商品退货金额",
                              "购买单价":"商品销售单价",
                              "用户id":"购买用户数量"},inplace=True)
orderGroupByItem.head()

再连接三个表：

item_page = sales_item.merge(sales_page,how="left",on="商品名")
dt = item_page.merge(orderGroupByItem,how="left",on="商品名")

3.3 分析整体情况（场）

总体运营部分，主要关注销售额、售卖比、UV、转化率等指标，其他指标作为辅助指标。销售额用来和预期目标做对比，售卖比用来看商品流转情况。

• GMV：销售额，即到手价。
• 实销：GMV – 拒退金额。
• 销量：累计销售量（含拒退）。
• 客单价：GMV / 客户数，客单价与毛利率息息相关，一般客单价越高，毛利率越高。
• UV：商品所在页面的独立访问数。
• 转化率：客户数 / UV。
• 折扣率：GMV / 吊牌总额（吊牌总额 = 吊牌价 * 销量），在日常工作中，吊牌额是必不可少的。
• 备货值：吊牌价 * 库存数。
• 售卖比：又称售罄率，GMV / 备货值。
• 收藏数：收藏某款商品的用户数量。
• 加购数：加购物车人数。
• SKU数：促销活动中的SKU计数（一般指货号）。
• SPU数：促销活动中的SPU计数（一般指款号）。
• 拒退量：拒收和退货的总数量。
• 拒退额：拒收和退货的总金额。

#1、GMV：销售额，包含退货的金额
gmv = dt["商品销售金额"].sum()   #3747167.0