BigQueryでGoogleアナリティクスのセッション開始日・時・分・秒を出す

BigQueryでセッションの開始日・時間・分・秒を出す

「BigQuery Export のスキーマ」には「visitStartTime」がありますので、これを使えばいいのですが、DATETIME型ではなくタイムゾーンもUTCになっています。このままでは、いつの何時何分からセッションが始まっているか分からないので、DATETIMEに変換し、JST時間で表示したいと思います。

　select
　fullvisitorid,
　date,
　datetime(timestamp_seconds(visitstarttime),'Asia/Tokyo') as visitstarttime,
　from `{bigqueryプロジェクトID}.{データセット}.ga_sessions_*`
　where _table_suffix between 'yyyymmdd' and 'yyyymmdd'
　order by 1,3
　;

 調べたところ、「visitStartTime」はおそらくUTCの秒数表記なので、TIMESTAMP_SECONDS関数でTIMESTAMP型に変換します。ただし、このままではタイムゾーンがUTCのままですので、DATETIME関数でタイムゾーンを日本時間にします。

すると、日本時間のセッション開始日時：時間：分：秒が取得できます。お試しください。