Automation · CRM · Apps Script

Retail CRM: Automated Clientelling Email System

A time-triggered email automation system for luxury retail clientelling: dempotent multi-interval send cadence, associate-level permission controls, and centralized copy management, built in Google Apps Script.

Overview

This system automated associate-level clientelling reminders and client emails for a retail operation, replacing ad-hoc manual outreach with a structured, time-triggered email cadence keyed to each client’s last purchase date. The goal was to ensure no client relationship went cold through oversight and to do it in a way that required no ongoing manual effort from associates beyond maintaining their client sheets. The system ran on Google Sheets and Google Apps Script, but the two roles in the system (associate and GM) ran genuinely different scripts against genuinely different workbooks.

Architecture

Two Separate Systems, Not One Shared Script

Associate level. Each associate had one locked, permission-restricted sheet containing only their own client roster, visible to them and the GM. Their script was hardcoded to that one sheet and their own row on the Email Template (no sheet name parameter, no loop, because an associate’s script only ever needed to know about itself). It ran on a time-based trigger and sent from whichever Google account was active at the time, which was always the associate’s own account, since the script lived inside their own sheet.

GM level. The GM’s workbook was a separate, larger structure that ingested a copy of every associate’s client data into a same-named worksheet inside the GM’s own workbook (an “Associate 1” tab in the GM’s workbook, distinct from Associate 1’s own locked sheet elsewhere). Alongside those per-associate copies, the GM’s workbook held an “All Clients” rollup combining every associate’s data, a “GM Clients” sheet for the GM’s own direct clients, and two separate template sheets (one holding one-off custom messages keyed per associate, and one holding the GM’s own default cadence copy). The GM’s workbook contained eight distinct functions, not six copies of one function:

Six per-associate functions, run manually by the GM on demand. Each one fires the congratulations-trigger mechanism (not the day-since-purchase cadence) against that associate’s one-off custom message template, for outreach the GM wanted to send on a specific associate’s behalf at a specific moment (a graduation, a birthday, anything outside the standard cadence).
One all-associates fallback function, run manually, which executes the full day-since-purchase cadence across every associate’s sheet inside the GM’s workbook using the GM’s own default template. This existed only as a failsafe for the rare case where an associate’s own timed trigger failed to fire (it was never actually triggered during the system’s use).
One GM-clients function, time-triggered like the associate-level scripts, running the full cadence against the GM’s own direct clients using the GM’s default template.

The GM could send any of these from either their own Google account or a shared store account, since both had identical edit access to the workbook and its scripts.

Sheet Structure

Each associate’s own sheet tracked contact information, purchase history, days-since-last-purchase, and a series of sent-flag columns (one per email interval) to ensure idempotent sends regardless of how frequently the trigger fired. The associate-level Email Template sheet held outreach copy across seven columns: email address, initial follow-up body, 3-week body, 3-month body, 6-month body, 1-year body, and a congratulations message triggered by a separate condition. Each associate mapped to one row of this template, allowing copy to be updated centrally without touching the script.

The GM’s workbook used two separate templates instead of one: an Associate Template holding the one-off custom congratulations copy the GM would send on an associate’s behalf, and a GM Template holding the GM’s own default cadence copy used by the two GM-side cadence functions.

Email Cadence

Used by every time-triggered or fallback cadence function (the associate scripts and GM functions 7 and 8):

≤ 7 days since purchase → initial follow-up
21 days → 3-week check-in
91 days → 3-month re-engagement
182 days → 6-month re-engagement
365 days → 1-year anniversary
Congratulations trigger → fires independently of the cadence above, whenever column G of the relevant template row is populated; could reference events such as graduation, new job, birthday, anniversary, etc.

The Scripts

Two separate files reflecting the two separate execution contexts described above.

Associate-Level Script

Installed directly into each associate’s own locked sheet. Hardcoded to that associate’s sheet and template row — no parameters, no loop, because it never needed to know about anyone else.

/**
* Retail CRM — Associate Clientelling Email System
* Google Apps Script — runs on each associate's own locked sheet
*
* Each associate has their own copy of this script, installed directly in
* their locked client-tracking sheet (not shared, not parameterized — the
* sheet name and template row are hardcoded per associate since each
* associate only ever needs their own data).
*
* Runs on a time-based trigger. Sends from whichever Google account is
* active when the trigger fires — the associate's own account, since this
* script lives in their own sheet.
*
* Email cadence (days since last purchase):
*   <=7 days  -> initial follow-up
*   21 days   -> 3-week check-in
*   91 days   -> 3-month re-engagement
*   182 days  -> 6-month re-engagement
*   365 days  -> 1-year anniversary
*   Special   -> congratulations trigger (column G of this associate's
*                template row; fires independent of the cadence above)
*
* To adapt this script for a different associate: change TEMPLATE_ROW
* below to that associate's row on the Email Template sheet. The client
* sheet itself is always the active sheet, so no sheet name is needed.
*/

var START_ROW = 6;
var TEMPLATE_ROW = 3;   // this associate's row on the Email Template sheet

function sendClientelingEmails() {
var ss = SpreadsheetApp.getActive();
var clientSheet = ss.getSheetByName('My Clients');
var templateSheet = ss.getSheetByName('Email Template');

var lastRow = clientSheet.getLastRow();
if (lastRow < START_ROW) return;   // no client data

var numRows = lastRow - START_ROW + 1;

// Column H: days-since-last-purchase countdown value
var reminderValues = clientSheet.getRange(START_ROW, 8, numRows, 1).getValues();

// Sent-flag column references
var sentCols = {
  initial:    clientSheet.getRange(START_ROW, 9,  numRows, 1),
  threeWeek:  clientSheet.getRange(START_ROW, 11, numRows, 1),
  threeMonth: clientSheet.getRange(START_ROW, 13, numRows, 1),
  sixMonth:   clientSheet.getRange(START_ROW, 15, numRows, 1),
  oneYear:    clientSheet.getRange(START_ROW, 17, numRows, 1)
};

// This associate's template row — columns:
// [email, initial, 3-week, 3-month, 6-month, 1-year, congrats]
var templateData = templateSheet.getRange(TEMPLATE_ROW, 1, 1, 7).getValues()[0];
var emailAddress = templateData[0];
var templates = {
  initial:    templateData[1],
  threeWeek:  templateData[2],
  threeMonth: templateData[3],
  sixMonth:   templateData[4],
  oneYear:    templateData[5],
  congrats:   templateData[6]
};

if (!emailAddress) return;   // no email configured

for (var i = 0; i < numRows; i++) {
  var remind = reminderValues[i][0];
  var rowSent = {
    initial:    sentCols.initial.getCell(i + 1, 1).isBlank(),
    threeWeek:  sentCols.threeWeek.getCell(i + 1, 1).isBlank(),
    threeMonth: sentCols.threeMonth.getCell(i + 1, 1).isBlank(),
    sixMonth:   sentCols.sixMonth.getCell(i + 1, 1).isBlank(),
    oneYear:    sentCols.oneYear.getCell(i + 1, 1).isBlank()
  };

  if (remind <= 7 && rowSent.initial) {
    sendAndMark(emailAddress, templates.initial, clientSheet, START_ROW + i, 9);
  } else if (remind == 21 && rowSent.threeWeek) {
    sendAndMark(emailAddress, templates.threeWeek, clientSheet, START_ROW + i, 11);
  } else if (remind == 91 && rowSent.threeMonth) {
    sendAndMark(emailAddress, templates.threeMonth, clientSheet, START_ROW + i, 13);
  } else if (remind == 182 && rowSent.sixMonth) {
    sendAndMark(emailAddress, templates.sixMonth, clientSheet, START_ROW + i, 15);
  } else if (remind == 365 && rowSent.oneYear) {
    sendAndMark(emailAddress, templates.oneYear, clientSheet, START_ROW + i, 17);
  }
}

// Congratulations trigger — fires when column G of this associate's
// template row is populated, independent of cadence above
var congratsCell = templateSheet.getRange(TEMPLATE_ROW, 7);
if (!congratsCell.isBlank()) {
  sendAndMark(emailAddress, templates.congrats, clientSheet, START_ROW, 8);
}
}

/**
* Sends a clienteling email and marks the sent-flag cell for that row.
* Flushes immediately to protect against script interruption.
*/
function sendAndMark(emailAddress, body, sheet, row, flagCol) {
MailApp.sendEmail(emailAddress, 'Clienteling Reminder', body);
sheet.getRange(row, flagCol, 1, 1).setValue('EMAIL_SENT');
SpreadsheetApp.flush();
}

GM-Level Script

Runs from the GM’s rolled-up workbook. Eight distinct functions: six manual per-associate congratulations sends, one manual all-associates cadence fallback, and one time-triggered cadence for the GM’s own clients.

/**
* Retail CRM — GM Clienteling Email System
* Google Apps Script — runs from the GM's rolled-up workbook
*
* The GM's workbook ingests each associate's client data into a
* same-named worksheet (e.g. "Associate 1") inside this workbook, plus:
*   - "All Clients"      combined rollup of every associate's clients
*   - "GM Clients"       the GM's own direct clients
*   - "Associate Template"  one-off custom message copy per associate
*   - "GM Template"         the GM's own default cadence copy
*
* Sends from whichever Google account is active when a function is run:
* either the GM's own account or the shared store account, since both
* have identical edit access to this workbook and its scripts.
*
* Eight functions in total:
*   1-6. sendAssociateCongrats_<n>()   — one per associate, manual.
*        Uses the congratulations trigger (not the day-since-purchase
*        cadence) against the Associate Template sheet, for one-off
*        custom outreach the GM sends on behalf of a specific associate.
*   7.   sendAllAssociatesCadence()    — manual fallback.
*        Runs the full day-since-purchase cadence across every associate
*        sheet using the GM Template's default copy. Exists for cases
*        where an associate's own timed trigger failed to fire.
*        KNOWN LIMITATION: this writes sent-flags only to the GM's local
*        copy of the associate's sheet inside this workbook, not back to
*        the associate's own locked sheet. If this fallback ever fired, 
*        the GM would need to manually relay which flags were set so the
*        associate could mark the same cells on their own sheet, preventing
*        a future duplicate send once their trigger resumed. This path was
*        never exercised in practice.
*   8.   sendGMClientsCadence()        — time-triggered.
*        Runs the full cadence against the GM's own client sheet using
*        the GM Template's default copy.
*
* Email cadence (days since last purchase), used by functions 7 and 8:
*   <=7 days  -> initial follow-up
*   21 days   -> 3-week check-in
*   91 days   -> 3-month re-engagement
*   182 days  -> 6-month re-engagement
*   365 days  -> 1-year anniversary
*/

var START_ROW = 6;
var ASSOCIATE_TEMPLATE_START_ROW = 3;   // row 3 = Associate 1, row 4 = Associate 2, etc.
var GM_TEMPLATE_ROW = 3;

var ASSOCIATE_SHEETS = [
'Associate 1',
'Associate 2',
'Associate 3',
'Associate 4',
'Associate 5',
'Associate 6'
];

// ── Functions 1-6: per-associate congratulations send (manual, one-off) ──

function sendAssociateCongrats_1() { sendAssociateCongrats(0); }
function sendAssociateCongrats_2() { sendAssociateCongrats(1); }
function sendAssociateCongrats_3() { sendAssociateCongrats(2); }
function sendAssociateCongrats_4() { sendAssociateCongrats(3); }
function sendAssociateCongrats_5() { sendAssociateCongrats(4); }
function sendAssociateCongrats_6() { sendAssociateCongrats(5); }

/**
* Sends the one-off custom congratulations message for one associate's
* clients, using the Associate Template sheet (not the GM default
* template). Triggered manually by the GM for a specific associate at a
* specific moment — not on the day-since-purchase cadence.
*/
function sendAssociateCongrats(associateIndex) {
var ss = SpreadsheetApp.getActive();
var assocTemplateSheet = ss.getSheetByName('Associate Template');
var assocSheet = ss.getSheetByName(ASSOCIATE_SHEETS[associateIndex]);
if (!assocSheet) return;

var lastRow = assocSheet.getLastRow();
if (lastRow < START_ROW) return;

var templateRow = ASSOCIATE_TEMPLATE_START_ROW + associateIndex;
var templateData = assocTemplateSheet.getRange(templateRow, 1, 1, 7).getValues()[0];
var emailAddress = templateData[0];
var congratsBody = templateData[6];

if (!emailAddress) return;

var congratsCell = assocTemplateSheet.getRange(templateRow, 7);
if (!congratsCell.isBlank()) {
  sendAndMark(emailAddress, congratsBody, assocSheet, START_ROW, 8);
}
}

// ── Function 7: all-associates combined cadence (manual fallback) ────────

/**
* Runs the full day-since-purchase cadence across every associate sheet
* in this workbook, using the GM Template's default copy. Manual fallback
* for when an associate's own timed trigger failed to launch.
*
* Writes sent-flags to the GM's local copy of the associate's sheet only —
* there was no mechanism to write back to the associate's own locked sheet.
* Recovering from a fallback send required the GM to manually relay the
* flagged cells to the associate so they could mark them on their own
* sheet and avoid a duplicate send once their trigger resumed.
*/
function sendAllAssociatesCadence() {
var ss = SpreadsheetApp.getActive();
var gmTemplateSheet = ss.getSheetByName('GM Template');

for (var a = 0; a < ASSOCIATE_SHEETS.length; a++) {
  var assocSheet = ss.getSheetByName(ASSOCIATE_SHEETS[a]);
  if (!assocSheet) continue;

  runCadence(assocSheet, gmTemplateSheet, GM_TEMPLATE_ROW);
}
}

// ── Function 8: GM's own clients cadence (time-triggered) ────────────────

/**
* Runs the full day-since-purchase cadence against the GM's own client
* sheet, using the GM Template's default copy. Runs on a time-based
* trigger, same as each associate's own script.
*/
function sendGMClientsCadence() {
var ss = SpreadsheetApp.getActive();
var gmClientSheet = ss.getSheetByName('GM Clients');
var gmTemplateSheet = ss.getSheetByName('GM Template');

runCadence(gmClientSheet, gmTemplateSheet, GM_TEMPLATE_ROW);
}

// ── Shared cadence logic (used by functions 7 and 8) ──────────────────────

/**
* Evaluates one client sheet against the day-since-purchase cadence and
* sends/marks any due emails, using the given template sheet and row.
*/
function runCadence(clientSheet, templateSheet, templateRow) {
var lastRow = clientSheet.getLastRow();
if (lastRow < START_ROW) return;

var numRows = lastRow - START_ROW + 1;
var reminderValues = clientSheet.getRange(START_ROW, 8, numRows, 1).getValues();

var sentCols = {
  initial:    clientSheet.getRange(START_ROW, 9,  numRows, 1),
  threeWeek:  clientSheet.getRange(START_ROW, 11, numRows, 1),
  threeMonth: clientSheet.getRange(START_ROW, 13, numRows, 1),
  sixMonth:   clientSheet.getRange(START_ROW, 15, numRows, 1),
  oneYear:    clientSheet.getRange(START_ROW, 17, numRows, 1)
};

var templateData = templateSheet.getRange(templateRow, 1, 1, 7).getValues()[0];
var emailAddress = templateData[0];
var templates = {
  initial:    templateData[1],
  threeWeek:  templateData[2],
  threeMonth: templateData[3],
  sixMonth:   templateData[4],
  oneYear:    templateData[5],
  congrats:   templateData[6]
};

if (!emailAddress) return;

for (var i = 0; i < numRows; i++) {
  var remind = reminderValues[i][0];
  var rowSent = {
    initial:    sentCols.initial.getCell(i + 1, 1).isBlank(),
    threeWeek:  sentCols.threeWeek.getCell(i + 1, 1).isBlank(),
    threeMonth: sentCols.threeMonth.getCell(i + 1, 1).isBlank(),
    sixMonth:   sentCols.sixMonth.getCell(i + 1, 1).isBlank(),
    oneYear:    sentCols.oneYear.getCell(i + 1, 1).isBlank()
  };

  if (remind <= 7 && rowSent.initial) {
    sendAndMark(emailAddress, templates.initial, clientSheet, START_ROW + i, 9);
  } else if (remind == 21 && rowSent.threeWeek) {
    sendAndMark(emailAddress, templates.threeWeek, clientSheet, START_ROW + i, 11);
  } else if (remind == 91 && rowSent.threeMonth) {
    sendAndMark(emailAddress, templates.threeMonth, clientSheet, START_ROW + i, 13);
  } else if (remind == 182 && rowSent.sixMonth) {
    sendAndMark(emailAddress, templates.sixMonth, clientSheet, START_ROW + i, 15);
  } else if (remind == 365 && rowSent.oneYear) {
    sendAndMark(emailAddress, templates.oneYear, clientSheet, START_ROW + i, 17);
  }
}
}

/**
* Sends a clienteling email and marks the sent-flag cell for that row.
* Flushes immediately to protect against script interruption.
*/
function sendAndMark(emailAddress, body, sheet, row, flagCol) {
MailApp.sendEmail(emailAddress, 'Clienteling Reminder', body);
sheet.getRange(row, flagCol, 1, 1).setValue('EMAIL_SENT');
SpreadsheetApp.flush();
}

Implementation Notes

Idempotency

The sent-flag columns (EMAIL_SENT written to the relevant column on send) prevent duplicate emails if a trigger fires multiple times within the same interval window, or if a manual function is run more than once. Each row’s flag is checked individually — a flag set for one client does not block sends for any other client in the same sheet. SpreadsheetApp.flush() is called immediately after each write to ensure the flag persists even if the script is interrupted mid-run.

Template Separation

Email copy lives in template sheets, not in the scripts themselves. This means outreach messaging can be updated by the GM or an associate without touching the code — a deliberate design choice that keeps content management accessible to non-technical users. The GM’s workbook carries this further by separating the one-off custom message copy (Associate Template) from the GM’s own default cadence copy (GM Template), since the two were used for genuinely different kinds of outreach.

Permission Model

The associate-level, per-sheet architecture enforced data separation at the access control level: associates could not see each other’s client lists, could not access the GM’s workbook, and had no visibility into any rollup. The GM held the only credential set with cross-sheet, cross-workbook access — and that access could come from either the GM’s own account or a shared store account, both carrying identical permissions. This was a business requirement, not a technical limitation, and it’s why the associate and GM systems were built as genuinely separate scripts rather than one shared script running in different modes.