Looker Studioでのアンケートデータ可視化手法

## はじめに
弊社（VENECT株式会社）では、毎年SNSやECに関するアンケートを実施し、その集計結果をもとにスライド資料をPowerPointで作成してきました。今年度、そのCSVファイル形式のアンケートデータをLooker Studioでグラフ化するプロジェクトをインターンメンバーで実施しましたので、3回の記事に分けてトピックを紹介します。

今回の記事では、CSVファイル形式のアンケートデータをLooker Studioでグラフ化するまでの流れを紹介します。

### 関連記事
https://qiita.com/venect_qiita/items/0b26e0052fd7a5f67323

https://qiita.com/venect_qiita/items/03a5d1030b396935a6f3

## 背景
前述の通り、昨年まではアンケート結果をPowerPointを使用してスライド資料として作成してきました。この方法は、特定の年のデータを分析する上では便利ですが、**年度ごとの比較**や**異なるアンケート項目間の比較**が必要な場合に限界がありました。

従来の方法では、過去のアンケート結果と最新のデータを比較するために、異なる年度のスライドを開いて手動で並べて比較する必要があり、効率的ではありませんでした。

そこで、**BigQuery**を利用してアンケートデータを一元管理し、**Looker Studio**と連携してデータの可視化を行うことにしました。

## 新しいデータ管理方法のメリット

- **年度間の比較が容易**：過去のデータと最新のデータを同じ画面で瞬時に比較できるようになります。
- **柔軟な分析**：異なるアンケート項目をクロス集計したり、特定の項目に絞って分析することが可能です。
- **最新データの可視化**：アンケート結果が更新され次第、Looker Studioでのグラフも自動的に更新されるため、常に最新の情報を元に意思決定が行えます。

このデータ管理の改善により、私たちは今まで以上に効率的で柔軟な分析が可能になり、より有益な情報を得られるようになりました。


以下は実際に作成したグラフです。　このグラフでは、年代を選択することで、その年代のSNS利用率を年度ごとに比較できます。

![スクリーンショット 2024-10-31 10.37.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/ae03c330-8b5c-280d-cb70-96dcb6e97deb.png)

## アンケートデータについて
### アンケートの種類
アンケートには以下の種類があります。これに応じて適切にデータを管理します。
- **Single Answer**（単一選択）
- **Multiple Answer**（複数選択）
- **Matrix Single Answer**（行列型の単一選択）
- **Matrix Multiple Answer**（行列型の複数選択）

### 生データサンプル

以下は、実際に使用された設問と回答のデータサンプルです。

<details><summary>設問サンプルデータ</summary>

| Q4       | MA      |     普段使用するアプリケーションを選択して下さい　    |
|----------|---------|-----------------------------------------------|
| q4c1     | 1       | LINE                                          |
| q4s2     | 2       | Twitter                                       |
| q4s3     | 3       | Instagram                                     |


| Q5       | MA      |     　　投稿するアプリケーションを選択して下さい　    |
|----------|---------|-----------------------------------------------|
| q5c1     | 1       | LINE                                          |
| q5s2     | 2       | Twitter                                       |
| q5s3     | 3       | Instagram                                     |

</details>

<details><summary>回答サンプルデータ</summary>

IDは回答者を識別するための値です。

|    ID    |   q4s1   |   q4s2   |   q4s3   |   q5s1   |   q5s2   |   q5s3   |
|----------|----------|----------|----------|----------|----------|----------|
|    001   |     1    |     1    |     1    |     0    |     1    |     1    |
|    002   |     1    |     1    |     0    |     0    |     0    |     1    |


</details>

### アンケートデータの課題
弊社で活用しているWebアンケートサービスでは上述のようなアンケートデータが取得できるのですが、Looker Studioによる可視化・分析にあたって以下のような課題がありました。

- **グラフ化の制約**  
  Looker Studioでは、回答者のデータが上記の回答サンプルデータのように**横並び**（回答者毎に1行でまとめられた形式）になっていると、クロス集計や複雑な分析が難しくなります。そのため、私はアンピボットすることでデータを**縦型**に変換する必要があると考えました。


- **設問番号の変更**  
  同じ設問内容であっても設問番号が年によって変わることがあるため、データをBigQueryにアップロードする際に整合性を合わせる必要があります。

- **選択肢の変更**  
  例えば、前年の選択肢に「Twitter」が含まれていたものが、今年から「X」に変わるといったケースがあります。

## データの変換方法

毎年実施するアンケートデータを分析しやすくするため、データを**横並び**から**縦並び**に変換します。さらに、選択肢の重複を避けるための**マスターテーブル**を作成します。これにより、年度や設問の変更に柔軟に対応しつつ、データを整理することができます。

### 重複データの管理とマスターテーブル

以下の図は、設問、選択肢、回答を管理するために設計したマスターテーブルのER図です。

![スクリーンショット 2024-10-22 15.26.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/156f4641-3a6b-57a9-33c6-9ca54b4bc596.png)
(左図：設問、選択肢のマスターテーブル　右図：回答のマスターテーブル)

<details><summary>ER図の具体的な説明</summary>

上図（ER図）のように、選択肢や回答データをマスターテーブルで整理することで、データの管理が容易になります。設問や選択肢のマスターテーブルにおけるIDは独自のIDであり、アンケート実施時の設問番号や選択肢番号とは異なる場合があるため、これらの番号は別のテーブルで管理します。また、設問と選択肢の関係を保持するテーブルも用意します。回答のマスターテーブルでは、回答者IDと設問、選択肢、回答に対応する独自のIDを管理します。

</details>


上記の設問サンプルデータでは、同じ「LINE」が**設問4**と**設問5**の両方に登場しています。

- 設問4では「q4c1」  
- 設問5では「q5c1」  

このような重複があると管理が複雑になります。そこで、選択肢を**マスターテーブル**にまとめることで、データの冗長性を解消し、管理が容易になります。

### マスターテーブル例
- 設問マスターテーブル

|    q_id  |  format  |            question_text             |
|----------|----------|--------------------------------------|
|     4    |    MA    | 普段使用するアプリケーションを選択して下さい |
|     5    |    MA    |  投稿するアプリケーションを選択して下さい 　|

- 選択肢マスターテーブル

|    c_id  | choice_text |
|----------|-------------|
|     1    |    LINE     |
|     2    |   Twitter   |
|     3    |  Instagram  |


- 回答マスターテーブル

|   id   | q_id | c_id |answer|answer_text|
|--------|------|------|------|-----------|
|   001  |  4   |  1   |  1   |    LINE   |
|   002  |  4   |  1   |  1   |    LINE   |
|   001  |  4   |  2   |  1   |  Twitter  |
|   002  |  4   |  2   |  1   |  Twitter  |
|   001  |  4   |  3   |  1   | Instagram |
|   002  |  4   |  3   |  0   | Instagram |
|   001  |  5   |  1   |  0   |    LINE   |
|   002  |  5   |  1   |  0   |    LINE   |
|   001  |  5   |  2   |  1   |  Twitter  |
|   002  |  5   |  2   |  0   |  Twitter  |
|   001  |  5   |  3   |  1   | Instagram |
|   002  |  5   |  3   |  1   | Instagram |


このように、**設問マスターテーブル**、**選択肢マスターテーブル**、および**回答マスターテーブル**を作成することで、データの冗長性を排除し、管理が容易になります。また、`answer_text`カラムを追加することで、Looker Studioでのフィルタリングや編集が簡単になります。

さらに、設問番号の変更は手作業で対応が必要です。たとえば、前年の「普段使用するアプリケーション」が設問4だったものが、今年は設問5に変更される場合があります。このような場合、自動変換だけでなく、スプレッドシートでのマスター確認作業を取り入れることで、より正確に対応します。

## ワークフロー

大まかなデータ処理の流れは以下の通りです。

- **CSVファイル**からデータを取得  
- **データ加工**・マスターテーブル化  
- **スプレッドシート**に出力し、マスターID（`q_id`）の確認・修正  
- **BigQuery**にデータをアップロード  
- **Looker Studio**でグラフ表示  

### アップロードとデータ加工の詳細

以下は、上記のワークフローを細かく説明したものです。**Google Apps Script（GAS）**と**Google Cloud**を用いた、アンケートデータの自動アップロードおよびデータ変換のワークフローです。

1. **GASを使用したデータのアップロード**  
   スプレッドシートから設問データや回答データを、Google Cloud Storage(GCS)にアップロードします。

2. **Cloud Functionsでのデータ加工**  
   アップロード後、GASがCloud Functionsを呼び出し、設問データの加工が行われます（例：重複削除やデータフォーマットの調整）

3. **BigQueryから過去の設問データを取得**  
   BigQueryに保存された過去の設問データが取得され、最新のアンケートデータと比較されます。

4. **マスターIDの割り振り**
    4.1. **選択肢・設問文の一致確認**  
   過去のデータと新しいデータが完全一致するか確認し、一致する選択肢や設問文はデータベースの適したマスターIDを振ります。

    4.2. **一致しないデータの手動確認**  
   完全に一致しない選択肢や設問文は人間が手動で確認し、対応します。過去に存在しない新しい選択肢や設問には、新しいマスターIDが割り振られます。

5. **GASによるデータ変換**  
   GASが再びCloud Functionsをトリガーし、設問データと回答データの全てのデータに割り振られたマスターIDに変換され、回答データは横一列のフォーマットから縦一列に変換されます。

6. **BigQueryにデータをアップロード**  
   最終的に、変換されたデータがBigQueryにアップロードされ、Looker Studioでの視覚化に利用されます。

以下はワークフローを可視化した図です。

![スクリーンショット 2024-10-18 18.03.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/81db866c-492b-7674-1408-d7798b1e52e6.png)



## 終わりに
本記事では、CSV形式のアンケートデータをLooker Studioでグラフ化するためのワークフローを紹介しました。BigQueryを用いたデータの一元管理と、Looker Studioでのリアルタイム可視化によって、柔軟で効率的なデータ分析が可能になりました。

特に、一人の回答が横一列に並んでいる場合、クロス集計や高度なグラフ化が難しくなるため、データを縦型に変換することで柔軟性が向上しました。これにより、行数は増えるものの、より詳細な分析ができるようになっています。

また、毎年のアンケート設問や選択肢の変更に対しても、マスターテーブルを利用することで対応しやすくなり、データ管理の負担を軽減することが確認できました。この仕組みにより、継続的なデータの管理が容易になり、迅速で正確な意思決定を支える基盤を整えることができます。

次回の記事では、GASを使ったBigQueryの処理やCloud Storageへのファイルアップロードの具体的な実装について解説する予定ですので、ぜひご覧ください。

### 関連記事
https://qiita.com/venect_qiita/items/0b26e0052fd7a5f67323

https://qiita.com/venect_qiita/items/03a5d1030b396935a6f3