Build an AI lead-scoring model

A long list of leads is not a pipeline — it is a list. Some of those companies look exactly like Northwind’s best clients; most do not. The job is to tell them apart fast enough that sales spends its week on the right twenty, not the random first twenty.

This script turns Northwind’s ideal-customer profile into a scoring function. For each lead that has not been scored, it hands Claude the profile plus the lead’s company facts — industry, headcount, region — and asks for a single number from 0 to 100. The score is parsed, sanity-checked, and written back, so the sheet can be sorted highest-first and worked top to bottom. Already-scored rows are skipped, so a run only pays for new leads.

What you’ll need

• A Google Sheet with one lead per row. Row 1 holds headers including company, industry, headcount, region, and an (initially empty) score column.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• Your ideal-customer profile written into the ICP constant, in plain language — that single string is the model.

The script

// The ideal-customer profile. This one string defines what a high score
// means — edit it to retune the model, no code changes needed.
const ICP = 'Ideal Northwind clients: 10-200 person services or SaaS firms ' +
  'in EU/UK, mid-five-figure annual budget, design-led.';

// The spreadsheet that holds the Leads tab.
const LEADS_SHEET_ID = '1abcLeadsId';

/**
 * Reads the Leads sheet and fills the "score" column for any row that
 * has a company name but no score yet.
 */
function scoreLeads() {
  const sheet = SpreadsheetApp.openById(LEADS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split the header off the data rows.
  const [h, ...rows] = sheet.getDataRange().getValues();

  // 2. Map header names to column indexes so columns are addressed by name.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 3. Bail out if there is nothing to score.
  if (!rows.length) {
    Logger.log('No leads to score — nothing to do.');
    return;
  }

  // 4. Walk each row; skip rows already scored or with no company name.
  rows.forEach((r, i) => {
    if (r[col.score] || !r[col.company]) return;

    // 5. Give Claude the ICP plus the lead's facts, ask for a bare number.
    const prompt = `${ICP}\n\n` +
      `Lead: ${r[col.company]} (${r[col.industry]}, ` +
      `${r[col.headcount]} people, ${r[col.region]}).\n` +
      `Score 0-100 as a number only.`;

    // 6. Parse the reply and only write it if it is a real number.
    const score = parseInt(callClaude(prompt), 10);
    if (Number.isFinite(score)) {
      sheet.getRange(i + 2, col.score + 1).setValue(score);
    }
  });

  Logger.log('Finished scoring leads.');
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 *
 * @param {string} prompt The full prompt to send.
 * @return {string} Claude's reply, trimmed.
 */
function callClaude(prompt) {
  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: 'claude-haiku-4-5-20251001',
      max_tokens: 20,
      messages: [{ role: 'user', content: prompt }],
    }),
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. scoreLeads opens the Leads spreadsheet and reads every row, splitting the header off the data with a destructuring assignment.
2. It builds a col map of header name to column index, so each lead field can be addressed by name rather than a brittle number.
3. If there are no data rows, it logs a message and stops — no wasted API calls.
4. It walks each row and skips two cases: the row already has a score, or there is no company name to score.
5. For each remaining row it builds a prompt that puts the ICP first as the scoring rubric, then the lead’s facts, and asks for a bare number — max_tokens: 20 keeps the reply tight.
6. It parses the reply with parseInt and writes it back only if Number.isFinite confirms a real number, so a stray word from the model never lands a bad value in the sheet.

Example run

Say the Leads sheet has three new unscored rows and the ICP above:

company	industry	headcount	region	score
Brightfold	Design agency	45	UK
MegaCorp Logistics	Freight	12,000	US
Pixel & Co	SaaS	80	Germany

After scoreLeads, the score column is filled:

company	industry	headcount	region	score
Brightfold	Design agency	45	UK	92
MegaCorp Logistics	Freight	12,000	US	14
Pixel & Co	SaaS	80	Germany	88

Sort the sheet by score descending and the agency and SaaS firm float to the top; the giant US logistics company drops to the bottom where it belongs.

Trigger it

New leads arrive continuously, so run this on a schedule:

1. In the Apps Script editor open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose scoreLeads, a Time-driven source, and a Day timer — an overnight run means every morning’s list is already ranked.

To run it on demand instead, add a custom menu:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Sales tools')
    .addItem('Score leads', 'scoreLeads')
    .addToUi();
}

Watch out for

• The score is only as good as the ICP. A vague profile gives vague scores. Spend the effort on that one string — it is the entire model, and retuning it needs no code change.
• Scores are relative, not absolute. An 88 and a 92 both mean “strong fit”; do not over-read a four-point gap. Use the score to sort and to set a cut-off, not to rank leads to the decimal.
• Thin rows score poorly. A lead missing its industry or headcount gives Claude little to go on. Treat low scores on sparse rows as “needs enrichment”, not “bad lead”.
• Bad replies are dropped silently. The Number.isFinite guard skips any non-numeric reply, leaving the cell blank. Re-run to retry blank rows, or log the raw reply if a row never scores.
• Re-running never re-scores. Rows with a score are skipped, so to re-score a lead — say, after updating the ICP — clear the score column first.
• Long sheets can time out. Apps Script caps a run at six minutes; process thousands of leads in batches across scheduled runs.