概要
Google Spreadsheets（スプレッドシート）はGoogleアカウントさえあれば誰でも無料で利用でき、複数人で共有しながらデータを管理できるツールです。また同じくGoogleアカウントさえあれば無料で利用できるGoogle フォームで作成したアンケートの結果もスプレッドシートと連携、出力することで容易に集計することが可能です。
アンケートの結果だけでなく、セールスやマーケティングのデータベースとしてもしばしば利用されるスプレッドシートですが、ここに集められた大量のデータを他のDWH（データウェアハウス）に統合し、BIツールで可視化、戦略的な分析を行うというデータ分析基盤の構築にはある程度専門的なプログラミングの知識が必要になってきます。また常に新しいデータが集まる中、分析を最新の状態に保つのはコストがかかることです。
そこで今回は、trocco®（トロッコ）という分析基盤向けデータ統合サービスを使い、Google Spreadsheetsのデータ抽出、DWH（今回はGoogle BigQueryを使用）へのデータ統合、Looker Studio（旧：Googleデータポータル）を用いた可視化＋これらの自動化までをノンプログラミングでやってみます。
なお今回データの転送手段として使用するtrocco®は、スプレッドシートの他にも、様々な広告・CRM・DBなどのデータソースに対応しています。
ゴール
↓画像のようなデータから
↓画像のようなグラフをを30分くらいで作り上げます（作成後は自動で最新値に更新することも可能です）
こんな人におすすめ
・スプレッドシートに集まるデータをいちいち転送するのが面倒に感じる
・Googleフォームを利用したアンケートの結果などを自動的に分析できるようにしたい
・スプレッドシートをデータ元として利用したい
1. DWHと、データを統合する手段の決定
1-1. DWHの決定
まずはデータを集約する場所であるDWH（データウェアハウス）を選定します。
- Amazon Redshift
- Google BigQuery
- MySQLやPostgreSQL など
trocco®はいずれのサービスにも対応していますが、今回はGoogle BigQueryを利用することにします。
1-2. Google SpreadsheetのデータをGoogle BigQueryに転送する４つの方法
Google BigQueryにデータを集約することが決まったので、続いては転送するための手段を検討します。
- CSV形式でエクスポートしたスプレッドシートのデータを直接Google BigQueryに貼り付ける
- 外部データソースとしてスプレッドシートのシートURLを指定する。
- Google BigQuery データコネクタであるコネクテッド シートを使用する（ただしGoogle WorkspaceのプランがEnterprise以上である必要がある）
- trocco®を利用し、画面上の設定のみで転送する。
1 は単発の分析でしたら可能ですが、集めたデータをその都度人の手で更新するのは面倒な作業になりそうです。2 の方法ではGoogle BigQueryを操作するGoogleアカウントがデータ元となるスプレッドシートに対して閲覧以上の権限を有する必要があり、情報セキュリティの面でも余計な管理コストが発生しそうです。3 の方法ではGoogle Workspaceのプランが障壁となります。
今回は自動での更新が可能で、一度Googleアカウントとの連携を行えば設定を保存して異なる分析にも使い回せる4 のtrocco®を利用してみたいと思います。
2. trocco®でGoogle Spreadsheets → Google BigQueryの転送自動化
2-0. 事前準備
データの転送のためにはtrocco®のアカウント・Googleアカウントが必要です。
trocco®は無料トライアルを実施しているので、事前に申し込みをしておきましょう。
https://trocco.io/lp/index.html
（申し込みの際にこちらの記事を見たという旨を記載して頂ければご案内がスムーズに行えます）
Googleアカウントに関しては、今回転送元となるスプレッドシートのデータシートを所有するアカウントと、転送先となるGoogle BigQueryのアカウントが異なっても構いません。ただしデータの転送後に使用するGoogle BigQueryとGoogleデータポータルは同じアカウントで運用したほうが接続が簡単だと思います。
2-1. 転送元・転送先を決定
trocco®にアクセスして、ダッシュボードから「転送設定を作成」ボタンを押します。
転送元に「Google Spreadsheets」を指定し、転送先に「Google BigQuery」を選択して転送設定作成ボタンを押します。
設定画面になるので、必要な情報を入力していきます。
2-2. Google Spreadsheetsとの連携設定
trocco®には社内のユーザー間でチームを作成し、チームで転送設定を共有するチーム機能があります。チームの他メンバーにも転送設定の内容がわかるよう転送設定の名前とメモを入力します。
続いて「転送元の設定」内の「接続情報を追加」ボタンを押します。
別のタブで接続情報の新規作成画面が開きます。Googleアカウントとの連携を行います。
再度転送設定画面に戻り、接続情報を読み込むと、先ほど作成した接続情報が選択できるようになります。
2-3. Google Spreadsheetsからのデータ抽出設定
次にどのようなデータを取得するかを設定していきます。
今回は以下のようなシートから閲覧数（view）に関するデータを取得してみます。
必要なデータを入力していきます。
一番下のカラム設定のデータ型はstring, long, timestamp, boolean, doubleなどから選択が可能です。
2-4. 転送先Google BigQueryの設定
転送元と同様に設定していきます。Google BigQueryに関してもGoogleアカウントとの連携が必要です。（事前に転送先となるデータセットとテーブルを作成しておいてください。）
転送先とするデータセット名、テーブルを設定します。
これで入力は完了です。「保存して自動データ設定・プレビューへ」をクリックし、確認作業に進みましょう。
2-5. データのプレビュー
少し待つと、転送元のデータがプレビューされます。ここではスプレッドシートから取り込んだデータが表示されています。
転送したいデータが取れているので、設定を完了します。
転送設定の一覧から作成した設定を選び「スケジュール・通知設定」に進みます。
2-6. スケジュール・通知設定
「スケジュール・トリガー設定」タブを開きます。
「スケジュールを追加」ボタンを押すと、以下の画像のような入力欄が出てきます。ここで実行スケジュールを設定することで、転送を定期的に実行し自動化することが出来ます。
2-7. データ転送ジョブの実行
設定は以上です。最後に手動で転送ジョブを実行し、Google BigQueryにデータを送ります。
手動で実行する場合はジョブ詳細画面の「実行」ボタンを押します。
これで転送は完了です。
3. Google BigQueryの設定
Google BigQuery側で特に操作することはありません。
念の為データがきちんと送られているかをプレビューで確認してみます。
たしかにデータが転送されていることが確認できます。
4. Looker Studioで可視化
最後の工程としてこれらのデータをLooker Studioで可視化していきます。
まずはGoogle BigQueryとLooker Studioの接続設定を行います。
Looker Studioを開いて、新規のレポートを作成します。
「データを追加」のボタンを押し、データ元の選択肢からGoogle BigQueryを選択します。
転送に使用したプロジェクト、データセット、表（テーブル）を選択し、追加します。
データベースに接続できたら、右側の設定を変更し、必要な情報を表示させます。
後で作成する棒グラフにおいて、「ディメンション」が横軸、「指標」が縦軸に対応します。
最後に右上のグラフ一覧から目的に合ったグラフの形を選択します。
今回は閲覧数の比較をするため、棒グラフを選択します。
まとめ
いかがでしたでしょうか。trocco®を使うと、複数人で編集したり絶えず更新されるスプレッドシートのデータでも自動でGoogle BigQueryにまとめていくことができるほか、100万規模の大きなデータからそうではない小さなデータまで複雑なコーディングをせずtrocco®の画面上の設定のみでデータの転送が可能になります。
trocco®では、クレジットカード不要のフリープランをご案内しています。ご興味がある方はぜひこの機会に一度お試しください。
