本文是对某自行车制造公司2020年11月销售情况进行分析总结，为精细化运营提供数据支持。
本文目录：
一、 分析目的
二、分析过程
三、看板展示

一、分析目的

通过对整个公司的自行车销量持续监测和分析，掌握公司自行车销售状况、走势的变化，为客户制订、调整和检查销售策略，完善产品结构提供依据。

二、分析过程

分析过程导图如下：

image.png

具体分析如下:

2.1 自行车整体销售表现

• 导入模块包

#导入模块
import pandas as pd
import numpy as np
import pymysql
#pymysql.install_as_MySQLdb()
from sqlalchemy import create_engine

• 读取数据

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

• 查看数据源类型

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

• 筛选产品类型为自行车的数据

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

image.png

• 整体销量表现：按月份分组聚合总订单量和总销售额

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

• ·计算订单量和销售金额环比

#计算订单量环比
order_num_diff=list(overall_sales_performance.order_num.diff()/overall_sales_performance.order_num/-1)
order_num_diff.pop(0) #删除列表的第一个元素
order_num_diff.append(0) #列表末尾增加元素0
order_num_diff
overall_sales_performance['order_num_diff']=order_num_diff
# 计算销售金额环比
sum_amount_diff=list(overall_sales_performance.sum_amount.diff()/overall_sales_performance.sum_amount/-1)
sum_amount_diff.pop(0) #删除列表的第一个元素
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月自行车地域销售表现

• 数据清洗，筛选10月、11月自行车销售数据

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月各区域销售量情况
• 按区域、月份进行分组聚合

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

• 求各大区域10月、11月环比

#将区域存为列表
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月环比

#11月份销量数据
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销售数据
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'])]
#分组计算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月自行车产品销售表现

• 细分市场销量表现
  • 日销售量占月销售量比例

#细分市场销售表现
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')
#计算自行车日销售占月销售比
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

• 公路/山地/旅行自行车细分市场销售表现
  • 公路自行车各型号产品与月累计销量比值

#公路/山地/旅行自行车销售表现
#公路自行车筛选
gather_customer_order_road=gather_customer_order[gather_customer_order['cpzl_zw']=='公路自行车']
#公路自行车产品型号月销量
gather_customer_order_road_month=gather_customer_order_road.groupby(['year_month','product_name']).order_num.sum().reset_index()
#每个月公路自行车累计销量
gather_customer_order_road_month_sum=gather_customer_order_road_month.groupby('year_month').order_num.sum().reset_index()
#各型号公路自行车日销售量占月销售量比值
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

• 相同的步骤，得到每月各型号山地自行车销售量占月总销售量比值

#山地自行车筛选
gather_customer_order_mountain=gather_customer_order[gather_customer_order['cpzl_zw']=='山地自行车']
#山地自行车产品型号月销量
gather_customer_order_mountain_month=gather_customer_order_mountain.groupby(['year_month','product_name']).order_num.sum().reset_index()
#每个月山地自行车累计销量
gather_customer_order_mountain_month_sum=gather_customer_order_mountain_month.groupby('year_month').order_num.sum().reset_index()
#各型号山地自行车日销售量占月销售量比值
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']=='旅游自行车']
#旅行自行车产品型号月销量
gather_customer_order_tour_month=gather_customer_order_tour.groupby(['year_month','product_name']).order_num.sum().reset_index()
#每个月旅行自行车累计销量
gather_customer_order_tour_month_sum=gather_customer_order_tour_month.groupby('year_month').order_num.sum().reset_index()
#各型号旅行自行车日销售量占月销售量比值
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

• 计算2020年11月自行车销量环比

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'])
#计算自行车销售数量环比
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

• 计算2020年1-11月累计销量

#1-11月自行车数据
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月自行车产品销量、环比、累计销量
  累计销量在gather_customer_order_num_1_11_sum已计算好，11月自行车环比及销量占比在gather_customer_order_num_proportion_11已计算好，只需将两表关联起来。

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、用户行为分析

• 从数据库读取订单明细表

#读取数据库销售订单表
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数据类型
sales_customer_order_11['birth_year'] = sales_customer_order_11['birth_year'].astype('int')
# 计算用户年龄
sales_customer_order_11['customer_age'] = 2020 - sales_customer_order_11['birth_year']
新增'age_level'分层区间列
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'] == '自行车']
# 计算年龄比率
df_customer_order_bycle['age_level_rate'] = 1 / len(df_customer_order_bycle)
#将年龄分为3个层次
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

• 求每个年龄段的人数

# 求每个年龄段人数
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()
# 求每个年龄段人数
age_level2_count = df_customer_order_bycle.groupby(by = 'age_level2').sales_order_key.count().reset_index()
# 关联上 age_level2_count，也就是各年龄段的人数
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']
# 关联上 gender_count，也就是各个性别的人数
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月产品销量TOP10产品、销量及环比
  前面计算，直接拿来用

#筛选11月数据
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的产品信息

#TOP10销量产品信息
list(customer_order_11_top10['product_name'])

image.png

• 计算TOP10销量及环比

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产品、销售及环比

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增速产品

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