[이론내용만] Node 10. SQL 심화 - SQL로 데이터 분석하기

10-1. 들어가며

---

• 학습 목표
  • 실무 활용 분석 프레임워크 설명
  • 분석 프레임워크에 맞춘 SQL 쿼리문 작성
  • 분석 결과를 통한 인사이트 도출

10-2. 회사의 일별 매출액 구하기

---

• order 테이블과 payments 테이블을 이용해 모두몰 일별 매출액 총합 구하기

  • 기간 : 2018-01-01 이후부터 작성되어 있음.

  • 필요한 단계

    1. orders와 payment 테이블 조인

    SELECT 
      o.order_id, 
      o.order_timestamp, 
      p.value
    FROM 
      `project_name.dataset_name.orders` o
    JOIN 
      `project_name.dataset_name.payments` p
    ON 
      o.order_id = p.order_id;

    2. order_timestamp를 이용해 추출한 연월일 데이터 -> dt로 저장

    SELECT 
      o.order_id, 
      DATE(o.order_timestamp) AS dt, 
      p.value
    FROM 
      `project_name.dataset_name.orders` o
    JOIN 
      `project_name.dataset_name.payments` p
    ON 
      o.order_id = p.order_id;

    3. GROUP BY절 이용
    • 문제 조건에는 안나와 있었지만 정렬을 해서 결과가 잘 나오게끔!

    ...
    GROUP BY 
      dt
    ORDER BY 
      dt;

10-3. PV, UV 계산하기

---

• PV(Page View)

  • 페이지 단위 뷰 수
  • 허수가 많아 주의!
  • ex) 사용자 A가 페이지를 50번 새로고침 : 50회
  • 특정 페이지에 얼마나 많은 사람이 몰렸는지 측정하는 지표
  • 원클릭 결제 도입 등으로 PV 수가 줄어들기도 하기때문에, PV가 높다고 무조건 긍정신호가 아님!

• Unique PV

  • 중복 뷰 제거한 PV 수
  • ex) 사용자 A가 페이지를 50번 새로고침 : 1회

• Visits

  • 사이트 총 사용자 수(방문자 수)
  • 세션 기준 집계(주어진 시간인 세션 기준 안에 재방문 시 무조건 1회로 집계한다는 의미)

• UV(Unique Visits)

  • 중복 제거 Visites
  • 특정 기간을 설정하여 집계

분석 시 주의 사항

• UV 증가, PV 증가
  • 순 방문자수 증가, 방문 페이지 수도 늘었음.
• UV 감소, PV 증가
  • 사이트 방문 사용자 감소, 그러나 1명의 사용자가 더 많은 페이지를 보고 있다!
• UV 증가, PV 감소
  • 순 방문자수 증가, 그러나 실제 1명의 방문자 당 조회 페이지가 줄었음.
• UV 감소, PV 감소
  • 방문 사용자 감소, 방문 페이지도 감소

10-4. ARPU와 ARPPU 구하기

---

ARPU -> 전체

• Average Revenue Per User
• 유저 당 평균 수익
• 전체 매출 / 전체 유저 수

ARPPU -> 유료!

• Average Revenue Per Paying User
• 유료 결제 유저 당 평균 수익
• 전체 매출 / 유료 결제 유저 수

문제 풀이 시 NULLIF를 사용함.

• NULLIF 사용
  • revenue > 0인 경우가 하나도 없다는 것이 결과가 0이라고 말할 수 없음.
  • SUM(revenue) / 0처럼 0으로 나누는 오류가 발생할 수 있기 때문에 NULL로 걸러주는 것!

10-5. 퍼널 분석: 사람들은 어디에서 이탈하고 있을까?

---

퍼널 분석?

• 사용자의 최초 유입 이후 -> 최종 목적인 구매 행동까지 갈 때의 단계를 분석하는 프레임워크
• 쇼핑몰 앱 방문 > 상품 상세 정보 조회 > 장바구니 > 결제 수단 선택 > 구매

AIDA 모델과 AARRR 프레임워크

• AIDA 모델 : Attention > Interest > Desire > Action

  • 모델의 4단계별로 각각 고객 이탈 원인 정의 & 핵심 지표 선정해 데이터 분석
  
  

• AARRR 프레임워크 : Acquisition > Actiavation > Revenue > Retention > Referral

• 고객의 구매 여정을 순차적으로 분석하는 접근법

• 디지털 서비스에서 이용자 행동 설명 및 문제 진단에 용이

10-6. 리텐션 분석: 내가 VIP가 될 상인가?

---

리텐션?

• 사용자 방문 이후 서비스에 남아 있는 비율, 특징, 원인 분석 프레임워크
  ex) 10만명 고객 모집 > 7일 후 재방문자수 천명 -> 리텐션은 1%
• 목표 : 고객이 지속적으로 서비스를 찾아오는 것!

리텐션을 구하는 방법

• Classic Retention

  • N-day 유지율 = N-day 당일 제품 이용한 사용자 / Day 0 당일 제품 이용한 사용자
    • 가장 보편적
    • 고객의 제품 첫 사용 날짜를 Day 0으로 설정
    • 해당 날짜로부터 N일 후 제품 재사용 비율을 측정하는 것!
    • 대부분의 커머스 : D+7 리텐션, D+14 리텐션, D+30 리텐션
    • 영양제 등의 재구매 주기가 긴 커머스 : D+30 리텐션, D+90 리텐션
    • 특정일에만 재구매하는 경우를 통해서는 정확한 리텐션을 구하기 어렵다는 한계 존재!

• Rolling Retention

  • N-day 이후 1번이라도 제품 이용한 사용자 / Day 0 당일 제품 이용 사용자
    • 1번이라도 제품을 이용한 사용자들을 모두 리텐션으로 인정하기 때문에 그 값이 다소 높게 측정되는 문제가 있음.
    • 측정일이 바뀌면 리텐션도 계속 바뀐다는 것 역시 문제.

• Range Retention

  • Range N(N 구간) 제품 이용 사용자 / Range 0 제품 이용 사용자
    • 1일 단위 -> 구간으로 확장
    • 분석 구간을 유연하게 설정해서 사용 가능!
    • 재방문 주기가 긴 서비스에서 사용하면 좋음.
    • 단, 구간 설정 기간이 길면 -> 리텐션이 과도하게 높게 측정될 수 있는 문제.

그럼 왜 기업에서는 리텐션을 사용할까?

• 리텐션이 높다는 것은 -> 그만큼 우리의 서비스가 지속적인 재방문을 할만한 매력적 가치를 지니고 있다는 것!
  • 그렇기 때문에 리텐션이 높은 경우 -> 고객 유입을 최대치로 끌어올리는 전략을 사용함.

• 그러나, 리텐션이 낮다는 것은..
  • 고객 유입을 많이 시키더라도 -> 서비스 자체의 가치가 떨어져 -> 장기적으로 볼수록 서비스를 유지시키기 어려울 수 있음.

10-7. RFM 분석

---

RFM 분석?

• 사용자의 구매 최신성(Recency), 구매 빈도(Frequency), 구매 금액(Monetary)에 대한 3가지 기준으로 사용자를 그룹화

계산법

• 이 3가지의 척도의 총합을 통해 RFMScore를 구하는 것.
• 이론적인 세그먼트 나누기 경우의 수 : 5*5*5 = 125개의 세그먼테이션