一、 項(xiàng)目概述
1. 成果預(yù)覽
2. 背景介紹
2.1 公司背景
? ? ? ? Adventure Works Cycle是在國內(nèi)范圍內(nèi)生產(chǎn)和銷售金屬以及復(fù)合材料的自行車。前期主要以分銷商模式進(jìn)行線下銷售，2019年開始通過公司自有網(wǎng)站獲取線上銷售進(jìn)一步擴(kuò)大市場。為精細(xì)化運(yùn)營提供數(shù)據(jù)支持，能精準(zhǔn)的定位目標(biāo)客戶群體。
2.2 分析目的
1、如何制定銷售策略，調(diào)整產(chǎn)品結(jié)構(gòu)，才能保持高速增長，獲取更多的收益，占領(lǐng)更多市場份額，是公司最關(guān)心的問題。
2、報告通過對整個公司的自行車銷量持續(xù)監(jiān)測和分析，掌握公司自行車銷售狀況、走勢的變化，為客戶制訂、調(diào)整和檢查銷售策略，完善產(chǎn)品結(jié)構(gòu)提供依據(jù)。
二、 數(shù)據(jù)搭建
1. 指標(biāo)搭建
1.1 查看數(shù)據(jù)
adventure Works Cycles公司的mysql數(shù)據(jù)庫中有三張表：
有如下字段：
1.2 確定需求
1）dw_order_by_day 每日環(huán)比表：
2）dw_customer_order 時間地區(qū)產(chǎn)品聚合表：
3）dw_amount_diff 當(dāng)日維度表：
flag分類中字段注釋：（avg：平均訂單價同比，amount：金額同比，order：訂單量同比）
2. python處理數(shù)據(jù)
2.1 生成dw_order_by_day表
導(dǎo)入相關(guān)模塊，連接數(shù)據(jù)庫
讀取ods_sales_orders(訂單明細(xì)表)后，根據(jù)create_date聚合,求總銷量/訂單量/客單價
利用空列表及循環(huán)生成對應(yīng)隨機(jī)值，與銷量訂單聚合表合并形成sum_amount_order_goal(銷量訂單聚合目標(biāo)表）
讀取dim_date_df日期維度表
通過主鍵將兩張表鏈接，生成dw_order_by_day表
將dw_order_by_day表存入數(shù)據(jù)庫中
2.2 生成dw_customer_order表
連接數(shù)據(jù)庫
導(dǎo)入ods_sales_orders表
讀取每日新增用戶表
讀取日期維度表
進(jìn)行數(shù)據(jù)聚合
再和日期為度進(jìn)行合并
將dw_customer_order表儲存導(dǎo)數(shù)據(jù)庫中
2.3 生成dw_amount_diff表
連接數(shù)據(jù)庫并讀取dw_order_by_day表
計算各個階段的金額總和
求取各階段的總訂單數(shù)，類比“計算各個階段的金額總和”
求同期對比數(shù)據(jù)
3. 搭建Dashboard
3.1 使用powerbi鏈接mysql數(shù)據(jù)庫
3.2 可視化看板布置
（1）利用KPI工具生成相應(yīng)時段的銷售總額，比顯示出與目標(biāo)的差值百分比
（2）卡片圖制作訂單量，每單交易平均金額，同比去年銷售額等
（3）利用表個工具和環(huán)形圖工具制作銷量最好的自行車列表和所有品類的銷售占比環(huán)形圖
（4）地圖用來表示各地區(qū)的銷售狀況
（5）設(shè)置切片器和標(biāo)簽聯(lián)動
三、?數(shù)據(jù)分析
接下來主要對Adventure的自行車業(yè)務(wù)進(jìn)行分析，分析思路如下：
（1）、自行車整體銷售表現(xiàn)**
（2）、2019年11月自行車地域銷售表現(xiàn)**
（3）、2019年11月自行車產(chǎn)品銷售表現(xiàn)**
（4）、用戶行為分析**
（5）、2019年11月熱品銷售分析**
首先導(dǎo)入相關(guān)模塊
1.自行車整體銷售表現(xiàn)
連接數(shù)據(jù)庫讀取數(shù)據(jù)
產(chǎn)看數(shù)據(jù)特征
因?yàn)槲覀円疾斓氖遣煌路莸匿N售表現(xiàn)，所以我們可以考慮增加一個字段用來表示“年-月”
篩選類別為自行車的數(shù)據(jù)
為了得到每月訂單數(shù)量和銷售金額，用groupby創(chuàng)建一個新的對象，需要將order_num、sum_amount求和
利用diff()函數(shù)求環(huán)比（以銷售領(lǐng)的環(huán)比增長為例）
將環(huán)比轉(zhuǎn)化為dataframe之后，可以得到如下的overall_sales_performance表
銷售額的環(huán)比求法與之類似，不在這里過多說明，最終我們可以求得一個完整的overall_sales_performance表并將其存入數(shù)據(jù)庫中，存儲語句為
利用PowerBI對該表進(jìn)行可視化的結(jié)果如下圖所示：
分析：9月和12月的銷售數(shù)量和銷售金額有明顯的下降，特別是12月份，環(huán)比銷售量下降11.75%，環(huán)比銷售額下降12.68%。需要進(jìn)一步結(jié)合業(yè)務(wù)狀況和客觀條件來分析這種原因是內(nèi)在的還是外在的。與之相對應(yīng)的是10,11月份出現(xiàn)了連續(xù)的增長態(tài)勢，而且11月份的客單價是比較高，排除因?yàn)榇蛘鄞黉N導(dǎo)致的薄利多銷的情況。結(jié)合實(shí)際運(yùn)營動作，可以具體分析是因?yàn)槭裁丛蛟斐傻匿N售量顯著增長。
2.區(qū)域銷售表現(xiàn)
2.1 10月，11月的地區(qū)銷售表現(xiàn)
gather_customer_order在分析自行車整體表現(xiàn)時已從數(shù)據(jù)庫導(dǎo)入表（dw_customer_order），并篩選僅自行車數(shù)據(jù)，我們現(xiàn)在需要針對這張表取10月和11月的銷售數(shù)據(jù)。
按照區(qū)域進(jìn)行分組
然后分別求出order_num和sum_amount的環(huán)比情況
并將數(shù)據(jù)儲存在pt_bicy_november_territory_2中：
2.2?2019年11月自行車銷售量TOP10城市環(huán)比
方法同2.1，保存在數(shù)據(jù)庫的pt_bicy_november_october_city_3中，將10月11月不同地區(qū)以及Top10城市的銷售表現(xiàn)可視化：
總結(jié)：華東地區(qū)銷售量最高，華中地區(qū)和華南地區(qū)，東北地區(qū)的環(huán)比增長明顯。城市方面如北京，廣州等城市的環(huán)比增長均在50%左右，其中鄭州的增長率最高，達(dá)到了100%，可以進(jìn)一步深入發(fā)掘其中的原因。
3.細(xì)分市場表現(xiàn)
基于gather_customer_order表對每月的自行車銷量進(jìn)行會總，并合并到gather_customer_order中去，得到order_num_proportion表
計算當(dāng)天銷售量和當(dāng)月銷售量的占比字段，并對order_num_y重命名為sum_month_order
儲存在在pt_bicycle_product_sales_month_4中
查看自行車的細(xì)分品類
分別統(tǒng)計公路/山地/旅游自行車的細(xì)分市場表現(xiàn)，這里以公路自行車為例：
把對象設(shè)定為公路自行車
計算每個月某個型號銷售量，和該型號所屬大類別的與銷售量，匯總成?gather_customer_order_road_month表
按照此方法得到三類自行車的表，最后匯總為一個gather_customer_order_month表，并增加字段，反映各類自行車，銷售量占每月自行車總銷售量比率
將該表進(jìn)行可視化：
總結(jié)：這里把全國范圍內(nèi)按月度推移的各類自行車細(xì)分市場銷售變現(xiàn)展示了出來，但是針對于各個大區(qū)，不同品類的需求會不會也會不一樣（南北方會不會有明顯傾向上的差異等），這里先挖一個坑，后續(xù)可以做一個專題來進(jìn)行探討。
4.用戶行為分析
這里我們需要使用訂單明細(xì)表：ods_sales_orders，ods_customer用戶表
通過將兩張表進(jìn)行合并整理，添加生日，年齡，以及年齡分層信息，年齡占比，將最終的df_customer_order_bycle的DataFrame存入Mysql的pt_user_behavior_november當(dāng)中，可視化后如下所示：
總結(jié)：消費(fèi)者女性占比為45%，男性為55%，略多于女性，結(jié)合右下的圖可以知道，男性對旅游自行車和公路自行車的需求更高，女性對山地自行車的要求更高。從年齡角度來講，主要消費(fèi)人群為35-44歲的人群，這些人群站到總消費(fèi)人數(shù)的54%左右。制作營銷活動時，應(yīng)該考慮鞏固這個年齡層的客群，同時可以進(jìn)一步分析為什么30-34歲的年齡層的占比這么低。另外30-39歲的人群和40歲以上人群的消費(fèi)動機(jī)是相似的。
5.熱銷品分析
我們在分析“2019年11月自行車產(chǎn)品銷售表現(xiàn)”時已計算出11月所有產(chǎn)品的銷量及環(huán)比，這里不在重復(fù)計算，直接使用gather_customer_order_month_10_11、gather_customer_order_month_11
5.1、11月產(chǎn)品銷量TOP10產(chǎn)品，銷售數(shù)量及環(huán)比
得到銷量前十名的商品后，我們再計算一下環(huán)比情況，首先查詢一下11月環(huán)比情況
利用如下語句將商品限定在銷售前十名的商品并增加“本月TOP10銷量”字段
最后利用sort_values函數(shù)進(jìn)行排名，得到TOP10的榜單并可視化輸出：
附上完整的PowerBI鏈接：https://app.powerbi.com/groups/me/reports/f1c4ae70-9e0b-4cad-8e98-6dbe9c564035/ReportSection?bookmarkGuid=Bookmark3bbfc9196e640dee68a2
四、??嘗試實(shí)現(xiàn)hive數(shù)據(jù)倉庫滿足大數(shù)據(jù)需求
1.使用sqoop導(dǎo)數(shù)到hive數(shù)據(jù)庫 生成 ods 層，參考代碼如下：
2.使用hive庫進(jìn)行數(shù)據(jù)匯總加工生成dw層
以ods.dw_order_day()舉例進(jìn)行數(shù)據(jù)加工：
3.sqoop導(dǎo)出分析結(jié)果到mysql
