Implementation Guide: Trigger annual review scheduling workflows based on client anniversary dates

Hardware Procurement

No Dedicated Hardware Required

This project is entirely cloud-based. All processing, storage, and workflow orchestration occurs on SaaS platforms (CRM, Zapier, Calendly, Microsoft 365). The client's existing workstations and standard business internet (25+ Mbps) are the only infrastructure requirements. No servers, edge devices, or specialized networking equipment are needed.

Software Procurement

Prerequisites

• Active CRM subscription (Redtail CRM Growth plan or Wealthbox Launch plan) with API access enabled and admin credentials available to the MSP technician
• Microsoft 365 Business Premium (or equivalent) deployed for all advisors with Exchange Online mailboxes active and properly configured SPF, DKIM, and DMARC records for the firm's email domain
• Client anniversary dates (or account inception dates) populated in the CRM for at least 80% of active client households — this is the single most critical prerequisite and must be audited before project kickoff
• Client email addresses validated and current in CRM for all active client households — bounce rate must be below 5%
• Each client record must have an assigned primary advisor in the CRM to enable proper routing of tasks and scheduling links
• Client segmentation/tier field populated (e.g., A/B/C clients or Platinum/Gold/Silver) to determine review frequency: A-clients may get quarterly reviews, B-clients semi-annual, C-clients annual
• Chief Compliance Officer (CCO) identified and available for template review sessions — all automated email templates require CCO pre-approval before deployment
• The firm's Written Supervisory Procedures (WSP) document is accessible and the CCO is prepared to update it to include the new automation workflow
• Business internet connection of 25+ Mbps at all office locations with unrestricted HTTPS access to Zapier, Calendly, CRM, and Microsoft 365 endpoints
• Modern web browsers (Chrome 90+, Edge 90+, or Safari 15+) installed on all advisor workstations
• Email journaling or archiving solution in place or budgeted (M365 In-Place Archive with Litigation Hold at minimum, or Smarsh for broker-dealer firms)
• Existing Calendly account or willingness to adopt Calendly — if the firm already uses a different scheduling tool (Acuity, Microsoft Bookings), the integration approach may need adjustment

Installation Steps

Step 1: Discovery & CRM Data Audit

Conduct a thorough audit of the client's CRM to assess data quality and readiness for automation. Export all active client records and analyze completeness of critical fields: anniversary/inception date, email address, assigned advisor, and client tier. Document the firm's current annual review process (manual steps, email templates used, frequency by client tier, compliance review procedures). Meet with the CCO to understand regulatory requirements specific to this firm (SEC-registered RIA vs. FINRA broker-dealer vs. dual-registered).

# Redtail CRM: Export contacts via Reports > Contact Reports > All Active Contacts
# Include fields: Contact ID, First Name, Last Name, Email, Anniversary Date, Last Review Date, Category/Tag, Assigned Advisor
# Wealthbox: Export via Contacts > Filter: Status = Active > Export to CSV
# Run data quality check:
# In Excel/Google Sheets, use COUNTBLANK() on the Anniversary Date column
# Formula: =COUNTBLANK(D2:D500)/COUNTA(A2:A500) — this gives % of records missing anniversary dates
# Target: <20% missing (ideally <5%)

Step 2: CRM Data Cleanup & Standardization

Clean and standardize CRM data to ensure the automation triggers fire correctly. Populate missing anniversary dates, validate email addresses, ensure consistent advisor assignments, and establish client tier classifications if they don't exist. Create or verify that the CRM has the following custom fields: 'Next Review Date' (date), 'Review Frequency' (dropdown: Annual/Semi-Annual/Quarterly), 'Auto-Schedule Opt-Out' (checkbox), and 'Last Review Email Sent' (date).

• Redtail CRM: Navigate to Settings > Custom Fields > Contact Fields
• Add custom fields if not present: Field Name: 'Next Review Date' | Type: Date
• Add custom fields if not present: Field Name: 'Review Frequency' | Type: Dropdown (Annual, Semi-Annual, Quarterly)
• Add custom fields if not present: Field Name: 'Auto-Schedule Opt-Out' | Type: Checkbox
• Add custom fields if not present: Field Name: 'Last Review Email Sent' | Type: Date
• Wealthbox CRM: Navigate to Settings > Custom Fields > Contacts — Add equivalent fields using Wealthbox custom field builder
• For bulk date population in Redtail: Use Redtail's Import feature: Contacts > Import > CSV Upload
• Map 'Anniversary Date' column to the Redtail Anniversary field
• Map 'Next Review Date' column to the custom Next Review Date field

# Calculate Next Review Date in spreadsheet before import:
# For annual reviews: =DATE(YEAR(TODAY()),MONTH(AnniversaryDate),DAY(AnniversaryDate))
# If that date has passed: =DATE(YEAR(TODAY())+1,MONTH(AnniversaryDate),DAY(AnniversaryDate))

Step 3: Configure Zapier Account & Connect CRM

Set up the Zapier Team account, invite the MSP admin and client admin (CCO or operations manager) as team members, and establish the CRM connection. Create a dedicated Zapier folder/workspace for this client's review automation workflows.

Step 4: Configure Calendly Teams & Create Event Types

Set up Calendly Teams for the advisory firm, create advisor-specific event types for annual reviews, and configure confirmation/reminder email sequences within Calendly. Each advisor needs their own Calendly profile linked to their Outlook/Google calendar for real-time availability checking.

Step 5: Connect Calendly to Zapier

Establish the Calendly connection in Zapier to enable bidirectional data flow: scheduled events from Calendly will update the CRM, and CRM triggers will generate Calendly scheduling links in outbound emails.

Step 6: Design & Get Compliance Approval for Email Templates

Create all email templates that will be sent by the automation workflow. Every template must be reviewed and approved by the firm's Chief Compliance Officer (CCO) before deployment. Templates must not contain performance claims, testimonials, or any language that could be construed as investment advice. Include the firm's required compliance disclosures in the footer.

Step 7: Configure Email Journaling for Compliance Archiving

Set up email journaling so that every automated email sent by the workflow is captured in the firm's compliance archive. This satisfies SEC Rule 17a-4 and FINRA Rules 3110/4511 requirements. For M365 environments, configure In-Place Archive with Litigation Hold. For broker-dealer firms or firms requiring WORM storage, configure Smarsh journal transport rules.

# Option A: Microsoft 365 In-Place Archive + Litigation Hold (for RIA firms)
# 
# 1. Enable In-Place Archive for each advisor mailbox:
#    Microsoft 365 Admin Center > Users > Active Users > [Select User]
#    > Mail > Manage email apps > Enable 'Archive mailbox'
#    OR via PowerShell:
Connect-ExchangeOnline -UserPrincipalName admin@firmname.com
Enable-Mailbox -Identity advisor@firmname.com -Archive

# 2. Create a Litigation Hold on the mailbox:
Set-Mailbox -Identity advisor@firmname.com -LitigationHoldEnabled $true -LitigationHoldDuration 2555
# Note: 2555 days = ~7 years (exceeds FINRA 6-year requirement)

# 3. Create a Transport Rule to tag automated emails:
#    Exchange Admin Center > Mail Flow > Rules > Add a rule
#    Condition: Sender is advisor@firmname.com AND Subject contains 'Annual Portfolio Review' OR 'Annual Review'
#    Action: Add X-Header 'X-AutoReview: true'
#    This tags automated emails for easy search/filtering in compliance reviews

# Option B: Smarsh Journal Transport (for broker-dealer firms)
#
# 1. Obtain Smarsh journal address from Smarsh portal (format: journalid@journal.smarsh.com)
# 2. Configure Exchange Online journal rule:
New-JournalRule -Name 'Smarsh Compliance Journal' -JournalEmailAddress journalid@journal.smarsh.com -Scope Global -Enabled $true
# 3. Verify journal delivery in Smarsh portal > Archive > Recent Messages

Step 8: Build the Primary Zapier Workflow — Anniversary Date Trigger

Build the core Zap that runs daily, checks for clients whose anniversary dates are approaching (within the configured lead time window), and initiates the review scheduling sequence. This is the master workflow that orchestrates the entire process. Due to Zapier's limitations with date-based CRM queries, the approach uses a Schedule trigger combined with a CRM search to identify clients due for review.

// TRIGGER: Schedule by Zapier
//   Frequency: Every Day
//   Time: 8:00 AM (firm's local timezone)
//   Day of Week: Monday through Friday only

// STEP 2: Code by Zapier (JavaScript) — Calculate Target Date
const today = new Date();
const targetDate = new Date(today);
targetDate.setDate(targetDate.getDate() + 30); // 30-day lead time
const targetMonth = String(targetDate.getMonth() + 1).padStart(2, '0');
const targetDay = String(targetDate.getDate()).padStart(2, '0');
output = [{targetMonth: targetMonth, targetDay: targetDay, todayISO: today.toISOString().split('T')[0]}];

// STEP 3: Webhooks by Zapier (GET) — Query CRM for Matching Clients
//   For Redtail:
//     URL: https://smf.crm3.redtailtechnology.com/api/public/v1/contacts/search
//     Headers: Authorization: Basic [base64-encoded API key]
//             Content-Type: application/json
//     Query: Filter contacts where anniversary month/day matches target
//   For Wealthbox:
//     URL: https://api.crmworkspace.com/v1/contacts
//     Headers: Authorization: Bearer [access_token]
//     Query params: Filter on custom date fields

// STEP 4: Filter by Zapier — Only Continue If Clients Found
//   Condition: Response body 'contacts' array length > 0

// STEP 5: Looping by Zapier — For Each Client in Results
//   For each matching client:

// STEP 5a: Filter — Skip if Auto-Schedule Opt-Out is TRUE
//   Condition: client.opt_out != true

// STEP 5b: Filter — Skip if Last Review Email Sent < 30 days ago
//   Condition: client.last_review_email_sent is blank OR > 30 days ago

// STEP 5c: Lookup — Get Advisor's Calendly Link
//   Use a Zapier Lookup Table:
//     Advisor Name → Calendly URL mapping
//     e.g., 'Jane Smith' → 'https://calendly.com/jane-smith-advisors/annual-review'

// STEP 5d: Email by Zapier (or Outlook Send Email) — Send Review Invitation
//   To: {{client.email}}
//   From: {{advisor.email}} (via Outlook connected account)
//   Subject: It's Time for Your Annual Portfolio Review, {{client.first_name}}
//   Body: [Template 1 — HTML format with Calendly link inserted]
//   Reply-To: {{advisor.email}}

// STEP 5e: Update CRM Record — Stamp 'Last Review Email Sent' Date
//   For Redtail: PUT to /api/public/v1/contacts/{{contact_id}}
//     Body: {"last_review_email_sent": "{{todayISO}}"}
//   For Wealthbox: PUT to /v1/contacts/{{contact_id}}
//     Body: {"custom_fields": {"last_review_email_sent": "{{todayISO}}"}}

// STEP 5f: Create CRM Task — 'Annual Review Due' for Advisor
//   For Redtail: POST to /api/public/v1/activities
//     Body: {
//       "subject": "Annual Review Due - {{client.full_name}}",
//       "due_date": "{{targetDate}}",
//       "assigned_to": "{{advisor.id}}",
//       "category": "Annual Review",
//       "priority": "High"
//     }

Step 9: Build the Follow-Up Reminder Workflow

Build a second Zap that checks for clients who received the initial review invitation but have not yet booked a meeting. This Zap sends follow-up reminders at 7-day and 14-day intervals, and after 21 days creates a task for the advisor to personally call the client.

const today = new Date();
const sevenDaysAgo = new Date(today);
sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 7);
const fourteenDaysAgo = new Date(today);
fourteenDaysAgo.setDate(fourteenDaysAgo.getDate() - 14);
const twentyOneDaysAgo = new Date(today);
twentyOneDaysAgo.setDate(twentyOneDaysAgo.getDate() - 21);
output = [{
  sevenDayTarget: sevenDaysAgo.toISOString().split('T')[0],
  fourteenDayTarget: fourteenDaysAgo.toISOString().split('T')[0],
  twentyOneDayTarget: twentyOneDaysAgo.toISOString().split('T')[0]
}];

Step 10: Build the Booking Confirmation & CRM Update Workflow

Build a third Zap that triggers when a client books a meeting through Calendly. This workflow updates the CRM with the meeting details, sends a confirmation email (optional if Calendly's built-in confirmations are enabled), and clears the client from the follow-up queue.

# Update contact to set next review date and clear follow-up flag

// Redtail — Update CRM Contact Record
// PUT /api/public/v1/contacts/{{contact_id}}
{
  "next_review_date": "{{calendly.event.start_time}}",
  "last_review_email_sent": null
}

# Create activity record for the booked annual review meeting

// Redtail — Create CRM Activity (Meeting Record)
// POST /api/public/v1/activities
{
  "subject": "Annual Review Meeting - {{client.full_name}}",
  "start_date": "{{calendly.event.start_time}}",
  "end_date": "{{calendly.event.end_time}}",
  "type": "Meeting",
  "category": "Annual Review",
  "notes": "Booked via Calendly automated scheduling. Meeting link: {{calendly.event.location}}"
}

Step 11: Build the Post-Meeting Follow-Up Workflow

Build a fourth Zap that fires after a review meeting has taken place, sending a thank-you email and updating the CRM to reset the annual review cycle for next year. This closes the loop and ensures the client's next anniversary trigger is properly configured.

• ZAP 4: Post-Meeting Follow-Up & Next Cycle Reset
• TRIGGER: Schedule by Zapier — Frequency: Every Day, Time: 10:00 AM, Day of Week: Monday through Friday only
• STEP 2: Query CRM for Completed Review Meetings — Search for activities/events where: Type = 'Annual Review', Date = yesterday (completed meetings), Status = 'Completed' (if CRM tracks meeting completion). Alternative: Use Calendly 'Event Ended' webhook if available
• STEP 3: Looping — For each completed meeting:
• STEP 3a: Send Post-Review Email — Template 5. To: {{client.email}}, From: {{advisor.email}} (via Outlook), Subject: Thank You for Meeting with Us, {{client.first_name}}, Body: [Template 5 with next steps, action items placeholder]
• STEP 3b: Update CRM Contact Record — Reset for Next Year. For Redtail: PUT /api/public/v1/contacts/{{contact_id}}
• STEP 3c: Create CRM Task — Post-Meeting Action Items. Subject: 'Complete Post-Review Tasks - {{client.full_name}}', Due: 5 business days from today, Assigned to: advisor, Notes: 'Update financial plan, process any allocation changes, send documents discussed in meeting'

# reset contact record for next review cycle

{
  "last_review_date": "{{meeting_date}}",
  "next_review_date": "{{meeting_date + review_frequency_interval}}",
  "review_meeting_booked": false,
  "follow_up_count": 0
}

Step 12: Configure SPF, DKIM, and DMARC for Email Deliverability

Ensure the firm's email domain has proper authentication records so automated review invitation emails are not flagged as spam. This is critical because spam-filtered emails mean missed client touchpoints and potential compliance issues (the firm thinks an email was sent, but the client never received it).

# 1. Verify current SPF record:
nslookup -type=TXT firmname.com
# Look for: v=spf1 include:spf.protection.outlook.com -all
# If missing or incomplete, update DNS:
# Add TXT record: v=spf1 include:spf.protection.outlook.com ~all

# 2. Verify DKIM:
# Microsoft 365 Admin Center > Settings > Domains > [firmname.com] > DNS Records
# Ensure CNAME records for selector1._domainkey and selector2._domainkey point to Microsoft
# Enable DKIM signing:
#   Exchange Admin Center > Mail Flow > DKIM > Select domain > Enable

# 3. Configure DMARC:
nslookup -type=TXT _dmarc.firmname.com
# If no record exists, add TXT record at _dmarc.firmname.com:
# v=DMARC1; p=quarantine; rua=mailto:dmarc-reports@firmname.com; pct=100

# 4. Test email deliverability:
# Send test emails to: https://www.mail-tester.com/
# Target score: 9/10 or higher
# Also test by sending to Gmail, Yahoo, and Outlook.com addresses

Step 13: Testing — Pilot with Test Client Records

Before going live, test the entire workflow end-to-end using 10–20 test or volunteer client records. Verify every step: trigger firing, email delivery, Calendly link functionality, CRM updates, follow-up timing, booking detection, post-meeting reset, and compliance archive capture.

# 1. Create 10-20 test contacts in CRM (or tag real volunteer clients)
#    Set anniversary dates across a range:
#    - 5 contacts: anniversary 30 days from today (should trigger immediately)
#    - 5 contacts: anniversary 7 days ago (should trigger follow-up)
#    - 5 contacts: anniversary 60 days from today (should NOT trigger yet)
#    - 3 contacts: with Auto-Schedule Opt-Out = TRUE (should be skipped)
#    - 2 contacts: with missing email addresses (should be skipped gracefully)

# 2. Manually run Zap 1 (or wait for scheduled trigger)
#    Zapier > Zap 1 > Run Now

# 3. Verify:
#    [ ] Initial emails sent to correct clients only
#    [ ] Emails NOT sent to opt-out clients
#    [ ] Emails NOT sent to clients with missing emails
#    [ ] Calendly links work and show correct advisor availability
#    [ ] CRM 'Last Review Email Sent' date updated
#    [ ] CRM task created for each triggered client
#    [ ] Emails captured in compliance archive (check M365 archive or Smarsh)

# 4. Book a test meeting via Calendly link
#    [ ] Zap 3 fires and updates CRM with meeting details
#    [ ] Client removed from follow-up queue
#    [ ] CRM activity created with meeting record

# 5. Wait 7 days (or manually adjust test dates) and verify:
#    [ ] Zap 2 sends follow-up to non-booked clients only
#    [ ] No follow-up sent to clients who already booked

Step 14: Update Written Supervisory Procedures (WSP) & Go-Live

Work with the firm's CCO to update the Written Supervisory Procedures document to include the new automated review scheduling workflow. This is a regulatory requirement. Then execute a controlled go-live, transitioning from the test pilot to the full client base.

WSP Update — Add the following sections:

• Section: 'Automated Client Communication — Annual Review Scheduling'
• Subsection a: Description of automated workflow (high-level)
• Subsection b: Email template approval process (CCO must approve before deployment)
• Subsection c: Supervisory review frequency (CCO samples 10% of automated emails monthly)
• Subsection d: Archive and retention policy (all emails archived for 7 years minimum)
• Subsection e: Opt-out process (how clients can stop receiving automated emails)
• Subsection f: Exception handling (what happens when automation fails)
• Subsection g: Annual review of the automation system itself

Go-Live Checklist

Phased Go-Live (recommended)

Custom AI Components

Anniversary Date Trigger Engine

Type: workflow Core deterministic workflow that runs daily via Zapier Schedule trigger, queries the CRM for clients whose anniversary dates fall within the configured lead-time window (default: 30 days ahead), filters out opted-out clients and those already in an active review cycle, and initiates the email sequence. This is not an AI component — it is a pure rule-based, date-math-driven automation workflow. It is the central orchestrator for the entire solution. Implementation:

• Zapier Workflow: Anniversary Date Trigger Engine
• Platform: Zapier Team Plan
• Trigger: Schedule by Zapier — Daily at 8:00 AM local time, Mon-Fri

Zap Configuration (Zap 1 of 4)

Step 1: Schedule Trigger

• Type: Schedule by Zapier
• Frequency: Every Day
• Time of Day: 08:00
• Days of Week: Monday, Tuesday, Wednesday, Thursday, Friday

Step 2: Code by Zapier (JavaScript)

• Purpose: Calculate target anniversary month/day (30 days from today)

const today = new Date();
const leadTimeDays = 30; // configurable: 30 = one month advance notice
const targetDate = new Date(today);
targetDate.setDate(targetDate.getDate() + leadTimeDays);

const targetMonth = String(targetDate.getMonth() + 1).padStart(2, '0');
const targetDay = String(targetDate.getDate()).padStart(2, '0');
const todayISO = today.toISOString().split('T')[0];

// Also calculate the 'no earlier than' date to prevent duplicate sends
const thirtyDaysAgo = new Date(today);
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
const noEarlierThan = thirtyDaysAgo.toISOString().split('T')[0];

output = [{
  targetMonth: targetMonth,
  targetDay: targetDay,
  todayISO: todayISO,
  noEarlierThan: noEarlierThan
}];

Step 3: Webhooks by Zapier (Custom Request)

• Method: GET
• URL (Redtail): https://smf.crm3.redtailtechnology.com/api/public/v1/contacts/search
• Headers — Authorization: Basic {{base64_api_key}}
• Headers — Content-Type: application/json
• Query Parameters — status: active
• Query Parameters — per_page: 100
• Note: Redtail API returns contacts; post-filter in Step 4 for anniversary date match.
• For Wealthbox, use: GET https://api.crmworkspace.com/v1/contacts?status=active with Authorization: Bearer {{access_token}}

Step 4: Code by Zapier (JavaScript) — Filter Matching Clients

const contacts = JSON.parse(inputData.apiResponse);
const targetMonth = inputData.targetMonth;
const targetDay = inputData.targetDay;
const noEarlierThan = inputData.noEarlierThan;

const matchingClients = contacts.filter(contact => {
  // Check if contact has anniversary date
  if (!contact.anniversary_date) return false;
  
  const annivDate = new Date(contact.anniversary_date);
  const annivMonth = String(annivDate.getMonth() + 1).padStart(2, '0');
  const annivDay = String(annivDate.getDate()).padStart(2, '0');
  
  // Match month and day (ignore year)
  if (annivMonth !== targetMonth || annivDay !== targetDay) return false;
  
  // Skip if opted out
  if (contact.auto_schedule_opt_out === true) return false;
  
  // Skip if already emailed in the last 30 days
  if (contact.last_review_email_sent && contact.last_review_email_sent > noEarlierThan) return false;
  
  // Skip if missing email
  if (!contact.email || contact.email.trim() === '') return false;
  
  return true;
});

output = [{matchingClients: JSON.stringify(matchingClients), count: matchingClients.length}];

Step 5: Filter by Zapier

• Condition: count (from Step 4) greater than 0
• If false: Zap stops (no clients to process today)

Step 6: Looping by Zapier

• Input: matchingClients array from Step 4
• For each client, execute Steps 7-10

Step 7: Lookup Table by Zapier — Advisor to Calendly Link

• Table configuration: maps Advisor Name → Calendly URL and Advisor Email (e.g., Jane Smith → https://calendly.com/jane-smith-ria/annual-review → jane@firmname.com; John Doe → https://calendly.com/john-doe-ria/annual-review → john@firmname.com)
• Lookup key: client.assigned_advisor
• Output: calendly_url, advisor_email

Step 8: Microsoft Outlook — Send Email (via connected M365 account)

• To: {{client.email}}
• From: {{advisor_email}}
• Subject: It's Time for Your Annual Portfolio Review, {{client.first_name}}
• Body Type: HTML
• Body: [Template 1 — with {{calendly_url}} dynamically inserted]
• Importance: Normal

Step 9: Webhooks by Zapier — Update CRM (stamp email sent date)

• Method: PUT
• URL (Redtail): https://smf.crm3.redtailtechnology.com/api/public/v1/contacts/{{client.id}}
• Headers: Authorization, Content-Type

# stamp email sent date and reset follow-up counters

{"custom_fields": {"last_review_email_sent": "{{todayISO}}", "review_meeting_booked": false, "follow_up_count": 0}}

Step 10: Webhooks by Zapier — Create CRM Task

• Method: POST
• URL (Redtail): https://smf.crm3.redtailtechnology.com/api/public/v1/activities

# create high-priority Annual Review task for advisor

{"subject": "Annual Review Due - {{client.full_name}}", "due_date": "{{targetDate}}", "assigned_to": "{{advisor.id}}", "category": "Annual Review", "priority": "High", "notes": "Automated review invitation sent on {{todayISO}}. Calendly link provided to client."}

Follow-Up Escalation Workflow

Type: workflow A secondary Zapier workflow that runs daily and checks for clients in the 'awaiting booking' state. It implements a 3-tier escalation: 7-day email follow-up, 14-day second follow-up, and 21-day escalation to a personal phone call task for the advisor. This workflow ensures no client falls through the cracks while respecting communication preferences.

Implementation

Zapier Workflow: Follow-Up Escalation Engine Platform: Zapier Team Plan Trigger: Schedule by Zapier — Daily at 9:00 AM local time, Mon-Fri Zap Configuration (Zap 2 of 4)

Step 1: Schedule Trigger

• Frequency: Every Day, 09:00, Mon-Fri

Step 2: Code by Zapier (JavaScript)

const today = new Date();
const d7 = new Date(today); d7.setDate(d7.getDate() - 7);
const d14 = new Date(today); d14.setDate(d14.getDate() - 14);
const d21 = new Date(today); d21.setDate(d21.getDate() - 21);

output = [{
  sevenDayWindow: d7.toISOString().split('T')[0],
  fourteenDayWindow: d14.toISOString().split('T')[0],
  twentyOneDayWindow: d21.toISOString().split('T')[0],
  todayISO: today.toISOString().split('T')[0]
}];

Step 3: Webhooks — Query CRM for Clients with Pending Reviews

• GET contacts where last_review_email_sent is NOT null
• GET contacts where review_meeting_booked = false
• GET contacts where auto_schedule_opt_out = false

Step 4: Code by Zapier — Categorize Clients by Follow-Up Stage

const contacts = JSON.parse(inputData.apiResponse);
const d7 = inputData.sevenDayWindow;
const d14 = inputData.fourteenDayWindow;
const d21 = inputData.twentyOneDayWindow;

const followUp7 = contacts.filter(c => c.last_review_email_sent === d7 && (c.follow_up_count || 0) === 0);
const followUp14 = contacts.filter(c => c.last_review_email_sent <= d14 && c.last_review_email_sent > d21 && (c.follow_up_count || 0) === 1);
const escalate21 = contacts.filter(c => c.last_review_email_sent <= d21 && (c.follow_up_count || 0) === 2);

output = [{
  followUp7: JSON.stringify(followUp7),
  followUp14: JSON.stringify(followUp14),
  escalate21: JSON.stringify(escalate21)
}];

Step 5: Paths by Zapier

• Path A: 7-Day Follow-Up — Loop through followUp7 contacts → Send Template 2 (Follow-Up Reminder) via Outlook → Update CRM: follow_up_count = 1
• Path B: 14-Day Follow-Up — Loop through followUp14 contacts → Send Template 3 (Second Follow-Up) via Outlook → Update CRM: follow_up_count = 2
• Path C: 21-Day Escalation (No Email — Create Call Task) — Loop through escalate21 contacts → Create CRM Task: Subject: 'CALL CLIENT - No Review Booked - {{client.full_name}}', Priority: Urgent, Due Date: Today, Assigned To: {{advisor}}, Notes: 'Client has not responded to 3 automated review invitations sent over the past 21 days. Please call to schedule their annual review personally.' → Update CRM: follow_up_count = 3 (stops all future automated emails for this cycle)

Booking Detection & CRM Sync

Type: integration Real-time integration that fires when a client books an annual review meeting through Calendly. It matches the booking to a CRM contact, updates the contact record with the meeting details, creates a CRM activity/event, and sets the review_meeting_booked flag to TRUE to halt follow-up emails.

Implementation:

# Zapier Workflow: Calendly Booking → CRM Sync
# Platform: Zapier Team Plan
# Trigger: Calendly — Invitee Created (real-time webhook)

## Zap Configuration (Zap 3 of 4)

### Step 1: Calendly Trigger — Invitee Created
- Event Scope: Organization-wide
- Event Type Filter: 'Annual Portfolio Review' (all matching event types)
- Output fields used: invitee_email, invitee_name, event_start_time, event_end_time, event_type_name, event_location

### Step 2: Webhooks by Zapier — Search CRM by Email
- Method: GET
- URL (Redtail): `https://smf.crm3.redtailtechnology.com/api/public/v1/contacts/search?email={{invitee_email}}`
- URL (Wealthbox): `https://api.crmworkspace.com/v1/contacts?email={{invitee_email}}`
- Headers: Authorization as configured

### Step 3: Filter by Zapier
- Only Continue If: API response contains at least 1 contact
- Prevents errors for unknown email addresses

### Step 4: Code by Zapier — Extract Contact ID & Calculate Next Review
const contact = JSON.parse(inputData.apiResponse)[0];
const meetingDate = new Date(inputData.eventStartTime);

// Calculate next review date based on client's review frequency
let nextReview = new Date(meetingDate);
const frequency = contact.review_frequency || 'Annual';
if (frequency === 'Quarterly') {
  nextReview.setMonth(nextReview.getMonth() + 3);
} else if (frequency === 'Semi-Annual') {
  nextReview.setMonth(nextReview.getMonth() + 6);
} else {
  nextReview.setMonth(nextReview.getMonth() + 12);
}

output = [{
  contactId: contact.id,
  contactName: contact.first_name + ' ' + contact.last_name,
  advisorId: contact.assigned_advisor_id,
  meetingDateISO: meetingDate.toISOString().split('T')[0],
  nextReviewISO: nextReview.toISOString().split('T')[0],
  reviewFrequency: frequency
}];

### Step 5: Webhooks — Update CRM Contact
- Method: PUT
- URL: `.../contacts/{{contactId}}`
- Body:
{
  "custom_fields": {
    "next_review_date": "{{nextReviewISO}}",
    "review_meeting_booked": true,
    "follow_up_count": 0,
    "last_review_email_sent": null
  }
}

### Step 6: Webhooks — Create CRM Meeting Activity
- Method: POST
- URL: `.../activities`
- Body:
{
  "subject": "Annual Review Meeting - {{contactName}}",
  "start_date": "{{meetingDateISO}}",
  "end_date": "{{eventEndTime}}",
  "type": "Meeting",
  "category": "Annual Review",
  "assigned_to": "{{advisorId}}",
  "contact_id": "{{contactId}}",
  "notes": "Booked via Calendly automated annual review scheduling. Location: {{eventLocation}}"
}

### Step 7: (Optional) Send Confirmation Email — Template 4
- Only if Calendly's built-in confirmation is disabled
- Via Microsoft Outlook action in Zapier

Post-Meeting Cycle Reset

Type: workflow Daily workflow that identifies completed review meetings (from yesterday) and triggers the post-meeting sequence: sends a thank-you email, updates the CRM Last Review Date, resets the Next Review Date for the next cycle, and creates post-meeting action item tasks for the advisor. This workflow closes the loop and ensures the perpetual annual cycle continues without manual intervention.

Implementation

Zapier Workflow: Post-Meeting Follow-Up & Cycle Reset Platform: Zapier Team Plan Trigger: Schedule by Zapier — Daily at 10:00 AM, Mon-Fri Zap Configuration (Zap 4 of 4)

• Step 1: Schedule Trigger — Frequency: Every Day, 10:00, Mon-Fri
• Step 3: Webhooks — Query CRM for Completed Meetings: GET activities/events where type = 'Meeting', category = 'Annual Review', date = {{checkDate}} (For Redtail: GET /api/public/v1/activities?date={{checkDate}}&category=Annual%20Review)
• Step 4: Filter — Only Continue If Meetings Found
• Step 5: Looping — For Each Completed Meeting
• Step 5a: Lookup Advisor → Calendly Link & Email — Use same Lookup Table as Zap 1
• Step 5b: Send Post-Review Email (Template 5) — To: {{client.email}}, From: {{advisor.email}}, Subject: Thank You for Meeting with Us, {{client.first_name}}. Body: HTML template with gratitude for their time, summary placeholder ('Your advisor will follow up with specific notes and action items'), note that next review is scheduled for approximately {{nextReviewDate}}, firm contact information, compliance footer
• Step 5c: Update CRM Contact — Full Cycle Reset (Note: next_review_date was already set by Zap 3 when the booking occurred)

Step 2: Code by Zapier — Calculate Yesterday's Date

const today = new Date();
const yesterday = new Date(today);
yesterday.setDate(yesterday.getDate() - 1);
// Skip weekends: if today is Monday, check Friday
const dayOfWeek = today.getDay();
if (dayOfWeek === 1) { // Monday
  yesterday.setDate(yesterday.getDate() - 2); // Check Friday
}
output = [{
  checkDate: yesterday.toISOString().split('T')[0],
  todayISO: today.toISOString().split('T')[0]
}];

Step 5c: CRM Contact Update Payload

PUT /contacts/{{contactId}}
{
  "custom_fields": {
    "last_review_date": "{{checkDate}}",
    "review_meeting_booked": false,
    "follow_up_count": 0,
    "last_review_email_sent": null
  }
}

Step 5d: Create CRM Task — Post-Meeting Action Items

POST /activities
{
  "subject": "Complete Post-Review Tasks - {{client.full_name}}",
  "due_date": "{{todayISO + 5 business days}}",
  "assigned_to": "{{advisorId}}",
  "priority": "Normal",
  "category": "Annual Review",
  "notes": "Post-annual-review checklist:\n1. Send meeting summary notes to client\n2. Update financial plan if goals changed\n3. Process any investment allocation changes\n4. File signed documents in document management\n5. Update beneficiary information if changes discussed\n6. Log review in compliance system"
}

Advisor Calendly Link Lookup Table

Type: integration A Zapier Storage lookup table that maps each advisor's name (as stored in the CRM's assigned advisor field) to their Calendly scheduling link, email address, title, and phone number. This table is referenced by Zaps 1, 2, and 4 to personalize outbound emails and route scheduling correctly. Must be updated whenever advisors join, leave, or change their Calendly event types.

Implementation:

Table Structure

Location: Zapier > Storage by Zapier (included in Team plan) Referenced by: Zap 1 (Step 7), Zap 2 (Paths A & B), Zap 4 (Step 5a)

Zapier Storage Configuration

To create the table:

To read from the table:

Alternative: Use Zapier Tables (newer feature)

Maintenance Process

When an advisor joins the firm:

When an advisor leaves:

When a Calendly link changes:

Testing & Validation

• TEST 1 — Trigger Accuracy: Create 5 test CRM contacts with anniversary dates exactly 30 days from today. Run Zap 1 manually. Verify that all 5 contacts receive an email and CRM tasks are created. Then create 5 contacts with anniversary dates 60 days out and verify they do NOT receive emails.
• TEST 2 — Opt-Out Exclusion: Set 'Auto-Schedule Opt-Out' to TRUE on 2 test contacts with triggering anniversary dates. Run Zap 1. Verify these contacts receive NO email and no CRM task is created.
• TEST 3 — Missing Data Handling: Create 2 test contacts with anniversary dates set but email addresses blank. Run Zap 1. Verify the Zap does not error/crash and these contacts are skipped gracefully. Check Zapier task history for clean filter-out.
• TEST 4 — Email Deliverability: Send test review invitation emails to 5 different email providers (Gmail, Outlook.com, Yahoo, Apple iCloud, firm's own domain). Verify all emails land in the inbox (not spam/junk). Check that the Calendly link renders correctly and is clickable in all email clients.
• TEST 5 — Calendly Booking Flow: Click the Calendly link in a test email. Book a meeting as if you were the client. Verify: (a) booking confirmation appears in Calendly, (b) Zap 3 fires within 2 minutes, (c) CRM contact record updated with meeting date, (d) CRM activity created with meeting details, (e) 'review_meeting_booked' flag set to TRUE.
• TEST 6 — Follow-Up Suppression: After booking a test meeting (Test 5), manually trigger Zap 2. Verify that the booked client does NOT receive a follow-up email. The follow-up should only go to clients who have NOT booked.
• TEST 7 — 7-Day Follow-Up: Set a test contact's 'last_review_email_sent' to exactly 7 days ago with 'review_meeting_booked' = false and 'follow_up_count' = 0. Run Zap 2. Verify Template 2 (follow-up reminder) is sent, and follow_up_count is updated to 1.
• TEST 8 — 21-Day Escalation: Set a test contact's 'last_review_email_sent' to exactly 21 days ago with follow_up_count = 2 and review_meeting_booked = false. Run Zap 2. Verify NO email is sent but a CRM task is created with 'CALL CLIENT' in the subject, assigned to the correct advisor with Urgent priority.
• TEST 9 — Compliance Archive Capture: After sending automated test emails, check the compliance archive (M365 In-Place Archive or Smarsh portal) within 30 minutes. Verify all automated emails appear in the archive with full headers, body, and the X-AutoReview transport rule tag applied.
• TEST 10 — Post-Meeting Cycle Reset: Mark a test meeting activity as completed in the CRM (dated yesterday). Run Zap 4. Verify: (a) Template 5 (thank-you email) is sent, (b) CRM 'last_review_date' updated to the meeting date, (c) 'review_meeting_booked' reset to false, (d) 'follow_up_count' reset to 0, (e) post-meeting task created with checklist in notes.
• TEST 11 — Duplicate Prevention: Run Zap 1 twice in the same day. Verify that clients who were already emailed earlier today do NOT receive a second email. The 'last_review_email_sent' date stamp should prevent duplicates.
• TEST 12 — Full End-to-End Cycle: Using one test contact, simulate the complete lifecycle: (1) anniversary date triggers initial email → (2) wait/simulate 7 days → follow-up sent → (3) client books via Calendly → (4) CRM updated, follow-ups stopped → (5) meeting occurs → (6) post-meeting email sent, cycle reset → (7) verify next_review_date is set ~12 months in the future. Document timing and results at each stage.
• TEST 13 — Error Notification: Temporarily break an API connection (e.g., revoke the CRM API key). Run Zap 1. Verify that Zapier sends an error notification email to the configured MSP and firm operations email addresses. Then restore the API key and verify the Zap recovers on next run.
• TEST 14 — Volume Load Test: If the firm has 500+ clients, calculate the maximum number of clients that could trigger on a single day (worst case: ~2-3% of clients share the same anniversary month/day = 10-15 emails). Verify this stays within Zapier's task limits (2,000 tasks/month on Team plan) and the CRM API's rate limits.

Client Handoff

Client Handoff Agenda (90-minute session with advisors + operations manager + CCO)

1. System Overview Demo (20 minutes)

• Walk through the complete workflow visually: what triggers when, what the client sees, what the advisor sees
• Show a real example: trigger → email delivered → client books → CRM updated → follow-up stopped → post-meeting reset
• Show the Zapier dashboard and how to check if workflows are running (green = healthy, red = error)

2. Advisor Training (20 minutes)

• How to check their personal Calendly link and availability settings
• How to mark meetings as 'completed' in the CRM (triggers post-meeting workflow)
• How to manually opt out a specific client (set Auto-Schedule Opt-Out = TRUE in CRM)
• How to handle client questions: 'Yes, we recently upgraded our scheduling system to make it easier for you to book at your convenience'
• What NOT to modify: email templates (require CCO re-approval), Zapier workflows, CRM custom fields

3. Operations Manager Training (20 minutes)

• How to add a new advisor to the Calendly lookup table
• How to monitor Zapier task usage (Settings > Usage — alert if approaching 2,000 tasks/month)
• How to check the Zapier error log for failed runs
• How to run a CRM report showing clients missing anniversary dates (for ongoing data hygiene)
• Monthly checklist: verify all Calendly links work, check email bounce rates, review Zapier usage

4. CCO/Compliance Training (15 minutes)

• Where archived emails are stored and how to search them (M365 Compliance Center or Smarsh portal)
• How to perform supervisory review of automated emails (sample 10% monthly per WSP)
• Process for requesting email template changes (submit to MSP → MSP updates → CCO re-approves → deploy)
• Location of the updated WSP section documenting the automation
• Annual review requirement: review automation system annually as part of firm compliance review

5. Documentation Delivered (15 minutes)

• Workflow Diagram: Visual flowchart of all 4 Zaps with triggers, actions, and decision points
• Email Template Library: All 5 approved templates with CCO sign-off dates and version numbers
• Advisor Quick Reference Card: 1-page laminated card with Calendly link, how to opt out a client, and who to call for issues
• Operations Runbook: Step-by-step guide for common tasks (add advisor, remove advisor, check errors, pull compliance reports)
• Escalation Contact Sheet: MSP support email/phone, Zapier support link, Calendly support link, CRM vendor support
• WSP Addendum: Copy of the updated Written Supervisory Procedures section for the firm's compliance files

6. Success Criteria Review

Maintenance

Ongoing MSP Maintenance Responsibilities

Weekly Tasks (15 minutes/week)

• Monitor Zapier Dashboard: Check all 4 Zaps are running (green status). Review any failed runs in the Task History. Common failures: expired API tokens, CRM API rate limiting, email delivery failures. Resolve within 4 business hours per SLA.
• Check Email Bounce Report: Review bounced emails from the past week. Update CRM records with corrected email addresses or flag clients with permanently invalid emails.

Monthly Tasks (1 hour/month)

• Zapier Usage Report: Check task consumption vs. plan limits (2,000 tasks/month on Team plan). If consistently above 80%, discuss plan upgrade with client. Generate usage report for client operations manager.
• Calendly Link Verification: Click-test each advisor's scheduling link to ensure it works, shows correct availability, and has the proper meeting type configured.
• CRM Data Quality Audit: Run a report on active clients missing anniversary dates, email addresses, or advisor assignments. Provide the client with a 'data cleanup needed' list.
• Compliance Archive Spot Check: Verify that automated emails from the past 30 days are present in the compliance archive. Check 5 random emails for completeness (full headers, body, attachments).
• Email Deliverability Check: Send a test email through the automation to mail-tester.com and verify the score remains 9/10 or higher. Check the firm's domain reputation at mxtoolbox.com.

Quarterly Tasks (2 hours/quarter)

• Workflow Optimization Review: Analyze client booking rates, follow-up conversion rates, and escalation-to-call rates. Recommend adjustments to email templates, timing, or lead-time window based on data.
• API Token/Credential Rotation: If the firm's security policy requires credential rotation, update API keys in Zapier and re-test all connections. Update the MSP password vault.
• Platform Update Review: Check for new Zapier features, CRM API updates, or Calendly changes that could improve or break the workflow. Apply updates as needed.
• CCO Quarterly Review: Provide the CCO with a quarterly summary: number of automated emails sent, booking rate, bounce rate, any errors, and confirmation that all emails are archived. This supports the firm's supervisory review obligations.

Annual Tasks (4 hours/year)

• Full System Audit: End-to-end test of all 4 Zaps. Verify every step fires correctly. Update email templates if branding, disclosures, or advisor information has changed.
• WSP Annual Review Support: Assist the CCO in reviewing and updating the WSP section on automated communications as part of the firm's annual compliance review.
• Capacity Planning: Project next year's task usage based on client growth. Recommend Zapier plan changes if needed. Review if the firm has outgrown the current CRM/scheduling tier.
• Client Satisfaction Survey: Send a brief survey to advisors asking about the automation's effectiveness and any desired improvements.

SLA Considerations

• Response Time: Automation failures affecting client-facing emails: 4-hour response, 8-hour resolution (business hours).
• Non-Critical Issues: CRM field updates, task creation failures: next business day response.
• Scheduled Maintenance: Platform updates and credential rotations scheduled outside business hours with 48-hour advance notice.
• Escalation Path: Tier 1 (operations manager self-service for data issues) → Tier 2 (MSP for workflow/integration issues) → Tier 3 (MSP escalation to Zapier/CRM/Calendly vendor support for platform-level issues).

Recommended MSP Billing

• Monthly Managed Services Retainer: $300–$500/month covers all weekly and monthly tasks, SLA-backed support, and quarterly reviews.
• Annual System Audit: Included in retainer or billed separately at $500–$1,000 if not bundled.
• Ad-Hoc Changes: New advisor onboarding ($150), email template revision ($200 including CCO review coordination), CRM field additions ($150).

Alternatives

Microsoft Power Automate + Microsoft Bookings (Full Microsoft Stack)

Replace Zapier with Microsoft Power Automate and Calendly with Microsoft Bookings, creating an all-Microsoft solution. Power Automate cloud flows handle the scheduling logic, date calculations, and CRM API calls. Microsoft Bookings provides the client-facing scheduling interface. Both are included or available as add-ons within the Microsoft 365 ecosystem the firm likely already uses.

Redtail CRM Native Workflows (No External Automation Platform)

Use Redtail CRM's built-in workflow engine to trigger annual review tasks and email reminders without any external automation middleware. Redtail includes workflow templates for annual reviews that can be configured to fire based on date fields. Email is sent directly from Redtail, and tasks are created natively within the CRM.

Salesforce Financial Services Cloud + Salesforce Flow

For larger or growing advisory firms, implement the entire solution within Salesforce Financial Services Cloud using Salesforce Flow (formerly Process Builder + Flow Builder) for automation. Salesforce Flow provides enterprise-grade workflow orchestration with conditional logic, scheduled triggers, email actions, and deep CRM integration — all within a single platform.

Self-Hosted n8n Automation Engine

Replace Zapier with n8n, an open-source workflow automation platform that can be self-hosted on a low-cost VPS or the MSP's existing infrastructure. n8n provides equivalent functionality to Zapier (HTTP requests, code nodes, scheduling, conditional logic) but with execution-based pricing instead of task-based pricing, and full data sovereignty.