* 자료 출처: 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
'TIL' 카테고리의 다른 글
[240118] SQL: 코드카타 106~107 & 파이썬: 코드카타 18 (0) | 2024.01.18 |
---|---|
[240117] 기초 프로젝트: 피드백 내용 정리 및 최종 아젠다 (0) | 2024.01.17 |
[240115] 기초 프로젝트: SQL을 활용한 고객 세그먼트 분류 (0) | 2024.01.15 |
[240112] 파이썬: 코드카타 16 (0) | 2024.01.12 |
[240111] 파이썬: 코드카타 15 (1) | 2024.01.11 |