SQL | 프로젝트 #2

소리·2024년 6월 16일
sql프로젝트
0


▶ 이전 포스팅 에 적혀있던 분석 목표와 방향

데이터 확인

  • 데이터 모습
SELECT *
FROM 2019dec
LIMIT 20;

  • category_id 종류 : 482
  • brand_id 종류 : 253
  • product_id 종류 : 44624

더 많은 내용은 이전 포스팅에서 확인 가능

파악한 문제점

event_type에서 cart > purchase로 간 데이터보다 cart > remove_from_cart로 간 비율이 더 많다. 그 이유가 무엇일까?


  • cart > purchase로 간 비율
SELECT COUNT(DISTINCT ct.user_id, ct.user_session) AS cart_count
    , COUNT(DISTINCT pt.user_id, pt.user_session) AS purchase_count
    , COUNT(DISTINCT pt.user_id, pt.user_session) / COUNT(DISTINCT ct.user_id, ct.user_session) AS cart_purchase_ratio
	-- , COUNT(DISTINCT rt.user_id, rt.user_session) / COUNT(DISTINCT vt.user_id, vt.user_session) AS view_remove_ratio #이건 의미가 없는 값인 것 같은데. 전체 뷰에서 삭제를 봐서 뭐하지
FROM ct
LEFT JOIN pt ON ct.user_id = pt.user_id
		AND ct.user_session = pt.user_session
        AND ct.event_time <= pt.event_time;
cart_countremove_countcart_remove_count
165571234750.1418

🔼 카트에 담긴 14.18% 만이 구매로 이어진다.


  • cart > remove_from_cart로 간 비율
SELECT COUNT(DISTINCT ct.user_id, ct.user_session) AS cart_count
    , COUNT(DISTINCT rt.user_id, rt.user_session) AS remove_count
    , COUNT(DISTINCT rt.user_id, rt.user_session) / COUNT(DISTINCT ct.user_id, ct.user_session) AS cart_remove_ratio
FROM ct
LEFT JOIN rt ON ct.user_id = rt.user_id
		AND ct.user_session = rt.user_session
        AND ct.event_time <= rt.event_time;
cart_countremove_countcart_remove_count
165571598230.3613

🔼 36.13% 정도가 카트에 들어갔다가 삭제된다.


가설

  • cart 상태에서 remove_from_cart 상태로 이동한 고객은
순번가설세부내용확인방법
1특정 브랜드 문제특정 브랜드에서 많이 발생해 제거율이 높아졌는가?브랜드 별 카트 제거 이벤트 수 비교
2특정 제품 문제특정 제품에서 카트 제거 이벤트가 많이 발생했는가?제품 별 카트 제거 이벤트 수 비교
3단순변심카트에 넣었다가 뺐다가 다시 넣는 경우조건에 해당하는 데이터 수를 추출하여 확인
4가격 비교일단 카트에 넣었다가 더 저렴한 곳을 찾아 구매한 경우category_id에 다른 가격대 물건이 얼마나 있는지 파악
5할인 쿠폰쿠폰을 사용하려고 했으나 적용되지 않거나 기대한 만큼 할인이 되지 않아서 구매를 취소하는 경우프로모션 추가 데이터 필요

위와 같이 생각해볼 수 있다.

데이터를 통해 어떤 현상이 있는지 살펴본다.

진행 내용

가설 1 : 특정 브랜드 문제 - 가은, 소리

  1. 특정 브랜드의 문제일 가능성이 존재하기 때문에 카트 제거 이벤트가 많이 생긴 브랜드를 확인한다.

🔼 brand에 null값이 큰 비중을 차지해 제일 상단에 위치했다. 현재 결과에서는 특정 브랜드의 문제인지 확인하기가 어렵다.

🔼 cart에 많이 담길 수록 remove_from_cart가 되는 값도 많을 것이다. 비율로 크게 튀는 값이 있는지 확인한다.

➡️ 0.5에서 0.9 사이까지 골고루 있어 눈에 띄는 브랜드가 있진 않지만, 1에 가까운 브랜드에 대해 문제가 없다고 판단해도 될까요?


가설 2: 특정 제품 문제 - 소리, 예나

  1. 어떤 제품이 카트 제거가 많이 이루어 졌을까?

🔼 5809912 제품이 8828번 카트에서 제거되었다. (left join c_to_r 테이블을 활용했을 때)


  1. cart에 많이 담길 수록 remove_from_cart가 되는 값도 많을 것이다. 제품이 카트에서 제거되는 횟수/ 카트에 담긴 횟수를 확인해보자

🔍 cart_counts 수로 정렬한 테이블

🔼 cart_counts 수로 정렬한 테이블을 확인했을 때 ratio 값이 큰 제품으로 이슈가 되는 특정 제품을 추릴 수 있다.


🔍 ratio로 정렬한 테이블

🔼 테이블에서 1을 넘는 product_id 데이터를 확인하였다. 값이 매우 큰 경우 39배에 달하는 데이터를 포착했다.

❓ 해당 데이터는 12월 데이터만 가지고 있다. 11월에 카트에 담았던 이력이 확인되지 않아서 cart_counts는 1이지만 remove_carts가 39인 상황이 나오지 않을까?

➡️ 가설을 푸는 방향이 맞는지 확신이 없습니다.

가설 3 : 단순 변심 - 가은, 예나, 소리

  1. 카트에 넣어놓고 경과한 시간으로 관심을 파악할 수 있지 않을까?
  • 제품마다 cart > purchase로 넘어가는 시간 파악
SELECT product_id, AVG(time_gap) AS gap
FROM (
	SELECT ct.*
		, pt.event_time AS remove_event_time
		, TIMESTAMPDIFF(SECOND, ct.event_time, pt.event_time) / 86400 AS time_gap  -- 1일 기준
	FROM ct
	LEFT JOIN pt ON ct.user_id = pt.user_id
			AND ct.user_session = pt.user_session
			AND ct.product_id = pt.product_id
			AND ct.event_time <= pt.event_time) t3
GROUP BY product_id
ORDER BY gap DESC;

🔼 평균적으로 당일부터 16일까지 시간 차이가 있다.


  • 제품마다 cart > remove_from_cart로 넘어가는 시간

🔼 평균적으로 당일부터 23일까지 시간 차이가 있다.


  • 눈에 띄는 제품인 '5809910'으로 purchase로 이어지는 시간과 remove_from_cart로 이어지는 시간을 비교했을 때,
    구매로 이어지는 경우는 카트 담은 후 약 1시간 25분 정도의 시간이 소요되었고, 제거한 경우는 약 4시간 정도 이후에 실행되었다.

➡️ 그러나 기준점이 없어서 결과에 대한 판단 불가능

➡️ 위 코드가 가설을 확인하기 위한 방향이 맞는지 궁금합니다. 이 방향이 아니라면 이 상황에서 어떻게 일시적인 관심을 데이터로 확인할 수 있을까요?



  1. remove_from_cart > cart로 다시 간 경우 횟수
  • 12월에 발생한 구매 건 수는 총 213,176 건
  • 이 중에서 remove_from_cart가 일어난 제품이지만 결국 구매까지 이어진 건 수는 103,811 건

➡️ 이 때 cart > remove > purchase 수를 세었기 때문에
cart → remove → cart → remove → purchase의 경우의 수가 누락되었음. 위에 것까지 포함해야 해야하는지 고민

➡️ 12월에 remove_from_cart가 일어난 이벤트 수는 총 664,655 건.
이 중 단순 변심은 약 11만건 이상이다. 라고 얘기하면 나머지 55만건은 그냥 안 산 것이라고 생각해도 될지 에 대한 궁금증

➡️ 가설을 푸는 방향이 맞는지 확신이 없습니다.

가설 4 : 가격 비교 - 소리

더 저렴한 제품을 찾아서 이동한 것일까 가설을 생각했을 때, 이를 확인하기 위해서
product_id와 카테고리 별 평균 가격과 표준편차를 비교하였다.

WITH cart_count AS (
	SELECT DISTINCT product_id, category_id, brand, price
		, COUNT(product_id) OVER (PARTITION BY product_id) AS count
	FROM ct
), remove_count AS (
	SELECT DISTINCT product_id, category_id, brand, price
		, COUNT(product_id) OVER (PARTITION BY product_id) AS count
	FROM rt
), avg_price AS(
	SELECT category_id, ROUND(AVG(price),2) AS avg_price, ROUND(STD(price),2) AS std
	FROM 2019dec
	GROUP BY category_id
), main AS (
	SELECT DISTINCT cc.product_id
		, cc.category_id
        , cc.brand
        , cc.price
		, rc.count AS remove_counts
		, cc.count AS cart_counts
		, COALESCE(rc.count, 0) / cc.count AS ratio
	FROM remove_count rc
	INNER JOIN cart_count cc ON rc.product_id = cc.product_id
		AND rc.brand = cc.brand
		AND rc.price = cc.price
	ORDER BY ratio DESC
)

SELECT m.*, a.avg_price, a.std
FROM main m
LEFT JOIN avg_price a ON m.category_id = a.category_id;

➡️ remove/cart 비율이 높은 순으로 확인했을 때 표준편차가 어느 정도 있음을 발견하였다.
(1.38 평균 가격에 0.59 표준편차는 약 +- 43% 변동범위 가 있다고 볼 수 있다)

➡️ 제품 하나하나가 아닌 전체적인 그림으로 해석할 수 있을까요?


고민 지점

1. 가장 큰 고민은 가설을 세우는 부분에서 확신이 없다는 것입니다. 가설을 세우고 쿼리로 검증하는 과정에서 계속 풀어내는 방향이 맞는지 확신이 없습니다. 계속 가설을 푸는 과정에서 또 다른 가설이 생기면서 결론 없이 쳇바퀴 도는 느낌입니다.

그래서 가설 2, 3, 4의 방향이 맞는 것인지, 더 나은 방법이 있는지 궁금합니다.


2. 분석 시 초기 테이블을 만들 때 2가지 방향으로 갈렸는데, 어떤 게 현재 데이터에서 더 적절한지 궁금합니다.

  • 상황 : 브랜드 별 remove_from_cart/cart 비율을 구함

(풀이1)

event_type에만 조건을 줘서 remove_from_cart 인 데이터와 cart인 데이터로 쿼리 작성

WITH removed AS (
	SELECT brand, COUNT(product_id) AS removed_cnt
	FROM dec19
	WHERE event_type = 'remove_from_cart'
	GROUP BY brand
	ORDER BY removed_cnt DESC
), cart AS (
	SELECT brand, COUNT(product_id) AS cart_cnt
	FROM dec19
	WHERE event_type = 'cart'
	GROUP BY brand
	ORDER BY cart_cnt DESC
), purchased AS (
	SELECT brand, COUNT(product_id) AS purchased_cnt
	FROM dec19
	WHERE event_type = 'purchase'
	GROUP BY brand
	ORDER BY purchased_cnt DESC
)

SELECT cart.brand
		, cart_cnt
    , removed_cnt
    , ROUND(removed_cnt/cart_cnt,2) AS removed_rate
    , purchased_cnt
    , ROUND(purchased_cnt/cart_cnt,2) AS purchased_rate
FROM cart LEFT JOIN removed ON cart.brand = removed.brand
					LEFT JOIN purchased ON purchased.brand = cart.brand
WHERE cart.brand IS NOT NULL
ORDER BY cart_cnt DESC;

(풀이2)

event_type을 조건으로 vt, ct, pt, rt 테이블을 생성하고 (각각 view, cart, purchase, remove_from_cart table)

event_time 순서를 적용해
v_to_c, c_to_p, c_to_r 테이블을 생성하여 쿼리 작성

CREATE TEMPORARY TABLE vt AS 
	SELECT user_id, user_session, event_time, category_id, product_id, brand, price
	FROM 2019dec
	WHERE event_type = 'view';
    
CREATE TEMPORARY TABLE ct AS 
	SELECT user_id, user_session, event_time, category_id, product_id, brand, price
	FROM 2019dec
	WHERE event_type = 'cart';
    
CREATE TEMPORARY TABLE pt AS 
	SELECT user_id, user_session, event_time, category_id, product_id, brand, price
	FROM 2019dec
	WHERE event_type = 'purchase';
    
CREATE TEMPORARY TABLE rt AS 
	SELECT user_id, user_session, event_time, category_id, product_id, brand, price
	FROM 2019dec
	WHERE event_type = 'remove_from_cart';
  
  
  
CREATE TEMPORARY TABLE v_to_c AS  -- 같은 아이디, 같은 세션, 같은 제품에 대해서 view 후 cart에 넣은 데이터들을 추출하는 테이블
	SELECT vt.*
		, ct.event_time AS cart_event_time
		, TIMESTAMPDIFF(SECOND, vt.event_time, ct.event_time) AS time_gap
	FROM vt
	LEFT JOIN ct ON vt.user_id = ct.user_id  -- left join 매칭으로 갯수가 더 커지는 이슈 있음
			AND vt.user_session = ct.user_session
			AND vt.product_id = ct.product_id
			AND vt.event_time <= ct.event_time;
            
CREATE TEMPORARY TABLE c_to_r AS  -- 같은 아이디, 같은 세션, 같은 제품에 대해서 카트를 넣은 시간보다 카트 제거한 시간이 더 늦을 경우에 속하는 값들을 추출하는 테이블
	SELECT ct.*
		, rt.event_time AS remove_event_time
		, TIMESTAMPDIFF(SECOND, ct.event_time, rt.event_time) AS time_gap
	FROM ct
	LEFT JOIN rt ON ct.user_id = rt.user_id
			AND ct.user_session = rt.user_session
			AND ct.product_id = rt.product_id
			AND ct.event_time <= rt.event_time;
            
CREATE TEMPORARY TABLE c_to_p AS  -- 같은 아이디, 같은 세션, 같은 제품에 대해서 카트에 넣고 구매로 이어진 값을 추출하는 테이블
	SELECT ct.*
		, pt.event_time AS purchase_event_time
		, TIMESTAMPDIFF(SECOND, ct.event_time, pt.event_time) AS time_gap
	FROM ct
	LEFT JOIN pt ON ct.user_id = pt.user_id
			AND ct.user_session = pt.user_session
			AND ct.product_id = pt.product_id
			AND ct.event_time <= pt.event_time;

3. 해당 프로젝트의 결론을 어떤 식으로 내야하는 지 궁금합니다.

profile
소리
데이터로 경로를 탐색합니다.
이전 포스트

SQL | CTE문, view, temporary table의 차이

다음 포스트

SQL | 프로젝트 #3

0개의 댓글