写《2020年11月线上自行车业务分析报告》

一、分析报告的背景：

2020年12月业务组组长需要向领导汇报2020年11月自行车销售情况，为精细化运营提供数据支持，能精准的定位目标客户群体。

二、分析目的：

1、如何制定销售策略，调整产品结构，才能保持高速增长，获取更多的收益，占领更多市场份额，是公司最关心的问题。
2、报告通过对整个公司的产品销量持续监测和分析，掌握公司产品销售状况、走势的变化，为客户制订、调整和检查销售策略，完善产品结构提供依据。
3、数据来源与表名，基础表存于Mysql数据库, 数据库名：dadventure_ods
dw_customer_order 时间地区产品聚合表

三、分析思路：

a．从整体的角度：分析2020.01—2020.11产品整体销售表现
b．从地域的角度：分析11月每个区域销售表现、11月TOP10城市销售表现
c．从产品的角度：分析11月类别产品销售量表现、11月细分产品销售量表现
d．热销产品：分析11月TOP10产品销量榜、11月TOP10销量增速榜
e.用户行为分析：按照年龄和性别分析用户消费行为。

四、展示形式：PPT

PPT预览：

五、分析过程

用到的工具有：
使用SQL和Python查询并统计汇总数据，使用PowerBi画图。
操作流程：

1.数据准备与清洗

1. 获取数据

#导入模块
import pandas as pd
import numpy as np
import pymysql
pymysql.install_as_MySQLdb()
from sqlalchemy import create_engine
#读取源数据
#创建数据库引擎
engine = create_engine('mysql://xxxx:xxxx@120.25.xxx.xxx:3306/adventure_dw?charset=gbk')
gather_customer_order=pd.read_sql_query("select * from dw_customer_order",con = engine)

2. 清洗数据
   增加create_year_month月份字段。按月维度分析时使用

gather_customer_order['create_date'] = gather_customer_order['create_date'].apply(pd.to_datetime)
#增加create_year_month月份字段。按月维度分析时使用
gather_customer_order['create_year_month'] = gather_customer_order['create_date'].apply(lambda x:x.strftime('%Y-%m'))

3. 筛选数据
   从产品种类中筛选出自行车产品

#筛选产品类别为自行车的数据
gather_customer_order = gather_customer_order[gather_customer_order['cplb_zw'] =='自行车']
gather_customer_order.head()

2.整体销售表现

1. 按月计算每月销售量和销售额

#每月订单数量和销售金额，用groupby创建一个新的对象，需要将order_num、sum_amount求和

overall_sales_performance = gather_customer_order.groupby('create_year_month').agg({
    'order_num':'sum',
    'sum_amount':'sum'
}).reset_index()

2. 计算销量和销售额环比

overall_sales_performance['order_num_diff'] = overall_sales_performance['order_num'].pct_change().fillna(0)
overall_sales_performance['sum_amount_diff'] = overall_sales_performance['sum_amount'].pct_change().fillna(0)

3. 将整理好的数据存入Mysql数据库

#将数据存入数据库
overall_sales_performance.to_sql('pt_overall_sale_performance_1_rc',con = engine,if_exists='replace', index=False)

4. 可视化展示
   （1）自行车销售量走势图
   · 近11个月，11月销量最高，为3438台设备，较10月同比增长14.14%
   （2）自行车销售金额走势图
   ·近11个月，11月销量金额最高，为657万元，较10月同比增长17.84%
   ·产品销售金额和销售数量趋势一致
   

3.地域销售表现

1. 2020年11月地区销售表现

• 按照区域、月分组,订单量求和，销售金额求和

#筛选10月11月的数据
gather_customer_order_10_11 = gather_customer_order[gather_customer_order['create_year_month'].isin(['2020-10','2020-11'])]
#按照区域、月分组,订单量求和，销售金额求和
gather_customer_order_10_11_group= gather_customer_order_10_11.groupby(['chinese_territory','create_year_month']).agg({
    'order_num':sum,
    'sum_amount':sum
}).reset_index()

• 分析不同地区的11月销售环比情况

#将区域存为列表
region_list= gather_customer_order_10_11_group['chinese_territory'].unique()
#pct_change()当前元素与先前元素的相差百分比,求不同区域10月11月环比
order_x = pd.Series([])
amount_x = pd.Series([])
for i in region_list:
    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
#10月11月各个区域自行车销售数量、销售金额环比
gather_customer_order_10_11_group.head()

• 将数据存入数据库

gather_customer_order_10_11_group.to_sql('pt_bicy_november_territory_2_rc',con = engine,if_exists='replace',index=False)

2. 2020年11月Top10城市销量表现

• 筛选出11月销量TOP10的城市名称

#筛选11月自行车的交易数据
gather_customer_order_11 = gather_customer_order_10_11[gather_customer_order_10_11['create_year_month']=='2020-11']
gather_customer_order_city_11= gather_customer_order_11.groupby('chinese_city').agg({'order_num':sum}).reset_index()
#11月自行车的销售数量前十城市
gather_customer_order_city_head = gather_customer_order_city_11.sort_values(by='order_num',ascending = False).head(10)

• 从10月11月数据中筛选出前10销量城市的数据
• 分组计算前十城市，自行车销售数量和销售金额，并计算环比

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'])]
分组计算前十城市，销售数量销售金额
gather_customer_order_city_10_11 = gather_customer_order_10_11_head.groupby(['chinese_city','create_year_month']).agg({
    'order_num':sum,
    'sum_amount':sum
}).reset_index()

#计算前十城市环比
city_top_list = list(gather_customer_order_city_10_11['chinese_city'].unique())
order_top_x = pd.Series([])
amount_top_x = pd.Series([])
for i in city_top_list:
    #print(i)
    a=gather_customer_order_city_10_11[gather_customer_order_city_10_11['chinese_city']==i]['order_num'].pct_change().fillna(0)
    b=gather_customer_order_city_10_11[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)
    
#order_diff销售数量环比，amount_diff销售金额环比
gather_customer_order_city_10_11['order_diff']=order_top_x
gather_customer_order_city_10_11['amount_diff']=amount_top_x
gather_customer_order_city_10_11.head(5)

• 将数据存入数据库

gather_customer_order_city_10_11.to_sql('pt_bicy_november_october_city_3_rc',con = engine,if_exists='replace',index=False)

3. 可视化展示
   （1）地域销量环比增速
   · 11月华东地区销量在七个地区最高
   · 较10月，西北地区增长28%，增速最快
   （2）地域销售金额环比增速
   · 11月华东地区销售金额最多
   · 较10月，西北地区销售金额增长32%，增速最快
   （3）Top10城市销量表现
   · 北京市销量最高
   · 郑州市销量环比最高，达到100%
   · 11月Top10城市市场份额总占比13.38%
   
   （4）Top10城市销售金额表现
   · 11月北京市的销售金额最高
   ·较10月，郑州市的销售金额增长最快，为1.7倍
   

4.产品销售表现

1. 分析11月类别产品销售量表现

• 求每个月自行车累计销售数量
• 合并自行车销售信息表+自行车每月累计销售数量表
• 计算每一个订单在当月的占比（产品销量/产品每月销量占比），为之后按照细分市场聚合做准备

#求每个月自行车累计销售数量
gather_customer_order_group_month = gather_customer_order.groupby('create_year_month').agg({'order_num':sum}).reset_index()
#合并自行车销售信息表+自行车每月累计销售数量表，pd.merge
order_num_proportion = pd.merge(gather_customer_order,gather_customer_order_group_month,on='create_year_month')
#计算自行车销量/自行车每月销量占比
order_num_proportion['order_proportion'] = order_num_proportion['order_num_x'] / order_num_proportion['order_num_y']
#重命名sum_month_order：自行车每月销售量
order_num_proportion = order_num_proportion.rename(columns={'order_num_y':'sum_month_order'})

• 将数据存入数据库

order_num_proportion.to_sql('pt_bicycle_product_sales_month_4_rc',con = engine,if_exists='append',index=False)

2. 分析11月细分产品销售量表现

• 查看有那些产品子类

#查看有那些产品子类
gather_customer_order['cpzl_zw'].unique()
# array(['公路自行车', '山地自行车', '旅游自行车'], dtype=object)

• 筛选出各个细分产品的数据
• 按照时间维度（月份）、信息维度（细分产品的不同型号）进行分组对销量进行求和
• 计算每个细分产品不同型号的占比情况

# 公路自行车
gather_customer_order_road = gather_customer_order[gather_customer_order['cpzl_zw'] == '公路自行车']
#求公路自行车不同型号产品销售数量
gather_customer_order_road_month = gather_customer_order_road.groupby(by = ['create_year_month','product_name']).order_num.sum().reset_index()
gather_customer_order_road_month['cpzl_zw'] = '公路自行车'
#每个月公路自行车累计销售数量
gather_customer_order_road_month_sum = gather_customer_order_road_month.groupby('create_year_month')['order_num'].sum().reset_index()
#合并公路自行车gather_customer_order_road_month与每月累计销售数量
#用于计算不同型号产品的占比
gather_customer_order_road_month = pd.merge(gather_customer_order_road_month,gather_customer_order_road_month_sum,on='create_year_month')

# 山地自行车
gather_customer_order_Mountain = gather_customer_order[gather_customer_order['cpzl_zw'] == '山地自行车']
#求山地自行车不同型号产品销售数量
gather_customer_order_Mountain_month = gather_customer_order_Mountain.groupby(by = ['create_year_month','product_name']).order_num.sum().reset_index()
gather_customer_order_Mountain_month['cpzl_zw'] = '山地自行车'
#每个月山地自行车累计销售数量
gather_customer_order_Mountain_month_sum = gather_customer_order_Mountain_month.groupby('create_year_month')['order_num'].sum().reset_index()
#合并山地自行车gather_customer_order_Mountain_month与每月累计销售数量
#用于计算不同型号产品的占比
gather_customer_order_Mountain_month = pd.merge(gather_customer_order_Mountain_month,gather_customer_order_Mountain_month_sum,on='create_year_month')

# 旅游自行车
gather_customer_order_tour = gather_customer_order[gather_customer_order['cpzl_zw'] == '旅游自行车']
#求旅游自行车不同型号产品销售数量
gather_customer_order_tour_month = gather_customer_order_tour.groupby(by = ['create_year_month','product_name']).order_num.sum().reset_index()
gather_customer_order_tour_month['cpzl_zw'] = '旅游自行车'
#每个月旅游自行车累计销售数量
gather_customer_order_tour_month_sum = gather_customer_order_tour_month.groupby('create_year_month')['order_num'].sum().reset_index()
#合并旅游自行车gather_customer_order_tour_month与每月累计销售数量
#用于计算不同型号产品的占比
gather_customer_order_tour_month = pd.merge(gather_customer_order_tour_month,gather_customer_order_tour_month_sum,on='create_year_month')

#将'山地自行车', '公路自行车', '旅游自行车'每月销量信息合并
gather_customer_order_month = pd.concat([gather_customer_order_road_month,gather_customer_order_Mountain_month,gather_customer_order_tour_month])

#各类自行车产品，销售量占每月总销售量比率
gather_customer_order_month['order_num_proportio'] = gather_customer_order_month['order_num_x'] / gather_customer_order_month['order_num_y']
#order_month_product当月产品子类累计销量
#sum_order_month当月产品类型的总销量
gather_customer_order_month.rename(columns={'order_num_x':'order_month_product','order_num_y':'sum_order_month'},inplace=True)
gather_customer_order_month.head()

• 将数据存入数据库

gather_customer_order_month.to_sql('pt_bicycle_product_sales_order_month_4_rc',con = engine,if_exists='append',index=False)

3. 2020年11月自行车环比情况

• 先筛选10月11月数据
• 将10月11月销售信息排序
• 计算自行车销售数量环比

#计算11月环比，先筛选10月11月数据
gather_customer_order_month_10_11 = gather_customer_order_month[gather_customer_order_month.create_year_month.isin(['2020-10','2020-11'])]
#排序。将10月11月销售信息排序
gather_customer_order_month_10_11 = gather_customer_order_month_10_11.sort_values(by = ['product_name','create_year_month'])
#计算销售数量环比
product_name_list  = list(gather_customer_order_month_10_11.product_name.drop_duplicates())
order_top_x = pd.Series([])
for i in product_name_list:
    a = gather_customer_order_month_10_11[gather_customer_order_month_10_11['product_name']==i]['order_month_product'].pct_change().fillna(0)
    order_top_x = order_top_x.append(a)
gather_customer_order_month_10_11['order_num_diff'] = order_top_x

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

#筛选出11月数据
gather_customer_order_month_11 = gather_customer_order_month_10_11[gather_customer_order_month_10_11['create_year_month'] == '2020-11']
#筛选2020年1月至11月数据
gather_customer_order_month_1_11 = gather_customer_order_month[gather_customer_order_month['create_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'])]
#计算2020年1月至11月累计销量
gather_customer_order_month_1_11_sum = gather_customer_order_month_1_11.groupby(by = 'product_name').order_month_product.sum().reset_index()
#重命名sum_order_1_11：1-11月产品累计销量
gather_customer_order_month_1_11_sum = gather_customer_order_month_1_11_sum.rename(columns = {'order_month_product':'sum_order_1_11'})
#按相同字段product_name产品名，合并两张表
gather_customer_order_month_11 = pd.merge(gather_customer_order_month_11,gather_customer_order_month_1_11_sum,on='product_name')
gather_customer_order_month_11.head()

• . 将数据存入数据库

gather_customer_order_month_11.to_sql('pt_bicycle_product_sales_order_month_11_rc',con = engine,if_exists='append',index=False)

4. 可视化展示
   （1）细分市场销量表现
   · 三种产品类型每月销量变化不大
   · 11月公路自行车销量占比最高
   · 较10月，旅游自行车销量增速最快
   

（2）公路自行车细分市场销量表现
·11月除型号AWJ455-232、AWJ500-232外，其他产品销量环比都呈上升趋势
·较10月，型号AWJ450-232销量增速最快，为24.92%
·型号AWJ450-232销量份额占比最大，为21.88%
（3）旅游自行车细分市场销量表现
·11月除型号MR850-232、MR800-485外，其他产品销量环比都呈上升趋
·较10月，型号MR900-232销量增速最快，为40.18%
·型号MR900-232销量份额占比最大，为31.21%

（4）山地自行车细分市场销量表现
·11月除型号AJ500-485外，其他产品销量环比都呈上升趋势
·较10月，型号AWS550-232销量增速最快，为54.55%
·型号AWS650-485销量份额占比最大，为72.65%

5.热销产品分析

1. 2020年11月销量TOP10产品

• 筛选11月数据
• 按照销量降序，取TOP10产品
• 筛选出Top10销量产品的数据

#筛选11月数据
gather_customer_order_11 = gather_customer_order.loc[gather_customer_order['create_year_month'] == '2020-11']
#按照销量降序，取TOP10产品
customer_order_11_top10 = gather_customer_order_11.groupby(by = 'product_name').order_num.sum().reset_index().\
                        sort_values(by = 'order_num',ascending = False).head(10)
                        customer_order_month_10_11 = gather_customer_order_month_10_11[['create_year_month','product_name','order_month_product','cpzl_zw','order_num_diff']]
customer_order_month_10_11 = customer_order_month_10_11[customer_order_month_10_11['product_name'].\
                                                        isin(list(customer_order_11_top10['product_name']))]
customer_order_month_10_11['category'] = '本月TOP10销量'

2. 2020年11月销量增速TOP10产品

• 筛选11月数据
• 按照销量环比降序，取TOP10产品
• 筛选出Top10销量增速产品的数据

customer_order_month_11 = gather_customer_order_month_10_11.loc[gather_customer_order_month_10_11['create_year_month'] == '2020-11'].\
                            sort_values(by = 'order_num_diff',ascending = False).head(10)
customer_order_month_11_top10_seep = gather_customer_order_month_10_11.loc[gather_customer_order_month_10_11['product_name'].\
                                                        isin(list(customer_order_month_11['product_name']))]
customer_order_month_11_top10_seep = customer_order_month_11_top10_seep[['create_year_month','product_name','order_month_product','cpzl_zw','order_num_diff']]
customer_order_month_11_top10_seep['category'] = '本月TOP10增速'

• 合并数据：将TOP10销量表和TOP10增速表拼接在一起

#axis = 0按照行维度合并，axis = 1按照列维度合并
hot_products_11 = pd.concat([customer_order_month_10_11,customer_order_month_11_top10_seep],axis = 0)

3. 将数据存入数据库

hot_products_11.to_sql('pt_hot_products_november_rc',con = engine,if_exists='append',index=False)

4. 可视化展示
   （1）11月Top10销量榜
   · 11月型号AWS650-485销量最高，为1563台，较10月增长14.4%
   
   （2）11月Top10增速榜
   · 11月型号AWS550-232增速最快，较10月增长54.55%
   

6.用户行为分析

• 用户年龄分析

1. 读取数据库的客户信息表和订单明细表，并将这两张表关联起来

#读取数据库客户信息表
con = pymysql.connect(host = 'IP',user = '####',password = '####', database = 'adventure_ods',charset = 'gbk',use_unicode = True)
# sql命令
sql_cmd = "select customer_key,birth_date,gender,marital_status from ods_customer where create_date < '2019-12-1'"
df_CUSTOMER = pd.read_sql(sql_cmd,con)
#读取数据库销售订单表
df_sales_orders_11 = pd.read_sql("select *  from ods_sales_orders where create_date>='2019-11-1' and   create_date<'2019-12-1'",con)
df_sales_orders_11['customer_key'] = df_sales_orders_11['customer_key'].astype(int)

sales_customer_order_11=pd.merge(df_sales_orders_11,df_CUSTOMER,on='customer_key',how='inner')

2.利用split获取客人的年份作为新的一列,作为客户的出生年份

sales_customer_order_11['birth_date'] = sales_customer_order_11['birth_date'].astype(str)
customer_birth_year = sales_customer_order_11['birth_date'].apply(lambda x:x.split('-',1)[0])
customer_birth_year.name = 'birth_year'
sales_customer_order_11 = pd.concat([sales_customer_order_11,customer_birth_year],axis = 1)

3.计算用户年龄，并对年龄进行分层，划分层次为"30-34",“35-39”,“40-44”,“45-49”,“50-54”,“55-59”,“60-64”

#修改出生年为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']
#年龄分层1
bins = [30,35,40,45,50,55,60,65]
labels=["30-34","35-39","40-44","45-49","50-54","55-59","60-64"]
#新增'age_level'分层区间列
sales_customer_order_11['age_level'] = pd.cut(sales_customer_order_11['customer_age'],bins=bins,labels=labels)

4.计算各年龄段比率

#筛选销售订单为自行车的订单信息
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)

5.将年龄分为3个层次，分别为’<=29’、‘30-39’、’>=40’

#将年龄分为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']<=39),'age_level2'] = '30-39'
df_customer_order_bycle.loc[df_customer_order_bycle['customer_age']>=40,'age_level2'] = '>=40'

6.将数据存入数据库

df_customer_order_bycle.to_sql('pt_user_behavior_november_rc',con = engine,if_exists='append',index=False)

7.可视化展示
（1）不同年龄消费情况占比
根据图可以看出：

• 消费者的年龄在30岁以上；
• 35-39岁的消费者占较大部分，约占36%；
• 随着年龄的增长消费力逐渐下降

（2）不同年龄具体消费情况占比
根据图可以看出：

• 两个年龄段中购买公路自行车占主要部分，购买旅游自行车的的占比最小。

（3）男女消费者消费占比情况
根据图可以看出：

• 男性与女性购买自行车占比几乎相同 。

（4）男女具体消费情况占比
根据图可以看出：

• 男性和女性购买公路自行车占比最高，购买旅游自行车占比最少 。