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

[이전 글]

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

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

---

고객 세그먼트

 

서비스를 이용하는 고객의 특성을 분류할 수 있다면, 서비스 사용자에 대한 이해를 넓힐 수 있고, 타깃 마케팅 같은 개인화된 혜택을 제공할 수 있다.

 

1) RPM

서비스에서 높은 가치를 가진 고객을 구분하기 위해 자주 사용되는 모델이다.RPM은 가치 있는 고객을 추출해 이를 기준으로 고객을 분류할 수 있는 매우 간단하면서도 유용하게 사용될 수 있는 방법으로 알려져 마케팅에서 가장 많이 사용되는 분석 방법 중 하나이다.RPM은 구매 가능성이 높은 고객을 선정하기 위한 데이터 분석 방법으로, 분석 과정을 통해 데이터는 의미 있는 정보로 전환된다.

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

고객별로 Recency, Frequency, Monetary를 계산해 고객의 Segment를 구분해 보자.

 

Recency란 거래의 최근성을 나타내는 지표로, 고객이 얼마나 최근에 구입했는지를 의미한다. 보통 산출하는 시점을 기준으로 하여 며칠 또는 몇 달 전에 구매했는지 계산한다.

 

해당 데이터 세트의 마지막 구매일(invoiceDate)은 2011-12-01로, 우리는 2011-12-02을 기준으로 하여 Recency를 계산해 보자. 먼저 고객의 마지막 구매일을 구해보자.

select customerid,
max(invoiceDate) mxdt
from mydata.dataset3
group
by 1;

[쿼리 실행 결과]

이후, 2011-12-02로부터의 Timer Interval을 계산해 보자.

select customerid,
datediff('2011-12-02',mxdt) recency
from
(select customerid,
max(invoiceDate) mxdt
from mydata.dataset3
group
by 1) a
;

[쿼리 실행 결과]

이어서 Frequency와 Monetary를 계산해 보자. 2가지 지표는 한 번에 구해보자.

select customerid,
count(distinct invoiceNo) frequency,
sum(Quantity*UnitPrice) Monetary
from mydata.dataset3
group
by 1
;

[쿼리 실행 결과]

이제 위에서 구한 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 mydata.dataset3
group
by 1) a
;

[쿼리 실행 결과]

이렇게 계산한 RFM Score를 이용해 어떻게 Segment를 나눌 수 있을까?

먼저 K-Means 알고리즘과 같은 클러스터링 기법으로 고객군을 나눌 수 있다. 이런 알고리즘은 주로 R이나 Python과 같은 언어로 적용한다.

 

1-1) K-Means Algorithm

K-Means Algorithm은 비슷한 특성을 가진 데이터를 그룹핑하는 Clustering기법 중 하나로, 널리 사용되는 알고리즘이다.

 

2) 재구매 Segment

동일한 상품을 2개 연도에 걸쳐서 구매한 고객과 그렇지 않은 고객을 Segment로 나누려면 어떻게 해야 할까?A라는 상품을 2010년도와 2011년에 걸쳐 구매한 고객과 A라는 상품을 특정 연도에만 구매한 고객으로 나눌 수 있을까?

 

이를 구하기 위해서 먼저 고객별, 상품별 구매 연도를 Unique 하게 카운트해 보자.

select customerid,
StockCode,
count(distinct substr(invoiceDate,1,4)) unique_yy
from mydata.dataset3
group
by 1,2
;

[쿼리 실행 결과]

unique_yy가 2 이상인 고객과 그렇지 않은 고객을 구분하면, 우리가 구하려고 했던 Segment를 구할 수 있다.  고객별로 unique_yy의 최댓값을 계산했을 때, 그 값이 2 이상인 고객은 특정 상품을 2개 연도에 걸쳐 구매한 것으로 볼 수 있고, 그렇지 않은 고객은 연도에 걸쳐 재구매한 상품이 없다고 볼 수 있다.

select customerid,
max(unique_yy) mx_unique_yy
from
(select customerid,
StockCode,
count(distinct substr(invoiceDate,1,4)) unique_yy
from mydata.dataset3
group
by 1,2) a
group
by 1
;

[쿼리 실행 결과]

이제 max_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 mydata.dataset3
group
by 1,2) a
group
by 1) a
group
by 1
;

[쿼리 실행 결과]

 

일자별 첫 구매자 수

 

다음으로 일자별 첫 구매자 수를 계산해 보자. 2006-01-01에 첫 구매한 고객 수는 몇 명인지, 2006-01-02에 첫 구매한 고객 수는 몇명인지 구해 보자.

 

먼저 고객별로 첫 구매일을 계산하고 일자별로 고객 수를 카운트하면, 일자별 첫 구매자 수를 계산할 수 있다.

 

1) 고객별 첫 구매일

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

[쿼리 실행 결과]

 

2) 일자별 첫 구매 고개 수

SELECT MNDT,
COUNG(DISTINCT CUSTOMERID) BU
FROM
(SELECT CUSTOMERID,
MIN(INCOICEDATE) MNDT
FROM MYDATA.DATASET3
GROUP
BY CUSTOMERID) A
GROUP
BY MNDT
;

[쿼리 실행 결과]

 

상품별 첫 구매 고개 수

 

서비스에서 신규 유저를 확보하는 것은 매우 중요한 일이다.

그런 이유로 많은 커머스 회사는 신규 고객을 유치하기 위한 Hooking 상품을 광고하고 노출한다. 첫 구매가 가장 많이 이루어진 상품을 찾을 수 있다면, 첫 구매를 유도한 상품의 특징을 파악할 수 있을 것이다.

 

먼저 고객별로 구매일 기준으로 순위(랭크)를 생성하면, 고객의 최초 구매 상품을 조회할 수 있다. 다음으로 최초 구매 사품으로 데이터를 그룹핑하고 고객 수를 집계하면, 상품별로 최초 구매 고객 수를 계산할 수 있다.

 

1) 고객별, 상품별 첫 구매 일자

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

[쿼리 실행 결과]

 

2) 고객별 구매와 기준 순위 생성(RNK)

 

고객벽, 상품별 첫 구매 일자를 계산한다. 계산 결과(최초 구매일)를 기준으로 순위를 생성해야 하므로, 앞에서 작성한 쿼리를 서브 쿼리로 생성하고 순위 열을 생성한다.

SELECT *,
ROW_NUMBER() OVER(PARTITION BY CUSTOMERID ORDER BY MNDT) RNK
FROM
(SELECT CUSTOMERID,
STOCKCODE,
MIN(INVOICEDATE) MNDT
FROM MYDATA.DATASET3
GROUP
BY 1,2) A
;

[쿼리 실행 결과]

 

2-1) 고객별 첫 구매 내역 조회

 

이제 순위 열의 값이 1인(고객별 최초 구매 내역)조건을 생성해야 한다.순위 열은 SELECT구문에서 새롭게 생성한 열이므로 앞선 쿼리를 서브 쿼리로 다시 생성해 WHERE 절에 조건을 추가해야한다.

SELECT *
FROM
(SELECT *,
ROW_NUMBER() OVER(PARTITION BY CUSTOMERID ORDER BY MNDT) RNK
FROM
(SELECT CUSTOMERID,
STOCKCODE,
MIN(INVOICEDATE) MNDT
FROM MYDATA.DATASET3
GROUP
BY 1,2) A) A
WHERE RNK = 1
;

 

2-2) 상품별 첫 구매 고객 수 집계

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 MYDATA.DATASET3
GROUP
BY 1,2) A) A
WHERE RNK = 1) A
GROUP
BY STOCKCODE
ORDER
BY 2 DESC
;

[쿼리 실행 결과]

 

첫 구매 후 이탈하는 고객의 비중

 

모든 서비스에서 신규 고객을 확보하는 것은 중요한 일이고, 동시에 어려운 일이기도 한다. 따라서 신규로 유입된 고객이 계속 서비스를 이용할 수 있도록 유도하는 액션이 필요하다. 이를 위해서는 얼마나 많은 고객이 이탈하는지를 특정할 필요가 있다. 이제 첫 구매 고객 중 얼마나 많은 고객이 이탈하는지 살펴보자.

 

먼저, DATASET3 구조를 살펴보자.

해당 데이터에는 고객 번호(CustomerID)와 구매일자(InvoiceDate)가 존재한다. 첫 구매를 하고 이탈해 버린 고객의 데이터는 어떻게 적재되어있을까?(동일한 날짜에 2번 이상 주문하고 이탈한 경우도 첫 구매 후 이탈 고객으로 본다.) 구매일자(InoviceDate)값이 하나만 존재할 것이다. 예를들어 A라는 고객이 '2010-01-01'에 첫 구매를 하고 이후 구매를 하지 않았다면, A라는 고객의 InvoiceDate 값은 '2010-01-01' 하나만 존재할 것이다.

 

즉 고객별로 구매 일자(InvoiceDate)의 중복을 제거하고 카운트 했을때, 1의 값을 갖는 고객은 첫 구매를 하고 이탈한 고객이 된다. 먼저 고객별 구매 일자의 중복을 제거하고 카운트해보자.

SELECT CUSTOMERID,
COUNT(DISTINCT INVOICEDATE) F_DATE
FROM MYDATA.DATASET3
GROUP
BY 1
;

[쿼리 실행 결과]

F_DATE 값이 1인 고객 번호(CUSTOMERID)는 첫 구매 후 이탈한 고객들이다.

이제 F_DATE가 1인 고객의 수를 카운트해 그 값을 전체 고객의 수로 나누면, 첫 구매 후 이탈한 고객 수를 계산할 수 있다.

SELECT SUM(CASE WHEN F_DATE = 1 THEN 1 ELSE 0 END) / SUM(1) BOUNC_RATE
FROM
(SELECT CUSTOMERID,
COUNT(DISTINCT INVOICEDATE) F_DATE
FROM MYDATA.DATASET3
GROUP
BY 1) A
;

[쿼리 실행 결과]

 

THE END...