Looker Studioが内部的に参照するBigQueryのテーブルは2種類？

GA4の探索レポートの検証もそうなのですが、Looker Studioの可視化がどんなフィールドをどう計算して行われているのかをBigQueryで検証する場合、Looker Studioが参照しているテーブルと同じものをBigQueryからも参照しなくてはいけません。一方、Looker Studioが内部的にどんなテーブルを参照しているのかは明らかになっていません。

それを推測してみたら、Looker Studioは、以下の場合に分けて、2種類のテーブルを参照しているのではないか？という結論となりました。ちょっとマニアックですし、あくまでも私の推測なので、普通の人はスルーで良いのだろうと思いますが、きっと、100人に一人くらいの、Looker Studioで計算フィールドを書いて、それをグラフで使う人は「参考になった」と言ってくれるんじゃないかと思って書いてます。

その場合分けとは・・・

1. Looker Studioで描画したいグラフが、元のテーブルのevent_paramsが保持するフィールドを使っていない場合

→ events_YYYYMMDDを直接参照する

2. Looker Studioで描画したいグラフが、元のテーブルのevent_paramsが保持するフィールドを使ってる場合

→　元のテーブル（events_YYYYMMDD）と、event_paramsをunnestしてクロスジョインしたテーブルを参照する

それでは、その結論に至った経緯を順に見ていきましょう。まずは、events_YYYYMMDDテーブルについて知るところから始めましょう。

events_YYYYMMDDテーブルとは

GA4がBigQueryにエクスポートしたデータは、events_YYYYMMDDというテーブルにエクスポートされます。以下は公式ヘルプの記述です。

Looker Studiioから、BigQuery上のGA4へのデータ接続

上記の、BigQuery上のGA4のデータに、Looker Studioからは、以下の画面経由で接続します。一見、events_YYYYMMDDに直接接続しているように思え（見え）ますね。

Looker Studioが参照しているテーブルの推測

しかし、どうも、Looker Studioが、events_YYYYMMDDを直接参照しているようには思えない「状況証拠」があるんですね。以下の2つの挙動です。

Looker StudioがBigQueryのテーブルを直接参照しているようには見えない挙動1

挙動1は、ディメンションに、Event Params Name、Event Params Value、Event Params (String)などが存在することです。元データ（events_YYYYMMDD）に含まれる、「行に入れ子になっているテーブル状のデータ」であるevent_paramsが、すぐに使えるようになっている。ってことですね。（event_paramsについては、このあと詳述します。）

これは、元の events_YYYYMMDDテーブルに直接接続したのではありえない挙動です。

Looker StudioがBigQueryのテーブルを直接参照しているようには見えない挙動2

もうひとつの挙動は、ページタイトルごとのイベント数を可視化してみると、ページタイトルにnullが大量発生し、イベント数の合計がBigQueryの元のテーブルの行数と大きく異ることです。以下はどちらも2023年2月1日のデータですが、BigQueryの_events_20230201テーブル（オレンジ枠）では、総レコード数が、301しかありません。

一方、Looker Stuido（水色枠）では「総計」こそ301と、BigQueryと一致していますが、Page_TitleがnullのEvent Countに、謎の「3,227」があります。この3,227は総計にはカウントされていないようですが、仮に、3,227と301を足すと、3,528となります。

events_YYYYMMDDと、unnest(event_params)をクロスジョインしてみた

event_paramsが直接利用できるようになっていて、（内部的に参照しているはずの）テーブルのレコード数が大幅に増加している。。。こういう挙動をするのは・・・ピン！と来ました。

「クロスジョイン！！」

で、検証してました。

ビンゴ！

3,528行となり、ページタイトルが、nullの場合も含めた、Event Countと一致しました。

あぁ、スッキリした。という訳で、Looker Studioが内部的に参照するのは、元のテーブルである、events_YYYYMMDDと、event_paramsをunnestして、クロスジョインしたテーブルだろう。と結論しました。

一方、こんな結果も

先程の例ではページタイトル別にEvent Countを確認したのですが、以下では、Event NameごとにEvent Countを集計しています。すると、nullもでず、Event Countは、BigQueryのevents_YYYYMMDDの総レコード数と完全に一致します。ということは、Event Nameをディメンションとして利用した場合と、Page_Titleをディメンションとして利用した場合とでは、別のテーブルを参照しているのではないか？という推測が生まれます。

いくつかのディメンションを適用し見つけた法則

いくつかのディメンションを適用してEvent Countを可視化したしてみたところ、以下の法則が見つかりました。

ディメンションに「event_params内のフィールド」を使うと総レコード数は3,525行になる
ディメンションに「event_params内のフィールド」を使わないと、総レコード数は301となる

つまり、1番の場合は、クロスジョインした表を、2番の場合は元の events_YYYYMMDDテーブルを参照していえると思われるのです。では「event_params内のフィールド」とは何でしょう？それを知るには、events_YYYYMMDDテーブルの構造を知る必要があります。

events_YYYYMMDDテーブルの構造

以下が、events_YYYYMMDDの1ヒットの例です。

上記のヒットの構造を本記事に関連する範囲でごく大雑把に図式化すると、以下の通りとなります。青が event_paramsの外側のフィールド、オレンジが、一ヒットの中に入れ子になったevent_paramsという「詳細表」です。

Looker Studioがグラフを描画するときのフィールドの使い分け

Looker Studioがグラフを描く場合、利用するフィールドを、上記の青と、オレンジに分けて考えると、「青だけを使ったグラフ」、「オレンジだけを使ったグラフ」、「青とオレンジを使ったグラフ」に大別できます。

例えば、、、

「日別（青：event_date）のユーザー数（青：user_pseudo_idの固有の数）」は青だけ
「メディア（オレンジ：media）別のセッション（オレンジ：ga_session_idの固有の数）」はオレンジだけ
「ページタイトル（オンレジ：page_title）別のイベント数（青：行数、あるいはevent_nameの個数）」は青とオレンジ

を使って描画されます。

そして、上記の1番の場合には、event_paramsをunnestする必要はないんですね。なので、events_YYYYMMDDを利用している。2番と3番の場合には、event_paramsがunnestされたテーブルを参照する必要があるので、クロスジョインされた表を利用している。そいういうことではないでしょうか？

UNNESTしたevent_paramsとクロスジョインするとは？

少し長くなってきました。UNNESTとクロスジョインを説明して終わりにしましょう。UNNEST()というのは、行の中に入れ子になったテーブル（＝上記の模式図のオレンジのパートを見てください。行と、列があり、まさにテーブルの形をしていますね。）を、行から抜き出す関数だし、仮想的なテーブルとして取り出す関数だと思って良いです。

クロスジョインとは、表同士の結合（内部結合とか、左外部結合とかの結合です）の一種で、左側（右側と考えても同じことになりますが、理解の容易さを優先し、左側とします。）の表の1行に、右側の表の全行をくっつける結合方法です。元のテーブル（events_YYYYMMDD）とevent_paramsをunnestしたテーブルをクロスジョインすると、以下の模式図の通りになります。

以下は1ヒットに対する模式図ですが、実際には、左側の表の全部の行に対して、以下の模式図の操作が適用されます。元の左側の表に比べて、クロスジョインした結果のテーブルは、行数が何倍にも増えること、理解していただけるんじゃないかと思います。

だとすると何が嬉しいのか？

仮に、私の推測が正しく、

Looker Studioは、event_paramsが保持するフィールドを使わないグラフのデータソースには、内部的に、events_YYYYMMDDを直接参照する
Looker Studioは、event_paramsが保持するフィールドを使うグラフのデータソースには、内部的、events_YYYYMMDDとunnest(event_params)をクロスジョインしたテーブルを参照する

ということが分かると、何が嬉しいのでしょうか？まぁ、そんなに嬉しくないかもしれませんが、以下のようなメリットがあるかもしれません。

Looker Studioのグラフを、より、間違えないように作成できる
Looker Studioのグラフの数値がちょっとおかしいと思ったときにデバックのヒントになる
どうして、オレンジ同士のフィールドでは可視化できないのか？納得がいく

上記の3番については、追加の説明が必要かもしれませんね。例えば、「メディア別のセッション」をLooker Studioで可視化したいと考えた場合、メディアは、event_paramsの内部のmediumを利用します。つまり、オレンジです。セッションは、event_paramsの内部の、ga_session_idを利用します。つまり、こちらもオレンジです。なので、「メディア別のセッション」はオレンジ同士の組み合わせということになります。

すると、以下の通り、organicのところは、0となり、値が入ってきません。