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

본 글은 SQL을 공부하기 위해

<SQL로 맛보는 데이터 전처리 분석> 속 예제를 연습하고 기록한 글이다.

매일 배운 내용을 잘 기록해서 제대로 소화하는 게 이 글의 최종 목표이다.

막간 도서 소개

출처 YES 24 (노수영 저)

7장

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

1. 데이터 생성하기

https://www.kaggle.com/datasets/carrie1/ecommerce-data

이번에 분석할 데이터는 UK Commerce 회사의 데이터를 분석해볼 것이다.

해당 데이터 세트는 위의 URL로 들어가 Clone or Download를 클릭해 데이터를 다운 받을 수 있다.

다운로드한 파일은 MySQL Workbench에서 실행한다.

2. 데이터 구조 이해하기

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

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

-- 상품별 판매수량 구하기
SELECT StockCode
, SUM(Quantity) SALE
FROM dataset3
GROUP BY 1;



-- 판매수량에 따라 순위 열 추가하기, 2순위까지 추출
SELECT StockCode
FROM (
SELECT *
, ROW_NUMBER() OVER(ORDER BY SALE DESC) RNK
FROM (
SELECT StockCode
, SUM(Quantity) SALE
FROM dataset3
GROUP BY 1
) A ) A
WHERE RNK <= 2;

특정 상품 구매자가 많이 구매한 상품 정보를 구하기 위해서는

우선 상품별 판매 수량을 카운트하고,

해당 판매 수량을 기준으로 순위를 생성한다.

가장 많이 팔린 2개의 상품을 구하기 위해 WHERE 절에 조건을 추가한다.

위에서는 가장 많이 팔린 2개 상품을 구했고,

이제 2개 상품을 모두 구매한 고객이 구매한 상품별 주문건수를 구하려고 한다.

우선 고객별로 각각의 상품을 모두 구매했다면 1,

아니라면 0이 출력되도록 CASE WHEN 구문을 작성한다.

HAVING 절에는 가장 많이 판매된 상품번호인 84077, 85123A를 넣어 조건을 생성한다.

-- 가장 많이 판매된 2개 상품을 모두 구매한 구매자가 구매한 상품
SELECT *
FROM dataset3
ORDER BY stockcode, customerID;

-- 두개의 상품을 모두 구매한 customerID
SELECT customerID
FROM dataset3
GROUP BY 1
HAVING MAX(CASE WHEN stockcode = '84077' THEN 1 ELSE 0 END) = 1 -- True
AND MAX(CASE WHEN stockcode = '85123A' THEN 1 ELSE 0 END) = 1 -- True, 두 개가 동시에 True 인것을 만족

위의 쿼리를 새로운 테이블로 만든 뒤,

해당 고객이 구매한 상품을 출력할 수 있도록 IN 을 사용해

고객들이 구매한 상품 번호를 조회한다.

CREATE TABLE mydata.BU_LIST AS
SELECT customerID
FROM dataset3
GROUP BY 1
HAVING MAX(CASE WHEN stockcode = '84077' THEN 1 ELSE 0 END) = 1 -- True
AND MAX(CASE WHEN stockcode = '85123A' THEN 1 ELSE 0 END) = 1 -- True, 두 개가 동시에 True 인것을 만족
;

-- 2개의 상품을 구매한 구매자가 어떤 상품을 구매했는지 구하기
SELECT customerID, stockcode, quantity
FROM dataset3
WHERE customerID IN (SELECT customerID FROM BU_LIST)
AND stockcode NOT IN ('84077', '85123A');

4. 국가별 재구매율 계산

우선 국가별 재구매율을 계산하기 위해

국가별 고객별 상품 구매일 정보를 조회한다.

SELECT DISTINCT country
, invoicedate
        , customerID
FROM dataset3;

재구매라는 건 A라는 고객이 2018년에 구매, 2019년에도 구매한 경우 해당되는 것,

한편 2017년에 구매했지만 2018년에는 구매하지 않은 경우는 2018년에 재구매했다고 볼 수 없다.

이 방법에 착안해 위의 테이블에서

invoicedate에서 1을 차감한 뒤, customerid, invoicedate, country로 조인하면

해당 연도에 구매한 경우에는 데이터가 결합하고,

그렇지 않은 경우는 결합하지 않는다.

결합하면 현재 연도, 작년 연도(현재 연도 -1)별 customerID가 나오고

각각 연도별로 customerID를 카운트하면 현재 연도에 구매한 구매자 수와 작년에 구매한 구매자 수를 계산할 수 있다.

현재 연도의 구매자 수를 작년 연도의 구매자 수로 나누어서 RET_RATE열을 추가하였다.

SELECT A.country
, SUBSTR(A.invoicedate, 1, 4) as YY
        , COUNT(DISTINCT B.customerID) / COUNT(DISTINCT A.customerID) AS RET_RATE

FROM (
SELECT DISTINCT country
, invoicedate
        , customerID
FROM dataset3
) A
LEFT JOIN (SELECT DISTINCT country, invoicedate, customerID FROM 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;

5. 코호트 분석

이제는코호트 분석을 통해 특정 기간에 구매한 또는 가입한 고객들의 이후 구매액 및 리텐션을 살펴보겠다.

*코호트 분석이란?

출처 : https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/

주로 시간 흐름에 따라 사용자의 리텐션, 구매 패턴, 행동패턴을

파악하는 데 사용되는 분석을 일컫는다.

코호트 분석은 보통 첫 구매 월, 가입 월, 구매 월을 기준으로

시간의 흐름에 따른 변화를 살펴본다.

필자는 첫 구매 월을 기준으로 살펴보기 위해 먼저 고객별로 첫 구매일을 확인한다.

-- 고객별로 구매일 확인
-- 첫 구매일
SELECT customerID,
MIN(invoicedate) MNDT
FROM dataset3
GROUP BY 1;

이후 고객별 주문일자와 구매액을 조회한다.

-- 고객별 주문일자, 구매액 조회
SELECT customerID
, invoiceno
, invoicedate
        , unitprice * quantity AS sales -- 구매금액
FROM dataset3;

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

SELECT *
FROM (SELECT customerID
, MIN(invoicedate) MNDT -- 최초 구매일
        -- MAX(invoicedate) MXDT -- 마지막 구매일
FROM dataset3
GROUP BY 1) A
LEFT JOIN (SELECT customerID
, invoicedate
        , unitprice * quantity AS sales -- 구매금액
FROM dataset3) B
ON A.customerID = B.customerID;

join을 통해 코호트 분석을 위한 테이블 생성을 완료했다면

이제는 최초 구매일에서 SUBSTR() 함수를 통해 연-월까지만 데이터를 가져온다.

이후 TIMESTAMPDIFF() 함수를 이용해 각 구매일 간의 간격을 구한다.

최초 구매월, 구매 간격으로 그룹핑해 구매자 수를 카운트하고

SUM() 함수로 매출을 합계해 각 코호트의 리텐션과 매출액을 구할 수 있다.

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 AS sales -- 구매금액
FROM dataset3) B
ON A.customerID = B.customerID
GROUP BY 1, 2;

6. 고객 세그먼트

서비스를 이용하는 고객의 특성을 분류하는 고객 세그먼트를 통해

개인화된 혜택을 제공하는 것은 디지털 마케팅의 핵심이다.

1) RFM

RFM은 고객 세그먼트의 중요한 분석 방법으로,

구매가능성이 높은 고객을 선정하는데에 널리 쓰이는 방법 중 하나이다.

-- Recency : 제일 최근에 구입한 시기는 언제인가?
-- Frequency : 어느 정도로 자주 구매했는가?
-- Monetary : 구입한 총 금액은 얼마인가?

위의 세가지 기준에 의해 고객의 가치를 계산하는데

이번 분석도 바로 이 RFM 분석에 맞추어 고객 세그먼트를 구분해보도록 하자.

먼저 Recency를 구하기 위해 마지막 구매일을 구한다.

-- Recency 계산하기
SELECT customerID
, MAX(invoiceDate) MXDT
FROM dataset3
GROUP BY 1;

본 데이터 세트의 마지막 구매일을 확인해보았을 때

2011-12-02 이므로, 이 날짜를 기준으로 하여 Recency를 구할 수 있다.

-- 2011-12-02로부터의 Time Interval 계산하기
SELECT customerID
, DATEDIFF('2011-12-02', MXDT) RECENCY
FROM (
SELECT customerID
, MAX(invoiceDate) MXDT
FROM dataset3
GROUP BY 1
) A;

이어서 Frequency와 Monetary를 계산한다.

Frequency는 주문번호를 중복없이 카운트하여 구매건수로,

Monetary는 상품 개수 * 개당 가격을 곱한 값을 합친 구매금액으로 구한다.

-- Frequency(구매건수), Monetary(구매금액) 계산하기 
SELECT customerID
, COUNT(DISTINCT invoiceNo) Frequency
        , SUM(Quantity * UnitPrice) Monetary
FROM dataset3
GROUP BY 1;

이제 위에서 구한 Recency, Frequency, Monetary를 하나의 쿼리로 구한다.

-- Recency, Frequency, Monetary 계산하기
SELECT customerID
, DATEDIFF('2011-12-02', MXDT) RECENCY
        , FREQUENCY
        , MONETARY
FROM (
SELECT customerID
, MAX(invoiceDate) MXDT
, COUNT(DISTINCT invoiceNo) FREQUENCY
        , SUM(Quantity * UnitPrice) MONETARY
FROM dataset3
GROUP BY 1
) A;

이렇게 구한 RFM Score는 머신러닝에서 K-Means과 같은 클러스팅 기법으로

고객군을 나눌 수 있다. 위의 데이터를 R이나 Python으로 옮겨서 알고리즘을 적용하는 방법이 일반적이다.

2) 재구매 Segment

동일한 상품을 2개 연도에 걸쳐서 구매한 고객 vs 그렇지 않은 고객

두 세그먼트로 나누기 위해서는 고객별, 상품별 구매 연도를 Unique하게 카운트해야한다.
* 파생변수를 만드는 과정 중 하나가 바로 이 부분이다.

-- 고객별, 상품별 구매연도를 Unique하게 카운트
SELECT customerID
, StockCode
, COUNT(DISTINCT SUBSTR(invoiceDate, 1, 4)) Unique_yy
FROM dataset3
GROUP BY 1, 2;

여기에서 Unique_yy의 최대값이 2 이상인 고객은 특정 상품을 2개 연도에 걸쳐

구매한 것으로 재구매를 한 상품이 있다고 볼 수 있고

그렇지 않은 고객은 연도에 걸쳐 재구매를 한 상품이 없다고 볼 수 있다.

-- Unique_yy의 최대값이 2 이상인 고객 vs 그렇지 않은 고객 나누기
SELECT customerID
, MAX(Unique_yy) mx_unique_yy
FROM (
SELECT customerID
, StockCode
, COUNT(DISTINCT SUBSTR(invoiceDate, 1, 4)) Unique_yy
FROM dataset3
GROUP BY 1, 2
) A
GROUP BY 1;

이제는 CASE WHEN을 통해 mx_unique_yy 값이 2 이상인 경우는 1,

아닌 경우는 0으로 생성해 repurchase_segment를 생성한다.

SELECT customerID
, CASE WHEN mx_unique_yy >= 2 THEN 1 ELSE 0 END Repurchase_segment
FROM (
SELECT customerID
, MAX(Unique_yy) mx_unique_yy
FROM (
SELECT customerID
, StockCode
, COUNT(DISTINCT SUBSTR(invoiceDate, 1, 4)) Unique_yy
FROM dataset3
GROUP BY 1, 2
) A 
GROUP BY 1 ) A;

7. 일자별 첫 구매자 수

이제는 일자별 첫 구매자 수를 구해보도록 하자.

먼저 고객별로 첫 구매일을 계산하고 이를 일자별로 고객 수를 카운트하면

일자별 첫 구매자 수를 계산할 수 있다.

-- 고객별 첫 구매일
SELECT customerID
, MIN(invoiceDate) MNDT
FROM dataset3
GROUP BY 1;

-- 일자별로 첫 구매 고객 수 카운트
SELECT MNDT
, COUNT(DISTINCT customerID) BU
FROM (
SELECT customerID
, MIN(invoiceDate) MNDT
FROM dataset3
GROUP BY 1
) A
GROUP BY 1;

8. 상품별 첫 구매 고객수

상품별로 첫 구매 고객 수를 파악해

가장 많은 구매가 이루어진 상품을 찾을 수 있다면,

첫 구매를 유도한 상품의 특징을 분석해

신제품을 기획할 때에 좋은 의사결정을 내릴 수 있을 것이다.

먼저 고객별, 상품별 첫 구매일자를 구한다.

-- 고객별, 상품별 첫 구매일자
SELECT customerID
, StockCode
        , MIN(invoiceDate) MNDT
FROM dataset3
GROUP BY 1, 2;

고객별로 구매일 기준으로 순위를 생성해 고객의 최초 구매상품을 조회할 수 있다.

-- 고객별로 구매일 기준으로 순위 생성
SELECT *
        , ROW_NUMBER () OVER(PARTITION BY customerID ORDER BY MNDT) RNK
FROM (
SELECT customerID
, StockCode
        , MIN(invoiceDate) MNDT
FROM dataset3
GROUP BY 1, 2
) A;

-- 고객별 첫 구매 내역 조회
SELECT *
FROM (
SELECT *
        , ROW_NUMBER () OVER(PARTITION BY customerID ORDER BY MNDT) RNK
FROM (
SELECT customerID
, StockCode
, MIN(invoiceDate) MNDT
FROM dataset3
GROUP BY 1, 2
) A) A
WHERE RNK  = 1;

-- 상품별 첫 구매 고객 수 집계
SELECT StockCode
, COUNT(DISTINCT customerID) FIRST_BU
FROM (
SELECT *
FROM (
SELECT *
, ROW_NUMBER () OVER(PARTITION BY customerID ORDER BY MNDT) RNK
FROM (
SELECT customerID
, StockCode
, MIN(invoiceDate) MNDT
FROM dataset3
GROUP BY 1, 2
) A) A
WHERE RNK  = 1) A
GROUP BY 1
ORDER BY 2 DESC;

# 9. 첫 구매 후 이탈하는 고객의 비중
SELECT *
FROM dataset3;

-- 동일한 날짜에 2번 이상 주문하고 이탈한 경우도 첫 구매 후 이탈 고객으로 보기.
-- 구매일자 값이 하나만 존재할 것.
-- 이탈 고객 : 고객별로 구매일자 중복을 제거하고 카운트할 때 1의 값을 가진 고객

-- 고객별 구매일자의 중복 제거, 카운트
SELECT customerID
, COUNT(DISTINCT invoiceDate) F_DATE
FROM dataset3
GROUP BY 1;

SELECT SUM(CASE WHEN F_DATE = 1 THEN 1 ELSE 0 END) CNT_BOUNCE
, SUM(CASE WHEN F_DATE = 1 THEN 1 ELSE 0 END) / SUM(1) BOUNCE_RATE
FROM (
SELECT customerID
, COUNT(DISTINCT invoiceDate) F_DATE
FROM dataset3
GROUP BY 1
) A;
-- 첫 구매 후 이탈한 고객의 비중은 약 73%

-- 국가별 첫 구매 후 이탈 고객

-- 국가별 고객 첫 구매일자 중복 제거, 카운트
SELECT customerID
, country
, COUNT(DISTINCT invoiceDate) F_DATE
FROM dataset3
GROUP BY 1, 2;

-- 국가로 데이터 그룹핑한 뒤 집계
SELECT country
, SUM(CASE WHEN F_DATE = 1 THEN 1 ELSE 0 END) CNT_BOUNCE
        , SUM(CASE WHEN F_DATE = 1 THEN 1 ELSE 0 END) / SUM(1) BOUNCE_RATE
FROM (
SELECT customerID
, country
, COUNT(DISTINCT invoiceDate) F_DATE
FROM dataset3
GROUP BY 1, 2
) A
GROUP BY 1
ORDER BY 1;

# 10. 판매수량이 20% 이상 증가한 상품 리스트
SELECT DISTINCT(SUBSTR(invoiceDate, 1, 4))
FROM dataset3;
-- 판매기간은 2010, 2011년으로 구성됨.

-- 2010년 대비 2011년 판매수량이 20% 증가한 상품 찾기
-- 2011년 상품별 판매수량 계산
SELECT StockCode
, SUM(Quantity) QTY
FROM dataset3
WHERE SUBSTR(invoiceDate, 1, 4) = '2011'
GROUP BY 1;

-- 2010 상품별 판매수량 계산
SELECT StockCode
, SUM(Quantity) QTY
FROM dataset3
WHERE SUBSTR(invoiceDate, 1, 4) = '2010'
GROUP BY 1;

-- 2011년도 상품별 판매수량 테이블에 2010년도 상품별 판매수량 테이블 결합

SELECT *
FROM (
SELECT StockCode
, SUM(Quantity) QTY
FROM dataset3
WHERE SUBSTR(invoiceDate, 1, 4) = '2011'
GROUP BY 1
) A
LEFT JOIN (
SELECT StockCode
, SUM(Quantity) QTY
FROM dataset3
WHERE SUBSTR(invoiceDate, 1, 4) = '2010'
GROUP BY 1
) B
ON A.StockCode = B.StockCode;

-- 출력해야할 내용 : 상품 코드, 2011년 판매수량, 2010년 판매수량, 2010년 대비 증가율
SELECT A.QTY QTY_2011
, B.QTY QTY_2010
        , A.QTY / B.QTY -1 QTY_INCREASE_RATE
FROM (
SELECT StockCode
, SUM(Quantity) QTY
FROM dataset3
WHERE SUBSTR(invoiceDate, 1, 4) = '2011'
GROUP BY 1
) A
LEFT JOIN (
SELECT StockCode
, SUM(Quantity) QTY
FROM dataset3
WHERE SUBSTR(invoiceDate, 1, 4) = '2010'
GROUP BY 1
) B
ON A.StockCode = B.StockCode;

-- 2010년 대비 2011년 증가율이 0.2 이상인 경우로 조건.
-- QTY_INCREASE_RATE가 SELECT 절에 있기 때문에 바로 WHERE 절에 사용 x
-- 서브쿼리를 생성해 WHERE 절에서 사용할 수 있도록 만들기

SELECT *
FROM (
SELECT A.StockCode
, A.QTY QTY_2011
, B.QTY QTY_2010
, A.QTY / B.QTY -1 QTY_INCREASE_RATE
FROM (
SELECT StockCode
, SUM(Quantity) QTY
FROM dataset3
WHERE SUBSTR(invoiceDate, 1, 4) = '2011'
GROUP BY 1
) A
LEFT JOIN (
SELECT StockCode
, SUM(Quantity) QTY
FROM dataset3
WHERE SUBSTR(invoiceDate, 1, 4) = '2010'
GROUP BY 1
) B
ON A.StockCode = B.StockCode) BASE
WHERE BASE.QTY_INCREASE_RATE >= 1.2;

'Database' 카테고리의 다른 글

Streamlit과 BigQuery 연동 (with Google Cloud Platfrom) (1)	2023.10.18
PostgreSQL 기초 실습 및 개념 (0)	2023.10.13
MySQL - 식품 배송 데이터 분석 (1)	2023.10.10
MySQL - 상품 리뷰 데이터를 이용한 리포트 작성 (1)	2023.10.10
MySQL 기본구문 - 분석을 위한 조건을 정확하게! 필터 역할 'WHERE' (0)	2023.10.10

Nayoon's Data Analytics

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

7장

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

1. 데이터 생성하기

2. 데이터 구조 이해하기

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

4. 국가별 재구매율 계산

5. 코호트 분석

6. 고객 세그먼트

1) RFM

2) 재구매 Segment

7. 일자별 첫 구매자 수

8. 상품별 첫 구매 고객수

'Database' 카테고리의 다른 글

티스토리툴바

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

7장

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

1. 데이터 생성하기

2. 데이터 구조 이해하기

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

4. 국가별 재구매율 계산

5. 코호트 분석

6. 고객 세그먼트

1) RFM

2) 재구매 Segment

7. 일자별 첫 구매자 수

8. 상품별 첫 구매 고객수

'Database' 카테고리의 다른 글

관련글

티스토리툴바