[스타터스 TIL] 52일차.SQL 실전 트레이닝 (8) - 미니프로젝트 1

---

1. 미니 프로젝트

1.1 데이터

Brazilian E-Commerce Public Dataset by Olist

 

1.2 데이터 IMPORT

① 테이블 생성

② psql에서 csv 파일 경로 입력

③ 제약조건 설정

 

1.3 분석 방법

① 데이터 탐색

② 분석 목적 설정

③ 지표 설정 및 분석 계획

④ 데이터 추출/정제/가공/분석

⑤ 리포트 작성 및 발표

 

✅ 분석 목적 설정

- 고객 담당자 

- 유통 담당자

- 제품 담당자

- 배송 담당자

 

---

2. 프로젝트 진행

2.1 분석 목적 설정

✔ 고객 담당자

✔ 고객 중심 분석

→ 효과적인 고객 관리 전략 수립을 위한 데이터 분석 (고객 세분화(RFM) → 고객 특성 분석 → 마케팅 전략)

 

[+] 고객 담당자와 제품 담당자 중에 고민을 했다가 고객 담당자로 진행을 함

✔ 제품 담당자로 설정할 경우, 진행할 분석들
- 효과적인 제품관리를 위한 분석
- 지역 별 탑 10 카테고리
- 카테고리 1개당 몇 천개씩 있는 경우가 있음
- 주문 취소 비율이 높은/낮은 제품 (order_status)
- 재구매율이 높은/낮은 제품
- 제품의 계절성
- 카테고리별 / 제품별 매출
- 카테고리별 MoM/ YoY
- 2016-09-04 -2018-10-17
- 리뷰에 따른 재구매율

 

2.2 사용한 테이블 및 컬럼

customers	orders	order_payments	products	reviews	order_items
customer_id	order_id	payment_sequential	product_id	review_id	order_item_id
customer_unique_id	order_status	payment_installments	product_category_name	review_score	price
customer_city	order_purchase_timestamp	payment_value		review_creation_date	freight_value
customer_state	order_delivered_customer_date

'

2.3 분석할 항목 설정 (1차)

✔ RFM을 통한 고객 유지

- 이탈률 → 앱 푸쉬 알림, 00님을위한 추천목록, ~~까지 사용할 수 있는 쿠폰 발급
- 재구매율 (전체 데이터)
- R이 3인값(휴면X) 안에서 F+M 을통해 1등급 부터 5등급까지 나눠 해택주기

- 재구매율, 점수 높은 사람들이 많이 구매하는 카테고리를 파악
- 날짜 사용 윈도우함수 (order_purchase_timestamp)
- 지역별 매출액 비교
- 지역별 top 3의 top3 도시

✔ 전체적인 고객 유지

- 이 카드를 쓰면 이만큼 할인을 해드릴게여 (카드 회사와 제휴)
- 할부 vs 일시불 -> 무이자 개월 수.....
- 고객에게 가장 인기있는 카테고리
- 상품이 도착한 후 리뷰 작성까지 걸린 기간 (기간 안에 리뷰를 쓰면 benefit)
- 시간대 별 주문건수 변화 (00시부터 24시까지 6시간씩)
- 계절성 확인(어느 분기 or 월에 전체적으로 소비가 늘어나는지) → 많이들 찾는 제품으로 중/하위 고객
- 등급별 상위 판매 카테고리

▶ 고객 속성 분석을 통해 기존 고객 유지 / 매출 증대 기여
▶ 마지막 구매이후로 특정 기간이 지나면 재구매시 혜택 주기 (by 문자, 카톡 등등)
▶ 높은 grade에게는 다양한 혜택 제공

 

2.4 지표 설정

1) RFM 분석 활용

RFM을 활용하여 지표를 설정하고, 해당 지표로 점수를 매겨서 고객을 세분화하고자 함

 

🔎 RFM 분석을 활용한 이유는?

이커머스 등 다른 기업이 설정한 고객의 등급을 확인해본 결과,

구매액 뿐만 아니라, 구매 건수도 고려하기 때문이다. 특히 가입하고 전체 기간이 아닌 특정 기간동안의 구매건수와 구매액을 기준으로 하는 것으로 보아 얼마나 최근에 구매를 했는지도 중요하다는 것을 알 수 있었다.

→ 그래서 이 모든 것들을 고려할 수 있는 R(최근성), F(최빈성), M(구매액) 분석을 선택하였다.

 

[출처] 4조 Figma

 

2) RFM 분석을 활용한 점수 산정

RFM 지표로 점수를 매길 때, NTILE() 함수를 사용하여 구간을 각 구간 당 동일한 데이터 비율로 나누고자 함

 

❓❗ 하지만!! 문제점 발생!!

✅ Frequency (최빈성)

Frequency가 같은 1인데, 점수가 다른 고객이 생기는 문제가 발생함

→ frequency가 1인 고객이 전체 중 약 9만건 이상에 달함 (재구매를 하지 않고 한 번만 구매한 고객이 대부분)

 

✅ Monetary (구매액)

또한, Monetary의 경우, 분포가 너무 커서 10등급에는 319 달러부터 ~ 13,664달러가 같이 있는 문제가 발생함

→ 구매액이 큰 이상치가 많은 것들을 알 수 있음

 

▶▶ 위와 같은 문제로 인해 Frequency와 Monetary의 경우, NTILE() 함수를 사용하여 구간을 나누지 않고

분포를 확인하고 그에 맞는 기준을 설정하여 구간을 나누기로 함

 

 

3) 각 지표 기준 설정

✅ Recency (최근성)

✔ 구매한지 6개월 이내인 고객을 위주로 등급을 부여하고 혜택을 제공하고자 함

✔ 구매한지 6개월 ~ 12개월 이내인 고객은 이탈위험고객으로 간주

✔ 구매한지 12개월이 지난 고객은 이탈 고객, 즉 휴면고객으로 간주

 

✅ Frequency (최빈성)

✔ 1번 구매한 사람이 전체의 약 97%를 차지하고 있으며, 2번 구매한 사람은 약 3%를 차지하고 있음

✔ 따라서 Frequency를 1번 / 2번 / 3번 이상 구매한 고객으로 등급을 나누고자 함

 

✅ Monetary (구매액)

✔ 구매액의 분포가 매우 차이나며, 이상치가 큰 것을 확인할 수 있음

✔ 0 부터 중앙값 (108)까지, 중앙값(108)부터 상한 수염(364)까지, 그리고 그 이후 이상치까지로 등급을 나누고자 함

 

4) RFM 지표를 활용한 최종 등급 기준

RFM 지표의 기준을 설정하였으며, 이제 이 점수들을 또 기준을 세워 등급을 나누고자 함

✔ R이 3인, 즉 6개월 이내에 구매한 고객들을 기준으로 전반적인 등급을 나눔

✔ F와 M을 더한 값이 2~6점이기에 각 점수 별로 등급을 나눔 (총 5등급)

 

 

2.5 분석할 항목 설정 (최종)

위에서 정한 분석할 항목 중에서 실제로 분석할 항목을 정해보았다.

RFM을 활용한 분석과 전반적인 고객의 분석 총 2가지로 나누어져있는 것들 중에 유익한 결과를 얻을 수 있는 분석 항목을 설정하고자 하였다.

 

1) RFM을 통한 고객 유지

① 이탈률 → 앱 푸쉬 알림, 00님을위한 추천목록, ~~까지 사용할 수 있는 쿠폰 발급
② R이 3인값(휴면X) 안에서 F+M 을통해 1등급 부터 5등급까지 나눠 해택주기
③ 지역별 매출액 비교 : 지역별 top 3의 top3 도시

 

2) 전체적인 고객 유지

④ 시간대 별 주문건수 변화 (00시부터 24시까지 6시간씩)

⑤ 계절성 확인(어느 분기 or 월에 전체적으로 소비가 늘어나는지) → 많이들 찾는 제품으로 중/하위 고객

⑥  등급별 상위 판매 카테고리

---

3. 다양하게 진행한 분석, 시도들 (feat. Figma)

3.1 특정 데이터 파악

 

3.2 다양한 분석, 시도들

---

💡 회고

미니 프로젝트를 시작하였다.

데이터셋의 용량이 많아서 처음엔 겁 먹었는데, 막상 데이터를 하나씩 뜯어보니까 데이터가 없어서 오히려 제대로된 분석을 하기 어렵다는 생각이 들었다. 그래도 새로운 데이터로 SQL 쿼리를 짜볼 수 있어서 좋았다.

 

😎

SQL 프로젝트는 처음이었지만 이전에 태블로 프로젝트를 진행해서 그런가 수월하게 진행이 되었다.

어떤 식으로 진행할지 빠르게 방향성을 잡고 그에 맞춰 데이터를 파악하고 분석해보았다.

또한 지표를 설정하는 것에 있어서 많은 노력을 기울였는데, 지표를 잘 설정한 것 같아서 만족스럽다.

 

😭🤓

SQL이 쉽지 않은 점은 바로 쿼리 단 하나에, 몇 글자에 추출되는 데이터의 결과값이 다르다는 것이다.

우리 조는 각자 하나씩 맡아서 쿼리를 짜보기 전에 RFM 분석을 다같이 쿼리로 짜보았다. 그랬더니 쿼리 값이 각기 다르게 나왔고 쿼리를 디버깅하듯 다시 한 번 확인해보고 동일한 쿼리값이 나오도록 쿼리를 짜는 것에 많은 시간을 투자하였다...!

이 부분이 중요하다고 생각이 되었으며, 각자 성장할 수 있는 시간이 아니었나 싶다! :)