このブログ記事は、Looker Studioから、BigQueryのネイティブコネクタを使ってGA4がエクスポートしたテーブルに対する可視化を対象としています。それって何？と思った方は、「Looker StudioのBigQueryコネクタでGA4を可視化するときの注意点」をまずはご参照ください。
BigQueryに格納されたデータにLooker Studioから接続していますが、特に、時系列ディメンションについて加工されているところがあります。そのため、かえって、BigQueryにSQLを掛けている人は戸惑う部分があるんじゃないかと思って、忘備録を兼ねて記事にしておきます。
Looker Studio上の「Event Date」は日付型
BigQuery上のevent_dateは文字列型ですが、Looker Studio上の「Event Date」は日付型です。オレンジがBigQuery、水色がLooker Studioです。
なので、なんの加工もしなくても、DATE_ADDやDATE_DIFFのような関数で使えます。親切ですね。
Looker Studio上の「Event Time」は日付と時刻型
BigQuery上のevent_timestampは、整数型(INTEGER）ですが、Looker Studioでは日付と時刻型です。オレンジがBigQuery、水色がLooker Studioです。
しかも、event_timestampはUNIX時のマイクロ秒で表現されていますが、Looker Studioでは、YYYY/MM/DD HH:MM:SSで表現されています。
Event Dateはタイムゾーン反映済、Event TimeはUTC
これは、Looker Studioに限ったことではなく、そもそもBigQuery上のデータもそうなっているのですが、EVENT DATE(BigQuery上はevent_date）は、タイムゾーン反映済です。一方、Event Time（BigQuery上は、event_timestamp）は協定世界時（UTC: Coordinated Universal Time）表現であり、日本時刻からマイナス9時間となっています。
以下は、Looker StudioであるユーザーのEvent DateとEvent Timeを比較したものですが、2/4の19:55のUTCは、JST（日本標準時）では9時間をプラスした、2/5 04:55なので、Event Dateは、2/5になっています。
Event Timeの、JST（日本標準時）への変換
Event TimeをJSTに変換するには、DATETIME_ADD()関数を利用して9時間足してあげれば良いです。
いい感じですね。
Looker StudioのFirst Touch Timeは日付と時刻型
Event Time同様、Looker StudioのFirst Touch Timeは、日付と時刻型になっています。元の、BigQuery上のuser_first_touch_timestampは、UNIX時のマイクロ秒でした。First Touch Timeも、Event Time同様UTCなので、JSTに変換するにはDATETIME_ADD関数で、9時間足してあげます。
こちらもいい感じにJSTに変換できています。
番外編 Looker Studioの「Days Since First Touch」
この段落は、もし、意味がわからなくても大丈夫ですので、わからなったらスルーしてください。気になる人だけどうぞ。
Looker Studioに、Days Since First Toouchという指標があります。
Firebase Google Groupにこのような投稿があり、2019年という大昔の投稿にもかかわらずレスが付いていない。閲覧数は261回。という状況の投稿です。比較的マニアックな、マイナーな疑問ではあるのでしょう。以下は、この方の疑問に対しての答えになっています。
この指標は、「初回訪問日時から、各イベントが発生した時間の差分」を「日」で表したものです。面白い使い方ができそうだな。と思うのですが、①イベントにひも付く、ということ、②デフォルトの計算方法がSUMであることから注意が必要です。
時系列で上から並べてある以下の表で、18行目までは順調に値が増えているのですが、19行目、突如値が減ります。これは、18行目のscrollイベントが、実は複数存在しているので、Days Since First TouchがSUMで合算されているのです。ですので、First Touch Timeから2日しか経過していないのに、6.23などという誤解を生む指標がでています。
その仕様がわかったとき、私は、このちょっと使えないな。（使うなら、BigQueryにカスタムクエリ書いた方がいいな）と思いました。が、この指標を頑張って使う人はそのあたり、気をつけると良いのかなと思いました。
この記事に関連した学習リソース
Looker Studioは、棒グラフや折れ線グラフを作っている分にはあまり面白くない（←褒めてます）のですが、計算フィールドを使って何ができるのか、、、と考え出すと楽しいですね。
Looker Studioの勉強でしたら、ぜひ、こちらをどうぞ。画像をクリックすると、Udemyにジャンプします。
