BI-電商銷售數(shù)據(jù)分析

時(shí)間：2023-03-16 00:34:02 | 來源：電子商務(wù)

時(shí)間：2023-03-16 00:34:02 來源：電子商務(wù)

目錄

0 項(xiàng)目背景

1 數(shù)據(jù)需求分析

2 數(shù)據(jù)加工

3 ETL效率提升

4 自動(dòng)化部署

5 Dashboard設(shè)計(jì)

---

0 項(xiàng)目背景

Adventure Work Cycle是一家大型跨國制造公司，該公司生產(chǎn)和銷售金屬、復(fù)合材料自行車。在全國各個(gè)市場，銷售方式主要有兩種，分銷商模式和電商模式。前期主要是分銷商模式，2018年公司實(shí)現(xiàn)財(cái)政收入目標(biāo)后，2019年就開始通過公司自有網(wǎng)站獲取線上商戶進(jìn)一步擴(kuò)大市場。隨著銷售數(shù)據(jù)量日益增長，需要監(jiān)控業(yè)務(wù)發(fā)展過程中的異常問題；為了能夠一路了然業(yè)務(wù)情況，應(yīng)該建立BI系統(tǒng)，形成固化日常看板，供運(yùn)營和管理者決策。

1 數(shù)據(jù)需求分析

首先，一定要對數(shù)據(jù)需求進(jìn)行分析；數(shù)據(jù)需要方的意圖？需要哪些數(shù)據(jù)內(nèi)容？能夠拆解成哪些指標(biāo)？要加工哪些數(shù)據(jù)？把這些問題理清楚，在搭建BI系統(tǒng)的時(shí)候才能有清晰的邏輯，“謀定而后動(dòng)”說的就是這個(gè)道理。

公司的人事架構(gòu)呈現(xiàn)金字塔結(jié)構(gòu)，那么便可以借助這個(gè)金字塔對數(shù)據(jù)需求進(jìn)行分析。不同的角色關(guān)注的指標(biāo)不同；公司高層關(guān)注的是結(jié)果性指標(biāo)，例如交易金額、訂單量、訂單均價(jià)等；公司中高層關(guān)注的是過程性指標(biāo)，例如交易金額趨勢、訂單量趨勢；公司基層關(guān)注的是運(yùn)營性指標(biāo)，例如各產(chǎn)品訂單量排名、區(qū)域細(xì)分訂單表。

通過數(shù)據(jù)需求分析可知，Dashboard需要展示3部分內(nèi)容：周期性看板、數(shù)據(jù)監(jiān)控看板、區(qū)域細(xì)分看板。對看板展示內(nèi)容進(jìn)行拆分，包括監(jiān)控指標(biāo)、篩選器、標(biāo)題、色彩等，具體如下：

2 數(shù)據(jù)加工

明確數(shù)據(jù)需求后，便可對數(shù)據(jù)進(jìn)行加工，提取需要的監(jiān)控指標(biāo)。在數(shù)據(jù)加工前，務(wù)必要熟悉數(shù)據(jù)，否則無從下手；熟悉數(shù)據(jù)最快的方式就是查看源數(shù)據(jù)層（ods層）的數(shù)據(jù)字典，理清數(shù)據(jù)表之間的關(guān)系。該項(xiàng)目的數(shù)據(jù)字典如下：

ods_sales_orders是訂單明細(xì)表，ods_customer是每日新增用戶表，dim_date_df是日期維度表，三張表存在連接關(guān)系，通過表連接、聚合等操作可以得到想要的監(jiān)控指標(biāo)。ods_sales_orders和ods_customer通過customer_key連接，得到的表再通過create_date和dim_date_df連接，可得到dw_customer_order（時(shí)間_地區(qū)_產(chǎn)品聚合表）；ods_sales_orders和dim_date_df通過create_date連接，得到dw_order_by_day（每日環(huán)比表）；dw_order_by_day進(jìn)一步加工便可得到dw_amount_diff（當(dāng)日維度表，即同比表）。

有了思路后，就可以進(jìn)行數(shù)據(jù)加工了，將源數(shù)據(jù)層（ods層）的數(shù)據(jù)加工至數(shù)據(jù)倉庫（dw層），以便于后續(xù)開展數(shù)據(jù)分析工作。以加工dw_customer_order表的代碼為例：

# 導(dǎo)入所需要的庫import numpy as npimport pandas as pdimport pymysqlpymysql.install_as_MySQLdb()from sqlalchemy import create_engineimport datetime# 連接數(shù)據(jù)庫（源數(shù)據(jù)層和數(shù)據(jù)倉庫）AdventureOds = create_engine("mysql://UserName_1:Password_1@IP_Address_1/ods?charset=gbk")AdventureDw = create_engine("mysql://UserName_2:Password_2@IP_Address_2/dw?charset=gbk")# 讀取ods_sales_orders表（訂單明細(xì)表）SQLquery1 = """select sales_order_key, create_date, customer_key, english_product_name, cpzl_zw, cplb_zw, unit_price from ods_sales_orders where create_date = ( select create_date from dim_date_df order by create_date desc limit 1) """OdsSaleOrder = pd.read_sql_query(SQLquery1, con=AdventureOds)# 讀取ods_customer表（每日新增用戶表）SQLquery2 = """select customer_key, chinese_territory, chinese_province, chinese_city from ods_customer"""OdsCustomer = pd.read_sql_query(SQLquery2, con=AdventureOds)# 讀取dim_date_df表（日期維度表）SQLquery3 = """select create_date, is_current_year, is_last_year, is_yesterday, is_today, is_current_month, is_current_quarter from dim_date_df"""OdsDimDate = pd.read_sql_query(SQLquery3, con=AdventureOds)# 數(shù)據(jù)加工SaleOrderCustomer = pd.merge(OdsSaleOrder, OdsCustomer, 'left', 'customer_key')SaleOrderCustomerP = SaleOrderCustomer.pivot_table(index=['create_date', 'english_product_name', 'cpzl_zw', 'cplb_zw', 'chinese_territory', 'chinese_province', 'chinese_city'], values=['sales_order_key', 'customer_key', 'unit_price'], aggfunc={'sales_order_key': pd.Series.nunique, 'customer_key': pd.Series.nunique, 'unit_price': sum} )SaleOrderCustomerP = SaleOrderCustomerP.reset_index()SaleOrderCustomerP.rename(columns={'customer_key':'SumCustomer', 'sales_order_key':'SumOrder', 'unit_price':'SumAmount'}, inplace=True)dw_customer_order = pd.merge(SaleOrderCustomerP, OdsDimDate, 'left', 'create_date')# 加工好的dw_customer_order表(時(shí)間_地區(qū)_產(chǎn)品聚合表)導(dǎo)出至數(shù)據(jù)倉庫try: pd.read_sql_query('Truncate table dw_customer_order', con=AdventureDw)except Exception as e: print('舊表刪除Error: %s' %e)dw_customer_order.to_sql('dw_customer_order', con=AdventureDw, if_exists='replace', index=False)

3 ETL效率提升

ETL過程處理的數(shù)據(jù)量很大時(shí)，需要等待很長一段時(shí)間，通過一些方法可以提高ETL的效率，例如在mysql追加索引優(yōu)化查詢速度、在python存儲(chǔ)至mysql時(shí)增添多進(jìn)程。

3.1 追加索引

mysql索引：搜索過程不再遍歷整張表，而是根據(jù)索引搜尋，索引一般是表中元素的前綴。例如，where條件篩選'2019-1'，那么索引會(huì)直接排除掉['2020','2018','2021']等這些年份結(jié)果，直接匹配前綴相符合的內(nèi)容，索引的長度就是前綴的長度。下面舉例說明。

給ods_sales_orders表創(chuàng)建日期索引前，查詢耗時(shí) 3.806 ms。

# 創(chuàng)建日期索引前，查詢?nèi)掌跒椤?019-03-01’訂單明細(xì)select * from ods_sales_orders where create_date='2019-03-01';給ods_sales_orders表創(chuàng)建日期索引后，查詢耗時(shí) 0.168 ms，查詢速度提高 21.65 倍。可見，給表追加索引可以大大提高查詢速度，提升ETL效率。

# 創(chuàng)建日期索引后，查詢?nèi)掌跒椤?019-03-01’訂單明細(xì)create index date_index on ods_sales_orders(create_date(7));select * from ods_sales_orders where create_date='2019-03-01';索引的工作原理：表中存在索引時(shí)，查詢語句不再遍歷列的所有元素，而是先遍歷索引，再遍歷前綴與索引相同的元素，遍歷復(fù)雜度降低，從而提高查詢速度。從explain結(jié)果可以看出，創(chuàng)建日期索引date_index后，查詢首先遍歷索引。

# 使用explain展示查詢過程explain select * from ods_sales_orders where create_date='2019-03-01';3.2 增添多進(jìn)程

進(jìn)程：程序在計(jì)算機(jī)上的一次執(zhí)行活動(dòng)， 操作系統(tǒng)進(jìn)行資源分配的單位。

多進(jìn)程：多個(gè)程序同時(shí)在計(jì)算機(jī)上執(zhí)行活動(dòng)，以此實(shí)現(xiàn)充分調(diào)用資源目的。

除了使用索引提高mysql的查詢速度，還可以利用多進(jìn)程庫提高python的執(zhí)行效率，多進(jìn)程并發(fā)可以大大減少讀取數(shù)據(jù)庫、寫入數(shù)據(jù)庫、數(shù)據(jù)加工的時(shí)間。示例：

import multiprocessing # 導(dǎo)入多進(jìn)程庫def runtask(): # dw_order_by_day.py # dw_amount_diff.py # dw_customer_order.pydef callBackTask(arg): # 回調(diào)函數(shù)必須要有一個(gè)形參，否則將報(bào)錯(cuò) print("執(zhí)行回調(diào)函數(shù)",arg)if __name__ == "__main__": pool = multiprocessing.Pool(5) # 設(shè)置進(jìn)程池最大同時(shí)執(zhí)行進(jìn)程數(shù) for index in range(20): pool.apply_async(func=runtask,callback=callBackTask) # 并行的,有回調(diào)方法 # pool.apply(func=runtask,) # 串行的，無回調(diào)函數(shù) pool.close() # 關(guān)閉進(jìn)程池 pool.join() # 調(diào)用join之前，先調(diào)用close函數(shù)，否則會(huì)出錯(cuò)。執(zhí)行完close后不會(huì)有新的進(jìn)程加入到pool,join函數(shù)等待所有子進(jìn)程結(jié)束multiprocessing庫中的Pool就是進(jìn)程池，進(jìn)程池能夠管理一定的進(jìn)程，當(dāng)有空閑進(jìn)程時(shí)，則利用空閑進(jìn)程完成任務(wù)，直到所有任務(wù)完成為止。

4 自動(dòng)化部署

數(shù)據(jù)庫每天都會(huì)更新，每天的數(shù)據(jù)報(bào)表也是需要更新的，那么可以借助自動(dòng)化代碼進(jìn)行每天更新。示例：

import schedule # 定時(shí)執(zhí)行模塊import timeimport datetimeimport os # 命令窗口交互模塊import requests?def job1(): """ dw_order_by_day 每日環(huán)比表 """ print('Job1:每天8:00執(zhí)行一次') print('Job1-startTime:%s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))) os.system( "/home/anaconda3/bin/python3 /home/******/adventure/dw_order_by_day.py >> /home/******/adventure/logs/dw_order_by_day_schedule.log 2>&1 &") time.sleep(20) print('Job1-endTime:%s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))) print('------------------------------------------------------------------------') if __name__ == '__main__': schedule.every().day.at('08:00').do(job1) while True: schedule.run_pending() time.sleep(10) print("wait", datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))將這個(gè)文件掛在linux系統(tǒng)的后臺(tái)，每天早上8點(diǎn)，自動(dòng)執(zhí)行，定時(shí)更新；同時(shí)將代碼運(yùn)行情況寫入dw_order_by_day_schedule.log文件。

5 Dashboard設(shè)計(jì)

每天早上8點(diǎn)，數(shù)據(jù)自動(dòng)更新后，Dashboard也會(huì)隨之更新，方便監(jiān)控日常指標(biāo)。