[240116] 기초 프로젝트: SQL을 활용한 코호트 분석

* 자료 출처: Kaggle - Marketing Insights for E-Commerce Company (2019)

 

▼ 프로젝트 기획안  

1. 프로젝트 명

(주)머천재의 매출 극대화 TF

 

2. 프로젝트 개요

- 온라인 커머스 기업 (주)머천재는 2019년초 사이트 개편 후 매출 상승세 기록 중
- 2020년 전년대비 연간 총 매출액 30% 상승을 목표로 효과적인 마케팅 전략 마련 필요

 

3. 프로젝트 목적
- 전년대비 연 매출 30% 상승 목표 달성을 위한 효과적인 마케팅 전략 방향 제안
    
4. 프로젝트 목표
 - 고객 세그먼트 분석을 통한 효과적인 마케팅 전략 마련
 - 지역 타겟팅 마케팅 필요성 검토를 위한 지역별 매출 데이터 분석
 - 고객별 쿠폰 사용 여부 분석을 통한 쿠폰 설계 방향 제시

 

1. 코호트 분석  

- 월 기준으로 구매 고객 잔존율 확인  

#코호트 분석_전처리 
with first_order_by_month as (select CustomerID, 
                                 month ( min(str_to_date(Transaction_Date,'%m/%d/%Y') )) frist_month
                            from online_sales
                            group by CustomerID ),
    customer_by_month as ( select CustomerID ,
                                  month(str_to_date(Transaction_Date,'%m/%d/%Y')) order_month ,
                                  count(distinct CustomerID) CNT , 
                                  avg(Quantity*Avg_Price) AVG_amount
                              from online_sales
                              group by CustomerID, month(str_to_date(Transaction_Date,'%m/%d/%Y')) )


#코호트 분석_월별 고객수 변화 추이					  
select frist_month,
	   count(distinct CustomerID) num_of_customer,
		sum(if (order_month=1, 1, 0)) 1m,
		sum(if (order_month=2, 1, 0)) 2m,
		sum(if (order_month=3, 1, 0)) 3m,
		sum(if (order_month=4, 1, 0)) 4m,
		sum(if (order_month=5, 1, 0)) 5m,
		sum(if (order_month=6, 1, 0)) 6m,
		sum(if (order_month=7, 1, 0)) 7m,
		sum(if (order_month=8, 1, 0)) 8m,
		sum(if (order_month=9, 1, 0)) 9m,
		sum(if (order_month=10, 1, 0)) 10m,
		sum(if (order_month=11, 1, 0)) 11m,
		sum(if (order_month=12, 1, 0)) 12m
  from customer_by_month
  join first_order_by_month using (CustomerID)
  group by frist_month
  order by frist_month


#코호트 분석_월별 고객수 변화 추이 비율(백분율) 					  
select frist_month,
	   count(distinct CustomerID) num_of_customer,
	   round( sum(if (order_month=1, 1, 0)) /  count(distinct CustomerID)*100, 2) 1m,
	   round( sum(if (order_month=2, 1, 0)) /  count(distinct CustomerID)*100, 2) 2m,
	   round( sum(if (order_month=3, 1, 0)) /  count(distinct CustomerID)*100, 2) 3m,
	   round( sum(if (order_month=4, 1, 0)) /  count(distinct CustomerID)*100, 2) 4m,
	   round( sum(if (order_month=5, 1, 0)) /  count(distinct CustomerID)*100, 2) 5m,
	   round( sum(if (order_month=6, 1, 0)) /  count(distinct CustomerID)*100, 2) 6m,
	   round( sum(if (order_month=7, 1, 0)) /  count(distinct CustomerID)*100, 2) 7m,
	   round( sum(if (order_month=8, 1, 0)) /  count(distinct CustomerID)*100, 2) 8m,
	   round( sum(if (order_month=9, 1, 0)) /  count(distinct CustomerID)*100, 2) 9m,
	   round( sum(if (order_month=10, 1, 0)) /  count(distinct CustomerID)*100, 2) 10m,
	   round( sum(if (order_month=11, 1, 0)) /  count(distinct CustomerID)*100, 2) 11m,
	   round( sum(if (order_month=12, 1, 0)) /  count(distinct CustomerID)*100, 2) 12m 
  from customer_by_month
  join first_order_by_month using (CustomerID)
  group by frist_month
  order by frist_month

 

▶결과값 

 

[회고]

- 코호트 분석 개념이 부족해서 구글링을 통해 어떤 지표를 기준 값으로 잡아야 하는지 살펴봄

└ 기준 자체가 중요할 것으로 보여 추후에는 일/주/월/분기/년 등으로 단위를 다르게 설정해 살펴보면 좋을듯 

- 피봇테이블 형태를 뽑아낸다고 생각하고 코드를 작성하니 생각보다 많이 헤매지 않고 결과값을 뽑아냄 

 

* 참고

- 고동의 데이터 분석 ｜[실습] SQL을 활용한 리텐션 분석하기｜https://schatz37.tistory.com/16