[유데미 스타터스 취업 부트캠프 4기] 데이터분석/시각화(태블로) - 11주차 학습 일지

---

11주차 교육에 대한 회고 (4L)

이번 주는 생각보다 너무 많이 힘들었다...
미니 프로젝트부터 최종 평가까지... 프로젝트와 최종 평가 공부를 한 번에 하다보니까 꽤나 힘들었다ㅠㅠ
최대한 둘 다 열심히 하려고 노력했는데, 좋은 결과가 있으면 좋겠다!
 
벌써 다음 주가 이 교육의 마지막이다... 그리고 대망의 해커톤이 진행된다!
이 날이 올 줄은 몰랐는데, 벌써 올 줄이야.. 긴장되기도 하며 후련할 것 같기도 하며 아무 생각도 없기도 하다!😵
 
해커톤에서 어느 누구와 같은 조가 되어도 우리 모두가 좋은 결과를 낼 것 같아서 이런 부분은 전혀 부담이 되지 않아서 좋다. 모두가 다들 열심히 하고 잘하기에 순위는 큰 의미가 없지 않을까 싶다!!!
그렇기에 내가 해커톤에서 3개월동안 쌓아온 역량을 얼마나 잘 발휘하는지가 가장 중요하다고 생각이 든다. 잘 하겠지!라는 믿음으로 해커톤에 임해야겠다 :)😎💪
 

---

1. 이번주 수업에서 좋았던 점은? (Liked)
2. 이번주에 새롭게 배운 점은? (Learned)
3. 배운 것에 관해서 내가 부족했던 부분은? (Lacked)
4. 앞으로 뭘 더 하면 좋을까? (Longed for) 

 

1. 이번주 수업에서 좋았던 점은? (Liked)

✔ 미니 프로젝트 피드백
SQL 미니 프로젝트를 이틀 동안 진행하였다. 사실 최종평가도 겹쳐 있어서 많은 리소스를 투입하지 못한 프로젝트라 제법 아쉬움이 남은 프로젝트이긴 하다ㅠㅠ
그러나 프로젝트 발표 후 강사님께서 피드백을 해주시는데 쉽게 간과할 수 있는 부분을 딱! 잡아주시고 해커톤 때 적용을 해볼 수 있는 피드백들이라서 유용했으며, 너무나도 감사했다.
(교육 커리큘럼에서 SQL 프로젝트가 더 많았으면 좋았을 것 같다!)
 

2. 이번주에 새롭게 배운 점은? (Learned)

✔ SQL 쿼리를 뜯어보는 힘
미니 프로젝트를 하면서 쿼리의 결과값이 달라서 어떤 부분이 잘못 되었는지를 확인해보면서 SQL 쿼리를 뜯어보고 오류를 찾아내는 힘을 기를 수 있었다.
지난 주에 진행한 프로젝트에서 쿼리 실수를 하였기 때문에 이 부분에 조금 더 신경을 쓰려고 노력을 하였다.
또한 프로젝트 했던 쿼리를 다시 한번 실행을 해보는데, 모두가 발견하지 못한 오류를 발견했고 데이터의 결과값이 확 달라지는 경우를 발견했다. 그렇기 때문에 SQL 쿼리를 하나씩 뜯어보고 팀원들 서로가 SQL 더블 체크를 해주는 것이 매우 중요하다는 것을 깨닫게 되었다.
 

3. 배운 것에 관해서 내가 부족했던 부분은? (Lacked)

✔ 쿼리 짜는 시간
쿼리를 짜는데 어떤 식으로 쿼리를 작성해야 할지에 대해 아직은 바로 생각이 나지 않는다.
찬찬히 주석을 달면서 쿼리를 짜보고, 또 오류를 하나씩 고쳐나가면서 짜다보니 쿼리를 짜는데에 시간이 제법 소요된다ㅠㅠ
쿼리를 짜는 시간을 조금씩 줄여야 하는데, 꾸준히 쿼리를 짜다보면 이 시간도 조금씩 줄지 않을까 싶다.
 

4. 앞으로 뭘 더 하면 좋을까? (Longed for)

✔ 해커톤 준비
다음 주가 대망의 해커톤이기 때문에 미리 조금씩 준비를 해둬야 할 듯하다.
SQL의 경우 2주 정도 꾸준히 해왔기 때문에 지금처럼만 하면 되는데, 태블로의 경우 최근에 많이 다뤄보질 않아서 태블로의 기능을 다시 익혀야 할 듯 싶다!
그리고 어떤 데이터셋이 나올지는 잘 모르겠지만, 웅진테크연구소에서 데이터를 설명해준다고 하였으니 웅진 씽크빅, 웅진 스마트올 등 여러 상품에 대한 기본적인 도메인 지식을 미리 습득해놓으면 좋지 않을까라는 생각이 들었다. 간단하게 이에 대해 찾아볼 예정이다.
마지막으로 해커톤 때 빡세게 해야 하므로 체력 구비! 최대한 주말에 푹 쉴 것이다🤓🤓

---

11주차 교육 내용 - 23.04.17 ~ 23.04.21

📌 Decil 분석

1) NTILE() 함수를 활용한 10개의 그룹으로 세분화

① 고객의 총 매출액 기준으로 정렬
② 상위부터 10%씩 나누어 10개의 그룹 할당
③ decil 별 매출합계
④ decil 별 구성비
⑤ decil 별 구성비 누계
 

-- ① 고객의 총 매출액 기준으로 정렬
WITH cte_customers AS (
	SELECT o.customer_id 
		 , sum(od.unit_price * od.quantity * (1-od.discount)) AS 매출액
	FROM orders o 
		INNER JOIN order_details od 
			ON o.order_id = od.order_id
	GROUP BY 1
	ORDER BY 2 DESC)
-- ② 상위부터 10%씩 나누어 10개의 그룹 할당
, cte_ntile AS (
	SELECT *
		, ntile(10) over(ORDER BY 매출액 DESC) AS 그룹
	FROM cte_customers)
-- ③ decil 별 매출합계
, cte_group_sales AS (
	SELECT 그룹
		 , sum(매출액) AS 매출액
	FROM cte_ntile
	GROUP BY 1
	ORDER BY 1)
--④ decil 별 구성비
, cte_group_percent AS (
	SELECT 그룹
		 , 매출액
		 , round((매출액 / sum(매출액) over() * 100)::NUMERIC, 2) AS 구성비
	FROM cte_group_sales)
--⑤ decil 별 구성비 누계
SELECT *
	 , sum(구성비) over(ORDER BY 구성비 DESC) AS 구성비누계
FROM cte_group_percent;

 

2) NTILE() 함수를 활용한 ABC 분석

WITH customers_sales AS (
	SELECT c.customer_id
		 , od.unit_price * od.quantity * (1-od.discount) AS sales
	FROM customers c 
		LEFT JOIN orders o 
			ON c.customer_id = o.customer_id 
		LEFT JOIN order_details od
			ON o.order_id = od.order_id)
-- 1) 고객 별 매출액 계산
, cte_sales AS (
	SELECT customer_id
		 , sum(sales) AS 매출액
	FROM customers_sales
	GROUP BY 1
	HAVING sum(sales) IS NOT NULL 
	ORDER BY 2 desc)
-- 2) 고객 별 매출 비율 계산
, cte_sales_percent AS (
	SELECT *
		 , sum(매출액) over() AS 전체매출액
		 , 매출액 / sum(매출액) over() * 100 AS 매출비율
	FROM cte_sales)
-- 3) 고객 별 매출 비율 누계 계산
, cte_sales_percent_cumnum AS (
	SELECT customer_id
		 , 매출액
		 , 매출비율
		 , sum(매출비율) over(ORDER BY 매출비율 desc) AS 매출비율누계
	FROM cte_sales_percent)
SELECT *
	 , CASE WHEN 매출비율누계 <= 70 THEN 'A'
	 		WHEN 매출비율누계 <= 90 THEN 'B'
	 		ELSE 'C'
	 		END AS 등급
FROM cte_sales_percent_cumnum;

---

📌 RFM 분석

- Recency (R) : 얼마나 최근에 구매를 했는지
- Frequency (F) : 얼마나 빈번하게 구매를 했는지
- Monetary (M) : 얼마나 많은 금액을 지불을 했는지를 확인하기 위한 지표
 

1) RFM 계산하기

WITH cte_customers AS (
	SELECT c.customer_id, c.company_name
		 , o.order_id, o.order_date 
		 , od.product_id, od.unit_price, od.quantity, od.discount
		 , od.unit_price * od.quantity * (1-od.discount) AS sales
	FROM customers c, orders o , order_details od
	WHERE c.customer_id = o.customer_id
	  AND o.order_id = od.order_id)
-- 1) 기준일 설정
, cte_max_date AS (
	SELECT *
		 , max(order_date) over() AS 기준일
		 , max(order_date) over() - order_date AS diff
	FROM cte_customers)
-- 2) RFM 계산
SELECT customer_id
	 , min(diff) AS R
	 , count(DISTINCT order_id) AS F
	 , sum(sales) AS M
FROM cte_max_date
GROUP BY 1
ORDER BY 4 desc;

 

2) RFM 지표를 활용하여 단계 설정 및 스코어 계산

-- 3) 단계 설정 및 스코어 지정
, cte_score AS (
	SELECT *
		 , CASE WHEN recency <= 6 THEN 5
		 		WHEN recency <= 15 THEN 4
		 		WHEN recency <= 30 THEN 3
		 		WHEN recency <= 70 THEN 2
		 		ELSE 1 
		 	END AS R
		 , CASE WHEN frequency >= 14 THEN 5
		 		WHEN frequency >= 10 THEN 4
		 		WHEN frequency >= 7 THEN 3
		 		WHEN frequency >= 5 THEN 2
		 		ELSE 1 
		 	END AS F
		 , CASE WHEN monetary >= 22000 THEN 5
		 		WHEN monetary >= 12000 THEN 4
		 		WHEN monetary >= 5500 THEN 3
		 		WHEN monetary >= 3000 THEN 2
		 		ELSE 1 
		 	END AS M		 
	FROM cte_rfm)
SELECT *
FROM cte_score;

 

3) 총점 계산 및 특별 관리 고객 선정

-- 4) 계산한 RFM 점수를 합산한 total_score 계산
, cte_total_score AS (
	SELECT customer_id
		 , R, F, M
		 , R+F+M AS total_score
	FROM cte_score
	ORDER BY total_score DESC)
SELECT *
FROM cte_total_score;

 

4) 핵심고객 구매이력 추출

total_score가 15점인 핵심고객의 구매이력추출

-- 5) 첫번째 cte 테이블과 조인하여 구매이력 추출
SELECT *
FROM cte_customers c1
	INNER JOIN cte_total_score c2
		ON c1.customer_id = c2.customer_id
WHERE total_score = 15
ORDER BY 1;

---

📌재구매율 (self join 활용)

- 고객 별 재구매율이기 때문에 distinct customer_id 해야 함
   (+ 많은 컬럼을 select 하는 것이 아니기에 distinct, 만약 컬럼이 많다면 고민해봐야 함)
- AND c1.YEAR::integer + 1 = c2.YEAR::integer)
   → self join을 하는 두번째 테이블의 year를 next_year로 지정하기 위해서 조인 시에 첫번째 테이블의 year에 1을 더한 값과 같다는 조건을 추가해줘야 함
- LEFT JOIN 진행
  → LEFT JOIN을 해야 재구매를 하지 않은 데이터에는 NULL 값이 생김 (매우 중요💡)

-- 1) 고객 별 구매 연도 출력
WITH cte_customers AS (
	SELECT DISTINCT o.customer_id
		 , to_char(o.order_date, 'YYYY') AS YEAR
	FROM orders o 
		INNER JOIN order_details od 
			ON o.order_id = od.order_id
	ORDER BY 1, 2)
-- 2) self join(left join)을 활용하여 기준 해, 다음 해 컬럼 생성
, cte_self_join AS (
	SELECT c1.customer_id
		 , c1.YEAR
		 , c2.YEAR AS next_year
	FROM cte_customers c1
		LEFT JOIN cte_customers c2
			ON c1.customer_id = c2.customer_id
			AND c1.YEAR::integer + 1 = c2.YEAR::integer)
-- 3) 재구매율 구하기 (각 연 컬럼의 count로 구할 수 있음)
SELECT YEAR
	 , count(year) AS 당해구매자수
	 , count(next_year) AS 다음해재구매자수
	 , round((count(next_year) / count(year)::numeric)*100, 2) AS 재구매율
FROM cte_self_join
GROUP BY year;

---

📌 이탈률

① 고객 별 최종 구매일 추출
② 경과일 계산
③ 이탈 고객 여부 (마지막 구매일 이후 90일 이상 경과한 고객)
④ 이탈률 계산
 

WITH cte_customers AS (
	SELECT c.customer_id, c.company_name
		 , o.order_id, o.order_date 
		 , od.product_id, od.unit_price, od.quantity, od.discount
		 , od.unit_price * od.quantity * (1-od.discount) AS sales
	FROM customers c, orders o , order_details od
	WHERE c.customer_id = o.customer_id
	  AND o.order_id = od.order_id)

 
① 고객 별 최종 구매일 추출
각 고객 별 order_date의 가장 마지막 날을 max 함수를 사용하여 구하기
→ max 함수를 사용하기 때문에 customer_id로 group by 해주어야 함

-- ① 고객 별 최종 구매일 추출
, cte_cust_max_date AS (
	SELECT customer_id
		 , max(order_date) AS 최종구매일
	FROM cte_customers
	GROUP BY 1)

 
② 경과일 계산
기준일인 전체 데이터에서 가장 마지막 날을 구하려면 max() over() 함수를 사용해야 함
기준일과 위에서 구한 최종구매일을 빼면 경과일이 됨

-- ② 경과일 계산
, cte_diff AS (
	SELECT *
		 , max(최종구매일) over() AS 기준일
		 , max(최종구매일) over() - 최종구매일 AS 경과일
	FROM cte_cust_max_date)

 
③ 이탈 고객 여부
경과일이 90일이 넘을 경우 1, 그렇지 않은 경우 0으로 이탈여부 컬럼을 새롭게 만듦
→ 이탈률을 구할 때 해당 컬럼의 데이터를 합산하기 위함

-- ③ 이탈 고객 여부 (마지막 구매일 이후 90일 이상 경과한 고객)
, cte_cust_90 AS (
	SELECT customer_id
		 , CASE WHEN 경과일 >= 90 THEN 1 ELSE 0 END AS 이탈여부
	FROM cte_diff)

 
④ 이탈률 계산
count(*)은 null 값을 포함한 전체 고객수를 계산함
위에서 만든 이탈여부 컬럼의 값을 다 더한 값이 이탈고객의 수가 됨
이를 활용하여 이탈률을 구할 수 있음

-- ④ 이탈률 계산
SELECT count(*) AS 전체고객수
	 , sum(이탈여부) AS 이탈고객수
	 , sum(이탈여부) / count(*)::NUMERIC * 100 AS 이탈률
FROM cte_cust_90;

---

📌 미니 프로젝트

1) 데이터

Brazilian E-Commerce Public Dataset by Olist

 

2) 분석 흐름

 

3) 분석 목적

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

4) 분석 항목

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

✅ 전체적인 고객 유지

④ 시간대 별 주문건수 변화 (00시부터 24시까지 6시간씩)
⑤ 계절성 확인(어느 분기 or 월에 전체적으로 소비가 늘어나는지) → 많이들 찾는 제품으로 중/하위 고객
⑥  등급별 상위 판매 카테고리
 

5) 사용할 테이블 & 컬럼

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

 

6) 지표 설정

 ✅ RFM 지표 기준 설정

Score / 지표	R (Recency)	F (Frequency)	M (Monetary)
3점	1 day ~ 180 days	3번 이상	$365 ~
2점	181 days ~ 365 days	2번	$109 ~ $364
1점	366 days~	1번	~ $108

 
✅ 등급 기준 설정

VVIP	R(Recency) = 3	F(Frequency)+M(Monetary) = 6
VIP	R(Recency) = 3	F(Frequency)+M(Monetary) = 5
GOLD	R(Recency) = 3	F(Frequency)+M(Monetary) = 4
SILVER	R(Recency) = 3	F(Frequency)+M(Monetary) = 3
BRONZE	R(Recency) = 3	F(Frequency)+M(Monetary) = 2
FAMILY	R(Recency) = 2	-
SLEEPING	R(Recency) = 1	-

 

7) 세부 분석 및 마케팅 방안 마련

① 등급 별 고객수 → 각 등급 별로 적립, 할인, 쿠폰, 무료배송 등 차등 혜택을 제공
② 고객 수가 많은 지역의 도시 TOP 3 → 고객 수가 많은 도시에 오프라인 행사 및 광고를 통해 고객 유지/휴면고객 활성화
③ 시간대별 주문건수 변화 → 특정 시간의 유입률을 증가시키기 위해 앱 푸시나 특가 이벤트 진행
④ 월/분기 별 주문건수 변화 → 계절성에 따라 프로모션 적용 가능
⑤ 등급 별 TOP 5 판매 카테고리 → 등급 별로 많이 팔린 카테고리 관련 혜택을 제공

 

8) 기대효과

위와 같은 분석을 통해 다양한 분석 지표를 활용하여 효과적인 마케팅 전략 수립에 도움을 주어 기존 고객을 활성화시키고 매출을 증대할 수 있다.
 

---

이번 주에 작성한 TIL

[스타터스 TIL] 50일차.SQL 실전 트레이닝 (6) - 고객 분석, Decil 분석, RFM 분석
[스타터스 TIL] 51일차.SQL 실전 트레이닝 (7) - RFM 분석, 재구매율, 이탈고객 분석, 백분위수, 최빈값
[스타터스 TIL] 52일차.SQL 실전 트레이닝 (8) - 미니프로젝트 1
[스타터스 TIL] 53일차.SQL 실전 트레이닝 (9) - 미니 프로젝트 2
[스타터스 TIL] 54일차.SQL 실전 트레이닝 (10) - 미니 프로젝트 3
[유데미 스타터스 취업 부트캠프 4기] SQL 최종평가 공부 (쿼리)

---

* 유데미 큐레이션 바로가기 : https://bit.ly/3HRWeVL
* STARTERS 취업 부트캠프 공식 블로그 : https://blog.naver.com/udemy-wjtb
본 후기는 유데미-웅진씽크빅 취업 부트캠프 4기 데이터분석/시각화 학습 일지 리뷰로 작성되었습니다.
 

---

마지막은 최종평가 끝나고 놀러간 우리 사쥔~📸📸

담주는 14명 모두 다같이 놀면 넘 좋겠다뤼,,,🤡