Kaggle项目 — Hotel booking demand 业务情况分析

分析思路：

一、整体营业情况

（1）月订单数量 -----> 衡量酒店的热门程度

       图中黄色为度假酒店，蓝色为城市酒店；不难看出，城市酒店的订单数量存在明显的周期性。12月-1月附近是预定低谷，同时在7月份附近也会出现小幅度的回落，而5月及10月附近是全年的预定高峰期。另一方面，就趋势总体而言是上涨的。而度假酒店的订单量波动趋势和城市酒店类似，但波动幅度大幅度减小，即需求量相对稳定。

（2）月收入-----> 衡量酒店的盈利能力

       城市酒店的月收入与其订单数呈高度相关，走势几乎完全一致。因此可以大致推断出城市酒店的价格波动幅度较小，即买的越多收入越高。同样，就整体趋势而言是上涨的。对于度假酒店，与订单数量不同的是，收入出现了极其鲜明的周期性。三年的高峰期都是8月，最低谷都是1月，在峰谷之间几乎没有任何波动。故推测这与度假酒店行业的定价和盈利模式相关。

select 'HotelType','Date',
	'Order_Num','Income','canceled_num','true_num','canceled_rate','adr'
union all
select 
	hotel as HotelType, 
	concat(arrival_date_year,"-",arrival_date_month) as `date`, 
	count(*) as Num, 
    sum((1-is_canceled)*adr*(stays_in_week_nights+stays_in_weekend_nights))/10000 income 
from bookingdata 
group by hotel, arrival_date_year, arrival_date_month;
into outfile 'D://Desktop//general.csv'
FIELDS TERMINATED BY ',' optionally ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

二、人员结构变化

（1）年龄结构

        从图中可以看出，无论是城市酒店还是度假酒店，消费主题都是成年人，且婴儿的比例都极低，基本可以忽略不计。成年人的比例几乎全部维持在90%以上，而孩童的占比会在每年的7-8月份迎来高潮，能够达到10%左右，其余月份均在5%上下波动。结合酒店淡旺季即为旺季孩童比例比例较高，淡季比例较低。

select 'HotelType','Date',
	'adult_rate','child_rate','baby_rate','repeat_rate'
union all
select
	hotel as HotelType, 
	concat(arrival_date_year,"-",arrival_date_month) as `date`,
    sum(adults)/(sum(adults)+sum(children)+sum(babies)) adult_rate,
    sum(children)/(sum(adults)+sum(children)+sum(babies)) child_rate,
    sum(babies)/(sum(adults)+sum(children)+sum(babies)) baby_rate,
    sum(is_repeated_guest)/count(*) repeat_rate
from bookingdata
group by hotel, arrival_date_year, arrival_date_month
into outfile 'D://Desktop//customerStruct.csv'
FIELDS TERMINATED BY ',' optionally ENCLOSED BY ''
LINES TERMINATED BY '\r\n';

select distinct is_repeated_guest from bookingdata;
select distinct customer_type from bookingdata;
select distinct market_segment from bookingdata;
select distinct distribution_channel from bookingdata;

 

2）国籍分布

        总体来说：从前六国籍一览表中可以看出，几乎所有的月份都是葡萄牙的游客最多，因此可以推断出本酒店数据应该是来在葡萄牙地区的酒店。

        就城市酒店而言，根据层叠柱状图可以看出，无论是本土顾客的占比还是前六国籍顾客的总占比都在迅速下滑。数据初期本土顾客数量占比高达90%以上，而到数据末期，本土顾客占比不足20%、前六总占比不足70%。故城市酒店具有明显的国籍多元化趋势。

        和城市酒店不同，度假酒店的国际多元化的趋势不是非常明显，不过也存在缓慢的多元化趋势。在度假酒店身上更明显的是本土顾客的周期性波动：每年1月份前后本土游客占比显著高于其他月份，即淡季的本土顾客率很高。

select 'HotelType','Date',
	'country_1st','country_2nd','country_3th','country_4th','country_5th','country_6th',
	'prop_1st','prop_2nd','prop_3th','prop_4th','prop_5th','prop_6th' 
union all 
select 
	hotel as HotelType, 
	concat(arrival_date_year,"-",arrival_date_month) as `date`,
    (select country from bookingdata where hotel=b.hotel and arrival_date_year=b.arrival_date_year and arrival_date_month=b.arrival_date_month group by country order by count(*) desc limit 1) country_1st,
    (select country from bookingdata where hotel=b.hotel and arrival_date_year=b.arrival_date_year and arrival_date_month=b.arrival_date_month group by country order by count(*) desc limit 1,1) country_2nd,
    (select country from bookingdata where hotel=b.hotel and arrival_date_year=b.arrival_date_year and arrival_date_month=b.arrival_date_month group by country order by count(*) desc limit 2,1) country_3th,
	(select country from bookingdata where hotel=b.hotel and arrival_date_year=b.arrival_date_year and arrival_date_month=b.arrival_date_month group by country order by count(*) desc limit 3,1) country_4th,
    (select country from bookingdata where hotel=b.hotel and arrival_date_year=b.arrival_date_year and arrival_date_month=b.arrival_date_month group by country order by count(*) desc limit 4,1) country_5th,
    (select country from bookingdata where hotel=b.hotel and arrival_date_year=b.arrival_date_year and arrival_date_month=b.arrival_date_month group by country order by count(*) desc limit 5,1) country_6th,
	(select count(*) from bookingdata where hotel=b.hotel and arrival_date_year=b.arrival_date_year and arrival_date_month=b.arrival_date_month group by country order by count(*) desc limit 1)/count(*) prop_1st,
    (select count(*) from bookingdata where hotel=b.hotel and arrival_date_year=b.arrival_date_year and arrival_date_month=b.arrival_date_month group by country order by count(*) desc limit 1,1)/count(*) prop_2nd,
    (select count(*) from bookingdata where hotel=b.hotel and arrival_date_year=b.arrival_date_year and arrival_date_month=b.arrival_date_month group by country order by count(*) desc limit 2,