P
AI for Hospice Coordinator

Automation Recipe: Bereavement Follow-Up Tracker with Zapier

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 {{tool:Zapier.price}}/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)
  3. 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 {{tool:Zapier.price}}/month for multi-step). These automations require some technical comfort with connecting web apps.