1. 과제 목표
- 목적 : "제공된 주제를 선택하여 SQL을 활용해 EDA 를 해봅시다."
EDA란? 수집한 데이터가 들어왔을 때, 이를 다양한 각도에서 관찰하고 이해하는 과정
(이전 글 : [TIL] EDA와 통계분석))
2. 주제 선택
: 자전거 이용 데이터 / 아마존 책 리뷰 데이터 / 이커머스 이벤트 히스토리 데이터 / 건강보험 청구 의료 데이터 중
--> 총 4개 종류가 있었으나 우리 조는 이커머스 이벤트 히스토리 데이터를 선택했다.
그 이유로는 이미 갖고 있는 이커머스 도메인 및 마케팅 지식으로 좀더 수월하게 분석할 수 있을 것같았고
매출 기반 데이터이기 때문에 나올 수 있는 문제점과 인사이트가 명확할 것 같았다.
원본 데이터 위치 : 링크
(해당 데이터는 sql 로 불러들이기에 양이 매우 많은 것 같아서 5개월 치 > 1개월로 일부만 가져와 분석해보기로 했음)
3. 설명
- 이 파일에는 중형 화장품 온라인 스토어의 1개월(2020년 2월)의 행동 데이터가 포함되어 있습니다.
- 파일의 각 행은 이벤트를 나타냅니다.
- 모든 이벤트는 제품 및 사용자와 관련이 있습니다.
- 각 이벤트는 제품과 사용자 간의 다대다 관계와 같습니다.
*Event types
view: 사용자가 제품을 보았습니다cart: 사용자가 장바구니에 제품을 추가했습니다.remove_from_cart: 사용자가 장바구니에서 제품을 제거했습니다.purchase: 사용자가 제품을 구매했습니다
4. 방법론
1) EDA를 위해 나올 수 있는 소주제 생각해보기
매출에 영향을 주는 요인에는 뭐가 있을까?
뷰수, 구매 수
전환율
객단가 등이 있을 거같다
요인에 대한 디테일한 인사이트를 파악하기 위해 sql로 코딩을 짜보자!
2) 데이터 조작적 정의해보기
5. 분석 결과
1) 이벤트 타입별 분포
SELECT event_type,
count(DISTINCT user_id) as count
FROM events
Group by 1
ORDER BY 2 desc
인사이트 :
총 유저 수 39만 명 중 > view 37.9만 > cart 8.9만 > purchase 2.5만
전체 유저 중 구매한 유저 비율 2.5만 / 39만 = 6.59%
2) 브랜드별 퍼널 분석
SELECT
a.brand,
a.count_view, -- 상품 조회자 수 --
b.count_cart, -- 장바구니 유저 수 --
c.count_purchase, -- 구매자 수 --
(b.count_cart / NULLIF(a.count_view, 0)) * 100 AS CVR_cart, -- 장바구니 전환율 --
(c.count_purchase / NULLIF(b.count_cart, 0)) * 100 AS CVR_purchase, -- 구매 전환율 --
(c.count_purchase / NULLIF(a.count_view, 0)) * 100 AS CVR_total, --전체 구매 전환율
d.total_selling_price, -- 전체 매출 --
d.total_selling_price / NULLIF(c.count_purchase, 0) AS Avg_selling_price -- 객단가 --
FROM
-- 브랜드 별 상품 조회자 수를 구하는 쿼리문 --
(SELECT
brand,
COUNT(DISTINCT user_id) AS count_view
FROM
events
WHERE
event_type = 'VIEW'
AND brand IS NOT NULL
AND brand != ''
GROUP BY
brand) a
-- 브랜드 별 장바구니 유저 수를 구하는 쿼리문 --
LEFT JOIN
(SELECT
brand,
COUNT(DISTINCT user_id) AS count_cart
FROM
events
WHERE
event_type = 'cart'
AND brand IS NOT NULL
AND brand != ''
GROUP BY
brand) b ON a.brand = b.brand
-- 브랜드 별 상품 구매자 수를 구하는 쿼리문 --
LEFT JOIN
(SELECT
brand,
COUNT(DISTINCT user_id) AS count_purchase
FROM
events
WHERE
event_type = 'purchase'
AND brand IS NOT NULL
AND brand != ''
GROUP BY
brand) c ON a.brand = c.brand
-- 브랜드 별 전체 매출을 구하는 쿼리문--
LEFT JOIN
(SELECT
brand,
ROUND(SUM(price), 1) AS total_selling_price
FROM
events e
WHERE
event_type = 'purchase'
AND brand IS NOT NULL
AND brand != ''
GROUP BY
brand) d ON a.brand = d.brand
ORDER BY
total_selling_price DESC;
인사이트
1) 브랜드별 퍼널별 유저 수 ( view - cart - purchase )
-view 값 기준 상위 top 3 브랜드 : runail > irisk > grattol 순 (장바구니 및 구매 유저 수 기준으로 봐도 동일함)
-3개 브랜드의 장바구니 전환율 / 구매 전환율 / 전체 전환율 비교
- irisk의 장바구니 전환율이 나머지 브랜드 대비 높은편이나 구매전환율에서는 runail과 비슷한 수준 ( 35% )
- 전체 전환율에 있어서는 irisk가 18% 로 나머지 브랜드 대비 소폭 높은 편임
결론 : 일단 장바구니에 담게 하는 것부터 매출 증대의 시그날이 될 수 있음2)매출 / 객단 관점 비교
- 총 매출 기준 순위 : nunail > grattol > irisk
- 객단가 기준으로 봤을 때는 grattol > nunail > irisk
- nunail 과 irisk의 경우 객단 7불 대이나 grattol의 경우 14불로 2배 큼
- 구매전환율과 객단가는 음의 상관관계를 가지는 것으로 확인됨
- 즉 객단가가 높은 고관여 상품의 경우 객단가가 낮은 저관여 상품 대비 낮은 구매 전환율을 보임
- 그만큼 결제 단계에서 많은 이탈을 야기한다고 해석될 수 있음
6. 분석 과제를 통해 느낀 점
EDA 분석을 통해 먼저 주어진 데이터를 확인하고 팀원들과 역할 분담을 통해 협업으로 진행하니 더 많은 관점과 인사이트를 담을 수 있었다.
동일한 데이터임에도 불구하고 관점에 따라 문제 해결 주제에 따라 다른 해석이 존재할 수 있음을 느끼기도 하였고, 객관적 지표를 통해 공감할 수 있는 결과가 중요하다는 것또한 느꼈다.
