2. BigQueryでGA4のevent_timestampを日時に変換する方法

この記事ではBigQueryでGoogleアナリティクス4のevent_timestampを日時に変換する方法をご紹介しています。

BigQueryやデータ分析に関するお仕事のご相談はTwitter、Linkedinからお願いいたします。

event_timestampの変換例

BigQueryにエクスポートされたGoogleアナリティクスのevent_timestampはINTEGERで記録されています。

「YYYY-MM-DD」などの表示形式に変換する際のクエリ例をご紹介します。

-- データを取得する対象期間を設定
CREATE TEMPORARY FUNCTION fromDate() AS (DATE('2018-10-03'));
CREATE TEMPORARY FUNCTION toDate()  AS (DATE('2018-10-03'));

SELECT
 PARSE_DATE("%Y%m%d", event_date) AS date
 , timestamp_micros(event_timestamp) AS time_micro
 , TIMESTAMP_TRUNC(timestamp_micros(event_timestamp), SECOND) AS time_second
 , TIMESTAMP_TRUNC(timestamp_micros(event_timestamp), SECOND, "Asia/Tokyo") AS time_second_jst1
 , FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_TRUNC(timestamp_micros(event_timestamp), SECOND), "Asia/Tokyo") AS time_second_jst2
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', fromDate()) AND FORMAT_DATE('%Y%m%d', toDate())
LIMIT 1

実行結果が以下になります。

おすすめの変換方法

複数の変換方法を記載していますが、JSTでの表示形式に変換するには以下の変換を行う必要があります。

FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_TRUNC(timestamp_micros(event_timestamp), SECOND), "Asia/Tokyo")

補足：表示形式

「SECOND」の部分は、表示形式にあわせてDAY・ISOWEEK・MONTH・YEARなどに書き換える必要があります。

補足：クエリで実行したテーブル

「FROM `analytics_XXXXXXXX.events_*`」のXXXXXXXの部分はご自身のGoogle AnalyticsのプロパティIDに置き換えてください。
今回はFirebaseデモプロジェクト（firebase-public-project、analytics_153293282.events_*）のデータを利用しています。

おまけ

TIMESTAMP型のUTC形式の時刻をJSTに変換する方法

例：2023-01-01 12:00:00 UTC　→　2023-01-01 21:00:00

FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', カラム名, "Asia/Tokyo")