1. はじめに
アンケートを実施する理由はさまざまです。顧客満足度の評価、市場調査の実施、商品やサービスの改善、従業員のエンゲージメントの評価などです。ただし、アンケート データを扱ったことがある方なら、標準形式のデータは扱いにくいことをご存じでしょう。このガイドでは、Google フォームの結果を取得し、Cloud Dataprep で分析用にデータを準備し、BigQuery に読み込み、Looker やデータポータルなどのツールを使用してチームがビジュアル分析を実行できるようにする自動パイプラインを構築します。
作成するアプリの概要
この Codelab では、Dataprep を使用して、Google フォームのアンケートの回答例をデータ分析に役立つ形式に変換します。変換されたデータは BigQuery にプッシュされ、SQL を使用してより詳細な質問をしたり、他のデータセットと結合してより強力な分析を行ったりできます。最後に、事前構築済みのダッシュボードを探索するか、独自のビジネス インテリジェンス ツールを BigQuery に接続して新しいレポートを作成できます。
学習内容
- Dataprep を使用してアンケート データを変換する方法
- アンケート データを BigQuery にプッシュする方法
- アンケート データからより多くの分析情報を得る方法
必要なもの
- 課金、BigQuery、Dataprep が有効になっている Google Cloud プロジェクト
- Dataprep の基礎知識があると役立ちますが、必須ではありません
- BigQuery と SQL の基礎知識があると役立ちますが、必須ではありません。
2. Google フォームの回答を管理する
まず、サンプル アンケートの 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 スプレッドシートのデータセット オブジェクトを右クリックして、[置換] を選択します。
次に、モーダルの下部にある [Import Datasets] リンクをクリックします。[パスを編集] 鉛筆アイコンをクリックします。
現在の値を、Google フォームの結果を含む Google スプレッドシートを指すこのリンクに置き換えます。Google の例を使用することも、独自のコピーを使用することもできます。https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk/edit?usp=sharing
[Go] をクリックし、右下の [Import & Add to Flow] をクリックします。モーダルに戻ったら、右下の [置換] ボタンをクリックします。
BigQuery テーブルを接続する
フローの右側で、出力を独自の BigQuery インスタンスに接続する必要があります。各出力について、アイコンをクリックして、次のようにプロパティを編集します。
まず、「手動の宛先」を編集します。
次の [公開設定] 画面で、編集ボタンをクリックします。
[Publishing Action] 画面が表示されたら、BigQuery 接続をクリックしてプロパティを編集し、接続設定を変更する必要があります。
Google フォームの結果を読み込む BigQuery データセットを選択します。BigQuery データセットをまだ作成していない場合は、「default」を選択できます。
「手動の宛先」を編集したら、「スケジュールされた宛先」の出力についても同じ手順で操作します。
同じ手順に沿って各出力を反復処理します。合計 8 つの宛先を編集する必要があります。
5. Cloud Dataprep フローの説明
「Google フォーム分析デザイン パターン」フローの基本的な考え方は、前述のように、各質問カテゴリを特定の Cloud Dataprep データ変換レシピに分割して、アンケートの回答に対して変換を実行することです。
このフローでは、質問を 4 つの表に分割します（わかりやすくするために、4 つの質問カテゴリに対応）。
「Clean Headers」、「SingleChoiceSELECT-Questions」、その他のレシピの順に、各レシピを 1 つずつ試すことをおすすめします。
すべてのレシピには、さまざまな変換ステップの説明がコメントとして記載されています。レシピでは、ステップを編集して、特定の列の変更前後の状態をプレビューできます。
6. Cloud Dataprep フローを実行する
ソースと宛先が正しく構成されたので、フローを実行して回答を変換し、BigQuery に読み込むことができます。各出力を選択し、[実行] ボタンをクリックします。指定された BigQuery テーブルが存在する場合は、新しい行が追加されます。存在しない場合は、新しいテーブルが作成されます。
左側のペインで [ジョブ履歴] アイコンをクリックして、ジョブをモニタリングします。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 の十字マークが付いたフレームの [空白のレポート] をクリックして、BigQuery に接続します。データポータルの手順に沿って操作します。詳細については、こちらでデータポータルのクイック スタートと主な機能の概要をご覧ください。構築済みのデータポータル ダッシュボードもこちらでご覧いただけます。
9. クリーンアップ
課金を停止する最も簡単な方法は、チュートリアル用に作成した Cloud プロジェクトを削除することです。また、リソースを個別に削除することもできます。
- Cloud コンソールで、リソースの管理に移動します。
- プロジェクト リストで、削除するプロジェクトを選択し、[削除] をクリックします。
- ダイアログでプロジェクト ID を入力し、[シャットダウン] をクリックしてプロジェクトを削除します。