FTP/FTPS/SFTPのデータをBigQueryに統合してユーザーの行動を可視化してサービスを改善

概要

運営しているサービスのユーザーの行動ログをFTP/FTPS/SFTPに溜めており、サービス改善のためにデータ分析を行おうと考える方もいらっしゃると思います。そこで今回はFTP/FTPS/SFTPのデータをBigQueryに自動転送し、データを可視化するまでを行ってみます。

ゴール

↓こんなのを15分くらいで作り上げます（当然、作ったあとは自動で最新値が更新されるようにします）

こんな人におすすめ

FTP/FTPS/SFTPにサービスの情報等を溜めている方
FTP/FTPS/SFTPにあるデータをBigQueryで分析したい方
自前のコードを用いたデータ取り込みに疲れている方・・・

1. DWHと同期する手段の選定

1-1. DWHの選定

まずはデータをどこに集約するか、DWH（データウェアハウス）を選定します。

Google BigQuery
Amazon Redshift
MySQLやPostgreSQL

今回はBigQueryを利用することにします。

1-2. FTP/FTPS/SFTPのデータをBigQueryに転送する4つの方法

BigQueryにデータを集約することが決まったので、次は転送するための手段を検討していきます。
　1. FTP/FTPS/SFTPのデータをダウンロードし、手動でBigQueryにアップロードする
　2. FTP/FTPS/SFTPとBigQueryを各APIやライブラリ等を用いてプログラムを書いて連携する
　3. Embulkを利用し、自分で環境を構築する
　4. troccoを利用し、画面上で設定する

1は単発の実行であればよいのですが、定期的な取り込み用途だと毎回同じ作業を繰り返すことになり、非効率な作業になりがちです
2はAPIのキャッチアップ工数+プログラムを書く工数+環境構築工数が発生する他、エラー対応などの運用工数も継続的に発生します
3も2と同じくEmbulkはある程度の専門知識が必要になり、自前で環境構築・運用を行う手間が発生します。加えてエラーの内容が少し専門的なので、↓のように困っている人もいたりします。

そこで今回はEmblukの課題も解決してくれて、プログラムを書かずに画面上の設定で作業が完結する、4のtroccoというSaaSを利用します。

その他にも広告やデータベースなど、様々な分析データをETL・転送した事例をまとめました。
[troccoの使い方まとめ（CRM・広告・データベース他）]
(https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)

2. troccoでFTP/FTPS/SFTP→BigQueryの転送自動化

2-0. 事前準備

troccoのアカウントおよびFTP/FTPS/SFTPのホスト情報等が必要です。
無料トライアルもやっているみたいなので、事前に申し込み・登録しておいてください！
（申込時に、この記事を見た旨を記載して頂ければご案内がスムーズです）

2-1. 転送元・転送先を決定

troccoにアクセスし、ダッシュボードから転送設定を作成ボタンを押します。

FTP/FTPSから転送したい場合は転送元にFTPを、SFTPから転送したい場合は転送元にSFTPを指定し、転送先にBigQueryを選択して転送設定作成ボタンを押します。（画像は転送元にFTPを選んだものです。）

すると、設定画面になるので、設定画面から必要な情報を入力していきます。

2-2. FTP/FTPS/SFTPとの連携設定

転送設定の名前とメモを入力したら、「転送元の設定」内の「接続情報を追加」ボタンを押します。

別タブで接続情報の新規作成画面が開きますので、FTP/FTPS/SFTPに関する情報を入力して、「保存」ボタンを押します。画像はFTPの接続設定作成画面であり、FTPSを利用する場合はSSL通信を有効にしてPEMファイルの内容を添付します。SFTPを利用する場合は画像の内容に加えて秘密鍵、秘密鍵のパスフレーズを入力します。

再度転送設定画面に戻り、接続情報の「再読込」ボタンを押すと、作成した接続情報が選択できるかと思います。

2-3. FTP/FTPS/SFTPからのデータ抽出設定

これでFTP/FTPS/SFTPとの連携は完了です。次に、どのようなデータを取得するか設定していきましょう。
ここでは試しにFTP/FTPS/SFTPの中に溜めておいたあるサイトのログを取得します。
パスプレフィックスで取得したいデータのディレクトリを指定します。
入力内容はFTP/FTPS/SFTPのいずれでもほぼ変わりません。

2-4. 転送先BigQueryの設定

転送元と同じ要領で設定していきます。
データセットとテーブルはお好きな名前を入力してください。自動生成オプションを有効にすれば、データセット・テーブルが自動作成されます。
また、カスタム変数を使うことにより、ジョブ実行時に指定の値に置き換えることができます。
BigQueryデータセットのロケーションを指定することができます。デフォルトはUSリージョンです。

これで入力は完了です。「保存して自動データ設定・プレビューへ」をクリックし、確認作業に進みましょう。

2-5. データのプレビュー

転送元のデータがプレビューされます。ここではFTP/FTPS/SFTPから取り込んだデータが表示されています。
問題ないので、このまま「スケジュール・通知設定」に進みます。

2-6. スケジュール・通知設定

以下のように実行スケジュールを設定することで、転送を自動化することが出来ます。

2-7. データ転送ジョブの実行

設定は以上です。最後に、手動で転送ジョブを実行し、BigQueryにデータを送ってみましょう。
実行はジョブ詳細画面の「実行」ボタンを押すだけです。

3. BigQueryの設定

特に設定することありません。データが溜まっているので、今すぐに分析・可視化を行うことが出来ます。
念の為データをプレビューして確認してみます。

4. Googleデータポータル（旧データスタジオ）で可視化

BigQueryの画面から、「エクスポート > データポータル」を選択します。

すると、以下のようなData Portalの画面に遷移します。
試しに、日ごとのクリック数を可視化してみましょう。
図の指示のとおりに変更してみてください。

すると、以下のようなグラフが出来上がります。

この画面はデータポータルの「エクスプローラ」という機能になります。

データポータルのトップ画面から、「レポート」を作成し、上述の通りグラフ作成を行うと、サービス用ダッシュボードが出来上がります。

まとめ

いかがでしたでしょうか。troccoを使うと管理画面を触ることなく、簡単にデータを転送し、DWH(BigQuery)に貯めることが出来ました。
また、BigQueryにデータを貯めると、データポータルというGoogleの無料ダッシュボードですぐに可視化することが出来ます。
実際に弊社サービスのtroccoにおいても、マーケティングKPI等をこのような流れで収集・分析しています。

実際に試してみたい場合は、無料トライアルを実施しているので、この機会にぜひ一度お試しください。
（申込時に、この記事を見た旨を記載して頂ければスムーズにご案内できます）

その他にも広告やデータベースなど、様々な分析データをETL・転送した事例をまとめました。
[troccoの使い方まとめ（CRM・広告・データベース他）]
(https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)