Automation: Build a Required Monthly Contact Reminder System

What This Builds

An automated system that checks your case tracker every morning and sends you an email listing which families require contact within the next 7 days and which contacts are already overdue. You'll never miss a required monthly contact again — and if a case goes to audit or court, you have documented proof of your contact schedule.

This addresses a genuine child safety issue: required monthly contacts exist because regular contact helps workers detect deteriorating conditions before they become crises.

Prerequisites

• Google Sheets case tracker (built in Level 2 guide, or equivalent)
• Gmail account (personal is fine)
• A Zapier account — free tier covers this automation
• Basic comfort with Google Sheets

The Concept

Zapier is like an automatic assistant that checks things for you and takes action based on what it finds. In this case: every morning at 7am, Zapier looks at your case tracker spreadsheet, finds all rows where a contact is due within 7 days or already overdue, and emails you a summary.

It's like having someone review your caseload tracker every morning and leave you a note: "These families need contact this week."

Build It Step by Step

Part 1: Set up your Google Sheet case tracker

If you built this in the Level 2 guide, great — use that. If not, create a Google Sheet with these columns:

| A: Case Code | B: Contact Required Every (days) | C: Last Contact Date | D: Next Contact Due | E: Status |

In column D, use this formula: =C2+B2 (adds the required interval to the last contact date)

Add a column F: =IF(D2<TODAY(),"OVERDUE",IF(D2<TODAY()+7,"DUE SOON","OK"))

Enter your cases (use codes, not real names).

Part 2: Create a Zapier account

Go to zapier.com and create a free account with your personal email. The free tier allows 100 "tasks" per month — easily enough for this automation.

What you should see: Zapier's dashboard with a "Create Zap" button.

Part 3: Create your morning digest Zap

Click "Create Zap." This automation has two parts: a Trigger (what starts it) and an Action (what happens).

Trigger — Schedule:

1. Search for "Schedule" in the trigger list
2. Select "Schedule by Zapier" → "Every Day"
3. Set time: 7:00 AM in your timezone
4. Click Continue

Action — Google Sheets search:

1. Add an Action → search for "Google Sheets"
2. Select "Find Multiple Spreadsheet Rows"
3. Connect your Google account
4. Select your case tracker spreadsheet and the correct sheet tab
5. In "Filter by Column," select column F (your Status column)
6. In "Filter Value," type "OVERDUE"
7. Click Continue and test

What you should see: Zapier finds your overdue rows and shows them as test data.

Part 4: Add a second search for "DUE SOON"

Click the "+" to add another step. Repeat the Google Sheets search, but this time filter for "DUE SOON."

Part 5: Send yourself an email digest

Add a final Action: "Gmail → Send Email"

1. Connect your Gmail
2. To: your email address
3. Subject: "Case Contact Reminders — [Date]"
4. Body: Use Zapier's data formatting to build a message like:

OVERDUE CONTACTS:
{{Step 3 results — Case Code, Last Contact, Days Overdue}}

CONTACTS DUE IN 7 DAYS:
{{Step 4 results — Case Code, Next Contact Due}}

1. Turn the Zap on.

What you should see: A test email showing your overdue and upcoming contacts formatted as a clear list.

Part 6: Update the tracker after every contact

The automation is only useful if the tracker is current. After every family contact, open the sheet and update column C (Last Contact Date) — it takes 10 seconds. Column D and F recalculate automatically.

Real Example: A Monday Morning

Setup: You built this Zap in February. You have 24 active cases.

What happens at 7am on a Monday:

• Zapier wakes up, checks your Google Sheet
• Finds: 2 cases are OVERDUE (missed last week), 4 cases are DUE SOON (due within 7 days)
• Sends you an email before you've opened your laptop

Email subject: "Case Contact Reminders — Monday March 16" Email body:

OVERDUE CONTACTS (2):
- SM family: Last contact 2/28, 16 days overdue (required every 30 days)
- TK family: Last contact 3/1, 15 days overdue (required every 30 days)

DUE SOON THIS WEEK (4):
- AR family: Due 3/17 (required every 14 days — high risk)
- BJ family: Due 3/18 (required every 30 days)
- CL family: Due 3/20 (required every 30 days)
- DP family: Due 3/21 (required every 30 days)

What you do: Adjust your weekly schedule to hit these 6 families first.

Outcome: You go 6 months without missing a required contact. When a case goes to QSR audit, you have a clean contact record and the automated logs as backup.

What to Do When It Breaks

• Zap doesn't find overdue rows → Check that the formula in column F is correct; make sure "OVERDUE" text matches exactly what you typed in the Zapier filter
• Email doesn't format nicely → In the Gmail action body, use line breaks between cases and label each section clearly
• Too many or too few cases showing up → Check that all cases have dates entered in column C and formulas in columns D and F
• Zapier free tier limit hit → 100 tasks/month covers up to 3 daily automations with small datasets; if you hit the limit, upgrade to Starter ($20/mo) or simplify to weekly digest

Variations

• Simpler version: Instead of Zapier, just set a weekly recurring calendar event: "Review case tracker" — less automatic but achieves the same awareness
• Extended version: Add court date reminders by creating a separate "court dates" sheet and a second Zap that emails you 7 days and 2 days before each scheduled hearing

What to Do Next

• This week: Build the Google Sheet tracker and get your data in — even if you don't build the Zap yet, the tracker alone helps
• Weekend: Set up the Zap when you have time to experiment without time pressure
• This month: After the first 30 days, check whether you've missed any contacts — compare to before the system

Advanced guide for child welfare caseworker professionals. Use case codes, not real names, in your tracking spreadsheet. This is a personal productivity tool — your official case contact records remain in your agency's case management system.