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

 

📚 주제

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

📚 데이터 소스

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

eCommerce Events History in Cosmetics Shop

📚 분석 기간

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

📚 분석 목표

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

📚 세부 주제

커머스 입점 브랜드 'Grattol'의 분석 및 인사이트 도출

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

---

📚 EDA 

1. 데이터 요약

• 2019-10 ~ 2020-02 기간에 발생한 한 코스메틱 이커머스의 월별  이벤트 히스토리
• 총 19,583,742행의 데이터

컬럼명	설명	null 유무	공백 유무	음수 유무
event_time	이벤트가 발생한 날짜 및 시간	X	X	-
event_type	총 4종류 : 상세페이지 조회 (view), 장바구니에 담기(cart), 장바구니에서 제거(remove_from_cart), 구매(purchase)	X	X	-
product_id	제품별 고유 ID	X	X	X
category_id	카테고리별 고유 ID	X	X	X
category_code	카테고리코드	X	O	-
brand	브랜드	X	O	-
price	가격	X	O	O
user_id	사용자마다 부여되는 ID	X	X	X
user_session	사용자가 웹사이트에 방문했을 때 발생하는 값	X	O	-

 

*참고) GA4 기준 user id, user session 추가 설명

• user id (사용자 id) : 등록된 사용자 별 고유 ID로, 쿠키 기반으로 식별함.
  같은 사람도 브라우저, 디바이스에 따라 다른 사용자로 집계될 수 있음.
• user session (사용자 세션) : 방문할 때 발생하는 값
• user id와 user session 차이 예시)
  1. 첫 번째 세션: 사용자가 오전 9시에 웹사이트의 특정 페이지를 엽니다. 페이지 조회(page view) 이벤트가 발생하여 세션이 시작됨.
  2. 두 번째 세션: 사용자가 같은 페이지를 오후 2시에 다시 돌아와 사이트를 탐색함
  → 이때 새로운 세션이 시작되지만 페이지 조회 이벤트는 기록되지 않고, 두 번째 세션이 사용자 참여로만 기록됨
• 단, 이번 데이터는 2019~2020년 데이터로 Google Anlaytics UA버전을 사용했을 것으로 예상되므로 세션 수의 정의는 약간 다름.

GA4에선 세션 지표 이해하고 사용하셔야 합니다.

 

---

2. user_id 와 user_session의 관계 및 중복 여부 확인 

이벤트 기록을 사용자 별로 구분할 기준 지표를 user_id와 user_session 중 고르기 위해 두 지표의 관계를 확인해본 결과, 1개의 user_id에 여러 개의 user_session이 있고, 1개의 user_session에도 여러 개의 user_id가 집계됨 

 

즉,  user_id와 user_session은 n:m 관계

👉보다 정확한 user 식별을 위해 concat으로`별도의 새로운 유저 id` 지표 생성 

-- user_id별 user_session 개수 확인
SELECT  user_id 
	   ,COUNT(DISTINCT user_session)
FROM oct_2019 o 
GROUP BY user_id 
ORDER BY 2 DESC;

-- user_session별 user_id 개수 확인
SELECT  user_session 
	   ,COUNT(DISTINCT user_id)
FROM oct_2019 o 
GROUP BY user_session 
ORDER BY 2 DESC;

 

---

3. 컬럼별 null 및 공백 값 유무 확인

데이터에 null값은 없으나, 공백 값이 있음 

 

`null 파악`

• null을 포함하는 COUNT(*)과 포함하지 않는 COUNT(컬럼명) 결과값이 모두 같으므로 null값 없음

 

`공백값 파악`

• COUNT로 category_code, price, brand, user_session에서 공백(‘ ‘) 존재 확인

-- 데이터 용량 문제로 10월 데이터로 파악
-- 칼럼별 null 유무 파악
SELECT COUNT(*)
	,count(event_time)
	,count(event_type)
	,count(product_id)
	,count(category_id)
	,count(category_code)
	,count(brand)
	,count(price)
	,count(user_id)
	,count(user_session)
FROM oct_2019 o 

-- 칼럼별 공백값 유무 파악
select count(*)
from oct_2019 o 
where user_session = "";

select count(*)
from oct_2019 o 
where brand = "";

select count(*)
from oct_2019 o 
where category_code = "";

 

---

4. event_type 중 view의 정의 

❓view는 언제 발생하는 이벤트일까?

홈페이지 메인 접속 시, 아니면 상세페이지 조회 시 발생하는 이벤트일까?

 

👇view 포함 모든 이벤트 데이터에 product_id가 부여됨.

즉, view는 특정 제품의 `상세페이지 조회` 시 발생하는 이벤트라고 볼 수 있음.

-- product_id가 공백값인 데이터가 있는지 확인
select count(*)
from oct_2019 o 
where product_id = "";

 

---

5. 음수값 확인

price에 음수 값이 있음

👉매출액 계산 시 정확한 결과를 위해 `음수 데이터 제외` 필요 ('WHERE price > 0' 조건문)

-- 가격이 음수인 데이터 확인
select *
from oct_2019 o 
where price <0;

 

---

6. 가격 관련 정보 

그럼 여기서 이어진 고민

❓ price에 음수값이 있다는 것은 환불 데이터일까?

 price는 단일 제품의 가격이 아니라 결제금액을 나타내는걸까?

 

👇우선 price에서 음수값이 발생한 데이터 전체를 확인해본 결과 purchase 외에 remove_from_cart 이벤트도 있었음

즉, 환불 데이터는 아님

 

다만 category_id가 모두 일치했음

❓ 그럼 특정 category_id의 상품들이 모종의 이유로 모두 가격 표기를 음수로 해둔걸까?

👇 그건 아님. price가 양수인 것도 있음. 

 

❓ 그러면 특정 제품(product_id)의 가격이 무조건 음수 표기되고 있는걸까?

아래는 가격이 음수 표기되고 있는 제품(product_id) 리스트 

👇그렇다! 위 5개의 product_id를 가진 이벤트(87행)는 전부 마이너스 가격을 가지고 있었다.

특정 제품들의 가격이 음수로 잘못 세팅되어있다고 볼 수 있음 

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 distinct product_id
from uni 
where price <0 -- 가격이 음수인 product_id 찾기


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 *
from uni 
-- 해당 product_id를 가진 데이터 찾기
where product_id in ('5716857','5716855','5716859','5670257','5716861')
order by product_id

그럼  모든 product_id는 하나의 price를 가질까?

❓즉 모든 제품의 가격은 변동이 없을까?

👇 아니다. 제품의 가격은 데이터가 수집된 5개월간 최소 0번(가격 데이터 1개) ~ 최대 6번(가격 데이터 7개) 바뀌었다.

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 
),
-- product별로 price가 몇번 바뀌었는지 파악
a as 
	(select product_id, count(distinct price) as cnt_price
	from uni 
	group by product_id
	order by 2 asc)
-- price가 바뀐 최대 횟수, 최소 횟수 
select min(cnt_price), max(cnt_price)
from a;

 

❓그럼 대부분의 제품이 할인을 한 특정 할인 이벤트 기간이 있었을까, 있었다면 언제일까?

특히 연말에는 Black Friday나 Cyber Monday같은 대규모 연말 할인이 있지 않았을까?

아니면 각 브랜드에서 자체적으로 가격 ab test를 시행했거나 별도 할인을 하는 등 모두 개별적이었을까?

 

👇11월에 대규모 `할인 이벤트`가 있었다.

 

가장 많은 제품이 할인한 날짜 top3가 11월 말에 몰려있고 다른 시기와 비교하여 2배 이상의 제품들이 할인에 참여한 것으로 보아 해당 시기에 커머스 상에서 대규모 할인 이벤트를 진행했을 가능성이 높다.  

특히 2019년 11월 21일에 할인을 한 제품 개수는 19,332개로 이벤트 기간 중 가장 높았다. 

 

2019년의 Black Friday는 11/29, Cyber Monday는 12/2.

아마 Pre-black friday 이벤트가 아니었을까 추측해본다.

-- 전체 데이터 합산
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 
),
-- 이벤트타임 날짜 데이터 추가
d as
	(select *,
		date(event_time) as event_date
	from uni
), 
-- 제품 별로 직전 이벤트가 발생했던 날짜와의 가격 차이 조회 
l as 
	(select product_id
		,event_date
		,price
		,lag(price) over(partition by product_id order by event_date) as previous_price
		,lag(price) over(partition by product_id order by event_date) - price as price_diff
	from d
),
-- 할인이 있었던 때의 데이터만 조회
pd as 
	(select *
	from l
	where price_diff >0
)
-- 날짜 별로 할인했던 제품 개수 조회
select event_date, count(product_id) on_sale_products
from pd
group by event_date
order by 2 desc,1

 

---

7. 브랜드 

❓어떤 브랜드들일까?

👇검색해보니 주로 네일 관련 브랜드가 많았고 브랜드 국적은 다양함  

ex) Runail - 러시아 네일케어 브랜드

 

Grattol - 독일 네일케어 브랜드

 

 

❓ 브랜드 정보가 없는 상품은 뭘까? 

👉이건 커머스에 들어가보지 않는 한 확인할 방법이 없어서 PB 상품이 아닐까 추측함

 

---

(분석 과정은 추후 포스팅 예정)