빅쿼리 활용법 (2) GA4 타임스탬프 한국 시간으로 변환하기

💡 빅쿼리(BigQuery)에서 전환 이벤트를 조회해 봅니다. 타임스탬프도 한국 시간으로 변환해서 보면 더 편하겠죠?

1. 빅쿼리에서 전환 이벤트 조회하기
- 1) 빅쿼리에서 쿼리 창을 열어서 아래 쿼리를 실행해 보겠습니다.
- 2) 빅쿼리 결과 확인하기 (1차 시도 실패)
2. 빅쿼리 타임스탬프 한국 시간으로 변환하기
마무리

1. 빅쿼리에서 전환 이벤트 조회하기

💡 일단 빅쿼리에서 전환 이벤트부터 조회해볼까요?

1) 빅쿼리에서 쿼리 창을 열어서 아래 쿼리를 실행해 보겠습니다.

SELECT 
  event_timestamp,
  event_name,
  traffic_source.source, 
  traffic_source.medium,
  traffic_source.name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content') AS content,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'term') AS term,
  user_pseudo_id,
FROM 
  `project.dataset.table`
WHERE 
  event_name = '전환체크'
ORDER BY 
  event_timestamp DESC
LIMIT 1000;

빅쿼리 활용법 (1) 초보 마케터를 위한 GA4와 빅쿼리

활용법 (1)에서 설명드린 바대로 project.dataset.table 에는 실제 빅쿼리 테이블 경로를 써주세요.

WHERE 부분은 필터와 같은 역할을 합니다. event_name 에는 여러분이 측정할 이벤트 이름을 써주세요.

위와 같이 쿼리를 실행하면

시간

이벤트 이름

유입 소스

유입 매체

유입 캠페인

유입 콘텐츠

유입 키워드

유저 수도 ID

순으로 컬럼이 조회되고, 최신 데이터가 가장 위에 정렬될거에요. (시간 역순) 한 번 실행해 볼까요?

2) 빅쿼리 결과 확인하기 (1차 시도 실패)

결과 중 일부 캡처 화면입니다. 뭔가 원하는 형태로 나오지 않았네요. 🤔

시간이 알아볼 수 없는 형태로 나왔고, 콘텐츠와 키워드도 null로 나옵니다.

구글 오가닉과 cpc 키워드는 개인 정보 보호 이슈 때문에 ga4에서는 (not provided) 라고 표시되며 조회할 수 없습니다. 그러나 네이버 오가닉과 cpc 키워드도 볼 수 없는 상황은 이상하죠? 왜 그럴까요?

시간 : ga4에서 빅쿼리로 보내지는 타임스탬프 데이터는 마이크로초 단위입니다. 이 데이터를 빅쿼리에서 사용하는 시간 데이터 타입으로 변환해서 한국 시간으로 변환해야 합니다.
콘텐츠, 키워드 : 아무래도 유입 콘텐츠, 키워드가 전환 이벤트까지 따라가지 못하고 유실된 상황으로 보입니다. 유저 수도 ID는 남아 있으니, 이 ID가 유입되었을 때 콘텐츠와 키워드를 함께 불러와야겠습니다.

유입되었을 때의 콘텐츠와 키워드를 불러오기 위해서는 전환 이벤트와 동일한 user_pseudo_id를 가진 session_start 이벤트를 조인해야 합니다. 이 내용은 다음 글에서 추가로 알아보기로 하고, 이번 글에서는 먼저 타임스탬프를 변환하는 방법부터 알아보겠습니다.

user pseudo id란?

2. 빅쿼리 타임스탬프 한국 시간으로 변환하기

💡 마이크로초 단위의 타임스탬프를 한국 시간으로 변환하는 쿼리를 알아봅니다.

1) 다음 쿼리를 실행해 봅니다.

SELECT 
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(event_timestamp) + INTERVAL 9 HOUR) AS korean_event_timestamp,
  event_name,
  traffic_source.source, 
  traffic_source.medium,
  traffic_source.name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content') AS content,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'term') AS term,
  user_pseudo_id
FROM 
  `project.dataset.table`
WHERE 
  event_name = '전환체크'
ORDER BY 
  korean_event_timestamp DESC
LIMIT 1000;

2) 결과 확인하기 (성공!!) 😆

원하는 대로 한국 시간으로 잘 변환되었습니다. 성공!!

3) 쿼리 설명 : 한국 시간으로 빅쿼리 타임스탬프 변환하기

FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(event_timestamp) + INTERVAL 9 HOUR) AS korean_event_timestamp,

TIMESTAMP_MICROS(event_timestamp):
- event_timestamp는 마이크로초 단위의 타임스탬프 값입니다.
- TIMESTAMP_MICROS 함수는 이 값을 BigQuery의 TIMESTAMP 데이터 타입으로 변환합니다. 즉, event_timestamp를 마이크로초 단위의 숫자에서 TIMESTAMP 객체로 변환합니다.
TIMESTAMP_MICROS(event_timestamp) + INTERVAL 9 HOUR:
- 변환된 TIMESTAMP 값에 9시간을 더합니다. 이는 UTC 시간을 한국 표준시(KST)로 변환하는 과정입니다.
- 한국은 UTC보다 9시간 앞서 있기 때문에 UTC 시간에 9시간을 더해 한국 시간을 구합니다.
FORMAT_TIMESTAMP(‘%Y-%m-%d %H:%M:%S’, …):
- 이 함수는 TIMESTAMP 값을 지정된 형식의 문자열로 변환합니다.
- '%Y-%m-%d %H:%M:%S' 형식은 “년-월-일 시:분:초”의 형태로 변환하라는 의미입니다.
- 예를 들어, 2024-06-20 13:45:30와 같은 형식으로 출력됩니다.
AS korean_event_timestamp:
- 이 구문은 결과 열의 이름을 korean_event_timestamp로 지정합니다.
- 즉, 변환된 한국 시간 타임스탬프가 korean_event_timestamp라는 이름의 열로 쿼리 결과에 포함됩니다.

전체 과정

event_timestamp라는 마이크로초 단위의 타임스탬프 값을 TIMESTAMP 객체로 변환합니다.
이 TIMESTAMP 객체에 9시간을 더해 UTC 시간에서 KST 시간으로 변환합니다.
변환된 TIMESTAMP 값을 ‘년-월-일 시:분:초’ 형식의 문자열로 포맷합니다.
최종 결과를 korean_event_timestamp라는 열로 반환합니다.

마무리

이번 글에서는 빅쿼리에서 조회되는 타임스탬프를 한국 시간으로 변환하는 방법을 알아보았습니다.

다음 글에서는 전환 이벤트와 session_start 이벤트를 user_pseudo_id 기준으로 조인하여 추가 데이터를 확인하는 방법을 알아보겠습니다.

감사합니다.