SQL with 빅쿼리 - PV, UV, ARPU, ARPPU, AARRR, 리텐션 분석

SQL심화 - SQL로 데이터 분석하기

PV, UV란?

1️⃣ PV (Page View)

• 페이지가 열릴 때마다 1번씩 카운트되는 값
• 같은 사람이 여러 번 봐도 모두 PV로 계산
• 새로고침도 PV 증가
• 페이지 이동도 PV 증가
• 총 페이지 조회 수
• 허수가 많기 때문에 주의가 필요함

SELECT page_url, COUNT(user_id) AS PV
FROM `modulabs.visits`
GROUP BY page_url

2️⃣ Unique PV

• 한 사람이 같은 페이지를 여러 번 봐도 1번만 카운트
• 페이지 기준 중복 뷰를 제거한 조회수
• 특정 페이지의 중복 제거 조회

SELECT page_url, COUNT(DISTINCT user_id) AS UniquePV
FROM `modulabs.visits`
GROUP BY page_url

3️⃣ Visits (Session)

• 사이트에 들어와서 나가기까지의 한 번의 방문
• 사이트 진입 → 여러 페이지 이동 → 사이트 나감 = 1 Visit
• 방문 횟수 (세션 수)
• 세션 단위가 30분이라면 30분 후 다시 접속하면 = 1 Visit

WITH LAST_SESSION AS (
   SELECT log_id, user_id, page_url, timestamp, 
      LAG(timestamp) OVER(PARTITION BY user_id, page_url ORDER BY timestamp) AS last_timestamp
   FROM `modulabs.visits`
),

   SESSION_DIFF AS (
   SELECT log_id, user_id, page_url, timestamp, last_timestamp,
      TIMESTAMP_DIFF(timestamp, last_timestamp, MINUTE) AS diff_minute,
      CASE 
         WHEN last_timestamp IS NULL THEN 1
         WHEN TIMESTAMP_DIFF(timestamp, last_timestamp, MINUTE) > 30 THEN 1
         ELSE 0
      END AS new_visit_flag
   FROM LAST_SESSION
)

SELECT page_url, SUM(new_visit_flag) AS Visits
FROM SESSION_DIFF
GROUP BY page_url

4️⃣ UV (Unique Visits / Unique Visitors)

• 중복을 제거한 방문자 수
• 같은 사람이 여러 번 방문해도 1명으로 계산
• 실제 방문자 수

SELECT page_url, COUNT(DISTINCT user_id) AS UV
FROM `modulabs.visits`
GROUP BY page_url

< PV, UV 예시 >

사용자	행동
A	페이지 3개 조회
B	페이지 2개 조회
A	다시 방문해서 2개 조회

지표	값
PV	7
Visits	3
UV	2

ARPU와 ARPPU란?

1️⃣ ARPU (Average Revenue Per User)

• 전체 사용자 기준 평균 매출
• ARPU = 총 매출 / 전체 사용자 수

2️⃣ ARPPU (Average Revenue Per Paying User)

• 결제한 사용자 기준 평균 매출
• ARPPU = 총 매출 / 결제 사용자 수

SELECT 
   SUM(revenue) / COUNT(DISTINCT user_id) AS ARPU, 
   SUM(revenue) / COUNT(DISTINCT CASE WHEN revenue > 0 THEN user_id END) AS ARPPU
FROM `modulabs.arpu`

퍼설 분석이란?

• 퍼널 분석은 사용자가 목표 행동까지 가는 과정을 단계별로 쪼개서, 어디서 많이 이탈하는지 보는 분석
• 마케팅, 서비스 기획, PM, 데이터 분석에서 많이 쓰임
• 사람들은 한 번에 구매하거나 가입하지 않음 → 여러 단계를 거치고, 각 단계마다 일부가 떠러여 나감 → 그 흐름을 깔때기(funnel)처럼 보는 것이 퍼널 분석
• 단순히 유저가 적다를 보는 게 아니라, 정확히 어디서 문제가 생기는지 찾게 해줌

퍼널 분석의 목적

• 문제 구간 찾기
• 개선 우선순위 정하기
• A/B 테스트 효과 검증
• 마케팅/제품 성과 측정

퍼널 분석에서 꼭 보는 지표

1. 단계별 사용자 수 : 각 단계에 몇 명이 도달했는지
2. 전환율 : 이전 단계 대비 다음 단계로 넘어간 비율

전환율 = 다음 단계 사용자 수 / 이전 단계 사용자 수

1. 이탈률 : 이전 단계에서 다음 단계로 못 간 비율

이탈률 = 1 - 전환율

1. 최종 전환율 : 맨 처음 단계 대비 마지막 목표 달성 비율

최종 전환율 = 방문(처음 단계) / 결제 완료(마지막 목표)

AIDA 모델

• AIDA는 전통적인 마케팅 퍼널 모델
• 광고/마케팅 메시지 관점 강함
• 사람의 심리적 변화를 설명하는 데 적합
• 고객이 광고나 메시지를 보고 구매하기까지의 심리 흐름을 설명할 때 많이 쓰임
• 광고 성과, 콘텐츠 카피, 캠페인 메시지 분석
• 한계 : 현대 서비스처럼 반복 방문, 재방문, 추천, 리텐션을 충분히 설명하지 못함

Attention(인지)	고객이 브랜드나 제품을 알게 되는 단계
Interest(관심)	고객이 “오? 괜찮은데?” 하고 관심을 가지는 단계
Desire(욕구)	고객이 실제로 갖고 싶다고 느끼는 단계
Action(행동)	실제 행동으로 이어지는 단계

AARRR 모델

• AARRR은 스타트업과 디지털 서비스 분석에서 매우 유명한 프레임워크
• 보통 해적 지표(Pirate Metrics)라고도 불림
• 앱/웹 서비스 성장, 온보딩, 리텐션, 수익화 분석
• AIDA보다 훨씬 제품 중심 / 성장 중심

Acquisition(유입)	사용자가 어떻게 처음 들어왔는가
Activation(활성화)	사용자가 처음 와서 “가치를 경험한 상태”에 도달했는가
Retention(유지)	사용자가 다시 돌아오는가
Revenue(매출)	매출이 발생하는가
Referral(추천)	사용자가 다른 사용자를 데려오는가

AIDA와 AARRR의 차이

항목	AIDA	AARRR
출발점	마케팅 메시지	제품 성장
초점	구매 전 심리 변화	유입부터 유지, 매출, 추천까지
활용 분야	광고, 브랜딩, 카피라이팅	스타트업, 앱, SaaS, 서비스 분석
마지막	행동(Action)	매출과 추천까지 확장
반복 사용 고려	약함	강함

SAFE_DIVIDE(a, b) : 나눗셈할 때 쓰는 안전한 함수, b가 0 일 때 에러 대신 NULL 반환함

ROUND(..., 2) : 소수점 둘째 자리까지 반올림

리텐션 분석이란?

• 사용자가 서비스에 다시 돌아오는지, 얼마나 오래 계속 사용하는지를 분석하는 방법
• 앱, SaaS, 게임, 커머스, 플랫폼 서비스에서 매우 중요한 지표
• 리텐션은 특정 시점에 유입된 사용자들이 시간이 지나도 계속 활동하는 비율
• 서비스 성공 여부를 판단하는 가장 중요한 지표 중 하나
• 예시

날짜	활동한 유저
Day0 (가입일)	100
Day1	40
Day7	20
Day30	10

Day1 Retention = 40 / 100 = 40%
Day7 Retention = 20 / 100 = 20%
Day30 Retention = 10 / 100 = 10%

리텐션의 3가지 주요 방식

• 리텐션 계산 방식에는 대표적으로 세 가지가 있음

1. 클래식 리텐션 (Classic Retention) : 특정 날짜에 정확히 그날 다시 온 사용자 비율

• 가장 많이 사용하는 방식
• 코호트 리텐션 그래프에 사용
• 시간이 지날수록 계속 감소

Day2 Retention = Day2에 방문한 유저 / Day0 유저

1. 롤링 리텐션 (Rolling Retention) : 특정 날짜 이후에 한 번이라도 돌아온 사용자 비율

• 값이 클래식보다 항상 높음
• 감소 속도가 느림
• 서비스 “수명”을 보는 데 좋음

Rolling Day2 = Day2 또는 이후 방문 유저 / Day0 유저

1. 랜지 리텐션 (Range Retention) : 특정 기간 사이에 방문한 사용자 비율

• 기간 기반 분석
• 마케팅 성과 분석에 좋음
• CRM 분석에 많이 사용

Range Retention (Day1~7) = 60 / 100 = 60%

유형	의미	특징
Classic Retention	특정 날짜에 정확히 방문	가장 일반적
Rolling Retention	해당 날짜 이후 한 번이라도 방문	값이 높음
Range Retention	특정 기간 안에 방문	마케팅 분석에 유용

• DATE_DIFF(date1, date2, unit) : 두 날짜 사이의 차이를 계산하는 함수

SELECT DATE_DIFF('2023-04-01', '2023-01-01', MONTH)

결과 : 3

단위	의미
DAY	일 차이
WEEK	주 차이
MONTH	월 차이
YEAR	연 차이
- DATE_TRUNC(date, unit) : 날짜를 특정 단위 기준으로 잘라주는 함수

# 월의 시작 날짜로 반환
SELECT DATE_TRUNC('2023-02-15', MONTH)

결과 : 2023-02-01

단위	결과
DAY	그대로
WEEK	그 주의 시작일
MONTH	그 달의 1일
YEAR	그 해의 1월1일

RFM 분석이란?

• RFM 분석은 고객의 구매 패턴을 3가지 기준으로 평가해서 고객을 그룹으로 나누는 방법

1. Recency (최근 구매일) : 고객이 마지막으로 구매한 시점이 얼마나 최근인지

• Recency 값이 작을수록 좋은 고객
• 재구매 가능성을 보여주는 지표

Recency = 오늘 날짜 - 마지막 구매일

1. Frequency (구매 빈도) : 고객이 일정 기간 동안 몇 번 구매했는지

• Frequency가 높을수록 충성 고객일 가능성
• 서비스에 대한 만족도가 높을 가능성

Frequency = 구매 횟수

1. Monetary (구매 금액) : 고객이 얼마나 많은 돈을 썼는지

• Monetary가 높을수록 고객 가치(Customer Value)가 높음

Monetary = 총 구매 금액

1. RFM Score : 각각 R, F, M을 1~5점으로 점수를 매겨서 합친 값

NTILE(5) OVER (ORDER BY Recency DESC) AS RecencyScore,
NTILE(5) OVER (ORDER BY Frequency ASC) AS FrequencyScore,
NTILE(5) OVER (ORDER BY Monetary ASC) AS MonetaryScore

RFMScore = RecencyScore + FrequencyScore + MonetaryScore

NTILE(5) : 데이터를 5등분해서 1~5 점수를 부여하는 함수

RecencyScore : 오늘 날짜와 최근 구매일 차이니까 작을수록 좋은 값, 최근에 구매한 사람에게 높은 점수를 줘야 하므로 큰 값(오래 안 산 사람)부터 DESC 정렬

FrequencyScore : 구매 횟수는 많을수록 좋은 값

MonetaryScore : 구매 금액은 클수록 좋은 값

RFM 고객 세그먼트

• RFM 점수를 기반으로 고객을 분류할 수 있음

고객 유형	특징
VIP 고객	R 높음, F 높음, M 높음
충성 고객	F 높음
잠재 VIP	R 높음
이탈 위험 고객	R 낮음
휴면 고객	R 낮음, F 낮음

RFM 분석 활용

< VIP 고객 전략 >

• VIP 이벤트, 프리미엄 혜택, 개인화 추천

< 충성 고객 전략 >

• 포인트 프로그램, 멤버십 혜택, 리워드

< 이탈 고객 전략 >

• 할인 쿠폰, 리텐션 캠페인, 리마케팅 광고

< 신규 고객 전략 >

• 온보딩 프로모션, 첫 구매 할인