📚 주제

이커머스 이벤트 히스토리 분석

📚 데이터 소스

2019-10 ~ 2020-02 까지 5개월 간의 중형 코스메틱 온라인 샵의 이벤트 히스토리

 

eCommerce Events History in Cosmetics Shop

This dataset contains 20M users' events from eCommerce website

www.kaggle.com

📚 분석 기간

2024-12-03 ~ 2024-12-05, 3일

📚 분석 목표

  • 브랜드 분석을 통한 실무 중심의 인사이트 도출 및 액션 플랜 제시
  • 경쟁사와의 비교 분석을 통한 차별화 전략 도출

📚 세부 주제

쇼핑몰에 입점한 브랜드 'Grattol'의 분석 및 인사이트 도출

  • 퍼널 분석
  • 시간/요일별 분석
  • RFM 고객 분석

📚 EDA 

❕ 별도 포스팅

 

이커머스 데이터 분석 프로젝트 1. EDA

목차1주제2데이터 소스3분석 기간4 목표 설정 5 세부 주제 6 EDA 7분석 과정8인사이트9레퍼런스 📚 주제이커머스 이벤트 히스토리 분석📚 데이터 소스2019-10 ~ 2020-02 까지 5개월 간의 중형 코스메

cho-dam.tistory.com

📚 분석 과정

브랜드 별 매출 조회

  • 브랜드 별 매출을 확인해 2위 기업인 Grattol을 중심 브랜드로 선정하고, 매출 1위 Runail, 3위 Irisk를 경쟁사로 비교 분석함
  • 문제 정의 : 우리 브랜드 grattol의 매출은 전체 2등이지만 구매 전환율은 11.6%로 평균 이하로 낮다
  • 분석 목표 : 구매 전환율이 낮은 이유를 분석해보고 전환율을 높일 수 있는 방법을 찾아보자

더보기 
WITH uni AS(
	SELECT * FROM oct_2019 o
	UNION
	SELECT * FROM nov_2019 n
	UNION
	SELECT * FROM dec_2019 d
	UNION
	SELECT * FROM jan_2020 j
	UNION
	SELECT * FROM feb_2020 f
)
,seg2 AS(
SELECT brand
	  ,event_type
	  ,count(*) event_cnt
	  ,sum(price) event_price
FROM uni
WHERE price>0
GROUP BY 1,2
)
,piv AS(
SELECT brand,
	   max(if(event_type='view', event_cnt,0)) view_cnt,
 	   max(if(event_type='cart', event_cnt,0)) cart_cnt,
 	   max(if(event_type='remove_from_cart',event_cnt,0)) remove_cnt,
 	   max(if(event_type='purchase',event_cnt,0)) purchase_cnt,
 	   max(if(event_type='purchase',event_price,0)) brand_total_price
FROM seg2
GROUP BY 1
)
SELECT  brand
	   ,view_cnt
	   ,cart_cnt
	   ,remove_cnt
	   ,purchase_cnt
	   ,ROUND(brand_total_price) AS total_price
	   ,ROUND(purchase_cnt/view_cnt,3) AS conversion
FROM piv
ORDER BY total_price DESC;

퍼널분석 

1. 고객 별로 첫 발생 이벤트 확인

  • user 별로 발생한 첫 이벤트 view외에 cart, remove_from_cart도 있음

  •  원인 추론
  1. 구글애널리틱스의 집계 상의 특성 : 유저는 계속 쇼핑몰에 머물러있었는데 자정이 지나 세션이 리뉴얼되며 id가 바뀜
  2. 쇼핑몰 UI 자체의 특징 : 상세페이지를 조회하지 않고도 장바구니에 담을 수 있는 UI의 쇼핑몰 있음(ex. 러시아 쇼핑몰 OZON)

2. 퍼널 분석 방법 선정

  • 실제 쇼핑몰의 모습을 정확히 알 수 없어 사용자의 행동 순서를 정할 수 없으므로 '단순집계퍼널'로 분석 결정
  • 퍼널 분석의 다양한 방법론
  1. `연속 순서 퍼널` : 사용자 별로 view → cart → purchase의 순서(혹은 설정한 다른 순서)대로 연속으로 간 경우만 추려서 분석
  2. `클로즈드 퍼널` : 사용자 별로 view → cart → purchase의 순서(혹은 설정한 다른 순서)대로 간 경우만 추려서 분석
    (단, 그 순서의 사이에 어떤 다른 이벤트가 발생했든 상관 없이 집계)
  3. `단순 집계 퍼널` : 순서에 관계 없이 view, cart, purchase의 전체 숫자를 기반으로 분석 

3. Grattol의 전환율 분석

  • Grattol의 전환율을 경쟁사와 비교함
  • 카트에 담은 후 구매하는 전환율은 경쟁사와 유사하나, 상세페이지 조회(view) 후 카트(cart), 구매(purchase)로 이어지는 비율 낮음

4. Grattol의 이탈률 분석

  • 경쟁사에 비해 장바구니에 상품을 담고 삭제하는 비율과 조회 후 구매 없이 이탈하는 유저의 비율이 높음

더보기 
-- grattol과 경쟁사의 전환율 및 이탈률 구하기
-- 전체 데이터 유니온

WITH uni as(
	select * from ecommerce.oct_2019 o
	union
	select * from ecommerce.nov_2019 n
	union
	select * from ecommerce.dec_2019 d
	union
	select * from ecommerce.jan_2020 
	union
	select * from ecommerce.feb_2020 f
),a AS( -- userid와 usersession 합쳐서 user 식별
SELECT CONCAT(user_id,user_session) AS id
			,Event_type
			,rank() OVER (PARTITION BY CONCAT(user_id,user_session) ORDER BY event_time) as ranking
			,event_time
FROM uni
),b as (-- view를 첫 세션으로 가지는 경우 / 단순집계퍼널에서는 이 부분만 삭제
SELECT *
FROM a
WHERE ranking=1 and event_type = 'view'
),t1 as( -- grattol과 경쟁사 추출
select brand
			,event_type
			,concat(user_id,user_session) as id
FROM uni
where brand in ('grattol','runail','irisk')
),funnel as ( -- 이벤트 타입 별 숫자
select brand,
			 count(case when event_type='view' then 1 end) view_cnt
			,count(case when event_type='cart' then 1 end) cart_cnt
			,count(case when event_type='purchase' then 1 end) purchase_cnt
			,count(case when event_type='remove_from_cart' then 1 end) remove_cnt
from t1
group by brand
) -- 전환율, 이탈률 측정
select brand
			,(cart_cnt/view_cnt)*100 as vc_conversion -- 방문 후 카트에 담은 전환율
			,(purchase_cnt/cart_cnt)*100 as cp_conversion -- 카트에 담고 구매한 전환율
			,(purchase_cnt/view_cnt)*100 as vp_conversion -- 방문 후 구매한 전환율
			,(remove_cnt/cart_cnt)*100 as remove_rate -- 장바구니에 담고 삭제한 비율
			,(1-(purchase_cnt/cart_cnt))*100 as cart_abn_rate -- 장바구니에 담고 구매 안 한 비율(장바구니 이탈률)
			,(1-(purchase_cnt/view_cnt))*100 as churn_rate -- 방문 후 구매 안 한 비율(이탈률)
from funnel;

가격대 분석 

  • Grattol의 평균 가격은 Runail이나 Irisk에 비해 1.7배 높음
  • Grattol 판매량의 90%가 해당 쇼핑몰 가격대 기준 ‘중간가’ 제품이며 Runail과 Irisk는 판매량의 80% 이상이 ‘저가’ 제품임
  • 다만, 경쟁사 Runail에서는 고가 제품의 판매량 대비 매출 비율이 높아 해당 쇼핑몰 기준 초고가 상품의 판매가 동시에 이루어지고 있으며, 검색을 통해 Runail 제품군에 네일 케어 가전제품이 있음을 확인함

더보기 
-- grattol과 경쟁사의 매출, 판매가격, 판매량 구하기
WITH uni as(
	select * from ecommerce.oct_2019 o
	union
	select * from ecommerce.nov_2019 n
	union
	select * from ecommerce.dec_2019 d
	union
	select * from ecommerce.jan_2020 j
	union
	select * from ecommerce.feb_2020 f
)
select
			brand,
			round(avg(price),2) '평균 가격',
			count(*) '판매량',
			round(sum(price),2) '매출'
from uni
where event_type = 'purchase' and price >0 and brand in ('grattol', 'irisk', 'runail')
group by brand
order by sum(price) desc

시간, 요일 별 사용자 분석

1. 쇼핑몰 전체 사용자 기준

  • 쇼핑몰 전체 사용자 대상 쇼핑 선호 시간 : 목,금요일 저녁 시간(18~20시)
  • 주말보다 목, 금요일의 조회, 장바구니, 구매 이벤트 발생 수가 높음
  • 탐색(조회) 활동은 저녁 시간에(18~20시) 가장 활발하고, 새벽(1~2시) 사이에 구매 전환율이 높음
    • 원인 추론 
      • 주말에는 외출을 하기 때문에 온라인 쇼핑 빈도가 낮음
      • 점심 시간과 퇴근 및 저녁 식사 이후의 휴식 시간에 쇼핑몰 접속 및 구매가 활발함
  • 구매 수가 가장 많은 날의 직전 일에는 장바구니에 담은 횟수도 가장 높음
더보기 
ㅇㅇㅇㅇ

 

2. Grattol 구매 고객 기준

  • 요일별 전환율과 구매 발생 수
    • 조회(view)는 주말보다 주중에 높음
    • 장바구니(cart)는 목,금요일에 가장 많이 담음
    • 구매(purchase)는 목, 금요일에 가장 전환율 높음

요일 View수 Cart 수  Purchase 수
월 32,462 11,382 3,020
화 33,255 11,585 3,096
수 33,810 11,664 2,886
목 34,636 12,756 3,378
금 33,410 12,295 3,358
토 30,126 10,629 2,628
일 29,344 10,882 2,786
  • 시간별 구매 발생 수 
    • 조회는11시~13시에 잠시 높았다가 19~20시에 가장 높아짐
    • 장바구니는 19~20시에 가장 높음
    • 구매는 19~20시와 취침 전 시간에 높음

3. Runail 구매 고객 기준

  • 요일별 전환율과 구매 발생 수
    • 조회(view)는 화,수,목 등 주말보다 주중에 높음
    • 장바구니(cart)는 목요일에 가장 많이 담음
    • 구매(purchase)는 화, 목, 금요일에 가장 높음

요일 View 수 Cart 수  Purchase 수
월 54,239 23,513 6,594
화 55,473 24,049 6,925
수 55,581 24,536 6,539
목 56,170 25,032 7,179
금 53,959 23,422 6,999
토 49,528 21,064 5,571
일 50,805 22,543 5,883
  • 시간별 구매 발생 수 
    • 조회는11시~12시와 18~19시에 가장 높음
    • 장바구니는 12~13시와 18~20시에 가장 높음, 20시 이후로 2배 가까이 떨어지는 경향
    • 구매는 11~12시와 19시에 가장 높음

4. Irisk 구매 고객 기준

  • 요일별 전환율과 구매 발생 수
    • 조회(view)는 수, 목요일에 높음
    • 장바구니(cart)는 수, 목요일에 높음
    • 구매(purchase)는 목요일에 가장 높음

요일 View 수 Cart 수  Purchase 수
월 39,261 19,455 5,501
화 39,896 19,286 5,713
수 41,309 20,204 5,504
목 41,041 20,127 5,949
금 39,026 18,399 5,667
토 35,405 16,679 4,537
일 36,923 18,189 4,850
  • 시간별 구매 발생 수 
    • 조회는12~13시에 잠시 높았다가 19~20시에 가장 높음
    • 장바구니는 18~20시에 가장 높음
    • 구매는 11시에 잠시 높았다가 19시에 가장 높음

더보기 
사용한 코드

Grattol 고객 RFM 분석

  • 목적 : RFM 기반 Grattol의 구매 고객을 세분화 하고, 그룹별 우선순위를 설정하여 맞춤형 마케팅 솔루션을 제안하고자 함
  • 데이터 전처리 과정

  • RFM은 평균치를 기준으로 계산함 (기간의 촉박함으로 평균치로 설정했으나 나중에는 가중치를 고려하여 구해보자..)
  • `Recency` (고객의 평균 재구매 주기)
    • 2020-03-01 기준 고객이 가장 최근에 구매한 날짜로부터 바로 직전 구매 주기의 평균 값을 R 지표로 산출 (2020-01-21 )
    • 재구매 내역이 없는 고객은 제외
  • `Frequency` (고객의 평균 구매 빈도)
    • 2019-10-01~2020-02-29 기간내 고객의 평균 구매건수를 F지표로 산출
  • `Monetary` (고객의 평균 구매 금액)
    • 2019-10-01~2020-02-29 기간내 고객 평균 구매 금액을 M 지표로 산출

더보기 
-- RFM 고객 세분화 
WITH total AS (
	SELECT * FROM feb_2020 f WHERE PRICE>0 AND event_type ='purchase' AND brand='grattol'
	UNION
	SELECT * FROM dec_2019 d WHERE PRICE>0 AND event_type ='purchase'AND brand='grattol'
	UNION
	SELECT * FROM nov_2019 n WHERE PRICE>0 AND event_type ='purchase'AND brand='grattol'
	UNION
	SELECT * FROM oct_2019 o WHERE PRICE>0 AND event_type ='purchase'AND brand='grattol'
	UNION
	SELECT * FROM jan_2020 j WHERE PRICE>0 AND event_type ='purchase'AND brand='grattol'
),seg1 AS ( -- 일자별 유저 구매 건수/금액 전처리
	SELECT DATE(event_time) AS days 
		  ,user_id
		  ,COUNT(DISTINCT product_id) AS p_cnts -- 제품 구매 건수(하루에 몇개 구매했는지)
		  ,SUM(price) AS total_price -- 구매액(하루에 얼마 지불했는지)
	FROM total
	GROUP BY days,user_id
),lag_add AS (
	SELECT user_id
		  ,days
		  ,LAG(days,1) OVER (PARTITION BY user_id ORDER BY days) AS prev_days
		  ,p_cnts -- 제품 구매 건수(하루에 몇개 구매했는지)
		  ,total_price -- 구매액(하루에 얼마 지불했는지)
	FROM seg1
),seg2 AS (
	SELECT user_id
		   ,MAX(days) AS last_order_date -- 고객의 가장 최근 구매 일자(=마지막 구매)
		   ,max(prev_days) AS prev_order_date
	   	   ,datediff(MAX(days),max(prev_days)) AS diff -- 고객의 재방문 주기
		   ,COUNT(days) AS order_cnts -- 고객이 해당 기간에 몇번 구매했는지
		   ,SUM(total_price) AS sales -- 고객이 해당 기간에 얼마 지불했는지
	FROM lag_add
	GROUP BY user_id
),seg3 AS (
SELECT CASE WHEN last_order_date>'2020-01-21' THEN 'recent' -- 마지막구매와 그 직전 구매 사이의 기간의 평균(재구매 아닌 고객 제외)
			ELSE 'past'
		END AS recency
		,CASE WHEN ORDER_cnts>1.22 THEN 'high' -- 평균치
			ELSE 'low'
		END AS frequency
		,CASE WHEN sales>15.53 THEN 'high' -- 평균치
			ELSE 'low'
		END AS monetary
		,COUNT(user_id) AS users
FROM seg2
GROUP BY 1,2,3
ORDER BY 1 DESC,2,3
)
SELECT recency
	  ,frequency
	  ,monetary
	  ,users
	  ,ROUND(users/sum(users) OVER (),2) AS portion
FROM seg3 ;

 

-- 세분화 기준 쿼리 작성
WITH total AS (
	SELECT * FROM feb_2020 f WHERE PRICE>0 AND event_type ='purchase' AND brand='grattol'
	UNION
	SELECT * FROM dec_2019 d WHERE PRICE>0 AND event_type ='purchase'AND brand='grattol'
	UNION
	SELECT * FROM nov_2019 n WHERE PRICE>0 AND event_type ='purchase'AND brand='grattol'
	UNION
	SELECT * FROM oct_2019 o WHERE PRICE>0 AND event_type ='purchase'AND brand='grattol'
	UNION
	SELECT * FROM jan_2020 j WHERE PRICE>0 AND event_type ='purchase'AND brand='grattol'
),seg1 AS ( -- 일자별 유저 구매 건수/금액 전처리
	SELECT DATE(event_time) AS days 
		  ,user_id
		  ,COUNT(DISTINCT product_id) AS p_cnts -- 제품 구매 건수(하루에 몇개 구매했는지)
		  ,SUM(price) AS total_price -- 구매액(하루에 얼마 지불했는지)
	FROM total
	GROUP BY days,user_id
),lag_add AS (
	SELECT user_id
		  ,days
		  ,LAG(days,1) OVER (PARTITION BY user_id ORDER BY days) AS prev_days
		  ,p_cnts -- 제품 구매 건수(하루에 몇개 구매했는지)
		  ,total_price -- 구매액(하루에 얼마 지불했는지)
	FROM seg1
),seg2 AS (
	SELECT user_id
		   ,MAX(days) AS last_order_date -- 고객의 가장 최근 구매 일자(=마지막 구매)
		   ,max(prev_days) AS prev_order_date
	   	   ,datediff(MAX(days),max(prev_days)) AS diff -- 고객의 재방문 주기
		   ,COUNT(days) AS order_cnts -- 고객이 해당 기간에 몇번 구매했는지
		   ,SUM(total_price) AS sales -- 고객이 해당 기간에 얼마 지불했는지
	FROM lag_add
	GROUP BY user_id
)
SELECT ROUND(AVG(IF(diff=0,null,diff)),0) AS r_avg -- 재방문 내역이 없는 유저 제외하고 평균 구매 주기 확인 (한번더 확인 필요. AVG 평균값이 맞는지)
	  ,ROUND(AVG(order_cnts),2) AS f_avg
	  ,ROUND(AVG(sales),2) AS m_avg
FROM seg2;
  • 월별 RFM 평균 추이 비교 : 특히, 12월은 평균 대비 구매 주기가 짧고, 구매 빈도는 비슷하나 평균 매출 하락(평균 대비 11%)
    • 원인으로는 연말 할인 행사로 객단가가 낮아졌을 가능성이 있음
    • 12월 이후 모든 지표가 이전 수준으로 회복된 것으로 보아 일시적인 현상으로 추측됨
더보기 
-- 월별 매출계, 평균 매출, PU,ARPPU 추이 확인
WITH total AS ( -- 환불 금액 전처리 후 월별 테이블 연결
	SELECT event_time,product_id,price, user_id,user_session FROM oct_2019 o WHERE PRICE>0 AND event_type ='purchase'
	UNION
	SELECT event_time,product_id,price, user_id,user_session FROM nov_2019 n WHERE PRICE>0 AND event_type ='purchase'
	UNION
	SELECT event_time,product_id,price, user_id,user_session FROM dec_2019 d WHERE PRICE>0 AND event_type ='purchase'
	UNION
	SELECT event_time,product_id,price, user_id,user_session FROM jan_2020 j WHERE PRICE>0 AND event_type ='purchase'
	UNION
	SELECT event_time,product_id,price, user_id,user_session FROM feb_2020 f WHERE PRICE>0 AND event_type ='purchase'
)
SELECT MONTH(event_time)
	  ,ROUND(SUM(price),0) AS daily_sales
	  ,ROUND(AVG(price),0) AS daily_avg_sales
	  ,COUNT(DISTINCT user_id) AS PU -- 구매 고객수 
	  ,COUNT(DISTINCT CONCAT(user_id,user_session)) AS cnts -- 고객의 구매 건수 
	  ,ROUND(SUM(price)/COUNT(DISTINCT user_id)) AS ARPPU
	  ,ROUND(COUNT(DISTINCT user_id)/COUNT(DISTINCT CONCAT(user_id,user_session)),2) AS re_po -- 재구매한 고객 비중 
FROM total
GROUP BY MONTH(event_time);
  • 우선순위 설정 
    • 고객 비중  : 그룹별 고객 수를 파악하여, 한정된 예산으로 마케팅을 진행할 그룹의 우선순위를 설정
    • 매출 비중 : 그룹별 매출 비중 확인 후 어떤 그룹이 매출 기여도 높은 지 확인

  • 이탈 위험 고객 그룹 → 고 가치 고객 그룹 → 충성 고객 그룹 순으로 우선순위를 설정하여 구매 촉진 및 마케팅 전략 제안 필요
    • 고객 비중은 이탈 위험 고객/신규고객 → 고 가치 고객 그룹 순으로  진행
    • 매출 비중은 이탈 위험 고객/고 가치 고객 → 충성 고객 그룹 순으로 진행
    • 고객/매출 비중이 높은 이탈 위험 고객과 매출 비중이 높은 고 가치 고객을 우선으로 마케팅 플랜을 제안 

📚 인사이트 및 제안

  • 상세페이지 보완
    • 전환율을 높일 수 있도록 grattol의 상세페이지에 다양한 제품 이미지 추가
    • 제품 가격이 경쟁사에 비해 비싸므로 고객을 설득할 수 있도록 타 제품과의 차별점, 상품 품질 강조, 사용자 만족도 후기 등 추가

참고) 한 쇼핑몰의 Grattol 상세페이지. 고객에 어필할 문구와 이미지가 부족하다

  • 장바구니에서 삭제 비율이 높으므로 장바구니에 담은 후 구매를 망설이는 고객 대상 적극적 CRM 제안

예시) 장바구니 전환율을 높일 CRM

  • 프리미엄 젤 네일 브랜드로 포지셔닝한 Grattol답게 고가의 홈 네일 케어 가전 제품 등 출시로 매출상승 기대
  • 고객 그룹 별 마케팅 액션 플랜
    • 이탈 위험 그룹 - Grattol 컬러 팔레트, 스티커, 네일 테스터 등 대중적 사은품을 제품과 함께 지급하여 락인 효과 기대
    • 고가치 고객 그룹 - 한번에 많은 금액을 구매하는 도매 고객 비중이 높으므로 도매가 멤버십 제안 링크 노출 
    • 충성 고객 그룹 - 등급별 맞춤 마케팅 제안

📚 레퍼런스

퍼널 분석 | Hackle

RFM 고객 세분화 분석에서 합리적으로 기준을 잡는 방법 | Datarian

eCommerce Events History 3. Funnel 분석 | blog

구매 데이터를 이용한 고객 세분화 기법 | blog

RFM 분석을 통한 고객 세분화 및 타겟 마케팅 전략 제안 | Dacon

RFM Segmentation | Optimove

“패션뷰티 이커머스 수요일에 집중, 가장 많이 즐기는 시간대는 월요일 오전 11시와 오후 2시” | 패션넷

이커머스 쇼핑 시계 | biginsight

[조미현의 fin코노미] “이건 못 참지, 잠들기 전 야식만큼 치명적인 유혹” | 한경신문

재방문이 많고 적음에 따른 고객 행동 차이 알아보기 | blog

 

티스토리툴바