このページは Cloud Translation API によって翻訳されました。

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

1. はじめに

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

作成するアプリの概要

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

学習内容

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

必要なもの

課金、BigQuery、Dataprep が有効になっている Google Cloud プロジェクト
Dataprep の基本的な知識があると役立ちますが、必須ではありません
BigQuery と SQL の基本的な知識があると役立ちますが、必須ではありません

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

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

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

各回答の種類と、Google スプレッドシートファイルへの変換方法について確認しましょう。

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

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

質問の名前: 列名
レスポンス: セル値
変換の要件: 変換は不要です。レスポンスはそのまま読み込まれます。

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

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

選択式（グリッド）の問題

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

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

選択式チェックボックス（グリッド）形式の質問

チェックボックスグリッドの例を次に示します。各行から 0 ～複数の値を選択できます。

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

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

2 つ目: 各回答をテーブルの新しい行にして、2 つの列に分割する必要があります。1 つの列に質問のオプション、もう 1 つの列に回答を入力します。

次に、これらの変換が Cloud Dataprep でどのように処理されるかについて説明します。

4. Cloud Dataprep フローを作成する

Cloud Dataprep に「Google フォームアナリティクス設計パターン」をインポートする

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

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

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

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

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

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

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

BigQuery テーブルを接続する

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

まず、[手動リンク先] を編集します。

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

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

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

[手動の宛先] を編集したら、[スケジュール設定された宛先] の出力に対しても同様に操作します。

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

5. Cloud Dataprep のフローの説明

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

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

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

すべてのレシピには、さまざまな変換ステップを説明するコメントが付いています。レシピ内では、ステップを編集して特定の列の変化を確認できます。

6. Cloud Dataprep フローを実行します。

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

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

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

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

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

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 インスタンスをお持ちでないが、詳細を知りたい場合は、こちらからデモを予約してください。

データポータル

または、データポータルでレポートを作成するには、Google のクロスが表示されているフレーム「Blank Report」をクリックして BigQuery に接続します。データポータルに表示される手順に沿って操作します。詳しくは、データポータルのクイックスタートと主な機能の概要をこちらでご確認ください。データポータルの組み込みダッシュボードもこちらで確認できます。

9. クリーンアップ

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

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