唯品会电商销售复盘分析

Python 同时被 2 个专栏收录
14 篇文章 0 订阅
8 篇文章 0 订阅

什么是特卖:

    唯品会是一个专门做特卖的网站,什么是特卖呢。特卖一般是指在特定的时间段里,以优惠的价格出售指定的商品,一般以商城或者专卖店为多。该模式在线下早已存在(比如商场促销、街边的尾货甩卖),在国外成熟的大商场内也有针对滞销商品的打折特卖,如奥特莱斯。特卖一般是商家清库存,不过也有一些专门生产商品做特卖的商家。

    特卖行业也是有个真实存在的产业链,只是因为快速分销渠道,地理位置等关系,大多数都集中在一线城市,部分生活在一线城市的都基本或多或少去过几次各个品牌的特卖仓,但是二三线甚至四五线城市的就比较难接触到,后来就有一群人成了品牌搬运工,和各大品牌联系通过微信等渠道快速分销大牌库存,达到快速低价消除库存,加快周转回笼资金等目的。

    在货源上,由于品牌尾货具备天然的清仓需求,是折扣零售最常见的货源,但实际上,只要成本足够低,新品首发、定制包销、自有品牌均可以成为折扣特卖零售的可持续货源。成立初期,唯品会货源以尾货为主,但随着唯品会在电商领域的不断发展,新品和专供品的占比不断提升,早在 2016 年 Q2 分析中,唯品会当季新品和平台特供品就已经占 37%了。

此次分析的目标:

评估促销活动的结果,并根据情况优化商品结构,以便让自己的商品卖的更好。

分析流程

  • 总体运营指标
  • 在价格区间中找到表现不佳的商品,优化商品结构
  • 在折扣区间中找到表现不佳的商品,优化商品结构
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
## 1.读取数据表
import sqlalchemy
# 变量
engine=sqlalchemy.create_engine('mysql+pymysql://xxx:xxx@localhost:3306/froghd')
sql_cmd = 'select * from sales_info1'
# 读取
dt1 = pd.read_sql(sql=sql_cmd,con=engine)
dt1.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKU
0A00115700.21428650135070140.0666672
1A0022366100.386885423258030750.6822031
2A00347312530.3774944155199953940.1670191
3A0043208350.3832346245210402790.1281252
4A00515820.1829271791467827-0.8000001
# 修改列名
dt1.rename(columns={"sale_name":"商品名",
                    "sale_price":"售卖价",
                    "tag_price":"吊牌价",
                    "discout":"折扣率",
                    "stocks":"库存量",
                    "stocks_value":"货值",
                    "cost_price":"成本价",
                    "profit_rate":"利润率",
                    "skus":"SKU"},
          inplace=True)
sql_cmd = 'select * from sales_info2'

dt2 = pd.read_sql(sql=sql_cmd, con=engine)
dt2.head()
sale_nameuvscollectionscarts
0A0011092648372
1A0021312484193
2A0032565745173
3A004208335273
4A0051937171356
dt2.rename(columns={"sale_name":"商品名",
                    "uvs":"UV数",
                    "collections":"收藏数",
                    "carts":"加购物车数"},
            inplace= True)
dt2.head()
商品名UV数收藏数加购物车数
0A0011092648372
1A0021312484193
2A0032565745173
3A004208335273
4A0051937171356
sql_cmd = 'select * from sales_info3'
dt3 = pd.read_sql(sql=sql_cmd, con=engine)

dt3.head()
user_idbuy_datesale_namebuy_consbuy_pricecost_priceis_tuitui_constui_price
0120191111F0011920.0920.01920.0
1220191111B0072548.01096.000.0
2220191111E0071930.0930.01930.0
3320191111A0042320.0640.02640.0
4320191111H0072750.01500.000.0
dt3.rename(columns={"user_id":"用户id",
                    "buy_date":"购买日期",
                    "sale_name":"商品名",
                    "buy_cons":"购买数量",
                    "buy_price":"购买单价",
                    "cost_price":"购买金额",
                    "is_tui":"是否退货",
                    "tui_cons":"退货件数",
                    "tui_price":"退货金额"},
          inplace=True)
# 把是否用0,1代替

dt3["是否退货"] = dt3["是否退货"].map({"是":1,"否":0})
dt3
用户id购买日期商品名购买数量购买单价购买金额是否退货退货件数退货金额
0120191111F0011920.0920.011920.0
1220191111B0072548.01096.0000.0
2220191111E0071930.0930.011930.0
3320191111A0042320.0640.012640.0
4320191111H0072750.01500.0000.0
..............................
8031250520191111E0012144.0288.012288.0
8032250620191111B0012426.0852.0000.0
8033250720191111B0042491.0982.0000.0
8034250820191111H0062710.01420.0121420.0
8035250920191111A001115.015.01115.0

8036 rows × 9 columns

合并商品信息表和商品热度表数据

product_sales = dt3.groupby("商品名").agg({"购买数量":"sum",
                                       "购买金额":"sum",
                                       "退货件数":"sum",
                                       "退货金额":"sum",
                                       "用户id":pd.Series.nunique}).reset_index() # 购买的统计用户数
product_sales.head()
商品名购买数量购买金额退货件数退货金额用户id
0A0011852775.059885.0116
1A00214634456.0317316.087
2A00314468112.03114663.094
3A00417255040.05617920.0111
4A0051221830.032480.081
product_sales.rename(columns={"购买数量":"商品销售数量",
                              "购买金额":"商品销售金额",
                              "是否退货":"商品退货数量",
                              "退货金额":"商品退货金额",
                              "购买单价":"商品销售单价",
                              "用户id":"购买用户数量"},inplace=True) 
product_sales.head()
商品名商品销售数量商品销售金额退货件数商品退货金额购买用户数量
0A0011852775.059885.0116
1A00214634456.0317316.087
2A00314468112.03114663.094
3A00417255040.05617920.0111
4A0051221830.032480.081

合并商品信息

# 商品信息和销售热度合并
dt_product = dt1.merge(dt2,how='left',on="商品名")

dt_product.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数
0A00115700.21428650135070140.06666721092648372
1A0022366100.386885423258030750.68220311312484193
2A00347312530.3774944155199953940.16701912565745173
3A0043208350.3832346245210402790.1281252208335273
4A00515820.1829271791467827-0.80000011937171356
# 商品信息、销售热度以及用户销售明细
dt_product_sales = dt_product.merge(product_sales,how="left",on="商品名")
dt_product_sales.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量
0A00115700.21428650135070140.066667210926483721852775.059885.0116
1A0022366100.386885423258030750.6822031131248419314634456.0317316.087
2A00347312530.3774944155199953940.1670191256574517314468112.03114663.094
3A0043208350.3832346245210402790.128125220833527317255040.05617920.0111
4A00515820.1829271791467827-0.800000119371713561221830.032480.081

一、总体运营情况评价

  • 总体运营部分,主要关注销售额售卖比UV转化率等指标,其他指标作为辅助指标。

  • 销售额用来和预期目标做对比,售卖比用来看商品流转情况。

  • GMV:销售额,在唯品会里称为到手价。

  • 实销:GMV – 拒退金额。

  • 销量:累计销售量(含拒退)。

  • 客单价:GMV / 客户数,客单价与毛利率息息相关,一般客单价越高,毛利率越高。

  • UV:商品所在页面的独立访问数。

  • 转化率:客户数 / UV。

  • 折扣率:GMV / 吊牌总额(吊牌总额 = 吊牌价 * 销量),在日常工作中,吊牌额是必不可少的。

  • 备货值:吊牌价 * 库存数。

  • 售卖比:又称售罄率,GMV / 备货值。

  • 收藏数:收藏某款商品的用户数量。

  • 加购数:加购物车人数。

  • SKU数:促销活动中的SKU计数(一般指货号)。

  • SPU数:促销活动中的SPU计数(一般指款号)。

  • 拒退量:拒收和退货的总数量。

  • 拒退额:拒收和退货的总金额。

GMV

gmv = dt_product_sales["商品销售金额"].sum()
gmv
3747167.0

实际销售=GMV-退货金额

return_sales = dt_product_sales["商品退货金额"].sum()
return_money = gmv - return_sales
return_money
2607587.0

销量(包括退货)

all_sales = dt_product_sales["商品销售数量"].sum()
all_sales
12017

客单价 = GMV / 客户数

custom_price = gmv/dt_product_sales["购买用户数量"].sum()
custom_price
493.56783456269756

UV 商品所在页面的独立访问数

uv_cons = dt_product_sales["UV数"].sum()
uv_cons
1176103

转化率:客户数 / UV

uv_rate = dt_product_sales["购买用户数量"].sum()/uv_cons
uv_rate
0.006455216932530569

折扣率:GMV / 吊牌总额(吊牌总额 = 吊牌价 * 销量)

tags_sales = np.sum(dt_product_sales["吊牌价"]*dt_product_sales["商品销售数量"])

discount_rate = gmv/tags_sales
discount_rate
0.4179229541452886

备货值:吊牌价*库存数

goods_value = np.sum(dt_product_sales["吊牌价"]*dt_product_sales["库存量"])
goods_value
18916395

售卖比 = GMV/备货值

sales_rate = gmv/goods_value
sales_rate
0.19809096817866195

收藏数

coll_cons = sum(dt_product_sales["收藏数"])
coll_cons
6224

加购数

add_shop_cons = dt_product_sales["加购物车数"].sum()
add_shop_cons
18690

SKU数 商品的编码数,例如 同款衣服不同尺寸和配色对应不同sku码

sku_cons = dt_product_sales["SKU"].sum()
sku_cons
125

SPU数

spu_cons = len(dt_product_sales["商品名"].unique())
spu_cons
80

拒退量:拒收和退货的总数量。退货件数

reject_cons = dt_product_sales["退货件数"].sum()
reject_cons
3643

拒退额:拒收和退货的总金额。

reject_money = dt_product_sales["商品退货金额"].sum()
reject_money
1139580.0

汇总统计

今年双十一数据

sales_state_dangqi = pd.DataFrame(
    {"GMV":[gmv,],"实际销售额":[return_money,],"销量":[all_sales,],"客单价":[custom_price,],
     "UV数":[uv_cons,],"UV转化率":[uv_rate,],"折扣率":[discount_rate,],"货值":[goods_value,],
     "售卖比":[sales_rate,],"收藏数":[coll_cons,],"加购数":[add_shop_cons,],"sku数":[sku_cons,],
     "spu数":[spu_cons,],"拒退量":[reject_cons,],"拒退额":[reject_money,],}, 
    ) #index=["今年双11",]
sales_state_dangqi
GMV实际销售额销量客单价UV数UV转化率折扣率货值售卖比收藏数加购数sku数spu数拒退量拒退额
03747167.02607587.012017493.56783511761030.0064550.417923189163950.1980916224186901258036431139580.0

去年双十一数据

sales_state_tongqi = pd.DataFrame(
    {"GMV":[2261093,],"实际销售额":[1464936.517,],"销量":[7654,],"客单价":[609.34567,],
     "UV数":[904694,],"UV转化率":[0.0053366,],"折扣率":[0.46,],"货值":[12610930,],
     "售卖比":[0.1161,],"收藏数":[4263,],"加购数":[15838,],"sku数":[82,],
     "spu数":[67,],"拒退量":[2000,],"拒退额":[651188.57,],}, 
    ) #index=["去年双11",]
sales_state_tongqi
GMV实际销售额销量客单价UV数UV转化率折扣率货值售卖比收藏数加购数sku数spu数拒退量拒退额
022610931464936.5177654609.345679046940.0053370.46126109300.116142631583882672000651188.57
sales_state = pd.concat([sales_state_dangqi, sales_state_tongqi]).T

sales_state.columns = ["今年双十一","去年双十一"]
sales_state = sales_state.reset_index().rename(columns ={'index':"指标"})
sales_state["同比"] = (sales_state["今年双十一"]-sales_state["去年双十一"])/sales_state["去年双十一"]
sales_state

指标今年双十一去年双十一同比
0GMV3.747167e+062.261093e+060.657237
1实际销售额2.607587e+061.464937e+060.780000
2销量1.201700e+047.654000e+030.570029
3客单价4.935678e+026.093457e+02-0.190004
4UV数1.176103e+069.046940e+050.300001
5UV转化率6.455217e-035.336600e-030.209612
6折扣率4.179230e-014.600000e-01-0.091472
7货值1.891640e+071.261093e+070.500000
8售卖比1.980910e-011.161000e-010.706210
9收藏数6.224000e+034.263000e+030.460005
10加购数1.869000e+041.583800e+040.180073
11sku数1.250000e+028.200000e+010.524390
12spu数8.000000e+016.700000e+010.194030
13拒退量3.643000e+032.000000e+030.821500
14拒退额1.139580e+066.511886e+050.750000

二、从价格区间来优化商品结构

我们需要做的是,深入探究不同区间的数据,以此来优化后期的促销结构。

首先我们需要找到在本次促销中此区间的销售源数据,源数据要求显示具体的款号、销售额、销量等信息。第二步,计算出每个款的转化率、折扣率等数据。

查看指标:

  • 销售额
  • 销量
  • 件单价
  • 客户数
  • UV
  • 转换率
  • 库存
  • 货值
  • 售卖比

划分价格区间

# 设置价格区域
#利用pd.cut进行数据离散化切分,注意分组标签和分组数要一致

listBins = [0,200,400,1000000]
listLabels = ["1-200","200-400","400及以上"]
"""
pandas.cut(x,bins,right=True,labels=None,retbins=False,precision=3,include_lowest=False)
x:需要切分的数据
bins:切分区域
right : 是否包含右端点默认True,包含
labels:对应标签,用标记来代替返回的bins,若不在该序列中,则返回NaN
retbins:是否返回间距bins
precision:精度
include_lowest:是否包含左端点,默认False,不包含
"""
dt_product_sales["价格分组"] = pd.cut(dt_product_sales["售卖价"], bins=listBins, right=True, labels=listLabels,include_lowest=True)
dt_product_sales.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组
0A00115700.21428650135070140.066667210926483721852775.059885.01161-200
1A0022366100.386885423258030750.6822031131248419314634456.0317316.087200-400
2A00347312530.3774944155199953940.1670191256574517314468112.03114663.094400及以上
3A0043208350.3832346245210402790.128125220833527317255040.05617920.0111200-400
4A00515820.1829271791467827-0.800000119371713561221830.032480.0811-200

价格区间销售情况统计

  • 价格区间
  • 货值
  • 货值占比
  • 销售额
  • 售卖比
  • 销售占比
  • 销量
  • 客单价
  • UV
  • 收藏数
  • 加购数
  • 转化率

价格分组

货值 商品销售数量 UV数 购买用户数量 收藏数 加购物车数

dt_product_sales_info = dt_product_sales.groupby("价格分组").agg({"货值":"sum",
                                                               "商品销售金额":"sum",
                                                                "商品销售数量":"sum",
                                                                "UV数":"sum",
                                                                "购买用户数量":"sum",
                                                                "收藏数":"sum",
                                                                "加购物车数":"sum"
                                                                }).reset_index()
dt_product_sales_info
价格分组货值商品销售金额商品销售数量UV数购买用户数量收藏数加购物车数
01-2001573146339896.03615369561228017335324
1200-40085859731417702.04978465547315126088302
2400及以上87572761989569.03424340995216118835064

货值占比、销售占比、客单价、转化率

dt_product_sales_info["货值占比"] = dt_product_sales_info["货值"]/dt_product_sales_info["货值"].sum() 
dt_product_sales_info["销售占比"]=dt_product_sales_info["商品销售金额"]/dt_product_sales_info["商品销售金额"].sum()
dt_product_sales_info["客单价"]=dt_product_sales_info["商品销售金额"]/dt_product_sales_info["购买用户数量"]
dt_product_sales_info["转化率"]=dt_product_sales_info["购买用户数量"]/dt_product_sales_info["UV数"]

dt_product_sales_info.head()
价格分组货值商品销售金额商品销售数量UV数购买用户数量收藏数加购物车数货值占比销售占比客单价转化率
01-2001573146339896.036153695612280173353240.0831630.090707149.0771930.006169
1200-40085859731417702.049784655473151260883020.4538910.378340449.9212950.006768
2400及以上87572761989569.034243409952161188350640.4629460.530953920.6705230.006337

对400及以上价格区间的商品进行优化

product_400 = dt_product_sales[dt_product_sales["价格分组"]=="400及以上"]
product_400.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组
2A00347312530.3774944155199953940.1670191256574517314468112.03114663.094400及以上
5A00642814930.2866742643941522330.4556071580513416114361204.04619688.090400及以上
10B00142611210.3800184795369593110.269953120448624215867308.04318318.0101400及以上
13B00449113940.3522243965520243530.28105921453512021116078560.04723077.0102400及以上
15B00648414670.3299252964342323980.1776862373311528514168244.04823232.091400及以上

ABC分析方法

计算商品指标
  • 销售额
  • 销量
  • 件单价
  • 客户数
  • UV
  • 转换率=客户数 / UV
  • 库存
  • 备货值=吊牌价 * 库存数
  • 售卖比=又称售罄率,GMV / 备货值

转化率

product_400["转化率"] = product_400["购买用户数量"]/product_400["UV数"]

备货值

product_400["备货值"] = product_400["吊牌价"]*product_400["库存量"]

售卖比=又称售罄率,GMV / 备货值

product_400["售卖比"] = product_400["商品销售金额"]/product_400["备货值"]
product_400.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数...加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组转化率备货值售卖比
2A00347312530.3774944155199953940.167019125657...17314468112.03114663.094400及以上0.0036645199950.130986
5A00642814930.2866742643941522330.45560715805...16114361204.04619688.090400及以上0.0155043941520.155280
10B00142611210.3800184795369593110.269953120448...24215867308.04318318.0101400及以上0.0049395369590.125350
13B00449113940.3522243965520243530.281059214535...21116078560.04723077.0102400及以上0.0070185520240.142313
15B00648414670.3299252964342323980.17768623733...28514168244.04823232.091400及以上0.0243774342320.157160

5 rows × 21 columns

product_400[["商品名","商品销售金额","商品销售数量","售卖价","购买用户数量","UV数",'转化率',"库存量","备货值","售卖比"]]
商品名商品销售金额商品销售数量售卖价购买用户数量UV数转化率库存量备货值售卖比
2A00368112.014447394256570.0036644155199950.130986
5A00661204.01434289058050.0155042643941520.155280
10B00167308.0158426101204480.0049394795369590.125350
13B00478560.0160491102145350.0070183965520240.142313
15B00668244.01414849137330.0243772964342320.157160
16B007110148.0201548122294920.0041373254871750.226095
17B00865280.013648082185740.0044153394820580.135419
22C00370950.015047392172440.0053352423196820.221939
26C00770122.0174403104207540.0050112582894760.242238
29C01087750.01177507550440.0148692292807540.312551
36D00776718.0178431106209430.0050613874949730.154994
42E00359292.012248677190940.0040333544775460.124160
46E007143220.01549309672640.0132164096454020.221908
48E00962155.015540199254770.0038862242248960.276372
50F001106720.01169208046300.0172792173120460.342001
51F00270370.01554549473660.0127612112918130.241148
56F00773200.015048895253200.0037522353174850.230562
60G00165746.014246395130110.0073021421797720.365719
62G003111200.013980092115000.0080003284182000.265901
69G010104400.014572091191190.0047602673086520.338245
73H004163000.0163100098109860.0089201742550840.639005
75H00686620.012271082104790.0078253242673000.324055
76H007119250.015975010345200.0227882232676000.445628

优化方案:

  • A 转化率大于0.7%的商品,暂时保留,用于下次促销活动;
  • B 转化率小于0.7%的商品,但是售卖比大于36%的商品予以保留参加下次促销活动,
  • C 转化率小于0.7%的商品,并且售卖比小于36%的商品进行清仓处理。

1、保留商品:转化率大于0.7%的商品予以保留

stay_stocks1 = product_400[product_400["转化率"]>0.007]
stay_stocks1
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数...加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组转化率备货值售卖比
5A00642814930.2866742643941522330.45560715805...16114361204.04619688.090400及以上0.0155043941520.155280
13B00449113940.3522243965520243530.281059214535...21116078560.04723077.0102400及以上0.0070185520240.142313
15B00648414670.3299252964342323980.17768623733...28514168244.04823232.091400及以上0.0243774342320.157160
29C01075012260.6117462292807541280.82933315044...5111787750.04332250.075400及以上0.0148692807540.312551
46E00793015780.5893544096454023560.61720417264...388154143220.04743710.096400及以上0.0132166454020.221908
50F00192014380.6397772173120462370.74239114630...64116106720.04036800.080400及以上0.0172793120460.342001
51F00245413830.3282722112918133260.28193827366...13315570370.05725878.094400及以上0.0127612918130.241148
60G00146312660.3657191421797722680.421166213011...14314265746.04621298.095400及以上0.0073021797720.365719
62G00380012750.6274513284182002640.670000211500...373139111200.04636800.092400及以上0.0080004182000.265901
73H004100014660.6821281742550843470.653000110986...360163163000.05656000.098400及以上0.0089202550840.639005
75H0067108250.8606063242673002030.714085210479...6312286620.04330530.082400及以上0.0078252673000.324055
76H00775012000.6250002232676002670.64400014520...356159119250.02921750.0103400及以上0.0227882676000.445628

12 rows × 21 columns

2、保留商品:找出转化率小于0.7% 但是 售卖比大于36%的部分予以保留

stay_stocks2 = product_400[(product_400["售卖比"]>=0.36)&(product_400["转化率"]<0.007)]
stay_stocks2
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数...加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组转化率备货值售卖比

0 rows × 21 columns

3、清仓处理商品,找出转化率小于0.7%并且售卖比小于36%的部分

stay_stocks3 = product_400[(product_400["售卖比"]<0.36)&(product_400["转化率"]<0.007)]
stay_stocks3
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数...加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组转化率备货值售卖比
2A00347312530.3774944155199953940.167019125657...17314468112.03114663.094400及以上0.0036645199950.130986
10B00142611210.3800184795369593110.269953120448...24215867308.04318318.0101400及以上0.0049395369590.125350
16B00754814990.3655773254871754200.233577229492...384201110148.06636168.0122400及以上0.0041374871750.226095
17B00848014220.3375533394820583020.370833218574...31813665280.03918720.082400及以上0.0044154820580.135419
22C00347313210.3580622423196822540.463002217244...32815070950.04420812.092400及以上0.0053353196820.221939
26C00740311220.3591802582894761670.585608220754...23617470122.04718941.0104400及以上0.0050112894760.242238
36D00743112790.3369823874949733560.174014120943...28917876718.06025860.0106400及以上0.0050614949730.154994
42E00348613490.3602673544775462200.547325219094...18812259292.04320898.077400及以上0.0040334775460.124160
48E00940110040.3994022242248962680.331671225477...14915562155.05020050.099400及以上0.0038862248960.276372
56F00748813510.3612142353174854020.176230225320...10315073200.03416592.095400及以上0.0037523174850.230562
69G01072011560.6228372673086521520.788889119119...66145104400.04230240.091400及以上0.0047603086520.338245

11 rows × 21 columns

### 对200-400价格区间的商品进行优化
product_200_400 = dt_product_sales[dt_product_sales["价格分组"]=="200-400"]
product_200_400.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组
1A0022366100.386885423258030750.6822031131248419314634456.0317316.087200-400
3A0043208350.3832346245210402790.128125220833527317255040.05617920.0111200-400
6A0072145230.4091783711940331690.21028021607113228017637664.05110914.0110200-400
7A00823810390.229066254263906268-0.1260502893610521315937842.0409520.098200-400
9A0102175040.4305563731879921230.4331801133235236115633852.05010850.097200-400
# 转化率
product_200_400["转化率"] = product_200_400["购买用户数量"]/product_200_400["UV数"]
con_rate = product_200_400["转化率"].median()
# 库存量
stock = product_200_400["库存量"].mean()

# A类
A = product_200_400[(product_200_400["转化率"] > con_rate) & (product_200_400["库存量"] > stock)]
A
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组转化率
9A0102175040.4305563731879921230.4331801133235236115633852.05010850.097200-4000.007281
34D0052606130.4241443762304881300.500000152229331915339780.04611960.095200-4000.018192
61G0022325130.4522424602359801090.5301722435616714533640.0439976.094200-4000.021579
# B类
B = product_200_400[(product_200_400["转化率"] <= con_rate) & (product_200_400["库存量"] <= stock)]
B
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组转化率
27C0083398200.4134152211812202490.2654871141559328613646104.03812882.090200-4000.006358
30D0013469510.3638282392272892690.2225431244181458713245672.03712802.090200-4000.003686
54F0053389570.3531871791713032060.3905331188172736511739546.03210816.077200-4000.004092
59F01034610410.3323732522623322410.3034681187439913815051900.04415224.096200-4000.005122
77H0083248860.3656882372099821570.5154321192453537213945036.04012960.092200-4000.004780
# C类
C = product_200_400[(product_200_400["转化率"] <= con_rate) & (product_200_400["库存量"] > stock)]
C
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组转化率
1A0022366100.386885423258030750.6822031131248419314634456.0317316.087200-4000.006629
3A0043208350.3832346245210402790.128125220833527317255040.05617920.0111200-4000.005328
6A0072145230.4091783711940331690.21028021607113228017637664.05110914.0110200-4000.006845
12B0032887460.3860594393274941090.62152812317011129915143488.04412672.089200-4000.003841
41E00238910800.3601856296793202440.3727512241507716315459906.03614004.094200-4000.003892
49E01034510450.3301445505747502340.3217392127603516113847610.04515525.085200-4000.006661
53F0042585600.4607145633152801980.2325582227882018917645408.06917802.0105200-4000.004608
58F0092465520.4456525152842801170.52439022405310924411528290.0389348.076200-4000.003160
63G00439210400.3769234794981602340.40306121535612830814757624.05019600.090200-4000.005861
71H0022465170.475822491253847750.6951221130872625114836408.0307380.092200-4000.007030
74H0052435360.4533583431838481470.395062216453938813131833.0276561.083200-4000.005045
78H0092395180.4613904622393161680.2970711257071315617341347.05212428.0113200-4000.004396

三、从折扣区间来优化商品结构

同样地,我们选择0.35-0.4折扣区间进行深入探究。dt_product_sales表中,
我们可以得到0.35-0.4折扣区间的售卖比为16.90%,转化率为0.53%,折扣率为37%,在进行商品结构优化的时候要着重对比这几个指标。

dt_product_sales.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组
0A00115700.21428650135070140.066667210926483721852775.059885.01161-200
1A0022366100.386885423258030750.6822031131248419314634456.0317316.087200-400
2A00347312530.3774944155199953940.1670191256574517314468112.03114663.094400及以上
3A0043208350.3832346245210402790.128125220833527317255040.05617920.0111200-400
4A00515820.1829271791467827-0.800000119371713561221830.032480.0811-200

设置折扣区间

listBins = np.arange(0.15, 1.01, 0.05)
listLabel = ['0.15_0.2','0.2_0.25','0.25_0.3','0.3_0.35','0.35_0.4','0.4_0.45','0.45_0.5','0.5_0.55','0.55_0.6',
             '0.6_0.65','0.65_0.7','0.7_0.75','0.75_0.8','0.8_0.85','0.85_0.90','0.90_0.95','0.95_1']
dt_product_sales["折扣区间"] = pd.cut(dt_product_sales["折扣率"], bins=listBins, labels=listLabel, include_lowest=True)
dt_product_sales.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组折扣区间
0A00115700.21428650135070140.066667210926483721852775.059885.01161-2000.2_0.25
1A0022366100.386885423258030750.6822031131248419314634456.0317316.087200-4000.35_0.4
2A00347312530.3774944155199953940.1670191256574517314468112.03114663.094400及以上0.35_0.4
3A0043208350.3832346245210402790.128125220833527317255040.05617920.0111200-4000.35_0.4
4A00515820.1829271791467827-0.800000119371713561221830.032480.0811-2000.15_0.2

折扣区间销售情况统计

  • 价格区间
  • 货值
  • 货值占比
  • 销售额
  • 售卖比
  • 销售占比
  • 销量
  • 客单价
  • UV
  • 收藏数
  • 加购数
  • 转化率

货值、商品销售金额、商品销售数量、UV数、购买用户数、收藏数、加购物车数

dt_product_sales_discount_info = dt_product_sales.groupby("折扣区间").agg({"货值":"sum",
                                                                       "商品销售数量":"sum",
                                                                       "商品销售金额":"sum",
                                                                       "UV数":"sum",
                                                                       "购买用户数量":"sum",
                                                                       "收藏数":"sum",
                                                                      "加购物车数":"sum"}).reset_index()
dt_product_sales_discount_info
折扣区间货值商品销售数量商品销售金额UV数购买用户数量收藏数加购物车数
00.15_0.2146781221830.0193718171356
10.2_0.255973761052106944.0678086345201505
20.25_0.354651672579924.066471462538971
30.3_0.3525538861065382794.0876096605361530
40.35_0.4810578436961369758.0443317234120465884
50.4_0.4520983521988453179.018420512588453428
60.45_0.518692621452311158.01381949346831996
70.5_0.5511239519638024.0260881242584
80.55_0.6645402154143220.072649678388
90.6_0.6517859461144590706.0982107356301795
100.65_0.7319498301183010.027087185169690
110.7_0.75000.00000
120.75_0.8000.00000
130.8_0.85000.00000
140.85_0.9026730012286620.010479828363
150.90_0.95000.00000
160.95_1000.00000

货值占比、销售占比、客单价、转化率

dt_product_sales_discount_info["货值占比"]=dt_product_sales_discount_info["货值"]/dt_product_sales_discount_info["货值"].sum()
dt_product_sales_discount_info["销售占比"]=dt_product_sales_discount_info["商品销售金额"]/dt_product_sales_discount_info["商品销售金额"].sum()
dt_product_sales_discount_info["客单价"]=dt_product_sales_discount_info["商品销售金额"]/dt_product_sales_discount_info["购买用户数量"]
dt_product_sales_discount_info["转化率"]=dt_product_sales_discount_info["购买用户数量"]/dt_product_sales_discount_info["UV数"]
dt_product_sales_discount_info
折扣区间货值商品销售数量商品销售金额UV数购买用户数量收藏数加购物车数货值占比销售占比客单价转化率
00.15_0.2146781221830.01937181713560.0007760.00048822.5925930.004182
10.2_0.255973761052106944.06780863452015050.0315800.028540168.6813880.009350
20.25_0.354651672579924.0664714625389710.0288910.021329172.9956710.006950
30.3_0.3525538861065382794.08760966053615300.1350090.102156579.9909090.007533
40.35_0.4810578436961369758.04433172341204658840.4285060.365545585.1166170.005281
50.4_0.4520983521988453179.0184205125884534280.1109280.120939360.2376790.006829
60.45_0.518692621452311158.013819493468319960.0988170.083038333.1456100.006759
70.5_0.5511239519638024.02608812425840.0059420.010147306.6451610.004753
80.55_0.6645402154143220.0726496783880.0341190.0382211491.8750000.013216
90.6_0.6517859461144590706.09821073563017950.0944130.157641803.6816330.007484
100.65_0.7319498301183010.0270871851696900.0168900.048840989.2432430.006830
110.7_0.75000.000000.0000000.000000NaNNaN
120.75_0.8000.000000.0000000.000000NaNNaN
130.8_0.85000.000000.0000000.000000NaNNaN
140.85_0.9026730012286620.0104798283630.0141310.0231161056.3414630.007825
150.90_0.95000.000000.0000000.000000NaNNaN
160.95_1000.000000.0000000.000000NaNNaN

分析0.35-0.4折扣区间的数据内容

dt_product_discount_354 =  dt_product_sales[dt_product_sales["折扣区间"] == "0.35_0.4"]
dt_product_discount_354.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数收藏数加购物车数商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组折扣区间
1A0022366100.386885423258030750.6822031131248419314634456.0317316.087200-4000.35_0.4
2A00347312530.3774944155199953940.1670191256574517314468112.03114663.094400及以上0.35_0.4
3A0043208350.3832346245210402790.128125220833527317255040.05617920.0111200-4000.35_0.4
10B00142611210.3800184795369593110.269953120448624215867308.04318318.0101400及以上0.35_0.4
12B0032887460.3860594393274941090.62152812317011129915143488.04412672.089200-4000.35_0.4
计算商品指标
  • 销售额
  • 销量
  • 件单价
  • 客户数
  • UV
  • 转换率=客户数 / UV
  • 库存
  • 货值=吊牌价 * 库存数
  • 售卖比=又称售罄率,GMV / 备货值

转化率

dt_product_discount_354["转化率"] = dt_product_discount_354["购买用户数量"]/dt_product_discount_354["UV数"]

备货值

dt_product_discount_354["备货值"] = dt_product_discount_354["库存量"]*dt_product_discount_354["吊牌价"]

售卖比

dt_product_discount_354["售卖比"] = dt_product_discount_354["商品销售金额"]/dt_product_discount_354["备货值"]
dt_product_discount_354.head()
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数...商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组折扣区间转化率备货值售卖比
1A0022366100.386885423258030750.682203113124...14634456.0317316.087200-4000.35_0.40.0066292580300.133535
2A00347312530.3774944155199953940.167019125657...14468112.03114663.094400及以上0.35_0.40.0036645199950.130986
3A0043208350.3832346245210402790.128125220833...17255040.05617920.0111200-4000.35_0.40.0053285210400.105635
10B00142611210.3800184795369593110.269953120448...15867308.04318318.0101400及以上0.35_0.40.0049395369590.125350
12B0032887460.3860594393274941090.621528123170...15143488.04412672.089200-4000.35_0.40.0038413274940.132790

5 rows × 22 columns

ABC分析方法

dt_product_discount_354[["商品名","库存量","备货值","折扣率","售卖比","转化率"]].describe()
库存量备货值折扣率售卖比转化率
count25.00000025.00000025.00000025.00000025.000000
mean326.440000324231.3600000.3706880.1947280.006746
std136.776302160733.3274360.0137080.0763990.004307
min142.00000086436.0000000.3522240.0881850.003325
25%235.000000209982.0000000.3602670.1311210.003892
50%287.000000262892.0000000.3656880.1874350.005011
75%417.000000487175.0000000.3800180.2308540.007296
max629.000000679320.0000000.3994020.3657190.021228

转化率低于50%,且库存量高于75% ,清仓处理

clear_sale = dt_product_discount_354[(dt_product_discount_354["转化率"] <= 0.005011) & (dt_product_discount_354["库存量"] >= 417)]
clear_sale
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数...商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组折扣区间转化率备货值售卖比
10B00142611210.3800184795369593110.269953120448...15867308.04318318.0101400及以上0.35_0.40.0049395369590.125350
12B0032887460.3860594393274941090.621528123170...15143488.04412672.089200-4000.35_0.40.0038413274940.132790
32D0031935330.3621014172222611650.145078127367...15129143.0499457.0911-2000.35_0.40.0033252222610.131121
41E00238910800.3601856296793202440.372751224150...15459906.03614004.094200-4000.35_0.40.0038926793200.088185

4 rows × 22 columns

热销商品 转化率大于75% ,库存量高于50%

hot_sale = dt_product_discount_354[(dt_product_discount_354["转化率"] > 0.007296) & (dt_product_discount_354["库存量"] > 287)]
hot_sale
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数...商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组折扣区间转化率备货值售卖比
70H0012977550.3933773382551901660.44107716856...14944253.04212474.092200-4000.35_0.40.0134192551900.173412

1 rows × 22 columns

保留商品

clear_sale["商品名"].tolist()+hot_sale["商品名"].tolist()
['B001', 'B003', 'D003', 'E002', 'H001']
dt_product_discount_354=dt_product_discount_354[~dt_product_discount_354['商品名'].isin(
    clear_sale['商品名'].tolist()+hot_sale["商品名"].tolist())]

dt_product_discount_354
商品名售卖价吊牌价折扣率库存量货值成本价利润率SKUUV数...商品销售数量商品销售金额退货件数商品退货金额购买用户数量价格分组折扣区间转化率备货值售卖比
1A0022366100.386885423258030750.682203113124...14634456.0317316.087200-4000.35_0.40.0066292580300.133535
2A00347312530.3774944155199953940.167019125657...14468112.03114663.094400及以上0.35_0.40.0036645199950.130986
3A0043208350.3832346245210402790.128125220833...17255040.05617920.0111200-4000.35_0.40.0053285210400.105635
13B00449113940.3522243965520243530.281059214535...16078560.04723077.0102400及以上0.35_0.40.0070185520240.142313
16B00754814990.3655773254871754200.233577229492...201110148.06636168.0122400及以上0.35_0.40.0041374871750.226095
19B0103909920.3931451881864962650.32051327934...12046800.04316770.082200-4000.35_0.40.0103351864960.250944
21C0022015530.3634722871587111050.47761217835...14829748.0438643.097200-4000.35_0.40.0123801587110.187435
22C00347313210.3580622423196822540.463002217244...15070950.04420812.092400及以上0.35_0.40.0053353196820.221939
24C0052707650.3529411781361701150.574074212610...14739690.0359450.092200-4000.35_0.40.0072961361700.291474
26C00740311220.3591802582894761670.585608220754...17470122.04718941.0104400及以上0.35_0.40.0050112894760.242238
30D0013469510.3638282392272892690.222543124418...13245672.03712802.090200-4000.35_0.40.0036862272890.200942
37D0083409160.3711792872628922040.40000026236...12442160.03612240.084200-4000.35_0.40.0134702628920.160370
42E00348613490.3602673544775462200.547325219094...12259292.04320898.077400及以上0.35_0.40.0040334775460.124160
48E00940110040.3994022242248962680.331671225477...15562155.05020050.099400及以上0.35_0.40.0038862248960.276372
54F0053389570.3531871791713032060.390533118817...11739546.03210816.077200-4000.35_0.40.0040921713030.230854
56F00748813510.3612142353174854020.176230225320...15073200.03416592.095400及以上0.35_0.40.0037523174850.230562
60G00146312660.3657191421797722680.421166213011...14265746.04621298.095400及以上0.35_0.40.0073021797720.365719
63G00439210400.3769234794981602340.403061215356...14757624.05019600.090200-4000.35_0.40.0058614981600.115674
64G0052215880.375850147864361060.52036224334...14331603.0439503.092200-4000.35_0.40.021228864360.365623
77H0083248860.3656882372099821570.515432119245...13945036.04012960.092200-4000.35_0.40.0047802099820.214476

20 rows × 22 columns

  • 2
    点赞
  • 1
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值