一、 项目概述
1. 成果预览
2. 背景介绍
2.1 公司背景
Adventure Works Cycle是在国内范围内生产和销售金属以及复合材料的自行车。前期主要以分销商模式进行线下销售，2019年开始通过公司自有网站获取线上销售进一步扩大市场。为精细化运营提供数据支持，能精准的定位目标客户群体。
2.2 分析目的
1、如何制定销售策略，调整产品结构，才能保持高速增长，获取更多的收益，占领更多市场份额，是公司最关心的问题。
2、报告通过对整个公司的自行车销量持续监测和分析，掌握公司自行车销售状况、走势的变化，为客户制订、调整和检查销售策略，完善产品结构提供依据。
二、 数据搭建
1. 指标搭建
1.1 查看数据
adventure Works Cycles公司的mysql数据库中有三张表：
有如下字段：
1.2 确定需求
1）dw_order_by_day 每日环比表：
2）dw_customer_order 时间地区产品聚合表：
3）dw_amount_diff 当日维度表：
flag分类中字段注释：（avg：平均订单价同比，amount：金额同比，order：订单量同比）
2. python处理数据
2.1 生成dw_order_by_day表
导入相关模块，连接数据库
读取ods_sales_orders(订单明细表)后，根据create_date聚合,求总销量/订单量/客单价
利用空列表及循环生成对应随机值，与销量订单聚合表合并形成sum_amount_order_goal(销量订单聚合目标表）
读取dim_date_df日期维度表
通过主键将两张表链接，生成dw_order_by_day表
将dw_order_by_day表存入数据库中
2.2 生成dw_customer_order表
连接数据库
导入ods_sales_orders表
读取每日新增用户表
读取日期维度表
进行数据聚合
再和日期为度进行合并
将dw_customer_order表储存导数据库中
2.3 生成dw_amount_diff表
连接数据库并读取dw_order_by_day表
计算各个阶段的金额总和
求取各阶段的总订单数，类比“计算各个阶段的金额总和”
求同期对比数据
3. 搭建Dashboard
3.1 使用powerbi链接mysql数据库
3.2 可视化看板布置
（1）利用KPI工具生成相应时段的销售总额，比显示出与目标的差值百分比
（2）卡片图制作订单量，每单交易平均金额，同比去年销售额等
（3）利用表个工具和环形图工具制作销量最好的自行车列表和所有品类的销售占比环形图
（4）地图用来表示各地区的销售状况
（5）设置切片器和标签联动
三、 数据分析
接下来主要对Adventure的自行车业务进行分析，分析思路如下：
（1）、自行车整体销售表现**
（2）、2019年11月自行车地域销售表现**
（3）、2019年11月自行车产品销售表现**
（4）、用户行为分析**
（5）、2019年11月热品销售分析**
首先导入相关模块
1.自行车整体销售表现
连接数据库读取数据
产看数据特征
因为我们要考察的是不同月份的销售表现，所以我们可以考虑增加一个字段用来表示“年-月”
筛选类别为自行车的数据
为了得到每月订单数量和销售金额，用groupby创建一个新的对象，需要将order_num、sum_amount求和
利用diff()函数求环比（以销售领的环比增长为例）
将环比转化为dataframe之后，可以得到如下的overall_sales_performance表
销售额的环比求法与之类似，不在这里过多说明，最终我们可以求得一个完整的overall_sales_performance表并将其存入数据库中，存储语句为
利用PowerBI对该表进行可视化的结果如下图所示：
分析：9月和12月的销售数量和销售金额有明显的下降，特别是12月份，环比销售量下降11.75%，环比销售额下降12.68%。需要进一步结合业务状况和客观条件来分析这种原因是内在的还是外在的。与之相对应的是10,11月份出现了连续的增长态势，而且11月份的客单价是比较高，排除因为打折促销导致的薄利多销的情况。结合实际运营动作，可以具体分析是因为什么原因造成的销售量显著增长。
2.区域销售表现
2.1 10月，11月的地区销售表现
gather_customer_order在分析自行车整体表现时已从数据库导入表（dw_customer_order），并筛选仅自行车数据，我们现在需要针对这张表取10月和11月的销售数据。
按照区域进行分组
然后分别求出order_num和sum_amount的环比情况
并将数据储存在pt_bicy_november_territory_2中：
2.2 2019年11月自行车销售量TOP10城市环比
方法同2.1，保存在数据库的pt_bicy_november_october_city_3中，将10月11月不同地区以及Top10城市的销售表现可视化：
总结：华东地区销售量最高，华中地区和华南地区，东北地区的环比增长明显。城市方面如北京，广州等城市的环比增长均在50%左右，其中郑州的增长率最高，达到了100%，可以进一步深入发掘其中的原因。
3.细分市场表现
基于gather_customer_order表对每月的自行车销量进行会总，并合并到gather_customer_order中去，得到order_num_proportion表
计算当天销售量和当月销售量的占比字段，并对order_num_y重命名为sum_month_order
储存在在pt_bicycle_product_sales_month_4中
查看自行车的细分品类
分别统计公路/山地/旅游自行车的细分市场表现，这里以公路自行车为例：
把对象设定为公路自行车
计算每个月某个型号销售量，和该型号所属大类别的与销售量，汇总成 gather_customer_order_road_month表
按照此方法得到三类自行车的表，最后汇总为一个gather_customer_order_month表，并增加字段，反映各类自行车，销售量占每月自行车总销售量比率
将该表进行可视化：
总结：这里把全国范围内按月度推移的各类自行车细分市场销售变现展示了出来，但是针对于各个大区，不同品类的需求会不会也会不一样（南北方会不会有明显倾向上的差异等），这里先挖一个坑，后续可以做一个专题来进行探讨。
4.用户行为分析
这里我们需要使用订单明细表：ods_sales_orders，ods_customer用户表
通过将两张表进行合并整理，添加生日，年龄，以及年龄分层信息，年龄占比，将最终的df_customer_order_bycle的DataFrame存入Mysql的pt_user_behavior_november当中，可视化后如下所示：
总结：消费者女性占比为45%，男性为55%，略多于女性，结合右下的图可以知道，男性对旅游自行车和公路自行车的需求更高，女性对山地自行车的要求更高。从年龄角度来讲，主要消费人群为35-44岁的人群，这些人群站到总消费人数的54%左右。制作营销活动时，应该考虑巩固这个年龄层的客群，同时可以进一步分析为什么30-34岁的年龄层的占比这么低。另外30-39岁的人群和40岁以上人群的消费动机是相似的。
5.热销品分析
我们在分析“2019年11月自行车产品销售表现”时已计算出11月所有产品的销量及环比，这里不在重复计算，直接使用gather_customer_order_month_10_11、gather_customer_order_month_11
5.1、11月产品销量TOP10产品，销售数量及环比
得到销量前十名的商品后，我们再计算一下环比情况，首先查询一下11月环比情况
利用如下语句将商品限定在销售前十名的商品并增加“本月TOP10销量”字段
最后利用sort_values函数进行排名，得到TOP10的榜单并可视化输出：
附上完整的PowerBI链接：https://app.powerbi.com/groups/me/reports/f1c4ae70-9e0b-4cad-8e98-6dbe9c564035/ReportSection?bookmarkGuid=Bookmark3bbfc9196e640dee68a2
四、 尝试实现hive数据仓库满足大数据需求
1.使用sqoop导数到hive数据库 生成 ods 层，参考代码如下：
2.使用hive库进行数据汇总加工生成dw层
以ods.dw_order_day()举例进行数据加工：
3.sqoop导出分析结果到mysql
