Automation Recipe: Automate Lender Stip-Chasing with Zapier

For F&I Managers

Tools: Zapier + Google Sheets + Gmail | Time to build: 1.5–2 hours | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using Google Sheets and Gmail — see Level 2 guides: "Use Google Sheets AI to Track Your Monthly PVR" and "Use Gmail's Help Me Write for Post-Delivery Emails"

What This Builds

Instead of manually tracking which customers still owe you documents and writing follow-up messages one at a time, this automation watches a Google Sheets deal log and sends personalized email follow-ups automatically — and reminds you 24 hours later if the customer hasn't responded. You get notified only when action is needed; the routine follow-up happens without you.

By the end, you'll have a system where logging "paystub needed" in your spreadsheet is all it takes to trigger a customer email, a 24-hour reminder, and a flag when it's resolved.

Prerequisites

• Google account with Gmail and Sheets access
• Zapier account (free plan handles this use case) at zapier.com
• Your stip-tracking spreadsheet set up (see Level 2 PVR Tracker guide)
• 1.5–2 hours uninterrupted (best done on a slow weekday afternoon)

The Concept

Think of Zapier as a rule-following assistant: "When X happens, do Y." In this case, the rule is: "When a new row appears in my Google Sheets with a stip status of 'Pending,' send an email to the customer asking for the document."

You write the trigger condition once. Zapier checks for new rows every few minutes and handles the rest automatically. You never have to manually send another stip-chasing email.

Build It Step by Step

Part 1: Set Up Your Stip-Tracking Sheet

First, add a dedicated stip section to your Google Sheets deal log. Create a new sheet tab called "Stip Tracker" with these columns:

The trigger column is G (Status). When you type "Pending" in that column for a new row, Zapier will fire.

Why this structure matters: Zapier watches for new rows where Status = "Pending." When it finds one, it reads the other columns to personalize the email. This single sheet becomes your stip dashboard.

Part 2: Build the Zapier Workflow

1. Go to zapier.com and click "Create" → "New Zap"

2. Set your Trigger:

   • Search for "Google Sheets" and select it
   • Choose "New or Updated Spreadsheet Row" as the trigger event
   • Connect your Google account when prompted
   • Select your spreadsheet name and "Stip Tracker" as the worksheet
   • In the "Trigger Column" field (this is key), select Column G (Status)
   • Set the value to watch for: "Pending"
   • Click "Test trigger" — if your sheet has existing rows with "Pending," Zapier will find one to confirm it's working

   What you should see: Zapier shows you the row data it found, including the customer name, email, and stip description.

3. Add a Filter step (prevents duplicate emails):

   • Click the "+" button after the trigger to add a step
   • Search for "Filter" and select "Filter by Zapier"
   • Set condition: Column H (Email Sent) Does not contain "Yes"
   • This prevents Zapier from sending a second email if you update the row later

   Why this matters: Without this filter, any edit to that row might trigger another email. The filter ensures each stip only gets one automatic email.

4. Set your Action — Gmail: Send Email:

   • Click "+" to add the action step
   • Search for "Gmail" and select "Send Email"
   • Connect your Gmail account
   • Configure the email:

     • To: Select the Customer Email column from your sheet data
     • Subject: "Quick follow-up on your [Column D — Stip Needed] — [Column A — Deal Number]"
     • Body: Write your template message (see example below)

   Sample email body to copy-paste:

   Copy and paste this

   Hi [Customer First Name],
   
   Just a quick follow-up — we're working to get your loan funded and our lender needs your [Stip Needed] before we can finalize.
   
   Could you email it to [your email address] or text a photo to [your cell]? The sooner we receive it, the faster everything gets wrapped up.
   
   Thanks for your patience — you're almost done!
   
   [Your name]
   [Dealership name]
   [Your direct number]
   

   Use Zapier's data fields (the blue chip items) to insert the customer's first name and the stip description automatically.

5. Add a second action — Update Google Sheets row:

   • Click "+" after the Gmail action
   • Search for "Google Sheets" and select "Update Spreadsheet Row"
   • Select your spreadsheet and "Stip Tracker" worksheet
   • Set Column H (Email Sent) to "Yes"
   • This marks the row so the filter prevents duplicate emails

6. Name your Zap and turn it on:

   • Give it a name: "F&I Stip Follow-Up Automation"
   • Click "Publish" to activate it
   • Toggle the Zap to "On"

Part 3: Test and Refine

1. Add a test row to your Stip Tracker with your own email address in the Customer Email column and "Pending" in Status
2. Wait up to 15 minutes (Zapier checks on a schedule on the free plan)
3. Check your inbox — you should receive the email with your name and stip description filled in correctly

What good output looks like: You receive an email that reads naturally — "Hi [Your Name], Just a quick follow-up — we're working to get your loan funded and our lender needs your paystub before we can finalize." The customer name and stip type were pulled from your spreadsheet automatically.

Troubleshooting:

• No email received: Check the Zap History tab in Zapier to see if it ran and what happened. Common issue: Gmail connection expired — reconnect in Zapier's Connected Accounts.
• Email body shows "[Customer First Name]" literally: You used plain text instead of Zapier's data fields. Edit the Zap and click the blue "+" in the body to insert the actual field.
• Getting duplicate emails: Check that the Filter step is active and that Column H is updating correctly after sending.

Real Example: From Logging to Customer Email in Under 15 Minutes

Setup: You have 4 pending deals that need stips: paystub (2 customers), proof of insurance (1 customer), and a utility bill (1 customer).

Input: You open your Stip Tracker sheet and add all 4 rows. For each one: deal number, customer name, customer email, what's needed, lender, and "Pending" in the Status column.

Output: Within 15 minutes, all 4 customers receive a personalized email asking for their specific document. You haven't written a single message. You didn't pick up the phone. The "Email Sent" column shows "Yes" on all 4 rows.

Time saved: What previously took 45–60 minutes of individual follow-up (writing, sending, logging) now takes 5 minutes of data entry.

What to Do When It Breaks

• Zap shows "off" / turned itself off → Usually means the Google Sheets or Gmail connection expired. Go to zapier.com → Connected Accounts → reconnect both. Then re-enable the Zap.
• Emails are going to spam for customers → Add a sentence to your email body with the dealership name and deal number prominently — transactional emails with identifying info are less likely to be flagged.
• A customer gets two emails → Check the Filter step — the Column H "Email Sent = Yes" check may not have triggered correctly. Look at the Zap History for that run to diagnose.
• Zapier stops working during a busy month → The free Zapier plan has a limit (100 tasks/month). If you're doing high volume, upgrade to the Starter plan (~$20/month) or use Google Sheets' built-in AppScript as an alternative.

Variations

• Simpler version: Skip Zapier entirely — use the Level 1 prompt guide to generate the email text quickly, then send manually. This automation saves the most time when you have 10+ pending stips at once.
• Extended version: Add a second Zap that triggers a 24-hour reminder email if Status is still "Pending" the next day. This requires a more complex date-comparison filter — start with the simple version first.

What to Do Next

• This week: Build the basic version, test with your own email, then use it on your next real stip situation
• This month: Track how many emails it sends automatically vs. how many you manually follow up on
• Advanced: Connect a text messaging service (like Twilio or SimpleTexting via Zapier) to send SMS instead of email — text response rates are 5–10x higher than email

Advanced guide for F&I Manager professionals. Zapier's interface updates regularly but the core workflow logic (trigger → filter → action) is stable. Free plan handles up to 100 task runs per month.