Google フォームのアンケートの回答を変換して BigQuery に読み込む

1. はじめに

アンケートを実施する理由は、顧客満足度の評価、市場調査の実施、プロダクトやサービスの改善、従業員のエンゲージメントの評価など、多岐にわたります。しかし、以前にアンケート データを扱ったことがある方なら、標準形式は扱いにくいことをご存じでしょう。このガイドでは、Google フォームの結果をキャプチャし、Cloud Dataprep で分析用にデータを準備して BigQuery に読み込み、Looker やデータポータルなどのツールを使用してチームがビジュアル分析を実行できるようにする自動パイプラインを構築します。

作成するアプリの概要

この Codelab では、Dataprep を使用して、Google フォームのアンケートの回答例をデータ分析に役立つ形式に変換します。変換したデータを BigQuery に push します。BigQuery では、SQL を使用してより詳細な質問をしたり、他のデータセットと結合してより強力な分析を行ったりできます。最後に、事前構築済みのダッシュボードを探索したり、独自のビジネス インテリジェンス ツールを BigQuery に接続して新しいレポートを作成したりできます。

学習内容

  • Dataprep を使用してアンケート データを変換する方法
  • アンケート データを BigQuery に push する方法
  • アンケート データからより多くの分析情報を得る方法

必要なもの

2. Google フォームの回答を管理する

まず、サンプル アンケートに対する Google フォームの回答を詳しく見ていきましょう。

f3d25efd2cc923f5.png

アンケート結果をエクスポートするには、[回答] タブで Google スプレッドシートのアイコンをクリックし、新しいスプレッドシートを作成するか、既存のスプレッドシートに結果を読み込みます。Google フォームでは、[回答を受け付ける] ボタンの選択を解除するまで、回答者が回答を送信すると、スプレッドシートに回答が追加されます。

d499e5a4dccdf5fd.png4939332a5d8f9f19.png

次に、各回答タイプと、それが Google スプレッドシート ファイルにどのように変換されるかを確認しましょう。

3. アンケートの回答を変換する

アンケートの質問は、特定の書き出し形式を持つ 4 つのファミリーに分類できます。質問の種類に応じて、特定の方法でデータを再構築する必要があります。ここでは、各グループと、適用する必要がある変換の種類を確認します。

単一選択の質問: 記述式、段落、プルダウン、均等目盛など。

  • 質問名: 列名
  • レスポンス: セルの値
  • 変換要件: 変換は必要ありません。レスポンスはそのまま読み込まれます。

3eeedc50b0fd54fd.png

多肢選択式の質問: 多肢選択、チェックボックス

  • 質問名: 列名
  • レスポンス: セミコロンで区切られた値のリスト(例: 「Resp 1; Resp 4; Resp 6」)
  • 変換要件: 値のリストを抽出してピボットし、各レスポンスが新しい行になるようにする必要があります。

cab8a38a96a13ce4.png

多肢選択式(グリッド)の質問

多肢選択式の質問の例を次に示します。各行から 1 つの値を選択する必要があります。

c6ea3d47d4dd5e78.png

  • 質問名: 各質問は、「質問 [オプション]」という形式の列名になります。
  • 回答: グリッド内の個々の回答が、一意の値を持つ列になります。
  • 変換要件: 各質問と回答は、テーブルの新しい行になり、2 つの列に分割される必要があります。1 つの列に質問の選択肢、もう 1 つの列に回答を記載します。

9223d0271516c58d.png

多肢選択式チェックボックス(グリッド)形式の質問

以下は、チェックボックス(グリッド)の例です。各行から 0 個から複数の値を選択できます。

4e3189b8cc2d4a8b.png

  • 質問名: 各質問は、「質問 [オプション]」という形式の列名になります。
  • 回答: グリッド内の個々の回答は、セミコロンで区切られた値のリストを含む列になります。
  • 変換要件: これらの質問タイプは「チェックボックス」と「多肢選択式(グリッド)」のカテゴリを組み合わせたもので、この順序で解決する必要があります。

まず、各回答の値のリストを抽出してピボットし、各回答が特定の質問の新しい行になるようにする必要があります。

2 つ目は、個々の回答をテーブルの新しい行にし、2 つの列に分割する必要があるということです。1 つの列に質問の選択肢、もう 1 つの列に回答を記載します。

3c3c2bd098e03003.png

次に、これらの変換が Cloud Dataprep でどのように処理されるかを示します。

4. Cloud Dataprep フローを構築する

Cloud Dataprep に「Google フォーム分析設計パターン」をインポートする

Google フォーム アナリティクス デザイン パターンのフロー パッケージをダウンロードします(解凍はしないでください)。Cloud Dataprep アプリケーションで、左側のナビゲーション バーにあるフローアイコンをクリックします。[Flows] ページで、コンテキスト メニューから [Import] を選択します。

ba7c0cb0eec398df.png

フローをインポートしたら、インポートしたフローを選択して編集します。画面は次のようになります。

44978861eb34ec71.png

Google スプレッドシートのアンケート結果スプレッドシートを接続する

フローの左側で、データソースを Google フォームの結果を含む Google スプレッドシートに再接続する必要があります。Google スプレッドシートのデータセット オブジェクトを右クリックして、[置換] を選択します。

55c16f0c04366f0c.png

次に、モーダルの下部にある [Import Datasets] リンクをクリックします。[パスを編集] の鉛筆アイコンをクリックします。

8afeef260c96277f.png

そこで、現在の値を、Google フォームの結果を含む Google スプレッドシートを指すこのリンクに置き換えます。この例を使用することも、独自のコピーを使用することもできます。https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk/edit?usp=sharing

[Go] をクリックし、右下の [Import & Add to Flow] をクリックします。モーダルに戻ったら、右下の [Replace] ボタンをクリックします。

BigQuery テーブルを接続する

フローの右側で、出力を独自の BigQuery インスタンスに接続する必要があります。各出力のアイコンをクリックし、次のようにプロパティを編集します。

まず、[手動で設定した目的地] を編集します。

a3fc2cb80153ec25.png

次の [公開設定] 画面で、編集ボタンをクリックします。

85791e6162a370de.png

[Publishing Action] 画面が表示されたら、BigQuery 接続をクリックしてそのプロパティを編集し、接続設定を変更する必要があります。

1f3e4887baaeaffd.png

Google フォームの結果を読み込む BigQuery データセットを選択します。BigQuery データセットをまだ作成していない場合は、[default] を選択できます。

f4eaa05ecf9de162.png

「手動の宛先」を編集したら、「スケジュールされた宛先」の出力についても同じ手順を行います。

46edea1b8ca63270.png

同じ手順で各出力を反復処理します。合計 8 つの宛先を編集する必要があります。

5. Cloud Dataprep フローの説明

「Google フォーム分析デザイン パターン」フローの基本的な考え方は、前述のように、各質問カテゴリを特定の Cloud Dataprep データ変換レシピに分解して、アンケートの回答を変換することです。

このフローでは、質問を 4 つのテーブルに分割します(わかりやすくするために、4 つの質問カテゴリに対応しています)。

afa421849b1bd398.png

「Clean Headers」、「SingleChoiceSELECT-Questions」の順に、各レシピを 1 つずつ確認することをおすすめします。

すべてのレシピには、さまざまな変換ステップを説明するコメントが付けられています。レシピでは、ステップを編集して、特定の列の変更前と変更後の状態をプレビューできます。

449da06d96cd520e.png4ac6e14f578d0707.png

6. Cloud Dataprep フローを実行する

ソースと宛先が正しく構成されたので、フローを実行して回答を変換し、BigQuery に読み込むことができます。各出力を選択し、[実行] ボタンをクリックします。指定した BigQuery テーブルが存在する場合、Dataprep は新しい行を追加します。存在しない場合は、新しいテーブルを作成します。

47cf50f6d17a5b1e.png

左側のパネルにある [ジョブ履歴] アイコンをクリックして、ジョブをモニタリングします。BigQuery テーブルの読み込みが完了するまでに数分かかります。

afc79eeb27202fb4.png

すべてのジョブが完了すると、アンケート結果がクリーンで構造化された正規化形式で BigQuery に読み込まれ、分析の準備が整います。

7. BigQuery でアンケート データを分析する

BigQuery の Google コンソールで、新しい各テーブルの詳細を確認できます。

df370873572511ac.png

BigQuery にアンケート データを取り込むことで、より包括的な質問を簡単に行い、アンケートの回答をより深く理解できるようになります。たとえば、さまざまな役職の人々が最も一般的に使用しているプログラミング言語を把握しようとしている場合、次のようなクエリを作成できます。

SELECT
   programming_answers.Language  AS programming_answers_language,
   project_answers.Title  AS project_answers_title,
   AVG((case when programming_answers.Level='None' then 0 
when programming_answers.Level='beginner' then 1
when programming_answers.Level='competent' then 2 
when programming_answers.Level='proficient' then 3
when programming_answers.Level='expert' then 4 
else null end) ) AS programming_answers_average_level_value
FROM `my-project.DesignPattern.A000111_ProjectAnswers` AS project_answers
INNER JOIN `my-project.A000111_ProgrammingAnswers` AS programming_answers
ON programming_answers.RESPONSE_ID = project_answers.RESPONSE_ID
GROUP BY 1,2
ORDER BY 3 DESC

分析をさらに強化するには、アンケートの回答を CRM データに結合して、参加者がデータ ウェアハウスにすでに含まれているアカウントにマッピングされているかどうかを確認します。これにより、カスタマー サポートや新規リリースのターゲット ユーザーに関する意思決定を、より多くの情報に基づいて行うことができます。

ここでは、回答者のドメインとアカウントのウェブサイトに基づいて、アンケート データをアカウント テーブルに結合する方法を示します。アカウントの種類別に回答の分布を確認できるようになりました。これにより、既存の顧客アカウントに属する回答者の数を把握できます。

SELECT
   account.TYPE  AS account_type,
   COUNT(DISTINCT project_answers.Domainname) AS project_answers_count_domains
FROM `my-project.A000111_ProjectAnswers` AS project_answers
LEFT JOIN `my-project.testing.account` AS account 
ON project_answers.Domainname=account.website
GROUP BY 1

8. ビジュアル分析を実行する

アンケート データがデータ ウェアハウスに一元化されたので、ビジネス インテリジェンス ツールで簡単にデータを分析できるようになりました。データポータルと Looker で、いくつかのサンプル レポートを作成しました。

Looker

すでに Looker インスタンスがある場合は、このフォルダの LookML を使用して、このパターンのサンプル アンケートと CRM データの分析を開始できます。新しい Looker プロジェクトを作成し、LookML を追加して、ファイル内の接続名とテーブル名を BigQuery の構成に合わせて置き換えるだけです。Looker インスタンスをお持ちでないものの、詳細にご興味がある場合は、こちらからデモのスケジュールを設定できます。

129db05d6f85f484.png

データポータル

または、データポータルでレポートを作成するには、Google の十字アイコンが付いた [空のレポート] フレームをクリックして BigQuery に接続します。データポータルのすべての手順に沿って操作します。詳細については、こちらでデータポータルの主な機能のクイック スタートと概要をご覧ください。また、事前構築済みのデータポータル ダッシュボードもこちらでご覧いただけます。

5e744869e3fe3f8f.png

9. クリーンアップ

課金を停止する最も簡単な方法は、チュートリアル用に作成した Cloud プロジェクトを削除することです。また、リソースを個別に削除することもできます。

  1. Cloud コンソールで、[リソースの管理] に移動します。
  2. プロジェクト リストで、削除するプロジェクトを選択し、[削除] をクリックします。
  3. ダイアログでプロジェクト ID を入力し、[シャットダウン] をクリックしてプロジェクトを削除します。