Jan 16, 2022

Bigquery _table_suffix을 사용해 Google Analytics4 (GA4)로부터 전송된 ‘events_’ 멀티 테이블 쿼리하는 방법

Bigquery events_ Table — Bigquery 내부 GA4 데이터 세트의 events_ 테이블

특정 일자를 쿼리할 때는 아래와 같이, 분석하고자 하는 일자의 테이블을 조회하면 됩니다.

SELECT * FROM `warrensproject.analysis_000000000.events_20230101`

여러 테이블을 쿼리하게 될 경우에는 어떻게 하면 될까요? 이번 포스팅은 Bigquery의 _table_suffix 로 GA4의 여러 events_ 테이블을 쿼리하는 방법에 대해서 알아보겠습니다.

GA4 에서 빅쿼리(Bigquery)로 전송되는 데이터는 매일 별도의 테이블에 저장되는데, 여러 기간에 걸쳐 테이블을 조회하기 위해서는 _table_suffix 를 사용하면 됩니다.
위의 쿼리에서 FROM 절에 이벤트 테이블의 날짜를 지정해준 것을 볼 수 있습니다. 여러 테이블을 조인하게 될 경우 날짜별로 쌓인 테이블을 JOIN하면 될까요?

물론 LEFT, RIGHT, UNION 등의 JOIN 문을 써서 하나로 합칠 수 있겠지만 한 달, 분기, 반기, 년 단위 등의 큰 쿼리를 돌리게 될 경우 JOIN을 그만큼 여러번 해야하기 때문에 상당히 복잡할 것입니다.
위에서 언급한 _table_suffix를 사용하면 이를 해결할 수 있습니다.

‍

정적 기간 조회

대부분의 경우 여러 기간의 데이터를 조회합니다. 만약 2023년 1월 1일 부터 2023년 1월 31일까지 조회한다고 했을 때, 아래와 같이 FROM 에서 events_ 뒤에 * 을 붙이고 아래와 같이 사용합니다.

SELECT
    DISTINCT(user_id),
    event_date,
    event_name
FROM
    `warrensproject.analysis_000000000.events_*`
WHERE
    _table_suffix BETWEEN '20230101' AND '20230131'
    AND geo.country = 'South Korea'
ORDER BY
    event_date;

SELECT 에서 user_id, event_date, event_name 의 column을 지정해주고, 위에서 언급한 것 처럼 FROM 에서 events_ 뒤에 * 을 붙여줍니다.
여기에 WHERE 문에서 _table_suffix 와 BETWEEN을 사용해 2023-01-01 부터 2023-01-31까지 조회한 것을 볼 수 있습니다.

이 쿼리 예시와 같이 _table_suffix 를 사용해 여러 테이블을 쿼리할 수 있습니다.
해당 쿼리에 대해 더 궁금하신 분들은 아래 Bigquery 문서를 확인해보실 수 있습니다

Bigquery 문서 참고 : _table_suffix를 사용하여 테이블 범위 스캔

GA4 에서 빅쿼리로 전송하는 데이터 coulmn이 많기 때문에, 분석하고자 하는 column 을 추려서 SELECT 에 추가해주는 것이 좋습니다.

동적 기간 조회

날짜 함수 중 format_date로 지난 30일의 변화하는(=rolling) 기간을 선택합니다. (오늘 및 어제를 포함한 30일 입니다.)

SELECT
    *
FROM
    `warrensproject.analysis_000000000.events_*`
WHERE
    -- 함수 설명
    _table_suffix BETWEEN format_date('%Y%m%d',date_sub(current_date(), interval 30 day))
    -- date_sub를 통해 현재 날짜에서 하루 전 날짜를 제외 : interval 1 day
    AND format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

Bigquery 문서 참고 : format_date

정적 및 동적 기간 조합

GA4 에서 Bigquery 로 보내는 데이터를 조회할 때, 오늘을 제외하고 어제 날짜까지 조회하는 것을 선호하는 것이 일반적인 것 같습니다. (스트리밍으로 데이터를 보낼 수 있긴 하지만 실시간 데이터를 보기에는 개인적으로는 GA4에서 보는 것이 좀 더 효율적인 것 같습니다. 하지만, GA4에서 Bigquery로 스트리밍 내보내기를 하고 싶으신 분들은 아래 링크를 참고하시면 됩니다.)

https://support.google.com/analytics/answer/9358801

아래는 데이터 세트에 새 데이터가 추가되면 쿼리에 자동으로 포함되는 형태입니다. 2023년 1월 1일부터 어제까지의 데이터 범위를 지정해 줍니다.

SELECT
    *
FROM
    `warrensproject.analysis_000000000.events_*`
WHERE
    -- 함수 설명
    _table_suffix BETWEEN '20230101'
    AND format_date('%Y%m%d',date_sub(current_date(), interval 1 day))