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

Last updated at 2023-12-17Posted at 2023-12-16

はじめに

本記事は、AlphaDriveアドベントカレンダー　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謹製のコネクターという点はいいなと思いました。

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

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

これで使う準備が完了しました。

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

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

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

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

一度に読み込むレコード数が20万レコードを超えてくると、読み込みエラーが発生してしまいます
- 対処法としては、SFDC側のレポートを分割して、20万超えないようにフィルターをかけて、それぞれスプレッドシートに読み込むようにする、という方法です
SFDCレポート側でグループ設定している場合はGroup results if possibleにチェックを入れると読み込めるようになりました。してないとエラーになります
日次スケジューラは時間指定ができないのですが、固定で毎日16時に発火しています

BigQueryサンドボックス（無料）環境を作る

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

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

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

Bigqueryのサービス紹介サイトから「コンソールへ移動」ボタンを押して、利用許諾にチェック入れてOK
プロジェクトを選択＞新しいプロジェクト　を選んで、プロジェクトを作成してください。ここでは「組織なし」配下に作りましたが、あなたが所属してる会社組織がある場合は、そちらの組織を選択してその配下にプロジェクトを作ってください
プロジェクト作成できたら利用開始できます。他の人を招待する場合はIAMでBigQuery利用権限を付与してください

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

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

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

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

サンドボックス状態の機能制限は

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

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

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

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

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

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

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

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

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

プロジェクト名の右にある三点リーダをクリックし、データセットを作るを選択
データセット設定画面に以下のように設定して、データセットをつくる

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

データセット名の横の三点リーダをクリックして、テーブルを作成
Googleドライブを指定
スプレッドシートのURLと読み込み範囲を指定（読み込み範囲は指定しなくてもいい、最初のシートのデータを自動で取得してくれるので）
カラム名を指定、データ型を指定する。自動読み込みでも読み込めるが、細かい設定ができず読み込みエラーの原因になったりするので、自分は自動読み込みしないようにしています
詳細オプションを開いて、スキップするヘッダー行に「1」を入力
テーブルを作成ボタンを押す

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

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

こういうフローが出来たことになります。BQは直接スプシを見に行っています。

注意点

以前はカラム名が英語じゃないとエラーになってしまっていたが、今は日本語でもOKになりました
日付のデータはスラッシュで区切るのではなくハイフンで区切りじゃないとBQは読み込まないので、スプレッドシート側で日付データを選択し、「表示形式＞数字からハイフン区切りの日付フォーマットを選択してください
一度外部テーブル設定したら、あとから設定を変えられない項目が多い（カラム名とか）ので、一度テーブルを削除して作り直す必要がある。テーブル詳細タブ＞詳細を編集から編集できる項目はこれしかない
スプレッドシートの共有情報と外部テーブルのアクセス情報は一致するので、外部テーブル指定したBQのテーブルはスプレッドシートの共有者のみに公開されます

読み込んだSFDCのデータを変形・カスタマイズする

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

データマート用のデータセットを作成
今までの手順で作った外部テーブルのデータを変形するクエリを書く
保存＞ビューを保存を選択
ビューの名前など入力して保存ボタンを押す

作ったデータマートテーブルをスプレッドシート側からコネクトする

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

BQ側でクエリを実行
データを探索から「シートを使って調べる」を選択。これでスプレッドシートに先ほどのクエリの結果が接続されます

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

BQとつながった画面で、更新オプションを表示
右サイドバーでスケジュールが設定できます

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

最後の仕上げ

あとはピボットテーブルのデータを加工して、グラフを作成すればスプシのダッシュボードのできあがり！ここまで無料です！
※以下のグラフはダミーのデータです。

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

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

つくったグラフの三点リーダメニューから共有を選択し、社内のメンバーに共有すると、URLが生成されます
URLをコピーして、notionのグラフを表示したい場所に移動し、「/埋め込み」と書くと、URLを指定するボックスが表示されるので、URLを貼り付けます
グラフが表示されるので、枠を調整すれば完成です

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

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

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

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

SlackBotの準備

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

SpreadSheet側の準備

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

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);
}

実際のスクリプトエディタの画面はこんな感じです。

定期実行の設定

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

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

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

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

終わりに

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

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

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

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up