[D+21][프로젝트 #1-3] 군집별 호텔 서비스 이용 점수와 일평균 매출의 상관 관계 분석과 시각화

📊 INDEX

01.  고객 구성에 따른 total_socre와 adr의 관계 
02. 국적에 따른 total_socre와 adr의 관계 
03. 마켓 세그먼트에 따른 total_score와 adr의 관계 

*호텔 서비스 이용 점수는 total_socre, 평균 일일 요금은 adr 칼럼입니다. 
*마켓 세그먼트(market_segment)는 고객의 예약 채널을 의미합니다. 

 

 

01. 고객 구성에 따른 total_score와 adr의 관계 

---

저는 SQL을 활용해서 데이터를 1차 집계했고, 해당 결과를 파이썬에 가져와서 시각화했어요. 

추후에 SQL에서 쿼리를 CSV 파일로 추출하는 방법은 나중에 한번 정리해서 올려보겠습니다! 

 

 

 

고객 구성에 따른 호텔 서비스 사용 점수를 집계하기 위해서 서브 쿼리를 두 번 사용했는데요. 

WITH 문을 사용했다면 훨씬 간결하고 가독성 있게 작업할 수 있었을 것 같아요. 

# 이용객 세그먼트(고객 유형)에 따른 호텔 서비스 사용 점수와 일평균 매출 집계 

SELECT 
    customer_segment,
    total_score, 
    ROUND(AVG(adr)) AS avg_adr 
FROM (
    SELECT # total_score에 따른 활성화 고객 세그먼트
    	  case when (meal_score + parking_score + request_score) <=1 then '[0~1점] 소극 이용'
    	  when (meal_score + parking_score + request_score) <=3 then '[2~3점] 편의 이용'
    	  else '[4점 이상] 적극 이용 고객' end as total_score, 
        adr, 
        hotel, 
        arrival_date_year, 
        arrival_date_month,
    # 인원 및 아이 여부에 따른 이용객 세그먼트 (철휘님, 유림님 쿼리 활용)
		CASE WHEN total_customer >= 4 AND youth_yn = 'y' THEN '다인가족'
             WHEN total_customer >= 4 AND youth_yn = 'n' THEN '단체'
             WHEN total_customer = 3 AND youth_yn = 'y' THEN '3인가족'
             WHEN total_customer = 3 AND youth_yn = 'n' THEN '3인'
             WHEN total_customer = 2 AND youth_yn = 'y' THEN '2인가족'
             WHEN total_customer = 2 AND youth_yn = 'n' THEN '2인'
             ELSE '1인' END customer_segment,
        youth_yn
    FROM (
        SELECT 
            hotel, 
            arrival_date_year, 
            arrival_date_month, 
            adr,
            # 총 인원 확인
            adults + babies + children AS total_customer,
            # 유아 동반 확인
            CASE WHEN babies + children > 0 THEN 'y' 
            	 ELSE 'n' 
            	 END AS youth_yn, 
            # 식사 서비스 사용에 따른 점수 할당 
            CASE 
                WHEN meal IN ('SC', 'BB') THEN 1 
                WHEN meal = 'HB' THEN 2 
                WHEN meal = 'FB' THEN 3 
                ELSE 0 
            	END AS meal_score, 
            # 주차 서비스 사용에 따른 점수 할당 
            CASE 
                WHEN required_car_parking_spaces BETWEEN 1 AND 3 THEN 1
                WHEN required_car_parking_spaces >= 4 THEN 2 
                ELSE 0 
            	END AS parking_score,
            # 특별 요청 서비스에 따산
            CASE 
                WHEN total_of_special_requests BETWEEN 1 AND 2 THEN 1
                WHEN total_of_special_requests >= 3 THEN 2 
                ELSE 0 
            	END AS request_score
        FROM processed_hotel_booking_file phbf  
        WHERE adr > 0 and is_canceled = 0 #일평균 매출 음수, 예약 취소 고객 제외 
    ) AS scores_and_family
) a
GROUP BY customer_segment, total_score
ORDER BY avg_adr desc;

 

위 쿼리 결과를 CSV 파일로 추출했고(파일명 cus_score) 

맥플롯립과 시본 라이브러리를 활용해서 막대 그래프를 만들었습니다. 

파이썬은 한글 글꼴을 지원하지 않기 때문에 타이블 및 범례 텍스트가 깨져서 나왔는데요, 

아래와 같이 글꼴을 지정하면 오류 없이 그래프를 추출할 수 있습니다. (소현 튜터님께서 알려주신 방법!) 

import pandas as pd 
import seaborn as sns 
import matplotlib.pyplot as plt

# DataFrame 정렬 
cus_score.sort_values(by=['customer_segment', 'total_score', 'avg_adr', 'cnt'], ascending=[True, True, False, False], inplace=True)

# 한글 폰트 설정 
plt.rcParams['font.family'] ='Malgun Gothic'
plt.rcParams['axes.unicode_minus'] =False

# 막대 그래프 생성 
sns.barplot(x='total_score', y='avg_adr', hue='customer_segment', data=cus_score)

# 타이틀 및 범례 설정
plt.title('이용객 유형별 호텔 사용 서비스 점수, 일평균 매출')
plt.legend(title='customer_segment', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.show

 

 

02. 국적에 따른 total_socre와 adr의 관계

---

처음에는 내국인과 외국인 두 집단이니 산점도(scatter) 그래프를 이용해서, 각 집단의 매출과 평균의 관계를 분석하면 되겠다 생각했는데요. x축과 y축에 모두 숫자형 데이터가 들어가야 해서 한 번에 나타내기에 어렵더라고요...  그리고 호텔 서비스 사용 점수가 모두 정수여서 줄무늬 티셔츠 마냥 결과가 나와더라구요 .... 🥲

 

여러가지 시각화 방법을 고민하다가 데이터를 가장 명료하게 보여줄 수 있는 피벗 테이블을 만들었습니다.

데이터에 적합한 시각화, 정리 방법을 아는 것도 숙련도가 더 필요하다는 것을 여실히 느꼈습니다. 

해당 작업도 데이터 집계는 SQL, 시각화/정제는 파이썬을 활용해서 진행했어요. 

국적에 따른 호텔 서비스 사용 점수와 일일 요금과의 관계

국적에 따른 호텔 서비스 사용 점수와 일평균 매출 데이터 집계(SQL) 

select `guest_country`,
	   total_score, 
       round(avg(adr)) as avg_adr
from 
(
    SELECT # total_score에 따른 활성화 고객 세그먼트
	    	   case when (meal_score + parking_score + request_score) <=1 then '[0~1점] 소극 이용'
    	   		when (meal_score + parking_score + request_score) <=3 then '[2~3점] 편의 이용'
    	   		else '[4점 이상] 적극 이용 고객' end as total_score, 
           adr, 
           `guest_country`,
           hotel, 
           arrival_date_year, 
           arrival_date_month
    FROM 
    (
        SELECT hotel, 
               arrival_date_year, 
               arrival_date_month, 
               adr, 
               if(country = 'PRT', '내국인', '외국인') as `guest_country`,	
               case when meal in ('SC', 'BB') then 1 
                    when meal = 'HB' then 2 
                    when meal = 'FB' then 3 
                    else 0 end as meal_score, 
               case when required_car_parking_spaces between 1 and 3 then 1
                    when required_car_parking_spaces >= 4 then 2 
                    else 0 end as parking_score,
               case when total_of_special_requests between 1 and 2 then 1
                    when total_of_special_requests >= 3 then 2 
                    else 0 end as request_score
        FROM processed_hotel_booking_file phbf 
        WHERE `adr` > 0 and is_canceled = 0
    ) as scores
) a
group by `guest_country`, total_score
order by avg_adr desc;

피벗 테이블 제작 (파이썬)
# DataFrame 정렬
country_score_str.sort_values(by=['guest_country', 'total_score', 'adr'], ascending=[True, True, False], inplace=True)

# 피벗 테이블 생성
country_score_str.pivot_table(
    index=['guest_country', 'total_score'],
    values='adr',
    aggfunc=['mean', 'max', 'min', 'count']).round()

 

03. 마켓 세그먼트에 따른 total_score와 adr의 관계 

---

팀원들과 데이터를 호텔 종류별로, 연도별로, 그리고 3개의 군집별로 나누어 보기로 해서

해당 마켓 세그먼트에 따른 호텔 서비스 사용 점수와 일일 요금의 관계도 집계해보고 그래프로 그려보았습니다. 

다만, 예약 경로 자체가 고객 유형이나 특징을 대표하긴 어려워서 3번 분석은 유의미한 결론을 도출하지 못했다고 생각해요😭 😭

그래도 진행한 내용이 있어 일단 정리해보았고, 위와 동일하게 SQL로 데이터 집계, 파이썬으로 시각화했습니다. 

마켓 세그먼트에 따른 호텔 서비스 사용 점수와 평균 일매출 집계 

select market_segment,
	   total_score,
       round(avg(adr)) as avg_adr
from 
(
    SELECT # total_score에 따른 활성화 고객 세그먼트
    	   case when (meal_score + parking_score + request_score) <=1 then '[0~1점] 소극 이용'
    	   		when (meal_score + parking_score + request_score) <=3 then '[2~3점] 편의 이용'
    	   		else '[4점 이상] 적극 이용 고객' end as total_score, 
           adr, 
           hotel, 
           arrival_date_year, 
           arrival_date_month,
           market_segment
    FROM 
    (
        SELECT hotel, 
               arrival_date_year, 
               arrival_date_month, 
               adr, 
               market_segment,
               # 호텔 사용 서비스에 따라 점수 할당 
               case when meal in ('SC', 'BB') then 1 
                    when meal = 'HB' then 2 
                    when meal = 'FB' then 3 
                    else 0 end as meal_score, 
               case when required_car_parking_spaces between 1 and 3 then 1
                    when required_car_parking_spaces >= 4 then 2 
                    else 0 end as parking_score,
               case when total_of_special_requests between 1 and 2 then 1
                    when total_of_special_requests >= 3 then 2 
                    else 0 end as request_score
        FROM processed_hotel_booking_file phbf 
        WHERE `adr` > 0 and is_canceled = 0
    ) as scores
) a
group by market_segment, total_score
order by avg_adr desc ;

# DataFrame 정렬 
market_score.sort_values(by=['market_segment', 'total_score', 'avg_adr'], ascending=[True, True, True], inplace=True)

# 글꼴 설정
plt.rcParams['font.family'] ='Malgun Gothic'
plt.rcParams['axes.unicode_minus'] =False

# 시본을 사용한 막대 그래프 생성
sns.barplot(x='total_score', y='avg_adr', hue='market_segment', data=market_score)

# 타이틀 및 범례 설정 
plt.title('예약 채널별 호텔 서비스 사용 점수, 일평균 매출')
plt.legend(title='market_segment', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.show

---

 

[ Today I Learned ] 처음엔 고객의 행동이나 유형을 특정할 수 있는 칼럼이 많이 없어서, '호텔 서비스를 적극적으로 사용하는 고객이 더 많은 소비를 하여 평균 매출이 높을 것이다.'라는 가설을 세워 칼럼을 추가했던 것인데요. 전반적으로 호텔 서비스 사용 점수가 높을수록 평균 매출이 높은 양의 상관 관계를 보여주고 있어서 어느 정도 가설이 맞은 것 같아 재미있었습니다. 만약, 더 많은 데이터를 확인할 수 있었다면 조금 더 뾰족한 분석을 할 수 있지 않을까 싶어요!  그리고 데이터에 적합한 시각화 방법을 고려하고 적용하는 것도 많은 고민이 필요하다는 사실을 느꼈습니다. 아래는 제가 열심히 노력해봤지만 망한 그래프들이에요 ... 오늘을 기념하며 올려봅니다.

 

02번 데이터에 피봇 테이블을 적용하기 전 거쳤던 많은 시행 착오들(=그래프)