こんにちは。そしてはじめまして。
ContractS コーポレートサクセス部のぽよさん こと新井です。
僕は職業エンジニアではないのですが、職務の一つとして社内の業務システムの管理者を受け持っており、仕事柄多くのツール（特にSaaS 製品）を駆使して業務設計する機会が多く、「あ、組み合わせるとこんな感じの動きができるのね」というネタが出てきます。
そんなネタを紹介してみようと思います。
今回出てくるツール
Salesforce ・・・CRM /顧客管理ツール。以下SF。
Pardot・・・MarketingAutomation（MA）ツール。セールスフォース社のMAでSFと連携/連動している。（※現：Account Engagemant）
GoogleAppsScript・・・GWSにも含まれる、Google のプログラミングツール。以下GAS。
事の発端
当社はContractS CLMというSaaS サービスを提供していまして、お客様のサービス利用契約はサブスクリプション モデル/期間契約となっています。
当社のカスタマーサクセスチームより、「お客様に『あなたの会社の契約更新日はmm月dd日ですよ』のようなメール通知を特定のタイミングでお送りしたい。」とのリクエス トをもらいまして、早速話を聞いてみました。
会話する中で決まった要件が下記なのですが、、、
1. 一斉配信メールではあるが、顧客の利用代表者様に適切にメールを届けたい。営業目的ではないため、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つ組み合わせることで、データの絞り込みを効かせる方法です。
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は設定せず、一度Reportを実行したあとに見出し以外のデータレコードをダミーに書き替えることをおすすめします。
Step3：Spreadsheetでのデータ整形（QUERY関数）
元データを抽出できるようになったら、データを加工するところはGoogle先生 に託します。
=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ヶ月間に契約更新日が来る』レコードだけを更に抽出するようにします。
Step4：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 アカウントから送信する仕様ですが、Gmail に返ってくる数が増えてきます。
「自動でメール送ったはいいけどFailureで返ってくる数多いなあ。1件ずつメーラー でアドレス確認して確認依頼を社内に回すの面倒だなぁ」と怠惰な気持ちが芽生えてくるのですが、長くなってしまうのでこのあたりはまた別の機会に、、、。
また、SFのAutoRefreshも、実はこのままだとFail発生時に気付くことができないので、こちらもまた別の機会に、、、。
お読みいただきありがとうございました。もう1本、他のメンバーの記事を見ていただけるととても嬉しいです！
