[복습] 퍼널분석 & AARRR

이상해씨·2025년 3월 18일

sql

SQL

목록 보기

23/24

📌 퍼널분석(Funnel Analysis)

퍼널(funnel) :
- user가 특정 목적을 달성하기 위해 거쳐야 하는 단계
- 특정 목적에 가까운 단계일 수록 사용자 수가 줄어들어 깔데기 모양(Funnel)을 띔
(출처 - 데이터리안 : https://datarian.io/blog/funnel-analysis)
퍼널분석 :
- 퍼널별로 이탈율을 계산하고, 이탈원인을 분석 및 해결법을 제시하는 분석 방법
  -> 퍼널분석을 통해 이탈율을 감소하고, 전환율을 높임

1️⃣ 이탈율과 전환율 구하기

이탈율(drop_off rate) :
- 다음 퍼널로 넘어갈 때, 이탈하는 비율
- 이탈율 = ((이전 퍼널 사용자 수 - 현재 퍼널 사용자수) / 이전 퍼널 사용자 수 )*100
전환율 (conversion rate) :
- 다음 퍼널로 넘어가는 비율 (전환conversion하는 비율)
- 전환율 = 1- (이탈율/100)

2️⃣ 전환율 높이기

퍼널 간 이탈율, 전환율을 구하면, 유난히 전환율이 낮은 전환이 있다.
퍼널 분석에서는 이 부분의 전환율을 높이기 위해서 어떤 방법을 취할 것 인지 해결방법을 제시해야 한다.
대표적인 방법으로는 UX/UI 최적화 (속도 개선, 입력 필드 최소화, 자동입력기능제공, 디자인 개선 및 크기 조정), 인센티브 제공(쿠폰, 포인트, 회원가입시 혜택, 장바구니 리마인드, 긴급성강도, 무료체험기간), A/B 테스트& 데이터기반 최적화&CTA(call to Action : 유도 메시지), 신뢰도 향상 (불안요소 해결: 결제 보안 표시, 리뷰평점 노출, 브랜드 인증, 정책 명확화)

📌 AARRR (Pirate Metric)

AARRR : 퍼널분석의 대표적인 종류
- 사용자 행동 흐름을 추적
- Acquisition (획득), Activity (활성화), Retention(유지), Revenue(수익), Referral(추천) 5단계로 나뉨
- 이탈율과 전환율에 집중하기 보다, 각 단계를 세밀하게 분석
  
  (출처 - 요즘 IT : https://yozm.wishket.com/magazine/detail/1071/)

AARRR의 5단계

Acquisision(흭득)
- user가 서비스를 처음 접하는 단계
- 광고, SNS, SEO
- 지표 : 고객 획득 비용(CAC: Customer Acquisition Cost, 한 명의 고객을 획득하기 위해 지출되는 비용), 일간/월간, 주간 활성 유저(DAU: Daily Active User, WAU: Weeknedly , MAU : Monthly)
Activity (활성화)
- 사용자가 서비스를 처음 이용하거나 행복한 경험 happy experience 를 느끼는 단계 (가치)
- 회원가입, 첫구매
- 페이지뷰(PV : page view, 둘러본 페이지수, 다양한 페이지를 방문한다는 뜻), 체류시간(DT: Duration Time), 아하모먼트(Aha moment, 제품의 핵심 가치를 고객이 경험하는 결정적인 순간)
Retention(유지)
- 사용자가 일정기간 지속적으로 서비스를 반복하여 사용하는 단계
- 재방문, 재구매
- 리텐션율(retention rate, 고객이 유지되는 비율), 이탈율 (churn rate)
Revenue (수익)
- 사용자가 서비스를 통해 수익 발생
- 유료결제, 구독
- 고객 생애가치 (LTV : Life Time Value, 고객 한 명이 자신의 일생동안 기업에 가져다주는 이익의 합), 결제 유저(pu : paying user, 실제로 결제하는 고객), 유저 당 평균 수익(ARPU : averahe revenue per user, 전체수익/전체회원 값)
Referral (추천)
- 다른 사람에게 추천하는 단계. 입소문
- 친구초대, 리뷰작성
- 사용자 언급 댓글수, SNS 공유수, 레퍼럴 트래픽(referral traffic, GA용어, 다양한 채널에서 웹사이트로 유입된 트래픽)

📌 SQL 쿼리

count if, count distinct , max case

검색해보니까 여러 방식으로 퍼널을 구현할 수 있었다.
어떤 방식이 좋을 지 ChatGPT에 비교를 요청했더니 다음과 같았다.
📌 목표에 따라 추천하는 방식이 다름!
- 단순 퍼널 분석, 유저 수만 반환하여 이탈률 및 단계별 분석 추적이 어려울 수도! → COUNT(DISTINCT user_id) 사용
- 유저별 세부 분석 필요 → MAX(CASE WHEN ... THEN 1 ELSE 0 END) 사용
- MySQL/PostgreSQL에서 성능 최적화 필요, 가독성이 좋음 → COUNT(IF(...)) 사용

count case를 사용

SELECT 
    COUNT(DISTINCT CASE WHEN event_name = 'visit' THEN user_id END) AS visit_users,
    COUNT(DISTINCT CASE WHEN event_name = 'view_product' THEN user_id END) AS view_product_users,
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS add_to_cart_users,
    COUNT(DISTINCT CASE WHEN event_name = 'checkout' THEN user_id END) AS checkout_users,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchase_users
FROM events;

max case를 사용

WITH funnel AS (
   SELECT 
       user_id,
       MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS visit,
       MAX(CASE WHEN event_name = 'view_product' THEN 1 ELSE 0 END) AS view_product,
       MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS add_to_cart,
       MAX(CASE WHEN event_name = 'checkout' THEN 1 ELSE 0 END) AS checkout,
       MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchase
   FROM events
   GROUP BY user_id
)
SELECT
   COUNT(*) AS total_users,
   SUM(visit) AS visit_users,
   SUM(view_product) AS view_product_users,
   SUM(add_to_cart) AS add_to_cart_users,
   SUM(checkout) AS checkout_users,
   SUM(purchase) AS purchase_users
FROM funnel;

count if를 사용

SELECT 
   COUNT(IF(event_name = 'visit', user_id, NULL)) AS visit_users,
   COUNT(IF(event_name = 'view_product', user_id, NULL)) AS view_product_users,
   COUNT(IF(event_name = 'add_to_cart', user_id, NULL)) AS add_to_cart_users,
   COUNT(IF(event_name = 'checkout', user_id, NULL)) AS checkout_users,
   COUNT(IF(event_name = 'purchase', user_id, NULL)) AS purchase_users
FROM events;