1.項(xiàng)目目的：基于現(xiàn)有數(shù)據(jù)扬跋，以業(yè)務(wù)帶看情況為主題甩卓，搭建實(shí)時(shí)更新的BI看板倦卖。
2.基礎(chǔ)數(shù)據(jù)源介紹
現(xiàn)有數(shù)據(jù)源為三張表：帶看表洒擦，daikan_table；認(rèn)購(gòu)表怕膛，chengjiao_table熟嫩；人員架構(gòu)表；其中帶看表褐捻，認(rèn)購(gòu)表可以理解為多條記錄掸茅，人員架構(gòu)表每一行都記錄著經(jīng)紀(jì)人信息。
以上三張表的基礎(chǔ)數(shù)據(jù)源為后臺(tái)管理系統(tǒng)下載的.csv文件柠逞。
每張表包含的關(guān)鍵字段羅列如下：

image.png

3.項(xiàng)目步驟
（1）想要?jiǎng)?chuàng)建實(shí)時(shí)刷新的powerBI昧狮，因此考慮構(gòu)建寬表，使用python腳本板壮，將daikan_table+chengjiao_table進(jìn)行數(shù)據(jù)清洗逗鸣，將清洗后的數(shù)據(jù)存入mysql數(shù)據(jù)庫(kù)。定時(shí)執(zhí)行腳本即可實(shí)現(xiàn)最新數(shù)據(jù)的入庫(kù)绰精。
總體思路如下：

image.png

滲透率=帶看人數(shù)/在職人數(shù)拳锚；
人均帶看=帶看量/在職人數(shù)；
人均業(yè)績(jī)=認(rèn)購(gòu)總業(yè)績(jī)/在職人數(shù)寻行；
5.數(shù)據(jù)源架構(gòu)
（1）將帶看數(shù)據(jù)霍掺、認(rèn)購(gòu)數(shù)據(jù)整合成一張寬表timeline，存入數(shù)據(jù)庫(kù)寡痰。
從數(shù)據(jù)庫(kù)讀取這個(gè)寬表timeline抗楔。根據(jù)業(yè)務(wù)場(chǎng)景，給用戶查看大概半年的數(shù)據(jù)拦坠，因此在SQL語(yǔ)句中將日期設(shè)定為從今天起向前回滾8個(gè)月连躏。

image.png

日期表 = ADDCOLUMNS (
    CALENDAR(DATE(2019,1,1),LASTDATE('YG_ALL Timeline'[日期])),
    "年",YEAR([Date]),
    "季度","Q" & ROUNDUP(MONTH([Date])/3,0),
    "月",MONTH([Date]),
    "日",day([Date]),
    "周",WEEKNUM([Date]),
    "年季度",YEAR([Date]) & "Q" & ROUNDUP(MONTH([Date])/3,0),
    "年月",YEAR([Date])*100+MONTH([Date]))

（3）人員架構(gòu)表的處理與搭建，ad_jg_shishi
人員架構(gòu)表的難點(diǎn)在于骄噪，每日一份人員架構(gòu)表（例如昨天1000個(gè)經(jīng)紀(jì)人尚困，今天可能有1500個(gè)經(jīng)紀(jì)人），因此這份人員架構(gòu)表是實(shí)時(shí)變動(dòng)的链蕊。目前采取處理方式是每天讀取一份.csv的人員架構(gòu)表事甜，進(jìn)行相關(guān)處理，導(dǎo)入powerBI滔韵。
引入相關(guān)包逻谦；

import pandas as pd
import datetime
import glob
import os
from sqlalchemy import create_engine
import warnings
import pymysql
pymysql.install_as_MySQLdb()
warnings.filterwarnings("ignore")

每天動(dòng)態(tài)查找文件夾，獲取最新架構(gòu)表的地址陪蜻；

# 文件地址獲取
path=os.path.join(r'\\10.8.29.213','日常文件存檔', '08 戰(zhàn)報(bào)每日存檔', today_1)
Ad_path=''.join(glob.glob(os.path.join(path,'在職人員明細(xì)*')))
Ad_path = Ad_path.strip()

if not os.path.exists(Ad_path):
    today_1=str(datetime.datetime.now().year)+'.'+str(datetime.datetime.now().month)+'.'+str(datetime.datetime.now().day-1)
    path = os.path.join(r'\\10.8.29.213', '日常文件存檔', '08 戰(zhàn)報(bào)每日存檔', today_1)
    Ad_path = ''.join(glob.glob(os.path.join(path, '在職人員明細(xì)*')))
    # print(Ad_path)

根據(jù)業(yè)務(wù)所需邦马，進(jìn)行相關(guān)字段處理，例如有些字段需要簡(jiǎn)稱宴卖，有些人員入職天數(shù)短被豁免不計(jì)入考核滋将；

df_aoding = pd.read_csv(Ad_path, header=0)
df_aoding.drop_duplicates(subset=['工號(hào)'],keep='first',inplace=True)
df_aoding_tichu = df_aoding[df_aoding.權(quán)限.isin(['綜合經(jīng)紀(jì)人','聯(lián)動(dòng)經(jīng)紀(jì)人','買賣經(jīng)紀(jì)人','新房經(jīng)紀(jì)人'])]
df_aoding_tichu.工號(hào) = df_aoding_tichu.工號(hào).astype(str)
df_aoding_tichu.事業(yè)部 = df_aoding_tichu.事業(yè)部.str.replace("事業(yè)部","").replace("鏈家豪宅","豪宅")
df_aoding_tichu["在職天數(shù)"] = (datetime.datetime.today()-pd.to_datetime(df_aoding_tichu.入職日期)).astype(str).str.split(" ",expand=True)[0]
df_aoding_tichu["在職天數(shù)"]=df_aoding_tichu["在職天數(shù)"].astype(int)
df_aoding_tichu.loc[df_aoding_tichu["事業(yè)部"]!="豪宅","大區(qū)"]=df_aoding_tichu['大區(qū)'].str.replace("區(qū)","大區(qū)")
df_aoding_tichu = df_aoding_tichu[df_aoding_tichu.在職天數(shù)>90]

根據(jù)經(jīng)紀(jì)人職級(jí)的不同，為其貼標(biāo)簽症昏；

zhiji_map = {'A0':'A0~2','A0/2':'A0~2','A1':'A0~2', 'A2':'A0~2','A3':'A3~5','A4':'A3~5','A5':'A3~5',
         'A6':'A6~10','A7':'A6~10','A8':'A6~10','A9':'A6~10','A10':'A6~10',
         'M1':'M1~M5','M2':'M1~M5','M3':'M1~M5','M4':'M1~M5','M5':'M1~M5',
         'M6':'M6~M10','M7':'M6~M10','M8':'M6~M10','M9':'M6~M10', 'M10':'M6~M10'}
df_aoding_tichu['職級(jí)分類'] = df_aoding_tichu['職級(jí)'].map(zhiji_map)

最新的人員架構(gòu)的工號(hào)耕渴，一對(duì)多關(guān)系連接timeline（含帶看+認(rèn)購(gòu)信息），并且計(jì)算好基礎(chǔ)字段（也可以在powerBI里面計(jì)算齿兔，但推薦能在代碼里面實(shí)現(xiàn)的盡量在代碼里面實(shí)現(xiàn)，畢竟代碼比powerBI的DAX函數(shù)穩(wěn)定，本人在DAX函數(shù)里面踩過(guò)坑）;

## 拼接最新帶看日期
engine_b = create_engine('mysql+pymysql://root:yunguan@520@10.8.29.213:3306/YG_ALL')
sql_b = '''
select aa.成交經(jīng)紀(jì)人工號(hào),aa.歷來(lái)最新帶看日期,bb.當(dāng)月帶看天數(shù),bb.當(dāng)月最新帶看日期
from
(select 成交經(jīng)紀(jì)人工號(hào),MAX(日期) as 歷來(lái)最新帶看日期 from Timeline_3 where 類別='A帶看' group by 成交經(jīng)紀(jì)人工號(hào)) aa
left join 
    (select 成交經(jīng)紀(jì)人工號(hào),count(distinct 日期) as 當(dāng)月帶看天數(shù),count(distinct 項(xiàng)目名稱) as 當(dāng)月帶看項(xiàng)目數(shù),max(日期) as 當(dāng)月最新帶看日期
    from Timeline_3
    where 類別='A帶看'
    and 日期>=date_add(curdate(), interval - day(curdate()) + 1 day) and 日期<=last_day(curdate())
    group by 成交經(jīng)紀(jì)人工號(hào)
  ) bb
on aa.成交經(jīng)紀(jì)人工號(hào)=bb.成交經(jīng)紀(jì)人工號(hào);
'''
df_c = pd.read_sql(sql=sql_b,con=engine_b)
###合并表分苇，拼接最新帶看日期
ad_jg_shishi = pd.merge(df_aoding_tichu,df_c,left_on='工號(hào)',right_on='成交經(jīng)紀(jì)人工號(hào)',how='left')
ad_jg_shishi["至今未帶看天數(shù)"] = (datetime.datetime.today()-pd.to_datetime(ad_jg_shishi.歷來(lái)最新帶看日期)).astype(str).str.split(" ",expand=True)[0]

###至今未帶看天數(shù)的分箱操作
ad_jg_shishi["至今未帶看天數(shù)"] = ad_jg_shishi["至今未帶看天數(shù)"].replace("NaT","1000000").astype(int)
ad_jg_shishi["至今未帶看標(biāo)簽"] = pd.cut(ad_jg_shishi.至今未帶看天數(shù),bins=[-1,7, 30, 60, 90, 200,1000003],
                                 labels=['A.7天內(nèi)','B.8~30天','C.31~60天','D.61~90天','E.91~200天','F.200天以上'] )

6.powerBI面板數(shù)據(jù)可視化
（1）表關(guān)系搭建
ad_jg_shishi的工號(hào)添诉，一對(duì)多關(guān)聯(lián)Timeline表的經(jīng)紀(jì)人工號(hào)。
新建的日期表的日期医寿，要一對(duì)多關(guān)聯(lián)Timeline表的日期栏赴。

image.png

（2）度量值
相關(guān)關(guān)鍵指標(biāo)計(jì)算如下。

帶看人數(shù) = CALCULATE(DISTINCTCOUNT('YG_ALL Timeline'[成交經(jīng)紀(jì)人工號(hào)]),FILTER('YG_ALL Timeline','YG_ALL Timeline'[客源編號(hào)]<>""),FILTER('YG_ALL Timeline','YG_ALL Timeline'[成交事業(yè)部]<>""))+0
帶看量 = CALCULATE(DISTINCTCOUNT('YG_ALL Timeline'[帶看標(biāo)記](méi)),FILTER('YG_ALL Timeline','YG_ALL Timeline'[類別]="A帶看"))
工號(hào)計(jì)數(shù) = DISTINCTCOUNT(ad_jg_shishi[工號(hào)])
至今未帶看標(biāo)簽分類 = COUNTA(ad_jg_shishi[至今未帶看標(biāo)簽])
各職級(jí)計(jì)數(shù) = COUNTA(ad_jg_shishi[職級(jí)分類])
產(chǎn)生過(guò)帶看的人數(shù) = DISTINCTCOUNT(ad_jg_shishi[成交經(jīng)紀(jì)人工號(hào)])

7.結(jié)果展示

image.png

image.png