一.业务背景

作为数据输出端，每日或每月，各业务人员会定时来要相关业务数据，这时就可以用SQL语句+powerBI面板定时输出，给业务人员面板中下载数据的权限，做到一劳永逸。
本次数据：为某房地产开发行业-民宿运营业务的数据，下图为数据库相关关键字段的关联关系。

image.png

业务目标

1.可以从时间，地理纬度看承包的民宿源都来自哪里。
2.这些房源的总体运营情况，从订单量，订单GMV考察。

所涉及数据源

本次运用的数据库表主要有：
1.constract合同表，记录了从全国各地承包民宿房子所签署的合同信息。
2.tbl_info_house房屋表，记录了房源id，房源地理位置（城市、经纬度），房源运营的组织架构（运营商户，运营组）。
3.tbl_biz_order订单表,记录了顾客下的每笔订单的详细信息，例如顾客入住时间、离店时间，入住人，入住顾客信息，入住民宿所花费的各类费用等。

业务逻辑计算公式

总收入=归来订单收入=各渠道归来订单收入总和
实住间夜=归来订单实住间夜
入住率=实住间夜/累计间夜数
拒单率=拒单数/总支付成功订单数
归来订单=客户已经入住的订单数
平均售卖价钱=房屋下总售卖房费/关联订单实售间夜数
RevPar=gmv\在线房源数
平均客单价=gmv/订单
gmv=订单*平均客单价
平均间夜价=gmv/销售间夜数
入住率=当天入住/在线房源
入住率=实际入住间夜数/累计间夜数
ROI= 收入/成本 >100% 赚
ROI= 收入/成本 >100% 赔
同比增长率=(本期数-同期数)÷同期数×100%
环比增长率=(本期数-上期数)/上期数×100%。
平均间夜数=间夜数/订单数
毛利率=gmv-成本 /GMV

二.数据处理

constract表

在数据库中记录的大概信息如下。

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('武侯区')>=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数据源链接这个脚本，会生成一张基础表。

image.png

其中create_date需要做时间切片，所以将这个字段转换成日期格式。
新添加列-年；新添加列-月；并将年月组合连接起来。

image.png

image.png

image.png

tbl_info_house表

直接从数据库读取，稍作处理即可。

image.png

订单总览表count_order

根据订单情况，使用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

创建相关表关系

在关系构建中，需要将tbl_info_house的关键字段与count_order相连接，构建模型（当然，这一步也可以在通过SQL语句链接，因为之前SQL语句写的较长，因此选择在powerBI里连接表）。

image.png

房屋运营概况

制作三个切片，时间切片，商户切片和房屋所在城市切片。
主要数据表描述各个运营小组所运营民宿的订单状况，GMV状况。

六.最终展示

这些报表数据是业务人员每天下载查看的，因此将账号及授权下载权限提供给相关业务人员即可。
--合同签署情况

image.png

image.png