이커머스 데이터 분석

Suhyeon Lee·2024년 10월 8일

과제 목표

SQL 기능을 다양하게 활용하여 EDA 해보기
- EDA(Exploratory Data Analysis; 탐색적 데이터 분석)
  - 수집한 데이터가 들어왔을 때, 이를 다양한 각도에서 관찰하고 이해하는 과정
SQL 활용 범위
데이터 추출 및 탐색
- SELECT
- DISTINCT
데이터 필터링 및 조건부 검색
- WHERE
- BETWEEN
- LIKE
- IN
- NOT
데이터 집계 및 그룹화
- GROUP BY
- HAVING
통계량 계산
- COUNT
- SUM
- AVG
- MIN
- MAX
데이터 변환
- JOIN
- UNION
데이터 정렬 및 분류
- ORDER BY
데이터 변형 및 계산
- CASE WHEN
- IF

주제 선택

이커머스 이벤트 히스토리
- 1개월(2020년 2월)의 행동 데이터
  - 거래가 이뤄지기 전에 소비자들이 보여주는 행동 패턴: 플랫폼에서 해당 상품을 확인하고 장바구니에 상품을 추가하거나 삭제하는 이벤트들이 반복적으로 이뤄지다가 최종적으로 구매를 하거나 안 함
  - 이러한 행동들을 분석하여 패턴을 확인하고 유의미한 분석을 진행

과제 진행

DB명: ecommerce
table명: feb
파일의 각 행은 이벤트를 나타냄
- 모든 이벤트는 제품 및 사용자와 관련이 있음
- 각 이벤트는 제품과 사용자 간의 다대다 관계와 같음
Event types
- view: 사용자가 제품을 보았습니다.
- cart: 사용자가 장바구니에 제품을 추가했습니다.
- remove_from_cart: 사용자가 장바구니에서 제품을 제거했습니다.
- purchase: 사용자가 제품을 구매했습니다.

Property	-	설명
event_time	이벤트 시간	이벤트가 발생한 시간(UTC)
event_type	이벤트 유형	이벤트의 유형은 총 4가지
product_id	제품_아이디	제품의 ID
category_id	카테고리_아이디	제품 카테고리 ID
category_code	카테고리_코드	제품의 카테고리 택소노미(코드명)는 가능하다면 만들 수 있습니다.
		일반적으로 의미 있는 카테고리에 존재하고 다양한 종류의 액세서리에는 건너뜁니다.
brand	상표	브랜드 이름의 소문자 문자열. 놓칠 수 있음.
price	가격	제품의 부동 가격. 현재.
user_id	사용자_아이디	영구 사용자 ID.
user_session	사용자 세션	임시 사용자의 세션 ID. 각 사용자 세션에 대해 동일합니다.
		사용자가 긴 일시 정지에서 온라인 스토어로 돌아올 때마다 변경됩니다.

데이터 전처리 및 조건 설정

price의 값이 음수인 경우가 있어서 음수, 0은 무시하고 데이터 분석
주의 기준은 국제표준화기구(ISO)의 규정에 따라 월요일을 한 주의 시작, 일요일을 한 주의 끝으로 설정
- 2월 첫째 주: 2020/02/03 - 2020/02/09
- 2월 둘째 주: 2020/02/10 - 2020/02/16
- 2월 셋째 주: 2020/02/17 - 2020/02/23
- 2월 넷째 주: 2020/02/24 - 2020/02/29
- 기타: 2020/02/01, 2020/02/02
금액은 소수점 첫째 자리에서 반올림

날짜별, 시간별

일별 판매금액

SELECT 
  evt.days
  , ROUND(SUM(price), 0) AS sales
FROM (
  SELECT 
	SUBSTR(event_time, 9, 2) AS days
	, event_type
	, user_id 
	, price
  FROM 
    ecommerce.feb
  WHERE 
    event_type = 'purchase'
    AND price > 0
  ) evt
GROUP BY 
  evt.days
;

일별 판매량

SELECT 
  evt.days
  , count(*) sales
FROM (
  SELECT 
	SUBSTR(event_time, 9, 2) AS days
  FROM 
    ecommerce.feb
  WHERE 
    event_type = 'purchase'
    AND price > 0
  ) evt
GROUP BY 
  evt.days
;

주별 판매금액

SELECT 
  CASE
    WHEN evt.days < 3 THEN 'etc.'
	WHEN evt.days < 10 THEN '1st'
  	WHEN evt.days < 17 THEN '2nd'
  	WHEN evt.days < 24 THEN '3rd'
  	ELSE '4th'
  END AS weeknum
  , ROUND(SUM(price), 0) sales
FROM (
  SELECT 
	SUBSTR(event_time, 9, 2) AS days
	, event_type
	, user_id 
	, price
  FROM 
    ecommerce.feb
  WHERE 
    price > 0
  ) evt
WHERE 
  evt.event_type = 'purchase'
GROUP BY 
  `weeknum`
;

주별 판매량

SELECT 
  CASE
    WHEN evt.days < 3 THEN 'etc.'
	WHEN evt.days < 10 THEN '1st'
  	WHEN evt.days < 17 THEN '2nd'
  	WHEN evt.days < 24 THEN '3rd'
  	ELSE '4th'
  END AS week_num
  , COUNT(*) sales_num
FROM (
  SELECT 
	SUBSTR(event_time, 9, 2) AS days
	, event_type
	, user_id 
	, price
  FROM 
    ecommerce.feb
  WHERE 
    price > 0
  ) evt
WHERE 
  evt.event_type = 'purchase'
GROUP BY 
  `week_num`
;

주별 상품을 본 횟수

SELECT 
  CASE
    WHEN evt.days < 3 THEN 'etc.'
	WHEN evt.days < 10 THEN '1st'
  	WHEN evt.days < 17 THEN '2nd'
  	WHEN evt.days < 24 THEN '3rd'
  	ELSE '4th'
  END AS week_num
  , COUNT(*) view_num
FROM (
  SELECT 
	SUBSTR(event_time, 9, 2) AS days
	, event_type
	, user_id 
	, price
  FROM 
    ecommerce.feb
  WHERE 
    price > 0
  ) evt
WHERE 
  evt.event_type = 'view'
GROUP BY 
  `week_num`
;

주별 카트에 제품을 추가한 이벤트 발생 수

SELECT 
  CASE
    WHEN evt.days < 3 THEN 'etc.'
	WHEN evt.days < 10 THEN '1st'
  	WHEN evt.days < 17 THEN '2nd'
  	WHEN evt.days < 24 THEN '3rd'
  	ELSE '4th'
  END AS week_num
  , COUNT(*) cart_num
FROM (
  SELECT 
	SUBSTR(event_time, 9, 2) AS days
	, event_type
	, user_id 
	, price
  FROM 
    ecommerce.feb
  WHERE 
    price > 0
  ) evt
WHERE 
  evt.event_type = 'cart'
GROUP BY 
  `week_num`
;

주별 카트에서 제품 제거 이벤트 발생 수

SELECT 
  CASE
    WHEN evt.days < 3 THEN 'etc.'
	WHEN evt.days < 10 THEN '1st'
  	WHEN evt.days < 17 THEN '2nd'
  	WHEN evt.days < 24 THEN '3rd'
  	ELSE '4th'
  END AS week_num
  , COUNT(*) remove_num
FROM (
  SELECT 
	SUBSTR(event_time, 9, 2) AS days
	, event_type
	, user_id 
	, price
  FROM 
    ecommerce.feb
  WHERE 
    price > 0
  ) evt
WHERE 
  evt.event_type = 'remove_from_cart'
GROUP BY 
  `week_num`
;

주별, 이벤트별 발생 횟수

SELECT 
  evt.week_num
  , MAX(IF(event_type = 'view', cnt, 0)) AS 'view'
  , MAX(IF(event_type = 'cart', cnt, 0)) AS 'cart'
  , MAX(IF(event_type = 'remove_from_cart', cnt, 0)) AS 'remove_from_cart'
  , MAX(IF(event_type = 'purchase', cnt, 0)) AS 'purchase'
FROM (
  SELECT 
    CASE
      WHEN SUBSTR(event_time, 9, 2) < 3 THEN 'etc.'
      WHEN SUBSTR(event_time, 9, 2) < 10 THEN '1st'
  	  WHEN SUBSTR(event_time, 9, 2) < 17 THEN '2nd'
  	  WHEN SUBSTR(event_time, 9, 2) < 24 THEN '3rd'
  	  ELSE '4th'
    END AS week_num
    , event_type
    , COUNT(*) cnt
  FROM
    ecommerce.feb
  WHERE 
    price > 0
  GROUP BY 
    1
    , 2
  ) evt
GROUP BY 
  1
;

일별, 시간별 제품 구매 이벤트 발생 횟수

WITH RECURSIVE temp AS (
  (SELECT
    0 AS hours
  )
  UNION
  (SELECT
    hours + 1
  FROM
    temp
  WHERE
    hours < 23
  )
)
SELECT
  pt.days
  , MAX(IF(pt.hh = 0, cnt, 0)) AS AM00
  , MAX(IF(pt.hh = 1, cnt, 0)) AS AM01
  , MAX(IF(pt.hh = 2, cnt, 0)) AS AM02
  , MAX(IF(pt.hh = 3, cnt, 0)) AS AM03
  , MAX(IF(pt.hh = 4, cnt, 0)) AS AM04
  , MAX(IF(pt.hh = 5, cnt, 0)) AS AM05
  , MAX(IF(pt.hh = 6, cnt, 0)) AS AM06
  , MAX(IF(pt.hh = 7, cnt, 0)) AS AM07
  , MAX(IF(pt.hh = 8, cnt, 0)) AS AM08
  , MAX(IF(pt.hh = 9, cnt, 0)) AS AM09
  , MAX(IF(pt.hh = 10, cnt, 0)) AS AM10
  , MAX(IF(pt.hh = 11, cnt, 0)) AS AM11
  , MAX(IF(pt.hh = 12, cnt, 0)) AS PM12
  , MAX(IF(pt.hh = 13, cnt, 0)) AS PM01
  , MAX(IF(pt.hh = 14, cnt, 0)) AS PM02
  , MAX(IF(pt.hh = 15, cnt, 0)) AS PM03
  , MAX(IF(pt.hh = 16, cnt, 0)) AS PM04
  , MAX(IF(pt.hh = 17, cnt, 0)) AS PM05
  , MAX(IF(pt.hh = 18, cnt, 0)) AS PM06
  , MAX(IF(pt.hh = 19, cnt, 0)) AS PM07
  , MAX(IF(pt.hh = 20, cnt, 0)) AS PM08
  , MAX(IF(pt.hh = 21, cnt, 0)) AS PM09
  , MAX(IF(pt.hh = 22, cnt, 0)) AS PM10
  , MAX(IF(pt.hh = 23, cnt, 0)) AS PM11
FROM (
  SELECT 
    SUBSTR(event_time, 1, 10) AS days
    , SUBSTR(event_time, 12, 2) AS hh
    , COUNT(*) AS cnt
  FROM 
    ecommerce.feb
  WHERE 
    event_type = 'purchase'
    AND price > 0
  GROUP BY
    1
    , 2
  ) AS pt 
  JOIN temp ON pt.hh = temp.hours
GROUP BY
  pt.days
;

일별, 이벤트별 발생 횟수

SELECT
  pt.days
  , MAX(IF(event_type = 'view', cnt, 0)) AS view
  , MAX(IF(event_type = 'cart', cnt, 0)) AS cart
  , MAX(IF(event_type= 'remove_from_cart', cnt, 0)) AS remove_from_cart
  , MAX(IF(event_type = 'purchase', cnt, 0)) AS purchase
FROM (
  SELECT 
    SUBSTR(event_time, 1, 10) AS days
    , event_type
    , COUNT(*) AS cnt
  FROM 
    ecommerce.feb
  WHERE 
    price > 0
  GROUP BY
    1
    , 2
  ) AS pt
GROUP BY
  pt.days
;

시간별, 이벤트별 발생 횟수

SELECT
  pt.hh
  , MAX(IF(event_type = 'view', cnt, 0)) AS view
  , MAX(IF(event_type = 'cart', cnt, 0)) AS cart
  , MAX(IF(event_type= 'remove_from_cart', cnt, 0)) AS remove_from_cart
  , MAX(IF(event_type = 'purchase', cnt, 0)) AS purchase
FROM (
  SELECT 
    SUBSTR(event_time, 12, 2) AS hh
    , event_type
    , COUNT(*) AS cnt
  FROM 
    ecommerce.feb
  WHERE 
    price > 0
  GROUP BY
    1
    , 2
  ) AS pt
GROUP BY
  pt.hh
;

요일별 이벤트 발생 횟수 및 총 판매 금액

SELECT 
  evt.day
  , MAX(IF(event_type = 'view', cnt, 0)) AS 'view'
  , MAX(IF(event_type = 'cart', cnt, 0)) AS 'cart'
  , MAX(IF(event_type = 'remove_from_cart', cnt, 0)) AS 'remove_from_cart'
  , MAX(IF(event_type = 'purchase', cnt, 0)) AS 'purchase'
  , MAX(IF(event_type = 'purchase', sales, 0)) AS 'sales'
FROM (
  SELECT 
    CASE
      WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 3 THEN 2
      WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 4 THEN 3
  	  WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 5 THEN 4
  	  WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 6 THEN 5
      WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 0 THEN 6
  	  WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 1 THEN 7
  	  WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 2 THEN 1
    END AS day
    , event_type
    , COUNT(*) cnt
    , ROUND(SUM(price), 0) AS sales
  FROM
    ecommerce.feb
  WHERE 
    price > 0
  GROUP BY 
    1
    , 2
  ) evt
GROUP BY 
  1
;

참고:

고객 수 관련

온라인 스토어 가입 고객 수

SELECT 
  COUNT(DISTINCT user_id) AS total_usercnt
FROM 
  ecommerce.feb 
;

한 번이라도 구매한 적 있는 고객 수

SELECT 
  COUNT(*) AS purchase_user
FROM (
  SELECT
    DISTINCT user_id
  FROM 
    ecommerce.feb 
  WHERE
    event_type = 'purchase'
  ) cnt
;

활동이 많은 고객 순 정렬

SELECT 
  user_id,
  COUNT(*) AS in_out
FROM 
  ecommerce.feb 
GROUP BY
  user_id 
ORDER BY 
  COUNT(*) DESC
;

구매를 많이 한 고객 순 정렬

SELECT 
  user_id,
  COUNT(*) AS in_out
FROM 
  ecommerce.feb 
WHERE 
  event_type = 'purchase'
GROUP BY
  user_id 
ORDER BY 
  COUNT(*) DESC
;

가장 구매를 많이 한 고객의 활동 분석

SELECT 
  *
FROM 
  ecommerce.feb 
WHERE 
  user_id = 601469771
ORDER BY 
  event_time
;

제품 관련

온라인 스토어에서 판매하는 제품 중 고객들이 한 번이라도 확인한 제품의 개수

SELECT 
  COUNT(DISTINCT product_id) AS product_cnt 
FROM 
  ecommerce.feb 
;

한 번이라도 판매된 적 있는 제품의 수

SELECT 
  COUNT(DISTINCT product_id) AS product_cnt 
FROM 
  ecommerce.feb 
WHERE 
  event_type = 'purchase'
;

한 번이라도 판매된 적 있는 제품의 카테고리 수 확인

제품 카테고리 별 판매량

SELECT 
  category_id
  , COUNT(*) AS cnt 
FROM 
  ecommerce.feb 
WHERE 
  event_type = 'purchase'
GROUP BY 
  category_id
ORDER BY
  category_id
;

Suhyeon Lee

2 B R 0 2 B

이전 포스트

3주차

다음 포스트

이커머스 데이터 분석

과제 목표

SQL 활용 범위

주제 선택

과제 진행

데이터 전처리 및 조건 설정

날짜별, 시간별

일별 판매금액

일별 판매량

주별 판매금액

주별 판매량

주별 상품을 본 횟수

주별 카트에 제품을 추가한 이벤트 발생 수

주별 카트에서 제품 제거 이벤트 발생 수

주별, 이벤트별 발생 횟수

일별, 시간별 제품 구매 이벤트 발생 횟수

일별, 이벤트별 발생 횟수

시간별, 이벤트별 발생 횟수

요일별 이벤트 발생 횟수 및 총 판매 금액

고객 수 관련

온라인 스토어 가입 고객 수

한 번이라도 구매한 적 있는 고객 수

활동이 많은 고객 순 정렬

구매를 많이 한 고객 순 정렬

가장 구매를 많이 한 고객의 활동 분석

제품 관련

온라인 스토어에서 판매하는 제품 중 고객들이 한 번이라도 확인한 제품의 개수

한 번이라도 판매된 적 있는 제품의 수

한 번이라도 판매된 적 있는 제품의 카테고리 수 확인

제품 카테고리 별 판매량

3주차

6일차

0개의 댓글