How to Normalize CRM Job Titles by Seniority with Claude and Google Sheets

I used to manage audience data for B2B media companies, I still do, but I used to too. We sold ad campaigns, sponsored content, and email deployments; advertisers always wanted some version of “We want to target only directors and above.”

The problem was that the data wasn’t always…clean. How could it be? Some people have made up titles like “Growth Ninja”, “Chief Evangelist”, or sometimes people would shorten their titles to “Sr. Mgr. Digital Experience”. Every year, someone (usually me) has to normalize job titles and seniority buckets for advertisers.

Cleaning the data always felt rushed and fragile, and every time I found a goofy title, I would have to make a judgment call about where to bucket it, write a note about why, and move on.

Every time you need to update audience segmentation, or sales is breathing down your neck, ask,” How many C-Suite executives are on our email list?” You pay the segmentation tax, someone (me) has to clean the data so they can use it. How much time does that take, and is that the best use of someone’s time?

For B2B media companies, knowing who and what your readers do for a living is really important to how you advertise to them. Your segmentation can be filled with hot garbage because someone typed “Chief Executive Assistant,” and your lookup table included them in the C-Suite bucket. Which means you’re either lying to advertisers or leaving real audience value uncounted for.

If your contact list feeds anything like the daily email performance reports or the newsletter audience reporting that go to your sales team or advertisers, seniority accuracy is load-bearing data.

I’ve built lookup tables and regex to try and fix this issue in the past; it’s worked for simple things like “VP” and “Manager”, but it fails immediately on:

You can extend these lookup tables indefinitely and still not cover all use cases, but does anyone want to? Is that how you really want to spend your time?

The advantage of Claude is that you can offload some of that knowledge. You can provide instructions in a markdown file and explain your classification logic. With a dedicated section for edge cases, you can have the rules evolve. You’re building a standard for how you classify data.

The ruleset is a plain Markdown document that becomes Claude's system prompt. It defines the five seniority buckets with examples, sets normalization rules (fix capitalization, expand abbreviations, clean punctuation), and explicitly handles edge cases.

In this version of the tool, the ruleset lives in a dedicated "Title Normalizer Rules" tab in your Google Sheet — not in a file on your machine. That matters: anyone with access to the sheet can read and edit it without touching code. When your definition of "Director" changes, you update the tab, not a config file. Here’s an excerpt from the ruleset.

The edge case section is the most important part. Every time the tool flags something wrong, the right fix is to add a new edge-case rule and rerun — not to fight the model. The ruleset is a living documentation of how your organization thinks about seniority.

This script runs in Google Apps Script and is directly attached to your Google Sheet. It reads job titles from your sheet, sends them to Claude in batches with a classification ruleset you define, and writes "Normalized Title" and "Seniority Bucket" columns back — without touching your original data. Setup requires pasting two files into the Apps Script editor and saving your Anthropic API key once.

When you open the sheet, a CRM Tools menu appears with three options: Normalize Titles, Dry Run Preview, and Update Rules from Corrections.

The part that took me the longest to design was the feedback loop. Every tool like this has the same problem: Claude flags ambiguous titles as "Unknown," you fix them manually, and two months later, you're fixing the same titles again because nothing was learned from your corrections. This one does. Unknown titles are highlighted in yellow on the sheet. You correct them. One menu click sends those corrections back to Claude, which updates the ruleset automatically. The next run handles those titles correctly — and anything like them.

Here's what happens when you run Normalize Titles:

The script handles its own auth. Because it runs inside Apps Script with SpreadsheetApp Google handles the Sheets permission natively. The only credential you manage is your Anthropic API key, which is saved to Script Properties on first run, so you're asked only once.

The script processes titles in batches of 50 per API call. For lists with thousands of rows, you may need to run it in chunks due to Google Apps Script's 6-minute execution limit — but idempotency means already-processed rows are always skipped.

The script writes "Unknown" in the Seniority Bucket column and highlights the cell yellow. You correct it manually, then run "Update Rules from Corrections" to feed that correction back into the ruleset.

No. The script is paste-and-run inside the Apps Script editor. The only credential you manage is an Anthropic API key, which you save once and never touch again.

The script uses Claude Haiku, which is priced for exactly this kind of batch classification work. A list of 10,000 titles costs well under a dollar to process — check current per-token pricing at https://console.anthropic.com. The higher cost is the Anthropic API key, which requires a paid account.

The script uses claude-haiku-4-5 by default — Claude's fastest and cheapest model. Haiku is the right call for structured classification tasks like this: the input is simple, the output format is rigid, and speed matters more than reasoning depth. You're not asking it to write copy or analyze strategy; you're asking it to apply a ruleset you've already written. Haiku does that well at a fraction of the cost of larger models.

Sign up for the Automation Almanac newsletter to get the complete working code. Everything you need to run this on your own sheet today.