概要
Google Spreadsheets(スプレッドシート)に集められたデータを分析にかけるとき、更新がない数十件程度のデータであれば、BIツール上に手作業でグラフを作成することができます。しかし、分析用のデータはたいてい常に更新があり、最新のグラフを維持し続けることは難しいものです。そこで、DWH（データウェアハウス）にデータを自動で統合することが必要になるでしょう。
今回はtroccoという分析基盤向けデータ統合サービスを利用してGoogle Spreadsheetsのデータを自動で抽出できるよう設定し、DWHへの統合・データポータルの可視化までをやってみます。
データの転送手段として採用するtroccoは、Google Spreadsheetsの他にも、様々な広告・CRM・DBなどのデータソースに対応しています。
troccoの使い方まとめ（CRM・広告・データベース他）
ゴール
↓画像のようなデータから
↓画像のようなグラフをを30分くらいで作り上げます（作成後は自動で最新値に更新することも可能です）
こんな人におすすめ
・スプレッドシートに集まるデータを毎回DWHに転送するのは面倒
・Googleフォームを利用したアンケートの結果などを自動的に分析できるようにしたい
・スプレッドシート上だけでの分析では不十分なので手軽にDWHへ統合したい
1. DWHと同期する手段の選定
1-1. DWHの選定
まずはデータを集約する場所である、DWH（データウェアハウス）を選定します。
- Amazon Redshift
- Google BigQuery
- MySQLやPostgreSQL など
今回はAmazon Redshiftを利用しましょう。
1-2. Google SpreadsheetのデータをAmazon Redshiftに転送する方法
Amazon Redshiftにデータを集約することが決まったので、続いては転送するための手段を検討します。
- CSV形式でエクスポートしたGoogle Spreadsheetsのデータを直接Amazon Redshiftのクラスターへ転送する
- APIを用いた連携を構築する
- troccoを利用し、画面上の設定のみで転送する。
１は単発の分析でしたら可能ですが、収集したデータをその都度更新し続けることは困難です。２の方法ではAPIプログラミングに関する知識が必要で、非エンジニアには手が出しにくい、あるいは構築までに大きな時間的コストが必要となります。
今回はデータの自動更新が可能、かつ複雑なプログラミングの知識もそれほど必要としない４のtroccoを利用してみたいと思います。
2. troccoでGoogle Spreadsheets→Amazon Redshiftの転送自動化
2-0. 事前準備
データ転送のためにはtroccoのアカウント、データ元となるスプレッドシートにアクセスできるGoogleアカウント、Amazon Redshiftを操作するためAWSのアカウントが必要です。
troccoは無料トライアルを実施しています。ぜひ事前に申し込み・登録しておいてください。
https://trocco.io/lp/index.html
（申込の際に、この記事を見た旨を記載して頂ければご案内がスムーズに行えます）
2-1. 転送元・転送先を決定
troccoにアクセスして、ダッシュボードから「転送設定を作成」のボタンを押します。
転送元に「Google Spreadsheets」を指定し、転送先に「Amazon Redshift」を選択して転送設定作成ボタンを押します。
設定画面になるので必要な情報を入力していきます。
2-2. Google Spreadsheetsとの連携設定
まずはこの転送設定に名前をつけ、後で見返したときなんの設定かわかるようなメモを入力しておきます。複数のユーザーで転送設定を共有する機能もあるので、一度作成した設定をチーム内で使い回すことができます。
次に「転送元の設定」内の「接続情報を追加」ボタンを押します。事前に接続情報を設定しておけば「接続情報を読み込み」から呼び出すことが可能です。
別のタブで接続情報の新規作成画面が開きます。Googleアカウントとの連携が可能です。
再度転送設定画面に戻り、接続情報の「再読込」ボタンを押すと、先ほど作成した接続情報が選択できるようになります。
2-3. Google Spreadsheetsからのデータ抽出設定
次に、どのようなデータを取得するかを設定していきます。
今回はe-Statから取得した、日本の各宗教の信者に関する以下のサンプルデータを用いて分析を行ってみます。
必要なデータを入力していきます。
2-4. 転送先Amazon Redshiftの設定
転送元と同じく、「接続情報を追加」を押すと別のタブが開くので必要な情報を入力します。事前に設定しておくことでこちらもすぐに呼び出すことが可能です。
Redshiftとの接続設定が完了したら転送に必要な情報を記入していきます。
これで入力は完了です。「保存して自動データ設定・プレビューへ」をクリックし、確認作業に進みましょう。
2-5. データのプレビュー
転送元のデータがプレビューされました。先程設定したシートから取り込んだデータが表示されています。
データが正常に転送されてることが確認できました。
この画面ではマスキング設定・文字列の置換・暗号化といった設定も可能なほか、転送元・転送先に応じたオプション設定が可能です。(下の画像はRedshiftを利用した際のオプション設定です)
2-6. ジョブの実行
これで設定は終わりです。「実行」ボタンを押してAmazon Redshiftにデータを送ります。
これで転送は完了です！
3. Redshiftでプレビューを確認
trocco上の設定に従ってデータが転送されているはずです。
プレビューで確認してみます。
転送されていることが確認できました！
4. Lookerを用いた可視化
4-1.グラフ作成の準備
転送されたデータをLookerで可視化していきます。
まずはAmazon RedshiftとLookerを接続します。
Lookerを開いて、「管理」タブの「Connections」を開きます。「Add connections」からデータベースを追加します。
接続に必要な情報を入力していきます。
下部のテストボタンから接続を確認したら、エディタでモデルとビューの定義を行いましょう。これで可視化の準備が整いました。
モデルとビューの書き方は公式ドキュメントが参考になるかと思います。
4-2.グラフの作成
トップページに戻り、新規のダッシュボードを作成します。
「ダッシュボードの編集」、「タイルの追加」から先ほどモデルとビューを定義したExploreを選択しましょう。
ディメンション（縦軸）、メジャー（横軸）として定義されたデータが表示されています。
試しにディメンションとして「Area」と「Year」を、メジャーとして「Buddhism」を使用し、日本における仏教信者数の変動を可視化するとこのようなグラフができました。
このままでは少し見づらいので、フィルター機能を用いて地域を「関東」に限定し、折れ線グラフに変更してみましょう。
日本の仏教信者数は1990年と1997年を境に大きく増加しているようです。
こちらをタイルとして保存し、このダッシュボードの説明テキストのタイルも追加します。
他の宗教の信者数のグラフも追加するとこのようなタイルができました！
まとめ
いかがでしたでしょうか。troccoを使用することで、絶えず更新されていくGoogle Spreadsheetsのデータでも自動でAmazon Redshiftにまとめていくことができるほか、100万規模の大きなデータからそうではない小さなデータまで、複雑なコーディングをせずtroccoの画面上の設定だけでデータを転送することができます。
実際に弊社サービスのtroccoにおいても、マーケティングKPI等をこのような流れで収集・分析しています。
ぜひ広告データ分析の際にはご活用いただければと思います。
https://trocco.io/lp/index.html
実際に試してみたい場合は、無料トライアルを実施しているため、この機会にぜひ一度お試しください。（申込時にこの記事を見たという旨を記載していただければスムーズにご案内することができます）
その他にも広告やデータベースなど、様々な分析データをETL・転送した事例をまとめました。
troccoの使い方まとめ（CRM・広告・データベース他）