[Kaggle/MySQL] E-Commerce 데이터분석1️⃣ Actual transactions from UK retailer

E-Commerce Data

 

데이터 생성

• 국가별 상품별 구매 지표 추출
• 특정 상품 구매자가 구매한 다른 상품은?
• 국가별 재구매율 계산
• 코호트 분석
• 고객 Segment

우리가 분석할 UK Commerce 데이터 세트는 다음과 같이 구성되어 있다.

• IncoiceNo: 주문 번호
• StockCode: 상품 번호
• Description: 상품명
• Quantity: 구매 상품 수
• UnitPrice: 개당 판매 가격
• CustomerID: 고객 번호
• Country: 판매 국가
• InvoiceDate:판매 일자

---

국가별, 상품별 구매자 수 및 매출액

 

먼저 국가별, 상품별로 구매자수, 매출액을 계산해 본다. 국가별, 상품별로 지표를 계산하려면 해당 변수로 그룹핑한 뒤 각 변수를 집계하면 된다.

 

먼저 COUNTRY, STOCKCODE로 데이터를 그룹핑하고, 고객 번호(CUSTOMERID)를 카운트, 구매 상품 수(QUANTITY) * 개당 가격(UNITPRICE)의 곱을 합하면 된다.

 

여기서 유의할 점은 고객 번호가 중복 없는 데이터인지 확인하는 것이다. 앞에서도 살펴보았듯이, 고객 번호에 중복이 존재하는데 단순히 카운트로 집계하면 구매자 수가 중복으로 집계된다.(그래서 에러가 난 걸까.. )

* DISTINCT를 사용해 중복 제거하기

SELECT COUNTRY,
STOCKCODE,
COUNT(DISTINCT CUSTOMERID) BU,
SUM(QUANTITY*UNITPRICE) SALES
FROM MYDATA.DATASET3
GROUP
BY 1,2
ORDER
BY 3 DESC,4 DESC;

[쿼리 실행 결과]

 

특정 상품 구매자가 많이 구매한 상품은?

'맥주를 구매한 사람은 기저귀를 구매한다.'와 같은 얘기를 들어 본 적이 있다. 장바구니 분석의 대표적인 예다.

장바구니 분석의 핵심은 고객의 구매 내역을 보고 상품과 상품 사이에 상관관계가 있는지 살펴보는 것이다. 맥주를 구매할 경우, 기저귀를 구매하는 경향이 있는지 아니면 기저귀를 구매하지 않은 경향이 있는지와 같은 연관성을 파악할 수 있다.

 

1) 가장 많이 판매된 2개 상품 조회(판매 상품 수 기준)

SELECT STOCKCODE,
SUM(QUANTITY) QTY
FROM MYDATA.DATASET3
GROUP
BY 1
;

[쿼리 실행 결과]

 

다음으로 판매된 상품 수(QTY)를 기준으로 랭크(RNK)를 생성한다.가장 많이 판매된 2개의 상품을 조회하는 것이 목적이므로 내림차순으로 순위를 매긴다. 위의 쿼리를 SUBQUERY로 만들어 랭크를 생성해 보자.

SELECT *,
ROW_NUMBER() OVER(ORDER BY QTY DESC) RNK
FROM
(SELECT STOCKCODE,
SUM(QUANTITY) QTY
FROM MYDATA.DATASET3
GROUP
BY 1) A
;

[쿼리 실행 결과]

 

이제 해당 테이블에서 랭크(RNK)가 1, 2인 데이터를 조회하면 된다.

위의 쿼리를 서브 쿼리로 다시 생성해 WHERE 절을 생성하고 조건을 만들어 보자.

SELECT STOCKCODE
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY QTY DESC) RNK
FROM
(SELECT STOCKCODE,
SUM(QUANTITY) QTY
FROM MYDATA.DATASET3
GROUP
BY 1) A) A
WHERE RNK BETWEEN 1 AND 2
;

[쿼리 실행 결과]

 

2) 가장 많이 판매된 2개 상품을 모두 구매한 구매자가 구매한 상품

이제 2개 상품을 모두 구매한 고객이 구매한 상품별 주문 건수를 계산해보자.

 

가장 많이 판매된 상품 코드는 84077, 85123A 

 

먼저 고객별로 각각의 상품을 구매했다면 1, 그렇지 않으면 0이 출력되도록 쿼리를 작성하고, 각 상품을 모두 구매한 경우만 출력되도록 HAVING을 통해 조건을 생성해 보자.

 

#구매자 리스트 생성

CREATE TABLE MYDATA.BU_LIST AS
SELECT CUSTOMERID
FROM MYDATA.DATASET3
GROUP
BY 1
HAVING MAX(CASE WHEN STOCKCODE = '84077' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN STOCKCODE = '85123A' THEN 1 ELSE 0 END) = 1 
;

SELECT DISTINCT STOCKCODE
FROM MYDATA.DATASET3
WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM MYDATA.BU_LIST)
AND STOCKCODE NOT IN ('84077', '85123A')
;

[쿼리 실행 결과]

 

국가별 재구매율 계산

 

A라는 고객은 2024년에 구매했고, 2023년에도 구매했다고 가정하자.

B 고객은 2024년에만 구매했고, C는 2024년, 2022년에 각각 구매했다.

A는 23년에서 24년으로 구매가 연장되어 재구매했다고 볼 수 있다. 반면에 C는 22년에 구매했지만 23년에는 구매하지 않아 24년에 재구매했다고 볼 수 없다. 이런 경우에는 연도를 각각 1씩 차감한 뒤 Join 하면 해당 연도에 구매한 경우에는 데이터가 결합하고, 그렇지 않은 경우는 결합하지 않는다.

SELECT A.COUNTRY,
SUBSTR(A.INVOICEDATE,1,4) YY,
COUNT(DISTINCT B.CUSTOMERID)/COUNT(DISTINCT A.CUSTOMERID) RETENTION_RATE
FROM (SELECT DISTINCT COUNTRY,
INVOICEDATE,
CUSTOMERID
FROM MYDATA.DATASET3) A
LEFT
JOIN (SELECT DISTINCT COUNTRY,
INVOICEDATE,
CUSTOMERID
FROM MYDATA.DATASET3) B
ON SUBSTR(A.INVOICEDATE,1,4) = SUBSTR(B.INVOICEDATE,1,4) -1
AND A.COUNTRY = B.COUNTRY
AND A.CUSTOMERID = B.CUSTOMERID
GROUP
BY 1,2
ORDER
BY 1,2;

[쿼리 실행 결과]

 

코호트 분석

• 코호트 분석이란?

코호트 분석이란 주로 시간 흐름에 따라 사용자의 리텐션, 구매 패턴, 행동 패턴을 파악하는 데 사용되는 분석을 일컫는다.

코호트 분석은 보통 첫 구매 월, 가입 월, 구매 월 기준으로 시간의 흐름에 따라 변화를 살펴본다. 물론 다른 기준으로 코호트 분석을 사용할 수도 있다^^

우리는 첫 구매 월을 기준으로 각 그룹 간의 패턴을 파악해 볼 거다. 먼저! 고객별로 첫 구매일을 구해보자.

SELECT CUSTOMERID,
MIN(INVOICEDATE) MNDT
FROM MYDATA.DATASET3
GROUP
BY 1
;

[쿼리 실행 결과]

고객별 첫 구매일을 구한 다음, 각 고객의 주문 일자, 구매액을 조회한다.

SELECT CUSTOMERID,
INVOICEDATE,
UNITPRICE*QUANTITY SALES
FROM MYDATA.DATASET3
;

[쿼리 실행 결과]

첫 번째로 구매했던 고객별 첫 구매일 테이블에 고객의 구매 내역을 Join 한다.

SELECT *
FROM
(SELECT CUSTOMERID,
MIN(INVOICEDATE) MNDT
FROM MYDATA.DATASET3
GROUP
BY 1) A
LEFT
JOIN
(SELECT CUSTOMERID,
INVOICEDATE,
UNITPRICE*QUANTITY SALES
FROM MYDATA.DATASET3) B
ON A.CUSTOMERID = B.CUSTOMERID
;

[쿼리 실행 결과]

MNDT는 각 고객의 최초 구매 월을 의미하고, DATADIFF는 첫 구매 이후 몇 개월뒤에 구매가 이루어졌는지를 뜻하는 기간을 의미한다. SALES는 해당 기간에 구매한 총 매출액이 된다. 

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
FROM MYDATA.DATASET3
GROUP
BY 1) A
LEFT
JOIN
(SELECT CUSTOMERID,
INVOICEDATE,
UNITPRICE*QUANTITY SALES
FROM MYDATA.DATASET3) B
ON A.CUSTOMERID = B.CUSTOMERID
group
by 1,2
;

[쿼리 실행 결과]

먼저 최초 구매일은 SUBSTR() 함수를 이용해 '연도-월'까지만 데이터를 가져온다. 이후 최초 구매일과 각 구매일 사이의 간격은 TIMESTAMPDIFF() 함수를 이용할 수 있다.

 

Syntax

TIMESTAMPDIFF(MONTH/DAY, START, END)

 

💭다음 시간에는 고객 세그먼트를 직접 나눠보고 첫 구매 고객 수 관련해서 데이터를 추출해 보자,,, 총총