How to Automate Email Enrichment in Google Sheets (Lead Enrichment Workflow Tutorial)

Introduction

Email enrichment is a critical process for sales and marketing teams looking to build targeted outreach lists. However, many businesses rely on expensive all-in-one platforms like Clay, which can cost thousands of dollars per month for large contact volumes.

In this tutorial, we'll show you how to build a cost-effective, flexible email enrichment workflow using Google Sheets and direct API integrations with tools like Hunter.io, Findymail, MillionVerifier, and Instantly. This approach not only saves money but also gives you complete control over your data and process.

By the end of this guide, you'll have a robust system that can:

• Find email addresses based on domain names
• Enrich contacts with specific job titles and departments
• Verify email deliverability at scale
• Handle catch-all domains intelligently
• Make automated "Send/Don't Send" decisions

All at a fraction of the cost of all-in-one platforms.

Workflow Overview

Our email enrichment workflow consists of four main stages, each handled by a specialized tool:

1. Domain Enrichment (Hunter.io): Find potential contacts at a company based on domain name
2. Targeted Enrichment (Findymail): Fill gaps with targeted email finding for specific people or roles
3. Bulk Verification (MillionVerifier): Verify email deliverability at scale
4. Catch-All Handling (Instantly): Double-check risky emails and make send/don't send decisions

This modular approach allows you to optimize for cost and quality at each stage of the process. Let's dive into each component in detail.

Domain Enrichment (Hunter.io)

Hunter.io's Domain Search API is our first step in the enrichment process. It allows us to find email addresses associated with a domain name, making it perfect for building contact lists from a list of companies.

Why Hunter.io for Domain Enrichment?

Hunter's Domain Search API is extremely cost-effective for bulk enrichment because:

• It costs just 1 credit per domain search (regardless of how many emails are found)
• On the Growth plan (~$99/month), that's about $0.002 per contact when fully utilized
• Hunter doesn't charge for duplicate searches within the same month
• Results include name, job title, and email verification status

This makes Hunter ideal for the initial enrichment phase, especially when you need multiple contacts from the same company.

Custom Job Title Ranking Algorithm

One key improvement we've made is implementing a custom job title ranking algorithm. Hunter might return dozens of contacts for a single domain, but you typically only want the most relevant ones.

Our algorithm prioritizes contacts based on:

1. Relevance of job title to outreach (e.g., "Marketing Manager" over "Developer")
2. Seniority level (e.g., "Director" over "Assistant")
3. Email type (personal emails over generic ones like "info@")
4. Hunter's confidence score

Here's a simplified version of our job title ranking code:

// Job titles to prioritize, in order of importance
const PRIORITY_JOB_TITLES = [
  'link building', 'link builder', 'seo', 'content manager', 'content marketing', 
  'organic', 'outreach', 'digital marketing', 'growth', 'marketing manager', 
  'marketing', 'editor', 'editorial', 'partnership', 'sem', 'ceo', 'owner', 
  'founder', 'writer'
];

// Generic email prefixes to prioritize, in order of importance
const PRIORITY_GENERIC_PREFIXES = [
  'marketing', 'contact', 'editor', 'hello', 'help', 'info', 'media', 
  'press', 'support', 'sales'
];

// Sort contacts by priority job titles and email types
function sortContactsByPriority(contacts) {
  // First separate personal and generic emails
  const personalEmails = contacts.filter(contact => contact.type === 'personal');
  const genericEmails = contacts.filter(contact => contact.type === 'generic');
  
  // Sort personal emails by job title priority
  personalEmails.sort((a, b) => {
    const positionA = (a.position || '').toLowerCase();
    const positionB = (b.position || '').toLowerCase();
    
    // Find the priority indices
    let priorityA = Infinity;
    let priorityB = Infinity;
    
    for (let i = 0; i < PRIORITY_JOB_TITLES.length; i++) {
      const title = PRIORITY_JOB_TITLES[i];
      if (priorityA === Infinity && positionA.includes(title)) {
        priorityA = i;
      }
      if (priorityB === Infinity && positionB.includes(title)) {
        priorityB = i;
      }
    }
    
    // Sort by priority
    if (priorityA !== priorityB) {
      return priorityA - priorityB;
    }
    
    // If same priority, sort by confidence score
    return (b.confidence || 0) - (a.confidence || 0);
  });
  
  // Return personal emails first, then generic emails
  return [...personalEmails, ...genericEmails];
}

This algorithm ensures that you get the most relevant contacts for your outreach campaigns, maximizing the value of each domain search credit.

Targeted Enrichment (Findymail)

While Hunter.io is great for domain-wide searches, sometimes you need to find emails for specific people or roles. That's where Findymail comes in.

Why Findymail for Targeted Enrichment?

Findymail offers several advantages for targeted email finding:

• Can find emails for specific people at specific companies
• All emails are verified at acquisition (no separate verification step needed)
• Only charges for successful finds (no wasted credits)
• Integrates with LinkedIn Sales Navigator for prospecting

At around $0.01 per verified contact (on the $99/month plan), Findymail is slightly more expensive than Hunter but provides higher accuracy for targeted enrichment.

Implementing Findymail in Google Sheets

We use Findymail in two ways in our workflow:

1. Gap filling: For domains where Hunter didn't find relevant contacts
2. Targeted enrichment: When we need emails for specific people or roles

Here's a simplified example of our Findymail integration code:

/**
 * Find email addresses for contacts using Findymail API
 * 
 * @param {string} firstName - First name of the contact
 * @param {string} lastName - Last name of the contact
 * @param {string} domain - Company domain
 * @param {string} apiKey - Findymail API key
 * @return {object} The API response with email data
 */
function findEmailWithFindymail(firstName, lastName, domain, apiKey) {
  const url = 'https://api.findymail.com/v1/search';
  
  const payload = {
    first_name: firstName,
    last_name: lastName,
    domain: domain
  };
  
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + apiKey
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
  
  try {
    const response = UrlFetchApp.fetch(url, options);
    return JSON.parse(response.getContentText());
  } catch (error) {
    console.error('Error in findEmailWithFindymail:', error);
    return { success: false, error: error.message };
  }
}

This function is called for each contact that needs targeted enrichment, and the results are written back to the Google Sheet.

Bulk Verification (MillionVerifier)

Once we've collected email addresses from Hunter and Findymail, the next step is to verify their deliverability. This is where MillionVerifier comes in.

Why MillionVerifier for Bulk Verification?

MillionVerifier offers several advantages for bulk email verification:

• Extremely cost-effective: as low as $0.00099 per email at scale
• Pay-as-you-go pricing with no monthly subscription
• Only charges for definitive results (valid/invalid), not for "unknown" or "catch-all"
• Fast processing (up to 1M emails in ~6 hours)

This makes MillionVerifier perfect for cleaning large lists of emails before sending campaigns.

Implementing MillionVerifier in Google Sheets

Our MillionVerifier integration consists of two parts:

1. Export: Export emails from Google Sheets to a CSV file
2. Import: Import verification results back into Google Sheets

Here's a simplified example of our MillionVerifier export code:

/**
 * Export emails for Million Verifier
 * 
 * @param {string} sheetName - Name of the sheet containing emails
 * @param {boolean} onlyUnverified - Whether to only export unverified emails
 * @return {object} Export result with CSV data
 */
function exportEmailsForMillionVerifier(sheetName, onlyUnverified) {
  try {
    // Get the sheet
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(sheetName);
    
    if (!sheet) {
      return {
        success: false,
        message: "Could not find the specified sheet."
      };
    }
    
    // Get all the data
    const data = sheet.getDataRange().getValues();
    if (data.length <= 1) {
      return {
        success: false,
        message: "Sheet has no data or only headers."
      };
    }
    
    // Find column indices
    const headers = data[0];
    const emailColIndex = headers.findIndex(h => /email/i.test(h));
    const qualityColIndex = headers.findIndex(h => /quality|verification/i.test(h));
    
    if (emailColIndex === -1) {
      return {
        success: false,
        message: "Could not find email column in the sheet."
      };
    }
    
    // Filter rows with emails
    const emailRows = [];
    for (let i = 1; i < data.length; i++) {
      const email = data[i][emailColIndex];
      
      // Skip empty emails
      if (!email) continue;
      
      // If onlyUnverified is true, skip emails that already have verification results
      if (onlyUnverified && qualityColIndex !== -1 && data[i][qualityColIndex]) {
        continue;
      }
      
      emailRows.push([email]);
    }
    
    if (emailRows.length === 0) {
      return {
        success: false,
        message: "No emails found to export."
      };
    }
    
    // Create CSV content
    const csvContent = emailRows.map(row => row.join(',')).join('\n');
    
    // Generate filename with timestamp
    const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
    const filename = `emails_for_verification_${timestamp}.csv`;
    
    return {
      success: true,
      csvData: csvContent,
      filename: filename,
      emailCount: emailRows.length,
      message: `${emailRows.length} emails exported for verification.`
    };
  } catch (error) {
    console.error('Error in exportEmailsForMillionVerifier:', error);
    return {
      success: false,
      message: "An error occurred: " + error.message
    };
  }
}

And here's a simplified example of our MillionVerifier import code:

/**
 * Process Million Verifier import
 * 
 * @param {string} csvContent - CSV content from Million Verifier
 * @param {string} sheetName - Name of the sheet to update
 * @return {object} Import result
 */
function processMillionVerifierImport(csvContent, sheetName) {
  try {
    // Parse CSV content
    const csvData = Utilities.parseCsv(csvContent);
    
    if (!csvData || csvData.length <= 1) {
      return {
        success: false,
        message: "Invalid CSV data or empty file."
      };
    }
    
    // Get the sheet
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(sheetName);
    
    if (!sheet) {
      return {
        success: false,
        message: "Could not find the specified sheet."
      };
    }
    
    // Get all the data
    const data = sheet.getDataRange().getValues();
    if (data.length <= 1) {
      return {
        success: false,
        message: "Sheet has no data or only headers."
      };
    }
    
    // Find column indices
    const headers = data[0];
    const emailColIndex = headers.findIndex(h => /email/i.test(h));
    
    // Find or create Quality column
    let qualityColIndex = headers.findIndex(h => /quality/i.test(h));
    if (qualityColIndex === -1) {
      // Add Quality column
      qualityColIndex = headers.length;
      sheet.getRange(1, qualityColIndex + 1).setValue("Quality");
    }
    
    // Find or create Verification Result column
    let verificationResultColIndex = headers.findIndex(h => /verification.?result/i.test(h));
    if (verificationResultColIndex === -1) {
      // Add Verification Result column
      verificationResultColIndex = qualityColIndex + 1;
      sheet.getRange(1, verificationResultColIndex + 1).setValue("Verification Result");
    }
    
    // Find CSV column indices
    const csvHeaders = csvData[0];
    const csvEmailColIndex = csvHeaders.findIndex(h => /email/i.test(h));
    const csvResultColIndex = csvHeaders.findIndex(h => /result/i.test(h));
    const csvQualityColIndex = csvHeaders.findIndex(h => /quality/i.test(h));
    
    if (csvEmailColIndex === -1 || csvResultColIndex === -1) {
      return {
        success: false,
        message: "Could not find required columns in the CSV file."
      };
    }
    
    // Update sheet with verification results
    let updatedCount = 0;
    
    for (let i = 1; i < csvData.length; i++) {
      const email = csvData[i][csvEmailColIndex];
      const result = csvData[i][csvResultColIndex];
      const quality = csvQualityColIndex !== -1 ? csvData[i][csvQualityColIndex] : '';
      
      // Find matching row in sheet
      for (let j = 1; j < data.length; j++) {
        if (data[j][emailColIndex] === email) {
          // Update Quality
          sheet.getRange(j + 1, qualityColIndex + 1).setValue(quality);
          
          // Update Verification Result
          sheet.getRange(j + 1, verificationResultColIndex + 1).setValue(result);
          
          updatedCount++;
          break;
        }
      }
    }
    
    return {
      success: true,
      message: `Updated ${updatedCount} rows with verification results.`
    };
  } catch (error) {
    console.error('Error in processMillionVerifierImport:', error);
    return {
      success: false,
      message: "An error occurred: " + error.message
    };
  }
}

This two-step process allows us to leverage MillionVerifier's powerful verification capabilities while keeping the results organized in our Google Sheet.

Catch-All Email Handling (Instantly)

After bulk verification with MillionVerifier, we often have emails marked as "catch-all" or "unknown." These are domains that accept all emails, making it difficult to determine if an address is valid. This is where Instantly comes in.

Why Instantly for Catch-All Handling?

Instantly offers several advantages for handling catch-all domains:

• Specialized in catch-all domain verification
• Integrated with their email sending platform for better deliverability
• Affordable pricing (starting at $17/month)
• Provides clear "Send/Don't Send" recommendations

By double-checking risky emails with Instantly, we can make more informed decisions about which addresses to include in our outreach campaigns.

Implementing Instantly in Google Sheets

Our Instantly integration focuses on emails that MillionVerifier marked as "catch-all" or "unknown." Here's a simplified example of our Instantly verification code:

/**
 * Verify an email using Instantly API
 * 
 * @param {string} email - Email address to verify
 * @param {string} apiKey - Instantly API key
 * @return {object} Verification result
 */
function verifyEmailWithInstantly(email, apiKey) {
  const url = 'https://api.instantly.ai/api/v1/verify/email';
  
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + apiKey
    },
    payload: JSON.stringify({ email: email }),
    muteHttpExceptions: true
  };
  
  try {
    const response = UrlFetchApp.fetch(url, options);
    const result = JSON.parse(response.getContentText());
    
    return {
      success: true,
      email: email,
      status: result.status,
      deliverable: result.deliverable,
      message: result.message
    };
  } catch (error) {
    console.error('Error in verifyEmailWithInstantly:', error);
    return {
      success: false,
      email: email,
      error: error.message
    };
  }
}

We also implement a decision-making algorithm that combines the results from MillionVerifier and Instantly to determine whether to send to each email:

/**
 * Determine whether to send to an email based on verification results
 * 
 * @param {string} millionVerifierStatus - Status from MillionVerifier
 * @param {string} instantlyStatus - Status from Instantly
 * @return {string} "Send" or "Don't Send"
 */
function determineSendDecision(millionVerifierStatus, instantlyStatus) {
  // If Instantly verified the email, trust that result
  if (instantlyStatus === 'verified') {
    return 'Send';
  }
  
  if (instantlyStatus === 'invalid') {
    return 'Don't Send';
  }
  
  // If no Instantly result, fall back to MillionVerifier
  if (millionVerifierStatus === 'good') {
    return 'Send';
  }
  
  if (millionVerifierStatus === 'bad' || millionVerifierStatus === 'risky') {
    return 'Don't Send';
  }
  
  // Default to not sending if we're unsure
  return 'Don't Send';
}

This algorithm ensures that we only send to emails with a high likelihood of delivery, reducing bounce rates and improving campaign performance.

Real-World Pricing Comparison

Let's compare the cost of our DIY approach with an all-in-one platform like Clay for processing 100,000 contacts per month:

As you can see, our DIY approach costs just $478/month compared to $2,500/month for Clay, resulting in a 5x cost reduction. That's a savings of over $24,000 per year for processing 100,000 contacts per month.

Tech Stack Overview

Our email enrichment workflow is built on the following technologies:

• Google Sheets: The central database for storing and managing contacts
• Google Apps Script: For writing custom functions and UI components
• REST APIs: Direct integrations with Hunter.io, Findymail, MillionVerifier, and Instantly
• AI Coding Assistants: Tools like Cursor and GPT for generating and debugging code

This stack offers several advantages over all-in-one platforms:

• Modularity: Each component can be replaced or upgraded independently
• Flexibility: Custom logic can be implemented for specific use cases
• Transparency: Full visibility into how data is sourced and processed
• Cost-effectiveness: Pay only for the services you need
• Ownership: Complete control over your data and workflow

Extending Your Workflow

Once you have the basic email enrichment workflow in place, there are many ways to extend it:

Social Media Enrichment

Add social media profiles to your contacts using APIs like:

• LinkedIn API (via Sales Navigator)
• Twitter API
• GitHub API

This can provide valuable context for personalized outreach.

Automated Lead Scoring

Implement a scoring system based on:

• Job title relevance
• Company size and industry
• Email deliverability
• Social media activity

This helps prioritize your outreach efforts.

Automated Follow-ups

Integrate with email sending platforms like:

• Instantly
• Lemlist
• Mailshake

This allows you to automate follow-up sequences based on your enrichment data.

Conclusion

Building a DIY email enrichment workflow in Google Sheets offers significant advantages over all-in-one platforms like Clay:

• Cost savings: 5x cheaper ($0.00478 vs $0.025 per contact)
• Flexibility: Customize every aspect of the workflow
• Transparency: Full visibility into data sources and processing
• Control: No vendor lock-in or black-box algorithms

By leveraging specialized tools for each step of the process, you can build a robust email enrichment system that meets your specific needs at a fraction of the cost of all-in-one platforms.

If you'd like help implementing this workflow for your business, or if you have questions about any part of the process, don't hesitate to reach out to us. We specialize in building custom automation solutions that save time and money.