一、选手简介
1、选手介绍
· 个人介绍：帆软社区用户名lucaluca，坐标广州，是数据工程师，对数据分析、数据可视化很感兴趣，最近想学数据挖掘相关的内容，有点无从下手哈哈哈。
2、参赛初衷
· 之前没有用过FineBI，觉得以赛促学效率更高，所以报名了。想验证下自己的数据分析能力，也想和FineBI大神们交流学习，希望能有所收获。
二、作品介绍
1、业务背景
1.1 背景介绍
数字化时代，数据已经成为企业决策的关键一环。酒店业作为面向市场的服务业的重要组成部分，数据已存在于酒店业服务的方方面面。通过数据分析，能更好地为酒店经营助力。
City Hotel 位于葡萄牙里斯本，经营状况良好，但决策不能仅凭直觉，管理者们希望借助数据分析，以数据为依托，更直观、更客观地了解酒店的经营情况，并梳理接下来的经营思路。
1.2 需求痛点
酒店每天都在和数据打交道，但光看一条条预订明细，又无法直接得出业务洞察。
历史经营数据的统计维度较少，也缺少来自客户方面的评论数据，如何在相对有限的数据中，提取出可能对经营决策有价值的信息，是本次分析的难点。
2、数据来源
· 自选数据：来自Kaggle公开数据集，原始数据为1张表，通过Python清洗整合，得到两张表，还有一张是按网上信息整理的国家名称对照表，共计三张表。
· 链接：https://www.kaggle.com/datasets/jessemostipak/hotel-booking-demand
数据源① Kaggle数据源，清洗后，名称 hotel_bookingsV2
由Kaggle整理，备注由个人补充。清洗时，新增了Order ID用于区分，其实也增加了其他字段，但后续分析没有用上，省略不写。
|
字段
|
含义
|
备注
|
|
Order_ID
|
通过Python添加的索引字段
|
默认一条数据是一个订单
|
|
hotel
|
Hotel (H1 = Resort Hotel or H2 = City Hotel)
|
酒店名称，本次只分析 City Hotel 部分
|
|
is_canceled
|
Value indicating if the booking was canceled (1) or not (0)
|
预约已取消=1，未取消=0
|
|
lead_time
|
Number of days that elapsed between the entering date of the booking into the PMS and the arrival date
|
预约进入PMS系统之日起，到客户实际抵达之间的天数
|
|
arrival_date_year
|
Year of arrival date
|
|
|
arrival_date_month
|
Month of arrival date
|
|
|
arrival_date_week_number
|
Week number of year for arrival date
|
|
|
arrival_date_day_of_month
|
Day of arrival date
|
|
|
stays_in_weekend_nights
|
Number of weekend nights (Saturday or Sunday) the guest stayed or booked to stay at the hotel
|
入住或预约入住的夜数（仅统计周末）
|
|
stays_in_week_nights
|
Number of week nights (Monday to Friday) the guest stayed or booked to stay at the hotel
|
入住或预约入住的夜数（仅统计工作日）
|
|
adults
|
Number of adults
|
|
|
children
|
Number of children
|
|
|
babies
|
Number of babies
|
|
|
meal
|
Type of meal booked. Categories are presented in standard hospitality meal packages: Undefined/SC – no meal package; BB – Bed & Breakfast; HB – Half board (breakfast and one other meal – usually dinner); FB – Full board (breakfast, lunch and dinner)
|
Undefined/SC – 无餐包; BB – 住宿加早餐; HB – 半食宿（两餐）; FB – 全膳
|
|
country
|
Country of origin. Categories are represented in the ISO 3155–3:2013 format
|
客户来自哪些国家
|
|
market_segment
|
Market segment designation. In categories, the term “TA” means “Travel Agents” and “TO” means “Tour Operators”
|
细分市场
|
|
distribution_channel
|
Booking distribution channel. The term “TA” means “Travel Agents” and “TO” means “Tour Operators”
|
分销渠道，Direct--直销、Corporate--协议客户、TO/TA --旅社、GDS--分销系统
|
|
is_repeated_guest
|
Value indicating if the booking name was from a repeated guest (1) or not (0)
|
老客=1，新客=0，有预订但后面取消了也是老客。
|
|
previous_cancellations
|
Number of previous bookings that were cancelled by the customer prior to the current booking
|
|
|
previous_bookings_not_canceled
|
Number of previous bookings not cancelled by the customer prior to the current booking
|
|
|
reserved_room_type
|
Code of room type reserved. Code is presented instead of designation for anonymity reasons.
|
预约的房型
|
|
assigned_room_type
|
Code for the type of room assigned to the booking. Sometimes the assigned room type differs from the reserved room type due to hotel operation reasons (e.g. overbooking) or by customer request. Code is presented instead of designation for anonymity reasons.
|
实际分配的房型
|
|
booking_changes
|
Number of changes/amendments made to the booking from the moment the booking was entered on the PMS until the moment of check-in or cancellation
|
|
|
deposit_type
|
Indication on if the customer made a deposit to guarantee the booking. This variable can assume three categories: No Deposit – no deposit was made; Non Refund – a deposit was made in the value of the total stay cost; Refundable – a deposit was made with a value under the total cost of stay.
|
押金类型： No Deposit – 无押金; Non Refund – 不退款; Refundable – 可退款
|
|
agent
|
ID of the travel agency that made the booking
|
|
|
company
|
ID of the company/entity that made the booking or responsible for paying the booking. ID is presented instead of designation for anonymity reasons
|
|
|
days_in_waiting_list
|
Number of days the booking was in the waiting list before it was confirmed to the customer
|
|
|
customer_type
|
Type of booking, assuming one of four categories: Contract - when the booking has an allotment or other type of contract associated to it; Group – when the booking is associated to a group; Transient – when the booking is not part of a group or contract, and is not associated to other transient booking; Transient-party – when the booking is transient, but is associated to at least other transient booking
|
|
|
adr
|
Average Daily Rate as defined by dividing the sum of all lodging transactions by the total number of staying nights
|
已售客房的平均房价，也称平均每日房价，ADR = 订单交易额/停留夜数，默认单位：欧元€
|
|
required_car_parking_spaces
|
Number of car parking spaces required by the customer
|
所需停车位数
|
|
total_of_special_requests
|
Number of special requests made by the customer (e.g. twin bed or high floor)
|
数值，仅统计要求的数量，不含具体内容
|
|
reservation_status
|
Reservation last status, assuming one of three categories: Canceled – booking was canceled by the customer; Check-Out – customer has checked in but already departed; No-Show – customer did not check-in and did inform the hotel of the reason why
|
预约最终状态：Canceled – 已取消; Check-Out – 已退房; No-Show – 未入住但已告知
|
|
reservation_status_date
|
Date at which the last status was set. This variable can be used in conjunction with the ReservationStatus to understand when was the booking canceled or when did the customer checked-out of the hotel
|
|
数据源② 由原始数据源清洗得到明细表，名称：入住日明细
原表是一个订单一条数据，为了分析客户的在店时间，通过Python整理，将数据源1按 OrderID-在店日期（date） 整合为明细。
|
字段
|
含义
|
|
Order_ID
|
同数据源1
|
|
date
|
客户实际在店日期，按arrival_date和reservation_status_date 统计，一天一条数据
|
|
hotel
|
酒店名称
|
|
assigned_room_type
|
房型
|
|
stays_in_weekend_nights
|
停留天数，仅统计周末，用于核对不用计算
|
|
stays_in_week_nights
|
停留天数，仅统计工作日，用于核对不用计算
|
|
adr
|
平均每日房价，房收入/停留夜晚数
|
|
reservation_status_date
|
离店时间
|
数据源③ 国家名称对照表
|
字段
|
含义
|
|
ISO3
|
参考 ISO 3155
|
|
Country name
|
国家英文名
|
|
中文
|
国家中文名
|
3、分析思路
已选定数据源，确认方向：
- 数据源未包含客户ID，仅通过标签 is_repeated_guest 表示客户是否曾下过单。若下过单又取消了，也算老客，标签不够准确 → 新老客户分析、消费频次Pass
- total_of_special_requests 不含客户具体的需求，是数值字段 → 语料分析、用户需求分析Pass
- 数据源未包含利润、成本等数据，房间总数只能推算，放弃用利润、利润率、入住率等指标，可转换为经营指标的字段有 adr、Order ID、是否取消等，因此确定KPI为 有效订单数、收入、单均收入、取消率，入住率用已售房数替代。 → 经营分析OK
报告可分解为四个部分：
a.指标一览，通过KPI与趋势图等，判断经营情况，确认问题；
旅游业、酒店业一般有淡旺季，选用同比、同期累计。除了KPI，再加上趋势图，在“确认问题”上方向更准。
b.经营分析，了解多个关键维度情况，发现与所确认问题相关的因素；
上一部分初步定下了两个方面：取消（退订）、增速减缓 -- 在可视化报告部分详细阐述
关于酒店的维度，按5W2H为思路，按实际字段，拆解为：
- why：终极问题，酒店经营情况如何？直接问题，为什么退订那么多？
- where：客户来自哪里？
- who：客户是谁，主要客户群是哪个？
- when：入住时间有什么趋势？
- what：客户多选择什么房型？不同客户群选择上有什么差异？
- how：渠道、市场细分、押金模式，以上维度与如何获客，或客户行为有关
- how many：房型部分有探讨adr，因此这部分讨论留宿时长。
思路：
拆解时间维度，看指标的时间趋势；
对比分析，因为海内、外客户市场在退订率、下单量都有较大区别；
围绕why问题，按w/h维度分析拆解，探究用户情况，确认与取消率的关系；
c.问题诊断，确认导致问题的因素、下一步的措施；
d.总结与建议
4、数据处理
数据清洗利用 Python+FineBI
第一个数据源，做数据预览，判断数据质量，添加Order ID标签。
注：数据源1条记录代表预订一个房间，时间颗粒度到天，且不显示客户ID，整个数据源存在重复项正常。重复项中有相当多为正常入住，因此不对数据进行去重。
第二个数据源，在第一个数据源基础上，将按订单聚合 整合为按订单-日期 的明细数据。
注：由于存在入住天数为0但adr>0的数据，属于有效数据（小时房），故每条订单停留天数N+1导出，再在FineBI中按条件二次过滤。
 
第二个数据源效果如下
清理逻辑 -- 过滤、缺失值填充、异常值处理、字段设置等。
   
5、可视化报告
Part1 KPI一览与趋势
5.1.1 指标定义
|
KPI
|
具体指标
|
定义
|
|
年有效订单
|
年有效订单
|
抵达日为节点，当年未取消的订单数之和
|
|
同期累计
|
从上年1月到上年同期的累计值
|
|
同期累计%
|
2017有效订单/2016同期累计有效订单-1
|
|
年收入
|
年收入
|
单笔收入=adr*夜晚数，按年汇总所有有效订单
|
|
同期累计%
|
2017收入/2016同期累计收入-1
|
|
年单均收入
|
年单均收入
|
年收入/年有效订单数
|
|
同期累计%
|
2017单均收入/2016同期累计单均收入-1
|
|
8月有效订单
|
8月有效订单
|
抵达日为节点，8月未取消的订单数之和
|
|
同比
|
当月有效订单/去年同期有效订单-1
|
|
8月收入
|
8月收入
|
单笔收入=adr*夜晚数，汇总8月有效订单
|
|
同比
|
当月收入/去年同期收入-1
|
|
8月单均收入
|
8月单均收入
|
当月收入/当月有效订单数
|
|
同比
|
当月单均收入/去年同期单均收入-1
|
|
年订单取消率
|
|
抵达日为节点，年取消数/年总下单数
|
|
本国订单取消数
|
本国订单取消数
|
country=PRT，年取消订单总数
|
|
本国订单取消率
|
2017未取消本国订单数/2017本国订单数
|
|
海外订单取消数
|
海外订单取消数
|
country !=PRT，年取消订单总数
|
|
海外订单取消率
|
2017未取消海外订单数/2017海外订单数
|
5.1.2 构图
5.1.3 效果
观察：
- 以抵达日为节点，8月有效订单较去年同比略降5%
- 年取消率、年取消数指标，相对去年都是升高的，今年取消率达到42.62%，需要重视。
图1-3按客户实际在店日期统计
观察：
- 今年已售房数波动更小，且维持在不错的高位（图1），ADR收入较去年有提升（图3），说明整体经营良好；
- 折线图可以看出，5-10月是市场整体活跃的时间段（图2、3），但今年7-8月已售房数上升变缓（图2，在店统计），8月有效订单较去年同期下降5个百分点（图4，到店统计）。
因此，从市场来看、从同期来看，Q3是可能做到更好的。
总结
截至2017年8月，CH各项指标表现良好，有效订单数、住房收入、单均收入，比去年同期累计都有所提高，经营势头整体向好。
同时，有两个问题不容忽视：
1、订单退订问题，较去年同期累计，取消率提升9.67%，本国订单退订率高达65.5%；海外订单取消数涨幅最大，增加了2181笔；海外市场方面，法国、德国、英国等退订数过高；意大利、巴西等退订率过高，也应予以关注。
-- 葡萄牙：历史整体退订率高；海外：由于订单的增多，相应的取消量也上升；退订率有上涨的趋势。
2、无论是按 “到店日” 统计，还是按 “在店日” 统计，8月订单增速放缓。
Part2 经营分析（维度分析）
注：【3、分析思路】提及，按5W2H延伸，梳理酒店经营情况
客户来源（where）：客户来源国主要集中在欧洲、北美、南美，以欧洲最多，除了葡萄牙，客户主要来自法国、英国、德国、西班牙。
订单分布：预计2017到店的订单中，
① 本国Q3 下滑明显，仅1239笔，本地市场取消率高达64%，平均每个周期退订率都很高，是亟需应对的问题；
② 外国订单表现更强劲，Q3下单数5434，取消率保持在相对稳定的23%，但退订率仍有走高的趋势，需注意。
-- 从外国订单表现看，当前市场仍有活力，可能需要对葡萄牙市场做市场调研，或增加宣传投放。
客户构成（who）：以成人双人旅客、单人旅客为主，房收贡献接近80%左右，我们发现，家庭客户（父母+孩子）平均ADR要更高，而我们当前对这部分客户的开发较少，还有一定的市场潜力。
入住时段（when）：从月趋势观察，入住呈现季节性波动，5-10月为旺季，冬季入住率一般。
房型（what）：酒店有8种类型的房间，日均价在€100~€200 ，A房、D房是酒店主推房型，售价中等，在2017年所有到店订单中占90%。房型 E、F、G 售价偏高，但预定用户较少，售出率不理想。B、C、K房型在价格上区分度不高，且用户的订购率一般，建议对房型重新整合。家庭用户除了A、D房，最倾向预订E、F、G房型。
渠道（how）：本土市场主要通过TA/TO渠道获客，但其退订率异常高，应关注。海外市场通过官网预订（Direct）更多，但本地推广度一般。
市场细分（how）：葡萄牙-细分市场的 Offline TA/TO渠道、Groups渠道，取消率极高，应关注。Online TA 是 CH 海外获客的重要途径，目前该途径有高获客量、高取消率的特点，应关注。
押金模式（how）：No Deposit 是最主要的模式，占订单80%以上；Non Refund模式问题也最明显，选择此模式的用户99%的概率会退单，应注意。
留宿时长（how many）：双人旅客、家庭旅客，停留时间最长，平均3天；单人客户平均停留1天；外国客户平均的停留时间、平均每日房价，要略高于本国客户。
总结
（1）与客户取消有关的重要因素可能是：客户细分、押金类型。
- 葡萄牙市场Groups，Offline TA/TO 两个渠道 , 整体市场 押金模式 为non refund 的部分，取消率非常高，需要进一步追踪。
（2）家庭客户是潜力客群，可加强开发力度。
- 平均房收贡献，平均数高于双人、单人等常规客户；
- 平均留宿天数，相对较长，3天；
- 除了A、D房型，此群客户最倾向于选择E、F、G等adr相对更高的房型。
Part3 从上述两部分得出的初步结论，定位问题点。
退订率问题
1、葡萄牙市场退订率异常
葡萄牙问题：虽然2017退订增量不大，但历史整体退订率高达60%，更为严峻。（排查整体）
建议追溯 市场细分 Groups, Offline TA/TO 两个渠道、押金Non Refund 模式，同时，定位到 Agent_ID =1 的机构，在所有历史年份中，订单的取消高达4000+。
若要进一步跟踪，还需要更多交易细节。
2、海外市场部分退订有走高风险
海外问题：随着订单增多，退订数也变高了，且整体有走高的风险 （主要排查当年）
法国、德国、英国等退订数高的地区，意大利、巴西等退订率高（Part One）；海外的 Online TA 渠道有高获客量、高取消率的特点（Part Two）。
最终定位，Agent_ID =9 的机构，取消比例极高，且以上国家经过了此机构，且渠道是 Online TA。
若要更进一步跟踪，还需要更多交易细节。
3、退订率高，还与一个因素相关：提前预约天数
提前预约天数越大，客户取消预约的概率越高。
Part4 总结与建议
Lisboa City Hotel 酒店 2017年度1-8月经营状况整体不错。
年有效订单数、年收入、年单均收入，较去年同期累计（2016年1-8月），上升5%、26%、19%，当月指标较去年同期，也有一定提升。
值得进一步关注的问题是：
① 订单退订问题，较去年同期累计，取消率提升9.67%，本国订单呈现高退订率的特点，高达65.5%；海外订单取消数涨幅最大，提升了2181笔；海外市场方面，法国、德国、英国等退订数过高；意大利、巴西等退订率过高，也应予以关注。
② 无论是按 “到店日” 统计，还是按 “在店日” 统计，8月订单增速放缓。
总体而言，本地市场方面，2017年Q3订单下滑严重，取消率高达64%；海外市场方面，订单数已远超本国，但退订率仍有走高的趋势，法国、德国、英国呈现高退订数，巴西、意大利呈现高退订率。
因此，本次分析的方向是：查明并降低取消率、找到业务增长点
一、取消率
① 葡萄牙：针对取消率一直较高的问题，应追溯：市场细分 Groups， Offline TA/TO 两个渠道、押金 Non Refund 模式订单交易明细，进一步确认原因。同时，发现 Agent_ID =1 的机构，在所有历史年份中，订单的取消高达4000+。
② 海外市场，Online TA 目前该途径有高获客量、高取消率的特点，发现是 Agent ID=9 也存在异常，且上述提及高退订、高退订率国家均与此渠道有关。
③ 提前预约天数越长，取消率越高。
建议
（a） 排查 Agent 渠道，特别是 ID=1，ID=9
（b） 排查葡萄牙 Groups，Offline TA/TO 两个渠道 , 押金模式 non refund 渠道
（c） 针对提前2个月以上预约的客户，试点推出押金预约制；
（d） 针对退订客户，通过电邮、短信回访，推送酒店问卷，做好客户关怀并尽可能收集客户反馈；
二、业务增长点
在盘点中，我们发现酒店 B、C、K在夜均ADR上与A、D区分不大，但订购比例不够理想。
双人客户、单人客户，是当前最主要的客户群，房收贡献接近80%，但 家庭客户 平均ADR要更高、停留时间长、更倾向于预订 E、F、G 房型，有一定的市场潜力。
着力方向是 推广、客户挖掘、改善。
建议
（a）针对本地订单下滑的问题，应做好本地的市场调研、在线投放、推广；
（b）挖掘家庭客户，宣传上可突出Family Time 等概念；
（c） 对于房型，规划房型比例；在预订信息中，提及各个房型的亮点，吸引客户预订；
（d）做好旅客到店、离店等节点的客户关怀，提高用户粘性。
最终结果呈现的页面布局（实际颜色更亮）
三、参赛总结
1、FineBI工具
· 点赞：
布局调整非常方便；主题模型，对于多数据源分析也非常友好；有亮点，也有惊喜，希望帆软越来越好！
· 吐槽：
一、FineBI计算丰富度和灵活度差一些，且不支持在字段上做临时计算（逻辑判断）。
二、整体10w+数据量，FineBI做报表用下来宕机了好多次，电脑那叫一个烫手。
三、最崩溃的在于，已经在文本组件上写好的内容，会突然消失，然后重新写一遍。
第一点可以慢慢克服，第二三点希望改进下吧 _(:з」∠)_
2、心得体会
这次选数据源真的一波三折，出于各种原因，加上时间很赶，很多内容也没有做出来，有点遗憾。
但说到底，数据分析最重要的是分析思维，而不是工具，工具只能是锦上添花。数据分析上能学的还有太多。下次有机会还会再来参加，期待看到本次参赛的其他选手的作品~ | 
