BigQueryとLooker StudioでGoogleフォームのマルチアンサーを感じよくグラフにしてみた

地味に機能アップしている（ような気がする）Google forms。社内外のアンケート調査に利用している企業の方も多いと思います。収集した結果を分析する方法としては、

Google Formsに備わっている「回答」ページにあるグラフを利用する
Google FormsからシームレスにデータをエクスポートできるGoogleシートを利用する

の2つの方法がポピュラーだと思います。しかし、そのときに困るのが（少なくとも私が困ったのは）マルチアンサー(Multiple Answers、以降MA)のグラフ化です。このブログ記事では、Google Formsで収集したMAのグラフ化を、BigQueryとLooker Studioで感じよく行う方法について解説します。

サンプルのGoogle Forms

サンプルのGoogle Formsは、以下の4問としました。名前の他には、シングルアンサー(Single Answers、SA）が一つ、MAが2つの典型的なアンケートフォームだと思います。

Google Formsの「回答」がイケてないところ

まずは、結果を確認するタブとして用意されているGoogle Formsの「回答」のイケてないところを見ていきましょう。以下は、旅行に行きたい国についての回答結果(内容はダミーなので、意味ないです）ですが、回答の多い順に並べ替えできません。そのため、すっと頭に入ってきません。

Goolgeシートに記録したデータ

それならば、自分でグラフ化すればいいじゃないか。ということでデータをGoogleシートに吐き出します。

Googleシートへのデータの記録

Googleフォームスの「回答」タブにある「スプレッドシートにリンク」をクリックすると、とてもスムーズに、回答結果をGoogleシートに記録することができます。この機能はとても良いですね。（BigQueryにもシームレスに吐き出せたらもっといいのにな^^;）

Googleシート上のデータ

実際にGoogle Formsに回答をして、15人分の架空のデータを蓄積しました。Googleシートに記録されたデータは以下のような形をしています。このブログ記事のテーマであるMAについては、海外旅行に行きたい国を尋ねた結果である、赤枠のセルを参照してください。

一つのセルの中に、カンマ区切りで、選択された回答が記録されています。確かに、「田中理恵」さんが4つの国を回答したことは理解できるのですが、グラフ化するという観点では、このデータの持ち方はちょっと曲者です。

BigQueryの登場

その「曲者的なデータの持ち方」を処理して、いい感じでグラフ化するために、Googleシート上のデータをBigQueryに移し替え、SQL文で処理し、処理したデータを使って、Looker Studioでグラフ化します。手順は以下の通りです。

GoogleシートのデータをBigQueryにインポートしてテーブルを作成する
作成したテーブルに対してSQL文を実行し、グラフ化しやすい形に整形する
整形に成功したら、SQL文をコピーして、Looker StudioのBigQueryコネクタ（カスタムクエリ）に投入する
Looker Studioでグラフを作成する

以下、上記の番号順に手順を見ていきます。

1.Googleシート上のデータのBigQueryへのインポート

まずは、Googleシート上のデータを、BigQueryにインポートしてテーブル（＝表）を作成します。文章にすると長いのですが、動画だとあっという間なので、動画にしました。2分強の動画です。

2.作成したテーブル上のデータの整形

次に、作成したテーブルのデータに対して、SQLを使って整形を行います。ここで肝となるのが、BigQueryのSQLが持つSPLITという関数です。この関数は、セルの中の値について、特定の文字列をデリミタ（＝区切り文字）として区切って、区切った個別の値を配列に入れてくれます。

配列とは、”複数の値を格納できる変数”のことです。普通の変数は、a=”アメリカ”と、aに”アメリカ”という文字列を代入したら、その値しか持てません。しかし、aを配列として作成すれば、aの最初の値は”アメリカ”、2番目の値は”フランス”、3番目の値は”スペイン”・・・のように複数の値を持たせることができます。

普段「配列」は、フラット化しなくてはいけない面倒なヤツ。と認識されていると思うのですが、ここでは大活躍します。カンマで区切った値を一度配列に入れてしまえば、それをフラット化することで、行に分割できるからです。まずは、SPLIT関数を利用して、q1の海外旅行に行きたい国、q2の楽しみたい活動を配列に入れた状態を見てみましょう。

赤枠が記述したSQL文、緑枠がそのSQL文を実行した結果です。非常にシンプルなSQL文ですが、元々のテーブルのq1、q2列がちゃんと、カンマで区切られた値が格納された配列に入っているのが確認できます。ちなみに、SLPIT関数のデリミタはデフォルトではカンマなので省略可能。指定すれば別の文字列をデリミタとして利用できます。

次に、上記のテーブルを仮想テーブルとして利用して、配列を解いてフラット化します。

こちらがSQL文です。WITH句では、masterという名前の仮想テーブルを作っています。本体のSQL文では、UNNEST関数を使って配列をフラット化し、フラット化された値を、クロスジョインしています。（11行目、12行目）

クロスジョインとは左側テーブル（この場合、master）の各レコードに対し、ジョインするテーブル（この場合、UNNESTされた配列の値のリスト）の全レコードを結合するジョインのことです。

こちらが結果テーブルです。配列が解かれ、クロスジョインされたことで、もともとは一つのセルにカンマ区切りで格納されていたデータがレコード（行）に展開されています。

3.Looker StudioからBigQueryのテーブルへの接続

BigQuery上でデータが整形できたので、利用したSQL文をコピーして、Looker StudioのBigQueryコネクタ（カスタムクエリ）に貼り付けます。

4.Looker Studioでのグラフ化

データ接続が完了したら、Looker Studio側で、回答者数を示す指標として、respondentsという名前の新しいフィールドを作成します。内容は、　COUNT_DISTINCT(name) 　です。固有のnameの個数を取得しています。あとは、特に大きな工夫は必要なく、MAの質問を以下の通りにグラフ化できます。