프로젝트

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

_알파카 2024. 3. 11. 17:28
728x90

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

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

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

 

Tourism Page Engagement

Explore User Patterns on Social Media Page : EDA & Classification

www.kaggle.com

 

 

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

총 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. 성인은 돈이 많기에 여행 상품보다는 자유여행을 선호해 상품 구매가 적은 것이다. 

 

상관관계 분석

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

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

728x90
저작자표시 비영리 변경금지 (새창열림)