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

캐글에서 제공해주는 데이터셋을 SQL으로 분석해보자!

분석하고자 하는 데이터셋은 아래에서 구하였다. 

https://www.kaggle.com/datasets/ddosad/customer-behaviour-tourism-portal/data

Tourism Page Engagement

 

 

먼저 데이터의 컬럼들을 살펴보자. 

총 17개의 컬럼이 존재한다. 

컬럼명	설명
UserID	사용자의 고유 식별자
Taken_product	사용자가 여행상품을 샀는지 여부
Yearly_avg_view_on_travel_page	사용자가 연간 평균적으로 여행 관련 페이지를 조회한 횟수
preferred_device	사용자가 로그인에 가장 선호하는 기기 유형
total_likes_on_outstation_checkin_given	사용자가 지난 1년간 외부 지역 체크인에 대해 주는 총 좋아요 수
yearly_avg_Outstation_checkins	사용자가 연간 평균적으로 외부 지역 체크인을 한 횟수
member_in_family	사용자가 계정에 언급한 가족 구성원의 총 인원 수
preferred_location_type	사용자가 여행에 가장 선호하는 위치 유형
Yearly_avg_comment_on_travel_page	사용자가 연간 평균적으로 여행 관련 페이지에 남긴 댓글 수
total_likes_on_outofstation_checkin_received	사용자가 지난 1년간 외부 지역 체크인에 대해 받은 총 좋아요 수
week_since_last_outstation_checkin	사용자의 마지막 외부 지역 체크인 업데이트 이후 경과한 주 수
following_company_page	고객이 회사 페이지를 팔로우하고 있는지 여부
montly_avg_comment_on_company_page	사용자가 회사 페이지에 월간 평균적으로 남긴 댓글 수
working_flag	고객이 현재 근무 중인지 여부
travelling_network_rating	여행을 좋아하는 친구들과의 관계를 나타내는 등급. 1이 가장 높고, 4가 가장 낮은 등급
Adult_flag	고객이 성인인지 여부
Daily_Avg_mins_spend_on_traveling_page	사용자가 회사의 여행 페이지에 평균적으로 소비하는 시간

 

이제 이러한 데이터를 바탕으로 어떤 사용자가 여행을 많이 하는지, 어떤 사용자가 여행 상품을 구매할 가능성이 많은지를 알아볼 예정이다! 

 

---

데이터 클리닝(Data Cleaning)

Customer behaviour Tourism_ori.csv

0.80MB

 

해당 사이트에서 제공해주는 csv 파일 데이터의 이름을 "Customer behaviour Tourism_ori.csv"로 정의하였다. 

먼저, 기본적인 데이터 분석을 진행해보자. 

 

첫번째로, csv 파일을 보면 결측치가 보이기에 제거하기로 하였다. 

그 숫자가 많지 않기에 단순히 제거를 한다. 

결측치 제거 전 데이터 개수 : 11760개

 

일부의 결측치는 단순히 빈 문자열이고, NULL인 결측치 값도 있으므로, 2가지 케이스를 모두 고려하여 결측치를 제거해보자. 

-- 결측치 제거(빈 문자열)
DELETE FROM customer_behaviour_tourism_ori
WHERE 1=1
	AND (TRIM(yearly_avg_view_on_travel_page) = '' OR 
		TRIM(total_likes_on_outstation_checkin_given) = '' OR
		TRIM(yearly_avg_outstation_checkins) = '' OR
		TRIM(preferred_location_type) = '' OR
		TRIM(yearly_avg_comment_on_travel_page) = '' OR
		TRIM(following_company_page) = '' OR
		TRIM(working_flag) = '' OR
		TRIM(preferred_device) = '');

 

-> 해당 데이터의 결측치가 Null 형태가 아닌 문자열이 비어있는 형태이므로, 위와 같이 결측치를 제거한다. 

 

NULL로 채워진 결측치도 있다. 아래의 코드로 제거해보겠다. 

-- 결측치 제거(NULL)
DELETE FROM customer_behaviour_tourism_ori
WHERE 
    yearly_avg_view_on_travel_page IS NULL OR 
    total_likes_on_outstation_checkin_given IS NULL OR
    yearly_avg_outstation_checkins IS NULL OR
    preferred_location_type IS NULL OR
    yearly_avg_comment_on_travel_page IS NULL OR
    following_company_page IS NULL OR
    working_flag IS NULL OR
    preferred_device IS NULL OR
    Adult_flag IS NULL;

 

추가적으로, 'yearly_avg_Outstation_checkins' 컬럼이 모두 int 자료형이지만, *로 된 값이 있어 결측치로 판단을 하고 삭제하겠다. 

 

-- yearly_avg_Outstation_checkins 컬럼의 *로 된 값 제거
DELETE FROM customer_behaviour_tourism_ori
WHERE yearly_avg_Outstation_checkins = "*";

 

모든 결측치를 제거한 결과 데이터의 개수는 10454개로 줄어들었다. 

 

다음으로, 기본적인 데이터 정리를 해주겠다. 

-- member_in_family 컬럼의 three를 3으로 대체
UPDATE customer_behaviour_tourism_ori
SET member_in_family = 3
WHERE member_in_family = 'three';

-- preferred_location_type 컬럼의 Tour  Travel을 Tour and Travel로 대체
UPDATE customer_behaviour_tourism_ori
SET preferred_location_type = 'Tour and Travel'
WHERE preferred_location_type = 'Tour  Travel';

-- preferred_location_type 컬럼의 Game, Movie, OTT를 Entertainment로 대체(수가 작기에 그룹화)
UPDATE customer_behaviour_tourism_ori
SET preferred_location_type = 'Entertainment'
WHERE preferred_location_type = 'Game'
OR preferred_location_type = 'Movie'
OR preferred_location_type = 'OTT';

-- preferred_device 컬럼의 ANDROID, Android OS를 Android로 대체
UPDATE customer_behaviour_tourism_ori
SET preferred_device = 'Android'
WHERE preferred_device = 'ANDROID'
OR preferred_device = 'Android OS';

-- preferred_device 컬럼의 Others를 Other로 대체
UPDATE customer_behaviour_tourism_ori
SET preferred_device = 'Other'
WHERE preferred_device = 'Others';

-- following_company_page 컬럼의 Yeso와 1을 Yes로 대체
UPDATE customer_behaviour_tourism_ori
SET following_company_page = 'Yes'
WHERE following_company_page = 'Yeso'
OR following_company_page = '1';

-- following_company_page 컬럼의 0을 No로 대체
UPDATE customer_behaviour_tourism_ori
SET following_company_page = 'No'
WHERE following_company_page = '0';

-- working_flag 컬럼의 0을 No로 대체
UPDATE customer_behaviour_tourism_ori
SET working_flag = 'No'
WHERE working_flag = '0';

 

기본적인 정리를 마친 데이터는 다음과 같다. 

 

이를 Export하여 "Customer behaviour Tourism_basic.csv"로 저장하였고, 해당 csv 파일을 기준으로 새로운 테이블을 생성하였다. (테이블 명: customer_behaviour_tourism)

 

Customer behaviour Tourism_basic.csv

0.81MB

 

---

이제, 위의 데이터를 통해 간단한 데이터 분석을 해보자! 

먼저, 앞서 기초 정리를 끝낸 후 데이터의 개수는 10454개이다. 

 

컬럼별 분석

각 컬럼에 대한 상세 분석을 진행해보겠다. 

일단 분석을 위해 Yes 값은 모두 1로, No 값을 모두 0으로 바꾸겠다. 

-- Taken_product, following_company_page, working_flag 컬럼의 Yes는 1로, No는 0으로 바꾼다. 
UPDATE customer_behaviour_tourism
SET Taken_product = CASE WHEN Taken_product = 'Yes' THEN 1 ELSE 0 END,
    following_company_page = CASE WHEN following_company_page = 'Yes' THEN 1 ELSE 0 END,
    working_flag = CASE WHEN working_flag = 'Yes' THEN 1 ELSE 0 END;

 

추가로, Adult_flag 변수는 성인인지 아닌지 여부를 나타내지만, 0, 1, 2, 3의 값을 갖는다. Q&A에 따르면,,,

0은 성인이 아니고, 나머지는 모두 성인이므로, 2와 3의 값을 1로 대체하도록 하겠다. 

-- Adult_flag의 2, 3은 성인(1)을 의미
UPDATE customer_behaviour_tourism
SET Adult_flag = 1
WHERE Adult_flag IN (2, 3);

 

 

 

이를 바탕으로 각 컬럼의 평균값을 조회해보겠다. (문자열 값을 갖는 preferred_device와 preferred_location_type 제외)

- 궁극적으로 여행상품을 산 사람은 16%이다. 

- 사용자가 연 평균 여행 관련 페이지를 조회한 횟수는 평균적으로 281회이다. 

- 지난 1년간 외부 지역 체크인에 대해 주는 총 좋아요 수는 평균적으로 28158개이다. 

- 연 평균 외부 지역에 체크인한 수는 평균적으로 8번이다. => 평균적으로 8번의 여행을 갔다고 생각할 수 있다. 

- 사용자의 평균적인 가족 구성원 수는 약 3명이다. 

- 사용자가 연 평균 여행 관련 페이지에 남긴 댓글 수의 평균은 75번이다. 

- 사용자가 지난 1년간 외부 지역 체크인에 대해 받은 총 좋아요 수의 평균은 6535번이다. 

- 마지막 체크인 이후 경과한 주는 평균 3주이다. 

- 회사 페이지를 팔로우는 약 28%의 사람들만 하였다. 

- 사용자가 회사 페이지에 남긴 월 평균 댓글 수의 평균은 약 29번이다. 

- 사용자의 약 15%가 현재 근무 중이다. 

- 여행을 좋아하는 친구들과의 관계는 약 2.7등급이다. -> 1등급은 사용자 주변에 여행을 좋아하는 친구들이 가장 많다는 의미이고, 4등급은 사용자 주변에 여행을 싫어하는 친구들이 가장 많다는 의미이다. 

- 성인인 고객이 약 57%이다. 

- 사용자가 해당 회사의 여행 페이지를 구경하는 시간은 평균적으로 13시간이다. 

 

여행 상품을 구매한 사용자의 특징

Taken_product 컬럼은 사용자가 여행 상품을 샀는지 여부를 나타내는 컬럼이다. 

몇 명이 샀는지, 몇 명이 안샀는지 알아보자. 

-- Taken_product 컬럼의 Yes, No 개수 및 비율 분석
SELECT 
  SUM(CASE WHEN Taken_product = 1 THEN 1 ELSE 0 END) AS count_1,
  SUM(CASE WHEN Taken_product = 0 THEN 1 ELSE 0 END) AS count_0,
  SUM(CASE WHEN Taken_product = 1 THEN 1 ELSE 0 END) / COUNT(*) AS ratio_Yes, 
  SUM(CASE WHEN Taken_product = 0 THEN 1 ELSE 0 END) / COUNT(*) AS ratio_No
FROM customer_behaviour_tourism;

 

 

-> Yes의 비율이 16%, No의 비율이 84%로, 여행상품을 사지 않은 사람들이 훨씬 많다. 

우리의 목표는 어떤 사람이 여행상품을 샀는지, 어떤 사람이 여행상품을 안샀는지 알아보는 것이기 때문에, 

그 특성에 맞게 데이터를 분석해보자. 

 

먼저, 여행 상품을 구매한 사람들의 평균 값들, 구매하지 않은 사람들의 평균 값들을 통합적인 컬럼 평균과 비교해보자. 

-- 여행 상품을 구매한 사람들, 구매하지 않은 사람들, 모든 사람들에 대한 컬럼 평균
SELECT 
  'Yes' AS taken_product_status,
  AVG(Yearly_avg_view_on_travel_page) AS avg_Yearly_avg_view_on_travel_page,
  AVG(total_likes_on_outstation_checkin_given) AS avg_total_likes_on_outstation_checkin_given,
  AVG(yearly_avg_Outstation_checkins) AS avg_yearly_avg_Outstation_checkins,
  AVG(member_in_family) AS avg_member_in_family,
  AVG(Yearly_avg_comment_on_travel_page) AS avg_Yearly_avg_comment_on_travel_page,
  AVG(total_likes_on_outofstation_checkin_received) AS avg_total_likes_on_outofstation_checkin_received,
  AVG(week_since_last_outstation_checkin) AS avg_week_since_last_outstation_checkin,
  AVG(following_company_page) AS avg_following_company_page,
  AVG(montly_avg_comment_on_company_page) AS avg_montly_avg_comment_on_company_page,
  AVG(working_flag) AS avg_working_flag,
  AVG(travelling_network_rating) AS avg_travelling_network_rating,
  AVG(Adult_flag) AS avg_Adult_flag,
  AVG(Daily_Avg_mins_spend_on_traveling_page) AS avg_Daily_Avg_mins_spend_on_traveling_page
FROM customer_behaviour_tourism
WHERE Taken_product = 1

UNION ALL

SELECT 
  'No' AS taken_product_status,
  AVG(Yearly_avg_view_on_travel_page) AS avg_Yearly_avg_view_on_travel_page,
  AVG(total_likes_on_outstation_checkin_given) AS avg_total_likes_on_outstation_checkin_given,
  AVG(yearly_avg_Outstation_checkins) AS avg_yearly_avg_Outstation_checkins,
  AVG(member_in_family) AS avg_member_in_family,
  AVG(Yearly_avg_comment_on_travel_page) AS avg_Yearly_avg_comment_on_travel_page,
  AVG(total_likes_on_outofstation_checkin_received) AS avg_total_likes_on_outofstation_checkin_received,
  AVG(week_since_last_outstation_checkin) AS avg_week_since_last_outstation_checkin,
  AVG(following_company_page) AS avg_following_company_page,
  AVG(montly_avg_comment_on_company_page) AS avg_montly_avg_comment_on_company_page,
  AVG(working_flag) AS avg_working_flag,
  AVG(travelling_network_rating) AS avg_travelling_network_rating,
  AVG(Adult_flag) AS avg_Adult_flag,
  AVG(Daily_Avg_mins_spend_on_traveling_page) AS avg_Daily_Avg_mins_spend_on_traveling_page
FROM customer_behaviour_tourism
WHERE Taken_product = 0

UNION ALL

SELECT 
  'ALL' AS taken_product_status,
  AVG(Yearly_avg_view_on_travel_page) AS avg_Yearly_avg_view_on_travel_page,
  AVG(total_likes_on_outstation_checkin_given) AS avg_total_likes_on_outstation_checkin_given,
  AVG(yearly_avg_Outstation_checkins) AS avg_yearly_avg_Outstation_checkins,
  AVG(member_in_family) AS avg_member_in_family,
  AVG(Yearly_avg_comment_on_travel_page) AS avg_Yearly_avg_comment_on_travel_page,
  AVG(total_likes_on_outofstation_checkin_received) AS avg_total_likes_on_outofstation_checkin_received,
  AVG(week_since_last_outstation_checkin) AS avg_week_since_last_outstation_checkin,
  AVG(following_company_page) AS avg_following_company_page,
  AVG(montly_avg_comment_on_company_page) AS avg_montly_avg_comment_on_company_page,
  AVG(working_flag) AS avg_working_flag,
  AVG(travelling_network_rating) AS avg_travelling_network_rating,
  AVG(Adult_flag) AS avg_Adult_flag,
  AVG(Daily_Avg_mins_spend_on_traveling_page) AS avg_Daily_Avg_mins_spend_on_traveling_page
FROM customer_behaviour_tourism;

 

- 여행 상품을 구매하지 않은 사람들의 여행 페이지 조회 횟수가 더 높다. 

- 여행 상품을 구매하지 않은 사람들의 지난 1년간 외부 지역 체크인에 대해 주는 총 좋아요 수가 더 높다. 

- 여행 상품을 구매하지 않은 사람들과 구매한 사람들의 여행 횟수 차이는 크지 않다. 

- 사용자의 평균적인 가족 구성원 수는 여행 상품 구매 여부에 따른 큰 차이가 없다. 

- 사용자가 연 평균 여행 관련 페이지에 남긴 댓글 수의 평균은 여행 상품 구매 여부에 따른 큰 차이가 없다. 

- 사용자가 지난 1년간 외부 지역 체크인에 대해 받은 총 좋아요A 수는 여행 상품을 구매하지 않은 사람들이 더 높다.  

- 사용자가 마지막 체크인 이후 경과한 주의 평균은 여행 상품 구매 여부에 따른 큰 차이가 없다. 

- 여행 상품을 구매한 사람들의 53%는 회사 페이지를 팔로우했지만, 여행 상품을 구매하지 않은 사람들은 23%만 회사 페이지를 팔로우하였다. 

- 사용자가 회사 페이지에 남긴 월 평균 댓글 수의 평균은 여행 상품 구매 여부에 따른 큰 차이가 없다. 

- 사용자의 근무 여부 또한 여행 상품 구매 여부에 따른 큰 차이가 없다. 

- 여행을 좋아하는 친구들과의 관계 또한 여행 상품 구매 여부에 따른 큰 차이가 없다. 

- 여행 상품을 구매한 사람들 중 35%가 성인이고, 여행 상품을 구매하지 않은 사람들 중 61%가 성인이다. 전체 평균적으로는 57%의 사용자가 성인이다. 

- 사용자가 해당 회사의 여행 페이지를 구경하는 시간은 여행 상품을 구매한 사람들이 더 적다. 

 

==> 6개 정도의 핵심 특징을 발견하였다. 

 

추가적인 분석을 해보자. 

-- 가족 수에 따른 상품 구매자와 비구매자
SELECT
  member_in_family,
  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
FROM customer_behaviour_tourism
GROUP BY member_in_family
ORDER BY member_in_family;

 

-- 전체 구매자 대비 가족 수에 따른 구매자의 비율
SELECT
  member_in_family,
  COUNT(CASE WHEN Taken_product = 1 THEN 1 END) AS count_buyers,
  COUNT(CASE WHEN Taken_product = 1 THEN 1 END) / (SELECT COUNT(*) FROM customer_behaviour_tourism WHERE Taken_product = 1) * 100 AS buyer_percentage
FROM customer_behaviour_tourism
WHERE Taken_product = 1
GROUP BY member_in_family
ORDER BY member_in_family;

-- 전체 비구매자 대비 가족 수에 따른 비구매자의 비율
SELECT
  member_in_family,
  COUNT(CASE WHEN Taken_product = 0 THEN 1 END) AS count_buyers,
  COUNT(CASE WHEN Taken_product = 0 THEN 1 END) / (SELECT COUNT(*) FROM customer_behaviour_tourism WHERE Taken_product = 0) * 100 AS buyer_percentage
FROM customer_behaviour_tourism
WHERE Taken_product = 0
GROUP BY member_in_family
ORDER BY member_in_family;

 

--> 가족이 3~4명인 사용자의 여행 상품 구매가 가장 많은 것을 확인할 수 있다.

그러나 이와 더불어 동일한 그룹에서의 비구매자 비율 또한 가장 높기에 

가족 구성원 수는 여행 상품 구매 여부에 큰 관련이 없다. 

 

 

-- 회사 페이지 팔로우 여부에 따른 구매자와 비구매자, 비율
SELECT
  following_company_page,
  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 following_company_page;

 

--> 회사 페이지 팔로우 여부에 따른 구매자와 비구매자 현황을 살펴본 결과, 

여행 상품 구매자 내에서는 회사 페이지 팔로우 여부가 크게 차이나지 않는다. 

그러나, 비구매자의 경우 회사 페이지를 팔로우 한 사람이 2033명, 회사 팔로우를 팔로우하지 않은 사람이 6728명으로, 

차이가 매우 큼을 알 수 있다. 

 

 

-- 여행 횟수(체크인 횟수)에 따른 구매자/비구매자
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번일 때, 상품을 구매한 사람과 구매하지 않은 사람 모두 가장 많다. 

따라서 여행상품을 구매한 사람들과 구매하지 않은 사람들의 차이는 크지 않다고 할 수 있다. 

 

-- 성인 여부에 따른 구매자와 비구매자, 비율
SELECT
  Adult_flag,
  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 Adult_flag;

 

--> 여행 상품을 구매한 사람들 중 절반 이상(64%)은 성인이 아니고, 

여행 상품을 구매하지 않은 사람들 중 절반 이상(61%)은 성인이다. 

--> 즉, 성인은 여행 상품을 잘 구매하지 않는다고 추측할 수 있다. 

 

==> 흔히 생각하는 고정관념과 다르기에 데이터의 불균형이 있는가 찾아보았다. 

-- 성인/비성인자 몇 명인가
SELECT
  Adult_flag,
  COUNT(*) AS count
FROM customer_behaviour_tourism
GROUP BY Adult_flag;

==> 데이터의 불균형은 딱히 없어보인다. 

그렇다면, 성인의 구매비율이 더 적은 이유는 2가지로 추측해볼 수 있다!

1. 성인은 미성년자에 비해 온라인 이용이 서툴러 온라인 여행 페이지에서 구매를 잘 하지 않은 것이다. 

2. 성인은 돈이 많기에 여행 상품보다는 자유여행을 선호해 상품 구매가 적은 것이다. 

 

상관관계 분석

-> 상관관계 분석을 진행해보았다. 모든 컬럼들의 여행 상품 구매와의 상관도가 크지는 않으며, 

그나마 회사 페이지 팔로우 여부, 성인여부 등의 컬럼의 상관도가 커보인다.