분석 연습 _MAU, WAU, DAU, Stickiness

BigQuery 환경 & ga.sessions_* 데이터셋

코테 나올 것 같은 문제로 연습해보기. 데분으로서 기본적으로 조회할 것 같은 문제 다뤄보기가 목표.

• 데이터 연결

-- BigQuery
PARSE_DATE   #문자열 > DATE타입으로 변환 

-- <-> MYSQL 
DATE_FORMAT  #DATE > 문자열로 변환 

#1. MAU (월간 활성화된 유저수)

-- DATE > 문자열 변환 함수? 
-- MySQL
DATE_FORMAT('2024-07-10', '%Y-%m-%d')

-- BigQuery (형식먼저, 날짜)
FORMAT_DATE('%Y-%m-%d', (DATE 형식인)'2024-07-10')

-- 문제1. 월별 활성 유저수 집계하라. (MAU) > 조회 약 11초 걸림 
-- year_month로 잘라서 > 그걸로 group by > 유저id count 집계
WITH user_activity as (
  SELECT
      fullVisitorId as user_id
    , PARSE_DATE('%Y%m%d', date) as year_month 
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
)

SELECT FORMAT_DATE('%Y-%m', year_month) as year_month
     , COUNT(DISTINCT user_id) as MAU
FROM user_activity
GROUP BY year_month
ORDER BY year_month

-- 답2) 한번에 다시 해보기
-- 1) YEAR_MONTH, DAU 집계할거니 DISTINCT fullVisitorId, YEAR_MONTH 형태가 필요
--    DATE 컬럼 문자열 > DATE 타입으로 바꿔서 > 원하는 형태 문자열로 뽑아야함 
--    즉, PARSE_DATE(적힌 데이터 형태로 써줘야함) > FORMAT_DATE
-- 2) 월별 집계 

SELECT FORMAT_DATE('%Y-%m', PARSE_DATE('%Y%m%d', date)) as year_month
     , COUNT(DISTINCT fullVisitorId) as MAU
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
GROUP BY year_month
ORDER BY year_month

-- 문제1-2. MAU 증감율까지 표시하라.
-- BigQuery 윈도우 함수 중 탐색함수?
LAG(불러올 항목, 오프셋) OVER (ORDER BY(필수) 유저가 지정하는 행의 그룹 조건) 

-- 답1) 증감률 = (현재값-전값) / 전값 * 100
-- ROUND 함수는 기본적으로 소수점 유지됨 > 따라서 결과가 -4.0 이렇게 표시됨 
SELECT year_month
     , MAU
     , ROUND((MAU - LAG(MAU, 1) OVER (ORDER BY year_month)) / LAG(MAU, 1) OVER (ORDER BY year_month) * 100, 0) as growth
FROM MonthlyActiveUsers
ORDER BY year_month
;

-- 답2) 따라서, 대안은 CAST 함수 이용하여 정수로 바꿔준다. + NULL 경우 조건 추가. 
SELECT year_month
     , MAU
     , IFNULL(CAST((MAU - LAG(MAU, 1) OVER (ORDER BY year_month)) / LAG(MAU, 1) OVER (ORDER BY year_month) * 100 AS INT), 0) as growth_rate
FROM MonthlyActiveUsers
ORDER BY year_month
;

MAU, 증감율 조회 결과

#2. WAU (주간 활성화된 유저수)

-- 날짜를 주 단위로 자르는 함수?
-- BigQuery
-- DATE_TRUNC(DATE 타입 날짜, 지정 단위) 함수 
DATE_TRUNC('2023-04-15', MONTH)  #날짜를 지정된 단위(예: 월, 년)로 잘라내어 반환

-- MySQL
-- DATE_FORMAT 함수 이용 
DATE_FORMAT('2024-07-10', '%Y-%m-01')   #월 단위로 자르는법
DATE_FORMAT('2024-07-10', '%Y-01-01')   #년 단위 

-- 문제2. 주간 활성화 이용자 수를 파악하라. (WAU)
-- 1) YEAR_MONTH, WAU 집계할거니 DISTINCT fullVisitorId, YEAR_MONTH 형태가 필요
--    DATE 컬럼 문자열 > DATE 타입으로 바꿔서 > 원하는 형태 문자열로 뽑아야함 
--    즉, PARSE_DATE(적힌 데이터 형태로 써줘야함) > FORMAT_DATE
-- 2) 주별 집계 > 주별로 자르는법? DATE_TRUNC

SELECT DATE_TRUNC(PARSE_DATE('%Y%m%d', date), WEEK) as week
     , COUNT(DISTINCT fullVisitorId) as WAU
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
GROUP BY week
ORDER BY week

WAU 조회 결과

• • GROWTH_RATE 구해보기

#3. DAU (일일 활성화된 유저수)

-- 문제3. 일간 활성화 사용자수 구하라. (DAU)
-- 1) 일단위로 문자열 형태로 가져와서 USER_ID 집계하면됨 
--    문자열 > DATE > 원하는 형태의 문자열 반환 (이렇게 안해도 되군)
--    (애초에 DATE 타입이 2000-00-00 형태/ 대신 문자열이 아닌 DATE타입)
SELECT PARSE_DATE('%Y%m%d',date) as day
     , COUNT(DISTINCT fullVisitorId) as DAU
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
GROUP BY day
ORDER BY day

DAU 조회 결과

• +방문 빈도에 따른 dau 문제 추가

4. Stickiness (참여도 및 충성도)

Stickiness가 높을수록 사용자들이 자주 방문하고 이용하고 있는 것을 의미.

보통 DAU와 MAU의 비율로 표현.

Stickiness = DAU / MAU × 100

월간 사용자 중 (Stickiness)비율 만큼 매일 해당 서비스를 사용하고 있음을 의미.

-- 문제4. 일일 활성사용자수, 월간 활성사용자수를 구하여 Stickiness를 계산하라. 
-- DAU, MAU 계산하고 > DAU/MAU *100 
-- 1) MAU :월 단위/ 집계 (date 문자열 형태 > date 타입 > 원하는 문자열 형태로)
-- 2) DAU :일 단위/ 집계 (그냥 date 로 집계하면 됨 > 보기 편하려면 DATE 타입으로)
-- 3) 단위가 다른데 어떻게 나누지? 기본 일 단위/ 그 해당 달의 MAU로 나눠줘야함 
--    > join하려면 dau에 year_month 칼럼 추가하면, year_month 내 일일 집계됨

WITH MonthlyActiveUsers AS (
  SELECT FORMAT_DATE('%Y-%m', PARSE_DATE('%Y%m%d', date)) as year_month
      , COUNT(DISTINCT fullVisitorId) as MAU
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  GROUP BY year_month
  ORDER BY year_month
), 
DailyActiveUsers AS (
  SELECT FORMAT_DATE('%Y-%m', PARSE_DATE('%Y%m%d', date)) as year_month
      , PARSE_DATE('%Y%m%d',date) as day
      , COUNT(DISTINCT fullVisitorId) as DAU
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  GROUP BY year_month, day
  ORDER BY year_month, day
)

SELECT d.day as visit_date
     , d.DAU
     , m.MAU
     , CONCAT(ROUND(d.DAU / m.MAU * 100, 1), '%') as Stickiness
FROM MonthlyActiveUsers m
JOIN DailyActiveUsers d
  USING (year_month)
ORDER BY d.day
;

Stickiness 조회 결과

즉, 첫 행을 보자면, 지난 30일(2016/08) 동안 서비스 사용자 중 2.5%가 특정 하루(2016/08/01) 동안 서비스를 사용했다는 의미.

예를 들어, Stickiness 만 안다면

2016년 8월의 MAU를 10,000명이라고 가정하여 역계산으로

DAU = 2.5 * 100 = 250 (명)을 구할 수도 있음.

여기서 나아가 사용자 참여도(즉, Stickiness)가 특정 비율 이상이 되는 날짜나 시즌을 구해볼 수도 있을 것 같다. 분야에 따라 사용자 참여도에 따른 해석이 달라지겠지만, Stickiness가 낮다면 참여도를 높이기 위한 개선방안이 필요하다는 결론 내릴 수 있다.

cf. 참고자료 출처

• <Google BigQuery가이드북: 데이터로 풀어보는 소비자 행동분석>, 리디북스 e북 — https://ridibooks.com/books/2773000081