Googleフォームの回答をRedashで分析してみました

シナプスの技術部システム開発課の蔵坪と申します。

業務で、Googleフォームのデータをオンプレミス内のRedashで分析する機会がありましたので、そちらの手順を紹介します。

Redashとは
今回の構成
Google Cloud Platformでサービスアカウントの取得
Googleフォーム
Redash
最後に
参考

Redashとは

データ解析・データ分析を手助けするツールです。SQL文を設定するだけでグラフやチャート等を簡単に表示することができます。Redashのオープンソースバージョンは、ダウンロードして無料で利用できます。弊社では、現在Version7.0.0を利用しております。

SQL文を設定するだけで、簡単にレポートを作成できますので、非常に重宝しております。また、Redashでは複数のデータソース間をまたがったSQLを簡単に発行できます。

https://redash.io/

今回の構成

f:id:mkuratsubo:20191024173102p:plain — 構成

Google Cloud Platformでサービスアカウントの取得

RedashからGoogleスプレッドシートにアクセスするには、Google Cloud Platformのサービスアカウントが必要になります。

Google Cloud Platformのアカウントをお持ちでない方は、取得してください。
API資格情報ページを開きます。プロンプトが表示されたら、プロジェクトを選択または作成します。
「資格情報の作成」ボタンをクリックします。表示されるドロップダウンで、「サービスアカウントキー」を選択します。
次のページで、ドロップダウンを使用して、手順1で選択したプロジェクトを選択します。役割については、ツリーメニューから選択します。
キータイプの下で、JSONを選択し、「作成」をクリックします。
ダウンロードされたjsonファイルのclient_emailに記載されているメールアドレスを控えます。

        {
        "type": "service_account",
        "project_id": "***",
        "private_key_id": "***",
        "private_key": "***",
        "client_email": "***@***.iam.gserviceaccount.com",
        "client_id": "***",
        "auth_uri": "https://accounts.google.com/o/oauth2/auth",
        "token_uri": "https://oauth2.googleapis.com/token",
        "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
        "client_x509_cert_url": "***"
        }

Googleフォーム

適当な質問を作成します。
IDを埋め込む質問を作成作成します。
事前入力したURLを取得でリンクを取得します。
事前取得

アンケート
3で取得したリンクは、下記のようになっており、「1234567」の部分が回答IDに埋め込まれることになります。
```
https://docs.google.com/forms/d/e/********/viewform?usp=pp_url&entry.2005837113=1234567
```
回答をスプレッドシートに保存するように設定しておきます。
スプレッドシートで、Google Cloud Platform でJSONファイルに記載されていたメールアドレスに対して、読み取りを許可します。
回答

Redash

データソースの追加

Redashの[設定]で、の新しいデータソースを追加しGoogleSpreadsheetを選択します。表示されるモーダルで、この接続に名前を付けGoogle認証情報コンソールからダウンロードしたjsonファイルをアップロードします。

f:id:mkuratsubo:20191024173701p:plain — データソース

クエリで確認

Google フォームの回答を取得できるか確認します。
クエリのデータソースを変更し、クエリを実行します。この時、テーブル名に相当するものはスプレッドシートのURLの一部になります。

クエリ
クエリを実行し、Googleスプレッドシートのデータが取得できたら、OKです。

オンプレミスデータベースとGoogleスプレッドシートのデータを結合

RedashのQuery Resultデータソース内では、既存に作成済のクエリをクエリIDで指定できます。クエリIDは、RedashのURLで確認できます。Query Resultのデータソースは、sqliteで動作しておりますので、利用できるSQLの文法はsqliteを参考にしてください。

データソース	クエリ名	クエリID	テーブル名
Googleスプレッドシート	アンケート	1	query_1
オンプレミスデータベース	回答顧客	2	query_2
Query Result	結合クエリ	3	-

f:id:mkuratsubo:20191024173753p:plain — 結合クエリ

SELECT アンケート.*,回答顧客.*
FROM query_1 アンケート
INNER JOIN query_2 回答顧客 ON アンケート.回答ID = 回答顧客.回答ID ;

最後に

今回の紹介した手順で、オンプレミス内のデータベースとGoogleフォームの回答とオンプレミスデータベースを結合することができました。あとはRedash上で、分析に必要なクエリと閲覧用のダッシュボードを用意すれば完了になります。

参考

Redash : Querying a Google Spreadsheet

シナプス技術者ブログ

シナプスの技術者公式ブログ。インターネットで、鹿児島の毎日を笑顔にします。