SQL | 프로젝트 #4 구매로 가는 루트 파악

1. 구매로 가는 루트 전부 파악

• user_id, session, DATE_FORMAT(event_time, '%Y-%m-%d')의 고유값으로 루트확인하기
• 예) view > cart > remove > purchase

1-1. 데이터 추출 쿼리

WITH filter_table AS (
	SELECT user_id, user_session, DATE_FORMAT(event_time, '%Y-%m-%d') AS date_e, COUNT(*) AS count
	FROM 2019dec
	GROUP BY user_id, user_session, DATE_FORMAT(event_time, '%Y-%m-%d')
	HAVING COUNT(*) > 10 AND COUNT(*) < 50
)

SELECT *
FROM filter_table
ORDER BY count
LIMIT 10;

• user_session은 user_id 하나만 가짐, 여러 user_id가 중복되지 않음
• 하루 치 데이터가 충분하지 않을 경우 user_id만 동일한 여러 세션을 비교한다.

1-2. 총 3개 유저를 뽑음

2) 1번 케이스

	user_id = '593496422' 
AND user_session = 'bf733c87-8564-49f3-a231-a97f61541780' 
AND DATE_FORMAT(event_time, '%Y-%m-%d') = '2019-12-27'

• 추출값
  

• 해석

0. 12월 27일 같은 섹션 활동임.
1. 3개 각기 다른 제품을 카트에 넣음
2. 앞서 카트에 넣었던 2가지 제품을 30분 내외로 삭제함
3. 카트에 넣은 제품 줌 남아 있던 제품은 당일 추가 행동 없음
4. 당일 카트에 넣지 않은 제품들이 다수 삭제됨.
   +다른 날짜도 확인하였으나, 같은 user_id, 같은 session에서는 27일 외 없음
   +동일한 user_id 593496422 만 추가 확인 진행하였을 때 69행에서 여러 session이 섞여있었고, 구매로 이어진 전적이 없음

• 패턴
  -구매 X
  -cart > view > remove, cart > remove 혹은 view 만 존재

3) 2번 케이스

	user_id = '105697630' 
AND user_session = '9ba02ca6-1cd6-4b6b-8e92-0f05ba82fcdc' 
AND DATE_FORMAT(event_time, '%Y-%m-%d') = '2019-12-06'

• 추출값
  

• 해석

1. 구매 X
2. session 상관없이 id 전체 활동을 보았을 때, 12월 10일 카트에 있는 제품 일괄 purchase
3. 10일 이후로는 구매 액션 없이 view remove_from_cart cart만 존재
4. 아래 view > cart > view > purchas 를 확인해보면
   
   세션과 시간이 각기 다른 것으로 보아 user_id와 product_id로 DISTINCT 하여 확인해야 할 것으로 보임

• 패턴
  -view > cart > remove_from_cart
  -view > cart > purchase
  -cart > purchase : 바로 카트에서 시작하는 활동이 다수 존재
  -view > purchase 행동 존재
  
  -이중으로 cart에 넣은 경우 삭제하는 경우 존재
  -remove_from_cart를 1-2분 내 중복되는 경우 실수로 누르는 것으로 추정됨
  
  -바로 purchase하는 케이스도 존재

3) 3번 케이스

	user_id = '592889347' 
AND user_session = '59fda76f-f3c7-4945-9f02-8e0fa273ef99'

• 해석

• 패턴
  -view
  -view > cart > remove_from_cart
  -remove_from_cart > view > cart > remove_from_cart
  remove_from_cart로 시작하는 데이터 존재
  -view > cart > remove_from_cart > cart> purchase
  -cart > remove_from_cart > cart > purchase 카트로 시작하는 경우 존재
  -cart > remove_from_cart > view > purchase 로 보고 바로 구매로 가는 경우도 발생

패턴 정리

• view
• view > cart > remove_from_cart
• view > cart > purchase
  
  
• view > cart > remove_from_cart > cart > purchase
• view > purchase **
  
  

카트로 시작하는 데이터 존재

• cart > purchase **
• cart > view > remove
• cart > remove_from_cart
• cart > remove_from_cart > cart > purchase
• cart > remove_from_cart > view > purchase **

remove_from_cart로 시작하는 데이터 존재

• remove_from_cart > view > cart > remove_from_cart

행동 추측

• 이중으로 cart에 넣은 경우 삭제하는 경우 존재
• remove_from_cart를 1-2분 내 중복되는 경우 실수로 누르는 것으로 추정됨

논의 거리

• 기존에는 DISTINCT user_id, user_session으로 진행했으나, 데이터를 확인한 결과
  user_id와 user_session은 1:N으로 구성되어 있어, user_id와 product_id로 진행해야 구매 경로를 확인할 수 있을 것이라 판단

• 동일한 시간, 동일한 제품, 동일한 세션인 경우 실수로 판단하여 중복 제거 진행 (본인은 중복제거 시 개수가 감소하는데, 다른 팀원은 그렇지 않아 해당 부분 확인 필요)

• 최종 목적이 purchase이니까 purchase를 어떤 경로로 왔는지를 중점적으로 파악해야 할지 고민. 이 경로 안에서 remove_from_cart로 이탈하는 경우를 확인하는 가설을 설정해야 할까?

• 아래와 같이 user_id와 product_id가 같은 상황에서 purchase가 없는 경우 모두 한 경로로 취급해야 할까?