[Big Query] SQL로 데이터 분석하기 (실습 6문제 - PV,UV계산, APRU/APRRU, Funnel 분석, User Retention, RFM Segmentation)

학습 내용

---

SQL 심화 콘텐츠는 총 4개의 노드로 구성되어 있다.

1. ✅  빅데이터 톺아보기
   • 빅데이터를 지탱하는 툴과 기술
   • 빅쿼리(BigQuery) 실습 환경 설정하기

https://jpocket.tistory.com/29

 

2. ✅ SQL 기초 체력 키우기

• 주문 내역에서 VIP 고객 찾기
• 레스토랑 손님 중 대식가 찾기
• 우리 사이트 베스트 상품 추천!
• 베스트 리뷰어에게 경품을 주자
• 다음 환자분, 진료실로 들어오세요

https://jpocket.tistory.com/30

[Big Query] BigQuery개념 정리, SQL 기초 체력 키우기(실습 5문제)

 

3. ✅ 실무에서 많이 쓰이는 고급 SQL 기술

• 윈도우 함수(Window Function) (1): 함수 구조와 순위 함수
• 윈도우 함수(Window Function) (2): 집계 함수
• 그룹함수
• 복잡한 데이터 처리(JSON Formatting)

https://jpocket.tistory.com/31

[SQL] 윈도우 함수(RANK, DENSE_RANK, ROW_NUMBER, 집계 함수, LAG, LEAD), 그룹함수(ROLLUP, CUBE, GROUPING SETS)

 

4. ✅ SQL로 데이터 분석하기 [프로젝트]

• 회사의 일별 매출액 구하기
• PV, UV 계산하기
• 쇼핑몰 일별 매출과 ARPPU 구하기
• 퍼널 분석: 사람들은 어디에서 이탈하고 있을까?
• 리텐션 분석: 내가 VIP가 될 상인가?
• RFM 분석

 

 

 

 

 

 

 

 

 

---

주회사의 일별 매출액 구하기

---

 

회사의 일별 매출액을 구하라.

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

SELECT DATE(TIMESTAMP(order_timestamp)) AS dt, sum(payments.value) as daily_value
FROM `white-might-456101-t9.modulabs.orders` as orders
join `white-might-456101-t9.modulabs.payments` as payments on orders.order_id = payments.order_id
group by dt
order by dt;

 

How

회사의 일별을 DATE함수를 이용해서 yyyy-mm-dd 형태로 변환한다.

payments 테이블의 value값에 대한 sum을 이용해서 합계 매출액을 구한다.

payments 테이블의 value 컬럼을 사용하기 위해 orders와 payments 테이블을 order_id로 조인한다.

일별 매출액이 되려면 dt로 group by 해주어야 하고, 이를 오름차순으로 정렬하여 출력한다.

 

 

 

 

 

 

 PV, UV 계산하기 

---

유저가 조회한 페이지 정보를 기록한 visits 테이블을 활용하여 PV, Unique PV, Visits, UV를 계산하라.

-- 1. 페이지별 PV
select page_url, count(user_id) as PV
from white-might-456101-t9.modulabs.visits as visits
group by page_url;



-- 2. 페이지 별 Unique PV
select page_url, count(distinct user_id) as UniquePV
from white-might-456101-t9.modulabs.visits as visits
group by page_url;




-- 3. 페이지별 Visits
with last_session as(
  select user_id, page_url, timestamp, LAG(timestamp,1) over (partition by user_id, page_url
order by timestamp) as last_timestamp
from `white-might-456101-t9.modulabs.visits`
),
session_diff as (
    select *, if(
    timestamp_diff(timestamp,last_timestamp,MINUTE) > 30 or last_timestamp IS NULL, 1, 0) as count_visit
  from last_session
)
  
select page_url, sum(count_visit) as visits
from session_diff
group by page_url
order by page_url;



-- 4. 페이지별 Unique Visitors
with last_session as(
  select user_id, page_url, timestamp, LAG(timestamp,1) over (partition by user_id, page_url
order by timestamp) as last_timestamp
from `white-might-456101-t9.modulabs.visits`
),
session_diff as (
    select *, if(
    timestamp_diff(timestamp,last_timestamp,MINUTE) > 30 or last_timestamp IS NULL, 1, 0) as count_visit
  from last_session
)
  
select page_url, count(distinct user_id) as UV
from session_diff
group by page_url
order by page_url;

 

 

용어	의미	중복 처리	단위
PV	페이지당 사용자 조회수, 매출 창출을 확인	허용	페이지 단위
Unique PV	페이지당 고유 사용자 조회수	제외	페이지 + 사용자
Visits	사이트 방문 사용자 수	허용	세션 단위
Unique Visits(UV)	고유 방문자	제외	사용자

📌
PV: 메뉴판 본 횟수
Unique PV: 메뉴판을 본 고유한 손님 수
Visits: 식당을 방문한 손님 수
Unique Visits: 식당을 방문한 고유한 손님 수

나는 상황에 비유하여 이해해 보았다.

그렇다면 이 개념들을 가지고 어떤 인사이트를 도출할 수 있을까❓

 

 

💡
PV가 높고, UV도 높다면 방문하는 사람이 여러 페이지를 방문
PV는 높은데 UV는 낮다면 서비스가 지나치게 길고 불편함을 의미
UV가 높은데 PV가 낮다면 방문하는 사람은 많은데 서비스를 많이 이용하지 않고 나감
UV도 감소, PV도 감소라면 방문자도, 서비스 이용도 줄었음

PV가 높으면 긍정적일 것 같지만, 무조건적으로 숫자가 높은게 좋은 것은 아니다. 🔥

 

 

📑 예시

쿠팡은 원클릭 결제를 도입하기 전,
결제까지 PV가 6단계를 거쳤지만
이후 4단계로 줄이면서 UX 개선을 이뤘어요.

(이런 느낌으로 PV 개념이 쓰이구나)

 

 

 

How

페이지별 PV는 페이지별로 그룹화하여 방문한 사용자 수를 출력하면 되므로 user_id의 개수를 중복 허용하여 세주면 된다.

페이지별 Unique PV는 중복을 제거해주면 되므로 user_id 앞에 distinct 를 붙여 출력한다.

페이지별 Visits는 페이지별로 그룹화하고, 사용자별 접속 시간 데이터를 활용하여

세션이 30분이라는 가정하에, 30분 이상 간격 두고 접속하면 새로운 방문으로 판단한 개수를 센다.

(session_diff에서 현재 접속 시각과 직전 접속 시각의 차이를 구하고 있다. 30분 초과이거나 처음 접속이라면 새로운 방문으로 적용되어 count_visit = 1이 된다.)

페이지별 UV는 중복을 제거해주면 되므로 user_id 앞에 distinct 를 붙이고 개수를 센다.

 

 

 

 

 

 

ARPU와 ARPPU 구하기

---

유저들의 구매 정보를 기록한 arpu 테이블을 활용하여 ARPU, ARPPU를 계산하라.

select
sum(revenue)/count( distinct user_id) as ARPU,
SUM(revenue) / COUNT(DISTINCT IF(revenue > 0, user_id, NULL)) AS ARPPU
from `white-might-456101-t9.modulabs.arpu`

 

 

용어	의미
ARPU	사용자당 평균 수익
ARPPU	유료 결제 사용자당 평균 수익

 

how

ARPU와 ARPPU의 개념을 알면 쉽게 풀 수 있다.

ARPU는 전체 수익을 사용자 수로 나눈 값이 되고, 🔥

ARPPU는 유료 결제 사용자당이므로 전체 수익을 유료 결제 사용자의 수로 나눈 값이 된다. 🔥

COUNT(DISTINCT IF(조건, 값, NULL)) = 조건을 만족하는 고유한 값의 개수만 세는 방법 🎯

 

💡조건에 거짓인 데이터는 NULL값을 반환하는데 COUNT함수는 NULL을 개수에서 제외시키므로 조건을 만족하는 고유한 값의 개수만 셀 수 있다.

 

 

 

 

 

 

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

---

각 페이지 단위의 방문 수와 visits 수를 모수로 한 페이지 단위의 전환율을 계산하라.

 

select countif(action='visit') as Visits,
countif(action='signup') as SignUps,
countif(action='add_to_cart') as AddToCarts,
countif(action='purchase') as Purchases,
round(countif(action='signup')/countif(action='visit')*100,2) as SignupRate,
round(countif(action='add_to_cart')/countif(action='visit')*100,2) as AddtoCartRate,
round(countif(action='purchase')/countif(action='visit')*100,2) as PurchaseRate
from `white-might-456101-t9.modulabs.funnel`;

 

how

ROUND(숫자, 소수점_자리)  = 조건을 만족하는 고유한 값의 개수만 세는 방법 🎯

 

visit - signup - add_to_cart - purchase 순서로 웹페이지에 접속하게 된다.

해당 페이지를 방문한 사람을 전체 방문자 수로 나눠주면 각 페이지 단위의 전환율을 계산할 수 있다.

퍼센트로 표현하기 위해 *100을 해주었고, round함수를 이용하여 소수 둘째자리 까지 나타낸다.

 

🔎 헷갈렸던 점은

카트에 담는 페이지로의 전환율을 구하기 위해서는 visit, signup에 방문한 고유한 사용자 수인 줄 알았는데

👉 웹사이트 전체 방문자 수였다.🚀

 

 

 

 

 

 

 

 

리텐션 분석: 내가 VIP가 될 상인가?

---

월별 리텐션을 구하라.

 

with first_date as (
  select user_id, min(invoice_date) as cohort_day
  from `white-might-456101-t9.modulabs`.retention as retention
  group by user_id
)
select DATE_TRUNC(cohort_day, MONTH) as cohort_group, DATE_DIFF(DATE(invoice_date), cohort_day, MONTH) as cohort_index,count(distinct first_date.user_id) as user_count
from `white-might-456101-t9.modulabs`.retention as retention
join first_date on first_date.user_id = retention.user_id
group by cohort_group, cohort_index
order by cohort_group, cohort_index;

 

how

WITH문을 사용하였다.

사용자별로 가장 최초의 구매 일자를 cohort_day 라는 컬럼 명으로 구한다.

 

메인쿼리에서, cohort_group은 같은 시기에 웹 사이트에 가입 또는 제품을 사용한 사용자들의 집단이다.

최초 구매 일자를 월단위로 잘라서(DATE_TRUNC활용) cohort_group 컬럼 명으로 구한다.

invoice_date에서 cohort_day의 차이를 월단위로 구한 것은 사용자가 최초 구매한 이후, 해당 활동이 몇 개월째인지 나타낸다. 이는 cohort_index 컬럼 명으로 구한다.

cohort_group과 cohort_index의 조합에서 고유한 사용자 수를 세어서 user_count 컬럼 명으로 구한다.

 

 

 

해당 쿼리문을 csv파일로 저장하여 피봇테이블까지 만들어보았다.

CSV파일

피벗테이블 변형

 

 

 

 

 

 

RFM 세그먼테이션 분석

---

Recency, Frequency, Monetary를 계산하고, 1점에서 5점까지의 척도로 각각 점수를 매긴 후 RFMScore를 구하라.

WITH REM AS (
  SELECT 
    user_id, 
    DATE_DIFF(CURRENT_DATE(), DATE(MAX(order_date)), DAY) AS Recency,
    COUNT(order_date) AS Frequency,
    SUM(order_value) AS Monetary
  FROM `white-might-456101-t9.modulabs.rfm`
  GROUP BY user_id
), 
REMScores AS (
  SELECT 
    user_id,
    Recency,
    Frequency,
    Monetary,
    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,
  FROM REM
)

SELECT *,
       (RecencyScore + FrequencyScore + MonetaryScore) AS RFMSScore
FROM REMScores
order by user_id;

 

how

REM 임시 테이블을 생성하여, Recency, Frequency, Monetary 3개의 컬럼을 추가한다.

용어	의미
Recency	최신성. 고객이 마지막으로 구매한 날짜로부터 현재까지 지난 일 수를 의미. → 값이 작을수록 최근에 활동한 고객
Frequency	자주성. 고객이 얼마나 자주 구매했는지 나타냄. → 보통은 구매 횟수, 즉 주문일자 개수를 센다
Monetary	구매성. 고객이 얼마나 많은 금액을 지출했는지 나타냄. → 보통은 구매 금액의 합계를 의미

 

 

REMScores는 점수를 내는 코드다.

NTILE(5)는 데이터 범위를 5개의 구간으로 나누는 함수 🎯

 

🔎 어떤 기준으로 ASC, DESC를 해야 하는지❓

Recency를 기준으로 내림차순하여 recency값이 높을수록 오래된 고객이니 이들에게 낮은 점수를 부여한다.

Frequency, Monetary는 값이 높을수록 좋은 것이므로 오름차순으로 정렬하여 값이 낮은 데이터에 낮은 점수를 부여한다.

"누가 먼저 낮은 점수를 받을지" 라고 생각하면 쉬운 듯 🔥

 

REMScores는 각 척도의 합산으로 계산한다.