無料でSalesForce(SFDC)のデータ基盤をBigQueryにつくってみた

# はじめに
本記事は、[AlphaDriveアドベントカレンダー](https://qiita.com/advent-calendar/2023/alphadrive-tech)　12/17公開記事になります。

初めましての人は初めまして。AlphaDriveでPdMを担当している @tarox です。

こちらの記事では、無料で手軽にSalesForce.com（以下SFDC）のデータを使ったデータ基盤構築のやり方についてご紹介します。

環境としては以下が使える状態、という前提になります。
 - Google Workspace:スプレッドシートを使います
 - Salesforce.com(SFDC):データソースとして使います
 - おまけ1でnotionをつかいます、必須じゃないです
 - おまけ2でslackをつかいます、必須じゃないです


データ基盤を作るのに「無料でここまでできる」という点と、「有料でできるようになること」についても簡単にまとめます。


# 解決したい課題
SFDCのデータを使ったレポートはSFDC上で作ることができますが、ビッグデータの取り扱いが難しかったり、データを加工してよりデータ活用を進めたいという要件がありました。
また、弊社ならではの要件として、Googleスプレッドシートを使ったダッシュボードが定着しており、スプレッドシートでデータを見たい、というニーズもありました。

また、プロダクトによってはデータ基盤環境が存在せず、ゼロから環境を構築しなければなりませんでした。
そういう状況だったので、なるべくコストをかけずに上記の要件を踏まえつつデータ基盤を構築し、データ基盤の必要性やその価値を周りにアピールする必要がありました。

そこで私が考え、実践したものが本ページにまとめた内容です。

同じような悩みを抱えている方にこちらの知見を共有できたらと思います。


# スプレッドシートのSFDCコネクターによるSFDCのデータ取得

まず、SFDCのデータをどう出力するか？という課題にぶつかりました。
TroccoやFivetranをいきなり契約するというのも難しいし、無料でやるには定期的にSFDCからCSVを手でダウンロードするか、Seleniumとかつかってタスクを自動化するか、、いずれにしてもしんどいなと思っていました。

そのとき調べていて知ったのが、Googleスプレッドシートの拡張機能・アドオンの、Salesforce Connectorでした。Google謹製のコネクターという点はいいなと思いました。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/8edb6a17-fc11-9bdf-3a53-654c95c0ac31.png" width="640"/>

早速使えるようにセットアップしましょう。

1. スプレッドシートの拡張機能メニュー＞アドオン＞アドオンを取得を選択
2. Salesforce Connectorを選択してインストール
3. SalesforceのIDとパスワード入力して認証する

これで使う準備が完了しました。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/66637db9-b749-f50b-d430-9a1f473ceebc.png" width="480"/>

Salesforce Connectorでできることはいろいろありますが、私の使い方は以下です。

1. SFDC上でほしいデータのレポートを作成
2. Salesforce ConnectorのReportsメニューからSalesforce Connectorで該当のSFDCレポートを指定して読み込み
3. Salesforce Connectorのスケジューラ（Refreshメニュー）で日ごとに更新する設定を行う

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/18a489a9-bfa6-9d14-df96-0c787760f3b4.png" width="360"/>

使っていてびっくりするのは、サクッと1，2分ほどでSFDCのデータをスプレッドシートに取り込める点です。
私はいろいろなBIツールでSFDCコネクタを使ってきましたが、このスピードは異常なほど速いです。Googleのエンジニアがどういう処理を書いているのか、とても興味深く思います。これが無料で使えるなんて、使わない手はないです。

なお、使っていてわかった注意点があります。

1. 一度に読み込むレコード数が**20万レコード**を超えてくると、読み込みエラーが発生してしまいます
    + 対処法としては、SFDC側のレポートを分割して、20万超えないようにフィルターをかけて、それぞれスプレッドシートに読み込むようにする、という方法です
2. SFDCレポート側でグループ設定している場合はGroup results if possibleにチェックを入れると読み込めるようになりました。してないとエラーになります
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/8eca0819-9182-4f73-7c00-b82e2c6bc032.png" width="360" /><br />
3. 日次スケジューラは時間指定ができないのですが、固定で**毎日16時**に発火しています
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/72598030-0f81-58a5-08cc-9a8e4e263f8a.png" width="360" /><br />

# BigQueryサンドボックス（無料）環境を作る
さて、スプレッドシートにSFDCのデータが読み込めたら次はBigQueryでデータ基盤を作っていきます。
データ基盤（データウェアハウスとも言います）を作る理由は、いくつかあります。

1. 個別にスプレッドシートのデータを参照する運用だと、どの数字が正しい数字かわからなくなってしまうので、データ基盤を作って一元管理をするため
2. ビッグデータのデータ操作をスプレッドシートでやろうとすると、シートがクラッシュしてしまう。ビッグデータのデータ操作はデータ基盤環境でないと実現不可能なため
3. スプレッドシート以外の様々なデータを取り込むことができるので、散らばった業務データを集約するため（BigQueryだと無料でGoogleAnalyticsのローデータを取り込んだりすることができます、以前は高額でした。）

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/3a89d8a9-2e9f-af2b-bc07-d6fa164e56d5.png" width="640"/>

BigQueryを無料で使うには以下の手順で使えるようになります。

1. [Bigqueryのサービス紹介サイト](https://cloud.google.com/bigquery?hl=ja)から「コンソールへ移動」ボタンを押して、利用許諾にチェック入れてOK
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/3e128f0d-0d87-bb9c-82eb-478c6663e0dd.png" width="640"/><br/>
2. プロジェクトを選択＞新しいプロジェクト　を選んで、プロジェクトを作成してください。ここでは「組織なし」配下に作りましたが、あなたが所属してる会社組織がある場合は、そちらの組織を選択してその配下にプロジェクトを作ってください
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/dbcb830e-c6ac-bcdc-5b11-ae49e40b14f8.png" width="640"/><br/>
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/1c1b7faa-ece6-e93e-3c21-b48e3ebeef52.png" width="640"/><br/>
3. プロジェクト作成できたら利用開始できます。他の人を招待する場合はIAMでBigQuery利用権限を付与してください
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/26c822d8-5d21-14fa-5baa-f4d50dd5fff1.png" width="640"/><br/>
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/69c2dfb1-b3c6-e7f7-f3f5-59e53cce6545.png" width="640"/><br/>

# サンドボックス状態のBigQueryでできること・有料化しないと出来ないこと

サンドボックス状態のBigQueryでできることを以下まとめます。

1. スプレッドシートの外部テーブル読み込み
2. CSVファイルなどのアップロードによるテーブル作成
3. Select文の実行
4. クエリの保存（旧バージョンのクエリ保存）
5. ビューの保存
6. Google Analyticsローデータの読み込み
7. スプレッドシートからのBQデータへのコネクト

と、大体のことができます。

サンドボックス状態の機能制限は
1. 1か月あたり10GBのアクティブストレージの制限
2. 1か月あたり1TBのクエリデータ処理の制限
3. テーブルの有効期限が60日
4. INSERTやUPDATE、DELETEは使用できない
5. スケジュールドクエリの保存と実行ができない
6. Data Transfer Serviceの利用ができない
7. データリネージの表示ができない
8. データストリームが利用できない
などです。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/e1d59a62-8b57-16dc-706f-1e61835c3620.png" width="640"/><br/>

スケジュールドクエリが使えないので、カスタムクエリでデータマート用のテーブルを作ると言ったことができません。処理が多少重くなりますが、ビューで代用します。

また、サンドボックスだとData Transfer Serviceがつかえないので、S3バケットとかGCSなどのクラウドストレージからデータを転送することができません。ここではスプレッドシートを外部テーブルという機能を使って、参照する方法を利用していきます。

テーブルの有効期限が60日なので、つくった外部テーブルは60日後に消えてしまいます。
ただ、データの本体はスプレッドシート側にあるので、60日経ったら再度外部テーブルを作ることで、復元が出来ます。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/4fce5673-c023-2b1c-2d2b-1b75b033ea1a.png" width="640"/><br/>


この運用は結構厳しいと思うので、早いタイミングでのBQへの請求アカウントの設定をすることを目標にしましょう。

<font color="red"><strong>注意！</strong></font>
ただし、BQ環境をサンドボックス状態から請求アカウント設定して有効化しても、サンドボックス状態の時に作ったデータセットやテーブルは「有効期限が60日」にセットされたままです。
速やかに、データセットとテーブルの有効期限を「なし」に設定変更してください。そうしないと60日経ったら消えてしまいます。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/dc3ef80b-1841-78af-4e22-16518ae6f154.png" width="640"/><br/>

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/dca74c68-2cdf-f202-6ec1-4059252b48ae.png" width="640"/><br/>



# SFDCデータを取り込んだスプレッドシートをBigQueryで扱う方法

では早速SalesForceコネクタでスプレッドシートに取り込んだデータを外部テーブル機能を使いBigQueryから参照しましょう。

先ずは、そのテーブルを配置するデータセットを作りましょう。

1. プロジェクト名の右にある三点リーダをクリックし、データセットを作るを選択
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/20cb939a-c6b0-0def-3df8-ae35142ade4c.png" width="480"/><br/>
1. データセット設定画面に以下のように設定して、データセットをつくる
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/cf9e4665-322a-0fef-7027-490a6e34c296.png" width="480"/>

データセットが出来たら以下の手順に沿って作業してください。


1. データセット名の横の三点リーダをクリックして、テーブルを作成
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/0b873998-f94d-971d-c695-267e0199ba92.png" width="360"/><br/>
2. Googleドライブを指定
3. スプレッドシートのURLと読み込み範囲を指定（読み込み範囲は指定しなくてもいい、最初のシートのデータを自動で取得してくれるので）
4. カラム名を指定、データ型を指定する。自動読み込みでも読み込めるが、細かい設定ができず読み込みエラーの原因になったりするので、自分は自動読み込みしないようにしています
5. 詳細オプションを開いて、スキップするヘッダー行に「1」を入力
6. テーブルを作成ボタンを押す
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/dbfaa63a-99f5-1ebf-e3a7-d55dfecd33cc.png" width="360"/>

テーブルが出来たら、以下のクエリを実行してデータが参照できるか確認してください。

```sql
SELECT * FROM `プロジェクト名.データセット名.テーブル名` LIMIT 1000
```

こういうフローが出来たことになります。BQは直接スプシを見に行っています。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/bdb031c6-8fa5-e6ec-43af-bf144bba9188.png" width="640"/>

## 注意点
1. 以前はカラム名が英語じゃないとエラーになってしまっていたが、今は日本語でもOKになりました
2. 日付のデータはスラッシュで区切るのではなくハイフンで区切りじゃないとBQは読み込まないので、スプレッドシート側で日付データを選択し、「表示形式＞数字からハイフン区切りの日付フォーマットを選択してください
3. 一度外部テーブル設定したら、あとから設定を変えられない項目が多い（カラム名とか）ので、一度テーブルを削除して作り直す必要がある。テーブル詳細タブ＞詳細を編集から編集できる項目はこれしかない
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/42db7b08-b844-bd9a-fc58-cd6720b9194e.png" width="360"/><br/>
4. スプレッドシートの共有情報と外部テーブルのアクセス情報は一致するので、外部テーブル指定したBQのテーブルはスプレッドシートの共有者のみに公開されます



# 読み込んだSFDCのデータを変形・カスタマイズする
スプレッドシートのデータを読み込んだ外部テーブルを複数参照して、データマート的なビューを作成します。

1. データマート用のデータセットを作成
2. 今までの手順で作った外部テーブルのデータを変形するクエリを書く
3. 保存＞ビューを保存を選択
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/e08f810b-be74-0028-35d3-5fa8c53dfd32.png" width="360"/><br/>
4. ビューの名前など入力して保存ボタンを押す
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/96cdf94c-ed40-23e3-c709-188003f1de38.png" width="640"/><br/>


# 作ったデータマートテーブルをスプレッドシート側からコネクトする
BQで作ったテーブルをスプレッドシートから参照するやり方についてまとめます。

1. BQ側でクエリを実行
1. データを探索から「シートを使って調べる」を選択。これでスプレッドシートに先ほどのクエリの結果が接続されます
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/eef82492-3a1f-ac04-958f-d1b7626819a0.png" width="480"/><br/>



また、スプレッドシート側でクエリのスケジュール実行機能があるので、定期的に最新の情報を表示することができます。Salesforceコネクタが16時に発火するので、こちらのスケジューラは17時にセットします。

1. BQとつながった画面で、更新オプションを表示
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/61b29603-31e7-4947-afef-13c196ad2dd4.png" width="480"/><br/>
1. 右サイドバーでスケジュールが設定できます
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/b5047a71-6b92-fe70-1150-3023f6d55931.png" width="320"/><br/>

ここまでで作ったデータパイプラインの処理をまとめるとこうなります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/188c3b6d-50d9-1bb3-57f4-2721dc62216e.png" width="720"/><br/>


# 最後の仕上げ
あとはピボットテーブルのデータを加工して、グラフを作成すればスプシのダッシュボードのできあがり！ここまで無料です！
※以下のグラフはダミーのデータです。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/383cf0a1-a431-0c15-9136-497caca62818.png" width="640"/><br/>


# おまけ1、notionをダッシュボード化してしまう

スプレッドシートによみこんだデータをピボットテーブルで活用し、グラフを出力しましょう。

1. つくったグラフの三点リーダメニューから共有を選択し、社内のメンバーに共有すると、URLが生成されます
1. URLをコピーして、notionのグラフを表示したい場所に移動し、「/埋め込み」と書くと、URLを指定するボックスが表示されるので、URLを貼り付けます
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/283f4bb6-6098-0969-1771-a007650e9564.png" width="320"/><br/>

1. グラフが表示されるので、枠を調整すれば完成です

重いBIツールをいちいち立ち上げなくても、notionでサクッと現在の状況を把握できる体験が無料でできちゃいます。（もちろんnotionは別途契約必要ですけど）


# おまけ2、スプシのグラフを定期的にSlackにプッシュしたい

これもGAS（Google App Script）を使うことで無料で実行できます。

以下のコードをグラフを出力してるスプレッドシートに書き込んで、スケジュール実行をセットしましょう。（もちろんSlackは別途契約が必要です）

## SlackBotの準備
1. Slackの[YourAppsページ](https://api.slack.com/apps/)で、CreateNewAppボタンを押す
1. From Scratchを選択して「ボット名」と「インストールするワークスペース」を指定、CreateAppボタンを押す
1. Basic InfomationでBotsを選択、Review Scopes to Addボタンを押す
1. Scopesの項目にあるBot Token ScopesのAdd an OAuth Scopeボタンを押す
1. 「chat:wrtite」（チャット書き込み権限）と「files.write」（ファイル書き込み権限）をセット
1. OAuth Tokens for Your Workspaceの項目にある、Install to workspaseボタンを押す
1. Bot User OAuth Tokenの項目が表示されるので、「xoxb-」からはじまるトークンをコピーしておく。あとで必要になります
1. Slackのメッセージを飛ばしたいチャンネルに　/invite @ボット名　コマンドでボットを追加


## SpreadSheet側の準備
1. グラフを作ったスプレッドシートの共有設定をワークスペースのドメイン全体にする
1. 拡張機能＞AppScriptを選択してAppScript画面を表示
1. 以下のコードを参考に設定し、Webアプリでデプロイすると、スクリプトの認証設定の画面が出てくるので、有効化する
1. 実行ボタンをクリックする

```javascript
function uploadImageToSlack() {

//グラフデータの取得と画像ファイル化
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('シート1');　// グラフがあるシート名
  var charts = sheet.getCharts();
  var chartImage = charts[0].getBlob().getAs('image/png').setName('chart.png');
  // ↑　charts[0]は一つ目のグラフという意味

//Slackにアクセスするための設定
  var slackBotToken = 'xoxb-XXXXXXXXXXXXXX'; // 先ほどコピーしたSlackBotのトークンを記入
  var channelId = 'chart-share'; // 画像をポストする先のチャンネルを指定

//Slackに送るペイロードデータのセット
  var formData = {
    token: slackBotToken,
    channels: channelId,
    file: chartImage,
    filename: 'グラフのタイトル',
    initial_comment : 'スプレッドシートのURLなど記載'
  };

//Slackにデータ送信する設定
  var options = {
    method: 'post',
    payload: formData,
    muteHttpExceptions: true
  };

//SlackのファイルアップロードAPI実行
  var response = UrlFetchApp.fetch('https://slack.com/api/files.upload', options);
}
```

実際のスクリプトエディタの画面はこんな感じです。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/948bc5b1-8322-54a0-8dfe-9694444b4753.png" width="720"/><br/>


## 定期実行の設定
スプレッドシートがBQのクエリを定期実行するのは17時にセットしたので、このbotがslackにグラフを毎日投稿するのは18時としましょう。

1. 拡張機能＞AppScriptの左サイドメニューからトリガーを選択
1. トリガーを追加ボタンを押して、以下の設定をして保存ボタンを押せば、セット完了です
    1. 実行する関数：uploadImageToSlack
    1. 実行するデプロイ：head
    1. イベントのソースを選択：時間主導型
    1. 時間ベースのトリガータイプ：日付ベースのタイマー
    1. 時刻を選択：午後6時から7時
    1. エラー通知設定：毎日通知

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/983908af-4a52-5079-ec3f-6749d1c052c4.png" width="480"/><br/>

これで毎日18時に最新のグラフがSlackに流れてくるようになります。

今回構築したものの最終的な全体像はこうなります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44851/59fd8792-e23f-02be-ba4b-c8a8b16c5009.png" width="720"/><br/>


# 終わりに
この環境でもある程度の期間、運用に耐えることができました。

ただ、データ量が増えていくと、スプレッドシートを分割して読み込む運用が発生してしまうので、現在は20万レコード超えるSFDCレポートに限って、Troccoを導入してデータを取り込むスタイルに移行しました。

また、BigQueryをつかったスプレッドシートのダッシュボード運用が浸透していく中で、データ基盤の価値が理解されていき、今ではBigQueryをサンドボックス状態から有料アカウント設定され、ビューで代用していたデータマートをスケジュールドクエリでテーブル化したり、さらに活用が進んでおります。

BigQueryをつかったデータ基盤構築やデータ分析に興味のある方、是非一緒にお仕事しませんか？