一迂卢、項(xiàng)目背景介紹：

百川是一家電商公司某弦，運(yùn)營(yíng)日常需要復(fù)盤(pán)活動(dòng)效果，了解運(yùn)營(yíng)情況而克。

二靶壮、分析目標(biāo):

評(píng)估每次促銷活動(dòng)的結(jié)果，并根據(jù)情況優(yōu)化商品結(jié)構(gòu)员萍，以便讓自己的商品賣(mài)的更好腾降。

三、分析思路:

1碎绎、總體運(yùn)營(yíng)指標(biāo)
2螃壤、從價(jià)格區(qū)間找出表現(xiàn)不好的產(chǎn)品抗果，優(yōu)化商品結(jié)構(gòu)
3、從折扣區(qū)間來(lái)找出表現(xiàn)不好的產(chǎn)品映穗，優(yōu)化商品結(jié)構(gòu)

四窖张、分析過(guò)程：

1、讀取各部分?jǐn)?shù)據(jù)集

import pandas as pd
import numpy as np

import warnings
warnings.filterwarnings('ignore')

import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://frogdata05:Frogdata!1321@localhost:3306/froghd')
# 讀取數(shù)據(jù)
# 商品信息表
sql_cmd = "select * from sales_info1"
# 執(zhí)行sql語(yǔ)句蚁滋，獲取數(shù)據(jù)
dt1 = pd.read_sql(sql=sql_cmd, con=engine)
dt1.rename(columns={"sale_name":"商品名",
                   "sale_price":"售賣(mài)價(jià)",
                   "tag_price":"吊牌價(jià)",
                   "discout":"折扣率",
                   "stocks":"庫(kù)存量",
                   "stocks_value":"貨值",
                   "cost_price":"成本價(jià)",
                   "profit_rate":"利潤(rùn)率",
                   "skus":"SKU"},
         inplace=True)
dt1.head()

image.png

# 讀取數(shù)據(jù)
# 商品熱度表
sql_cmd = "select * from sales_info2"

# 執(zhí)行sql語(yǔ)句宿接，獲取數(shù)據(jù)
dt2 = pd.read_sql(sql=sql_cmd, con=engine)
dt2.rename(columns={"sale_name":"商品名",
                    "uvs":"UV數(shù)",
                    "collections":"收藏?cái)?shù)",
                    "carts":"加購(gòu)物車數(shù)"},
          inplace=True)

dt2.head()

image.png

# 讀取數(shù)據(jù)
# 用戶銷售明細(xì)表
sql_cmd = "select * from sales_info3"

# 執(zhí)行sql語(yǔ)句，獲取數(shù)據(jù)
dt3 = pd.read_sql(sql=sql_cmd, con=engine)
dt3.rename(columns={"user_id":"用戶id",
                    "buy_date":"購(gòu)買(mǎi)日期",
                    "sale_name":"商品名",
                    "buy_cons":"購(gòu)買(mǎi)數(shù)量",
                    "buy_price":"購(gòu)買(mǎi)單價(jià)",
                    "cost_price":"購(gòu)買(mǎi)金額",
                    "is_tui":"是否退貨",
                    "tui_cons":"退貨件數(shù)",
                    "tui_price":"退貨金額"},
          inplace=True)
dt3['是否退貨']=dt3["是否退貨"].map({"是":1,"否":0})
dt3.head()

image.png

2辕录、合并商品信息表和商品熱度表數(shù)據(jù)

# 把商品信息加上該商品的熱度信息
# 得到基礎(chǔ)的商品信息睦霎，以及商品的一些熱度信息：加購(gòu)物車數(shù)量，收藏?cái)?shù)量走诞、uv數(shù)
dt_product = dt1.merge(dt2,how="left",on="商品名")
dt_product.head()

image.png

3副女、合并商品信息表和商品熱度表數(shù)據(jù)和用戶銷售明細(xì)表

# 統(tǒng)計(jì)每個(gè)商品的一個(gè)銷售情況
product_sales = dt3.groupby("商品名").agg({"購(gòu)買(mǎi)數(shù)量":"sum",
                                                 "購(gòu)買(mǎi)金額":"sum",
                                                 "退貨件數(shù)":"sum",
                                                 "退貨金額":"sum",
                                                 "購(gòu)買(mǎi)單價(jià)":"mean",
                                                 "用戶id":pd.Series.nunique}).reset_index()
product_sales.rename(columns={"購(gòu)買(mǎi)數(shù)量":"商品銷售數(shù)量",
                              "購(gòu)買(mǎi)金額":"商品銷售金額",
                              "是否退貨":"商品退貨數(shù)量",
                              "退貨金額":"商品退貨金額",
                              "購(gòu)買(mǎi)單價(jià)":"商品銷售單價(jià)",
                              "用戶id":"購(gòu)買(mǎi)用戶數(shù)量"},inplace=True)
product_sales.head()

image.png

# 合并商品信息
dt_product_sales = dt_product.merge(product_sales,how="left",on="商品名")
dt_product_sales.head()

4、總體運(yùn)營(yíng)情況評(píng)價(jià)
總體運(yùn)營(yíng)部分蚣旱，主要關(guān)注銷售額碑幅、售賣(mài)比、UV塞绿、轉(zhuǎn)化率等指標(biāo)沟涨，其他指標(biāo)作為輔助指標(biāo)。銷售額用來(lái)和預(yù)期目標(biāo)做對(duì)比异吻，售賣(mài)比用來(lái)看商品流轉(zhuǎn)情況裹赴。

GMV：銷售額，為到手價(jià)诀浪。
實(shí)銷：GMV – 拒退金額棋返。
銷量：累計(jì)銷售量（含拒退）。
客單價(jià)：GMV / 客戶數(shù)雷猪，客單價(jià)與毛利率息息相關(guān)睛竣，一般客單價(jià)越高，毛利率越高求摇。
UV：商品所在頁(yè)面的獨(dú)立訪問(wèn)數(shù)酵颁。
轉(zhuǎn)化率：客戶數(shù) / UV。
折扣率：GMV / 吊牌總額（吊牌總額 = 吊牌價(jià) * 銷量）月帝，在日常工作中躏惋，吊牌額是必不可少的。
備貨值：吊牌價(jià) * 庫(kù)存數(shù)嚷辅。
售賣(mài)比：又稱售罄率簿姨，GMV / 備貨值。
收藏?cái)?shù)：收藏某款商品的用戶數(shù)量。
加購(gòu)數(shù)：加購(gòu)物車人數(shù)扁位。
SKU數(shù)：促銷活動(dòng)中的SKU計(jì)數(shù)（一般指貨號(hào)）准潭。
SPU數(shù)：促銷活動(dòng)中的SPU計(jì)數(shù)（一般指款號(hào)）。
拒退量：拒收和退貨的總數(shù)量域仇。
拒退額：拒收和退貨的總金額刑然。

#1、GMV：銷售額暇务，包含退貨的金額
gmv = dt_product_sales["商品銷售金額"].sum()
gmv

image.png

#2泼掠、實(shí)際銷售額=GMV - 退貨金額
return_sales = dt_product_sales["商品退貨金額"].sum()
return_money = gmv - return_sales
return_money

#3、銷量：累計(jì)銷售量（含拒退）
all_sales = dt_product_sales["商品銷售數(shù)量"].sum()
all_sales

#4垦细、客單價(jià)：GMV / 客戶數(shù)择镇，客單價(jià)與毛利率息息相關(guān)，一般客單價(jià)越高括改，毛利率越高腻豌。
# dt3.user_id.unique().count()
custom_price = gmv / dt_product_sales["購(gòu)買(mǎi)用戶數(shù)量"].sum()
custom_price

# 5、UV：商品所在頁(yè)面的獨(dú)立訪問(wèn)數(shù)
uv_cons = dt_product_sales["UV數(shù)"].sum()
uv_cons

image.png

# 6嘱能、轉(zhuǎn)化率：客戶數(shù) / UV吝梅。
uv_rate = dt_product_sales["購(gòu)買(mǎi)用戶數(shù)量"].sum() / dt_product_sales["UV數(shù)"].sum()
uv_rate

# 7、折扣率：GMV / 吊牌總額（吊牌總額 = 吊牌價(jià) * 銷量）惹骂，在日常工作中苏携，吊牌額是必不可少的。
tags_sales = np.sum(dt_product_sales["吊牌價(jià)"] * dt_product_sales["商品銷售數(shù)量"])
discount_rate= gmv / tags_sales 
discount_rate

image.png

# 8析苫、備貨值：吊牌價(jià) * 庫(kù)存數(shù)。
goods_value = dt_product_sales["貨值"].sum()
goods_value

image.png

# 9穿扳、售賣(mài)比：又稱售罄率衩侥，GMV / 備貨值。
sales_rate = gmv / goods_value
sales_rate

image.png

# 10矛物、收藏?cái)?shù)：收藏某款商品的用戶數(shù)量茫死。
coll_cons = dt_product_sales["收藏?cái)?shù)"].sum()
coll_cons

image.png

# 11、加購(gòu)數(shù)：加購(gòu)物車人數(shù)履羞。
add_shop_cons = dt_product_sales["加購(gòu)物車數(shù)"].sum()
add_shop_cons

image.png

# 12峦萎、SKU數(shù)：促銷活動(dòng)中的最小品類單元（一般指貨號(hào)）。
sku_cons = dt_product_sales["SKU"].sum()
sku_cons

image.png

# 13忆首、SPU數(shù)：促銷活動(dòng)中的SPU計(jì)數(shù)（一般指款號(hào)）爱榔。
spu_cons = len(dt_product_sales["商品名"].unique())
spu_cons

image.png

# 14、拒退量：拒收和退貨的總數(shù)量糙及。退貨件數(shù)
reject_cons = dt_product_sales["退貨件數(shù)"].sum()
reject_cons

image.png

# 15详幽、拒退額：拒收和退貨的總金額。
reject_money = dt_product_sales["商品退貨金額"].sum()
reject_money

image.png

# 匯總統(tǒng)計(jì)
# 匯總統(tǒng)計(jì)
sales_state_dangqi = pd.DataFrame(
    {"GMV":[gmv,],"實(shí)際銷售額":[return_money,],"銷量":[all_sales,],"客單價(jià)":[custom_price,],
     "UV數(shù)":[uv_cons,],"UV轉(zhuǎn)化率":[uv_rate,],"折扣率":[discount_rate,],"貨值":[goods_value,],
     "售賣(mài)比":[sales_rate,],"收藏?cái)?shù)":[coll_cons,],"加購(gòu)數(shù)":[add_shop_cons,],"sku數(shù)":[sku_cons,],
     "spu數(shù)":[spu_cons,],"拒退量":[reject_cons,],"拒退額":[reject_money,],}, 
    ) #index=["今年雙11",]

# 去年的數(shù)據(jù)是已經(jīng)統(tǒng)計(jì)好了的，這里用來(lái)做展示唇聘，不需要計(jì)算（寫(xiě)死的內(nèi)容）
sales_state_tongqi = pd.DataFrame(
    {"GMV":[2261093,],"實(shí)際銷售額":[1464936.517,],"銷量":[7654,],"客單價(jià)":[609.34567,],
     "UV數(shù)":[904694,],"UV轉(zhuǎn)化率":[0.0053366,],"折扣率":[0.46,],"貨值":[12610930,],
     "售賣(mài)比":[0.1161,],"收藏?cái)?shù)":[4263,],"加購(gòu)數(shù)":[15838,],"sku數(shù)":[82,],
     "spu數(shù)":[67,],"拒退量":[2000,],"拒退額":[651188.57,],}, 
    ) #index=["去年雙11",]

#sales_state = pd.concat([sales_state_dangqi, sales_state_tangqi])
sales_state_dangqi_s = pd.DataFrame(sales_state_dangqi.stack()).reset_index().iloc[:,[1,2]]
sales_state_dangqi_s.columns = ["指標(biāo)","今年雙11"]
sales_state_tongqi_s = pd.DataFrame(sales_state_tongqi.stack()).reset_index().iloc[:,[1,2]]
sales_state_tongqi_s.columns = ["指標(biāo)","去年雙11"]
sales_state = pd.merge(sales_state_dangqi_s, sales_state_tongqi_s,on="指標(biāo)")
sales_state["同比"] = (sales_state["今年雙11"] - sales_state["去年雙11"]) / sales_state["去年雙11"]
sales_state

image.png

5版姑、從價(jià)格區(qū)間來(lái)優(yōu)化商品結(jié)構(gòu)
我們需要做的是，深入探究不同區(qū)間的數(shù)據(jù)迟郎，以此來(lái)優(yōu)化后期的促銷結(jié)構(gòu)剥险。首先我們需要找到在本次促銷中此區(qū)間的銷售源數(shù)據(jù)，源數(shù)據(jù)要求顯示具體的款號(hào)宪肖、銷售額表制、銷量等信息。第二步匈庭，計(jì)算出每個(gè)款的轉(zhuǎn)化率夫凸、折扣率等數(shù)據(jù)。

查看指標(biāo)：
銷售額
銷量
件單價(jià)
客戶數(shù)
UV
轉(zhuǎn)換率
庫(kù)存
貨值
售賣(mài)比

# 劃分價(jià)格區(qū)間段
#設(shè)置切分區(qū)域
listBins = [0,200, 400, 100000]
#設(shè)置切分后對(duì)應(yīng)標(biāo)簽
listLabels = ['1_200','200_400','400及以上']
dt_product_sales['價(jià)格分組'] = pd.cut(dt_product_sales['售賣(mài)價(jià)'], bins=listBins, labels=listLabels, include_lowest=True)
dt_product_sales.head()

image.png

價(jià)格區(qū)間銷售情況統(tǒng)計(jì):
價(jià)格區(qū)間
貨值
貨值占比
銷售額
售賣(mài)比
銷售占比
銷量
客單價(jià)
UV
收藏?cái)?shù)
加購(gòu)數(shù)
轉(zhuǎn)化率

# 貨值占比阱持、銷售占比夭拌、客單價(jià)、轉(zhuǎn)化率  后面求
dt_product_sales_info = dt_product_sales.groupby("價(jià)格分組").agg({
                                        "貨值":"sum",
                                        "商品銷售金額":"sum",
                                        "商品銷售數(shù)量":"sum",
                                        "UV數(shù)":"sum",
                                        "購(gòu)買(mǎi)用戶數(shù)量":"sum",
                                        "收藏?cái)?shù)":"sum",
                                        "加購(gòu)物車數(shù)":"sum"
                                        }).reset_index()
dt_product_sales_info.head()

# 貨值占比衷咽、銷售占比鸽扁、客單價(jià)、轉(zhuǎn)化率
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["客單價(jià)"]=dt_product_sales_info["商品銷售金額"]/dt_product_sales_info["購(gòu)買(mǎi)用戶數(shù)量"]
dt_product_sales_info["轉(zhuǎn)化率"]=dt_product_sales_info["購(gòu)買(mǎi)用戶數(shù)量"]/dt_product_sales_info["UV數(shù)"]
dt_product_sales_info.head()

image.png

# 取出400及以上價(jià)格區(qū)間的數(shù)據(jù)內(nèi)容
product_400 = dt_product_sales[dt_product_sales["價(jià)格分組"]=='400及以上']
product_400.head()

image.png

計(jì)算商品指標(biāo):
銷售額
銷量
件單價(jià)
客戶數(shù)
UV
轉(zhuǎn)換率=客戶數(shù) / UV
庫(kù)存
備貨值=吊牌價(jià) * 庫(kù)存數(shù)
售賣(mài)比=又稱售罄率镶骗，GMV / 備貨值

# 轉(zhuǎn)換率=客戶數(shù) / UV
product_400['轉(zhuǎn)換率'] = product_400["購(gòu)買(mǎi)用戶數(shù)量"]/product_400["UV數(shù)"]
# 備貨值=吊牌價(jià) * 庫(kù)存數(shù)
product_400["備貨值"] = product_400["吊牌價(jià)"]*product_400["庫(kù)存量"]
# 售賣(mài)比=又稱售罄率桶现，GMV / 備貨值
product_400["售賣(mài)比"] = product_400["商品銷售金額"]/product_400["備貨值"]
product_400.head()

image.png

product_400[["商品名","商品銷售金額","商品銷售數(shù)量","商品銷售單價(jià)","購(gòu)買(mǎi)用戶數(shù)量","UV數(shù)",'轉(zhuǎn)換率',"庫(kù)存量","備貨值","售賣(mài)比"]]

image.png

• 優(yōu)化方案：
  轉(zhuǎn)化率大于0.7%的商品，暫時(shí)保留鼎姊，用于下次促銷活動(dòng)骡和；
  轉(zhuǎn)化率小于0.7%的商品，但是售賣(mài)比大于36%的商品予以保留參加下次促銷活動(dòng)相寇，
  轉(zhuǎn)化率小于0.7%的商品慰于，并且售賣(mài)比小于36%的商品進(jìn)行清倉(cāng)處理。

# 挑選合格的商品：
# 1唤衫、保留商品：轉(zhuǎn)化率大于0.7%的商品予以保留
stay_stocks571 = product_400[product_400["轉(zhuǎn)換率"]>0.007]
stay_stocks571

# 挑選合格的商品：
# 2婆赠、保留商品：找出轉(zhuǎn)化率小于0.7% 但是 售賣(mài)比大于36%的部分予以保留
stay_stocks573 = product_400[(product_400["售賣(mài)比"]>=0.36)&(product_400["轉(zhuǎn)換率"]<0.007)]
stay_stocks573

# 3、清倉(cāng)處理商品佳励，找出轉(zhuǎn)化率小于0.7%并且售賣(mài)比小于36%的部分
stay_stocks574 = product_400[(product_400["售賣(mài)比"]<0.36)&(product_400["轉(zhuǎn)換率"]<0.007)]
stay_stocks574

6休里、從折扣區(qū)間來(lái)優(yōu)化商品結(jié)構(gòu)

同樣地，我們選擇0.35-0.4折扣區(qū)間進(jìn)行深入探究赃承。dt_product_discount_info表中妙黍，我們可以得到0.35-0.4折扣區(qū)間的售賣(mài)比為16.90%，轉(zhuǎn)化率為0.53%瞧剖，折扣率為37%废境，在進(jìn)行商品結(jié)構(gòu)優(yōu)化的時(shí)候要著重對(duì)比這幾個(gè)指標(biāo)。

dt_product_sales.head()

image.png

# 劃分價(jià)格區(qū)間段
#設(shè)置切分區(qū)域
listBins = [0.15, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45, 0.5, 0.55, 0.6, 0.65, 0.7, 1]
#設(shè)置切分后對(duì)應(yīng)標(biāo)簽
listLabels = ['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_1']
dt_product_sales['折扣區(qū)間'] = pd.cut(dt_product['折扣率'], bins=listBins, labels=listLabels, include_lowest=True)
dt_product_sales.head()

image.png

# 貨值占比、銷售占比噩凹、客單價(jià)巴元、轉(zhuǎn)化率  后面求
dt_product_discount_info = dt_product_sales.groupby("折扣區(qū)間").agg({
                                        "貨值":"sum",
                                        "商品銷售金額":"sum",
                                        "商品銷售數(shù)量":"sum",
                                        "UV數(shù)":"sum",
                                        "購(gòu)買(mǎi)用戶數(shù)量":"sum",
                                        "收藏?cái)?shù)":"sum",
                                        "加購(gòu)物車數(shù)":"sum"
                                        }).reset_index()
# 貨值占比、銷售占比驮宴、客單價(jià)逮刨、轉(zhuǎn)化率  后面求
dt_product_discount_info["貨值占比"]=dt_product_discount_info["貨值"]/dt_product_discount_info["貨值"].sum()
dt_product_discount_info["銷售占比"]=dt_product_discount_info["商品銷售金額"]/dt_product_discount_info["商品銷售金額"].sum()
dt_product_discount_info["客單價(jià)"]=dt_product_discount_info["商品銷售金額"]/dt_product_discount_info["購(gòu)買(mǎi)用戶數(shù)量"]
dt_product_discount_info["轉(zhuǎn)化率"]=dt_product_discount_info["購(gòu)買(mǎi)用戶數(shù)量"]/dt_product_discount_info["UV數(shù)"]
dt_product_discount_info

image.png

# 取出0.35-0.4價(jià)格區(qū)間的數(shù)據(jù)內(nèi)容
product_354 = dt_product_sales[dt_product_sales["折扣區(qū)間"]=='0.35_0.4']
product_354.head()

image.png

# 轉(zhuǎn)換率=客戶數(shù) / UV
product_354['轉(zhuǎn)換率'] = product_354["購(gòu)買(mǎi)用戶數(shù)量"]/product_354["UV數(shù)"]
# 備貨值=吊牌價(jià) * 庫(kù)存數(shù)
product_354["備貨值"] = product_354["吊牌價(jià)"]*product_354["庫(kù)存量"]
# 售賣(mài)比=又稱售罄率，GMV / 備貨值
product_354["售賣(mài)比"] = product_354["商品銷售金額"]/product_354["備貨值"]
product_354.head()

image.png

product_354[["商品名","商品銷售金額","商品銷售數(shù)量","商品銷售單價(jià)","購(gòu)買(mǎi)用戶數(shù)量","UV數(shù)","庫(kù)存量","備貨值","折扣率","售賣(mài)比",'轉(zhuǎn)換率']]

image.png

優(yōu)化結(jié)果：

折扣率大于37%的部分找出售賣(mài)比大于36.5%且轉(zhuǎn)化率大于0.7%的商品予以保留堵泽，其余進(jìn)行清倉(cāng)處理修己；
折扣率小于37%的部分找出售賣(mài)比大于36.5%且轉(zhuǎn)化率大于0.7%的部分予以保留，其余進(jìn)行清倉(cāng)處理迎罗。

# 挑選合格的商品：
# 1睬愤、保留商品：折扣率大于37%的部分找出售賣(mài)比大于36.5%且轉(zhuǎn)化率大于0.7%的商品予以保留
stay_stocks1 = product_354[(product_354["折扣率"]>0.37)&(product_354["售賣(mài)比"]>0.365)&(product_354["轉(zhuǎn)換率"]>0.007)]
stay_stocks1

image.png

# 2、清倉(cāng)處理商品纹安，不滿足條件的：折扣率大于37%的部分找出售賣(mài)比小于36.5%或轉(zhuǎn)化率小于0.7%的商品
# 取反即可
stay_stocks2 = product_354[(product_354["折扣率"]>=0.37)&((product_354["售賣(mài)比"]<=0.365)|(product_354["轉(zhuǎn)換率"]<=0.007))] #
stay_stocks2

image.png

# 挑選合格的商品：
# 3尤辱、保留商品：在折扣率小于37%的部分找出售賣(mài)比大于36.5%且轉(zhuǎn)化率大于0.7%的部分予以保留
stay_stocks3 = product_354[(product_354["折扣率"]<=0.37)&(product_354["轉(zhuǎn)換率"]>0.007)&(product_354["售賣(mài)比"]>0.365)] 
stay_stocks3

image.png

# 4、清倉(cāng)處理商品厢岂，不滿足此條件的：在折扣率小于37%的部分找出售賣(mài)比小于36.5%或轉(zhuǎn)化率小于0.7%的部分
# 取反即可
stay_stocks4 = product_354[((product_354["折扣率"]<0.37) & ((product_354["售賣(mài)比"]<0.365)|(product_354["轉(zhuǎn)換率"]<0.007)))]
stay_stocks4

image.png