GPT for Google Sheets, Docs AI, Slides, Forms

How to Automate Cold Email Personalization with AI in Google Sheets

Learn how to use AI-powered functions in Google Sheets to create highly personalized cold emails at scale. Automate your outreach while maintaining a personal touch.

📋 Prerequisites

Before you begin, make sure you have:

🚀 Getting Started

Step 1: Set Up Your Data Sheet

Create a new Google Sheet with these essential columns:

Column Purpose
Prospect Name Recipient's full name
Company Company name
LinkedIn URL Profile link
Role Job title
Email Contact email

Step 2: Generate Personalized Content

Use the GPT function to create customized email content:

=GPT(
    "Write a personalized cold email to " & A2 & 
    " who works as " & D2 & 
    " at " & B2 & 
    ". Include specific details about their role and company."
)

Example Output:

Input Data Generated Email
Name: Sarah Chen
Role: Marketing Director
Company: TechGrowth Inc		 Hi Sarah,

I noticed your excellent work leading TechGrowth's marketing initiatives, particularly your recent campaign on sustainable tech solutions. Your approach to combining data-driven strategies with creative storytelling caught my attention...

[Email continues]

Step 3: Analyze LinkedIn Profiles

Extract insights from LinkedIn profiles using CLAUDE:

=CLAUDE(
    "Analyze this LinkedIn profile and extract key talking points: " & C2
)

Example Output:

Category Insights
Recent Achievements • Led digital transformation project
• Increased marketing ROI by 45%
• Speaker at MarTech Summit 2024
Interests • Marketing Analytics
• Brand Strategy
• Sustainable Business

💡 Advanced Personalization

Company Research Integration

Use PERPLEXITY to gather company insights:

=PERPLEXITY(
    "Research " & B2 & 
    " and provide recent news, achievements, and potential pain points"
)

Role-Specific Templates

Create targeted templates with MISTRAL:

=MISTRAL(
    "Generate an email template for a " & D2 & 
    " focusing on their specific challenges and goals"
)

📊 Email Performance Analysis

Track Response Rates

Use GPT to analyze response patterns:

=GPT(
    "Analyze these email responses and identify successful patterns:
    Subject: " & F2 & "
    Open Rate: " & G2 & "
    Response Rate: " & H2
)

A/B Testing

Test different approaches:

=GPT(
    "Generate two variations of this email:
    Original: " & E2 & "
    Variation 1: More direct
    Variation 2: More storytelling"
)

📨 Bulk Email Preparation

Step 1: Generate Multiple Variations

Use GPT to create different versions for testing:

=GPT(
    "Generate 3 different email subject lines for a " & D2 & 
    " at " & B2 & 
    " focusing on their " & E2 & 
    " initiative. Make them sound natural and engaging."
)

Example Output:

Role Generated Subject Lines
Marketing Director at TechGrowth 1. "Your sustainable tech campaign caught my eye, Sarah"
2. "Quick question about TechGrowth's marketing strategy"
3. "Loved your MarTech Summit presentation on ROI"

Step 2: Batch Process LinkedIn Data

Use CLAUDE for bulk profile analysis:

=ARRAYFORMULA(
    CLAUDE(
        "Analyze these LinkedIn profiles and extract key achievements: " & 
        C2:C50
    )
)

Example Output:

Profile Key Points for Personalization
Profile 1 • Recent promotion to Director
• Published article on AI in Marketing
• Led team of 15 people
Profile 2 • Startup founder
• Series A funding secured
• Featured in Tech Monthly

Step 3: Company Research at Scale

Use PERPLEXITY to analyze multiple companies:

=ARRAYFORMULA(
    PERPLEXITY(
        "For each company, provide their latest achievement and potential pain point: " & 
        B2:B20
    )
)

Step 4: Dynamic Template Generation

Create role-specific templates with MISTRAL:

=MISTRAL(
    "Create an email template that can be customized for these roles: " &
    JOIN(", ", D2:D10) &
    ". Include personalization placeholders."
)

Example Output:

Role Template
CTO Hi {name},

Your work on {company}'s {recent_tech_initiative} caught my attention, particularly the focus on {specific_technology}...

Given your experience with {expertise_area}, I thought you might be interested in...
CMO Hi {name},

I've been following {company}'s recent {marketing_campaign}, and your approach to {strategy_focus} really stands out...

With your focus on {marketing_priority}, I wanted to share...

Step 5: Quality Check

Before sending, validate your emails with GPT:

=GPT(
    "Review this email for tone, personalization, and effectiveness: " & 
    E2 & 
    ". Score it on a scale of 1-10 and suggest improvements."
)

📧 Sending Your Personalized Emails

Gmail Direct Sending

Use the SEND_EMAIL function for sending through Gmail:

=SEND_EMAIL(
    E2,                    // Recipient email
    "Quick question about your AI initiatives",  // Subject
    G2,                    // Message body (from template)
    true                   // Set to true for HTML formatting
)

Mailchimp Transactional Emails

For larger campaigns, use Mailchimp integration:

=MAILCHIMP_SEND_EMAIL(
    $A$1,              // API Key (stored in a fixed cell)
    "your@domain.com", // From email
    F2,               // Subject line
    G2,               // Email body
    E2                // Recipient email
)

Example Configuration:

Parameter Value Description
API Key abc123... Your Mailchimp API key (keep private)
From Email sales@company.com Verified sender address
Subject Loved your AI presentation Dynamic subject from template
Body Hi {name},... Personalized message content

Choosing the Right Sending Method

Feature Gmail (SEND_EMAIL) Mailchimp
Best for Small batches, personal outreach Large campaigns, marketing emails
Sending limits Gmail daily limits Higher sending capacity
Analytics Basic tracking Detailed reporting
Setup Simple Gmail auth Requires API key

Batch Sending with Error Handling

Combined approach for reliable sending:

=IF(
    ISBLANK(E2), 
    "",
    IF(
        $A$2="mailchimp",
        MAILCHIMP_SEND_EMAIL($A$1, $B$1, F2, G2, E2),
        SEND_EMAIL(E2, F2, G2, true)
    )
)

Best Practices for Sending

Email Sending Tips:
  • ✉️ Test emails with a small batch first
  • ⏰ Add 1-2 minute delays between sends
  • 📊 Track delivery and open rates
  • 🔍 Monitor spam folder placement
  • ✅ Verify email addresses before sending

Error Handling

Monitor sending status and handle errors:

=IF(
    ISERROR(H2),
    "Failed: " & ERROR.MESSAGE,
    IF(H2="", "", "Sent successfully")
)

🔍 Best Practices

Key Tips:
  • 🎯 Always verify data accuracy
  • ⏰ Space out your email sends
  • 📝 Test emails before bulk sending
  • 🔄 Regularly update templates based on performance

Data Cleanup

Clean and format your data:

=GPT("Format this name properly: " & A2)
=GPT("Standardize this job title: " & D2)

🔗 Additional Resources

🤝 Need Help?

If you encounter any issues or need assistance, click the “Talk to Human” button in the add-on sidebar for support.