Rank email templates by reply rate

Northwind’s client outreach runs on five email templates — different subject lines, different opening pitches — and there is a strong opinion in the studio about which one “works best”. Nobody can prove it. The decision over which template to send rests on a vague memory of which one got a reply last month.

This automation replaces the hunch with a number. Every templated send goes out through a logging helper that records which template was used and the thread it created. A daily job then revisits each logged thread and checks whether the recipient ever wrote back. With the send and the reply both captured, a single Sheets formula gives you a reply rate per template — real evidence for which one to keep using.

What you’ll need

• A Google Sheet with a TemplateSends tab. Row 1 must be a header with the columns sentAt, template, recipient, threadId, replied.
• A Gmail account to send the outreach from — the same account that runs the script, since the reply check compares against the active user’s address.
• Your existing outreach to call sendTrackedTemplate instead of GmailApp.sendEmail directly, so every send is logged.

The send-and-log helper

Use this in place of a raw GmailApp.sendEmail call wherever you send a templated email. It sends the mail, then records the send on the sheet.

// The spreadsheet that holds the send log.
const TEMPLATE_SENDS_SHEET_ID = '1abcTemplateSendsId';

/**
 * Sends a templated email and logs the send to the TemplateSends sheet.
 * The logged row starts with replied = false; reconcileTemplateReplies
 * flips it to true later if the recipient writes back.
 *
 * @param {string} template  A short name identifying which template this is.
 * @param {string} recipient The recipient's email address.
 * @param {string} subject   The email subject line.
 * @param {string} body      The plain-text email body.
 */
function sendTrackedTemplate(template, recipient, subject, body) {
  // 1. Send the email.
  GmailApp.sendEmail(recipient, subject, body);

  // 2. Find the thread the send just created so we can track replies to it.
  const threads = GmailApp.search(
    'to:' + recipient + ' subject:"' + subject + '" newer_than:1d'
  );
  const threadId = threads.length ? threads[0].getId() : '';

  // 3. Append a log row. replied starts false; the daily job updates it.
  SpreadsheetApp.openById(TEMPLATE_SENDS_SHEET_ID)
    .getSheets()[0]
    .appendRow([new Date(), template, recipient, threadId, false]);
}

The reply-checker (runs daily)

This revisits every logged send that has not yet been marked as replied and checks the thread for an inbound message.

/**
 * Walks the TemplateSends log and marks any send whose thread has
 * received a reply from someone other than us. Runs daily.
 */
function reconcileTemplateReplies() {
  const sheet = SpreadsheetApp.openById(TEMPLATE_SENDS_SHEET_ID).getSheets()[0];

  // 1. Read the whole log and split the header from the data rows.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;
  if (!rows.length) {
    Logger.log('No template sends logged yet — nothing to reconcile.');
    return;
  }

  // 2. Map header names to column indexes.
  const col = Object.fromEntries(header.map((k, i) => [k, i]));

  // 3. Our own address — a reply is any message NOT from us.
  const me = Session.getActiveUser().getEmail();

  // 4. Check each send that is not already marked replied.
  let updated = 0;
  rows.forEach((row, i) => {
    // Skip rows already resolved or with no thread to check.
    if (row[col.replied] || !row[col.threadId]) return;

    const thread = GmailApp.getThreadById(row[col.threadId]);
    if (!thread) return; // thread deleted or inaccessible

    // A reply is any message in the thread from someone other than us.
    const replied = thread
      .getMessages()
      .some((m) => !m.getFrom().includes(me));

    if (replied) {
      values[i + 1][col.replied] = true;
      updated++;
    }
  });

  // 5. Write the updated log back in one call.
  sheet.getDataRange().setValues(values);
  Logger.log('Marked ' + updated + ' send(s) as replied.');
}

How it works

1. sendTrackedTemplate is a drop-in replacement for GmailApp.sendEmail. It sends the email, then searches Gmail for the thread that send just created — matching on recipient and subject within the last day.
2. It appends a row to TemplateSends recording the timestamp, the template name, the recipient, the thread ID, and replied set to false.
3. reconcileTemplateReplies runs once a day. It reads the whole log, builds a col lookup from the header, and notes the active user’s own address.
4. For each row that is not already marked replied and has a thread ID, it loads the thread and checks whether any message in it came from someone other than us. One inbound message means the recipient replied.
5. When a reply is found, the replied cell is set to true in memory.
6. The whole log is written back in a single setValues call, so a row is only ever flipped from false to true — never re-checked once resolved.

Example run

After a week of outreach and a few daily reconciliations, the TemplateSends sheet looks like this:

sentAt	template	recipient	threadId	replied
2026-05-18	warm-intro	[email protected]	18f…	TRUE
2026-05-18	cold-pitch	[email protected]	18f…	FALSE
2026-05-19	warm-intro	[email protected]	18g…	TRUE
2026-05-19	cold-pitch	[email protected]	18g…	FALSE

Reading the data

Drop this QUERY formula into any empty cell in the spreadsheet to get a live league table of templates by send count and reply count:

=QUERY(TemplateSends!A:E, "select B, count(D), sum(E) where B != '' group by B label count(D) 'sent', sum(E) 'replies'")

sum(E) works because replied holds TRUE/FALSE, which Sheets sums as 1/0. Add a column dividing replies by sent for the reply rate itself — that percentage is the number that settles the argument over which template wins.

Watch out for

• The send-time search can miss the thread. If sendTrackedTemplate runs the search a fraction of a second after sending, Gmail’s index may not have the new thread yet, and threadId is logged blank. Those rows can never be reconciled — if it happens often, search again on the next day’s run instead.
• A duplicate subject confuses the search. If two sends share a recipient and subject within the same day, the search may grab the wrong thread. Keep subjects unique, or include a tracking token.
• “Reply” means any non-self message. An out-of-office auto-reply or a bounce notice counts as a reply here. For outreach that is usually acceptable, but be aware the rate is “got any response”, not “got a human response”.
• setValues rewrites the whole sheet. Keep the QUERY formula and any notes on a separate tab — the reconcile job overwrites every cell in the data range on TemplateSends.
• Reply rate needs volume. With only a handful of sends per template the numbers swing wildly. Wait for a few dozen sends each before reading much into the ranking.
• Threads are checked forever. A row stays in the unreplied pool until someone replies or the thread is deleted. Over time the daily job re-checks a growing backlog — archive or filter out rows older than your realistic reply window.