（一）项目介绍

公司业务简介：
Adventure Works Cycle主要生产和销售金属和复合材料自行车，并在国内各个市场销售。公司前期通过分销商模式达成收入目标。在2019年为进一步扩大市场和销售额，实行利用公司网站进行销售的新模式。

分析背景：
需要向领导汇报2019年11月自行车销售情况，为精细化运营提供数据支持，能精准的定位目标客户群体。

分析目的：
1.从整体上，掌握公司自行车销售状况、走势的变化。
2.从细节上，如何制定销售策略，调整产品结构，才能保持高速增长，获取更多的收益，占领更多市场份额。

数据源介绍：
1.dw_customer_order 时间地区产品聚合表——用于整体销售表现，地域销售表现，产品销售表现，热品销售分析。
2.ods_customer 每日新增用户表——用户用户行为分析。
3.ods_sales_orders 订单明细表——用于用户行为分析。

dw_customer_order

ods_customer

ods_sales_orders

指标介绍：
参考下一节分析过程和思路图

使用工具：
Python进行数据处理，powerbi对处理好的数据进行可视化和分析，excel制作表格

（二）分析过程和思路

在开篇之前附上分析过程和思路图

1. 整体销售表现

1.1从数据库读取源数据：dw_customer_order

导入必要模块

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

连接mysql获取表格信息

#读取源数据。不同城市，每天产品销售信息
#创建数据库引擎
engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
gather_customer_order=pd.read_sql_query('select * from dw_customer_order',con=engine)

查看数据前5行，看数据是否正常

#查看源数据前5行，观察数据，判断数据是否正常识别
gather_customer_order.head()

查看数据类型，对数据有初步了解

#查看数据类型
gather_customer_order.info()

因为要按月分组，因此在表格后面新增一列create_year_month，用于储存年月信息。

#增加create_year_month月份字段，按月维度分析时使用。strftime将datetime对象和pandas的timestamp对象进行格式化，转化成字符串。
gather_customer_order['create_year_month']=gather_customer_order.create_date.apply(lambda x:x.strftime('%Y-%m'))
gather_customer_order['create_year_month']

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

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

1.2自行车整体销售量表现

按照年月分组，对销售量和销售金额进行求和，并降序排序，方便计算环比

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

利用diff()函数计算环比

#求每月自行车销售订单量环比，观察最近一年数据变化趋势
#环比是本月与上月的对比，例如本期2019-02月销售额与上一期2019-01月销售额做对比
order_num_diff = list(overall_sales_performance.order_num.diff()/-(overall_sales_performance.order_num))
order_num_diff.pop(0) #删除列表中第一个元素
order_num_diff.append(0) #将0新增到列表末尾
order_num_diff

将上面的环比列表转换为DataFrame并与之前的分组的表连接（接在最后一列），再重新赋值给分组的表

#将环比转化为DataFrame
overall_sales_performance = pd.concat([overall_sales_performance,pd.DataFrame(order_num_diff,columns=['order_num_diff'])],axis=1)
overall_sales_performance

1.3自行车整体销售金额表现

按照之前销售量环比计算步骤，同理操作

#求每月自行车销售金额环比
sum_amount_diff = list(overall_sales_performance.sum_amount.diff()/-(overall_sales_performance.sum_amount))
sum_amount_diff.pop(0) #删除列表中第一个元素
sum_amount_diff.append(0) #将0新增到列表末尾
sum_amount_diff
#将环比转化为DataFrame
overall_sales_performance = pd.concat([overall_sales_performance,pd.DataFrame(sum_amount_diff,columns=['sum_amount_diff'])],axis=1)
#销量环比字段名order_diff，销售金额环比字段名amount_diff
#按照日期排序，升序
overall_sales_performance = overall_sales_performance.sort_values('create_year_month')
#查看每月自行车订单量、销售金额、环比、前5行
overall_sales_performance.head(5)

将数据存入数据库

#将数据存入数据库
engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
overall_sales_performance.to_sql('pt_overall_sale_performance_1_vayne',con=engine, index=False,if_exists='append')

1.4可视化展现和分析

自行车销售量走势图

由图可知，在2019年1月至11月期间，11月份自行车销售量最多，为3316辆；较10月份增长7.1%。

自行车销售金额走势图

由图可知，在2019年1月至11月期间，11月份自行车销售金额最多，为619万元；同时11月销售额环比增速也是最快的，较10月份增长8.7%。
自行车销售量和销售金额趋势一致。
11月份是今年首次销量额突破600万，之前月份销售额一般在570万左右小幅度波动。可以再深入挖掘具体是什么原因促使11月份销售额远高于其他月份，是特殊情况，还是一般情况？可参考去年月份的图表看11月销售额，也需要参考公司是否有做促销活动等信息。

2. 地域销售表现

2.1源数据dw_customer_order，数据清洗筛选10月11月数据

筛选10月11月数据

gather_customer_order_10_11 = gather_customer_order[(gather_customer_order.create_year_month=='2019-10')|(gather_customer_order.create_year_month=='2019-11')]
gather_customer_order_10_11.head()

2.2 2019年11月自行车区域销售量表现

按照区域和月份联合分组，对销售量，销售金额求和并重置索引形成DataFrame

#按照区域、月分组,订单量求和，销售金额求和
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()
gather_customer_order_10_11_group.head()

提取包含的所有区域并储存在列表中，为计算环比做准备

#将区域存为列表
region_list=list(gather_customer_order_10_11_group.chinese_territory.unique())
region_list

生成order_x和amount_x两个空Series，分别用于储存销售量环比和销售金额环比。通过for循环，对各个区域进行遍历。同时，对各个区域通过pct_change()函数计算环比。因为这里没有9月数据，所以10月环比为NaN，因此通过fillna(0)来填充。之后将销售量环比和销售金额环比分别存入order_x和amount_x两个Series。最后在原表格后面新增两列，将之前的两个Series接在原表最右侧。

#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()

将表格存入数据库

engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
gather_customer_order_10_11_group.to_sql('pt_bicy_november_territory_2_vayne',con=engine, index=False,if_exists='append')

2.3 2019年11月自行车销售量TOP10城市环比

首先筛选2019年11月份数据。然后按照城市进行分组，并对销售数量进行求和，重置索引转化为DataFrame。
之后按照销售数量降序排序，取出排名前十的数据。

#筛选11月自行车交易数据
gather_customer_order_11 = gather_customer_order[gather_customer_order.create_year_month=='2019-11']
gather_customer_order_city_11= gather_customer_order_11.groupby('chinese_city').order_num.sum().reset_index()
#11月自行车销售数量前十城市
gather_customer_order_city_head = gather_customer_order_city_11.sort_values('order_num',ascending=False).iloc[0:10]
gather_customer_order_city_head

查看10月11月自行车销售数据

#查看10月11月自行车销售数据gather_customer_order_10_11
gather_customer_order_10_11.head()

通过isin()，筛选销售量前十城市10月11月自行车销售数据

#筛选销售前十城市，10月11月自行车销售数据
gather_customer_order_10_11_head = gather_customer_order_10_11[gather_customer_order_10_11.chinese_city.isin(list(gather_customer_order_city_head.chinese_city))]
gather_customer_order_10_11_head.head(10)

将上表按照城市和月份共同分组，对销售量和销售金额求和

#分组计算前十城市，自行车销售数量销售金额
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()
gather_customer_order_city_10_11.head()

参照2.2节的方法，对销售量前十的城市计算11月份销售量和销售金额环比

#计算前十城市环比
city_top_list = list(gather_customer_order_city_head.chinese_city)
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)

将表格存入数据库

engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
gather_customer_order_city_10_11.to_sql('pt_bicy_november_october_city_3_vayne',con=engine, index=False,if_exists='append')

2.4 可视化展现和分析

区域销售表现图

由图可知，11月份华东地区在8个区域中销售量最多，达927辆。较10月份，华南地区销售量增速最快，达15%。
华东地区销量远大于其他地区，比第二名华中地区高63.5%。其他区域之间销量差距逐步缩小。

Top10城市销售量表现图

城市市场份额占比图

由图可知，北京市和上海市是10月份11月份自行车销售市场的主力军。同时，郑州市环比增速最快，达48%。
Top10城市市场份额占比13.48%。（注：城市市场份额=城市销售量/总销售量）

3. 产品销售表现

3.1 数据源 dw_customer_order

查看数据源

#gather_customer_order在分析自行车整体表现得时已从数据库导入表（ dw_customer_order），并筛选仅自行车数据，这里不再导入
#查看数据源
gather_customer_order.head(3)

3.2 细分市场销售表现

按月分组，对销售数量求和得到每月自行车销售总量

#求每个月自行车累计销售数量
gather_customer_order_group_month = gather_customer_order.groupby('create_year_month').order_num.sum().reset_index()
gather_customer_order_group_month.head()

通过pd.merge，以create_year_month为主键，连接自行车销售信息表和上表

#合并自行车销售信息表+自行车每月累计销售数量表，pd.merge
order_num_proportion = pd.merge(gather_customer_order,gather_customer_order_group_month,on='create_year_month',how='inner')
order_num_proportion.head()

通过自行车销量/自行车每月销量计算每单每月的销售量占比。在表格后面新增一列用于存储上述数据，同时修改列名。

#计算自行车销量/自行车每月销量占比
order_num_proportion['order_proportion'] = order_num_proportion.apply(lambda x:x.order_num_x/x.order_num_y, axis=1)
#重命名sum_month_order：自行车每月销售量
order_num_proportion = order_num_proportion.rename(columns={'order_num_y':'sum_month_order'})
order_num_proportion.head()

将表格存入数据库

engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
order_num_proportion.to_sql('pt_bicycle_product_sales_month_4_vayne',con=engine, index=False,if_exists='append')

3.3 公路/山地/旅游自行车细分市场表现

3.3.1 公路自行车细分市场表现

查看自行车有哪些产品子类

#查看自行车有哪些产品子类
gather_customer_order['cpzl_zw'].unique()

筛选公路自行车有销售数据，并根据月份和产品型号联合分组，对销售量求和并重置索引。在表格后面新增一列，为每一行注明公路自行车，方便区别和筛选。

#求公路自行车不同型号产品销售数量
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

按照月份分组，求每个月公路自行车累计销售数量

#每个月公路自行车累计销售数量
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_sum.head()

在gather_customer_order_road_month基础上，合并公路自行车每月累计销售数量gather_customer_order_road_month_sum，主键为'create_year_month'。用于计算不同型号产品的占比。

gather_customer_order_road_month = pd.merge(gather_customer_order_road_month,gather_customer_order_road_month_sum,on='create_year_month',how='inner')
gather_customer_order_road_month

3.3.2 山地自行车细分市场表现

与公路自行车处理过程一致，赋予变量gather_customer_order_Mountain筛选山地自行车→求山地自行车不同型号的产品销售数量→求每月累计销售数量→合并→目的是用于产品子类比较环比

# 筛选
gather_customer_order_Mountain = gather_customer_order[gather_customer_order['cpzl_zw'] == '山地自行车']
#求山地自行车不同型号产品销售数量
gather_customer_order_Mountain_month = gather_customer_order_Mountain.groupby(['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()

#合并山地自行车hz_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',how='inner')
gather_customer_order_Mountain_month

3.3.3 旅游自行车细分市场表现

gather_customer_order_tour = gather_customer_order[gather_customer_order['cpzl_zw'] == '旅游自行车']
#求旅游自行车不同型号产品销售数量
gather_customer_order_tour_month = gather_customer_order_tour.groupby(['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 = pd.merge(gather_customer_order_tour_month,gather_customer_order_tour_month_sum,on='create_year_month',how='inner')
gather_customer_order_tour_month

利用concat将上述三个表合并。同时用（order_num_x/order_num_y），计算每种自行车每个型号销售数量在本类自行车当月销售总量中的占比。

#将山地自行车、旅游自行车、公路自行车每月销量信息合并
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.apply(lambda x:x.order_num_x/x.order_num_y, axis=1)
gather_customer_order_month

修改列名

gather_customer_order_month=gather_customer_order_month.rename(columns={'order_num_x':'order_month_product','order_num_y':'sum_order_month'})
gather_customer_order_month

将数据表存入数据库

engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
gather_customer_order_month.to_sql('pt_bicycle_product_sales_order_month_4_vayne',con=engine, index=False,if_exists='append')

3.4 计算2019年11月自行车销量环比

筛选10月和11月数据，并按照产品型号和月份共同排序，为环比计算做准备

#计算11月环比，先筛选10月11月数据
gather_customer_order_month_10_11 = gather_customer_order_month[gather_customer_order_month.create_year_month.isin(['2019-10','2019-11'])]
#排序。将10月11月自行车销售信息排序
gather_customer_order_month_10_11 = gather_customer_order_month_10_11.sort_values(by = ['product_name','create_year_month'])
gather_customer_order_month_10_11.head(3)

查看总共有多少种型号自行车，并储存在列表中

product_name=list(gather_customer_order_month_10_11.product_name.drop_duplicates())
product_name

参照2.2节内容，计算环比，并储存在新的一列中

order_top_x=pd.Series([])
for i in product_name:
    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
gather_customer_order_month_10_11.head()

因为只需要11月份的环比数据，所以对数据进行筛选，剔除作为计算环比的10月数据

gather_customer_order_month_11 = gather_customer_order_month_10_11[gather_customer_order_month_10_11['create_year_month'] == '2019-11']
gather_customer_order_month_11.head(3)

3.5 计算2019年1月至11月产品累计销量

利用~取相反面以及str.contains()，筛选出2019年1月至11月的数据

gather_customer_order_month_1_11 = gather_customer_order_month[(gather_customer_order_month.create_year_month.str.contains('2019'))&(~gather_customer_order_month.create_year_month.str.contains('12'))]
gather_customer_order_month_1_11.head()

对上面的表，根据产品型号分组，并对销售量求和。得出每个型号1-11月的销售总量。最后对列进行重命名。

#计算2019年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()
gather_customer_order_month_1_11_sum = gather_customer_order_month_1_11_sum.rename(columns = {'order_month_product':'sum_order_1_11'})
gather_customer_order_month_1_11_sum.head()

3.6 2019年11月自行车产品销量、环比、累计销量

累计销量我们在gather_customer_order_month_1_11_sum中已计算好，11月自行车环比、及产品销量占比在gather_customer_order_month_11已计算好。我们通过pd.merge()将两张表连接起来。

先观察两张表

gather_customer_order_month_11.head(3)

gather_customer_order_month_1_11_sum.head(3)

按照相同字段产品型号进行合并

gather_customer_order_month_11 = pd.merge(gather_customer_order_month_11,gather_customer_order_month_1_11_sum,on='product_name',how='inner')
gather_customer_order_month_11.head()gather_customer_order_month_11 = pd.merge(gather_customer_order_month_11,gather_customer_order_month_1_11_sum,on='product_name',how='inner')
gather_customer_order_month_11.head()

将数据存入数据库

engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
gather_customer_order_month_11.to_sql('pt_bicycle_product_sales_order_month_11_vayne',con=engine, index=False,if_exists='append')

3.7 可视化展现和分析

细分市场表现图

细分市场表现表

由上可知，从2019年1-11月份，公路自行车一直占比最多，且每月占比均超过50%，是各类自行车中最畅销的产品。
较10月份，公路自行车销售量环比增速最快，达8.23%。
由以上两点可知，11月份公司销售额的增长，主要是由于本身销售量占比最大的公路自行车环比增长最高，给个公司带来了巨大的收益。因此，如果对公路自行车进行促销活动，会比其他两种自行车更容易带来销售额的提升。
每个月三种类型的自行车销售量占比差异不大，可能公司没有针对某一种类型自行车做促销。

公路自行车细分市场销量表现图

公路自行车细分市场销量表现表

由图可知，11月份除Road-350-W Yellow外，其他公路自行车环比都呈上升趋势。较10月份，Road-250 Red销量增速最快。11月份，Road-150 Red销售份额占比最高，为19.57%，同时该产品也是1-11月份总销售量最高的公路自行车产品。

山地自行车细分市场销量表现图

山地自行车细分市场销量表现表

由图可知，11月份除Mountain-200 Black外，其他型号山地自行车环比都呈上升或保持趋势。较10月份，Mountain-500 Silver环比增速最快，达19.51%。11月份，Mountain-200 Black销售量份额占比最高，达34.38%，同时该产品也是1-11月份总销售量最高的山地自行车产品。

旅游自行车细分市场销量表现图

旅游自行车细分市场销量表现表

由图可知，除Touring-2000 Blue和Touring-3000 Blue外，其他型号旅游自行车环比都呈上升趋势。较10月份，Touring-1000 Yellow环比增速最快，达28.43%。11月份Touring-1000 Blue销售量份额占比最高，达32.67%，同时该产品也是1-11月总销售量最高的旅游自行车产品。

4. 用户行为分析

4.1 从数据库读取源数据：ods_customer用户表，ods_sales_orders销售订单表

engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
df_CUSTOMER = pd.read_sql_query("select customer_key,birth_date,gender,marital_status from ods_customer where create_date < '2019-12-1'",con = engine)

#读取数据库销售订单表
engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
df_sales_orders_11 = pd.read_sql_query("select *  from ods_sales_orders where create_date>='2019-11-1' and   create_date<'2019-12-1'",con = engine)

销售订单表中仅客户编号，没有客户年龄性别等信息，因此需要将销售订单表和客户信息表合并

sales_customer_order_11=pd.merge(df_sales_orders_11,df_CUSTOMER,on='customer_key',how='left')
sales_customer_order_11.head(3)

利用split()和切片，取得客户出生年份并在表格后面新增一列储存

customer_birth_year = sales_customer_order_11['birth_date'].str.split('-').apply(lambda x: x[0] if type(x)==list else x)
customer_birth_year.name='birth_year'
sales_customer_order_11 = pd.concat([sales_customer_order_11,customer_birth_year],axis = 1)
sales_customer_order_11.head(3)

4.2 用户年龄分析

先将客户出生年份转换成Int类型，同时计算客户年龄

sales_customer_order_11['birth_year'] = sales_customer_order_11['birth_year'].fillna(method='ffill').astype('int32')
sales_customer_order_11['customer_age'] = 2019 - sales_customer_order_11['birth_year']
sales_customer_order_11.head()

利用pd.cut()函数对客户年龄进行分层。划分层次为"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=[i for i in range(30,66,5)],right=False,labels=['30-34','35-39','40-44','45-49','50-54','55-59','60-64'])
sales_customer_order_11.head()

在上表中筛选订单为自行车的信息

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

计算年龄比率并形成新的字段

df_customer_order_bycle['age_level_rate'] = 1/len(df_customer_order_bycle.customer_key)
df_customer_order_bycle.head()

为了将客户按年龄段划分为三个层次，先查看客户的最大年龄

df_customer_order_bycle.customer_age.max()

将客户年龄划分为<=29，30-39，>=40三个层次。因为客户最大年龄为62岁，所以给第三个层次设置上限值为100。

df_customer_order_bycle['age_level2']=pd.cut(df_customer_order_bycle.customer_age,bins=[0,30,40,100],right=False,labels=['<=29','30-39','>=40'])
df_customer_order_bycle.head(3)

分别求以上三个年龄段的人数

age_level2_count = df_customer_order_bycle.groupby(by = 'age_level2').sales_order_key.count().reset_index()
age_level2_count

4.3 用户性别分析

计算不同性别的总人数

gender_count = df_customer_order_bycle.groupby(by = 'gender').cplb_zw.count().reset_index()
gender_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']
df_customer_order_bycle.head()

同理，计算每个订单客户性别占该性别总人数的比例

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()

将表格存入数据库

engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
df_customer_order_bycle.to_sql('pt_user_behavior_november_vayne',con = engine,if_exists='append', index=False)

4.4 可视化展现和分析

2019年11月全国客户年龄段分布图

由图可知，35-39岁年龄段客户人数占比最大，达34%。之后随年龄段增长，占比逐渐下降。
可以重点针对35-39岁年龄段做问卷调查，了解他们对自行车的具体需求，并有针对性的进行促销活动。

不同年龄段购买自行车种类比例图

由图可知，大于30岁的客户购买公路自行车比例最高，达50%以上。30-39和40岁及以上两个年龄段的客户，购买自行车种类比例结构基本相同。
结合前面的图，可以重点给35-39岁年龄段的客户推送公路自行车信息，再根据客户对信息的查看反馈判断客户是否对该类自行车感兴趣。

性别比例图

由图可知，购买自行车的客户中，男性客户数量比女性客户数量要多10%。存在一些性别差异。

男女消费结构图

由图可知，不论是男女客户，都是购买公路自行车最多，购买旅游自行车最少。

5. 热品销售分析

5.1 11月产品销量TOP10产品，销售数量及环比

筛选11月数据

gather_customer_order_11 = gather_customer_order.loc[gather_customer_order['create_year_month'] == '2019-11']
gather_customer_order_11.head()

筛选销售量top10的产品

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()

查看销售量top10的产品型号

list(customer_order_11_top10['product_name'])

查看在第三节中已经计算好的11月自行车销售量环比

gather_customer_order_month_10_11.head()

对上表的字段进行筛选。这里我们只需要四个字段：create_year_month月份，product_name产品名，order_month_product本月销量，cpzl_zw产品类别，order_num_diff本月产品销量环比。

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.head()

对上表进行筛选，保留销量前十的自行车型号的信息，并且新增一个字段注明本月TOP10销量。

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销量'
customer_order_month_10_11.head()

5.2 11月增速TOP10产品，销售数量及环比

根据第三节的表，筛选11月份环比增速TOP10的自行车信息

customer_order_month_11 = gather_customer_order_month_10_11.loc[gather_customer_order_month_10_11['create_year_month'] == '2019-11'].sort_values(by = 'order_num_diff',ascending = False).head(10)
customer_order_month_11.head()

根据之前的表，筛选出11月份环比增速TOP10的自行车

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.head()

对上表的字段进行筛选。这里我们只需要四个字段：create_year_month月份，product_name产品名，order_month_product本月销量，cpzl_zw产品类别，order_num_diff本月产品销量环比。并且新增一列注明本月TOP10增速。

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增速'
customer_order_month_11_top10_seep.head()

将上述TOP10销量表和TOP10增速表合并

hot_products_11 = pd.concat([customer_order_month_10_11,customer_order_month_11_top10_seep],axis = 0)
hot_products_11.tail()

将表格存入数据库

engine = create_engine("mysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
hot_products_11.to_sql('pt_hot_products_november_vayne',con = engine,if_exists='append', index=False)

5.3 可视化展现和分析

11月份TOP10销量图

11月份TOP10销量表

由图可知，11月份Mountain-200 Silver销量最多，为395辆。较10月份增长10.64%。
对Mountain-200 Silver，Road-150 Red和Road-550-W Yellow，这几个产品销量大且增速快，需要做好库存管理避免断货。

11月份TOP10增速图

11月份TOP10增速表

由图可知，11月份Touring-1000 Yellow销量增速最快，较10月份增长28.43%。
对于同时在销量榜和增速榜里的产品，如Road-150 Red， Road-550-W Yellow, Touring-1000 Blue，需要保证产品库存，避免因销量上涨导致库存不足无法发货。

adventure项目第一部分

adventure项目第一部分

（一）项目介绍

（二）分析过程和思路

1. 整体销售表现

1.1从数据库读取源数据：dw_customer_order

1.2自行车整体销售量表现

1.3自行车整体销售金额表现

1.4可视化展现和分析

2. 地域销售表现

2.1源数据dw_customer_order，数据清洗筛选10月11月数据

2.2 2019年11月自行车区域销售量表现

2.3 2019年11月自行车销售量TOP10城市环比

2.4 可视化展现和分析

3. 产品销售表现

3.1 数据源 dw_customer_order

3.2 细分市场销售表现

3.3 公路/山地/旅游自行车细分市场表现

3.3.1 公路自行车细分市场表现

3.3.2 山地自行车细分市场表现

3.3.3 旅游自行车细分市场表现

3.4 计算2019年11月自行车销量环比

3.5 计算2019年1月至11月产品累计销量

3.6 2019年11月自行车产品销量、环比、累计销量

3.7 可视化展现和分析

4. 用户行为分析

4.1 从数据库读取源数据：ods_customer用户表，ods_sales_orders销售订单表

4.2 用户年龄分析

4.3 用户性别分析

4.4 可视化展现和分析

5. 热品销售分析

5.1 11月产品销量TOP10产品，销售数量及环比

5.2 11月增速TOP10产品，销售数量及环比

5.3 可视化展现和分析