Learning Lab: How to Build a Lead Generation System Using Google Sheets + AI

Hilary
May 31
5 min read

Most salespeople and solo founders treat lead generation like grocery shopping — they go when they run out. They panic, spend a weekend scraping LinkedIn, dump names into a spreadsheet with no system, and repeat the whole mess in six weeks.

The fix isn't a $400/month CRM. It's a simple, repeatable system you can build in an afternoon using tools you already have. Google Sheets, a couple of free add-ons, and AI as your research assistant. That's it.

This guide walks you through building a lightweight lead gen machine from scratch — one you'll actually use.

Watch the Video First

The walkthrough below mirrors this video step by step. Watch it for the visual context, then use the guide to move at your own pace.

https://www.youtube.com/watch?v=_aAqahAEOgk

What You'll Need

A Google account (free)
Claude or ChatGPT for research assistance (free tier works)
Hunter.io for email finding — free plan gives you 25 lookups/month
Optionally: CapGo.AI Google Sheets add-on for bulk AI enrichment (free tier available)
A defined ICP (Ideal Customer Profile) — you need to know who you're looking for before any tool can help

Step-by-Step: Building Your Lead Gen System

Step 1 — Set up your master Sheets template

Open a new Google Sheet and create the following columns. This structure is the backbone of the whole system — don't skip or rename columns yet, as later steps reference them directly.

Create a second tab called Sequences where you'll store your email copy for each segment. This keeps everything in one place.

Step 2 — Define your ICP in writing before sourcing a single lead

This is the step most people skip, and it's why their lists are garbage. Open Claude and do this exercise first.

Prompt:

"Help me define my Ideal Customer Profile. Ask me questions about my product/service, the problems I solve, the customers I've had the most success with, and the ones I've struggled with. Then summarize it into a one-paragraph ICP I can use to filter leads."

Go through that conversation. At the end, you'll have something like:

"My ICP is: Series A-B SaaS companies (50–300 employees), VP or Director of Sales, who are actively growing their sales team and struggling with new rep ramp time. They're typically found in HR Tech, Sales Tech, or vertical SaaS."

Paste this ICP into a new tab in your sheet called ICP & Targeting. Every lead you add should pass this filter.

Step 3 — Source leads from the right places

With your ICP defined, you now know exactly where to look. Here are the highest-quality free and low-cost sources:

LinkedIn (manual, high quality) Search by job title + industry + company size using LinkedIn's built-in filters. You don't need Sales Navigator for most ICP segments. Copy names, companies, and profile URLs directly into your sheet.

Apollo.io (free tier) Apollo's free plan gives you access to a large database with filters for industry, company size, job title, and location. The free tier export limit is enough to fill a focused list each month.

Google search operators (underused) Try searches like

site:linkedin.com/in "VP of Sales" "Series B" "SaaS" or "Director of Sales" "we're hiring" site:targetcompany.com.

You'll surface prospects others aren't targeting.

Industry directories and event lists Speakers at conferences, contributors to industry newsletters, members of niche Slack communities — these are warm leads who are already active and vocal about the problems you solve.

For each lead you add, fill in as many columns as you can. Leave gaps — you'll fill them with AI in the next step.

Step 4 — Use AI to enrich and research at scale

Here's where the system starts to pay off. You have names and companies. Now you need the context that makes your outreach actually personal.

Option A: Manual enrichment with Claude (best for high-value targets)

For your top 20–30 prospects, use Claude to do a quick research brief. Paste in their name, company, and LinkedIn URL, then prompt:

"Research [Name] at [Company]. Tell me: what does their company do, what growth stage are they at, what pain points are common for a [Title] at a company like this, and what recent news or signals might be relevant to why they'd want to talk to me? Here's my ICP for context: [paste ICP]."

Take the output and paste the key points into your Research Notes column.

Option B: CapGo.AI add-on (best for larger lists)

Install the free CapGo.AI add-on from the Google Workspace Marketplace. Once connected, you can write AI prompts directly as Sheets formulas that run against each row automatically.

For example, in your Research Notes column:

=AI("Summarize what " & B2 & " does, their company size, and one likely pain point for a " & F2 & " there. Keep it to 2 sentences.")

This populates research notes for every row in your list without leaving the sheet. It's slower than dedicated enrichment tools but free and surprisingly capable for most use cases.

Step 5 — Find and verify email addresses

A great list with bad emails is worthless. Use Hunter.io's free Chrome extension or web app to find verified addresses.

For each prospect:

Go to Hunter.io and enter the person's name and company domain
Hunter returns the most likely email format with a confidence score
Copy verified emails (aim for 90%+ confidence) into your Email column
For anyone Hunter can't find, check their LinkedIn "Contact Info" section, or look for a pattern from other verified contacts at the same company

Pro tip: Hunter's "Domain Search" feature lets you enter any company domain and see all emails it has on file, including the format the company uses (first.last@ vs flast@). Use that pattern to construct emails for anyone Hunter hasn't verified directly.

Step 6 — Set up your status and priority workflow

Your sheet is now a working pipeline. Keep it clean and usable with two habits.

Update status every time you touch a lead. The Status column is your at-a-glance view of where everyone stands. Color-code it with conditional formatting: red for Not Contacted, yellow for Emailed, green for Replied, blue for Meeting Booked. In Google Sheets: Format → Conditional Formatting → set rules per value.

Assign priority before you start outreach. Go through your list and mark each lead High, Medium, or Low based on ICP fit. Only work High-priority leads first. This forces your best shot in front of your best prospects — not the other way around.

Step 7 — Build a simple weekly rhythm

The most common failure mode for DIY lead systems is they get built and never maintained. Block 30 minutes every Monday to:

Add 10–20 new leads to the sheet
Update the status on anyone you've contacted
Move contacts who've gone through your full sequence with no reply to a "Cooled" tab — don't delete them, just get them off your active list
Review your reply rate honestly: is it a lead quality problem or an outreach problem?

A system you use imperfectly every week beats a perfect system you touch once a month.

Pro Tips

Quality over quantity, every time. A list of 50 people who precisely match your ICP will outperform 500 vague contacts. Every time. Resist the urge to inflate your numbers — a big list is a vanity metric, not a pipeline.

The Research Notes column is your competitive advantage. Most salespeople send the same email to everyone. If you spend 90 seconds reading your research note before hitting send — and adjust even one sentence to reference something specific — your reply rate will measurably improve.

Use Sheets filters ruthlessly. Filter by Status to see only "Not Contacted" leads when you sit down to write. Filter by Priority to work your best leads first. Don't scroll the whole sheet every session — filter it down to exactly what you need right now.

Learning Lab is a series of short, practical how-to guides built around the best YouTube tutorials for sales professionals and entrepreneurs. Each post embeds the source video and walks you through it step by step.