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