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

 

 

💭 이번주 회고

 

    이번주 월-화에는 SQL에 대한 고객 분석 실습을 통해 SQL로 심화 분석하는 방법을 배웠다. 수-목은 미니 프로젝트를 통해 해커톤을 대비할 수 있는 시간을 가졌다. 금요일에는 프로젝트 피드백 및 최종평가를 진행하였다. 처음 배워보는 심화 쿼리들이 쉽지 않았지만 수업시간에 나간 진도를 차근차근 따라가다보니 원하는 데이터를 스스로 뽑아낼 수 있었던 신기한 경험이었다. 그리고 함께 조를 했던 언니오빠들과 함께 쿼리에 대해 고민하고 이야기하면서 많은 것을 배우게되어서 의미있는 한주였다. 무엇보다도 금요일 마지막 시간에 있었던 최종평가 준비로 일주일을 치열하게 보내면서 모두가 더욱 돈독해진 것 같다🥺 

 

 

👩‍💻 이번주 TIL

 

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

 

📍 국가별 고객 수, 구성비, 누적 구성비

-- 누적구성비
select 국가명, 고객수, 구성비
	, sum(구성비) over(order by 구성비 desc, 국가명) as 누적구성비
from(
	-- 구성비
	select *
		, sum(고객수) over() 총고객수
		, round(고객수 / sum(고객수) over() * 100, 1) as 구성비
	from (
		-- 고객수
		select country 국가명
			, count(distinct customer_id) as 고객수
		from customers c 
		group by 1
		order by 2 desc
	)a
)b;

 

📍 상관계수

-- 상관계수
, cte_corr_customer_amount_ordercnt as(
	select round(corr(고객수, 매출액)::numeric, 2) as "고객수_매출액"
	     , round(corr(매출액, 주문건수)::numeric, 2) as "매출액_주문건수"
	     , round(corr(고객수, 주문건수)::numeric, 2) as "고객수_주문건수"
	from cte_country_customercnt_amount_ordercnt
)

 

📍 고객별 매출액, 주문건수, 건당평균 주문

-- 고객별 매출액, 주문건수, 건당평균 주문
, cte_customer as(
select customer_id
	, count(distinct order_id) 주문건수
	, round(sum(amount)) 매출액
	, round(round(sum(amount))/count(distinct order_id))건당평균주문액
from cte_customers
where order_id is not null
group by 1
order by 3 desc
)
, cte_customer_rank as(
select customer_id
	, 주문건수
	, row_number () over(order by 주문건수 desc) as 주문건수순위 
	, 매출액
	, row_number () over(order by 매출액 desc) as 매출액순위 
	, 건당평균주문액
	, row_number () over(order by 건당평균주문액 desc) as 건당평균주문액순위 
from cte_customer
)
select *
from cte_customer_rank
order by 7;

 

📍 Decil 분석: 데이터를 10단계로 분할하여 중요도를 파악하는 방법

➡️ decil별 전략을 세울 수 있다.

 

📍 RFM 분석: 구매 가능성이 높은 고객을 식별하기 위한 데이터 분석 방법

• recency: 기준일 대비 며칠이 지났는가?
• frequency: 총구매횟수
• monetary: 총구매

-- rfm 분석
, cte_rfm as(
select customer_id, order_date, amount, order_id
	, max(order_date) over() 기준일
	, max(order_date) over() - order_date 구매일diff
from cte_customers
)
select customer_id 
	, min(구매일diff) "Recency"
	, count(distinct order_id) "Frequency"
	, round(sum(amount))"Monetary"
from cte_rfm
group by 1;

---

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

 

• RFM 분석 (세부)
  • 등급부여
  • 등급별 점수부여
  • 점수별 고객 수 확인 
• 재구매율

/**************************************
 * 연도별 재구매율
 **************************************/
-- 1.고객, 구매년도를 중복되지 않게 불러온다.
, cte_select as(
	select distinct customer_id, year
	from cte_customers
	order by 1
)
-- 2. 다음 연도와 매칭되도록 self join 한다.
, cte_next_join as(
	select c1.customer_id, c1.year, c2.year as next_year
	from cte_select c1 left join cte_select c2
		on c1.customer_id = c2.customer_id 
		and c1.year::int+1 = c2.year::int
)
-- 3. 연도별 구매자 수 집계하여 재구매율 계산
, cte_reorder_ratio as(
	select year
	     , count(year) as 당해구매자수
	     , count(next_year) as 다음해재구매자수
	     , round(count(next_year)/count(year)::numeric *100,2)||'%' as 재구매율
	from cte_next_join
	group by 1
)

 

• percentile_cont 함수: 백분위수를 연속값으로 계산
  ➡️ 입력 받은 수치 값이 어느 두 값 사이일 경우 두 값의 평균을 계산하여 리턴
• percentile_disc 함수: 백분위수를 이산값으로 반환
  ➡️ 입력 받은 수치 값이 어느 두 값 사이일 경우 두 값 중 작은 값을 리턴
• mode 함수: 최빈값 반환

 

📍 with 구문으로 테이블 생성

/************
 * 백분위 수 구하기
 */
with
numbers(n) as(
values (1), (1), (1), (3), (4), (5)
)
select percentile_cont(0.5) within group (order by n)
from numbers;

---

 

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

 

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

 

📍 미니 프로젝트

✔️ 사용 데이터

Brazilian E-Commerce Public Dataset by Olist

 

✔️ 피그마라는 협업 툴을 사용하여 목적, 분석 방향, 지표 기준 등을 설정하였다.

 

✔️ 코드 쉐어라는 툴을 사용하여 각자 담당한 코드를 짜고 오류 확인 및 수정 과정을 거쳤다.

새로운 툴들과 활용법을 많이 알게되어 좋았다👍

-- state 고객 수 top 3, city 고객 수 top 3 구하기
with
cte_table as(
	select *
	from final_table ft
)
, cte_region as(
	select distinct customer_state, customer_city ,  count(distinct customer_unique_id) 고객수
	from cte_table
	group by 1,2
	order by 3 desc
)
, cte_state as(
	select customer_state , 고객수
	from cte_region
	limit 3
)
, cte_rank as(
select	cs.customer_state, cr.customer_city, cr.고객수
	, row_number () over(partition by cs.customer_state order by cr.고객수 desc) rank
from cte_state cs left join cte_region cr 
on cs.customer_state = cr.customer_state
order by 1, 3 desc
)
-- rank top 3
select *
from cte_rank
where rank <= 3;

/********************************
 * 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;

---

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

 

오전 시간에는 발표를 진행하고 이에 대한 피드백을 받았다.

 

✔️ SQL은 기본 -> 도메인 지식, 보고서 작성, 발표, 커뮤니케이션이 더욱 중요하다.

기획, 목표 설정, 표현, 전달이 중요하다.

 

 

💡 마무리하며

 

3개월간 쉴틈없이 몰아치는 스케쥴에 조금 지치기는 하지만, 서로서로 아낌없는 응원과 격려를 보내주는 동료들 덕분에 이 쉼없는 여정을 무사히 견뎌올 수 있었다. 후회없는 마지막을 위해 다음주까지 모두모두 화이팅!!💪💪

---

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

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

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