非エンジニアがMarketingAutomationを使わずに顧客向けメール配信を完全自動化した話

こんにちは。そしてはじめまして。

ContractS コーポレートサクセス部のぽよさんこと新井です。

僕は職業エンジニアではないのですが、職務の一つとして社内の業務システムの管理者を受け持っており、仕事柄多くのツール（特にSaaS製品）を駆使して業務設計する機会が多く、「あ、組み合わせるとこんな感じの動きができるのね」というネタが出てきます。そんなネタを紹介してみようと思います。

今回出てくるツール
事の発端
出てきた壁、課題
Step1：Salesforceでレポートを準備
Step2：Salesforce to SpreadSheetで自動レポート
Step3：Spreadsheetでのデータ整形（QUERY関数）
Step4：GASでメール一斉配信
Step5：GASのトリガー設定
終わりに

今回出てくるツール

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でのコーディング（メール一斉配信部分）は必要となりますが、比較的難易度が低く汎用性が高いものなので、

元データは当初の希望通りSFレコードを用い、レポートでの抽出条件は相対日付で設定。日付が変わったりSFレコードが更新されれば（多少のラグはあるが）自動的に反映される。
対象の抜き出し、リスト整形はGoogleSpreadsheetで柔軟に実行＆数式を組めばもちろん自動反映。

という形で元データの収集を自動化することができ、かつGASで仕込んだメール一斉配信ツールを定期発火できれば、自動化は万事OK。という算段です。

以降で順を追ってご紹介します！

Step1：Salesforceでレポートを準備

まず、『例えば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からバシャっとデータをエクスポートすることができます。詳細/機能全体の説明は割愛しますが、今回は、

「Report」でSpreadsheetに取り込む
「Auto Refresh」を設定して定期更新する

の2段構えです。

まずReportで、Step1にて作成したレポートを出力してみます。

※出力イメージ

SFレポートの内容が、そのままシートに貼り付けられます。

次に、RefreshからAuto Refreshを選択します。

Auto Refreshは、一度Reportをしていると、そのReportを再度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();
  
  //2行目から最終行までループ処理を行う
  const lastRow = sheet.getLastRow();
  for(let i = 2; i <= lastRow; i++){
 
    //行ごとに1列目を取得
    let account_name = sheet.getRange(i, 1).getValue();
    //行ごとに2列目を取得
    let renewal_date = sheet.getRange(i, 2).getValue();
     renewal_date = Utilities.formatDate(renewal_date, "JST", "yyyy/MM/dd");
    //行ごとに3列目を取得
    let to = sheet.getRange(i, 3).getValue(); 

  //メールのテンプレートがあるシートをアクティブにして内容を取得
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName("★メールテンプレート★")); 
  const templateSheet = SpreadsheetApp.getActiveSheet();

    //B1セルはメールの件名として取得
    const subject = templateSheet.getRange(1, 2).getValue();

    //行ごとにB2セルのメール本文を取得して文章内の{取引先名}をそれぞれ上で定義した変数で置換
    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();

    //取得した内容をGmailで送信
    GmailApp.sendEmail(to,subject,message,options);
  }
}