빅쿼리 BigQuery 사용 방법 (Firebase Analytics 연동)

BigQuery는 Google Cloud에서 제공하는 강력한 데이터 웨어하우스로, 대규모 데이터를 실시간으로 분석할 수 있는 플랫폼입니다.

 

 

 

Firebase Analytics와 연동하면 BigQuery에 데이터를 저장하고, SQL 쿼리를 통해 세밀하고 복잡한 데이터를 분석할 수 있습니다.

 

Firebase에서 제공하는 기본적인 분석 도구를 넘어서 더 강력한 분석을 원할 때 BigQuery가 유용합니다.

 

다음은 Firebase Analytics 데이터를 BigQuery에서 활용하는 방법을 단계별로 설명하겠습니다.

---

1. Firebase와 BigQuery 연동하기

1.1 Firebase 프로젝트에서 BigQuery 연결

1. Firebase 콘솔에 접속하여 프로젝트를 선택합니다.
2. 왼쪽 메뉴에서 Analytics > BigQuery로 이동합니다.
3. BigQuery 연동을 활성화하여 프로젝트와 Google Cloud BigQuery를 연결합니다.
4. BigQuery로 내보내고 싶은 Firebase 데이터(이벤트 데이터, 유저 프로퍼티 등)를 선택합니다.

1.2 BigQuery 데이터셋 생성

• Firebase와 BigQuery가 연결되면, BigQuery 콘솔에서 Firebase 데이터셋이 자동으로 생성됩니다.
• 이 데이터셋에는 실시간으로 Firebase Analytics 데이터(이벤트 데이터 등)가 저장됩니다.

---

2. BigQuery에서 Firebase Analytics 데이터 조회

BigQuery 콘솔에 접속하여 SQL 쿼리를 통해 Firebase Analytics 데이터를 조회할 수 있습니다.

2.1 기본 SQL 쿼리 작성 예시

BigQuery는 SQL 쿼리를 사용하여 데이터를 조회합니다. 다음은 Firebase Analytics 데이터를 조회하는 기본적인 쿼리 예시입니다.

• Firebase 데이터셋 구조
  • Firebase Analytics 데이터는 BigQuery에서 events_YYYYMMDD 테이블 형식으로 저장됩니다.
  • event_name: 이벤트 이름 (예: level_complete)
  • event_params: 이벤트의 파라미터 데이터 (예: 레벨 ID, 완료 시간 등)
  • user_properties: 사용자의 속성 데이터 (예: 사용자 지역, 나이 등)

예시 1: 레벨 완료 이벤트 조회

SELECT
  event_timestamp,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'level_id') AS level_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'completion_time_sec') AS completion_time_sec
FROM
  `your_project_id.your_dataset_id.events_*`
WHERE
  event_name = 'level_complete'
AND
  _TABLE_SUFFIX BETWEEN '20231001' AND '20231031' -- 2023년 10월 1일부터 31일까지의 데이터

• 이 쿼리는 level_complete 이벤트가 발생한 시간과 각 레벨의 ID, 레벨 완료 시간을 조회합니다.
• UNNEST: BigQuery에서 중첩된 데이터를 다룰 때 사용하는 함수로, 이벤트 파라미터를 추출할 때 사용됩니다.
• _TABLE_SUFFIX: 테이블 접미사를 사용하여 특정 날짜 범위의 데이터를 조회할 수 있습니다.

예시 2: 특정 레벨의 완료 시간 평균 계산

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'level_id') AS level_id,
  AVG((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'completion_time_sec')) AS avg_completion_time
FROM
  `your_project_id.your_dataset_id.events_*`
WHERE
  event_name = 'level_complete'
AND
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'level_id') = 'level_1'
GROUP BY
  level_id

• 이 쿼리는 특정 레벨(예: level_1)에서 플레이어가 레벨을 완료하는 데 걸린 평균 시간을 계산합니다.

---

3. 고급 분석 예시

3.1 사용자 세그먼트 분석

특정 사용자 그룹이 특정 이벤트에 어떻게 반응하는지 분석할 수 있습니다. 예를 들어, 특정 지역의 사용자들이 얼마나 자주 게임을 플레이하는지 파악할 수 있습니다.

SELECT
  user_properties.value.string_value AS user_country,
  COUNT(event_name) AS event_count
FROM
  `your_project_id.your_dataset_id.events_*`,
  UNNEST(user_properties) AS user_properties
WHERE
  user_properties.key = 'country'  -- 사용자 속성에서 'country' 속성을 선택
AND
  event_name = 'level_complete'    -- 'level_complete' 이벤트만 조회
GROUP BY
  user_country
ORDER BY
  event_count DESC

• 이 쿼리는 특정 국가의 사용자가 레벨 완료 이벤트를 얼마나 자주 발생시키는지 분석합니다.
• UNNEST로 중첩된 사용자 속성 데이터를 추출하여 분석합니다.

3.2 사용자 유지율 분석

유지율은 사용자가 앱을 처음 설치한 후, 며칠 동안 계속해서 앱을 사용하는지 분석하는 지표입니다.

WITH first_day_users AS (
  SELECT
    user_pseudo_id,
    MIN(event_timestamp) AS first_open_time
  FROM
    `your_project_id.your_dataset_id.events_*`
  WHERE
    event_name = 'first_open'
  GROUP BY
    user_pseudo_id
),
retention AS (
  SELECT
    first_day_users.user_pseudo_id,
    COUNTIF(event_name = 'session_start' AND DATE(TIMESTAMP_MICROS(event_timestamp)) = DATE(TIMESTAMP_MICROS(first_open_time)) + INTERVAL 1 DAY) AS day_1_retained,
    COUNTIF(event_name = 'session_start' AND DATE(TIMESTAMP_MICROS(event_timestamp)) = DATE(TIMESTAMP_MICROS(first_open_time)) + INTERVAL 7 DAY) AS day_7_retained
  FROM
    first_day_users
  JOIN
    `your_project_id.your_dataset_id.events_*` AS events
  ON
    first_day_users.user_pseudo_id = events.user_pseudo_id
  GROUP BY
    first_day_users.user_pseudo_id
)
SELECT
  AVG(day_1_retained) AS day_1_retention_rate,
  AVG(day_7_retained) AS day_7_retention_rate
FROM
  retention

• 이 쿼리는 앱 설치 후 1일 및 7일차 사용자 유지율을 계산하는 예시입니다.
• first_open 이벤트로 앱을 처음 연 사용자를 추적하고, session_start 이벤트로 이후 접속 여부를 확인합니다.

---

4. BigQuery 활용을 위한 팁

4.1 실시간 데이터 분석

Firebase와 BigQuery가 연동된 후에는 데이터가 실시간으로 BigQuery에 쌓이므로, 최신 데이터를 기반으로 빠르게 분석할 수 있습니다.

4.2 스케줄된 쿼리

BigQuery에서 스케줄된 쿼리 기능을 사용하면 정기적으로 쿼리를 실행하여 최신 리포트를 자동으로 생성할 수 있습니다. 이를 통해 반복적인 작업을 자동화할 수 있습니다.

4.3 데이터 시각화 도구 통합

BigQuery 데이터를 Google Data Studio나 Tableau 같은 시각화 도구와 연동하여 데이터 시각화 대시보드를 구축할 수 있습니다. 이를 통해 데이터의 흐름을 한눈에 파악할 수 있습니다.

---

결론

Firebase Analytics 데이터를 BigQuery와 연동하면 대용량 데이터를 처리하고 복잡한 분석을 할 수 있는 강력한 도구를 사용할 수 있습니다. SQL 쿼리를 통해 원하는 데이터를 세밀하게 추출하고, 사용자 행동을 심층적으로 분석할 수 있습니다. 또한 스케줄된 쿼리와 데이터 시각화 도구를 활용하면 정기적인 리포트 생성 및 대시보드 구축도 가능합니다.

 

BigQuery는 Firebase의 기본 대시보드로는 제공되지 않는 고급 분석과 커스텀 분석을 수행하는 데 필수적인 도구입니다.