こんにちは。そしてはじめまして。
ContractS コーポレートサクセス部のぽよさんこと新井です。
僕は職業エンジニアではないのですが、職務の一つとして社内の業務システムの管理者を受け持っており、仕事柄多くのツール（特にSaaS製品）を駆使して業務設計する機会が多く、「あ、組み合わせるとこんな感じの動きができるのね」というネタが出てきます。
そんなネタを紹介してみようと思います。
今回出てくるツール
Salesforce・・・CRM/顧客管理ツール。以下SF。
Pardot・・・MarketingAutomation（MA）ツール。セールスフォース社のMAでSFと連携/連動している。（※現：Account Engagemant）
GoogleAppsScript・・・GWSにも含まれる、Googleのプログラミングツール。以下GAS。
事の発端
当社はContractS CLMというSaaSサービスを提供していまして、お客様のサービス利用契約はサブスクリプションモデル/期間契約となっています。
当社のカスタマーサクセスチームより、「お客様に『あなたの会社の契約更新日はmm月dd日ですよ』のようなメール通知を特定のタイミングでお送りしたい。」とのリクエストをもらいまして、早速話を聞いてみました。
会話する中で決まった要件が下記なのですが、、、
1. 一斉配信メールではあるが、顧客の利用代表者様に適切にメールを届けたい。営業目的ではないため、MAで有している宛先ごとのOptOut設定を問わず送信したい。
（※OptOut設定・・・メールアドレスの持ち主が、MA等を用いて送信されるメルマガ/営業系メールなどを送られるのを止める設定のこと。メール配信停止、などと表記することが多い。 なおこのケースでは、お客様の契約に係る重要な要件となるため、営業系メールと異なりOptOutに関わらずメールは送信したい。）
2. メール配信タイミングは毎月月初で定期実行。月1回のみでよい。
3. 配信元データはすべてSFの中にある情報を用いて条件設定を行う。元データの所在がわかりやすく、かつ条件式は設計者以外も認識しやすいレベルに噛み砕いたものを希望したい。
4. 対象顧客の抽出条件は契約更新日を用いる。例えば1月月初のメールでは、翌翌々月中（=4月中）が契約更新日となっているお客様を宛先としたい。
5. 業務負荷やヒューマンエラーを回避するために理想は完全な自動化。難しいとしても、手動での毎回の抽出条件変更、宛先リストアップ、メールへの記入は極力避けたい。最初に設定した条件を毎月変更せずに使い続けたい。
（※ちなみにContractS CLMのサービス利用契約は比較的柔軟に組み合わせられるようにした都合、サービスプロダクト側にユーザの利用契約に関する情報は入れない仕様になっています。）
これらを受け、検討を始めます。
出てきた壁、課題
PardotでOptOut設定の如何に関わらずメール送信すること自体は可能なのですが、上記4の抽出条件を満たす方法があるか、という点が出発点となりました。
SF（Pardot）のレポート抽出は比較的柔軟性が高いので、「相対日付」の条件を用いることを検討したのですが、「翌翌々月」という相対日付がSFでは設定できません。
次に、「翌翌々月」を「（本日から見て）90日後~120日後」と読み替えて相対日付とする案を考えました。ただこれはメール送信する月日によっては月初日と月末日をピッタリ捉えることができないことに気が付き、断念しました、、、。
結局、SF＆Pardotで対象顧客を抽出を完結することは難しそうだな、という途中経過に至ります。
ここでこの課題をクリアすべく編み出した方法が、今回ご紹介する内容です。
この方法であれば、一部GASでのコーディング（メール一斉配信部分）は必要となりますが、比較的難易度が低く汎用性が高いものなので、
という形で元データの収集を自動化することができ、かつGASで仕込んだメール一斉配信ツールを定期発火できれば、自動化は万事OK。という算段です。
以降で順を追ってご紹介します！
まず、『例えば1月には、翌翌々月（=4月）に契約更新日（今の契約期間の最終日）がくるお客様を宛先としたい。』という難儀な条件を含むSFレポートを作成します。
前述の通り、日付ドンピシャの検索条件は作れないため、下記条件の組み合わせで少し広めに設定します。
① 一致しない = 翌70日間
AND
② 一致する = 翌130日間
日付条件の組み合わせ
この組み合わせで、「常に今日より71日先から130日先までの日付を持ったレコードを抽出する」という条件が組めました。
相対日付を2つ組み合わせることで、データの絞り込みを効かせる方法です。
実際には、1月頭であれば、ざっくり3月半ば〜5月上旬の日付を抽出する形ですね。
Step2：Salesforce to SpreadSheetで自動レポート
次に、SFのデータをSpreadsheetにサクッと展開する方法を設定します。
SFのデータを出力、といえば王道は「レポートのエクスポート（.csv/.xlsx）」もしくは「データローダ（.csv）」かと思いますが、今回はSpreadsheetでSFのデータを扱いたいので、
Salesforce Connector を利用します。
↓この機能です
support.google.com
この機能を有効にしておけば、GoogleSpreadsheetにSalesforceからバシャっとデータをエクスポートすることができます。
詳細/機能全体の説明は割愛しますが、今回は、
の2段構えです。
まずReportで、Step1にて作成したレポートを出力してみます。
Report
※出力イメージ
Spreadsheetでの出力イメージ
SFレポートの内容が、そのままシートに貼り付けられます。
次に、RefreshからAuto Refreshを選択します。
Auto Refreshは、一度Reportをしていると、そのReportを再度Refreshすることができる機能です。
Refresh
これによって、最短4時間ごと更新ですが、「常に最新のSFレポートをSpreadsheetに出力する」を実現しています。
ちなみにですが、この工程はレポートが2つ以上あっても有効です。
（それぞれ別のシートタブにレポートを出力→Auto Refresh設定1つで全レポートを定期更新できる）
検証中は、Auto Refreshは設定せず、一度Reportを実行したあとに見出し以外のデータレコードをダミーに書き替えることをおすすめします。
Step3：Spreadsheetでのデータ整形（QUERY関数）
元データを抽出できるようになったら、データを加工するところはGoogle先生に託します。
Spreadsheetでデータの抽出といえばQUERY関数が優秀です。
=QUERY('★元データのシート名★'!A:C,"select* where B>date'"&TEXT(eomonth(today(),+2),"YYYY-MM-DD")&"' and B<=date'"&TEXT(eomonth(today(),+3),"YYYY-MM-DD")&"'")
という形で、常に『翌翌々月の1ヶ月間に契約更新日が来る』レコードだけを更に抽出するようにします。
Step2でQUERY抽出したシートを、送信リストとして用います。
Step4：GASでメール一斉配信
続いてメール一斉送信です。
参考までに、私が利用しているスクリプトをサンプルで貼っておきます。
動作はSpreadsheet上ですので、コンテナバインドのGASエディターを使うのが良いかと思います。
function sendMail(){
const spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName("★送信先リスト★"));
const sheet = SpreadsheetApp.getActiveSheet();
const lastRow = sheet.getLastRow();
for(let i = 2; i <= lastRow; i++){
let account_name = sheet.getRange(i, 1).getValue();
let renewal_date = sheet.getRange(i, 2).getValue();
renewal_date = Utilities.formatDate(renewal_date, "JST", "yyyy/MM/dd");
let to = sheet.getRange(i, 3).getValue();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName("★メールテンプレート★"));
const templateSheet = SpreadsheetApp.getActiveSheet();
const subject = templateSheet.getRange(1, 2).getValue();
const message = templateSheet.getRange(5, 2).getValue()
.replace('{取引先名}',account_name)
.replace('{更新予定日}',renewal_date)
let options = {
from: "",
name: "",
cc: ""
};
options.from = templateSheet.getRange(2, 2).getValue();
options.name = templateSheet.getRange(3, 2).getValue();
options.cc = templateSheet.getRange(4, 2).getValue();
GmailApp.sendEmail(to,subject,message,options);
}
}
メールテンプレートのシートはこのような形ですね。
メールテンプレートのシートイメージ
ここまで完了すると、「（SFから自動転記をした上で）対象者を更に絞り、Spreadsheetの内容をもとにメールを一斉配信する」が行えるようになります。
Step5：GASのトリガー設定
最後にGASのトリガー設定を行います。
私はトリガー設定を、下記のように設定しています。
このあたりは、他のメール一斉配信のタイミングなどを鑑みて適宜調整いただくのがいいですね。
GASトリガー
終わりに
本件は、メール一斉配信のGAS以外はノーコードで、比較的簡素に仕上がっています。
GASのコードに関しても、汎用性高めなのと、パラメータ部分を変更するだけでおおよそ変化にも追従できる仕様になっていると思います。
こなれてくると、SF元データが正しい前提にはなりますが、他のメール配信系のアクションでも完全自動化させることが可能です。（弊社も順次、完全自動を増やしています。）
GASでのメール一斉配信は、裏側でGAS設定者のGmailアカウントから送信する仕様ですが、
メールが増えてくると、必然的にMail Send Failureとして返ってくるメールも設定者のGmailに返ってくる数が増えてきます。
「自動でメール送ったはいいけどFailureで返ってくる数多いなあ。1件ずつメーラーでアドレス確認して確認依頼を社内に回すの面倒だなぁ」と怠惰な気持ちが芽生えてくるのですが、長くなってしまうのでこのあたりはまた別の機会に、、、。
また、SFのAutoRefreshも、実はこのままだとFail発生時に気付くことができないので、こちらもまた別の機会に、、、。
お読みいただきありがとうございました。もう1本、他のメンバーの記事を見ていただけるととても嬉しいです！
