구글애널리틱스(GA4) 데이터를 세부적으로 분석하기 위해서는 빅쿼리의 사용이 일반화 되어 있습니다. 특히 추천 전자상거래 이벤트를 이용한 GA4 데이터의 경우 날짜 / 시간이 들어가 있기 때문에 이를 변환하고 분석하기 위해서는 함수의 사용이 필수적이게 됩니다. 빅쿼리의 날짜 / 시간 함수에 대해 알아보겠습니다.
날짜 / 시간의 유형은 DATE, TIME, TIMESTAMP, DATESTAMP 로 구분되어 있습니다.
| 데이터 유형 | 용도 |
| DATE | 연도, 월, 일을 나타내는 날짜를 저장하는 데 사용 |
| TIME | 시간 정보를 저장하는 데 사용 |
| TIMESTAMP | 날짜와 시간 정보를 모두 저장하는 데 사용 (타임존 적용) |
| DATESTAMP | 날짜와 시간 정보를 모두 저장하는 데 사용 |
빅쿼리는 위에 4가지 데이터 유형을 가지고 날짜 / 시간을 저장합니다. 일반적으로 시간, 날짜를 동시에 나타내는 TIMESTAMP, DATESTAMP가 많이 사용되기 때문에 2개를 중점으로 설명하겠습니다.
현재 시간 / 날짜를 나타내는 함수
- CURRENT_TIMESTAMP
- CURRENT_DATETAMP - 타임존 지원
각각 현재 시간 / 날짜를 출력해 주는 함수 입니다. DATETIME은 타임존을 제외한 날짜, 시간이 포함되고 TIMESTAMP는 타임존이 적용된 날짜, 시간이 포함된 것을 볼 수 있습니다.
여기서 UTC는 타임존을 나타냅니다.
타임존(time zone)이란, 영국의 그리니치 천문대를 기준으로 나누어진 국가별 시간대를 말합니다. 표준 시간은 UTC로 나타내며 한국의 표준 시간은 KST(UTC+9)입니다.
CURRENT_DATESTAMP는 CURRENT_TIMESTAMP와 다르게 추가적으로 파라미터를 줄 수 있는데, 다음과 같이 국가에 알맞는 타임존을 주면 그에 맞게 값이 변환됩니다.
'Asia/Seoul' 을 파라미터로 주면서 UTC에 9시간이 더 해진 시간을 볼 수 있습니다.
데이터 추출 함수
- EXTRACT - 타임존 지원
지금부터는 TIMESTAMP를 중심으로 설명하겠습니다.
EXTRACT 함수는 시간, 날짜 데이터 유형을 초, 분, 시 등 세부단위로 나눌 수 있습니다.
EXTRACT 함수를 사용해 월(MONTH)을 추출한 결과입니다. "2023-08-04"에서 "8"이라는 결과를 얻었습니다. 표처럼 월 뿐만 아니라 다양한 단위를 얻을 수 있습니다.
| MICROSECOND | MILLISECOND | SECOND | MINUTE | HOUR | DAYOFWEEK |
| 마이크로 초 | 밀리 초 | 초 | 분 | 시 | 요일에 대한 정수 |
| DAY | DAYOFYEAR | WEEK | MONTH | QUARTER | YEAR |
| 일 | 연중 일 | 연중 주 | 월 | 분기 | 년 |
EXTRACT 함수는 타임존도 사용 가능한 함수입니다. 다음은 한국 시간을 기준으로 계산하는 예시입니다.
UTC로 저장 된 "2023-08-04 21시 30분"를 한국 표준 시간으로 적용하였더니 9시간이 추가되어 다음 날인 5일에 대한 데이터를 얻을 수 있습니다.
데이터 포맷 변환 함수
- FORMAT_TIMESTAMP - 타임존 지원
- PARSE_TIMESTAMP - 타임존 지원
데이터 포맷 변환 함수 중 FORMAT_TIMESTAMP는 TIMESTAMP 데이터 형식을 다시 구성해서 특정 형식을 가진 TIMESTAMP로 제공해주는 함수이며 PARSE_TIMESTAMP는 FORMAT_TIMESTAMP로 구성된 특정 형식을 가진 데이터를 다시 원상복구 시켜줍니다.
여기서 PARSE_TIMESTAMP는 값이 UTC로 표현되지만 실제로는 타임존이 적용된 값이 나타나는 주의점이 있습니다.
수식 함수
- TIMESTAMP_ADD
- TIMESTAMP_SUB
- TIMESTAMP_DIFF
TIMESTAMP_ADD, TIMESTAMP_SUB 함수는 INTERVAL 뒤에 정수와 시간을 넣어주면 됩니다. 시간/날짜에는 마이크로 초, 밀리 초, 초, 분, 시간, 일이 들어갈 수 있습니다. 예시는 30분(ITERVAL 30 MINUTE)을 더하고 뺀 값입니다.
TIMESTAMP_DIFF 함수는 두 TIMESTAMP간의 차이를 얻을 수 있는 함수입니다.
예시는 두 TIMESTAMP 사이의 일자 간격을 나타낸 쿼리입니다. 총 "10월 23일"부터 "08월 04일"까지 총 80일이 차이나는걸 알 수 있습니다.
유닉스 시간 변환
- TIMESTAMP_MICROS
- TIMESTAMP_MILLIS
- TIMESTAMP_SECONDS
유닉스 시간대(UNIX_TIMESTAMP)는 1970년 01월 01일 0시 이후 부터 초 단위로 저장되는 시간입니다.
구글애널리틱스(GA4)에 저장된 빅쿼리 데이터를 보면 event_timestamp 칼럼에 "1676453528525834" 값처럼 일상에서 사용하는 시간이 아닌 값을 볼 수 있습니다. 이는 유닉스 시간대로 저장된 데이터이기 때문입니다. 따라서 예시처럼 TIMESTAMP_MICROS를 사용해준다면 일상에서 볼 수 있는 시간대로 값을 받을 수 있게 됩니다.
단, 타임존은 UTC로 출력되기 때문에 DATETIME 함수를 이용해 타임존을 설정해 줄 필요가 있습니다.
https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_diff
Timestamp functions | BigQuery | Google Cloud
GoogleSQL for BigQuery supports the following timestamp functions. IMPORTANT: Before working with these functions, you need to understand the difference between the formats in which timestamps are stored and displayed, and how time zones are used for the c
cloud.google.com
파인데이터랩
파인데이터랩은 커머스 데이터 구축을 통한 컨설팅, 구축, 솔루션제작 업체입니다.
www.finedatalab.com
'데이터베이스(DB)' 카테고리의 다른 글
| 구글애널리틱스4(GA4) 빅쿼리 연동 방법 (0) | 2023.07.21 |
|---|---|
| 구글 빅쿼리(Big Query)란? (3) | 2023.02.02 |
