Skip to content
P
AI for Insurance Customer Service Representative

Zapier Automation: Renewal Reminder Email Sequence

For Insurance Customer Service Representatives ·

Zapier

For Insurance Customer Service Representatives

Tools: Zapier, Google Sheets, Gmail | Time to build: 1.5-2 hours | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using ChatGPT for email drafting — see Level 3 guide: "Set Up ChatGPT for Insurance CSR Work"

What This Builds

You'll create an automated system that monitors a Google Sheet of upcoming policy renewals, then sends timed reminder emails at 60 days, 30 days, and 7 days before each policy expires. Each email is personalized with the client's name, policy type, and renewal date. Instead of manually tracking and sending renewal reminders (or not doing it at all because it takes too long), the system handles it automatically — you just keep the spreadsheet current.

Prerequisites

  • A free Zapier account at zapier.com (free tier handles this automation)
  • Google Sheets (free with any Google account)
  • Gmail for sending — or Outlook if you use Microsoft 365
  • Cost: Free on Zapier's free tier (up to 100 tasks/month — enough for most small agencies)

The Concept

Think of this like setting up a drip email system, but for renewals instead of marketing. You maintain one spreadsheet with your renewal pipeline. Zapier watches that spreadsheet every day. When a renewal date is approaching (60, 30, or 7 days out), Zapier automatically triggers an email to that client. You write the email templates once; the automation runs forever.

Build It Step by Step

Part 1: Set up the Google Sheet

  1. Open Google Sheets (sheets.google.com) and create a new spreadsheet

  2. Name it "Renewal Pipeline"

  3. Create these exact column headers in Row 1:

    • A: Client Name
    • B: Client Email
    • C: Policy Type (e.g., "Homeowners", "Personal Auto", "BOP")
    • D: Renewal Date (format: MM/DD/YYYY)
    • E: Rate Change (e.g., "+12%", "No change", "-5%")
    • F: 60-Day Sent (leave blank — Zapier will fill this)
    • G: 30-Day Sent (leave blank)
    • H: 7-Day Sent (leave blank)

  4. Add 5-10 real or test renewals to the spreadsheet to have data when you test your automation

Example row:

Part 2: Create email templates in Gmail

Before building the Zapier automation, write your three email templates in Gmail's Canned Responses (called "Templates" in Gmail):

  1. In Gmail, go to Settings → See all settings → Advanced → Enable Templates
  2. Compose a new email and write your 60-day template:
Copy and paste this
Subject: Your [POLICY_TYPE] policy renews in 60 days — a quick heads up

Hi [CLIENT_NAME],

I wanted to reach out early about your upcoming [POLICY_TYPE] renewal on [RENEWAL_DATE].

[IF RATE CHANGE: Your premium will be adjusting [RATE_CHANGE] at renewal due to current market conditions — I'll be in touch with more details as we get closer.]

If anything has changed in your life or business that might affect your coverage needs (new vehicle, home renovation, new employees, etc.), now is a great time to review. Just reply to this email and I'll set up a quick call.

Thanks for being a valued client — I'll send another reminder as your renewal gets closer.

[Your Name]
[Agency Name] | [Phone]
  1. Save it as a Gmail Template named "Renewal-60-Day"
  2. Repeat for 30-day and 7-day templates with appropriate urgency levels

Part 3: Build the Zapier automation

  1. Go to zapier.com and sign in (or create a free account)
  2. Click Create Zap

Step 1 — Trigger:

  • Search for "Google Sheets" and select it
  • Choose trigger event: New or Updated Row in Google Sheets
  • Connect your Google account when prompted
  • Select your "Renewal Pipeline" spreadsheet and the main sheet tab

Step 2 — Filter (for 60-day emails):

  • Click the + to add a step after the trigger
  • Search for "Filter by Zapier"
  • Set up the filter: Only continue if Column F (60-Day Sent) is empty AND if the renewal date is within 60-67 days from today
  • For the date math, use Zapier's Formatter step: Formatter by Zapier → Date/Time → Calculate Date — subtract today from Column D (Renewal Date) to get days remaining

Step 3 — Send the email:

  • Add a Gmail action
  • Choose Send Email
  • To: Use the Client Email column (Column B)
  • Subject: Fill in with the 60-day subject line
  • Body: Map Column A (Client Name), Column C (Policy Type), Column D (Renewal Date), Column E (Rate Change) into your template text

Step 4 — Mark as sent:

  • Add a Google Sheets action
  • Choose Update Spreadsheet Row
  • Find the same row and mark Column F (60-Day Sent) with today's date
  1. Test the Zap with a test row where the renewal date is exactly 60 days out
  2. Turn it on when the test succeeds

Repeat this process to build the 30-day and 7-day variations (checking Column G and H respectively).

Part 4: Maintain the spreadsheet

This automation only works if the spreadsheet stays current:

  • Add new renewals to the spreadsheet each week from your AMS renewal report
  • The automation handles the rest automatically
  • Review the "Sent" columns weekly to confirm emails are going out

Real Example: Small Independent Agency

Agency: 3 CSRs, 400 active personal lines clients, renewal reminders were inconsistent at best

Setup: Renewal Pipeline sheet populated from AMS weekly export, 3 Zapier Zaps (60/30/7 day), email templates written once

Input: CSR exports renewals from AMS Monday morning, pastes into Google Sheet — 5 minutes of work

Output: 60, 30, and 7-day reminder emails go out automatically to every renewing client with no CSR effort

Time saved: 30-60 minutes/week previously spent manually tracking and sending reminders; more consistent outreach means better retention

What to Do When It Breaks

  • Emails going to wrong clients → Check the row mapping in your Zapier Gmail step — make sure Client Email maps to Column B, not another column
  • Emails going out multiple times → The "Mark as Sent" step is critical — make sure it's writing to the correct column; without it, Zapier will re-send every time the row is updated
  • Date filter not working → Zapier's date math can be tricky; try the Formatter step to convert your date column to a consistent format (YYYY-MM-DD) before calculating days remaining
  • Zapier task limit hit on free plan → Free plan allows 100 tasks/month; if you have more renewals, upgrade to Zapier Starter ($19.99/month) or reduce to just 2 reminders (30 and 7 day)

Variations

  • Simpler version: Use only one reminder (30-day) and a single Zap — achieves 80% of the value with 40% of the setup effort
  • Extended version: Add a fourth Zap that fires the day after renewal to send a "your policy has renewed" confirmation email — closes the loop professionally

What to Do Next

  • This week: Build the spreadsheet and write the email templates; even before the automation is running, you now have a structured renewal pipeline
  • This month: Get the first Zap running (60-day reminders), confirm it works for a full week, then add the 30 and 7-day automations
  • Advanced: Connect the automation to your AMS data export — instead of manually copying renewals into Google Sheets, have the AMS export scheduled weekly

Advanced guide for insurance customer service representatives. Free Zapier accounts support up to 100 tasks/month — check your renewal volume before building to ensure you stay within the free tier limit.