一.業(yè)務背景

作為數(shù)據(jù)輸出端闭翩，每日或每月方咆，各業(yè)務人員會定時來要相關業(yè)務數(shù)據(jù)票编，這時就可以用SQL語句+powerBI面板定時輸出褪储，給業(yè)務人員面板中下載數(shù)據(jù)的權限，做到一勞永逸慧域。
本次數(shù)據(jù)：為某房地產(chǎn)開發(fā)行業(yè)-民宿運營業(yè)務的數(shù)據(jù)鲤竹，下圖為數(shù)據(jù)庫相關關鍵字段的關聯(lián)關系。

image.png

業(yè)務目標

1.可以從時間昔榴，地理緯度看承包的民宿源都來自哪里辛藻。
2.這些房源的總體運營情況，從訂單量互订，訂單GMV考察吱肌。

所涉及數(shù)據(jù)源

本次運用的數(shù)據(jù)庫表主要有：
1.constract合同表，記錄了從全國各地承包民宿房子所簽署的合同信息仰禽。
2.tbl_info_house房屋表氮墨，記錄了房源id纺蛆，房源地理位置（城市、經(jīng)緯度）勇边，房源運營的組織架構（運營商戶犹撒，運營組）。
3.tbl_biz_order訂單表,記錄了顧客下的每筆訂單的詳細信息粒褒，例如顧客入住時間、離店時間诚镰，入住人奕坟，入住顧客信息，入住民宿所花費的各類費用等清笨。

業(yè)務邏輯計算公式

總收入=歸來訂單收入=各渠道歸來訂單收入總和
實住間夜=歸來訂單實住間夜
入住率=實住間夜/累計間夜數(shù)
拒單率=拒單數(shù)/總支付成功訂單數(shù)
歸來訂單=客戶已經(jīng)入住的訂單數(shù)
平均售賣價錢=房屋下總售賣房費/關聯(lián)訂單實售間夜數(shù)
RevPar=gmv\在線房源數(shù)
平均客單價=gmv/訂單
gmv=訂單*平均客單價
平均間夜價=gmv/銷售間夜數(shù)
入住率=當天入住/在線房源
入住率=實際入住間夜數(shù)/累計間夜數(shù)
ROI= 收入/成本 >100% 賺
ROI= 收入/成本 >100% 賠
同比增長率=(本期數(shù)-同期數(shù))÷同期數(shù)×100%
環(huán)比增長率=(本期數(shù)-上期數(shù))/上期數(shù)×100%月杉。
平均間夜數(shù)=間夜數(shù)/訂單數(shù)
毛利率=gmv-成本 /GMV

二.數(shù)據(jù)處理

constract表

在數(shù)據(jù)庫中記錄的大概信息如下。

image.png

原表中有一列province里面空值太多抠艾，這個字段被棄用了苛萎。如果想提取合同簽署的城市信息，還有有一列地理位置信息name需要處理一下检号，希望從詳細的地理位置信息中提取簡要的城市信息腌歉，因此這個表最終是用腳本處理的。

image.png

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

engine = create_engine('mysql+mysqldb://root:****@localhost:3306/*****')
sql1 = '''select * from contract '''
contract = pd.read_sql_query(sql1, engine)

contract['name'] = contract['name'].map(lambda x:x.strip())
contract['city'] = contract['name'].map(lambda x:x[:9])

city = contract['city'].tolist()

city_list = []
for i in city:
    if i[2]=='市':
        city_list.append(i[:2])
    elif i[2]=='省':
        city_list.append(i[3:5])
    elif i[2]=='-':
        city_list.append(i[:2])
    elif i.find('武漢')>=0:
        city_list.append('武漢')
    elif i.find('西安')>=0:
        city_list.append('西安')
    elif i.find('南京')>=0:
        city_list.append('南京')
    elif i.find('寧波')>=0:
        city_list.append('寧波')
    elif i.find('杭州')>=0:
        city_list.append('杭州')
    elif i.find('桂林')>=0:
        city_list.append('桂林')
    elif i.find('莫干山')>=0:
        city_list.append('湖州')
    elif i.find('湖州')>=0:
        city_list.append('湖州')
    elif i.find('無錫')>=0:
        city_list.append('無錫')
    elif i.find('佛山')>=0:
        city_list.append('佛山')
    elif i.find('三月居')>=0:
        city_list.append('揚州')
    elif i.find('揚州')>=0:
        city_list.append('揚州')
    elif i.find('武侯區(qū)')>=0:
        city_list.append('成都')
    elif i.find('代運營')>=0:
        city_list.append('代運營')
    elif i.find('托管')>=0:
        city_list.append('代運營')
    elif i.find('重慶')>=0:
        city_list.append('重慶')
    elif i.find('投資')>=0:
        city_list.append('代運營')
    elif i.find('委托服務')>=0:
        city_list.append('代運營')
    elif i.find('上海')>=0:
        city_list.append('上海')
    elif i.find('哈爾濱')>=0:
        city_list.append('哈爾濱')
    else:
        city_list.append(i)

contract['citya'] = pd.Series(city_list)

powerBI數(shù)據(jù)源鏈接這個腳本齐苛，會生成一張基礎表翘盖。

image.png

其中create_date需要做時間切片，所以將這個字段轉換成日期格式凹蜂。
新添加列-年馍驯；新添加列-月；并將年月組合連接起來玛痊。

image.png

image.png

image.png

tbl_info_house表

直接從數(shù)據(jù)庫讀取混弥，稍作處理即可。

image.png

訂單總覽表count_order

根據(jù)訂單情況颈娜，使用sql語句將每日訂單情況匯總成count_order表剑逃。

select  a.house_id,a.order_code,merchant.merchant_name,a.check_in_time, DATE(a.order_time) order_data,hg.group_name,
    mark_zc as order_count,
    a.GMV*a.mark_zc as order_gmv,
    a.jianye*a.mark_zc as order_nights,
    mark_qx as order_count_qx,
    a.GMV*a.mark_qx as order_gmv_qx,
    a.jianye*a.mark_qx as order_nights_qx
    from (
        select house_id,order_code,merchant_id,order_status,type,inventory,order_time,check_in_time,
        ifnull(total_money,0)+ifnull(clean_money,0) as GMV,
        TIMESTAMPDIFF(day,check_in_time,check_out_time)*inventory as jianye,
        if(order_status IN ('WAITING_CHECKIN','CHECKIN','CHECKOUT'),1,0) as mark_zc,
        if(order_status ='CANCELED',1,0) as mark_qx
        from tbl_biz_order 
        where type = 'normal' 
        ) a 
left join tbl_info_house house on a.house_id = house.houseid
left join house_group_rel hgr on hgr.house_id = house.houseid
left join house_group hg on hg.id = hgr.house_group_id
left join merchant on merchant.id = a.merchant_id;

powerbi里面最終count_order表

image.png

創(chuàng)建相關表關系

在關系構建中，需要將tbl_info_house的關鍵字段與count_order相連接官辽，構建模型（當然蛹磺，這一步也可以在通過SQL語句鏈接，因為之前SQL語句寫的較長同仆，因此選擇在powerBI里連接表）萤捆。

image.png

房屋運營概況

制作三個切片，時間切片，商戶切片和房屋所在城市切片俗或。
主要數(shù)據(jù)表描述各個運營小組所運營民宿的訂單狀況市怎，GMV狀況。

六.最終展示

這些報表數(shù)據(jù)是業(yè)務人員每天下載查看的辛慰，因此將賬號及授權下載權限提供給相關業(yè)務人員即可区匠。
--合同簽署情況

image.png

image.png