本文是對(duì)某自行車制造公司2020年11月銷售情況進(jìn)行分析總結(jié)，為精細(xì)化運(yùn)營提供數(shù)據(jù)支持。
本文目錄：
一、 分析目的
二、分析過程
三、看板展示

一、分析目的

通過對(duì)整個(gè)公司的自行車銷量持續(xù)監(jiān)測和分析，掌握公司自行車銷售狀況、走勢的變化，為客戶制訂、調(diào)整和檢查銷售策略，完善產(chǎn)品結(jié)構(gòu)提供依據(jù)。

二、分析過程

分析過程導(dǎo)圖如下：

image.png

具體分析如下:

2.1 自行車整體銷售表現(xiàn)

• 導(dǎo)入模塊包

#導(dǎo)入模塊
import pandas as pd
import numpy as np
import pymysql
#pymysql.install_as_MySQLdb()
from sqlalchemy import create_engine

• 讀取數(shù)據(jù)

sql='select * from dw_customer_order'
engine=create_engine('mysql+pymysql://frogdata05:Frogdata!1321@106.15.121.232:3306/datafrog05_adventure')
gather_customer_order=pd.read_sql(sql,con=engine)
gather_customer_order.head()

image.png

• 查看數(shù)據(jù)源類型

gather_customer_order.info()

image.png

• 增加月份字段

gather_customer_order['year_monrh']=gather_customer_order['create_date'].apply(lambda x:x.strftime('%Y-%m'))
gather_customer_order.head()

image.png

• 篩選產(chǎn)品類型為自行車的數(shù)據(jù)

gather_customer_order=gather_customer_order.loc[gather_customer_order['cplb_zw']=='自行車']
gather_customer_order.head()

image.png

• 整體銷量表現(xiàn)：按月份分組聚合總訂單量和總銷售額

overall_sales_performance=gather_customer_order.groupby('year_month').agg({'order_num':'sum','sum_amount':'sum'}).\
sort_index(ascending=False).reset_index()
overall_sales_performance

image.png

• ·計(jì)算訂單量和銷售金額環(huán)比

#計(jì)算訂單量環(huán)比
order_num_diff=list(overall_sales_performance.order_num.diff()/overall_sales_performance.order_num/-1)
order_num_diff.pop(0) #刪除列表的第一個(gè)元素
order_num_diff.append(0) #列表末尾增加元素0
order_num_diff
overall_sales_performance['order_num_diff']=order_num_diff
# 計(jì)算銷售金額環(huán)比
sum_amount_diff=list(overall_sales_performance.sum_amount.diff()/overall_sales_performance.sum_amount/-1)
sum_amount_diff.pop(0) #刪除列表的第一個(gè)元素
sum_amount_diff.append(0) #列表末尾增加元素0
sum_amount_diff
overall_sales_performance['sum_amount_diff']=sum_amount_diff
overall_sales_performance

image.png

• 圖表展示：

  
  
  image.png

2.2、2020年11月自行車地域銷售表現(xiàn)

• 數(shù)據(jù)清洗，篩選10月、11月自行車銷售數(shù)據(jù)

gather_customer_order_10_11 = gather_customer_order[gather_customer_order['year_month'].isin(['2020-10','2020-11'])]
gather_customer_order_10_11

image.png

• 2020年11月各區(qū)域銷售量情況
• 按區(qū)域、月份進(jìn)行分組聚合

gather_customer_order_10_11_group=gather_customer_order_10_11.groupby(['chinese_territory','year_month']).agg({'order_num':sum,'sum_amount':sum}).reset_index()
gather_customer_order_10_11_group

image.png

• 求各大區(qū)域10月、11月環(huán)比

#將區(qū)域存為列表
region=gather_customer_order_10_11_group['chinese_territory'].drop_duplicates(keep='first').tolist()
order_x = pd.Series([])
amount_x = pd.Series([])
for i in region:
    a=gather_customer_order_10_11_group.loc[gather_customer_order_10_11_group['chinese_territory']==i]['order_num'].pct_change().fillna(0)
    b=gather_customer_order_10_11_group.loc[gather_customer_order_10_11_group['chinese_territory']==i]['sum_amount'].pct_change().fillna(0)
    order_x=order_x.append(a)
    amount_x = amount_x.append(b)
gather_customer_order_10_11_group['order_diff']=order_x
gather_customer_order_10_11_group['amount_diff']=amount_x
gather_customer_order_10_11_group.head()

image.png

• 求訂單量TPO10城市的10、11月環(huán)比

#11月份銷量數(shù)據(jù)
gather_customer_order_11=gather_customer_order_10_11[gather_customer_order_10_11['year_month']=='2020-11']
#11月份TOP10城市
gather_customer_order_city_head=gather_customer_order_11.groupby('chinese_city').order_num.sum().sort_values(ascending=False).reset_index().head(10)
#篩選10、11月TOP10銷售數(shù)據(jù)
gather_customer_order_10_11_head=gather_customer_order_10_11[gather_customer_order_10_11['chinese_city'].isin(gather_customer_order_city_head['chinese_city'])]
#分組計(jì)算top10城市的訂單量和訂單金額
gather_customer_order_city_10_11=gather_customer_order_10_11_head.groupby(['chinese_city','year_month']).agg({'order_num':sum,'sum_amount':sum}).reset_index()
city_list=gather_customer_order_city_10_11['chinese_city'].unique().tolist()
order_top_x=pd.Series([])
amount_top_x=pd.Series([])
for i in city_list:
    a=gather_customer_order_city_10_11.loc[gather_customer_order_city_10_11['chinese_city']==i]['order_num'].pct_change().fillna(0)
    b=gather_customer_order_city_10_11.loc[gather_customer_order_city_10_11['chinese_city']==i]['sum_amount'].pct_change().fillna(0)
    order_top_x=order_top_x.append(a)
    amount_top_x=amount_top_x.append(b)
gather_customer_order_city_10_11['order_diff']=order_top_x
gather_customer_order_city_10_11['sum_diff']=amount_top_x
gather_customer_order_city_10_11.head()

image.png

圖表展示：

image.png

2.3、2020年11月自行車產(chǎn)品銷售表現(xiàn)

• 細(xì)分市場銷量表現(xiàn)
  • 日銷售量占月銷售量比例

#細(xì)分市場銷售表現(xiàn)
gather_customer_order_group_month=gather_customer_order.groupby('year_month').order_num.sum().reset_index()
gather_customer_order_group_month
order_num_proportion=pd.merge(gather_customer_order,gather_customer_order_group_month,on='year_month')
#計(jì)算自行車日銷售占月銷售比
order_num_proportion['order_proportion']=order_num_proportion['order_num_x']/order_num_proportion['order_num_y']
order_num_proportion=order_num_proportion.rename(columns={'order_num_y':'sum_month_order'})
order_num_proportion.head()

image.png

• 公路/山地/旅行自行車細(xì)分市場銷售表現(xiàn)
  • 公路自行車各型號(hào)產(chǎn)品與月累計(jì)銷量比值

#公路/山地/旅行自行車銷售表現(xiàn)
#公路自行車篩選
gather_customer_order_road=gather_customer_order[gather_customer_order['cpzl_zw']=='公路自行車']
#公路自行車產(chǎn)品型號(hào)月銷量
gather_customer_order_road_month=gather_customer_order_road.groupby(['year_month','product_name']).order_num.sum().reset_index()
#每個(gè)月公路自行車?yán)塾?jì)銷量
gather_customer_order_road_month_sum=gather_customer_order_road_month.groupby('year_month').order_num.sum().reset_index()
#各型號(hào)公路自行車日銷售量占月銷售量比值
gather_customer_order_road_num_proportion=pd.merge(gather_customer_order_road_month,gather_customer_order_road_month_sum,on='year_month')
gather_customer_order_road_num_proportion=gather_customer_order_road_num_proportion.rename(columns={'order_num_x':'order_num','order_num_y':'order_num_sum'})
gather_customer_order_road_num_proportion['order_proportion']=gather_customer_order_road_num_proportion['order_num']/gather_customer_order_road_num_proportion['order_num_sum']
gather_customer_order_road_num_proportion['cpzl_zw']='公路自行車'
gather_customer_order_road_num_proportion.head()

image.png

• 相同的步驟，得到每月各型號(hào)山地自行車銷售量占月總銷售量比值

#山地自行車篩選
gather_customer_order_mountain=gather_customer_order[gather_customer_order['cpzl_zw']=='山地自行車']
#山地自行車產(chǎn)品型號(hào)月銷量
gather_customer_order_mountain_month=gather_customer_order_mountain.groupby(['year_month','product_name']).order_num.sum().reset_index()
#每個(gè)月山地自行車?yán)塾?jì)銷量
gather_customer_order_mountain_month_sum=gather_customer_order_mountain_month.groupby('year_month').order_num.sum().reset_index()
#各型號(hào)山地自行車日銷售量占月銷售量比值
gather_customer_order_mountain_num_proportion=pd.merge(gather_customer_order_mountain_month,gather_customer_order_mountain_month_sum,on='year_month')
gather_customer_order_mountain_num_proportion=gather_customer_order_mountain_num_proportion.rename(columns={'order_num_x':'order_num','order_num_y':'order_num_sum'})
gather_customer_order_mountain_num_proportion['order_proportion']=gather_customer_order_mountain_num_proportion['order_num']/gather_customer_order_mountain_num_proportion['order_num_sum']
gather_customer_order_mountain_num_proportion['cpzl_zw']='山地自行車'
gather_customer_order_mountain_num_proportion.head()

image.png

• 類似的步驟得到旅游自行車的占比情況

#旅行自行車篩選
gather_customer_order_tour=gather_customer_order[gather_customer_order['cpzl_zw']=='旅游自行車']
#旅行自行車產(chǎn)品型號(hào)月銷量
gather_customer_order_tour_month=gather_customer_order_tour.groupby(['year_month','product_name']).order_num.sum().reset_index()
#每個(gè)月旅行自行車?yán)塾?jì)銷量
gather_customer_order_tour_month_sum=gather_customer_order_tour_month.groupby('year_month').order_num.sum().reset_index()
#各型號(hào)旅行自行車日銷售量占月銷售量比值
gather_customer_order_tour_num_proportion=pd.merge(gather_customer_order_tour_month,gather_customer_order_tour_month_sum,on='year_month')
gather_customer_order_tour_num_proportion=gather_customer_order_tour_num_proportion.rename(columns={'order_num_x':'order_num','order_num_y':'order_num_sum'})
gather_customer_order_tour_num_proportion['order_proportion']=gather_customer_order_tour_num_proportion['order_num']/gather_customer_order_tour_num_proportion['order_num_sum']
gather_customer_order_tour_num_proportion['cpzl_zw']='旅游自行車'
gather_customer_order_tour_num_proportion.head()

image.png

• 將以上公路/山地/旅行自行車信息合并

gather_customer_order_num_proportion=pd.concat([gather_customer_order_road_num_proportion,gather_customer_order_mountain_num_proportion,gather_customer_order_tour_num_proportion])
gather_customer_order_num_proportion.head()

image.png

• 計(jì)算2020年11月自行車銷量環(huán)比

gather_customer_order_num_proportion_10_11=\
gather_customer_order_num_proportion[gather_customer_order_num_proportion['year_month'].isin(['2020-10','2020-11'])]
#排序
gather_customer_order_num_proportion_10_11=gather_customer_order_num_proportion_10_11.sort_values(['product_name','year_month'])
#計(jì)算自行車銷售數(shù)量環(huán)比
gather_customer_order_num_proportion_10_11
product_name  = list(gather_customer_order_num_proportion_10_11.product_name.drop_duplicates())
order_top_x=pd.Series([])
for i in product_name:
    a=gather_customer_order_num_proportion_10_11.loc[gather_customer_order_num_proportion_10_11['product_name']==i]['order_num'].pct_change().fillna(0)
    order_top_x=order_top_x.append(a)
gather_customer_order_num_proportion_10_11['order_diff']=order_top_x
gather_customer_order_num_proportion_11=gather_customer_order_num_proportion_10_11[gather_customer_order_num_proportion_10_11['year_month']=='2020-11']
gather_customer_order_num_proportion_11.head()

image.png

• 計(jì)算2020年1-11月累計(jì)銷量

#1-11月自行車數(shù)據(jù)
gather_customer_order_num_proportion_1_11=gather_customer_order_num_proportion[gather_customer_order_num_proportion['year_month'].\
isin(['2020-01','2020-02','2020-03','2020-04','2020-05','2020-06','2020-07','2020-08','2020-09','2020-10','2020-11'])]
gather_customer_order_num_1_11_sum=gather_customer_order_num_proportion_1_11.groupby('product_name').order_num.sum().reset_index()
gather_customer_order_num_1_11_sum=gather_customer_order_num_1_11_sum.rename(columns={'order_num':'sum_order_1_11'})
gather_customer_order_num_1_11_sum.head()

image.png

• 2020年11月自行車產(chǎn)品銷量、環(huán)比、累計(jì)銷量
  累計(jì)銷量在gather_customer_order_num_1_11_sum已計(jì)算好，11月自行車環(huán)比及銷量占比在gather_customer_order_num_proportion_11已計(jì)算好，只需將兩表關(guān)聯(lián)起來。

gather_customer_order_num_proportion_11=pd.merge(gather_customer_order_num_proportion_11,gather_customer_order_num_1_11_sum,on='product_name')
gather_customer_order_num_proportion_11.head()

image.png

圖表展示：

image.png

2.4、用戶行為分析

• 從數(shù)據(jù)庫讀取訂單明細(xì)表

#讀取數(shù)據(jù)庫銷售訂單表
sql="select *  from ods_sales_orders where create_date>='2020-11-1' and  create_date<'2020-12-1'"
engine=create_engine('mysql+pymysql://frogdata001:Frogdata@144@106.12.180.221:3306/adventure_ods')
df_sales_orders_11=pd.read_sql(sql,con=engine)
df_sales_orders_11

image.png

• 讀取客戶信息表

sql="select * from ods_customer"
engine=create_engine('mysql+pymysql://frogdata001:Frogdata@144@106.12.180.221:3306/adventure_ods')
df_customer=pd.read_sql(sql,con=engine)
df_customer.head()

image.png

• 合并訂單表和客戶信息表

sales_customer_order_11=pd.merge(df_sales_orders_11,df_customer,on='customer_key',how='left')
sales_customer_order_11

image.png

• 客戶年齡分析
  • 獲取用戶出生年份作為新的一列

customer_birth_year = sales_customer_order_11['birth_date'].str.split('-',expand = True).rename(columns = {0:'birth_year'}).drop(labels = [1,2],axis = 1)
sales_customer_order_11 = pd.concat([sales_customer_order_11,customer_birth_year],axis = 1)
sales_customer_order_11.head(3)

image.png

• 用戶年齡分析

#修改出生年為int數(shù)據(jù)類型
sales_customer_order_11['birth_year'] = sales_customer_order_11['birth_year'].astype('int')
# 計(jì)算用戶年齡
sales_customer_order_11['customer_age'] = 2020 - sales_customer_order_11['birth_year']
新增'age_level'分層區(qū)間列
sales_customer_order_11['age_level'] = pd.cut(sales_customer_order_11['customer_age'], [30,35,40,45,50,55,60,65], labels=["30-34","35-39","40-44","45-49","50-54","55-59","60-64"])
#篩選銷售訂單為自行車的訂單信息
df_customer_order_bycle = sales_customer_order_11.loc[sales_customer_order_11['cplb_zw'] == '自行車']
# 計(jì)算年齡比率
df_customer_order_bycle['age_level_rate'] = 1 / len(df_customer_order_bycle)
#將年齡分為3個(gè)層次
df_customer_order_bycle.loc[(df_customer_order_bycle['customer_age'] <= 29),'age_level2'] = '<=29'
df_customer_order_bycle.loc[(df_customer_order_bycle['customer_age'] >= 30) & (df_customer_order_bycle['customer_age'] < 40),'age_level2'] = '30-39'
df_customer_order_bycle.loc[(df_customer_order_bycle['customer_age'] >= 40),'age_level2'] = '>=40'
df_customer_order_bycle.head(3)

image.png

• 求每個(gè)年齡段的人數(shù)

# 求每個(gè)年齡段人數(shù)
age_level2_count = df_customer_order_bycle.groupby(by = 'age_level2').sales_order_key.count().reset_index()
age_level2_count

image.png

• 用戶性別分析

gender_count = df_customer_order_bycle.groupby(by = 'gender').cplb_zw.count().reset_index()
# 求每個(gè)年齡段人數(shù)
age_level2_count = df_customer_order_bycle.groupby(by = 'age_level2').sales_order_key.count().reset_index()
# 關(guān)聯(lián)上 age_level2_count，也就是各年齡段的人數(shù)
df_customer_order_bycle = pd.merge(df_customer_order_bycle,age_level2_count,on = 'age_level2').rename(columns = {'sales_order_key_y':'age_level2_count'})
df_customer_order_bycle['age_level2_rate'] = 1/df_customer_order_bycle['age_level2_count']
# 關(guān)聯(lián)上 gender_count，也就是各個(gè)性別的人數(shù)
df_customer_order_bycle = pd.merge(df_customer_order_bycle,gender_count,on = 'gender').rename(columns = {'cplb_zw_y':'gender_count'})
df_customer_order_bycle['gender_rate'] = 1/df_customer_order_bycle['gender_count']
df_customer_order_bycle.head()

image.png

• 可視化圖表分析：

  
  
  image.png

2.5、2020年11月熱品銷售分析

• 11月產(chǎn)品銷量TOP10產(chǎn)品、銷量及環(huán)比
  前面計(jì)算，直接拿來用

#篩選11月數(shù)據(jù)
gather_customer_order_11 = gather_customer_order.loc[gather_customer_order['year_month'] == '2020-11']
customer_order_11_top10 = gather_customer_order_11.groupby(by = 'product_name').order_num.count().reset_index().\
                        sort_values(by = 'order_num',ascending = False).head(10)
customer_order_11_top10.head()

image.png

• 銷量前10的產(chǎn)品信息

#TOP10銷量產(chǎn)品信息
list(customer_order_11_top10['product_name'])

image.png

• 計(jì)算TOP10銷量及環(huán)比

gather_customer_order_num_proportion_10_11.head()
customer_order_num_proportion_10_11=gather_customer_order_num_proportion_10_11[['year_month','product_name','order_num_sum','cpzl_zw','order_diff']]
customer_order_num_proportion_10_11.head()

image.png

customer_order_num_proportion_10_11 = customer_order_num_proportion_10_11[customer_order_num_proportion_10_11['product_name'].\
                                                        isin(list(customer_order_11_top10['product_name']))]
customer_order_num_proportion_10_11['category'] = '本月TOP10銷量'
customer_order_num_proportion_10_11.head()

image.png

• 11月增速TOP10產(chǎn)品、銷售及環(huán)比

customer_order_num_proportion_11=gather_customer_order_num_proportion_10_11.loc[gather_customer_order_num_proportion_10_11['year_month'] == '2020-11'].\
                            sort_values(by = 'order_diff',ascending = False).head(10)
customer_order_num_proportion_11.head()

image.png

• 篩選TOP10增速產(chǎn)品

customer_order_num_proportion_11_top10_seep = gather_customer_order_num_proportion_10_11.loc[gather_customer_order_num_proportion_10_11['product_name'].\
                                                        isin(list(customer_order_num_proportion_11['product_name']))]
#篩選出所需字段
customer_order_num_proportion_11_top10_seep = customer_order_num_proportion_11_top10_seep[['year_month','product_name','order_num_sum','cpzl_zw','order_diff']]
customer_order_num_proportion_11_top10_seep['category'] = '本月TOP10增速'
customer_order_num_proportion_11_top10_seep.head()

image.png

• 合并TOP10銷量表customer_order_month_10_11，TOP10增速customer_order_month_11_top10_seep

# axis = 0按照行維度合并，axis = 1按照列維度合并
hot_products_11 = pd.concat([customer_order_num_proportion_10_11,customer_order_num_proportion_11_top10_seep],axis = 0)
hot_products_11.tail()

image.png

圖表展示：

image.png

三、看板展示

部門看板展示：

image.png