56 min readDeterministic automation

Implementation Guide: Auto-trigger renewal workflows 90/60/30 days before policy expiration

Step-by-step implementation guide for deploying AI to auto-trigger renewal workflows 90/60/30 days before policy expiration for Insurance Agencies clients.

Use CaseImplementation Guide

Hardware Procurement

FortiGate 40F Next-Generation Firewall

FortinetFG-40FQty: 1

$400–$500 MSP cost / $700–$900 suggested resale (includes first year FortiGuard bundle)

Provides secure network perimeter for the agency. Required for GLBA and NAIC compliance — must have UTM, IPS, and SSL inspection to protect policy data in transit between workstations and cloud AMS/automation platforms. If client already has a business-class firewall with active subscriptions, this is not needed.

Ubiquiti UniFi Switch 24 PoE

UbiquitiUSW-24-POEQty: 1

$300–$400 MSP cost / $500–$650 suggested resale

Managed switch for reliable network infrastructure supporting cloud-based AMS and automation platform connectivity. VLAN segmentation capability supports compliance requirements for separating PII-handling traffic. Skip if client already has managed switching in place.

APC Smart-UPS 1500VA LCD

APC (Schneider Electric)SMT1500CQty: 1

$450–$550 MSP cost / $650–$800 suggested resale

Battery backup for firewall, switch, and any on-premises components (e.g., Power Automate on-premises data gateway server if needed). Ensures renewal automation monitoring and local network infrastructure remain operational during brief power events. Skip if client has existing UPS coverage.

Dell OptiPlex Micro 7010 (optional — on-prem gateway)

Dell TechnologiesD15UQty: 1

$650–$850 MSP cost / $1,000–$1,300 suggested resale

Only required if the agency's AMS exposes data through a local database or file export rather than a cloud API. Runs the Microsoft On-Premises Data Gateway for Power Automate or hosts a lightweight n8n instance. Spec: Intel i5-13500T, 16GB RAM, 256GB SSD. Not needed if AMS has a cloud-accessible REST API.

Software Procurement

Microsoft 365 Business Premium

MicrosoftPer-seat SaaS (monthly)Qty: 5–15 users

$22/user/month × 5–15 users = $110–$330/month. MSP CSP margin: 15–20%

Core productivity suite that includes Power Automate Premium (formerly standalone at $15/user/month), Exchange Online for email delivery, Teams for internal notifications, and SharePoint Online for renewal tracking lists. Also provides Entra ID with conditional access and MFA for NAIC/GLBA compliance. This is the primary automation engine.

Power Automate Premium (included in M365 Business Premium)

MicrosoftPower Automate PremiumQty: Per-seat SaaS (included with M365 BP)

Included in M365 Business Premium. Standalone: $15/user/month if purchased separately

The workflow orchestration engine. Provides scheduled cloud flows, HTTP connectors for AMS API calls, Outlook/Teams connectors for notifications, SharePoint connectors for renewal tracking, and Dataverse for structured policy data storage. Handles all 90/60/30-day trigger logic.

InsuredMine CRM (Alternative/Complement for agencies on NowCerts, AMS360, or QQCatalyst)

InsuredMine

Starting at $10/month (Growth plan) up to $90/user/month (Enterprise). Most agencies: $25–50/user/month

Insurance-specific CRM overlay that connects to the agency's AMS and provides built-in renewal workflow automation with pre-configured 90/60/30-day campaign templates. Use this INSTEAD of Power Automate if the agency is on NowCerts, AMS360, or QQCatalyst and wants an insurance-native solution with minimal custom configuration.

AgencyZoom

VertaforePer-seat SaaS (monthly, 7-seat minimum per plan)Qty: 7-seat minimum per plan

$75–$150/user/month. Plans include 7 seats. Contact Vertafore for MSP/partner pricing.

Best-fit renewal automation for agencies already on Vertafore products (AMS360, QQCatalyst). Provides complete renewal pipeline management, automated email sequences, task assignments, and analytics. Use this INSTEAD of Power Automate for Vertafore-stack agencies.

Better Agency CRM (Alternative for independent agencies)

Better Agency

$149/month base for up to 3 users + $35/month per additional user

All-in-one insurance CRM with pre-built renewal campaign templates, drag-and-drop workflow builder, and done-for-you email/SMS sequences. Good option for smaller agencies wanting turnkey solution without heavy configuration.

Twilio (for SMS notifications)

TwilioUsage-based API

$0.0079/SMS sent + $1.15/month per phone number. Typical agency: $20–$60/month

Programmatic SMS delivery for 90/60/30-day renewal text reminders to policyholders. Connected to Power Automate via HTTP connector or Twilio connector. Must implement TCPA-compliant opt-in/opt-out.

SharePoint Online (included in M365)

Microsoft

Included

Hosts the Policy Renewal Tracker list that serves as the structured data layer between the AMS and Power Automate. Stores policy number, client name, expiration date, renewal stage, assigned CSR, and communication log.

DMARC/DKIM/SPF Monitoring

EasyDMARC or ValimailSaaS (monthly)

$20–$50/month per domain

Monitors email authentication to ensure renewal notification emails are not flagged as spam. Critical for deliverability of automated renewal communications. Provides DMARC aggregate and forensic reporting.

Prerequisites

  • Active Agency Management System (AMS) subscription with API access enabled — confirm which AMS the agency uses: Applied Epic (requires separate SDK-API license), AMS360/QQCatalyst (Vertafore API), HawkSoft (partner API program), NowCerts (open REST API), or EZLynx
  • Microsoft 365 Business Premium deployed for all agency staff who will interact with the renewal workflow (CSRs, producers, account managers) — or willingness to upgrade from a lower M365 tier
  • Business-class internet connection with minimum 50 Mbps download/25 Mbps upload for reliable cloud AMS and automation platform access
  • Valid SSL/TLS certificates on all endpoints and TLS 1.2+ enforced on all communications (standard with cloud services)
  • Multi-factor authentication (MFA) enabled on all user accounts — required for NAIC Insurance Data Security Model Law compliance in 22+ states
  • Email domain with properly configured SPF, DKIM, and DMARC records — verify with MXToolbox before starting; if not configured, add to implementation scope
  • Administrative access to the AMS for API credential generation and data export configuration
  • Documented current renewal process: who does what, when, using which communication channels — needed for workflow mapping in Discovery phase
  • Complete policy inventory export from AMS including: policy number, insured name, policy type (auto, home, commercial, life), effective date, expiration date, assigned CSR/producer, client email, client phone number
  • Client consent/opt-in records for SMS communications if text message reminders will be used (TCPA compliance)
  • Designated agency point of contact (typically the agency principal or operations manager) with authority to approve workflow logic, email/SMS templates, and go-live decisions
  • Existing cyber liability insurance policy for the agency (recommended) — automation handling PII creates exposure that should be covered

Installation Steps

...

Step 1: Discovery and Renewal Process Audit

Conduct an on-site or virtual discovery session with the agency principal, lead CSR, and key producers. Document the current renewal handling process end-to-end: how expiration dates are currently tracked, what communications are sent and when, who is responsible for each step, what falls through the cracks, and what the ideal future state looks like. Identify the AMS in use and its API capabilities. Map out the 90/60/30-day trigger points and define what action should occur at each stage.

Note

Use a structured intake form. Key questions: (1) How many active policies? (2) What lines of business? (3) Average renewals per month? (4) Current retention rate? (5) Biggest pain points in renewal process? (6) Do you send renewal emails/texts today — if so, what tools? (7) Who handles renewals — dedicated staff or all CSRs? Typical discovery takes 2–3 hours. Document everything in a shared OneNote or SharePoint page.

Step 2: AMS API Access and Data Export Configuration

Obtain API credentials from the agency's AMS. For Applied Epic, request an SDK-API license from Applied Systems (if not already provisioned — this can take 1–2 weeks). For AMS360/QQCatalyst, enable the Vertafore API through the admin portal. For HawkSoft, apply through the HawkSoft Partner API program. For NowCerts, generate API keys from Settings > API. For EZLynx, contact EZLynx support for API access. Test the API connection and confirm you can pull policy records including expiration dates.

powershell
# NowCerts API test (PowerShell example):
$headers = @{ 'Authorization' = 'Bearer YOUR_API_KEY'; 'Content-Type' = 'application/json' }
$response = Invoke-RestMethod -Uri 'https://api.nowcerts.com/api/policies?status=active' -Headers $headers -Method GET
$response | Select-Object -First 5 | Format-Table PolicyNumber, InsuredName, ExpirationDate
# HawkSoft API test (requires partner credentials):
curl -X GET 'https://api.hawksoft.com/v1/policies?status=active&limit=5' \
  -H 'Authorization: Bearer YOUR_ACCESS_TOKEN' \
  -H 'Accept: application/json'
# AMS360 SOAP API test (legacy — check if REST is available):
# Contact Vertafore for current REST API documentation
# REST endpoint: https://api.vertafore.com/ams360/v1/policies
Note

Applied Epic SDK-API license procurement can take 1–2 weeks and requires an additional licensing agreement with Applied Systems. Factor this into the timeline. If API access is delayed or unavailable, fall back to scheduled CSV/Excel export from the AMS — most systems support automated daily report exports that can be ingested by Power Automate via SharePoint.

Step 3: Create SharePoint Renewal Tracking Infrastructure

Create a dedicated SharePoint Online site called 'Renewal Operations' within the agency's M365 tenant. Within it, create a SharePoint List called 'Policy Renewal Tracker' with columns for all required policy and workflow data. This list serves as the intermediate data layer between the AMS and Power Automate — it provides structured, queryable data that Power Automate can easily filter and trigger against.

PowerShell
powershell
# Create SharePoint site and list using PnP PowerShell

Install-Module -Name PnP.PowerShell -Force -AllowClobber
Connect-PnPOnline -Url 'https://YOURTENANT.sharepoint.com' -Interactive

# Create the site
New-PnPSite -Type TeamSite -Title 'Renewal Operations' -Alias 'RenewalOps' -Description 'Insurance policy renewal workflow tracking'

# Connect to the new site
Connect-PnPOnline -Url 'https://YOURTENANT.sharepoint.com/sites/RenewalOps' -Interactive

# Create the Policy Renewal Tracker list
New-PnPList -Title 'Policy Renewal Tracker' -Template GenericList

# Add columns
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Policy Number' -InternalName 'PolicyNumber' -Type Text -Required
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Insured Name' -InternalName 'InsuredName' -Type Text -Required
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Insured Email' -InternalName 'InsuredEmail' -Type Text
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Insured Phone' -InternalName 'InsuredPhone' -Type Text
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Policy Type' -InternalName 'PolicyType' -Type Choice -Choices 'Personal Auto','Homeowners','Commercial Package','Commercial Auto','Workers Comp','General Liability','Life','Health','Umbrella','Other'
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Expiration Date' -InternalName 'ExpirationDate' -Type DateTime -Required
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Assigned CSR' -InternalName 'AssignedCSR' -Type Text
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Assigned Producer' -InternalName 'AssignedProducer' -Type Text
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Renewal Stage' -InternalName 'RenewalStage' -Type Choice -Choices 'Not Started','90-Day Notice Sent','60-Day Notice Sent','30-Day Notice Sent','Client Contacted','Quote Provided','Renewed','Lost','Cancelled'
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Last Action Date' -InternalName 'LastActionDate' -Type DateTime
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Last Action Notes' -InternalName 'LastActionNotes' -Type Note
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Premium Amount' -InternalName 'PremiumAmount' -Type Currency
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Risk Level' -InternalName 'RiskLevel' -Type Choice -Choices 'Low','Medium','High'
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'Days Until Expiration' -InternalName 'DaysUntilExpiration' -Type Number
Add-PnPField -List 'Policy Renewal Tracker' -DisplayName 'SMS Opt-In' -InternalName 'SMSOptIn' -Type Boolean
1
Create a SharePoint view for 'Upcoming Renewals' filtered to the next 90 days: Go to the list > Add view > Name: 'Upcoming 90 Days' > Filter: DaysUntilExpiration <= 90 AND RenewalStage != 'Renewed' AND RenewalStage != 'Lost'
Note

The SharePoint List approach is chosen over Dataverse because it requires no additional licensing (included in M365 Business Premium), is easy for agency staff to view and manually update, and provides a familiar interface. If the agency has more than 5,000 active policies, consider Dataverse instead (requires Power Apps premium licensing at $20/user/month) for better performance with large datasets.

Step 4: Build the AMS-to-SharePoint Data Sync Flow

Create a Power Automate Scheduled Cloud Flow that runs daily (recommended: 5:00 AM local time) to pull active policy data from the AMS API and sync it to the SharePoint Policy Renewal Tracker list. This flow handles new policies, updated expiration dates, and calculates the 'Days Until Expiration' field. This is the foundational data pipeline that all trigger workflows depend on.

  • Flow Name: 'Daily AMS Policy Sync'
  • Trigger: Recurrence — every 1 day at 5:00 AM
  • Step 1: HTTP action — GET active policies from AMS API Method: GET URI: https://api.{your-ams}.com/v1/policies?status=active Headers: Authorization: Bearer @{variables('AMS_API_Key')}
  • Step 2: Parse JSON — parse the API response Schema: { type: array, items: { type: object, properties: { policyNumber: string, insuredName: string, ... }}}
  • Step 3: Apply to each — iterate over each policy Step 3a: Get items — SharePoint: check if policy already exists in list Filter: PolicyNumber eq '@{items('Apply_to_each')?['policyNumber']}' Step 3b: Condition — if item exists, update it; if not, create it Yes: Update item — update ExpirationDate, DaysUntilExpiration, etc. No: Create item — add new row with all policy fields Step 3c: Compose — calculate DaysUntilExpiration: div(sub(ticks(items('Apply_to_each')?['expirationDate']), ticks(utcNow())), 864000000000)
  • Step 4: Send notification to MSP monitoring mailbox if sync fails Scope: Try/Catch pattern using Configure Run After on failure
  • Alternative: CSV-based sync for AMS without API: Step 1: AMS scheduled report exports CSV to a shared folder Step 2: Power Automate watches the folder (SharePoint or OneDrive) Step 3: 'When a file is created' trigger processes the CSV Step 4: Parse CSV and upsert into SharePoint list
Note

For Applied Epic: Use the SDK-API with SOAP/REST calls. The policy endpoint typically returns XML that must be parsed. Consider using a custom connector in Power Automate. For HawkSoft: The API uses OAuth2 — you will need to register as a partner and obtain client_id/client_secret. For agencies with 1,000+ policies, implement pagination in the API calls (most AMS APIs support limit/offset or cursor-based pagination). Run the sync during off-hours to avoid API rate limits. Set up a failure alert email to the MSP's monitoring inbox.

Step 5: Build the 90-Day Renewal Trigger Flow

Create a Power Automate Scheduled Cloud Flow that runs daily and identifies all policies where DaysUntilExpiration equals 90 (±1 day buffer) and RenewalStage is 'Not Started'. For each matching policy, the flow: (1) sends a personalized renewal notification email to the insured, (2) sends an SMS if opted in, (3) creates a task in the AMS or Teams/Planner for the assigned CSR, (4) updates the SharePoint list RenewalStage to '90-Day Notice Sent', and (5) logs the action with timestamp.

Power Automate Flow Definition: 90-Day Renewal Trigger
plaintext
# Power Automate Flow Definition:
# Flow Name: '90-Day Renewal Trigger'
# Trigger: Recurrence — every 1 day at 8:00 AM
#
# Step 1: Get items — SharePoint 'Policy Renewal Tracker'
#   Filter Query: DaysUntilExpiration le 92 and DaysUntilExpiration ge 88 and RenewalStage eq 'Not Started'
#   Top Count: 500
#
# Step 2: Apply to each — for each matching policy:
#
#   Step 2a: Send an email (V2) — Office 365 Outlook
#     To: @{items('Apply_to_each')?['InsuredEmail']}
#     Subject: Your @{items('Apply_to_each')?['PolicyType']} policy renews in 90 days — Action may be needed
#     Body: (HTML template — see custom_ai_components for full template)
#     From: renewals@{agency-domain}.com (shared mailbox)
#     Importance: Normal
#
#   Step 2b: Condition — if SMSOptIn equals true:
#     Yes: HTTP action — POST to Twilio API
#       URI: https://api.twilio.com/2010-04-01/Accounts/{SID}/Messages.json
#       Body: To=@{items('Apply_to_each')?['InsuredPhone']}&From=+1XXXXXXXXXX&Body=Hi @{items('Apply_to_each')?['InsuredName']}, your @{items('Apply_to_each')?['PolicyType']} policy with {AgencyName} renews in 90 days. We'll be in touch to review your options. Reply STOP to opt out.
#
#   Step 2c: Create a task — Microsoft Planner (or Teams)
#     Plan: 'Renewal Tasks'
#     Bucket: '90-Day Reviews'
#     Title: '90-Day Review: @{items('Apply_to_each')?['InsuredName']} - @{items('Apply_to_each')?['PolicyNumber']}'
#     Assigned To: @{items('Apply_to_each')?['AssignedCSR']}
#     Due Date: addDays(utcNow(), 14)
#
#   Step 2d: Update item — SharePoint
#     RenewalStage: '90-Day Notice Sent'
#     LastActionDate: utcNow()
#     LastActionNotes: '90-day renewal email sent. SMS sent: @{if(items('Apply_to_each')?['SMSOptIn'], 'Yes', 'No')}. Task created for @{items('Apply_to_each')?['AssignedCSR']}.'
Note

The ±2 day buffer (88–92 days) accounts for the daily sync timing and prevents policies from being missed if the sync runs slightly early or late. Make sure the shared mailbox 'renewals@domain.com' is created in Exchange Online and the Power Automate service account has Send As permissions. For agencies processing more than 50 renewals per day, consider adding a 5-second delay between emails to avoid Exchange throttling (maximum 30 messages per minute for shared mailboxes).

Step 6: Build the 60-Day Renewal Trigger Flow

Clone the 90-Day flow and modify it for the 60-day checkpoint. This flow targets policies where DaysUntilExpiration is 58–62 and RenewalStage is '90-Day Notice Sent'. The 60-day communication is more urgent: it includes a direct call-to-action to schedule a review call and provides a calendar booking link. It also escalates if no client response has been received to the 90-day notice.

Power Automate Flow Definition: 60-Day Renewal Trigger
plaintext
# Power Automate Flow Definition:
# Flow Name: '60-Day Renewal Trigger'
# Trigger: Recurrence — every 1 day at 8:30 AM
#
# Step 1: Get items — SharePoint 'Policy Renewal Tracker'
#   Filter Query: DaysUntilExpiration le 62 and DaysUntilExpiration ge 58 and RenewalStage eq '90-Day Notice Sent'
#   Top Count: 500
#
# Step 2: Apply to each — for each matching policy:
#
#   Step 2a: Send an email (V2) — 60-day template (see custom_ai_components)
#     Subject: ACTION NEEDED: Your @{items('Apply_to_each')?['PolicyType']} policy renews in 60 days
#     Body: (60-day HTML template with booking link and coverage review CTA)
#     Importance: High
#
#   Step 2b: Condition — if SMSOptIn equals true:
#     Yes: Twilio SMS — 'Hi {Name}, your {PolicyType} policy renews in 60 days. Please call us at {AgencyPhone} or book a review at {BookingLink}. Reply STOP to opt out.'
#
#   Step 2c: Create a task — Microsoft Planner
#     Bucket: '60-Day Follow-ups'
#     Title: '60-Day Follow-up: {InsuredName} - {PolicyNumber}'
#     Due Date: addDays(utcNow(), 7)
#     Priority: High (Urgent)
#
#   Step 2d: Condition — if RiskLevel equals 'High':
#     Yes: Post message in Teams channel '#renewal-escalations'
#       Message: '⚠️ HIGH-VALUE RENEWAL: {InsuredName} ({PolicyNumber}) — Premium: {PremiumAmount} — Expires in 60 days. No response to 90-day notice. Assigned to: {AssignedProducer}'
#
#   Step 2e: Update item — SharePoint
#     RenewalStage: '60-Day Notice Sent'
#     LastActionDate: utcNow()
#     LastActionNotes: '60-day follow-up sent. Escalation: @{if(equals(items('Apply_to_each')?['RiskLevel'],'High'),'Yes - Teams notification','No')}'
Note

The 60-day flow is critical for catching policies where the client did not respond to the 90-day notice. The Teams escalation channel for high-risk/high-premium policies ensures producers are directly alerted. Create a Microsoft Bookings page linked to the agency for self-service scheduling — include this link in the 60-day email template.

Step 7: Build the 30-Day Renewal Trigger Flow

Create the final urgency tier flow for the 30-day mark. This targets policies where DaysUntilExpiration is 28–32 and RenewalStage is '60-Day Notice Sent' (meaning the client has not yet actively engaged). This is the last automated touchpoint before the policy lapses — communications are marked urgent, and the CSR is assigned a mandatory phone call task with same-week due date. Policies at this stage that are high-value trigger an alert to the agency principal.

Power Automate Flow Definition: 30-Day Renewal Trigger
plaintext
# Power Automate Flow Definition:
# Flow Name: '30-Day Renewal Trigger'
# Trigger: Recurrence — every 1 day at 9:00 AM
#
# Step 1: Get items — SharePoint 'Policy Renewal Tracker'
#   Filter Query: DaysUntilExpiration le 32 and DaysUntilExpiration ge 28 and RenewalStage eq '60-Day Notice Sent'
#   Top Count: 500
#
# Step 2: Apply to each:
#
#   Step 2a: Send an email (V2) — 30-day URGENT template
#     Subject: URGENT: Your @{items('Apply_to_each')?['PolicyType']} policy expires in 30 days
#     Body: (30-day urgent template — see custom_ai_components)
#     Importance: High
#
#   Step 2b: SMS via Twilio (if opted in):
#     'URGENT: {Name}, your {PolicyType} policy expires in 30 days. Please call {AgencyPhone} immediately to avoid a lapse in coverage. Reply STOP to opt out.'
#
#   Step 2c: Create Planner task — mandatory phone call
#     Bucket: '30-Day URGENT'
#     Title: 'URGENT CALL: {InsuredName} - {PolicyNumber} - Expires in 30 days'
#     Assigned To: AssignedCSR
#     Due Date: addDays(utcNow(), 3)
#     Priority: Urgent
#     Description: 'Policy expires in 30 days. Client has not responded to 90-day or 60-day notices. MUST call client today. Phone: {InsuredPhone}'
#
#   Step 2d: Post to Teams '#renewal-escalations' channel (ALL 30-day policies):
#     '🚨 30-DAY EXPIRATION: {InsuredName} | {PolicyType} | Premium: {PremiumAmount} | CSR: {AssignedCSR} | Producer: {AssignedProducer}'
#
#   Step 2e: Condition — PremiumAmount > 5000:
#     Yes: Send email to agency principal with full details and request personal outreach
#
#   Step 2f: Update SharePoint item:
#     RenewalStage: '30-Day Notice Sent'
#     LastActionDate: utcNow()
Note

The 30-day flow should have a very low false-positive rate because policies that were successfully renewed at 90 or 60 days will have their RenewalStage updated to 'Renewed' and will not match this filter. Ensure CSRs are trained to update the SharePoint list (or AMS, if syncing back) when they make contact. The $5,000 premium threshold for principal escalation should be adjusted per agency — some agencies may set this at $2,500 or $10,000 depending on book size.

Step 8: Configure Email Templates and Shared Mailbox

Create a shared mailbox in Exchange Online (renewals@agencydomain.com) for all outbound renewal communications. Configure Send As permissions for the Power Automate service account. Build and test HTML email templates for each of the three stages (90/60/30 days) following CAN-SPAM requirements and insurance communication best practices.

Exchange Online PowerShell: Create shared mailbox, set permissions, and verify DNS email authentication records
powershell
# Create shared mailbox via Exchange Online PowerShell:
Install-Module -Name ExchangeOnlineManagement
Connect-ExchangeOnline -UserPrincipalName admin@YOURTENANT.onmicrosoft.com

New-Mailbox -Shared -Name 'Agency Renewals' -DisplayName 'Agency Renewals' -Alias renewals -PrimarySmtpAddress renewals@agencydomain.com

# Grant Send As permission to the Power Automate service account:
Add-RecipientPermission -Identity renewals@agencydomain.com -Trustee powerautomate-svc@agencydomain.com -AccessRights SendAs -Confirm:$false

# Grant Full Access to the operations manager for monitoring:
Add-MailboxPermission -Identity renewals@agencydomain.com -User opsmanager@agencydomain.com -AccessRights FullAccess -InheritanceType All
# Verify DMARC/DKIM/SPF for the domain:
nslookup -type=TXT agencydomain.com
nslookup -type=TXT _dmarc.agencydomain.com
nslookup -type=TXT selector1._domainkey.agencydomain.com
Note

Never include full policy numbers, SSNs, or sensitive financial details in renewal emails — use only last 4 digits of policy number for identification. All emails MUST include: (1) physical agency address, (2) unsubscribe/opt-out link, (3) agency license number if required by state. Test email deliverability by sending to Gmail, Yahoo, and Outlook test accounts — check spam scores using mail-tester.com. Consider creating a branded HTML email template that matches the agency's website design.

Step 9: Configure Twilio for SMS Notifications

Set up a Twilio account, provision a local phone number, and configure the messaging service for TCPA-compliant SMS renewal reminders. Create message templates and implement opt-out handling.

Twilio CLI setup, phone number provisioning, messaging service configuration, opt-out handling, and SMS delivery test
shell
# Twilio setup via CLI:
npm install -g twilio-cli
twilio login

# Purchase a local phone number:
twilio phone-numbers:buy:local --area-code=YOUR_AREA_CODE --sms-enabled

# Create a messaging service:
twilio api:messaging:v1:services:create --friendly-name='Agency Renewals SMS'

# Add the phone number to the messaging service:
# (Use Twilio Console UI for this step — Services > Add Senders > Phone Number)

# Set up opt-out handling (Twilio Advanced Opt-Out):
# Navigate to: Messaging > Services > [Your Service] > Opt-Out Management
# Enable: STOP, STOPALL, UNSUBSCRIBE, CANCEL, END, QUIT
# Configure opt-out response: 'You have been unsubscribed from {AgencyName} renewal reminders. You will no longer receive texts. Call us at {AgencyPhone} if you need assistance.'
# Configure opt-in keywords: START, YES, UNSTOP

# Test SMS delivery:
twilio api:messaging:v1:services:messages:create \
  --messaging-service-sid=MGXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX \
  --to=+1YOURNUMBER \
  --body='Test: This is a renewal reminder test from {AgencyName}.'
Note

TCPA compliance is non-negotiable for SMS. Ensure: (1) The agency has documented opt-in consent for each client receiving SMS — this should be captured during policy onboarding and stored in the SMSOptIn field in SharePoint. (2) Every SMS includes 'Reply STOP to opt out.' (3) Twilio's Advanced Opt-Out is enabled to automatically handle STOP requests. (4) The agency registers for A2P 10DLC (Application-to-Person) messaging through Twilio to comply with carrier requirements — this takes 1–2 weeks for brand and campaign registration.

Step 10: Build the Weekly Renewal Dashboard and Reporting

Create a Power BI dashboard (or SharePoint dashboard page) that gives the agency real-time visibility into renewal pipeline status, upcoming expirations, conversion rates, and CSR performance. This is critical for management oversight and for demonstrating ROI to the agency principal.

  • Option A: SharePoint List Views (included, no extra cost): Create the following views on the Policy Renewal Tracker list: 1. 'This Week's Expirations' — Filter: DaysUntilExpiration >= 0 AND DaysUntilExpiration <= 7 2. 'Overdue / Lapsed' — Filter: DaysUntilExpiration < 0 AND RenewalStage != 'Renewed' 3. 'Pending by CSR' — Group by: AssignedCSR, Filter: RenewalStage != 'Renewed' AND RenewalStage != 'Lost' 4. 'Renewal Success Rate' — Use the List formatting JSON to show conditional colors
  • Option B: Power Automate weekly summary email: Flow Name: 'Weekly Renewal Summary' Trigger: Recurrence — every Monday at 7:00 AM Step 1: Get items — all policies with DaysUntilExpiration between 0 and 90 Step 2: Compose — build HTML summary table Step 3: Send email to agency principal and operations manager Include: Total upcoming renewals, renewals by stage, overdue count, retention rate
  • Option C: Power BI (if agency has M365 E5 or Power BI Pro at $10/user/mo): Connect Power BI to SharePoint List via OData connector Build visuals: renewal funnel, retention rate trend, CSR leaderboard, premium at risk
Note

Start with the free SharePoint List views and the weekly summary email. Only recommend Power BI if the agency has 500+ policies or specifically requests advanced analytics. The weekly summary email is the most impactful — it gives the agency principal a Monday-morning snapshot without requiring them to log into any system.

Step 11: Configure Compliance and Audit Logging

Implement comprehensive audit logging for all automated renewal actions to satisfy GLBA and NAIC compliance requirements. Configure data retention policies and access controls.

PowerShell: Enable M365 audit logging and provision the Renewal Audit Log SharePoint list with required compliance fields
powershell
# Enable Microsoft 365 Audit Log (if not already enabled):
Connect-ExchangeOnline
Set-AdminAuditLogConfig -UnifiedAuditLogIngestionEnabled $true

# Configure Power Automate flow run history retention:
# By default, Power Automate retains flow run history for 28 days
# For compliance, export flow run data to a SharePoint 'Audit Log' list:
# Add a step at the end of each renewal flow that creates an audit entry:
#   List: 'Renewal Audit Log'
#   Fields: Timestamp, FlowName, PolicyNumber, ActionTaken, Recipient, Channel (Email/SMS/Task), Status (Success/Failure)

# Create the Audit Log SharePoint list:
Connect-PnPOnline -Url 'https://YOURTENANT.sharepoint.com/sites/RenewalOps' -Interactive
New-PnPList -Title 'Renewal Audit Log' -Template GenericList
Add-PnPField -List 'Renewal Audit Log' -DisplayName 'Timestamp' -InternalName 'AuditTimestamp' -Type DateTime -Required
Add-PnPField -List 'Renewal Audit Log' -DisplayName 'Flow Name' -InternalName 'FlowName' -Type Text
Add-PnPField -List 'Renewal Audit Log' -DisplayName 'Policy Number' -InternalName 'AuditPolicyNumber' -Type Text
Add-PnPField -List 'Renewal Audit Log' -DisplayName 'Action Taken' -InternalName 'ActionTaken' -Type Text
Add-PnPField -List 'Renewal Audit Log' -DisplayName 'Recipient' -InternalName 'Recipient' -Type Text
Add-PnPField -List 'Renewal Audit Log' -DisplayName 'Channel' -InternalName 'Channel' -Type Choice -Choices 'Email','SMS','Task','Teams','Escalation'
Add-PnPField -List 'Renewal Audit Log' -DisplayName 'Status' -InternalName 'ActionStatus' -Type Choice -Choices 'Success','Failure','Pending'

# Configure SharePoint retention policy (retain audit data for 7 years — typical insurance requirement):
# Go to Microsoft Purview Compliance Center > Data lifecycle management > Retention policies
# Create policy: 'Renewal Audit Retention' — retain items for 7 years, apply to 'Renewal Operations' site
# Set role-based access on the SharePoint site:
# Site Permissions:
#   Owners: MSP admin, Agency principal — Full Control
#   Members: CSRs, Producers — Edit (can update RenewalStage, add notes)
#   Visitors: Read-only for reporting users
#   Audit Log list: Members have Read-only; only Owners and Power Automate service account can write
Note

NAIC Data Security Model Law requires audit trails of all automated processing involving customer PII. The 7-year retention period aligns with most state insurance record retention requirements (varies by state — some require 5 years, some 10). Check the specific state requirements for the agency's domicile state. The Renewal Audit Log list is separate from the Policy Renewal Tracker to prevent accidental modification of audit records.

Step 12: End-to-End Testing with Sample Policies

Before going live, create 10–15 test policy records in the SharePoint list with fabricated data at various points in the 90/60/30-day window. Run each flow manually to verify all triggers fire correctly, emails are delivered, SMS messages arrive, tasks are created, and audit logs are written. Test edge cases including missing email addresses, opted-out SMS, and high-value escalation thresholds.

Create test records via PnP PowerShell
powershell
$testPolicies = @(
  @{PolicyNumber='TEST-90-001'; InsuredName='John Test90'; InsuredEmail='test90@youremail.com'; InsuredPhone='+1XXXXXXXXXX'; PolicyType='Homeowners'; ExpirationDate=(Get-Date).AddDays(90); AssignedCSR='Jane CSR'; AssignedProducer='Bob Producer'; RenewalStage='Not Started'; PremiumAmount=1200; RiskLevel='Low'; SMSOptIn=$true; DaysUntilExpiration=90},
  @{PolicyNumber='TEST-60-001'; InsuredName='Jane Test60'; InsuredEmail='test60@youremail.com'; InsuredPhone='+1XXXXXXXXXX'; PolicyType='Commercial Package'; ExpirationDate=(Get-Date).AddDays(60); AssignedCSR='Jane CSR'; AssignedProducer='Bob Producer'; RenewalStage='90-Day Notice Sent'; PremiumAmount=8500; RiskLevel='High'; SMSOptIn=$true; DaysUntilExpiration=60},
  @{PolicyNumber='TEST-30-001'; InsuredName='Bob Test30'; InsuredEmail='test30@youremail.com'; InsuredPhone='+1XXXXXXXXXX'; PolicyType='Personal Auto'; ExpirationDate=(Get-Date).AddDays(30); AssignedCSR='Jane CSR'; AssignedProducer='Bob Producer'; RenewalStage='60-Day Notice Sent'; PremiumAmount=950; RiskLevel='Low'; SMSOptIn=$false; DaysUntilExpiration=30},
  @{PolicyNumber='TEST-30-HIGH'; InsuredName='High Value Test'; InsuredEmail='testhigh@youremail.com'; InsuredPhone='+1XXXXXXXXXX'; PolicyType='Commercial Auto'; ExpirationDate=(Get-Date).AddDays(30); AssignedCSR='Jane CSR'; AssignedProducer='Bob Producer'; RenewalStage='60-Day Notice Sent'; PremiumAmount=15000; RiskLevel='High'; SMSOptIn=$true; DaysUntilExpiration=30}
)

foreach ($policy in $testPolicies) {
  Add-PnPListItem -List 'Policy Renewal Tracker' -Values $policy
}
1
Go to Power Automate > My Flows > '90-Day Renewal Trigger' > Run
2
Verify: test90@youremail.com receives 90-day email
3
Verify: SMS received on test phone
4
Verify: Planner task created in '90-Day Reviews' bucket
5
Verify: SharePoint item updated to '90-Day Notice Sent'
6
Verify: Audit log entry created
7
Repeat for 60-day and 30-day flows
8
Check: TEST-30-HIGH should trigger principal escalation email and Teams post
Note

Use real email addresses (your MSP team's) for testing — do not use fictional addresses. For SMS testing, use your own mobile numbers. After testing, DELETE all test records from the SharePoint list before going live. Document all test results in a test log spreadsheet — this becomes part of the compliance documentation. Run the full test cycle twice: once in isolation, once with all three flows enabled on their scheduled triggers over 3 consecutive days.

Step 13: Staff Training and Go-Live

Conduct a 60–90 minute training session with all agency staff who will interact with the renewal system. Cover: how the automation works, what emails/texts clients receive, how to update the SharePoint list when they make manual contact, how to view the Planner task board, and how to handle client responses. Then enable the production flows and monitor closely for the first two weeks.

1
Delete all test records from Policy Renewal Tracker list
2
Run the Daily AMS Policy Sync flow manually — verify all active policies load correctly
3
Review the loaded data — spot-check 20 random policies against AMS
4
Enable the 90-Day Renewal Trigger flow (scheduled)
5
Wait 24 hours — review first batch of emails sent
6
Enable the 60-Day Renewal Trigger flow
7
Enable the 30-Day Renewal Trigger flow
8
Enable the Weekly Renewal Summary flow
9
Confirm all flows show 'Succeeded' in Power Automate run history
10
Send go-live confirmation email to agency principal and MSP account manager
Note

Schedule the training for a Tuesday or Wednesday (avoid Monday chaos and Friday disengagement). Record the training session via Teams for future reference and new-hire onboarding. Create a one-page quick reference card (laminated) for each CSR's desk showing: how to update the SharePoint list, what each renewal stage means, and the escalation path. The first two weeks are critical — check flow run history daily and have a Teams channel open with the agency for quick questions.

Custom AI Components

Daily AMS Data Sync Workflow

Type: workflow A Power Automate Scheduled Cloud Flow that runs daily at 5:00 AM to pull active policy data from the agency's AMS via API and synchronize it with the SharePoint Policy Renewal Tracker list. Handles creates, updates, and calculates DaysUntilExpiration. This is the foundational data pipeline for all renewal triggers.

Implementation:

Power Automate Flow: Daily AMS Policy Sync

Trigger

  • Type: Recurrence
  • Frequency: Day
  • Interval: 1
  • Time: 05:00 AM (agency local timezone)
  • Time Zone: (Set to agency's timezone, e.g., Eastern Standard Time)

Variables (Initialize at start)

1
varAMSApiKey (String) — Store in Azure Key Vault or flow variable
2
varAMSBaseUrl (String) — e.g., 'https://api.nowcerts.com/api' or AMS-specific endpoint
3
varSyncErrors (Array) — Collect any errors for end-of-flow notification
4
varProcessedCount (Integer) — Initialize to 0
5
varNewCount (Integer) — Initialize to 0
6
varUpdatedCount (Integer) — Initialize to 0

Actions

Step 1: HTTP — Get Active Policies from AMS

HTTP GET request to AMS API for active policies
http
Method: GET
URI: @{variables('varAMSBaseUrl')}/policies?status=active&limit=1000&offset=0
Headers:
  Authorization: Bearer @{variables('varAMSApiKey')}
  Content-Type: application/json
  Accept: application/json

Step 2: Parse JSON — Parse Policy Array

JSON schema for parsing the AMS policy array response
json
{
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "policyNumber": {"type": "string"},
      "insuredFirstName": {"type": "string"},
      "insuredLastName": {"type": "string"},
      "insuredEmail": {"type": "string"},
      "insuredPhone": {"type": "string"},
      "policyType": {"type": "string"},
      "effectiveDate": {"type": "string"},
      "expirationDate": {"type": "string"},
      "assignedCSR": {"type": "string"},
      "assignedProducer": {"type": "string"},
      "annualPremium": {"type": "number"},
      "status": {"type": "string"}
    }
  }
}

Step 3: Apply to Each — Process Each Policy

3a. Compose — Calculate Days Until Expiration

Power Automate expression to calculate days until policy expiration
power-fx
div(
  sub(
    ticks(items('Apply_to_each')?['expirationDate']),
    ticks(utcNow())
  ),
  864000000000
)

3b. Compose — Determine Risk Level

Power Automate expression to determine risk level based on annual premium
power-fx
if(
  greater(items('Apply_to_each')?['annualPremium'], 5000),
  'High',
  if(
    greater(items('Apply_to_each')?['annualPremium'], 2000),
    'Medium',
    'Low'
  )
)

3c. Get Items — Check if Policy Exists in SharePoint

  • Site: Renewal Operations
  • List: Policy Renewal Tracker
  • Filter Query: PolicyNumber eq '@{items('Apply_to_each')?['policyNumber']}'
  • Top Count: 1

3d. Condition — Policy Exists?

  • If length(body('Get_items')?['value']) > 0: Update Item in SharePoint — ID: first(body('Get_items')?['value'])?['ID'], ExpirationDate: items('Apply_to_each')?['expirationDate'], DaysUntilExpiration: outputs('Calculate_Days'), PremiumAmount: items('Apply_to_each')?['annualPremium'], RiskLevel: outputs('Determine_Risk_Level'), (Do NOT overwrite RenewalStage — preserve workflow state); then Increment varUpdatedCount
  • Else (new policy): Create Item in SharePoint — PolicyNumber: items('Apply_to_each')?['policyNumber'], InsuredName: concat(items('Apply_to_each')?['insuredFirstName'], ' ', items('Apply_to_each')?['insuredLastName']), InsuredEmail: items('Apply_to_each')?['insuredEmail'], InsuredPhone: items('Apply_to_each')?['insuredPhone'], PolicyType: items('Apply_to_each')?['policyType'], ExpirationDate: items('Apply_to_each')?['expirationDate'], AssignedCSR: items('Apply_to_each')?['assignedCSR'], AssignedProducer: items('Apply_to_each')?['assignedProducer'], RenewalStage: 'Not Started', PremiumAmount: items('Apply_to_each')?['annualPremium'], RiskLevel: outputs('Determine_Risk_Level'), DaysUntilExpiration: outputs('Calculate_Days'), SMSOptIn: false (default — must be manually verified); then Increment varNewCount
  • 3e. Increment varProcessedCount

Step 4: Send Summary Notification

Summary notification email sent after each sync run
text
To: msp-monitoring@yourmsp.com
Subject: [Renewal Sync] @{formatDateTime(utcNow(), 'yyyy-MM-dd')} — @{variables('varProcessedCount')} policies processed
Body:
  Sync completed at @{utcNow()}
  Total processed: @{variables('varProcessedCount')}
  New policies added: @{variables('varNewCount')}
  Existing policies updated: @{variables('varUpdatedCount')}
  Errors: @{length(variables('varSyncErrors'))}
  @{if(greater(length(variables('varSyncErrors')), 0), join(variables('varSyncErrors'), '<br>'), 'No errors')}

Error Handling

  • Wrap the HTTP action in a Scope with 'Configure run after' set to catch failures
  • On HTTP failure: Append to varSyncErrors, send alert email to MSP immediately
  • On SharePoint write failure: Append to varSyncErrors, continue processing

Pagination

  • If AMS returns paginated results, add a Do Until loop:
  • Do Until: length(body('HTTP')?['value']) equals 0
  • HTTP GET with offset = varProcessedCount
  • Process results
  • Increment offset

90-Day Renewal Email Template

Type: prompt HTML email template sent to policyholders 90 days before policy expiration. This is the initial friendly notification that the renewal period is approaching. It is warm, informational, and includes the agency's branding. Does not include sensitive PII.

Implementation:

Subject Line

Your {{PolicyType}} policy renews in 90 days — We're here to help

HTML Body

html
<!DOCTYPE html>
<html>
<head>
  <meta charset='utf-8'>
  <meta name='viewport' content='width=device-width, initial-scale=1.0'>
  <style>
    body { font-family: Arial, Helvetica, sans-serif; margin: 0; padding: 0; background-color: #f4f4f4; }
    .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }
    .header { background-color: {{AgencyPrimaryColor|#1a5276}}; padding: 24px; text-align: center; }
    .header img { max-height: 60px; }
    .header h1 { color: #ffffff; font-size: 20px; margin: 12px 0 0 0; }
    .body { padding: 32px 24px; color: #333333; line-height: 1.6; }
    .highlight-box { background: #eaf2f8; border-left: 4px solid #2980b9; padding: 16px; margin: 20px 0; border-radius: 4px; }
    .cta-button { display: inline-block; background: {{AgencyPrimaryColor|#1a5276}}; color: #ffffff; padding: 14px 28px; text-decoration: none; border-radius: 6px; font-weight: bold; margin: 16px 0; }
    .footer { background: #f8f8f8; padding: 20px 24px; font-size: 12px; color: #888888; text-align: center; border-top: 1px solid #eeeeee; }
    .footer a { color: #2980b9; }
  </style>
</head>
<body>
  <div class='container'>
    <div class='header'>
      <img src='{{AgencyLogoURL}}' alt='{{AgencyName}}'>
      <h1>Your Policy Renewal Is Approaching</h1>
    </div>
    <div class='body'>
      <p>Dear {{InsuredName}},</p>
      <p>This is a friendly reminder that your <strong>{{PolicyType}}</strong> policy is scheduled to renew in approximately <strong>90 days</strong>.</p>
      
      <div class='highlight-box'>
        <strong>Policy Details:</strong><br>
        Policy: ****{{PolicyNumberLast4}}<br>
        Type: {{PolicyType}}<br>
        Expiration Date: {{ExpirationDateFormatted}}
      </div>
      
      <p>Over the coming weeks, our team will review your coverage to ensure it still meets your needs. We'll look for opportunities to optimize your protection and find the best value.</p>
      
      <p><strong>What you can do now:</strong></p>
      <ul>
        <li>Think about any changes in your life or business that might affect your coverage needs</li>
        <li>Gather any questions you'd like to discuss during your renewal review</li>
        <li>Let us know if your contact information has changed</li>
      </ul>
      
      <p style='text-align: center;'>
        <a href='mailto:{{AgencyEmail}}?subject=Renewal Question - ****{{PolicyNumberLast4}}' class='cta-button'>Contact Us With Questions</a>
      </p>
      
      <p>Your dedicated team member, <strong>{{AssignedCSR}}</strong>, will be reaching out to you soon to schedule a renewal review.</p>
      
      <p>Thank you for being a valued client of {{AgencyName}}!</p>
      
      <p>Warm regards,<br>
      <strong>{{AgencyName}}</strong><br>
      {{AgencyPhone}}<br>
      {{AgencyEmail}}</p>
    </div>
    <div class='footer'>
      <p>{{AgencyName}} | {{AgencyAddress}}<br>
      License #{{AgencyLicenseNumber}}</p>
      <p><a href='{{UnsubscribeURL}}'>Unsubscribe</a> from renewal reminders<br>
      <em>Note: Unsubscribing from reminders does not affect your policy. Your policy will still renew or expire on the scheduled date.</em></p>
    </div>
  </div>
</body>
</html>

Template Variables (populated by Power Automate)

Power Automate Expression for PolicyNumberLast4

Power Automate expression to extract the last 4 characters of PolicyNumber
plaintext
substring(items('Apply_to_each')?['PolicyNumber'], sub(length(items('Apply_to_each')?['PolicyNumber']), 4), 4)

60-Day Renewal Email Template

Type: prompt HTML email template for the 60-day renewal touchpoint. More action-oriented than the 90-day template, includes a direct booking link for a renewal review appointment and references that this is a follow-up. Implementation:

Subject Line

ACTION NEEDED: Your {{PolicyType}} policy renews in 60 days

HTML Body

html
<!DOCTYPE html>
<html>
<head>
  <meta charset='utf-8'>
  <meta name='viewport' content='width=device-width, initial-scale=1.0'>
  <style>
    body { font-family: Arial, Helvetica, sans-serif; margin: 0; padding: 0; background-color: #f4f4f4; }
    .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }
    .header { background-color: #e67e22; padding: 24px; text-align: center; }
    .header img { max-height: 60px; }
    .header h1 { color: #ffffff; font-size: 20px; margin: 12px 0 0 0; }
    .body { padding: 32px 24px; color: #333333; line-height: 1.6; }
    .highlight-box { background: #fef5e7; border-left: 4px solid #e67e22; padding: 16px; margin: 20px 0; border-radius: 4px; }
    .cta-button { display: inline-block; background: #e67e22; color: #ffffff; padding: 14px 28px; text-decoration: none; border-radius: 6px; font-weight: bold; margin: 8px 4px; }
    .cta-secondary { display: inline-block; background: #ffffff; color: #e67e22; padding: 12px 24px; text-decoration: none; border-radius: 6px; font-weight: bold; margin: 8px 4px; border: 2px solid #e67e22; }
    .footer { background: #f8f8f8; padding: 20px 24px; font-size: 12px; color: #888888; text-align: center; border-top: 1px solid #eeeeee; }
    .footer a { color: #e67e22; }
  </style>
</head>
<body>
  <div class='container'>
    <div class='header'>
      <img src='{{AgencyLogoURL}}' alt='{{AgencyName}}'>
      <h1>⏰ 60 Days Until Your Policy Renews</h1>
    </div>
    <div class='body'>
      <p>Dear {{InsuredName}},</p>
      <p>Your <strong>{{PolicyType}}</strong> policy renewal is now just <strong>60 days away</strong>. Now is the ideal time to schedule a quick review to make sure you have the right coverage at the best price.</p>
      
      <div class='highlight-box'>
        <strong>Policy Details:</strong><br>
        Policy: ****{{PolicyNumberLast4}}<br>
        Type: {{PolicyType}}<br>
        Expiration Date: {{ExpirationDateFormatted}}<br>
        <strong>Days Remaining: ~60</strong>
      </div>
      
      <p><strong>Why review now?</strong></p>
      <ul>
        <li>Ensure your coverage still matches your current needs</li>
        <li>Explore potential savings with updated quotes</li>
        <li>Avoid last-minute rushes or coverage gaps</li>
        <li>Ask about any new discounts you may qualify for</li>
      </ul>
      
      <p style='text-align: center;'>
        <a href='{{BookingURL}}' class='cta-button'>📅 Schedule Your Renewal Review</a>
        <br>
        <a href='tel:{{AgencyPhoneClean}}' class='cta-secondary'>📞 Call Us: {{AgencyPhone}}</a>
      </p>
      
      <p>Your account representative <strong>{{AssignedCSR}}</strong> is ready to help. A 15-minute call is usually all it takes.</p>
      
      <p>Best regards,<br>
      <strong>{{AgencyName}}</strong><br>
      {{AgencyPhone}} | {{AgencyEmail}}</p>
    </div>
    <div class='footer'>
      <p>{{AgencyName}} | {{AgencyAddress}} | License #{{AgencyLicenseNumber}}</p>
      <p><a href='{{UnsubscribeURL}}'>Unsubscribe</a> from renewal reminders<br>
      <em>Unsubscribing does not affect your policy status.</em></p>
    </div>
  </div>
</body>
</html>

Additional Variable

  • {{BookingURL}} — Source: Microsoft Bookings page URL — e.g., https://outlook.office365.com/book/ABCInsurance@domain.com/
  • {{AgencyPhoneClean}} — Source: Phone number without formatting — e.g., +15551234567 (for tel: link)

30-Day Urgent Renewal Email Template

Type: prompt HTML email template for the 30-day urgent renewal notice. Uses red/urgent visual design, explicitly warns of potential coverage lapse, and strongly urges immediate contact. This is the final automated communication before the policy expires. Implementation:

Subject Line

🚨 URGENT: Your {{PolicyType}} policy expires in 30 days — Immediate action needed

HTML Body

30-Day Urgent Renewal Email — HTML Template
html
<!DOCTYPE html>
<html>
<head>
  <meta charset='utf-8'>
  <meta name='viewport' content='width=device-width, initial-scale=1.0'>
  <style>
    body { font-family: Arial, Helvetica, sans-serif; margin: 0; padding: 0; background-color: #f4f4f4; }
    .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }
    .header { background-color: #c0392b; padding: 24px; text-align: center; }
    .header img { max-height: 60px; }
    .header h1 { color: #ffffff; font-size: 22px; margin: 12px 0 0 0; }
    .urgent-banner { background: #e74c3c; color: #ffffff; text-align: center; padding: 12px; font-size: 16px; font-weight: bold; }
    .body { padding: 32px 24px; color: #333333; line-height: 1.6; }
    .highlight-box { background: #fdedec; border-left: 4px solid #e74c3c; padding: 16px; margin: 20px 0; border-radius: 4px; }
    .warning-box { background: #fff3cd; border: 2px solid #ffc107; padding: 16px; margin: 20px 0; border-radius: 4px; text-align: center; }
    .cta-button { display: inline-block; background: #c0392b; color: #ffffff; padding: 16px 32px; text-decoration: none; border-radius: 6px; font-weight: bold; font-size: 16px; margin: 8px 4px; }
    .cta-secondary { display: inline-block; background: #ffffff; color: #c0392b; padding: 14px 28px; text-decoration: none; border-radius: 6px; font-weight: bold; margin: 8px 4px; border: 2px solid #c0392b; }
    .footer { background: #f8f8f8; padding: 20px 24px; font-size: 12px; color: #888888; text-align: center; border-top: 1px solid #eeeeee; }
    .footer a { color: #c0392b; }
  </style>
</head>
<body>
  <div class='container'>
    <div class='header'>
      <img src='{{AgencyLogoURL}}' alt='{{AgencyName}}'>
      <h1>⚠️ Your Policy Expires in 30 Days</h1>
    </div>
    <div class='urgent-banner'>IMMEDIATE ACTION REQUIRED TO AVOID COVERAGE LAPSE</div>
    <div class='body'>
      <p>Dear {{InsuredName}},</p>
      <p>We're reaching out with urgency because your <strong>{{PolicyType}}</strong> policy is set to expire in just <strong>30 days</strong>, and we have not yet heard from you regarding your renewal.</p>
      
      <div class='highlight-box'>
        <strong>Policy Details:</strong><br>
        Policy: ****{{PolicyNumberLast4}}<br>
        Type: {{PolicyType}}<br>
        <strong>Expiration Date: {{ExpirationDateFormatted}}</strong><br>
        <strong style='color: #c0392b;'>⏰ Days Remaining: ~30</strong>
      </div>
      
      <div class='warning-box'>
        <strong>⚠️ Important:</strong> If your policy expires without renewal, you may have a gap in coverage that could leave you financially exposed. A lapse in coverage can also result in higher premiums when you reinstate.
      </div>
      
      <p><strong>Please contact us immediately</strong> so we can review your options and ensure continuous protection:</p>
      
      <p style='text-align: center;'>
        <a href='tel:{{AgencyPhoneClean}}' class='cta-button'>📞 CALL NOW: {{AgencyPhone}}</a>
        <br>
        <a href='{{BookingURL}}' class='cta-secondary'>📅 Book an Urgent Review</a>
        <br>
        <a href='mailto:{{AgencyEmail}}?subject=URGENT Renewal - ****{{PolicyNumberLast4}}' class='cta-secondary'>✉️ Email Us</a>
      </p>
      
      <p>Your dedicated representative <strong>{{AssignedCSR}}</strong> is standing by and will be calling you within the next few days. If you've already taken action on this renewal, please disregard this message.</p>
      
      <p>We value your business and want to make sure you stay protected.</p>
      
      <p>Sincerely,<br>
      <strong>{{AgencyName}}</strong><br>
      {{AgencyPhone}} | {{AgencyEmail}}</p>
    </div>
    <div class='footer'>
      <p>{{AgencyName}} | {{AgencyAddress}} | License #{{AgencyLicenseNumber}}</p>
      <p><a href='{{UnsubscribeURL}}'>Unsubscribe</a> from renewal reminders<br>
      <em>Unsubscribing does not affect your policy status. Your policy may still expire on the scheduled date if not renewed.</em></p>
    </div>
  </div>
</body>
</html>

Renewal Stage Transition Logic

Type: workflow Defines the deterministic state machine that governs how policies transition between renewal stages. This logic is implemented across the three trigger flows and also supports manual stage updates by CSRs. Ensures no duplicate notifications and proper escalation at each stage.

Renewal Stage State Machine

States

Not Started → 90-Day Notice Sent → 60-Day Notice Sent → 30-Day Notice Sent → [Terminal States] ├── Renewed ├── Lost └── Cancelled Additional intermediate state (manually set by CSR): Client Contacted — CSR has spoken with client, actively working on renewal Quote Provided — CSR has provided renewal quote, awaiting client decision

Transition Rules

Implementation in Power Automate Filter Queries

90-Day Flow Filter
text
DaysUntilExpiration ge 88 and DaysUntilExpiration le 92 and RenewalStage eq 'Not Started'
60-Day Flow Filter
text
DaysUntilExpiration ge 58 and DaysUntilExpiration le 62 and RenewalStage eq '90-Day Notice Sent'
30-Day Flow Filter (client communication)
text
DaysUntilExpiration ge 28 and DaysUntilExpiration le 32 and RenewalStage eq '60-Day Notice Sent'
30-Day Flow
text
# Internal Reminder for Active Renewals: Post to Teams only, do not email
# client.

DaysUntilExpiration ge 28 and DaysUntilExpiration le 32 and (RenewalStage eq 'Client Contacted' or RenewalStage eq 'Quote Provided')

Guard Rails

1
No duplicate sends: Each flow checks the current RenewalStage before acting. A policy can only receive one automated email per stage.
2
Manual override respected: If a CSR manually updates the stage to 'Client Contacted', 'Quote Provided', or 'Renewed', automated client-facing emails stop.
3
Backstop check: If DaysUntilExpiration < 0 and RenewalStage is NOT 'Renewed', 'Lost', or 'Cancelled', a daily 'Lapsed Policy Alert' flow sends an urgent Teams notification and email to the agency principal.
4
Re-entry prevention: A 'Renewed' policy that gets a new expiration date from the AMS sync will have DaysUntilExpiration recalculated. When it enters the 88-92 day window for the NEXT renewal cycle, the sync should reset RenewalStage to 'Not Started'. Implement this in the AMS sync flow: if ExpirationDate has changed AND the new date is >92 days out, set RenewalStage = 'Not Started'.

Lapsed Policy Alert Workflow

Type: workflow A daily Power Automate flow that identifies policies which have passed their expiration date without being marked as Renewed, Lost, or Cancelled. Sends urgent alerts to the agency principal and assigned CSR to take immediate action.

Implementation:

Power Automate Flow: Lapsed Policy Alert
plaintext
## Power Automate Flow: Lapsed Policy Alert

### Trigger
- Type: Recurrence
- Frequency: Day
- Interval: 1
- Time: 9:30 AM

### Actions

#### Step 1: Get Items from SharePoint
Site: Renewal Operations
List: Policy Renewal Tracker
Filter Query: DaysUntilExpiration lt 0 and RenewalStage ne 'Renewed' and RenewalStage ne 'Lost' and RenewalStage ne 'Cancelled'
Top Count: 500

#### Step 2: Condition — Any Lapsed Policies Found?
if length(body('Get_items')?['value']) > 0 then:

#### Step 3: Create HTML Table
From: body('Get_items')?['value']
Columns:
  - Policy Number: item()?['PolicyNumber']
  - Insured Name: item()?['InsuredName']
  - Policy Type: item()?['PolicyType']
  - Expiration Date: item()?['ExpirationDate']
  - Days Overdue: mul(item()?['DaysUntilExpiration'], -1)
  - Last Stage: item()?['RenewalStage']
  - Assigned CSR: item()?['AssignedCSR']
  - Premium: item()?['PremiumAmount']

#### Step 4: Send Urgent Email
To: agency-principal@domain.com; operations-manager@domain.com
CC: msp-monitoring@yourmsp.com
Subject: 🚨 LAPSED POLICIES ALERT — @{length(body('Get_items')?['value'])} policies expired without renewal
Body:
  <h2>Lapsed Policy Report — @{formatDateTime(utcNow(), 'MMMM dd, yyyy')}</h2>
  <p>The following policies have passed their expiration date and have NOT been marked as Renewed, Lost, or Cancelled in the system:</p>
  @{body('Create_HTML_table')}
  <p><strong>Immediate Action Required:</strong> Please contact these clients and update the Policy Renewal Tracker with the correct status.</p>
  <p><a href='https://YOURTENANT.sharepoint.com/sites/RenewalOps/Lists/Policy%20Renewal%20Tracker'>Open Renewal Tracker</a></p>
Importance: High

#### Step 5: Post to Teams
Team: Agency Operations
Channel: #renewal-escalations
Message: '🚨 **LAPSED POLICIES**: @{length(body('Get_items')?['value'])} policies have expired without renewal confirmation. Check your email for the full report. [Open Tracker](SharePoint URL)'

Renewal Metrics Dashboard (Power Automate Weekly Report)

Type: integration A weekly Power Automate flow that calculates key renewal metrics and sends a formatted executive summary to the agency principal every Monday morning. Tracks retention rate, pipeline status, CSR performance, and premium at risk.

Implementation:

Power Automate Flow: Weekly Renewal Metrics Report
plaintext
## Power Automate Flow: Weekly Renewal Metrics Report

### Trigger
- Type: Recurrence
- Frequency: Week
- On: Monday
- Time: 7:00 AM

### Actions

#### Step 1: Get All Active Renewal Records
Get items — SharePoint 'Policy Renewal Tracker'
Filter: DaysUntilExpiration le 90 and DaysUntilExpiration ge -30
Top Count: 5000

#### Step 2: Initialize Counter Variables
varTotal (Integer) = 0
varRenewed (Integer) = 0
varLost (Integer) = 0
varPending90 (Integer) = 0
varPending60 (Integer) = 0
varPending30 (Integer) = 0
varLapsed (Integer) = 0
varTotalPremiumAtRisk (Float) = 0
varRenewedPremium (Float) = 0
varLostPremium (Float) = 0

#### Step 3: Apply to Each — Count by Stage
For each item:
  Increment varTotal
  Switch on RenewalStage:
    'Renewed': Increment varRenewed, Add PremiumAmount to varRenewedPremium
    'Lost': Increment varLost, Add PremiumAmount to varLostPremium
    '90-Day Notice Sent' or 'Not Started': Increment varPending90, Add to varTotalPremiumAtRisk
    '60-Day Notice Sent' or 'Client Contacted': Increment varPending60, Add to varTotalPremiumAtRisk
    '30-Day Notice Sent' or 'Quote Provided': Increment varPending30, Add to varTotalPremiumAtRisk
    Default (DaysUntilExpiration < 0 and not Renewed/Lost/Cancelled): Increment varLapsed

#### Step 4: Calculate Retention Rate
Compose: varRetentionRate = 
  if(add(varRenewed, varLost) > 0,
    formatNumber(div(mul(varRenewed, 100.0), add(varRenewed, varLost)), 'N1'),
    'N/A — no policies have reached decision yet')

#### Step 5: Send Executive Summary Email
To: agency-principal@domain.com
CC: msp-account-manager@yourmsp.com
Subject: 📊 Weekly Renewal Report — @{formatDateTime(utcNow(), 'MMMM dd, yyyy')}
Body:
<h1>Weekly Renewal Pipeline Summary</h1>
<table style='border-collapse:collapse; width:100%;'>
  <tr style='background:#1a5276; color:white;'><th>Metric</th><th>Value</th></tr>
  <tr><td>Total Policies in Pipeline</td><td><strong>@{varTotal}</strong></td></tr>
  <tr style='background:#d4efdf;'><td>✅ Renewed</td><td><strong>@{varRenewed}</strong> ($@{formatNumber(varRenewedPremium, 'N0')} premium retained)</td></tr>
  <tr style='background:#fadbd8;'><td>❌ Lost</td><td><strong>@{varLost}</strong> ($@{formatNumber(varLostPremium, 'N0')} premium lost)</td></tr>
  <tr><td>📧 Pending — 90-Day Stage</td><td>@{varPending90}</td></tr>
  <tr><td>📞 Pending — 60-Day Stage</td><td>@{varPending60}</td></tr>
  <tr style='background:#fef9e7;'><td>🚨 Pending — 30-Day Stage</td><td><strong>@{varPending30}</strong></td></tr>
  <tr style='background:#fdedec;'><td>⚠️ Lapsed (No Decision)</td><td><strong>@{varLapsed}</strong></td></tr>
  <tr style='background:#1a5276; color:white;'><td>📈 Retention Rate</td><td><strong>@{varRetentionRate}%</strong></td></tr>
  <tr><td>💰 Premium at Risk (Pending)</td><td>$@{formatNumber(varTotalPremiumAtRisk, 'N0')}</td></tr>
</table>
<br>
<p><a href='https://YOURTENANT.sharepoint.com/sites/RenewalOps/Lists/Policy%20Renewal%20Tracker'>View Full Renewal Tracker →</a></p>
<p style='color:#888; font-size:12px;'>This report is generated automatically by your renewal automation system managed by {{MSPName}}.</p>

### Notes
- This report gives the agency principal a single metric (retention rate) that quantifies the automation's ROI.
- MSP should review this report monthly during QBR to show value and identify optimization opportunities.
- If retention rate drops below 80%, flag it in the QBR as needing process review.

Testing & Validation

  • AMS API Connectivity Test: Execute the API call from Step 2 manually via PowerShell or Postman. Confirm the response returns active policy records with all required fields (policyNumber, insuredName, insuredEmail, expirationDate, etc.). Record the response time — should be under 5 seconds for up to 1,000 policies.
  • SharePoint Data Sync Validation: After running the Daily AMS Policy Sync flow, compare 20 randomly selected policies in the SharePoint list against the AMS source data. Verify all fields match: policy number, insured name, expiration date, assigned CSR, premium amount. Confirm DaysUntilExpiration is calculated correctly (within ±1 day).
  • 90-Day Trigger Accuracy Test: Insert a test policy with ExpirationDate = today + 90 days and RenewalStage = 'Not Started'. Run the 90-Day Renewal Trigger flow manually. Verify: (1) email arrives at the test email address within 2 minutes, (2) email renders correctly in Outlook, Gmail, and mobile, (3) subject line and body contain correct policy details with only last 4 digits of policy number, (4) SMS arrives if SMSOptIn is true, (5) Planner task is created with correct assignee and due date, (6) SharePoint item updated to '90-Day Notice Sent', (7) Audit log entry created.
  • 60-Day Trigger Accuracy Test: Update the test policy to DaysUntilExpiration = 60 and RenewalStage = '90-Day Notice Sent'. Run the 60-Day flow. Verify all same criteria as 90-day plus: (1) email uses 60-day template with orange branding, (2) booking link is functional, (3) Teams escalation posts for High risk policies only.
  • 30-Day Trigger Accuracy Test: Update the test policy to DaysUntilExpiration = 30 and RenewalStage = '60-Day Notice Sent'. Run the 30-Day flow. Verify: (1) urgent email template with red branding, (2) Planner task has 3-day due date and Urgent priority, (3) Teams channel receives post for ALL 30-day policies, (4) principal receives escalation email for policies with premium > $5,000.
  • Duplicate Prevention Test: Run the 90-Day flow twice in succession on the same test policy. Verify the second run does NOT send a duplicate email — the filter should exclude the policy because RenewalStage is now '90-Day Notice Sent', not 'Not Started'.
  • Manual Override Respect Test: Set a test policy RenewalStage to 'Client Contacted' manually. Run the 60-Day flow. Verify the policy is NOT included in the automated email batch — the filter only matches '90-Day Notice Sent'.
  • Lapsed Policy Alert Test: Set a test policy DaysUntilExpiration to -3 with RenewalStage = '30-Day Notice Sent'. Run the Lapsed Policy Alert flow. Verify the urgent email is sent to the agency principal and the Teams post appears in #renewal-escalations.
  • Email Deliverability Test: Send test emails to at least 3 external email providers (Gmail, Yahoo, Outlook.com). Verify: (1) emails land in inbox, not spam/junk, (2) SPF/DKIM/DMARC pass (check email headers), (3) images render correctly, (4) all links work, (5) unsubscribe link is functional.
  • SMS Opt-Out Test: Send a test SMS to a test phone number, then reply STOP. Verify: (1) opt-out confirmation message is received, (2) subsequent SMS attempts to that number are blocked by Twilio, (3) the SMS opt-out does not affect email notifications.
  • Weekly Report Validation: Run the Weekly Renewal Metrics flow with a known set of test data (e.g., 5 Renewed, 2 Lost, 3 Pending). Verify the retention rate calculation is correct (5/7 = 71.4%), premium figures are accurate, and the email renders correctly on mobile devices.
  • Performance Test: Load the SharePoint list with 500+ policy records (use a CSV import). Run all flows and verify: (1) Daily sync completes within 15 minutes, (2) trigger flows process all matching policies without timeout, (3) no Power Automate throttling errors in the flow run history.
  • Security Validation: Confirm (1) MFA is enabled on all accounts involved in the automation, (2) the Power Automate service account does not have Global Admin rights, (3) SharePoint permissions are correctly configured (CSRs can edit Tracker, only service account can write to Audit Log), (4) API keys are stored securely (not in plain text in flow definitions).

Client Handoff

Client Handoff Checklist

Training Session (60–90 minutes, recorded via Teams)

1
System Overview (15 min): Walk through the entire automation flow end-to-end using a whiteboard diagram. Explain what happens at 90, 60, and 30 days. Show the state machine and how manual overrides work.
2
SharePoint Tracker Training (20 min): Demonstrate how to view, filter, and update the Policy Renewal Tracker. Cover: how to change RenewalStage when making manual contact, how to add notes, how to view the audit log, how to use the 'Upcoming 90 Days' and 'Overdue' views.
3
Planner/Tasks Training (10 min): Show how renewal tasks appear in Microsoft Planner, how to mark them complete, and how task assignment works.
4
Teams Notifications (5 min): Show the #renewal-escalations channel and explain what each alert type means.
5
Weekly Report Walkthrough (10 min): Review a sample weekly metrics report with the agency principal. Explain each metric, especially retention rate and premium at risk.
6
Edge Cases and Manual Overrides (10 min): Cover what to do when: a client calls to renew before 90 days (update to 'Renewed'), a client cancels, a policy is non-renewed by the carrier, or data is wrong in the system.
7
Escalation and Support (5 min): Explain how to contact the MSP for issues. Provide the support email/phone and expected response times.

Documentation to Leave Behind

1
Quick Reference Card (laminated, one per CSR desk): One-page summary of renewal stages, how to update SharePoint, and escalation contacts.
2
Email Template Gallery: Printed or PDF showing all three email templates (90/60/30 day) so staff know exactly what clients are receiving.
3
System Architecture Diagram: One-page visual showing AMS → SharePoint → Power Automate → Email/SMS/Tasks flow.
4
Admin Runbook (for agency ops manager): How to modify email templates, adjust trigger timing, add/remove staff from task assignments, and read the audit log.
5
Compliance Documentation Binder: Information security program documentation, data flow diagrams for GLBA compliance, annual risk assessment template, incident response plan template.
6
Training Recording: Teams recording of the training session, stored in the Renewal Operations SharePoint site.

Success Criteria to Review at 30-Day Post-Go-Live QBR

Maintenance

Ongoing Maintenance Plan

Daily (Automated + MSP Spot-Check)

  • Automated: Daily AMS Sync flow runs at 5:00 AM; success/failure notification sent to MSP monitoring inbox.
  • MSP Action: Check the monitoring inbox for any sync failure alerts. If a failure occurs, investigate within 4 hours (SLA). Common causes: AMS API key expiration, API rate limit changes, SharePoint list threshold exceeded.

Weekly

  • Monday: Review the automated Weekly Renewal Metrics email. Flag any anomalies: sudden drop in retention rate, unusual spike in lapsed policies, or sync count discrepancies.
  • Wednesday: Quick check of Power Automate flow run history — verify all three trigger flows ran successfully in the past 7 days. Check for any throttling or connector errors.
  • Ongoing: Monitor the renewals@domain.com shared mailbox for bounce-backs, out-of-office replies, and client responses that may indicate issues.

Monthly

  • Flow Performance Review: Check Power Automate analytics for flow run durations, success rates, and error patterns. Optimize any flows exceeding 10-minute runtime.
  • Email Deliverability Audit: Review SPF/DKIM/DMARC reports from EasyDMARC. Check if renewal emails are being flagged by any major email provider.
  • SharePoint List Health: Verify item count is within SharePoint list view threshold (5,000 items per view). If approaching limit, archive renewed/lost/cancelled policies older than 6 months to a separate archive list.
  • Security Review: Verify MFA is still enabled on all accounts. Check for any new user accounts that need Planner/SharePoint access. Rotate API keys if required by AMS vendor policy.

Quarterly (Aligned with QBR)

  • Business Review with Agency Principal: Present retention rate trends, automation ROI (premium retained vs. pre-automation baseline), CSR task completion rates, and recommendations for optimization.
  • Template Refresh: Review email/SMS templates for accuracy — update agency phone numbers, addresses, licensing info, seasonal messaging, or regulatory changes.
  • AMS Update Check: Verify AMS vendor has not changed their API endpoints or authentication methods. Test the API connection.
  • Compliance Checkpoint: Review NAIC state adoption status — confirm the agency is compliant with any newly adopted state data security requirements. Update the information security program documentation if needed.

Semi-Annual

  • Annual Risk Assessment (NAIC requirement): Assist the agency in completing or updating their information security risk assessment. Document the renewal automation system's data handling practices.
  • Disaster Recovery Test: Simulate a flow failure scenario — disable the Daily AMS Sync flow for 48 hours, verify alert triggers, re-enable and confirm catch-up sync processes all missed data.

Annual

  • Full System Audit: Comprehensive review of all Power Automate flows, SharePoint data integrity, API connections, and compliance documentation.
  • Pricing Review: Evaluate if the agency's policy count has grown to warrant a move from SharePoint lists to Dataverse, or if a native insurance CRM (InsuredMine, AgencyZoom) would now be more cost-effective than the custom Power Automate approach.
  • Renewal of MSP Managed Services Agreement: Present Year-in-Review metrics and renew the maintenance contract.

SLA Commitments

Escalation Path

1
Agency staff notices issue → Email/call MSP helpdesk
2
MSP L1 tech triages → checks flow run history, SharePoint list, API connectivity
3
If unresolved in 2 hours → Escalate to L2 (original implementation engineer)
4
If platform issue (Power Automate outage, AMS API down) → Open ticket with Microsoft/AMS vendor, notify agency of ETA
5
Monthly escalation summary included in QBR

Alternatives

Native AMS/CRM Renewal Automation (InsuredMine or AgencyZoom)

Instead of building custom Power Automate workflows, deploy an insurance-specific CRM platform that has built-in renewal pipeline automation. InsuredMine ($10–90/user/month) connects natively to NowCerts, AMS360, and QQCatalyst. AgencyZoom ($75–150/user/month) integrates with all Vertafore products. Both offer pre-built 90/60/30-day renewal campaigns, email templates, task management, and analytics dashboards out of the box with minimal configuration.

Self-Hosted n8n Workflow Engine

Deploy n8n (open-source workflow automation) on a self-hosted VM or Docker container managed by the MSP. Build the same 90/60/30-day trigger logic using n8n's visual workflow builder with HTTP nodes for AMS API calls, email nodes for SMTP delivery, and webhook nodes for Twilio SMS. The MSP hosts the platform and can white-label the solution across multiple insurance agency clients.

Zapier-Based Integration (Low-Code Quick Start)

Use Zapier Professional ($20–104/month) to connect the AMS to email, SMS, and task management tools. Create multi-step Zaps triggered by schedule that query a Google Sheet or Airtable (populated by AMS export) for upcoming expirations. Zapier's pre-built connectors simplify integration with Gmail, Outlook, Twilio, Slack, and hundreds of other tools.

EZLynx Native Renewal Automation

For agencies already on the EZLynx Management System, leverage EZLynx's built-in retention workflows that automatically manage renewals. EZLynx's patented retention features include automated renewal insights, proactive client engagement, and risk-based triage that auto-processes low-risk renewals while flagging high-risk clients for personal attention.

Outsourced Automation Implementation (Gravity Certs / Accelerated Automation)

Engage a specialized insurance automation consulting firm to implement the renewal workflows. Companies like Gravity Certs and Accelerated Automation specialize in AgencyZoom and AMS workflow optimization for insurance agencies, with project costs averaging $5,000–$15,000 and timelines of 2–6 months.

Want early access to the full toolkit?

← All Insurance Agencies solutions