Generate and test email subject lines

Northwind’s newsletter goes out every Thursday and the subject line takes longer to argue about than the rest of the email put together. Someone wants punchy, someone else wants friendly, and nobody can remember which style actually got opened last quarter. So you either pick a favourite and hope, or you stall.

This script turns it into a quick, evidence-based choice. It asks Claude for five subject-line variants in different tones, logs them to a sheet, then returns the historical winner — the subject style with the best open rate across past sends. You write the email body once, the script does the arguing, and you ship with a line that has the numbers behind it.

What you’ll need

• A Google Sheet to log variants. Four columns: date, subject, sends, opens (header row in row 1). The script reads and writes this sheet.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• A way to update sends and opens after a campaign — paste them in from Mailchimp, Gmail stats, or your ESP of choice.

The script

// The spreadsheet that holds the subject-line history.
const SUBJECTS_SHEET_ID = '1abcSubjectsId';

// How many tone variants to ask Claude for in one pass.
const DEFAULT_VARIANT_COUNT = 5;

// Column indexes in the log sheet.
const DATE_COL = 0;
const SUBJECT_COL = 1;
const SENDS_COL = 2;
const OPENS_COL = 3;

/**
 * Ask Claude for N subject-line variants in different tones for a given
 * email body. Returns one subject per array entry.
 *
 * @param {string} body - The email body the subject is for.
 * @param {number} [count] - How many variants to generate.
 * @returns {string[]} The list of subject lines, in the order Claude wrote them.
 */
function generateSubjectVariants(body, count = DEFAULT_VARIANT_COUNT) {
  if (!body) throw new Error('generateSubjectVariants needs an email body.');

  // Asking for a fixed tone palette gives diverse options instead of five
  // near-duplicates. One subject per line keeps parsing trivial.
  const prompt =
    'Write ' + count + ' Northwind email subject lines for this body. ' +
    'Vary the tone across these styles, in order: punchy, friendly, ' +
    'curious, urgent, plain. Return ONLY the subject lines — one per line, ' +
    'no numbering, no quotes, no extra text.\n\n' + body;

  return callClaude(prompt, 'claude-sonnet-4-6', 400)
    .split('\n')
    .map((s) => s.trim())
    .filter(Boolean);
}

/**
 * Log the new variants, then return whichever past subject has the best
 * open rate. If there is no history yet, returns the first new variant.
 *
 * @param {string[]} variants - Fresh subject lines from generateSubjectVariants.
 * @returns {string} The recommended subject to send.
 */
function logAndPickWinner(variants) {
  if (!variants || !variants.length) {
    throw new Error('logAndPickWinner needs at least one variant.');
  }

  const sheet = SpreadsheetApp.openById(SUBJECTS_SHEET_ID).getSheets()[0];
  const past = sheet.getDataRange().getValues().slice(1);

  // 1. Append the new variants with zero sends/opens — you fill those in
  //    after the campaign goes out.
  const now = new Date();
  variants.forEach((v) => sheet.appendRow([now, v, 0, 0]));

  // 2. Score history by open rate (opens / sends), guarding divide-by-zero.
  //    Rows with no sends sort to the bottom.
  const scored = past
    .filter((r) => r[SUBJECT_COL] && Number(r[SENDS_COL]) > 0)
    .map((r) => ({
      subject: r[SUBJECT_COL],
      rate: Number(r[OPENS_COL]) / Number(r[SENDS_COL]),
    }))
    .sort((a, b) => b.rate - a.rate);

  return scored.length ? scored[0].subject : variants[0];
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt, model = 'claude-haiku-4-5-20251001', maxTokens = 400) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model,
      max_tokens: maxTokens,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. generateSubjectVariants builds a prompt that pins the tone palette — punchy, friendly, curious, urgent, plain — so the five replies are meaningfully different instead of small rewrites of one another.
2. It calls Claude Sonnet with a small token budget, then splits the reply on newlines, trims each line, and drops anything empty. The result is a clean array of candidate subjects.
3. logAndPickWinner reads the existing log into memory, then appends one row per new variant with zero sends and zero opens. You fill those in after the campaign so future runs can learn from this one.
4. It scores every past row by opens / sends, guarding against rows with no sends so they don’t divide by zero or pollute the ranking.
5. It returns the historical top performer. If the sheet is empty (your first run), it falls back to the first variant from this batch so you still get a recommendation.

Example run

Suppose the log sheet already contains:

date	subject	sends	opens
2026-02-05	New Northwind drops Friday	4,800	1,300
2026-02-12	One thing changed inside Northwind	4,810	1,720
2026-02-19	Northwind: a quick heads-up	4,790	1,090

You call:

const variants = generateSubjectVariants(
  'Our spring catalogue lands Friday — twelve new pieces, three returning ' +
  'classics, free shipping for members.'
);
const winner = logAndPickWinner(variants);
Logger.log('Send with: ' + winner);

variants looks like:

• Spring drops Friday — be first in
• Twelve new pieces. One quiet favourite returns.
• Guess what’s coming back to Northwind?
• Friday only: free shipping for members
• New Northwind spring catalogue this Friday

The log gets five fresh rows added at the bottom. winner returns One thing changed inside Northwind — the line with the best open rate so far (1,720 / 4,810 = 35.8%).

Run it

This runs on-demand, just before you schedule a campaign:

1. In the Apps Script editor, open a wrapper that calls generateSubjectVariants with your draft body, then passes the result to logAndPickWinner.
2. Click Run, approve the auth prompt the first time.
3. Use the returned subject in your ESP. After the campaign lands, paste the final sends and opens next to that row.

Watch out for

• Past winners stay winners only because you tell the sheet how each send performed. If you never fill in sends and opens, the script always falls back to “first variant” — fine on day one, useless on day thirty.
• Open rate is a thin metric. It rewards clickbait. If your goal is sign-ups or sales, add a conversions column and rank by that instead.
• This generates ideas; it doesn’t send anything. Pair it with your usual Gmail or ESP workflow — the recommended subject is just a string.
• For deeper variant testing, write each variant to a slice of your list and measure properly. The script’s “history” ranking assumes one subject per send, not split tests.