Firebase와 BigQuery로 DAU, 체류시간, PV, Time on Page 집계

firebase 콘솔에서 DAU를 확인할 수 있지만 최근 통계만 볼 수 있고, 특정 그룹의 DAU를 확인하는 등의 세부 데이터 집계에는 어려움이 있다.

firebase 로그를 빅쿼리와 연동하면,

1. 오래 된 DAU/MAU 집계
2. raw 데이터로부터 원하는 통계 데이터를 자유롭게 집계

할 수 있는 이점이 있다.

반면, 단순 로그 데이터를 지지고 볶는 수고가 들고, 유지보수하기 귀찮은 방식이라는 단점이 있다.

장기적인 관점에서는 Google Analytics 같은 툴을 활용해서 원하는 데이터를 얻을 수 있는지 먼저 찾아보거나, braze, mixpanel 같은 솔루션 연동을 검토하는게 좋다.

하지만, 쿼리만으로 빠르게 DAU와 같은 통계 데이터를 얻고 싶을 때, (세션 로그를 쉽게 쌓을 수 있다면) 빅쿼리를 사용하는 것도 좋은 방법이다.

 

이번 글에서는 firebase 로그와 BigQuery 쿼리를 사용해서 DAU, 페이지뷰, 체류시간을 집계하는 방법을 정리하였다.

2021년 1월에 작업한 내용을 바탕으로 정리한 글입니다.
현재 상황과 많이 다를 수 있습니다.

 

 

Firebase 로그

firebase를 빅쿼리와 연동하면 analytics 데이터셋 events_ 멀티 테이블에 아래와 같이 이벤트 로그가 쌓인다.

아래 스크린샷은 events_intraday_<날짜> 테이블로서 오늘 발생한 이벤트 로그 테이블이다.
자정이 지나면 이 테이블이 events_<날짜> 테이블로 적재된다.

이벤트에는 세션시작, 화면전환, 설치 제거 등 다양한 종류가 있다.
(https://firebase.google.com/docs/reference/android/com/google/firebase/analytics/FirebaseAnalytics.Event 참고)

 

 

event_timestamp 컬럼으로 로그 시간을, event_name과 event_params 컬럼으로 이벤트 정보를 조회할 수 있다.

 

또한, firebase SDK를 통해 설정한 사용자 ID(user_id)와 사용자 속성(user_properties)을 조회할 수 있다.

 

 

DAU(Daily Active User), 체류시간(Duration Time) 집계

특정 event_date 일자에 특정 user ID의 로그가 기록됐다면, 해당일에 해당 사용자의 활동이 있었다고 볼 수 있다.

따라서, 아래와 같은 간단한 쿼리로 대략적인 DAU를 확인할 수 있다

실제로는, 설치 제거 등 실제 활동으로 볼 수 없는 이벤트를 제외하는 조건이 필요하다.
session_start, user_engagement 같은 특정 이벤트만 사용하여 집계할 수도 있다.

SELECT event_date,
  COUNT(DISTINCT user_id),
FROM `<프로젝트 ID>.analytics_<고유번호>.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20231201' AND '20991231' # 집계 기간 (_TABLE_SUFFIX는 멀티테이블 suffix 예약어로서 날짜를 뜻함)
GROUP BY event_date
ORDER BY event_date;

 

그런데, 사용자의 체류시간(DT)도 집계해야 한다면, 아래와 같이 쿼리가 조금 복잡해진다.

DECLARE TABLE_RANGE_FROM STRING DEFAULT '20231201'; -- 조회 기간 시작
DECLARE TABLE_RANGE_TO STRING DEFAULT '20991231'; -- 조회 기간 끝
DECLARE SESSION_GAP_THRESHOLD INT64 DEFAULT 30*60*1000*1000; -- 30분 이내 활동을 같은 세션으로 상정
DECLARE SESSION_DURATION_THRESHOLD INT64 DEFAULT 2; -- 2초 이상 세션만 집계 (user_engagement 집계와 가장 유사)

WITH session_log AS (
  SELECT *, COUNT(new_session OR NULL) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS session_id
  FROM (
    SELECT *, (prev_event_timestamp IS NULL OR event_timestamp - prev_event_timestamp >= SESSION_GAP_THRESHOLD) AS new_session
    FROM (
      SELECT event_date,
        user_id,
        event_timestamp,
        LAG(event_timestamp, 1) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS prev_event_timestamp
      FROM `<프로젝트 ID>.analytics_<고유번호>.events_*`
      WHERE _TABLE_SUFFIX BETWEEN TABLE_RANGE_FROM AND TABLE_RANGE_TO # 집계 기간
    )
  )
),
session AS (
  SELECT event_date,
    user_id,
    session_id,
    (MAX(event_timestamp) - MIN(event_timestamp)) / (1000*1000) AS duration
  FROM session_log
  GROUP BY event_date, user_id, session_id
  HAVING duration >= SESSION_DURATION_THRESHOLD # 30분이 넘어가는 이벤트는 새로운 세션으로 간주
),
stats AS (
  SELECT event_date,
    COUNT(DISTINCT user_id) AS dau,
    COUNT(*) AS sessions,
    SUM(duration) AS duration
  FROM session
  GROUP BY event_date
)
SELECT
  PARSE_DATE('%Y%m%d', event_date) AS date,
  dau,
  sessions,
  duration,
  ROUND(duration / NULLIF(sessions, 0), 1) AS avg_duration,
FROM stats
ORDER BY event_date

 

이벤트 로그로부터 세션 정보를 구성하여 집계하는 방식인데, 대략적인 쿼리 구조는 다음과 같다.

1. session_log CTE 테이블
   1. 이벤트 로그를 조회
   2. 이 때, 해당 사용자의 이전 로그와의 시간 간격으로 "새로운 세션 여부" 컬럼 new_session 추가
      시간 간격이 30분 이상이면 new_session 1, 아니면 0 설정
   3. 윈도우 함수(OVER)를 사용해서 COUNT(new_session) 값을 세션 ID로 부여
2. session CTE 테이블
   1. 특정 event_date, user_id에 대해 session_id 별 체류시간(duration) 계산
   2. 체류시간이 2초 미만이면 제외
3. stats 테이블
   1. event_date 단위로 dau, 세션 수, 총 duration 집계
4. SELECT 쿼리
   1. dau, sessions, duration, 평균 duration 조회

위 설명에서 보이는 것처럼 세션을 구분하기 위해 임의적인 조건이 사용되므로, 서비스 성격에 따라 수정이 필요할 수 있다.

 

쿼리 실행 결과는 아래와 같은 모습이다.

 

 

페이지뷰(PV), 페이지 체류시간(Time on Page) 집계

페이지 별로 뷰 수와 체류시간을 알고 싶을 때에는 추가적으로 페이지 관련 이벤트 로그가 필요하다.

1. 페이지 진입 이벤트: PV 집계를 위해 페이지 진입 시 기록
2. 페이지 종료 이벤트: 팝업 체류시간 계산을 위해 팝업을 닫을 때 기록

페이지뷰는 단순히 페이지 진입 이벤트를 총합하면 된다.

페이지 체류시간 집계 시에는 이 페이지가 팝업인지 고려해야 한다. 일반 페이지의 경우 다른 페이지로 이동한 시점으로 체류시간을 계산할 수 있지만, 팝업은 일반 페이지 위에 여러 개가 동시에 떠 있을 수 있으므로 명시적으로 종료 이벤트가 필요하다. 따라서, 체류시간 계산을 위해서 다음과 같은 이벤트 속성이 필요하다.

1. 페이지 타입: 일반 페이지 or 팝업 구분
2. 페이지 진입 or 종료 구분
3. 페이지뷰 인덱스 (진입과 종료 이벤트를 연결하기 위해)

위 조건을 만족하기 위해 다음과 같이 사용자 이벤트 및 속성을 설계하였다.

1. 이벤트 종류
   1. 페이지 진입: PageView
   2. 페이지 종료: PageClosed
2. 이벤트 속성
   1. pv_type: page(일반 페이지), modal(팝업)
   2. pv_index: 페이지뷰 고유번호
   3. pv_name: 페이지 이름

page 타입의 PageView 이벤트

modal 타입의 PageView, PageClosed 이벤트

 

참고로 사용자 이벤트는 firebase Analytics의 logEvent 함수를 사용하여 아래와 같이 전송할 수 있다.

import { getAnalytics, logEvent } from "firebase/analytics";

const analytics = getAnalytics();
logEvent(analytics, 'select_content', {
  content_type: 'image',
  content_id: 'P12453'
});

 

적재된 페이지뷰 이벤트 로그로부터 PV, 체류시간을 집계하는 쿼리와 실행 결과는 아래와 같다.

DECLARE TABLE_RANGE_FROM STRING DEFAULT '20231201'; -- 조회 기간 시작
DECLARE TABLE_RANGE_TO STRING DEFAULT '20991231'; -- 조회 기간 끝

WITH pv_log AS (
    SELECT *,
        COALESCE(ga_session_id, CAST(event_date AS INT64)) AS session_id,
    FROM (
        SELECT event_date,
            event_timestamp,
            event_name,
            user_id,
            PARSE_DATE('%Y%m%d', event_date) AS date,
            (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'pv_type') AS pv_type,
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'pv_name') AS pv_name,
            (SELECT CAST(value.double_value AS INT64) FROM UNNEST(event_params) WHERE key = 'pv_index') AS pv_index,
        FROM `<프로젝트 ID>.analytics_<고유번호>.events_*`
        WHERE _TABLE_SUFFIX BETWEEN TABLE_RANGE_FROM AND TABLE_RANGE_TO
            AND event_name IN ('PageView', 'PageClosed')
    )
)
, pv_session_log AS (
    SELECT *,
        FORMAT('%d-%d-%s', session_id, pv_index, pv_name) AS pv_sid, # 페이지뷰 구분을 위해 세션 ID와 PV 인덱스 조합 (PV 이름은 추가 정보)
        CASE WHEN pv_type = 'page'
            THEN LEAD(event_timestamp) OVER (PARTITION BY user_id, session_id, pv_type ORDER BY event_timestamp)
            ELSE NULL
        END AS end_timestamp, # page 타입은 다음 page 이동시간을 종료시간으로 사용
    FROM pv_log
)
, pv_session AS (
    SELECT *,
        NULLIF((end_timestamp - start_timestamp) / (1000*1000), 0) AS duration,
    FROM (
        SELECT MIN(date) AS date,
            MIN(event_timestamp) AS start_timestamp,
            MAX(IFNULL(end_timestamp, event_timestamp)) AS end_timestamp, # modal 타입은 팝업이 닫힐 때 기록된 PageClosed의 시간이 사용됨
            user_id,
            pv_sid,
            MIN(pv_type) AS pv_type,
            MIN(pv_name) AS pv_name,
        FROM pv_session_log
        GROUP BY user_id, pv_sid
    )
)
SELECT date,
    MIN(pv_type) AS pv_type,
    pv_name,
    COUNT(*) pv,
    COUNT(DISTINCT user_id) AS dau,
    AVG(duration) avg_duration,
    SUM(duration) sum_duration,
    COUNT(duration) count_duration,
FROM pv_session
GROUP BY date, pv_name
ORDER BY date, pv DESC

 

대략적인 쿼리 구조는 다음과 같다.

1. pv_log CTE 테이블
   1. 이벤트 로그 테이블에서 PageView, PageClosed 이벤트 조회
   2. 주요 컬럼 및 event_params 배열 컬럼에서 UNNEST 사용하여 하위 항목 조회
2. pv_session_log CTE 테이블
   1. PV 구분을 위한 ID 컬럼(pv_sid) 생성
   2. page 타입의 종료 시간 설정 (다음 페이지 진입시간으로)
3. pv_session CTE 테이블
   1. PV 단위로 시작시간, 종료시간 계산
4. SELECT 쿼리
   1. 페이지 정보, PV, 체류시간 집계

 

다음은 Looker Studio에서 PV 통계를 시각화한 모습이다.