E-Commerce 데이터를 이용한 리포트 작성

이번 글에서는 전자상거래 데이터를 분석해보는 실습을 진행해보자.

이전 글과 이어지는 내용이니, 데이터 준비를 위해 아래 글을 참조하기 바란다.

SQL - MySQL 데이터 준비

 

 

 

 

 

0. 사용 Schema 지정

USE mydata;

• mydata 스키마를 사용

 

 

 

 

 

1. 장바구니 분석

특정 상품 구매자가 많이 구매한 상품에 대해 분석해보자.

먼저 가장 많이 판매된 2개 상품을 조회해보자.

SELECT *
FROM (
	SELECT
		*
		, ROW_NUMBER() OVER(ORDER BY QTY DESC) AS RNK
	FROM (
		SELECT
			stockcode
			, SUM(quantity) AS QTY
		FROM dataset3
		GROUP BY 1
	) B
) A
WHERE RNK <= 2
;

• 모든 컬럼을 선택
• 서브 쿼리 A에서 데이터를 가져옴
  • 서브쿼리 B에서 모든 컬럼을 선택
  • QTY 컬럼을 내림차순으로 정렬하고 순위를 할당 이후 RNK 컬럼으로 표시
  • 서브 쿼리 B에서 데이터를 가져옴
    • stockcode 컬럼 선택
    • quantity의 합계를 구하고 QTY 컬럼으로 표시
    • dataset3 테이블에서 데이터를 가져옴
    • stockcode를 기준으로 그룹핑
• RNK 컬럼의 값이 2이하인 행만 최종 선택

 

 

다음으로 가장 많이 판매된 2개 상품(84077, 85123A)을 모두 구매한 고객이 구매한 상품들을 살펴보자.

먼저 서브 쿼리를 위한 테이블을 생성해준다.

CREATE TABLE bu_list AS
SELECT
	customerid
FROM dataset3
GROUP BY 1
HAVING MAX(CASE WHEN stockcode = '84077' THEN 1 ELSE 0 END) = 1 -- 1은 TRUE를 의미
	AND MAX(CASE WHEN stockcode = '85123A' THEN 1 ELSE 0 END) = 1 -- 1은 TRUE를 의미
;

• bu_list 이름으로 새로운 테이블을 생성 데이터는 아래 쿼리의 결과로 채워짐
• customerid 컬럼 선택
• dataset3 테이블에서 데이터를 가져옴
• customerid 기준으로 그룹핑
• 그룹핑된 테이블에서 HAVING절을 사용해 필터링
  • stockcode가 84077인 경우를 찾고 존재하면 1 없으면 0
  • stockcode가 85123A인 인 경우를 찾고 존재하면 1 없으면 0 위의 결과와 AND로 조합
• 최종적으로 84077, 85123A 둘다 존재하는 행만 선택됨

 

 

그리고 가장 많이 판매된 2개 상품을 모두 구매한 고객이 구매한 상품들을 구하는 쿼리는 다음과 같다.

SELECT customerid, stockcode, quantity
FROM dataset3
WHERE customerid IN(SELECT customerid FROM bu_list)
	AND stockcode NOT IN ('84077', '85123A')
;

• customerid, stockcode, quantity 컬럼을 선택
• dataset3 테이블에서 데이터를 가져옴
• WHERE + IN을 사용하여 customerid 컬럼을 조건에 따라 필터링
  • 아까 만든 bu_list 테이블에 있는 customerid로 제한
  • 그리고 가장 많이 판매된 2개 상품인 '84077', '85123A' 제품 이외의 다른 품목들로 제한

 

 

 

 

 

2. 코호트 분석 (Cohort Analysis)

코호트 분석은 시간 흐름에 따라 사용자의 리텐션, 구매 패턴, 행동 패턴 등 고객 세분화를 하는 것을 의미한다.

첫 구매월을 기준으로 각 그룹간의 패턴을 파악해보자

SELECT
    SUBSTR(MNDT, 1, 7) MM
    , TIMESTAMPDIFF(month, mndt, invoicedate) DATEDIFF
    , COUNT(DISTINCT A.customerid) BU
    , SUM(sales) sales
FROM (
	SELECT
		customerid
		, MIN(invoicedate) MNDT -- 최초 구매일, 마지막 구매일은 MAX(invoicedate) MXDT
	FROM dataset3
	GROUP BY 1
) A
LEFT
JOIN (SELECT 
		customerid
        , invoicedate
        , unitprice * quantity SALES
		FROM dataset3
        ) B
ON A.customerid = B.customerid
GROUP BY 1, 2
;

• SUBSTR()으로 MNDT에서 1~7번째 글자만 가져와(YYYY-MM) MM 컬럼으로 표시
• TIMESTAMPDIFF()을 사용해 월 기준으로, mndt와 invoicedate가 몇 개월 차이가 나는지 계산하고 DATEDIFF 컬럼으로 표시
• COUNT()를 사용해 중복없이 A.customerid를 카운트하고 BU 컬럼으로 표시
• SUM()으로 판매액(sales)의 총합계를 sales 컬럼으로 표시
• 서브쿼리 A에서 데이터를 가져옴
  • customerid 컬럼을 표시
  • MIN()을 사용해 invoicedate의 최소값을 구해 최초 구매일을 구하고 MNDT 컬럼으로 표시
  • dataset3 테이블에서 데이터를 가져옴
  • GROUP BY 1을 통해 customerid 기준으로 그룹핑
• 서브쿼리 B와 LEFT JOIN
  • customerid 컬럼을 표시
  • invoicedate 컬럼을 표시
  • unitprice * quantity로 판매액을 계산하고 SALES 컬럼으로 표시
  • dataset3 테이블에서 데이터를 가져옴
• customerid 기준으로 서브쿼리 A와 서브쿼리 B 테이블 결합
• GROUP BY 1, 2를 통해 구매월(MM)과 재구매까지의 기간(DATEDIFF)으로 그룹핑

 

 

 

 

 

3. 고객 세그먼트

서비스를 이용하는 고객의 특성을 분류할 수 있다면, 서비스 사용자에 대한 이해를 높이고, 타겟 마케팅 같은 개인화된 혜택을 제공할 수 있다. 그러한 목적으로 고객의 특성을 분류하는 것을 고객 세그먼트라고 한다.

그 중에서 서비스에서 높은 가치를 가진 고객을 구분하기 위해 자주 사용되는 모델인 RFM에 대해 알아보자

※ RFM

R(Recency): 가장 최근에 구입한 시기는 언제인가?

F(Frequency): 어느 정도로 자주 구입하는가?

M(Monetary): 구입한 총 금액을 얼마인가?

2011년 12월 02일 기준으로 고객의 RFM을 계산하는 쿼리이다.

SELECT 
	CustomerID
    , datediff('2011-12-02', mxdt) Recency
    , Frequency
    , Monetary
FROM (
	SELECT 
		customerid
		, MAX(invoiceDate) MXDT 
		, COUNT(DISTINCT invoiceno) AS Frequency
		, SUM(quantity * unitprice) AS Monetary 
	FROM dataset3
	GROUP BY 1
) A
;

• CustomerID 컬럼을 표시
• datediff()를 사용하여 '2011-12-02'와 최근주문일(mxdt)와의 날짜 차이를 구하고 Recency 컬럼으로 표시
• Frequency 컬럼을 표시
• Monetary 컬럼을 표시
• 서브쿼리 A에서 데이터를 가져옴
  • customerid 컬럼을 표시
  • MAX()로 invoiceDate의 최대값을 구하여 최근 구매일을 구하고 MXDT 컬럼으로 표시
  • COUNT()로 중복없이 invoiceno를 카운트하고 Frequency 컬럼으로 표시
  • SUM()으로 구매금액(quantity * unitprice)의 합계를 구하고 Monetary 컬럼으로 표시
  • dataset3 테이블에서 데이터를 가져옴
  • GROUP BY 1로 customerid 기준으로 그룹핑

 

 

 

 

 

4. 이탈률 (Bounce Rate)

첫 구매 후 이탈하는 고객의 비중은 얼마나 되는 지 알아보자

현재 주어진 데이터셋으로는 고객별로 구매일자를 중복없이 카운트하였을 때, 그 값이 1이되면 재구매가 없다는 것으로 접근 가능하다

다음은 국가별로 고객들의 첫 구매후 평균 이탈률을 구하는 쿼리이다

SELECT 
	country
    , SUM(CASE WHEN F_DATE = 1 THEN 1 ELSE 0 END) / SUM(1) AS BOUNC_RATE
FROM (
	SELECT
		customerid
		, country
		, COUNT(DISTINCT invoicedate) AS F_DATE
	FROM dataset3
	GROUP BY 1, 2
) A
GROUP BY 1
ORDER BY 1
;

• country 컬럼 표시
• “SUM(CASE WHEN F_DATE = 1 THEN 1 ELSE 0 END) / SUM(1)”으로 전체 중 총 주문횟수(F_DATE)가 1인 비율 계산하여 이탈률을 구하고 BOUNC_RATE 컬럼으로 표시
• 서브쿼리 A에서 데이터를 가져옴
  • customerid 컬럼 표시
  • country 컬럼 표시
  • COUNT()로 중복없이 invoicedate를 카운트하고 F_DATE 컬럼으로 표시
  • dataset3 테이블에서 데이터를 가져옴
  • GROUP BY 1, 2로 customerid와 country 기준으로 그룹핑
• GROUP BY 1로 country기준으로 그룹핑 (BOUNC_RATE 컬럼이 추가되었으므로 다시하는 것임)
• ORDER BY 1로 country기준으로 정렬