Zapier Automation: Aging Accounts Receivable Alerts

What This Builds

You'll set up an automation that monitors a spreadsheet of outstanding insurance claims and automatically sends you (or your billing team) an alert when claims pass 30, 60, or 90 days without payment. Instead of manually reviewing the aging report daily, you get a daily email digest of claims that need follow-up — organized by urgency.

Most practices lose 3–8% of net production to claims that age past 90 days and never get collected. This automation keeps every claim visible and prompts action before it's too late.

Prerequisites

• Free Zapier account at zapier.com (basic plan handles this)
• Google Sheets (free) for your claims tracker
• Gmail account for receiving alerts (or Slack if your practice uses it)
• Your practice management software must be able to export claims data (most PMS systems can export to CSV/Excel)
• Time: 1-2 hours to build; 0 minutes per day afterward (fully automated)

The Concept

Your practice management software (Dentrix, Eaglesoft, Open Dental) generates aging reports — but they're manual: you have to remember to look at them. Zapier checks your Google Sheets tracker automatically and pushes alerts to your inbox when a claim hits an aging threshold. The result: proactive AR management without daily manual review.

Build It Step by Step

Part 1: Set up your claims tracker in Google Sheets

Export your current outstanding claims from your practice management software and paste them into Google Sheets. Your sheet needs these columns:

Important: Add the formula =IF(G2="Paid","",TODAY()-E2) in column H — this auto-calculates days outstanding for unpaid claims and clears when status changes to "Paid."

Add a few test rows with older Claim Dates to verify the formula works.

Part 2: Create the 30-Day Alert Zap

1. Log into zapier.com, click Create Zap
2. Trigger: Google Sheets → New or Updated Spreadsheet Row

   • Connect your Google account
   • Select your practice's claims tracker spreadsheet and sheet tab
   • Set Trigger Column to H (Days Outstanding)
3. Filter step: Add Zapier's Filter by Zapier

   • Condition 1: Days Outstanding (Column H) is greater than 29
   • Condition 2: Status (Column G) does not equal "Paid"
   • Condition 3: Status does not equal "Written Off"
   • Condition 4: Alert Sent 30d (Column I) is empty
4. Action: Gmail → Send Email

   • To: your billing coordinator email address
   • Subject: "Insurance Claim Needs Follow-Up — 30+ Days — [Payer] [Claim ID]"
   • Body: "Claim Details: Patient: [Column B], Payer: [Column C], Procedure: [Column D], Submitted: [Column E], Amount: [Column F], Days Outstanding: [Column H]. Action: Call payer to check status or resubmit if needed."
5. Final step: Update Google Sheets → Update Spreadsheet Row

   • Set Alert Sent 30d (Column I) to today's date
6. Name and turn on: "AR Alert — 30 Day Claims"

Part 3: Create the 60-Day Alert Zap

Duplicate the 30-day Zap and modify:

• Filter: Days Outstanding > 59 AND Alert Sent 60d is empty
• Subject: "URGENT — 60+ Day Claim — [Payer] [Claim ID]"
• Body: add "Consider: (1) Verify claim was received, (2) Check for missing documentation, (3) File appeal if denied, (4) Escalate to payer supervisor if no response."
• Update Alert Sent 60d column

Part 4: Create the 90-Day Alert Zap

Duplicate again for 90 days:

• Filter: Days Outstanding > 89 AND Alert Sent 90d is empty
• Subject: "CRITICAL — 90+ Day Claim — Consider Write-Off Review — [Payer]"
• Body: add "At 90+ days, evaluate: (1) Is this worth appealing? (2) Should this go to secondary insurance? (3) Is this a patient balance? (4) Consider write-off discussion with office manager."
• Update Alert Sent 90d column

Part 5: Weekly digest option (bonus)

Instead of individual alerts, you can set up a weekly summary:

1. Create a new Zap: Schedule by Zapier → Every Monday at 8am
2. Action: Google Sheets → Look Up Spreadsheet Row (multiple) — filter for Status = "Pending" AND Days Outstanding > 29
3. Action: Gmail → Send Email with a summary of all pending claims over 30 days

This gives you a Monday morning action list without individual alert emails throughout the week.

Real Example: Full Automated Workflow

Setup: You enter 45 outstanding claims into your Google Sheets tracker after exporting from Dentrix. Zapier is watching.

Day 31: Claim #7842 (Delta Dental, $480 crown) has been pending 31 days. Zapier detects it crosses the 30-day threshold. You receive an email at 9am: "Claim #7842 — Delta Dental — D2750 Crown — $480 — 31 days — needs follow-up." You call Delta, learn it was pended for a missing X-ray. You resubmit with the X-ray and update the Notes column.

Day 62: Claim #7715 (Cigna, $1,200 implant) passes 60 days. You receive the URGENT alert. You discover it was denied (never noticed in the busy week). You file an appeal that day. You update Status to "Appealed."

Day 93: Claim #7601 (Blue Cross, $320 perio maintenance) reaches 90 days. The CRITICAL alert comes in. You review — it was denied for frequency limitation and you missed it. You update Status to "Written Off" and notify the dentist.

Result: Zero claims fell through the cracks. AR collection improved by catching denials earlier.

What to Do When It Breaks

• Zap not triggering → Zapier free accounts check for triggers every 15 minutes. If a claim crosses a threshold but the Zap hasn't fired, wait up to 15 minutes or manually trigger a test run.
• Duplicate alerts sent → The "Alert Sent" column should prevent this. Check that your update step is writing to the correct column. If the column update failed, the filter won't catch that the alert was already sent.
• Wrong claims showing up → Check your Status filter conditions. If a "Paid" claim is appearing in alerts, verify that "Paid" is entered exactly as the filter expects (case-sensitive matching in some Zapier configurations).
• Days Outstanding formula not calculating → Make sure column H uses the exact formula: =IF(G2="Paid","",TODAY()-E2). If you exported claim dates in a non-standard format, the date math may fail — reformat the dates as MM/DD/YYYY.

Variations

• Simpler version: Skip Zapier entirely. Set a recurring calendar reminder every Monday morning to open your aging report in Dentrix/Eaglesoft and review claims over 30 days. Lower tech, same discipline.
• Extended version: Add a fourth Zap that automatically sends a patient-facing email (through Gmail) when their claim reaches 60+ days unpaid, informing them that their insurance hasn't paid and they may receive a statement soon. Requires adding a patient email column to your tracker.

What to Do Next

• This week: Set up the tracker and build just the 30-day alert Zap — get comfortable with it before adding 60 and 90 day versions
• This month: Use it for a full billing cycle; adjust thresholds if 30 days is too early for your common payers (some payers legitimately take 45 days)
• Advanced: Add a column for "Secondary Insurance" and build a Zap that alerts you specifically when primary has paid but secondary hasn't been billed after 14 days

Advanced guide for Dental Insurance Coordinator professionals. These techniques use more sophisticated AI features that may require paid subscriptions.