데이터 분석을 하다 보면 GA4의 BigQuery를 활용해야 하는 경우가 많습니다. BigQuery는 SQL 기반의 강력한 데이터 웨어하우스이지만, 기본적인 SQL 문법을 넘어서 BigQuery만의 특화된 기능을 익히는 것이 중요합니다. 이번 글에서는 중급자들이 반드시 알아야 할 BigQuery의 주요 문법과 활용법을 자세히 설명하겠습니다.
1. WITH 절을 활용한 CTE (Common Table Expressions)
CTE(Common Table Expressions)는 복잡한 쿼리를 가독성 높게 작성할 수 있도록 도와줍니다. 여러 개의 서브쿼리를 사용할 때 유용하며, 반복되는 코드 작성을 줄일 수 있습니다.
WITH session_data AS (
SELECT
user_pseudo_id,
event_bundle_sequence_id,
event_name,
event_timestamp
FROM `your_project.your_dataset.events`
WHERE event_name = 'session_start'
)
SELECT * FROM session_data;위 예제에서는 WITH 절을 사용하여 세션 데이터를 추출하는 쿼리를 작성했습니다. 이후 메인 쿼리에서 session_data를 테이블처럼 사용하여 코드를 보다 깔끔하게 정리할 수 있습니다.
2. ARRAY와 STRUCT 활용하기
BigQuery는 JSON 구조의 데이터를 다룰 수 있도록 ARRAY와 STRUCT 타입을 지원합니다. 특히 GA4의 이벤트 데이터는 event_params와 같은 ARRAY<STRUCT> 형태로 저장되므로, 이를 처리하는 방법을 알아야 합니다.
ARRAY 데이터 처리
SELECT
event_name,
ARRAY_LENGTH(event_params) AS param_count
FROM `your_project.your_dataset.events`
WHERE event_name = 'purchase';위 쿼리는 특정 이벤트(purchase)에 포함된 파라미터 개수를 계산합니다.
STRUCT 데이터 처리
event_params 필드에서 특정 키를 가진 값을 추출하려면 UNNEST()를 활용해야 합니다.
SELECT
event_name,
ep.key AS param_key,
ep.value.string_value AS param_value
FROM `your_project.your_dataset.events`, UNNEST(event_params) AS ep
WHERE event_name = 'purchase';위 쿼리는 event_params 배열을 개별 행으로 변환한 후, 각 키-값을 가져오는 방식입니다.
3. PARTITION과 CLUSTER를 활용한 최적화
BigQuery는 테이블을 파티션 및 클러스터링하여 성능을 최적화할 수 있습니다.
테이블 파티셔닝 (Partitioning)
테이블을 날짜 기준으로 파티션하면 특정 날짜의 데이터만 조회할 때 속도를 크게 향상시킬 수 있습니다.
SELECT *
FROM `your_project.your_dataset.events`
WHERE DATE(_PARTITIONTIME) = '2024-02-20';GA4의 데이터는 event_date가 아닌 _PARTITIONTIME을 기준으로 파티션이 나누어지므로, 해당 컬럼을 활용하면 비용 절감과 성능 향상을 동시에 얻을 수 있습니다.
테이블 클러스터링 (Clustering)
클러스터링을 활용하면 특정 컬럼을 기준으로 데이터를 정렬하여 검색 성능을 높일 수 있습니다.
CREATE TABLE your_project.your_dataset.clustered_events
PARTITION BY DATE(event_timestamp)
CLUSTER BY event_name, user_pseudo_id AS
SELECT * FROM `your_project.your_dataset.events`;위와 같이 event_name과 user_pseudo_id를 클러스터링하면, 해당 컬럼을 활용한 조회 시 쿼리 성능이 향상됩니다.
4. WINDOW FUNCTION을 활용한 데이터 분석
BigQuery의 윈도우 함수(Window Function)는 특정 그룹 내에서 행별로 계산할 때 유용합니다.
사용자별 이벤트 순서 부여하기
SELECT
user_pseudo_id,
event_name,
event_timestamp,
ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS event_order
FROM `your_project.your_dataset.events`;각 사용자의 이벤트 발생 순서를 계산하여 분석할 수 있습니다.
세션 내 이벤트 수 계산하기
SELECT
user_pseudo_id,
event_bundle_sequence_id,
COUNT(*) OVER(PARTITION BY user_pseudo_id, event_bundle_sequence_id) AS events_in_session
FROM `your_project.your_dataset.events`;각 사용자의 세션별 이벤트 개수를 확인할 수 있습니다.
5. MERGE를 활용한 데이터 업데이트
BigQuery에서는 기존 데이터를 업데이트할 때 MERGE 문을 사용할 수 있습니다.
MERGE INTO your_project.your_dataset.user_data AS target
USING your_project.your_dataset.new_user_data AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN
UPDATE SET target.last_login = source.last_login
WHEN NOT MATCHED THEN
INSERT (user_id, last_login) VALUES (source.user_id, source.last_login);위 쿼리는 기존 사용자의 로그인 시간을 업데이트하거나, 새로운 사용자를 삽입하는 로직을 포함합니다.
결론
이 글에서는 BigQuery의 중급 문법과 활용법을 다뤘습니다. WITH 절을 이용한 CTE, ARRAY 및 STRUCT 처리, 파티셔닝과 클러스터링을 통한 성능 최적화, 윈도우 함수 활용, 그리고 MERGE 문을 이용한 데이터 업데이트까지 실전에서 자주 쓰이는 문법을 중심으로 정리했습니다.
GA4의 BigQuery 데이터를 보다 효율적으로 분석하려면 위 내용을 직접 실습하면서 익히는 것이 중요합니다. 앞으로도 BigQuery의 고급 기능과 실전 분석 예제에 대해 다룰 예정이니, 지속적으로 학습하여 데이터 분석 능력을 향상시켜 보세요!
답글 남기기