* 자료 출처: Kaggle - Marketing Insights for E-Commerce Company (2019)
▼ 프로젝트 기획안
더보기
1. 프로젝트 명
(주)머천재의 매출 극대화 TF
2. 프로젝트 개요
- 온라인 커머스 기업 (주)머천재는 2019년초 사이트 개편 후 매출 상승세 기록 중
- 2020년 전년대비 연간 총 매출액 30% 상승을 목표로 효과적인 마케팅 전략 마련 필요
3. 프로젝트 목적
- 전년대비 연 매출 30% 상승 목표 달성을 위한 효과적인 마케팅 전략 방향 제안
4. 프로젝트 목표
- 고객 세그먼트 분석을 통한 효과적인 마케팅 전략 마련
- 지역 타겟팅 마케팅 필요성 검토를 위한 지역별 매출 데이터 분석
- 고객별 쿠폰 사용 여부 분석을 통한 쿠폰 설계 방향 제시
1. 고객 세그먼트 기준 마련을 위한 데이터 검토
- 고객 평균 구매 주기 확인
#고객ID별 구매 날짜
with order_date as (select CustomerID,
str_to_date(Transaction_Date,'%m/%d/%Y') Date
from online_sales os
group by 1, 2
order by 1 ),
#고객 재구매 날짜(구매일 - 이전 구매일)
order_frequency as (select CustomerID,
Date,
lead(Date, 1) OVER(partition by CustomerID order by Date) following_date,
datediff(lead(Date, 1) OVER(partition by CustomerID order by Date), date) diff
from order_date )
#전체 평균 구매주기
#한 번만 산 사람 - null값 - 제외
select AVG(diff) total_avg_order_frequency
from order_frequency
where diff is not null ##결과값: 54.3
▶ 2회 이상 고객의 경우, 평균 54일 주기로 재구매 진행
▶ 유예기간 포함 3개월 이내 구매 유저를 활성 유저로 판단
▶ 그 외 세부 기준은 세그먼트 분류 후 고객수 비중에 따라 점진적으로 조정
2. 고객 세그먼트 분류 기준
| 구분 | 내용 |
| 이탈 고객(lapsed_customer) | 마지막 6개월 이내 구매 이력이 없는 유저 |
| 잠재 이탈 고객(potential_lapsed_customer) | 마지막 3개월 이내 구매 이력이 없으나 6개월 이내 구매 이력이 있는 유저 |
| 신규고객(acquired_customer) | 마지막 3개월 이내 회원가입 및 구매 이력이 있는 유저 |
| 일반고객(general_customer) | 마지막 3개월 이내 전체 평균 주문당 매출액(187달러) 미만 OR 평균 구매 건수(13건) 미만 AND 회원가입 경과일 4개월 이상 |
| 충성고객(loyal_customer) | 마지막 3개월 이내 전체 평균 주문당 매출액(187달러) 이상 AND 평균 구매 건수(13건) 이상 AND 회원가입 경과일 4개월 이상 |
- 세그먼트 분류 (with 절만)
with
#최근 3개월 평균 주문수 및 매출액 (회원가입 4개월 이상 경과)
average_sales as (select avg(num_of_order_by_customer) avg_order,
AVG( avg_amount_by_customer ) avg_amount
from (select sales.CustomerID,
count(distinct sales.Transaction_ID) num_of_order_by_customer,
sum(sales.Quantity*sales.Avg_price) / count(distinct sales.Transaction_ID) avg_amount_by_customer
from online_sales sales
left join customersdata cus on sales.CustomerID = cus.CustomerID
where month(str_to_date(sales.Transaction_Date,'%m/%d/%Y')) between 10 and 12
and cus.Tenure_Months > 3
group by sales.CustomerID ) average),
#이탈 고객 - 마지막 6개월 이내 구매 이력이 없는 유저
s1_lapsed as (select distinct CustomerID lapsed_id
from online_sales o_1_12
except
select distinct CustomerID
from online_sales o_7_12
where month(str_to_date(Transaction_Date,'%m/%d/%Y')) between 7 and 12),
#이탈 잠재 고객 - 마지막 3개월 이내 회원가입 및 구매 이력이 있는 유저
s2_potential as (select distinct CustomerID potential_id
from online_sales o_7_12
where month(str_to_date(Transaction_Date,'%m/%d/%Y')) between 7 and 12
except
select distinct CustomerID active_id
from online_sales o_10_12
where month(str_to_date(Transaction_Date,'%m/%d/%Y')) between 10 and 12),
#신규 고객 - 마지막 3개월 이내 회원가입 및 구매 이력이 있는 유저
s3_acquired as (select distinct sales.CustomerID acquired_id
from online_sales sales
left join customersdata cus on sales.CustomerID = cus.CustomerID
where month(str_to_date(sales.Transaction_Date,'%m/%d/%Y')) between 10 and 12
and cus.Tenure_Months <= 3
group by sales.CustomerID),
#일반 고객 - 마지막 3개월 이내 구매이력 & 회원가입 경과일 4개월 이상
AND 전체 평균 주문당 매출액(187달러) 미만 OR 평균 구매 건수(13건) 미만
s4_general as (select distinct sales.CustomerID general_id
from online_sales sales
left join customersdata cus on sales.CustomerID = cus.CustomerID
where month(str_to_date(sales.Transaction_Date,'%m/%d/%Y')) between 10 and 12
and cus.Tenure_Months > 3
group by sales.CustomerID
having COUNT(distinct sales.Transaction_ID) < (select avg_order
from average_sales)
or sum(Quantity*Avg_price) / count(distinct Transaction_ID) < (select avg_amount
from average_sales ) ),
#충성 고객 - 마지막 3개월 이내 구매이력 & 회원가입 경과일 4개월 이상
AND 전체 평균 주문당 매출액(187달러) 이상 AND 평균 구매 건수(13건) 이상
s5_loyal as (select distinct sales.CustomerID loyal_id
from online_sales sales
left join customersdata cus on sales.CustomerID = cus.CustomerID
where month(str_to_date(sales.Transaction_Date,'%m/%d/%Y')) between 10 and 12
and cus.Tenure_Months > 3
group by sales.CustomerID
having COUNT(distinct sales.Transaction_ID) >= (select avg_order
from average_sales)
and sum(Quantity*Avg_price) / count(distinct Transaction_ID) >= (select avg_amount
from average_sales ) ),
#상기 1~5개 세그먼트를 하나의 테이블로 생성
segment_table as (select CustomerID ,
Transaction_ID,
Transaction_Date ,
Product_SKU ,
Product_Description ,
Product_Category ,
Quantity ,
Avg_Price ,
Delivery_Charges ,
Coupon_Status ,
case when os.CustomerID = s1.lapsed_id then '1_lapsed_customer'
when os.CustomerID= s2.potential_id then '2_potential_lapsed_customer'
when os.CustomerID= s3.acquired_id then '3_acquired_customer'
when os.CustomerID= s4.general_id then '4_general_customer'
when os.CustomerID= s5.loyal_id then '5_loyal_customer'
end segment
from online_sales os
left join s1_lapsed s1 on os.CustomerID= s1.lapsed_id
left join s2_potential s2 on os.CustomerID= s2.potential_id
left join s3_acquired s3 on os.CustomerID= s3.acquired_id
left join s4_general s4 on os.CustomerID= s4.general_id
left join s5_loyal s5 on os.CustomerID= s5.loyal_id )
- 세그먼트별 데이터 확인
#WITH절 segment_table 활용
#인사이트 찾기 위해 여러 지표 추출
select segment,
count(distinct CustomerID) cnt,
(count(distinct CustomerID) / (select count(distinct CustomerID)from online_sales os )) *100 cnt_ratio,
COUNT(distinct Transaction_ID) number_order,
COUNT(distinct Transaction_ID) / count(distinct CustomerID) avg_order,
COUNT(distinct Transaction_ID) / count(distinct CustomerID) avg_order_per_customer,
avg(Quantity) avg_quantity,
sum(Quantity) total_quantity,
round(sum(Quantity*Avg_Price) / COUNT(distinct Transaction_ID),0) avg_amount_per_order,
round(sum(Quantity*Avg_Price) / count(distinct CustomerID),0) avg_amount_per_customer,
round(sum(Quantity*Avg_Price),0) total_amount
from segment_table
group by segment
▶ 결과값
- 세그먼트별 카테고리 주문수 (그래프용)
#WITH절 segment_table 활용
#피봇테이블 형태로 만들어서 차트 생성에 활용
select Product_Category,
max(if(segment='1_lapsed_customer', cnt_order,0)) 1_lapsed_order_cnt,
max(if(segment='2_potential_lapsed_customer', cnt_order,0)) 2_potential_lapsed_order_cnt,
max(if(segment='3_acquired_customer', cnt_order,0)) 3_acquired_order_cnt,
max(if(segment='4_general_customer', cnt_order,0)) 4_general_order_cnt,
max(if(segment='5_loyal_customer', cnt_order,0)) 5_loyal_order_cnt
from (select Product_Category,
segment,
count(Transaction_ID) cnt_order
from segment_table
group by Product_Category, segment ) a
group by Product_Category
▶ 결과값 시각화 예시
[회고]
- 맨 처음 고객 세그먼트를 나누는 기준을 논의하는 과정이 어려웠음. 어떤 근거가 타당할지 등
- 세그먼트 기준이 머리로는 정리 됐는데, SQL 코드로 풀자니 한 없이 길어지는 것 같아 효율적인 코드일지 의문이 들음
- 일단 WITH절로 분류를 마치고 나니 인사이트를 뽑는 것이 무척 유용했음
'TIL' 카테고리의 다른 글
| [240117] 기초 프로젝트: 피드백 내용 정리 및 최종 아젠다 (0) | 2024.01.17 |
|---|---|
| [240116] 기초 프로젝트: SQL을 활용한 코호트 분석 (0) | 2024.01.16 |
| [240112] 파이썬: 코드카타 16 (0) | 2024.01.12 |
| [240111] 파이썬: 코드카타 15 (1) | 2024.01.11 |
| [240110] 플로우 차트 & SQL/파이썬: 코드카타 (1) | 2024.01.10 |
