SQL | 프로젝트 #2-1 지표 해석 (문제 다시 찾기)

<현재 단계>
raw 데이터를 sql로 가공하여 일/주/월별로 사용자/세션/매출액 등을 파악할 수 있는 시트를 만들고 시각화 하였다.
현 데이터를 활용하기 위해서 "고객 지표"를 통해 매출을 증가하기 위한 전략을 세워야한다.

start_date / start_week / start_month -- 시작일자
# of unique sessions	-- 세션 수 (중복불포함)
# of unique users	    -- 유저 수 (중복불포함)
# of views				-- event_type이 view인 데이터 수
# of cart				-- event_type이 cart인 데이터 수
# of remove from cart	-- event_type이 remove인 데이터 수
# of purchases			-- event_type이 purchase 데이터 수
total value of views		-- event_type이 view가 있는 제품의 price 총합
total value of cart			-- 위와 동일
total value of remove from cart		-- 위와 동일
total value of purchase				-- 위와 동일
# of purchase users				-- 구매한 사용자 수
# of cart users					-- cart에 넣은 적이 있는 사용자 수
# of remove from cart users		-- remove한 적이 있는 사용자 수
# of purchase sessions			-- 구매 세션 수
# of cart sessions				-- cart 세션 수
# of remove from cart sessions	-- remove 세션 수

이용 패턴 분석

• view로 시작하는 데이터
  view
view > cart > remove_from_cart
view > cart > purchase
view > cart > remove_from_cart > cart > purchase
view > purchase

• 카트로 시작하는 데이터 존재
  cart > purchase
cart > view > remove_from_cart
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

1. 지표 트렌드와 추이 살피기

• user 추이
  
  🔍 전반적으로 user의 모습은 unique users 데이터의 그래프 형태와 비슷하다. cart > remove_from_cart > purchase 순으로 user 차이가나는 것을 볼 수 있다.
  
  
  ❗ 눈여겨봐야 할 포인트는
  (1) 10-02와 10-06이 unique 값과 cart 값만 높게 뛰었는데 이는 홍보/프로모션 등 acquisition을 높이는 외부 요인이 있는 것으로 추측할 수 있어 외부 요인 확인이 필요하다.
  (2) 12-31 (전후로) 눈에 띄게 모든 지표값들이 떨어짐을 확인할 수 있다.
  (3) 11-22과 11-28이 값이 뛰었음을 알 수 있다.

• 구매 user 및 매출액 파악
  

🔍 구매액과 구매자 수는 비례하며, 눈에 띄는 날짜는 없다.
🔍 앞서 봤던 10월 peak에서 매출값이 튀지 않았음을 확인할 수 있고 (purchase user이 많지 않았으므로), 11-07, 22,28,29일이 (최고 구매자 2315명, 매출 102,783)으로 높았으며, 01-27,28일자에도 (구매자 2014명, 91,420)으로 peakf를 확인할 수 있었다. 여기서도 12월 31일은 값이 눈에 띄게 낮다.


흥미로운 점은 1월에 15일과 27일은 unique 사용자 수는 비슷하지만 cart 수에서 15일의 값이 높아지지 안았는데, 이게 매출의 차이로 이어졌다. 15일은 50,185달러 매출이라면 27일은 약 80% 더 높은 매출을 보였다.

• 매출 관련 전반적인 추이
  앞서 언급한 눈에 띄는 증감 추세를 제외하고 대체적으로 일 평균 41802.57달러이며, 해당 값을 유지하고 있는 트렌드를 보이고 있다.

• 재구매 주기 파악
  약 33.5일인 것으로 보아 MAU를 파악하는 게 가장 적절해보인다.
  brand 이름을 살펴보았을 때 네일 등으로 뷰티샵 즉 올리브영 같은 드럭스토어라고 가정했을 때 매일 들어오는 것보다 제품을 다 사용했을 때 다시 돌아올 것이다.

2. Funnel 분석

지표 설정 기준

• Acquistion : 첫 방문한 user 수
• Activation : 첫 방문 기록 외 다른 액션(데이터)가 있는 user 수
• Revenue : 1번 이상 구매한 user 수
• Retention : 2번 이상 구매한 (재구매한) user 수
  

🔍 Activation에서 Revenue로 넘어가는 부분에서 전환율을 높이면, 수익과 리텐션을 크게 높일 수 있을 것이다.
? 첫 구매를 어떻게 이끌어낼 수 있을까?
? 재구매율이 높은 이유가 무엇일까?

3. Using KPI Tree

1) 신규고객 추이

• 2019년 47째주에 7,894로 가장 많은 신규 고객이 유입되었다.
• 2020년 01째주에 2,534로 가장 적은 신규 고객이 유입되었다.
• 대체적으로 4천에서 6천 이내로 신규 고객이 유입되었음을 알 수 있다.

🔍 47주차와 1주차의 많이 팔린 제품을 파악해서, 프로모션이 있는지 유추할 수 있다.

1-1) MAU 분석

• 한달 동안 활동한 고유 사용자 수로, 사용자 참여와 애플리케이션의 인기도를 측정
• 구매패턴을 보면 view로 시작하지 않는 경우도 있기 때문에, 한 번이라도 action을 한 사용자로 mau를 구한다. COUNT(distinct user_id)로 월별 고유 사용자 수를 파악한다.
  

🔍 12월 31일의 매출 저하로 가장 낮을 것이라 생각했던 것과 달리 11월에서 mau가 가장 낮고, 1월이 가장 높았다.

❗ 독특하게도 11월은 mau가 높지 않은데 전체 매출이 크다.
11월에 1인당 구매 비용이 컸다. 11월과 12월 사이에 5달러 정도 차이가 있다.

좀 더 자세히 살펴보기 위해 weekly로 확인했을 때

동일하게 11월 말과 12월 말에서 눈여겨 봐야할 것을 포착했다. 이 때 어떤 것을 포착하면 좋을까?

2) 기존 고객 유지

(1) 재구매율

한달 동안 2일 이상 구매한 사람 / 한달 동안 1번 이상 구매한 사람 비율

-- 쿼리
SELECT event_month
	, COUNT(DISTINCT CASE WHEN sub.purchase_count > 1 THEN sub.user_id END)  /  COUNT(DISTINCT sub.user_id) AS repurchase_rate
FROM 
    (select DATE_FORMAT(event_time, '%Y-%m-01') AS event_month
    	, user_id
    	, COUNT(event_time) AS purchase_count
     FROM beauty_ecommerce
     WHERE event_type = 'purchase'
     GROUP by event_month, user_id) AS sub
group by event_month;

크게 튀는 값이 없고, 재구매율은 대체적으로 0.9로 높은 편이다.

(2) 클래식 리텐션

🔍 201946이 다른 주보다 리텐션이 좋고, 201952는 다른 주보다 리텐션이 낮음을 확인할 수 있다.

(3) 롤링 리텐션 (이탈률)

3) 주문 당 평균금액

(1) 주문 당 평균 금액

주차별로 주문 당 평균금액을 확인했을 때 아래와 같으며 전체 평균금액은 42.2 달러이다.

🔍 눈에 띄는 변화는 없다.

(2) 전환율

• cart > purchase 전환율

• cart > remove_from_cart 전환율
  

(3) 구매이탈 (remove_from_cart)

🔍remove_from_cart 유저가 그렇지 않은 유저와 매출 차이가 유의미한가

category	user_counts	total_purchase	avg_event_count	purchase_counts
remove	152,841	4,561,284	82.5917	964,102
control	714,164	5,141,179	22.2687	1,044,993

지표에 대한 내용 더 참고 하기

---

스터디 피드백 내용

대시보드 파트

• 유저 레벨의 데이터를 뽑는다.
• 매출의 특정시점을 볼 수 있는 데이터와, 흐름(트렌드)를 파악할 수 있는 데이터를 모두 보여준다.
• 전반적인 상황을 보기 위해서 큰 범위 단계에서 드릴 다운 (Month > Week > Day) 으로 파악할 수 있도록 한다.

• action의 경우 했는지 여부는 의미를 가지기 어려움 → 그러면 이 액션을 한 유저가 몇 명인지, 한 유저가 얼마나 많은 액션을 했는지 그런 식으로 쪼개서 들어가면 좋다.
• 평균금액의 대부분의 사람과 큰 금액의 소수 사람들의 차이를 확인하면서 인사이트를 뽑아낼 수 있다.
• action 에 대해서 의미를 가지려면 user의 평균을 파악

• 리테션 그래프를 그릴 때 (1) 표 형태 (2) 상징적인 시점 (한 달 등 열 기준값) 값을 가지고 와서 라인 차트를 많이 그린다.

시도/추가할 부분

• 전환율을 보여주는 그래프
• 리텐션을 표현하는 그래프