Skip to content
P
AI for Hospice Coordinator

Automation Recipe: Bereavement Follow-Up Tracker with Zapier

For Hospice Coordinators ·

Zapier

For Hospice Coordinators

Tools: Zapier + Google Sheets + Gmail | Time to build: 2 hours | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using Google Sheets and Gmail. see Level 2 guides for these tools

What This Builds

A simple automation that, when you enter a patient's death date into a Google Sheet, automatically creates calendar reminders and email drafts for each bereavement follow-up interval (1 month, 3 months, 6 months, 13 months). No more manually tracking which families are due for a bereavement letter, the system tells you, and even pre-drafts the email.

Prerequisites

  • Zapier account. free tier works for basic version; Zapier Professional at $29.99/month/month for multi-step automation
  • Google Sheets (free)
  • Gmail or Outlook (for email drafts)
  • Google Calendar (for reminders)
  • Comfortable with basic spreadsheet use

The Concept

Zapier is like an automatic trigger system between apps. You tell it: "When something happens in App A, do something in App B." In this case: "When I add a new row to my bereavement tracking spreadsheet, automatically create 4 Google Calendar events, one at each bereavement milestone." It's like having an assistant who reads your spreadsheet and sets all the reminders for you.

Build It Step by Step

Part 1: Set up your Google Sheet

  1. Create a new Google Sheet named "Bereavement Tracking 2026"
  2. Create these column headers in Row 1:
  • A: Family Last Name
  • B: Patient Initials
  • C: Date of Death (format: MM/DD/YYYY)
  • D: Primary Survivor Name
  • E: Relationship to Patient
  • F: Personal Detail (one memory/interest)
  • G: Email Address (if communicating by email)
  • H: Phone (if mailing letters)
  • I: 1-Month Done (Y/N)
  • J: 3-Month Done (Y/N)
  • K: 6-Month Done (Y/N)
  • L: 13-Month Done (Y/N)
  1. Fill in a test row with a real or fictional family (for Zapier testing)

What you should see: A clean spreadsheet with headers in Row 1 and one data row in Row 2.

Part 2: Create a free Zapier account

Go to zapier.com and click Sign up free. Connect your Google account. Zapier needs permission to read your Google Sheets and write to Google Calendar.

Part 3: Create your first Zap

Click Create Zap (orange button). You'll build a trigger-action pair.

Set the Trigger:

  • App: Google Sheets
  • Trigger event: New Spreadsheet Row
  • Connect your Google account when prompted
  • Select your "Bereavement Tracking" spreadsheet
  • Select the sheet tab (usually "Sheet1")
  • Click Continue and test with your sample row

What you should see: Zapier pulling in your test row data and showing you each column as a variable (e.g., "Date of Death: 03/15/2026", "Family Last Name: Smith").

Part 4: Add your first action. 1-month calendar event

Click the + to add an action:

  • App: Google Calendar
  • Action: Create Detailed Event
  • Calendar: Select your work calendar
  • Summary: "Bereavement Letter Due: [Family Last Name]" (click the field, then select "Family Last Name" from the trigger data)
  • Start Date: Click the field → use Zapier's date formatter. In a separate step, add a Formatter by Zapier step: Action = Date/Time → Add/Subtract Time → Input = Date of Death field → Transform = Add 1 Month. Use the output as the Start Date for this calendar event.
  • Description: "1-month bereavement follow-up for [Patient Initials]. Personal detail: [Personal Detail]. Send letter or call."

What you should see: A configured action that will create a calendar event automatically.

Part 5: Duplicate for 3-month, 6-month, and 13-month milestones

Add three more actions (or three more separate Zaps for simplicity):

  • 3-month: Add 3 months to date of death
  • 6-month: Add 6 months to date of death
  • 13-month: Add 13 months to date of death

Each creates a separate Google Calendar event on the correct date with the family's name and personal detail in the description.

Part 6: Test and activate

Click Test in Zapier to run a test with your sample row. Check your Google Calendar, four events should appear on the correct dates. If they look right, click Turn on Zap. The automation is now live.

Part 7: Optional. add an email draft action

Add a Gmail action: Create Draft → pre-populate the To field, Subject ("Thinking of you"), and Body with a bereavement letter template that includes the Personal Detail field. This creates a draft in your Gmail that you review and send on the due date, one click versus 20 minutes of writing.

Real Example: Full Workflow

Setup: Bereavement tracking sheet with automation running.

Input: You enter a new row after a patient death: "Family: Johnson | Patient: R.J. | Date of Death: 02/01/2026 | Survivor: Mary Johnson | Relationship: Wife | Personal Detail: He loved woodworking and made furniture for all their grandchildren."

Output: 4 Google Calendar events automatically appear:

  • March 1, 2026: "Bereavement Letter Due. Johnson (1 month)" with Mary's personal detail in description
  • May 1, 2026: "Bereavement Letter Due. Johnson (3 months)"
  • August 1, 2026: "Bereavement Letter Due. Johnson (6 months)"
  • March 1, 2027: "Bereavement Letter Due. Johnson (13 months)"

Time saved: You never have to manually calculate or calendar these dates again. With 30-50 deaths per year, this eliminates hours of administrative tracking and eliminates the risk of missing a CMS-required follow-up.

What to Do When It Breaks

  • Zap triggers on every row, not just new ones → Ensure you're using "New Spreadsheet Row" trigger, not "New or Updated Row"
  • Date math produces wrong dates → Test the Formatter step with a known date and verify the output before connecting to Calendar
  • Calendar events appear on wrong user's calendar → Re-connect the Google Calendar app in Zapier and select the correct calendar account
  • Zap runs but no event appears → Check Zapier's "Task History" for error messages; most common issue is permission scope

Variations

  • Simpler version: Skip the email draft action. just the calendar reminders alone save significant tracking time
  • Extended version: Add a Slack notification action ("Bereavement letter due for Johnson family today") to notify your whole team when a letter is due

What to Do Next

  • This week: Build the sheet and test the Zap with two or three families
  • This month: Backfill your existing bereaved families into the sheet so all current follow-up is automated
  • Advanced: Connect the Gmail draft action so every due date also creates a pre-written letter draft in your inbox

Advanced guide for hospice coordinator professionals. Requires Zapier account (free or $29.99/month/month for multi-step). These automations require some technical comfort with connecting web apps.