【顧客管理RPA】formrun経由の顧客情報をSpread Sheetへ自動登録し、顧客ごとにユニークIDを自動採番するシステム構築

株式会社Giving Firstのコンサルティング事例を紹介します。

本プロジェクトでは、某有名YouTuberが運営するオンラインサロンの運営会社さまからのご依頼で、formrunおよびGoogle Spread Sheet上で管理しているものの、販促活動が盛況で、運用管理に苦難されていることから業務効率化支援として、Google Spread Sheetベースの自動化プログラムを構築し、ご支援させていただきました。

当プロジェクトでのご支援概要

formrunで申し込みがあった顧客を手動でSpread Sheet上で登録・管理しており、手作業による工数が肥大化。

formrunでの申込みをトリガーに、ユニークな顧客番号を自動採番したい、初回面談申し込み時のヌケモレを防ぐべく情報の記録を管理したい、流入経路を明示したい（どのLPからの流入なのか判別したい）等を課題に抱えておられました。

プロジェクトのゴール

情報共有の円滑化と業務運営の効率化を目的に、社内ポータルサイトと周辺システムの構築、業務フローを定義することをゴールとして設定し、ご支援致しました。

• LPごと（＝販促会社ごと）に分かれているフォームから登録される顧客をひとつのSpread Sheetに統一すること
• 販促会社が増えるたびに自動（もしくは半自動で）同じ仕組みで横展開できる運用体制を確立すること
• 顧客を判別できる仕組みを作ること（顧客IDを自動採番しSpread Sheetに登録する）

当社からの支援・施策事例

• formrunからの通知メールをトリガーとしたGoogle Spread Sheetへの情報連携プログラム
• formrunでの設定変更（複数フォームをひとつの通知メールに設定）
• 顧客管理簿・案件管理（Google Spread Sheet）の構築

Google Spread Sheetへの自動反映プログラム

弊社で納品したプログラムをサンプルとしてご紹介します。

当該プログラムは、Google App Scriptで実装し、5分程度のポーリング間隔で実行します。

BtoC領域のサービスを展開されている企業様で同じようなお悩みを抱えておられる場合は、弊社のお問合せフォームからご相談ください。

プログラムの処理内容

• formrunからお問い合わせがあった際に受信する「通知メール」をトリガー
• 過去のメールを重複して処理しないように、メールIDをキーに重複排除
• 通知メールから取得した顧客情報を任意のGoogle Spread Sheetに自動転記する
• 初回面談の予約日時をJSTに変換し、Spread Sheet上で記録
• 問い合わせがあった顧客にユニークなIDを自動採番（100000を開始番号）

function extractAndWriteToSheet() {
  // スプレッドシートとシートを指定
  var spreadsheetId = 'XXXXX'; // スプレッドシートのIDを指定
  var sheetName = 'Master'; // シート名を指定
  var logSheetName = 'ProcessedEmails'; // 処理済みメールIDを記録するシート名
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName(sheetName);
  var logSheet = spreadsheet.getSheetByName(logSheetName);
  
  // 処理済みメールIDシートが存在しない場合は作成
  if (!logSheet) {
    logSheet = spreadsheet.insertSheet(logSheetName);
    logSheet.appendRow(['メールID']); // ヘッダー行
  }

  // 処理済みメールIDのリストを取得
  var lastRow = logSheet.getLastRow();
  var processedEmailIds = [];
  if (lastRow > 1) { // ヘッダー行があるため、2行目以降にデータがあるかを確認
    processedEmailIds = logSheet.getRange(2, 1, lastRow - 1, 1).getValues().flat();
  }

  // Gmailのフィルタリング条件
  var threads = GmailApp.search('subject:トリガーとするメールタイトル');
  
  // 各スレッド（メール）の処理
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var message = messages[j];
      var messageId = message.getId();

      // メールが既に処理されているかを確認
      if (processedEmailIds.includes(messageId)) {
        continue; // 既に処理されている場合はスキップ
      }

      var body = message.getPlainBody();
      
      // 正規表現で特定の文字列を抽出
      var teamName = body.match(/チーム名：(.+)/);
      var formName = body.match(/フォーム名：(.+)/);
      var receptionNumber = body.match(/受付番号：(.+)/);
      var responseDate = body.match(/回答日時：(.+)/);
      var email = body.match(/メールアドレス：(.+)/);
      var name = body.match(/名前：(.+)/);
      var phoneNumber = body.match(/電話番号：(.+)/);
      var consultationQuestion = body.match(/個別相談会で特に聞きたいこと：(.+)/);
      var reservationDate = body.match(/予約日程の選択：(.+)/);
      var confirmationStatus = body.match(/日程調整の確定状況：(.+)/);
      var startDate = body.match(/開始日時：(.+)/);
      var endDate = body.match(/終了日時：(.+)/);
      var timeZone = body.match(/タイムゾーン：(.+)/);
      var applicationId = body.match(/申し込みID：(.+)/);
      var reservationDetails = body.match(/予約詳細（※bookrunの管理画面に遷移します）：(.+)/);
      var coordinator = body.match(/担当者：(.+)/);
      var meetingFormat = body.match(/受付完了時ミーティング形式を通知：(.+)/);
      var location = body.match(/場所：(.+)/);
      var offlineMeetingPlace = body.match(/オフラインミーティング場所名：(.+)/);
      var offlineMeetingAddress = body.match(/オフラインミーティング住所：(.+)/);
      var offlineMeetingNotes = body.match(/オフラインミーティング補足：(.+)/);
      var videoMeetingUrl = body.match(/ビデオ会議URL：(.+)/);
      var otherMeetingName = body.match(/その他ミーティング名：(.+)/);
      var otherMeetingUrl = body.match(/その他ミーティングURL：(.+)/);
      var otherMeetingNotes = body.match(/その他ミーティング補足：(.+)/);
      var birthdate = body.match(/生年月日：(.+)/);
      var inquiryPurpose = body.match(/お問い合わせ目的：(.+)/);

      // 日時変換関数
      function convertToJST(dateStr) {
        if (!dateStr) return '';
        var date = new Date(dateStr);
        var jstDate = new Date(date.toLocaleString('en-US', { timeZone: 'Asia/Tokyo' }));
        return Utilities.formatDate(jstDate, 'Asia/Tokyo', 'yyyy-MM-dd HH:mm:ss');
      }

      var startDateJST = convertToJST(startDate ? startDate[1].trim() : '');
      var endDateJST = convertToJST(endDate ? endDate[1].trim() : '');

      // ユニークなIDを生成（既存の最後のID + 1）
      var lastId = 100000;
      if (sheet.getLastRow() > 1) { // シートが空でない場合
        var lastIdRange = sheet.getRange("A2:A" + sheet.getLastRow()).getValues();
        lastIdRange = lastIdRange.map(function(row) { return parseInt(row[0], 10); }).filter(function(id) { return !isNaN(id); });
        if (lastIdRange.length > 0) {
          lastId = Math.max(...lastIdRange);
        }
      }
      var newId = lastId + 1;

      // スプレッドシートに書き込む
      var newRow = [
        newId,
        teamName ? teamName[1].trim() : '',
        formName ? formName[1].trim() : '',
        receptionNumber ? receptionNumber[1].trim() : '',
        responseDate ? responseDate[1].trim() : '',
        email ? email[1].trim() : '',
        name ? name[1].trim() : '',
        phoneNumber ? phoneNumber[1].trim() : '',
        consultationQuestion ? consultationQuestion[1].trim() : '',
        reservationDate ? reservationDate[1].trim() : '',
        confirmationStatus ? confirmationStatus[1].trim() : '',
        startDateJST,
        endDateJST,
        timeZone ? timeZone[1].trim() : '',
        applicationId ? applicationId[1].trim() : '',
        reservationDetails ? reservationDetails[1].trim() : '',
        coordinator ? coordinator[1].trim() : '',
        meetingFormat ? meetingFormat[1].trim() : '',
        location ? location[1].trim() : '',
        offlineMeetingPlace ? offlineMeetingPlace[1].trim() : '',
        offlineMeetingAddress ? offlineMeetingAddress[1].trim() : '',
        offlineMeetingNotes ? offlineMeetingNotes[1].trim() : '',
        videoMeetingUrl ? videoMeetingUrl[1].trim() : '',
        otherMeetingName ? otherMeetingName[1].trim() : '',
        otherMeetingUrl ? otherMeetingUrl[1].trim() : '',
        otherMeetingNotes ? otherMeetingNotes[1].trim() : '',
        birthdate ? birthdate[1].trim() : '',
        inquiryPurpose ? inquiryPurpose[1].trim() : ''
      ];
      
      sheet.appendRow(newRow);

      // 処理済みメールIDを記録
      logSheet.appendRow([messageId]);
    }
  }
}

＼本件と同様のご相談も受け付けております／