BigQuery 기초부터 GA4 신규/재사용자 쿼리 작성까지

Google BigQuery는 대규모 데이터를 빠르고 효율적으로 처리하기 위한 클라우드 기반의 데이터 웨어하우스입니다. BigQuery는 Google Cloud Platform(GCP)의 일환으로 제공되며 SQL 같은 친숙한 언어를 사용해 데이터를 분석할 수 있습니다. 이 글에서는 BigQuery의 기초를 설명하고, GA4 데이터를 활용해 신규 사용자와 재사용자를 구분하는 쿼리 작성법까지 안내합니다.

1. BigQuery란?

Table of Contents

주요 특징

서버리스: 서버 관리 필요 없이 즉시 사용 가능.
확장성: 수 페타바이트 이상의 데이터를 처리할 수 있음.
SQL 지원: 표준 SQL을 사용해 쿼리 작성.
통합성: Google Analytics 4(GA4), Google Ads 등 다양한 데이터 소스와 연결.
요금제: 사용량 기반 요금제로, 처리량에 따라 비용이 청구됨.

2. BigQuery 기초

2.1 BigQuery 구성 요소

프로젝트: 데이터가 저장되는 최상위 컨테이너.
데이터셋: 데이터를 논리적으로 그룹화한 컨테이너.
테이블: 실제 데이터가 저장되는 단위.
스키마: 테이블 내 필드(열)의 구조와 데이터 타입 정의.

2.2 기본 용어

SQL(Query): 데이터를 조회, 수정, 삭제할 때 사용하는 언어.
조인(Join): 여러 테이블의 데이터를 결합.
파티션(Partition): 데이터를 날짜별로 나누어 효율적으로 관리.
클러스터링(Clustering): 테이블 데이터를 특정 필드 기준으로 정렬하여 쿼리 성능을 최적화.

3. BigQuery 사용법

3.1 BigQuery Console

BigQuery는 GCP Console을 통해 사용합니다.

GCP Console에서 BigQuery를 클릭.
데이터셋과 테이블을 선택 후 쿼리 작성.
SQL을 입력하고 “Run”을 클릭해 실행.

3.2 BigQuery SQL 기본

데이터 조회하기

SELECT *
FROM `프로젝트명.데이터셋명.테이블명`
LIMIT 100;

조건으로 필터링

SELECT user_id, event_name, event_timestamp
FROM `프로젝트명.데이터셋명.테이블명`
WHERE event_name = 'purchase';

그룹화 및 집계

SELECT event_name, COUNT(*) AS event_count
FROM `프로젝트명.데이터셋명.테이블명`
GROUP BY event_name;

4. GA4 데이터를 이용한 신규/재사용자 분석

GA4 데이터를 BigQuery로 내보내면, 이벤트 기반의 테이블 구조로 데이터를 다룰 수 있습니다. 여기서는 신규 사용자와 재사용자를 구분하는 쿼리를 작성해보겠습니다.

4.1 GA4 테이블 구조

GA4 데이터는 이벤트 기반으로 저장됩니다.

주요 필드:
- user_pseudo_id: 사용자 고유 식별자.
- event_name: 이벤트 이름.
- event_params: 이벤트와 관련된 추가 파라미터.
- user_properties: 사용자 속성.
- event_date: 이벤트 발생 날짜.

4.2 신규 사용자 쿼리

“신규 사용자”는 GA4에서 첫 방문 이벤트(first_open 또는 first_visit)을 통해 식별됩니다.

SELECT
  user_pseudo_id,
  MIN(event_timestamp) AS first_event_time
FROM
  `프로젝트명.데이터셋명.events_*`
WHERE
  event_name = 'first_open' -- 또는 'first_visit'
GROUP BY
  user_pseudo_id;

4.3 재사용자 쿼리

재사용자는 첫 방문 이후 특정 기간 동안 다시 방문한 사용자입니다.

WITH FirstVisit AS (
  SELECT
    user_pseudo_id,
    MIN(event_timestamp) AS first_event_time
  FROM
    `프로젝트명.데이터셋명.events_*`
  WHERE
    event_name = 'first_open'
  GROUP BY
    user_pseudo_id
)
SELECT
  e.user_pseudo_id,
  COUNT(DISTINCT e.event_date) AS active_days
FROM
  `프로젝트명.데이터셋명.events_*` e
JOIN
  FirstVisit f
ON
  e.user_pseudo_id = f.user_pseudo_id
WHERE
  e.event_timestamp > f.first_event_time
GROUP BY
  e.user_pseudo_id;

4.4 신규 vs 재사용자 비율

신규 사용자와 재사용자를 한 쿼리로 계산할 수도 있습니다.

WITH FirstVisit AS (
  SELECT
    user_pseudo_id,
    MIN(event_timestamp) AS first_event_time
  FROM
    `프로젝트명.데이터셋명.events_*`
  WHERE
    event_name = 'first_open'
  GROUP BY
    user_pseudo_id
),
UserStatus AS (
  SELECT
    e.user_pseudo_id,
    CASE
      WHEN e.event_timestamp = f.first_event_time THEN 'new_user'
      ELSE 'returning_user'
    END AS user_type
  FROM
    `프로젝트명.데이터셋명.events_*` e
  LEFT JOIN
    FirstVisit f
  ON
    e.user_pseudo_id = f.user_pseudo_id
)
SELECT
  user_type,
  COUNT(*) AS user_count
FROM
  UserStatus
GROUP BY
  user_type;

마무리

이 글에서는 BigQuery의 기초부터 시작해 GA4 데이터를 활용한 신규 사용자와 재사용자 분석까지 다뤘습니다. BigQuery는 대용량 데이터를 처리할 수 있는 강력한 도구이며, GA4 데이터와 결합하면 사용자 행동 분석에 큰 강점을 발휘합니다. 다음 단계에서는 이러한 데이터를 시각화하거나, 머신러닝에 활용해보는 것도 추천드립니다!