[👩‍💻TIL 52일차 ] 유데미 스타터스 취업 부트캠프 4기

 

오늘부터 미니 프로젝트가 진행된다.

 

사용한 데이터

Brazilian E-Commerce Public Dataset by Olist

 

 

✔️ 스키마 생성 (default 설정)

 

✔️ create문만 실행

➡️ 연결문제 시 데이터베이스를 postgres로 변경해주어야 한다.

 

✔️ psql에서 인코딩 변경해주기

 

✔️ 데이터 업로드하기

olist의 Tables를 새로고침하면 데이터가 잘 들어간 것을 확인할 수 있다.

 

 

✔️ 테이블 정의서 확인

 

[특징]

• product_id가 같은 상품을 여러 sellers가 판매할 수 있음
• _length는 익명처리된 것으로 추측됨
• 고객을 유일하게 식별하는 식별자: costomer_unique_id임, customer_id가 아니다!
• product_category_name_translation을 사용하여 카테고리 이름 영어로 확인 가능
• sellers는 product가 아닌 order_items와 연결되어 있음 

 

✔️ SQL 데이터분석

데이터 탐색 → 분석 목적 설정 → 지표 설정 및 분석 계획 → 데이터 추출/정제/가공/분석 (BI도구, Python, R, 스프레드시트) → 리포트 작성 및 발표

 

[고객 담당자] 효과적인 고객관리 전략 수립을 위한 데이터 분석

: 고객 세분화(RFM) → 고객 특성 분석 → 마케팅 전략

+ 제품/유통/배송 등

➡️ 비즈니스 가치 창출

 

---

✔️ 우리 조가 사용하기로 한 테이블과 컬럼은 위와 같다.

 

 

✔️ 분석 전 기본적인 정보를 확인해보았다.

✔️ 고객 분석을 주제로 설정하고 분석할 수 있는 주제들을 결정해보았다. 

 

 

 

✔️ 우리가 설정한 RFM 기준은 다음과 같다.

 

 

그리고 각자 파트를 맡아 분석을 진행했다.

 

 

➡️ 나는 시간대별 주문건수 변화에 대해 분석해보았고 아침시간부터 증가하는 주문건수를 확인할 수 있었다.

 

/********************************
 * 4. 시간대별 소비/매출 변화 (현정)
 *********************************/
with
cte_table as(
select *
from final_table ft
)
, cte_time as(
select extract(hour from order_purchase_timestamp) 시간
	, trunc(extract(hour from order_purchase_timestamp)/6) timesix
	, count(distinct order_id) 주문수
from cte_table
group by 1
order by 1
)
select case 
	when timesix=0 then '00:00~05:59'
	when timesix=1 then '06:00~11:59'
	when timesix=2 then '12:00~17:59'
	when timesix=3 then '18:00~23:59'
	end as timesix_re
	, sum(주문수) totalnum
from cte_time
group by 1
order by 1;

 

 

 

🔎 배운점

 

to_char로 timestamp를 년-월-일만 뽑아준 후 빼려니 text끼리 계산할 수 없다는 오류발생

order_purchase_timestamp를 date로 감싸면 해결!

 

with
cte_table as(
select *
from final_table ft
)
, cte_date as(
	select distinct customer_unique_id, order_id, payment_value 
		, max(date(order_purchase_timestamp)) over() 기준일
		, max(date(order_purchase_timestamp)) over() - date(order_purchase_timestamp) "구매일_diff"
	from cte_table
)
, cte_rfm as(
	select customer_unique_id
		, min(구매일_diff) recency
		, count(distinct order_id) frequency
		, sum(payment_value) monetary
	from cte_date
	group by 1
)
, cte_rfe_score as(
	select customer_unique_id 
		, recency, ntile(10) over(order by recency desc) as r
		, frequency, ntile(10) over(order by frequency) as f
		, monetary, ntile(10) over(order by monetary) as m
	from cte_rfm
)
, cte_total as(
	select *
		, r+f+m as total
	from cte_rfe_score
	order by total asc
)
select total, count(distinct customer_unique_id)
from cte_total
group by 1
order by 1 desc;

 

[주의할점]

우리가 create table로 만든 table에는 중복되는 값이 많을 수 있다! (여러 개의 테이블을 left join 했기 때문에)

그래서 항상 개수를 체크해야 하고, 첫 select에 distinct를 가져와야 할 것 같다.

 

항상 테이블을 만들 때 늘어났는지 안늘어났는지 확인하고, 늘어났을 경우 그 이유를 생각해보고 합당한지 확인!

 

, cte_rfm as(
	select customer_unique_id
		, coalesce (min(구매일_diff),0) recency
		, coalesce (count(distinct order_id),0) frequency
		, coalesce (sum(payment_value),0) monetary
	from cte_date
	group by 1
)

 

 

✍️ 마무리하며

 

2일에 걸쳐 진행될 SQL 미니 프로젝트!

저번주부터 흥미로운 분석 기법을 많이 배워서 복습도 할 겸 다양하게 사용해보려고 노력했다.

내일까지 잘 마무리해봐야겠다!! 💪💪

---

* 유데미 큐레이션 바로가기 : https://bit.ly/3HRWeVL

* STARTERS 취업 부트캠프 공식 블로그 : https://blog.naver.com/udemy-wjtb

📌 본 후기는 유데미-웅진씽크빅 취업 부트캠프 4기 데이터분석/시각화 학습 일지 리뷰로 작성되었습니다.