관광회사 고객 행동 데이터 분석 (2)

저번 1차 분석을 바탕으로 심화 분석을 진행해보자. 

2024.03.11 - [프로젝트] - 관광회사 고객 행동 데이터 분석 (1)

관광회사 고객 행동 데이터 분석 (1)

 

체크인 수 컬럼을 중점으로 데이터를 분석해 볼 예정이다. 

외부 지역에 체크인을 한 횟수는 곧 여행 횟수라고 할 수 있다. (물론, 한 번의 여행에 오랜 기간을 여행하여 체크인 수가 커졌을 수도 있다)

이제부터 체크인 횟수를 여행 횟수 혹은 여행 기간이라고 생각해보자! 

체크인 수를 나타내는 컬럼은 yearly_avg_Outstation_checkins이다. 

 

먼저, 여행 상품을 구매한 사람들과 구매하지 않은 사람들에 따른 여행 횟수를 파악해보자. 

-- 여행 상품을 구매한 사람들, 구매하지 않은 사람들, 모든 사람들에 대한 컬럼 평균
SELECT 
  'Buy' AS taken_product_status,
  AVG(yearly_avg_Outstation_checkins) AS avg_yearly_avg_Outstation_checkins
FROM customer_behaviour_tourism
WHERE Taken_product = 1

UNION ALL

SELECT 
  'No Buy' AS taken_product_status,
  AVG(yearly_avg_Outstation_checkins) AS avg_yearly_avg_Outstation_checkins
FROM customer_behaviour_tourism
WHERE Taken_product = 0

UNION ALL

SELECT 
  'ALL' AS taken_product_status,
  AVG(yearly_avg_Outstation_checkins) AS avg_yearly_avg_Outstation_checkins
FROM customer_behaviour_tourism;

 

-> 여행 상품을 구매한 사람들은 평균적으로 9.7번의 체크인을 하였고, 

여행 상품을 구매하지 않은 사람들은 평균적으로 8번의 체크인을 진행하였다. 

전체 평균은 8.2번이다. 

 

다음으로 체크인 수에 따른 구매자와 비구매자의 차이를 살펴보자. 

-- 체크인 수에 따른 구매자/비구매자
SELECT
  yearly_avg_Outstation_checkins,
  COUNT(CASE WHEN Taken_product = 1 THEN 1 END) AS count_buyers,
  COUNT(CASE WHEN Taken_product = 0 THEN 1 END) AS count_non_buyers,
  COUNT(CASE WHEN Taken_product = 1 THEN 1 END) / (SELECT COUNT(*) FROM customer_behaviour_tourism WHERE Taken_product = 1) * 100 AS buyer_percentage,
  COUNT(CASE WHEN Taken_product = 0 THEN 1 END) / (SELECT COUNT(*) FROM customer_behaviour_tourism WHERE Taken_product = 0) * 100 AS non_buyer_percentage
FROM customer_behaviour_tourism
GROUP BY 1
ORDER BY 1;

 

 

--> 체크인 횟수가 1번일 때, 여행 상품 구매자와, 비구매자 모두 30%의 높은 비율을 보이는 것을 알 수 있다. 

==> 체크인 수가 1번일 때의 비율이 압도적으로 높으므로, 체크인 수에 따른 그룹을 나누어 구매자와 비구매자 현황을 파악해보자!

-- 체크인 수에 따른 그룹 생성 및 구매자와 비구매자 비율 파악
WITH cte AS (
  SELECT
    CASE
      WHEN yearly_avg_Outstation_checkins = 1 THEN 'Group 1(1)'
      WHEN yearly_avg_Outstation_checkins >= 2 AND yearly_avg_Outstation_checkins <= 10 THEN 'Group 2(2~10)'
      WHEN yearly_avg_Outstation_checkins > 10 AND yearly_avg_Outstation_checkins <= 20 THEN 'Group 3(11~20)'
      WHEN yearly_avg_Outstation_checkins > 20 THEN 'Group 4(21~)'
    END AS checkin_category,
    Taken_product
  FROM customer_behaviour_tourism
)
SELECT
  checkin_category,
  Taken_product,
  COUNT(*) AS count_customers,
  CASE
    WHEN Taken_product = 0 THEN (COUNT(*) / (SELECT COUNT(*) FROM cte WHERE Taken_product = 0)) * 100
    WHEN Taken_product = 1 THEN (COUNT(*) / (SELECT COUNT(*) FROM cte WHERE Taken_product = 1)) * 100
  END AS ratio_to_taken_product
FROM cte
GROUP BY checkin_category, Taken_product
ORDER BY checkin_category;

 

Group 1 : 체크인 수 1

Group 2 : 체크인 수 2~10

Group 3 : 체크인 수 11~20

Group 4 : 체크인 수 21~

 

--> 단순히 count 수 말고, 여행상품 구매자와 비구매자를 고려한 비율을 계산해볼 때, 

체크인 수가 상대적으로 낮을 때 여행상품을 구매하지 않은 사용자들이 더 많고, 

체크인 수가 상대적으로 높을 때는 여행상품을 구매한 사용자들이 더 많다. 

 

일단 체크인 수와 각 컬럼간의 상관계수 게산 결과는 다음과 같다. 

--> 여행 상품 구매 여부를 제외하고, 연 평균 여행 페이지에 남긴 댓글 수, 마지막 체크인 이후 지난 주 수, 회사 페이지 팔로우 여부, 근무중 여부, 성인여부, 가족수 등이 체크인 수(여행 수)와 상관도가 높아 보인다. 

 

 

그룹을 더 세분화하여 시각화를 해보자.

-- 체크인 수에 따른 그룹 생성 및 구매자와 비구매자 비율 파악(상세)
WITH cte AS (
  SELECT
    CASE
      WHEN yearly_avg_Outstation_checkins = 1 THEN 'Group 1(1)'
      WHEN yearly_avg_Outstation_checkins >= 2 AND yearly_avg_Outstation_checkins <= 5 THEN 'Group 2(2~5)'
      WHEN yearly_avg_Outstation_checkins >= 6 AND yearly_avg_Outstation_checkins <= 10 THEN 'Group 3(6~10)'
      WHEN yearly_avg_Outstation_checkins > 10 AND yearly_avg_Outstation_checkins <= 15 THEN 'Group 4(11~15)'
      WHEN yearly_avg_Outstation_checkins > 15 AND yearly_avg_Outstation_checkins <= 20 THEN 'Group 5(16~20)'
      WHEN yearly_avg_Outstation_checkins > 20 AND yearly_avg_Outstation_checkins <= 25 THEN 'Group 6(21~25)'
      WHEN yearly_avg_Outstation_checkins > 25 THEN 'Group 7(21~)'
    END AS checkin_category,
    Taken_product
  FROM customer_behaviour_tourism
)
SELECT
  checkin_category,
  Taken_product,
  COUNT(*) AS count_customers,
  CASE
    WHEN Taken_product = 0 THEN (COUNT(*) / (SELECT COUNT(*) FROM cte WHERE Taken_product = 0)) * 100
    WHEN Taken_product = 1 THEN (COUNT(*) / (SELECT COUNT(*) FROM cte WHERE Taken_product = 1)) * 100
  END AS ratio_to_taken_product
FROM cte
GROUP BY checkin_category, Taken_product
ORDER BY checkin_category;

 

--> 다음과 같은 시각화 테이블을 획득하였다. 

=> 체크인 수가 상대적으로 낮을 때 여행상품을 구매하지 않은 사용자들이 더 많고, 

체크인 수가 상대적으로 높을 때는 여행상품을 구매한 사용자들이 더 많다. 

 

 

 

 

가족 수에 따른 체크인 수를 알아보자. 

-- 가족 구성원 수에 따른 체크인 수
SELECT
  member_in_family,
  Taken_product,
  COUNT(*) AS total_count,
  AVG(yearly_avg_Outstation_checkins) AS avg_checkin_count
FROM customer_behaviour_tourism
GROUP BY member_in_family, Taken_product
ORDER BY 1;

--> 가족구성원의 수가 커질수록 평균 체크인 수가 커짐. 

 

==> 가족 구성원이 많을 수록 여행을 많이 간다(체크인을 많이 한다)라고 추측해볼 수 있음