kintoneの営業データをRedshiftにETL(転送)し、Lookerで可視化する方法

## 概要
kintoneを使っている方は、売上、顧客、案件データを分析することがあると思います。
しかし肝心の分析を行うためには、kintoneのデータを分析環境に統合するという、手間のかかる作業が待っています。
そこで、今回は[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)を用いてkintoneのデータをRedshiftに出来るだけ簡単に統合する方法を紹介します。
さらに、Redshiftに統合したデータをLookerで可視化する方法も紹介します。

今回、データの転送手段として採用した[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)は、kintoneの他にも、様々な広告・CRM・DBなどのデータソースにも対応しています。
[troccoの使い方まとめ（CRM・広告・データベース他）](https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)
![2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/e1fdcf0d-8943-5b81-33e5-7ee3720eae22.png)

## ゴール
このようなkintoneのデータから
![1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/4dd39d22-195d-7186-52c5-39e89dcfa892.jpeg)

このようなダッシュボードを30分程度で作りあげます（当然、作成後は自動で最新値が更新されるようにします）
![スクリーンショット 2020-11-29 18.37.55.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/e54c13a9-b097-fe47-5fb2-a598f224d33a.jpeg)
## こんな人におすすめ
- kintoneのデータを分析基盤やDWH（データウェアハウス）に取り込みたい方
- データをcsvファイルにエクスポートして分析基盤に取り込むまでの作業時間が長く、無駄が多いと感じている方
- データ取得の作業に疲れている方

## 1. DWHと同期する手段の選定
### 1-1. DWHの選定
まずはデータの集約先であるDWHを選定します。

- `Amazon Redshift`
- `Google BigQuery`
- `MySQL`
- `PostgreSQL` など

今回は`Amazon Redshift`を利用することにします。
### 1-2.転送手段の選定
データの集約先がRedshiftに決まったので、次は転送するための手段を以下の4つから選びます。
**1.** データをcsvエクスポートし、手動でRedshiftにアップロードする。
**2.** kintoneとRedshiftの各APIを用いて、自分でプログラムを書いて連携させる。
**3.** Embulkを利用し、自分で環境を構築する。
**4.** [trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)を利用し、画面上で設定する。

**1**は単発の実行なら特に問題はありませんが、定期的に取り込む必要がある場合は非効率な作業になります。
**2**はAPIのキャッチアップ工数・プログラムを書く工数・環境構築工数が発生する他、エラー対応などの運用工数も継続的に発生します。
**3**も2と同じく、Embulkはある程度の専門知識が必要になり、自分で環境構築・運用を行う手間が発生します。さらに、エラーの内容が専門的で、詰まると大幅に時間を浪費してしまいます。

そこで、今回はEmbulkの課題も解決してくれて、プログラムを書かずに画面上の設定のみで作業が完結する、**4**の[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)というSaaSを利用します。
## 2. troccoでkintone→Redshiftの転送自動化
### 2-0. 事前準備
ここからの作業には、[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)のアカウントとkintoneのアカウントが必要になります。
無料トライアルも実施しているので、申し込み・登録をしておいてください！
[https://trocco.io/lp/index.html](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)
（申込時に、この記事を見た旨を記載して頂ければスムーズにご案内することができます）
### 2-1. 転送元・転送先を決定
[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)にアクセスし、「ダッシュボード」にある「転送設定を作成」ボタンを押します。
![trocco_dashboard_button.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/d9f369ed-fa30-8a0e-49c0-c12f82d26ae7.png)

転送元にkintone、転送先にAmazon Redshiftを選択し、「この内容で作成」ボタンを押します。
![スクリーンショット 2020-11-26 15.45.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/2ab41ba8-7e7c-dc95-78ab-98f3dddb1453.png)


すると、設定画面になるので、転送に必要な情報を入力していきます。
### 2-2. kintoneとの連携設定
後に見た時に、一目で何の転送設定か把握できるように、転送設定の名前とメモを入力します。
![スクリーンショット 2020-11-26 15.49.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/1934ca96-8c1e-c387-578e-cfb08b953fef.png)

転送設定の名前を決めたら、「転送元の設定」内の「接続情報を追加」ボタンを押し、kintoneの接続情報の設定を行います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/ab1b5bb5-e8cc-5375-b68b-e42311e52209.png)


データを取得したいkintoneの情報を入力し、接続情報を作成します。
![screencapture-localhost-3000-connections-kintone-new-2020-11-26-15_58_07.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/5a0a9a55-479c-1af4-238e-e252c5ab3b9a.jpeg)

これでkintoneとの連携は完了です。

### 2-3.kintoneからのデータ抽出設定
次に、kintoneのアプリや取得データを設定します。
kintoneの画面に記載されていないメタデータ（idや更新日時など）は、[Cybouzu](https://developer.cybozu.io/hc/ja/articles/202166330-%E3%83%95%E3%82%A3%E3%83%BC%E3%83%AB%E3%83%89%E5%BD%A2%E5%BC%8F)の開発者向けサイトにを参考に設定していきます。
クエリによる転送レコードの絞り込みも可能です。
今回は、今年のデータだけに絞って転送を行なっています。
![aaaa.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/a4fc3992-2c30-b133-e73d-b625b15d113a.jpeg)

また、カスタム変数を使うことにより、ジョブ実行時に指定の値に置き換えることができます。

以上でkintone側の設定は完了です。

### 2-4. 転送先Redshiftの設定
次に、転送先のRedshiftの設定を行います。
基本的には転送元と同じ要領です。
「接続情報を追加」ボタンからRedshiftの接続設定を行い、データベース名、スキーマ、テーブルを入力します。
![貼り付けた画像_2020_10_23_15_15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/af4acf3d-f2e8-9169-75cc-aeb022277b9f.png)

[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)では一時データの保存にS3を利用するため、S3パケット・S3キープレフィックス・転送モードも必須項目です。これらも入力します。
![貼り付けた画像_2020_10_23_15_19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/555ee8e4-96d2-acff-c1c7-e1f39d161f5c.png)

最後に、「この内容で接続を確認」ボタンを押して、接続確認が問題なく通るか確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/c4cc76b8-d13a-2825-c065-74dad24f9fb7.png)

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

### 2-5. データのプレビュー
少し待つと、転送元のデータがプレビューされます。kintoneから取り込んだデータが表示されているのが確認できると思います。
問題なければ、「設定を完了する」ボタンを押して、スケジュールや通知設定に進みます。
![b.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/8373f1b0-f173-7f53-69ea-e49b55f6f893.jpeg)

### 2-6. スケジュール設定
「スケジュール・トリガー設定」タブを開きます。
![スクリーンショット 2020-11-26 21.43.03.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/5a3e51f6-5fad-9911-892a-27a99e5e276c.jpeg)

以下のように実行スケジュールを設定することで、転送を自動化することができます。
![スクリーンショット 2020-11-26 21.43.34.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/216e02c8-1ac9-7aa7-299d-4e3786b90d06.jpeg)
### 2-7. 通知設定
必須の設定ではないですが、通知タイプ・通知条件・通知先を選択し設定を保存することで、EmailやSlackに通知を行うことができます。
![スクリーンショット 2020-11-26 21.48.30.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/9bb15b6c-fc4d-5dcf-1603-869d90312bc0.jpeg)

また、必要な時のみ実行したい場合は、転送設定詳細の「実行」ボタンを押すと、手動で転送を実行することもできます。
![スクリーンショット 2020-11-26 21.49.05.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/f3f3a58b-5727-8eca-cd99-75eb4ca42808.jpeg)
## 3. Redshiftの設定
特に設定することはありません。
データがきちんと送られているかプレビューで確認してみます。
データが溜まっているので、今すぐに分析・可視化を行うことができます。
![a.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/db6cf6bb-ee21-28c3-3f8e-e947ce297ead.jpeg)

これで、kintoneのデータがRedshiftに同期されていることが確認できました。
## 4. Lookerで可視化
### 4-1. RedshiftとLookerの接続設定
ここから、Redshiftに集約されたデータを可視化していきます。
Lookerの画面から、「管理 > Database > Connections」を開きます。
![貼り付けた画像_2020_11_02_16_02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/1c49b7cb-6134-5b40-b443-6c6eb80301a4.png)

すると、接続しているデータベースが一覧で表示されます。
この画面で「Add Connection > Database Connection」を開き、「Connection Setting」に接続するデータベース情報を入力します。
![貼り付けた画像_2020_11_02_16_06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/758e541a-32d6-3c71-1f25-39a8083f46bb.png)
![貼り付けた画像_2020_11_02_16_06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/820f9990-216b-52d5-b436-51de296832f4.png)

これで、RedshiftとLookerの接続設定が完了です。
### 4-2. LookMLプロジェクトの作成
次に、データを可視化するのに必要なLookMLプロジェクトを作成していきます。
「開発 > LookML プロジェクトの管理」を開きます。
![貼り付けた画像_2020_11_02_16_09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/53220d80-d1c7-34a2-64d3-6a503c9e0726.png)

「New LookML Project」を押して、新しいLookMLプロジェクトを作成します。
![貼り付けた画像_2020_11_02_15_51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/0c186535-04b5-4578-aa37-148ae49f3a7d.png)
![スクリーンショット 2020-11-26 23.15.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/9c40501c-defa-e4c3-ea44-4f726ce9b9bc.png)

これで、LookMLプロジェクトを作成できました。
# 4-3. モデルとビューの定義
次に、表示されたエディタで、モデルとビューを定義します。
後々必要になるので、モデルにデータベース接続とその接続を使用するExploreを定義します。
(書き方はLookerの[公式ドキュメント](https://docs.looker.com/ja/data-modeling/getting-started/model-development#adding_a_new_view_from_an_existing_database_table)を参考にしてください)
![スクリーンショット 2020-11-29 16.47.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/beaf4162-21d6-00f0-ab39-572a32562e58.png)
![スクリーンショット 2020-11-29 16.48.03.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/10a6d4bc-8f76-e7d6-5c12-a1b3209bfda4.jpeg)

これでグラフを作成する準備ができました。
# 4-4. グラフの作成
最後に、今回のゴールであるグラフを作成します。
トップページに戻り、「New > Dashboard」を押して、ダッシュボードを作成していきます。
![貼り付けた画像_2020_11_02_16_18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/cf8f2f71-b374-ba31-0d65-7cad45ce2068.png)

このように白紙のダッシュボードが作成されました。
「Dashboardの編集」を押し、「タイルの追加」を押し、先ほどのモデルで定義したExploreを選択します。
![スクリーンショット 2020-11-29 16.54.16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/469e9acd-7a6a-7a6a-ef95-ba5c739e0220.png)
![スクリーンショット 2020-11-29 16.54.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/e48f67bf-6edd-07b9-a133-ef91f9a5d5dd.png)
![スクリーンショット 2020-11-29 16.55.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/138bdc4d-673f-cae4-3e5e-41b907aaf110.png)

「DIMENSIONS」に横軸にしたいデータ、「MEASURES」に縦軸にしたいデータを選択し、「実行」を押すことで、グラフを作成することができます。
試しに、日毎の契約数を可視化してみます。
![スクリーンショット 2020-11-29 18.11.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/26ae3918-8684-c940-5dc8-ea37ab5503ba.png)

これで一つグラフが完成しました。
このような感じで他のグラフも作成すると、今回のゴールであるkintoneのダッシュボードが完成します。
![スクリーンショット 2020-11-29 18.37.55.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/460354/e54c13a9-b097-fe47-5fb2-a598f224d33a.jpeg)
## まとめ
いかがでしたでしょうか。[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)を使うと管理画面を触ることなく、簡単にkintoneのデータを取得し、DWH(Redshift)に貯めることが出来ました。
また、今回のようにRedshiftにデータを貯めると、Lookerを用いてデータを可視化することができます。
実際に弊社サービスの[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)においても、広告データやマーケティングKPI等をこのような流れで収集・分析しています。

[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)は、kintoneの他にも、様々な広告・CRM・DBなどのデータソースにも対応しています。
[troccoの使い方まとめ（CRM・広告・データベース他）](https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)

実際に試してみたい場合は、[無料トライアル](https://trocco.io/inquiry_trial/new?utm_medium=social&utm_source=qiita&utm_campaign=kintone_to_redshift)を実施しているので、この機会にぜひ一度お試しください。（申込時に、この記事を見た旨を記載して頂ければスムーズにご案内することができます）