[TIL] GA4 데이터를 활용한 프로덕트 분석 - Day2 (2024-05-20)

SHU·2024년 5월 20일

BigQuery TIL sql

GA4 데이터를 활용한 프로덕트 분석

목록 보기

2/6

Summary
Google BigQuery를 활용한 데이터 분석

DAU, WAU, MAU

Bounce Rate

시간 및 요일별 방문자수

용어 설명

DAU, WAU, MAU

DAU : Daily Active User : 일간 활성 유저 수
WAU : Weekly Active User : 주간 활성 유저 수
MAU : Monthly Active User : 월간 활성 유저 수
→ Active User(활성 유저)란? 서비스에 접속하여 특정 행동에 도달한 유저를 의미. 특정 행동은 고정되어 있지 않으며 분석의 목적에 따라 정의하기 나름.

Bounce Rate

이탈률. 전체 DAU(접속한 유저 수)로 잡혔지만 특정 행동에 도달하지 못한 사람들의 비율.

오늘의 진도

total DAU 및 bounce rate 구하기

한 방문자가 하루에 여러 번 접속을 한 경우가 있기 때문에 활성 유저 수는 방문자 당 하나씩 고유한 값을 가지는fullVisitorId를 중복 없이 카운트하여 구함
이탈률의 경우, 우선 GA4에서 제공하는 이탈여부 컬럼인 totals_bounces를 활용하여 하루에 이탈이 한번 이상 있었던 방문자를 이탈 유저로 정의

SELECT d.`date`, 
	TOTAL_DAU, 
    BOUNCE, 
    (BOUNCE / TOTAL_DAU * 100) BOUNCE_RATIO
FROM
(-- total DAU 구하기
  SELECT `date`, COUNT(DISTINCT `fullVisitorId`) TOTAL_DAU
  FROM `ga`
  GROUP BY `date`
) d
INNER JOIN ( -- 이탈 구하기
  SELECT `date`, COUNT(DISTINCT `fullVisitorId`) BOUNCE
  FROM ( -- 방문자id별로 하루에 이탈이 한번 이상 있었던 고객 구하기
    SELECT  `date`, `fullVisitorId`, SUM(`totals_bounces`) BOUNCES
    FROM `ga`
    GROUP BY `date`, `fullVisitorId`
    HAVING BOUNCES >= 1
  ) id_r
  GROUP BY `date`
) r
ON d.`date` = r.`date`
ORDER BY 1;

DAU, WAU, MAU 구하기

bounce가 없는 유저(= transaction까지 진행한 유저)를 활성 유저로 정의
날짜별로 bounce가 없는 유저만 추출
일 / 주 / 월 단위로 fullVisitorId를 중복 없이 카운트

시각화 시, 주 번호 및 월만 있으면 DAU, WAU, MAU를 한 그래프에 겹쳐 그리기 어려우므로 각 주 / 월의 시작 날짜를 쿼리에 포함시킴
LEFT JOIN하여 WAU 혹은 MAU가 없는 날짜가 최종 결과에서 누락되지 않도록 함

SELECT d.START_DATE `DATE`, DAU, WAU, MAU
FROM (
  SELECT `date` START_DATE, 
    COUNT(DISTINCT `fullVisitorId`) DAU
  FROM (
    SELECT  `date`, `fullVisitorId`, SUM(`totals_bounces`) BOUNCES
    FROM `ga`
    GROUP BY `date`, `fullVisitorId`
    HAVING BOUNCES IS NULL
  ) id_r
  GROUP BY START_DATE
) d
LEFT JOIN (
  SELECT EXTRACT(WEEK FROM `date`) WEEK,
    MIN(`date`) START_DATE,
    COUNT(DISTINCT `fullVisitorId`) WAU
  FROM (
    SELECT  `date`, `fullVisitorId`, SUM(`totals_bounces`) BOUNCES
    FROM `ga`
    GROUP BY `date`, `fullVisitorId`
    HAVING BOUNCES IS NULL
  ) id_r
  GROUP BY WEEK
) w
ON d.START_DATE = w.START_DATE
LEFT JOIN (
  SELECT EXTRACT(MONTH FROM `date`) MONTH,
    MIN(`date`) START_DATE,
    COUNT(DISTINCT `fullVisitorId`) MAU
  FROM (
    SELECT  `date`, `fullVisitorId`, SUM(`totals_bounces`) BOUNCES
    FROM `ga`
    GROUP BY `date`, `fullVisitorId`
    HAVING BOUNCES IS NULL
  ) id_r
  GROUP BY MONTH
) m
ON d.START_DATE = m.START_DATE
ORDER BY 1;

시간 및 요일별 방문자 수

pivot table

피봇 오퍼레이터를 활용하여 긴 쿼리를 작성하지 않고도 쉽게 pivot할 수 있음
피봇 오퍼레이터는 다음과 같이 구성됨

FROM 테이블
PIVOT (
	집계식
	FOR 피봇 대상 컬럼
    IN 피봇한 결과 테이블에서 컬럼으로 쓰일 값들
)

요일을 CASE문으로 만든 뒤, 피봇 오퍼레이터를 활용해 피봇하는 쿼리를 아래와 같이 작성

BigQuery는 mySQL과 달리 날짜의 특정 부분을 추출할 때 EXTRACT([DATE_PART] FOR [DATETIME]) 구문을 사용

SELECT *
FROM (
  SELECT 
    CASE -- 요일 컬럼 만들기
      WHEN EXTRACT(DAYOFWEEK FROM `date`) = 1 THEN 'SUN'
      WHEN EXTRACT(DAYOFWEEK FROM `date`) = 2 THEN 'MON'
      WHEN EXTRACT(DAYOFWEEK FROM `date`) = 3 THEN 'TUE'
      WHEN EXTRACT(DAYOFWEEK FROM `date`) = 4 THEN 'WED'
      WHEN EXTRACT(DAYOFWEEK FROM `date`) = 5 THEN 'THU'
      WHEN EXTRACT(DAYOFWEEK FROM `date`) = 6 THEN 'FRI'
      WHEN EXTRACT(DAYOFWEEK FROM `date`) = 7 THEN 'SAT'
    END DOW,
    EXTRACT(HOUR FROM `visitStartTime`) HOUR, 
    COUNT(DISTINCT `fullVisitorId`) VISIT_CNT
  FROM `ga`
  GROUP BY 1, 2
)
PIVOT (
  SUM(VISIT_CNT) VISIT_CNT
  FOR DOW
  IN ('SUN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT')
)
ORDER BY HOUR;