10日ほどまえ、「Looker StudioのGA4データで「初回訪問からX日以内」のユーザー行動を可視化する」という記事を書きました。
その中では、Looker Studioの計算フィールドとフィルタで、「初回訪問からX日以内」かつ「初回訪問からY日間のデータが含まれる」ユーザーだけにデータを絞って、「ユーザーの最初のメディア」別に「ユーザーあたりのセッション」をApple to Appleで比較するワザを紹介しました。
でも、私、そのLooker Studioでの可視化結果が正しいことを、どのように担保しているのでしょうか？
「論理上正しいはずだから、これでいいや」とは思ってないんですね。人様に読んでいただくブログですから、間違いがあってはその人達をミスリードしますし、私への信頼性も大きく揺らいでしまうでしょう。なので、事前に検証しています。
どうやって？それがSQLです。当該の記事は「SQLを書かなくてもここまでできるよ」という内容ではあるのですが、裏側ではSQLをつかって検証している。そういう訳です。そこでこの記事では、メイキング・オブ的に、そのブログ記事をSQLを使ってどのように検証したのか？を紹介します。
前回のブログでやったことのおさらい
当該のブログ記事はできればちゃんと読んでもらいたいのですが、やったことは、以下の6人のユーザーについて、網掛け部分に絞り込むことです。便宜上、1週目、2週目、、、などとしていますが、Looker Studioの計算フィールドでは28日としています。
Looker Studioで利用したフィルタは実質的に以下の通りです。
検証のために記述したSQL文
検証のために記述したSQL文と、その結果は以下です。
上記は、直接BigQueryに問い合わせしていますし、SQL文でやっていることは明確です。なので、緑枠の結果と、Looker Studioの可視化結果が合致していれば、Looker Studioのフィルタが正しく掛かっているという検証となります。
Looker Studioでの可視化結果
Looker Studioでの「表」を用いた可視化結果は、以下となりました。上記の緑枠の数値と完全に一致しています。偶然ではこんな一致はまず置きないので、Looker Studioでのフィルタは想定通りに掛かっていると考えて良い。という結論としました。
検証用SQL文の解説
検証用SQL文の重要なところを解説すると、以下となります。上下にスクロールするのを避けるために、SQL文のところだけを再掲します。
SQL文の13行目
13行目は、Looker Studioでの以下のフィルタを模しています。
DATE_DIFF(Event Time, First Time Touch,day) <=28
BigQuery上のevent_timestampも、user_first_touch_timestampも整数型ですので、時間関数を書かなくても、そのまま引き算ができます。気をつけることは、両方のフィールドとも、UNIX時のマイクロ秒で表現されていることです。そのため、28日 x 24時間 x 60分 x 60秒 x 1,000,000 = 241920000000 を、「初回訪問時間を基準としたイベント発生時刻のしきい値」としています。
SQL文の15行目
15行目は、Looker Studioの以下のフィルタを模しています。
First Time Touch < 2023/2/1
BigQuery上の、UNIX時のマイクロ秒で記述されたFirst Time Touchと、2023年2月1日を比較し、First Time Touchがそれよりも小さいことを条件としています。SQL文中の 1675209600000000は、UNIX時の2023年2月1日をマイクロ秒で表示したものです。
グレゴリー歴（一般的に使われる日付形式）をUNIX時にするには、こちらのサイトを利用させてもらいました。以下のような計算結果を得ることができます。
SQL文の5行目
SQL文の5行目については、「あれ？何やってるんだろう？」と思う方が多いかと思います。ちょと難易度が高いので、普通の人は以下はスルーして、本ページ下部の「宣伝」のとこまで高速スクロールして頂いて大丈夫です。
気になる方は、「Looker Studioが内部的に参照するBigQueryのテーブルは2種類？」を参照していただければと思います。その記事では、私の推測として、Looker Studioは内部的に2種類のデータを利用してグラフを描画しているのではないか、という論を述べています。
そして、5行目は、2種類のテーブルを対象とできるようにしています。行頭のコメントがあれば、BigQueryの元テーブルに対して、コメントを削除すれば、クロスジョインされたテーブルを参照して検証します。念のため、両方で検証しているってことですね。（ちなみに、コメントを外しても、外さなくても結果は同じになります。）
宣伝
Looker Studioのグラフを作成し、部署の人やお客さんと共有する場合、その数値の正確性、妥当性がとても気になりますね。SQLを書ければ、「絶対ではないにしても」ある程度自信をもって、レポートを共有することができますし、「この数字、どうやってできているの？」と聞かれたときに、明確に説明できるようになります。SQLにはロジックがあるので、SQLを掛けた結果と合致するということはロジックを説明できるということなんです。
「おお、SQLを書けると、そんなことができるのか！勉強するしかないじゃん！？」と思った方は、こちらをどうぞ。画像をクリックすると、Udemyにジャンプします。値段はよく変動するので、今は1800円じゃないかもしれません^^;
