Lakeview Insurance Group

The Office Manager opened a Google Sheet titled “2026 Renewal Tracker” on 87 of 92 working days. That’s 1,247 Google Sheets sessions in 90 days, averaging 22 minutes per session. The workflow: open Applied Epic → find upcoming renewals → copy policy data → paste into the spreadsheet → add status notes → repeat.

Decoded Renewal Tracking Workflow (Mar 6 telemetry)

The Office Manager built this spreadsheet because Applied Epic’s renewal workflow was never configured. She didn’t choose a spreadsheet over the system — the system was never set up to offer what she needed. She improvised. The improvisation works, but it has a fatal flaw: it’s a single point of failure. When she’s out sick or on vacation, nobody knows which 30-day letters haven’t been sent. The renewal pipeline stops.

The Fix: Applied Epic Renewal Workflow Activation

Built Tool: Renewal Pipeline Dashboard

While Epic’s native workflow is the long-term fix, we built an HTML dashboard that reads from an Epic CSV export and gives you real-time pipeline visibility. This bridges the gap during the Epic configuration period.

The Claims Coordinator spent 2,142 minutes across 487 carrier portal sessions in 30 days checking the status of 37 active claims. That’s an average of 4.4 minutes per status check — login, navigate, find claim, read status, note it, logout — repeated 14.2 times per day across 5 carrier portals.

Decoded Claims Check Workflow (Feb 20 telemetry)

67% of claim status checks find no new information. The claim status hasn’t changed since yesterday. The Claims Coordinator logs in, checks, finds nothing new, moves on. That’s 9.5 portal logins per day that return zero value. Automated status feeds eliminate this entirely — the system only notifies when something actually changes.

The Fix: IVANS Claim Download + AI Status Parser

Client Status Email Templates (copy into Outlook)

TEMPLATE 1: ADJUSTER ASSIGNED
Subject: Your [CARRIER] Claim Update — Adjuster Assigned

Hi [FIRST NAME],

Quick update on your claim ([CLAIM NUMBER]): [CARRIER] has assigned
adjuster [ADJUSTER NAME] to your case. They should be reaching out
within 1-2 business days to schedule an inspection.

If you don't hear from them by [DATE + 3 DAYS], let me know and I'll
follow up directly.

[AGENT SIGNATURE]

---

TEMPLATE 2: INSPECTION SCHEDULED
Subject: Your [CARRIER] Claim — Inspection Scheduled [DATE]

Hi [FIRST NAME],

Your inspection has been scheduled for [DATE] at [TIME]. The adjuster
([ADJUSTER NAME]) will be looking at [BRIEF DESCRIPTION OF DAMAGE].

A few things to have ready:
- Photos of the damage (if you have them)
- Any receipts for emergency repairs you've already made
- Your copy of the police report (if applicable)

Call me if you need anything before then.

[AGENT SIGNATURE]

---

TEMPLATE 3: PAYMENT ISSUED
Subject: Your [CARRIER] Claim — Payment Issued

Hi [FIRST NAME],

[CARRIER] has issued a payment of [AMOUNT] for your claim
([CLAIM NUMBER]). You should receive the check within 5-7 business
days at your mailing address on file.

If the amount doesn't match your expectations or if you have
questions about the settlement, call me and I'll walk through the
breakdown with you.

[AGENT SIGNATURE]

---

TEMPLATE 4: CLAIM CLOSED
Subject: Your [CARRIER] Claim — Closed

Hi [FIRST NAME],

Your claim ([CLAIM NUMBER]) has been marked as closed by [CARRIER].
Final settlement: [AMOUNT].

If anything changes — reopening the claim, supplemental damage, or
questions about your coverage going forward — I'm here.

[AGENT SIGNATURE]Copy

AI Status Parser for Nationwide Email Notifications

Nationwide sends claim updates as email, not IVANS feeds. Use this Gemini Flash prompt to parse them into structured data. Gemini Flash is the right model here — it’s fast, cheap ($0.075/1M tokens), and parsing structured email is a simple task that doesn’t need a frontier model. At 3.2 Nationwide claims/day, monthly cost is under $0.50.

// Gemini Gem: "Nationwide Claim Parser"
// Go to gemini.google.com → Gems → Create
// Paste this as the system prompt:

You parse Nationwide Insurance claim status notification emails.

For each email, extract:
- Claim number
- Insured name
- Status change (what changed)
- New status (current state)
- Action required (yes/no, and what)
- Key dates mentioned

Output as a structured table row:
CLAIM# | INSURED | OLD STATUS → NEW STATUS | ACTION | DATES

If the email contains no actual status change (just a confirmation
or acknowledgment), output: "NO CHANGE — [one-line summary]"

Example input: "Claim NW-2026-18847 for Robert Martinez has been
updated. The field adjuster has completed their inspection and
submitted their estimate. Estimated repair cost: $4,217. The
insured will receive payment within 7-10 business days."

Example output:
NW-2026-18847 | Martinez, Robert | Inspection → Estimate Complete |
Payment pending | Est: $4,217, Payment ETA: 7-10 daysCopy

The Bookkeeper works 20–22 hours per month. 17 of those hours go to commission reconciliation. That leaves 3–5 hours for everything else — receivables, carrier statement review, QuickBooks entries, financial reporting. She’s a financial professional spending 85% of her time on mechanical data matching.

Decoded Reconciliation Workflow (Mar 12 telemetry)

The Fix: Production-Quality Commission Reconciliation Script

This Google Apps Script is paste-ready. It pulls carrier statement CSVs from Gmail, matches against an Applied Epic commission export, outputs a color-coded exception report, emails you a summary, and logs everything. The Bookkeeper reviews 16 exceptions instead of 282 line items.

/**
 * Commission Reconciliation — Lakeview Insurance Group
 * Version 1.0 | Production-ready
 *
 * WHAT IT DOES:
 * 1. Searches Gmail for carrier commission statement attachments (CSV)
 * 2. Reads your Applied Epic commission export from a Google Drive folder
 * 3. Matches every line item by policy number
 * 4. Outputs ONLY the mismatches to a "Commission Exceptions" sheet
 * 5. Color-codes exceptions: yellow (timing), orange (endorsement), red (error)
 * 6. Emails a summary to the bookkeeper when it finishes
 * 7. Logs every run to a "Reconciliation Log" sheet for audit trail
 *
 * SETUP (5 minutes, zero technical knowledge):
 * 1. Open Google Sheets (the spreadsheet you want results in)
 * 2. Click Extensions → Apps Script
 * 3. Delete everything in the editor
 * 4. Paste this entire script
 * 5. Update the CONFIG section below (3 fields to change)
 * 6. Click the disk icon (Save)
 * 7. Click Run → reconcileCommissions
 * 8. Google will ask for permissions — click "Review permissions"
 *    → Choose your account → "Advanced" → "Go to Commission Recon"
 *    → "Allow" (this lets the script read your Gmail and Drive)
 * 9. Set up auto-run: click the clock icon (Triggers) → "+ Add Trigger"
 *    → Function: reconcileCommissions → Event: Time-driven
 *    → Month timer → 1st of month → 9am-10am → Save
 *
 * Don't know how to do any of this? Copy this entire code block
 * and paste it into ChatGPT or Claude. Say "help me set this up."
 */

// ===== CHANGE THESE 3 THINGS =====
const CONFIG = {
  // 1. Your Google Drive folder ID for Epic exports
  //    (Open the folder in Drive → copy the long string after /folders/ in the URL)
  epicExportFolderId: 'PASTE_YOUR_FOLDER_ID_HERE',

  // 2. Email address to receive the summary
  notifyEmail: '[email protected]',

  // 3. Your agency name (for the email subject)
  agencyName: 'Lakeview Insurance Group',

  // ===== DON'T CHANGE BELOW THIS LINE =====
  exceptionSheetName: 'Commission Exceptions',
  logSheetName: 'Reconciliation Log',
  threshold: 0.50,  // flag differences > $0.50
  carriers: {
    'Travelers': {
      searchQuery: 'from:[email protected] has:attachment',
      policyCol: 0,   // column A = policy number
      amountCol: 4,   // column E = commission amount
      nameCol: 1      // column B = insured name (for reference)
    },
    'Hartford': {
      searchQuery: 'from:[email protected] has:attachment',
      policyCol: 0,
      amountCol: 3,
      nameCol: 1
    },
    'Progressive': {
      searchQuery: 'from:[email protected] has:attachment',
      policyCol: 1,
      amountCol: 5,
      nameCol: 2
    },
    'Nationwide': {
      searchQuery: 'from:[email protected] has:attachment',
      policyCol: 0,
      amountCol: 4,
      nameCol: 1
    },
    'Erie': {
      searchQuery: 'from:[email protected] has:attachment',
      policyCol: 0,
      amountCol: 3,
      nameCol: 1
    }
  }
};

function reconcileCommissions() {
  const startTime = new Date();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const errors = [];

  // --- Set up exception sheet ---
  let sheet = ss.getSheetByName(CONFIG.exceptionSheetName);
  if (!sheet) sheet = ss.insertSheet(CONFIG.exceptionSheetName);
  sheet.clear();

  const headers = [
    'Carrier', 'Policy Number', 'Insured Name', 'Statement Amount',
    'Epic Amount', 'Difference', 'Exception Type', 'Status', 'Notes'
  ];
  sheet.appendRow(headers);
  sheet.getRange(1, 1, 1, headers.length)
    .setFontWeight('bold')
    .setBackground('#f5f2ec')
    .setFontSize(10);
  sheet.setFrozenRows(1);

  // --- Load Epic export ---
  let epicData;
  try {
    epicData = loadEpicExport_();
    if (Object.keys(epicData).length === 0) {
      errors.push('WARNING: No Epic export data found. Check that CSV files exist in the Drive folder.');
    }
  } catch (e) {
    errors.push('ERROR loading Epic export: ' + e.message);
    logRun_(ss, startTime, 0, 0, 0, errors);
    sendNotification_(0, 0, 0, errors);
    return;
  }

  // --- Process each carrier ---
  let totalProcessed = 0;
  let totalExceptions = 0;
  let totalMatched = 0;
  let totalStatementAmount = 0;
  const carrierSummaries = [];

  Object.keys(CONFIG.carriers).forEach(carrier => {
    const config = CONFIG.carriers[carrier];
    let statementData;

    try {
      statementData = getLatestStatement_(carrier, config);
    } catch (e) {
      errors.push('ERROR reading ' + carrier + ' statement: ' + e.message);
      return;
    }

    if (!statementData || statementData.length === 0) {
      errors.push('INFO: No statement found for ' + carrier + ' in last 35 days.');
      return;
    }

    let carrierProcessed = 0;
    let carrierExceptions = 0;
    let carrierTotal = 0;

    statementData.forEach(row => {
      const policyNum = normalizePolicy_(row.policyNumber);
      const stmtAmount = parseFloat(row.amount);
      const insuredName = row.insuredName || '';

      if (isNaN(stmtAmount)) {
        errors.push('WARNING: Non-numeric amount for policy ' + policyNum + ' in ' + carrier);
        return;
      }

      totalProcessed++;
      carrierProcessed++;
      carrierTotal += stmtAmount;
      totalStatementAmount += stmtAmount;

      const epicRecord = epicData[policyNum];
      const epicAmount = epicRecord ? parseFloat(epicRecord.amount) : null;

      if (epicAmount === null) {
        sheet.appendRow([
          carrier, policyNum, insuredName, stmtAmount,
          'NOT FOUND', stmtAmount, 'Missing in Epic', 'Review', ''
        ]);
        colorRow_(sheet, sheet.getLastRow(), '#fce4ec'); // red
        totalExceptions++;
        carrierExceptions++;
        return;
      }

      const diff = stmtAmount - epicAmount;
      if (Math.abs(diff) > CONFIG.threshold) {
        const exType = classifyException_(stmtAmount, epicAmount, Math.abs(diff));
        const colors = { 'Timing': '#fff8e1', 'Endorsement': '#fff3e0', 'Error': '#fce4ec' };

        sheet.appendRow([
          carrier, policyNum, insuredName, stmtAmount,
          epicAmount, diff.toFixed(2), exType, 'Review', ''
        ]);
        colorRow_(sheet, sheet.getLastRow(), colors[exType] || '#fff8e1');
        totalExceptions++;
        carrierExceptions++;
      } else {
        totalMatched++;
      }
    });

    carrierSummaries.push({
      carrier: carrier,
      processed: carrierProcessed,
      exceptions: carrierExceptions,
      total: carrierTotal.toFixed(2)
    });
  });

  // --- Summary rows ---
  const summaryStartRow = sheet.getLastRow() + 2;
  sheet.appendRow([]);
  sheet.appendRow(['RECONCILIATION SUMMARY', '', '', '', '', '', '', '', '']);
  sheet.appendRow(['Run date:', startTime.toLocaleDateString()]);
  sheet.appendRow(['Total line items:', totalProcessed]);
  sheet.appendRow(['Auto-matched:', totalMatched]);
  sheet.appendRow(['Exceptions:', totalExceptions]);
  sheet.appendRow([
    'Match rate:',
    totalProcessed > 0
      ? ((totalMatched / totalProcessed) * 100).toFixed(1) + '%'
      : 'N/A'
  ]);
  sheet.appendRow(['Total commission amount:', '$' + totalStatementAmount.toFixed(2)]);
  sheet.appendRow([]);
  sheet.appendRow(['PER-CARRIER BREAKDOWN:']);
  carrierSummaries.forEach(cs => {
    sheet.appendRow([cs.carrier, cs.processed + ' items', cs.exceptions + ' exceptions', '$' + cs.total]);
  });

  sheet.getRange(summaryStartRow + 1, 1, 1, 9).setFontWeight('bold');

  // --- Auto-size columns ---
  for (let i = 1; i <= headers.length; i++) {
    sheet.autoResizeColumn(i);
  }

  // --- Log this run ---
  logRun_(ss, startTime, totalProcessed, totalMatched, totalExceptions, errors);

  // --- Email notification ---
  sendNotification_(totalProcessed, totalMatched, totalExceptions, errors, carrierSummaries);

  Logger.log('Reconciliation complete: ' + totalProcessed + ' items, '
    + totalMatched + ' matched, ' + totalExceptions + ' exceptions');
}

// ===== HELPER FUNCTIONS =====

function loadEpicExport_() {
  const folder = DriveApp.getFolderById(CONFIG.epicExportFolderId);
  const files = folder.getFilesByType(MimeType.CSV);
  const epicData = {};

  while (files.hasNext()) {
    const file = files.next();
    const csv = Utilities.parseCsv(file.getBlob().getDataAsString());
    csv.forEach((row, i) => {
      if (i === 0) return; // skip header
      if (row.length < 4) return; // skip malformed rows
      const policyNum = normalizePolicy_(row[0]);
      epicData[policyNum] = {
        amount: row[3],
        name: row[1] || ''
      };
    });
  }
  return epicData;
}

function getLatestStatement_(carrier, config) {
  const threads = GmailApp.search(config.searchQuery + ' newer_than:35d', 0, 1);
  if (threads.length === 0) return null;

  const messages = threads[0].getMessages();
  const lastMsg = messages[messages.length - 1];
  const attachments = lastMsg.getAttachments();

  for (const att of attachments) {
    const name = att.getName().toLowerCase();
    if (name.endsWith('.csv')) {
      const csv = Utilities.parseCsv(att.getDataAsString());
      return csv.slice(1)
        .filter(row => row.length > Math.max(config.policyCol, config.amountCol))
        .map(row => ({
          policyNumber: row[config.policyCol],
          amount: row[config.amountCol],
          insuredName: config.nameCol !== undefined ? (row[config.nameCol] || '') : ''
        }));
    }
  }
  return null;
}

function normalizePolicy_(num) {
  return String(num).replace(/[\s\-]/g, '').toUpperCase().trim();
}

function classifyException_(stmt, epic, diff) {
  // Small differences are usually timing (payment processed in different months)
  if (diff < 50) return 'Timing';
  // Large ratio differences are usually mid-term endorsement adjustments
  if (Math.abs(stmt) > Math.abs(epic) * 1.5
      || Math.abs(epic) > Math.abs(stmt) * 1.5) return 'Endorsement';
  // Everything else needs manual review
  return 'Error';
}

function colorRow_(sheet, row, color) {
  sheet.getRange(row, 1, 1, 9).setBackground(color);
}

function logRun_(ss, startTime, processed, matched, exceptions, errors) {
  let logSheet = ss.getSheetByName(CONFIG.logSheetName);
  if (!logSheet) {
    logSheet = ss.insertSheet(CONFIG.logSheetName);
    logSheet.appendRow(['Date', 'Processed', 'Matched', 'Exceptions', 'Match Rate', 'Duration (s)', 'Errors']);
    logSheet.getRange(1, 1, 1, 7).setFontWeight('bold').setBackground('#f5f2ec');
  }

  const duration = ((new Date() - startTime) / 1000).toFixed(1);
  const matchRate = processed > 0 ? ((matched / processed) * 100).toFixed(1) + '%' : 'N/A';
  logSheet.appendRow([
    startTime.toLocaleDateString() + ' ' + startTime.toLocaleTimeString(),
    processed, matched, exceptions, matchRate, duration,
    errors.length > 0 ? errors.join('; ') : 'None'
  ]);
}

function sendNotification_(processed, matched, exceptions, errors, carrierSummaries) {
  if (!CONFIG.notifyEmail) return;

  let body = CONFIG.agencyName + ' — Commission Reconciliation Complete\n\n';
  body += 'Total line items: ' + processed + '\n';
  body += 'Auto-matched: ' + matched + '\n';
  body += 'Exceptions for review: ' + exceptions + '\n';
  body += 'Match rate: ' + (processed > 0 ? ((matched / processed) * 100).toFixed(1) : 0) + '%\n\n';

  if (carrierSummaries && carrierSummaries.length > 0) {
    body += 'Per carrier:\n';
    carrierSummaries.forEach(cs => {
      body += '  ' + cs.carrier + ': ' + cs.processed + ' items, '
        + cs.exceptions + ' exceptions, $' + cs.total + '\n';
    });
    body += '\n';
  }

  if (exceptions > 0) {
    body += '→ Open the "Commission Exceptions" tab to review the ' + exceptions + ' items that need attention.\n\n';
  } else {
    body += '→ Everything matched. No action needed this month.\n\n';
  }

  if (errors.length > 0) {
    body += 'Warnings/Errors:\n';
    errors.forEach(e => { body += '  • ' + e + '\n'; });
  }

  try {
    GmailApp.sendEmail(
      CONFIG.notifyEmail,
      CONFIG.agencyName + ' Commission Recon — ' + exceptions + ' exceptions',
      body
    );
  } catch (e) {
    Logger.log('Failed to send notification email: ' + e.message);
  }
}Copy

Don’t know how to do this? Copy this entire section and paste it into ChatGPT or Claude. Say “help me set this up step by step.” It will walk you through everything.

In 90 days, Producer Support created 69 proposals (23/month avg). Each one follows the same manual pipeline: open Applied Epic client record, copy insured name/address/coverage details, switch to Word template, paste and reformat, copy premium options, build comparison table, draft cover letter from scratch, save as PDF, email via Outlook.

Decoded Proposal Workflow (Jan 29, commercial BOP)

Every cover letter is written from scratch even though they’re 80% identical. The data shows 69 cover letters produced over the quarter. After removing client-specific details, they share the same structure: thank you, coverage summary, why this recommendation, call to action. An AI trained on these 69 examples produces indistinguishable cover letters in 3 seconds.

The Fix

AI Cover Letter Generator

Which model and why: Use Claude Opus or GPT-4o for cover letters. Tone matters here — these go to clients, so you want the model that writes most naturally. Sonnet and Flash produce competent but detectably-AI text. At 23 proposals/month averaging 150 output tokens each, monthly cost is approximately $0.35–$0.52 on Opus, essentially free on Sonnet. Use Opus.

Option A: Set up as a Gemini Gem (easiest, no coding)

Option B: Set up in Claude (better output quality)

System Prompt (copy into either platform)

You are the proposal writer for Lakeview Insurance Group, an independent
P&C agency in the suburbs of Chicago. Write professional, warm cover
letters for insurance proposals.

VOICE: Professional but approachable. Not stiff or corporate. These are
suburban families and local businesses who know us by name. Sound like a
trusted neighbor who happens to know insurance, not a corporation.

STRUCTURE:
1. Thank them for the opportunity (1 sentence)
2. Summarize what you reviewed — specific lines, not "your insurance"
3. Your recommendation and the specific dollar benefit
4. One coverage gap you noticed (if applicable)
5. Clear next step — call, meet, or reply

RULES:
- 4-5 sentences maximum. Nobody reads long cover letters.
- First name only. "Hi Sarah" not "Dear Mrs. Chen"
- Name the carrier: "By bundling with Travelers" not "by bundling"
- Name the dollar amount: "$847 per year" not "significant savings"
- If renewal: acknowledge how long they've been a client
- If new business: welcome them, don't oversell
- Never use: "I hope this finds you well," "don't hesitate to reach out,"
  "please find attached," "in today's ever-changing landscape"
- Sign off as the producer by first name only

EXAMPLE INPUT:
"Commercial BOP renewal for Parkview Construction LLC. 6-year client.
Current with Hartford at $8,412. Requoted — Hartford renewal is $8,847
(+5.2%). Got Erie quote at $7,943. Recommend staying with Hartford because
their contractor endorsement is broader, net difference only $469 after
the endorsement value."

EXAMPLE OUTPUT:
"Dan — here's the renewal package for Parkview's BOP. Hartford came in at
$8,847, which is a 5.2% bump from last year. I did get Erie to $7,943, but
here's why I'm recommending you stay with Hartford: their contractor
liability endorsement covers completed operations claims that Erie excludes,
and on a construction account your size, that endorsement alone is worth
more than the $469 difference. I also want to talk about cyber liability
at renewal — you're processing client payment info on three job sites now
and your BOP doesn't cover a data breach. Call me when you've got 15
minutes and we'll button this up."Copy

Example: Real-World Input → Output

The Commercial Lines CSRs generate 34 Certificates of Insurance per week on average (range: 22–48, peaking during construction season in March). Each COI requires: open Applied Epic → find policy → create certificate → enter certificate holder info → generate PDF → draft email → attach → send. Average: 11.4 minutes per COI.

67% of COIs are for recurring certificate holders. The Commercial CSR re-types the same holder information every time — same general contractor address, same additional insured wording, same coverage verification. Saved templates would reduce these to a 2-click operation.

The Fix: Applied Epic Certificate Templates + Client Self-Serve

The Marketing/Admin Assistant manages Mailchimp campaigns, Canva social graphics, and birthday card mailing. In 90 days, she sent 6 newsletter campaigns and 187 birthday emails. Every message goes to the same undifferentiated list of 2,341 contacts.

This is not a marketing problem — it’s a data access problem. The Admin has no way to know which clients are up for renewal, which ones lapsed, or which ones only have one policy. Applied Epic has all of this data. Mailchimp can’t see it because nobody built the bridge.

The Fix: Mailchimp ↔ Applied Epic Sync

Here’s the complete sync script. It reads a weekly Epic CSV export from Google Drive, updates Mailchimp contacts with insurance-specific merge fields, and creates auto-segments. Uses the Mailchimp Marketing API — no coding knowledge needed beyond pasting this script.

/**
 * Mailchimp Audience Sync from Applied Epic Export
 * Runs weekly via Apps Script trigger
 *
 * Creates/updates these Mailchimp segments automatically:
 * - Renewal 60 Days: policies expiring within 60 days
 * - Lapsed 90 Days: cancelled/non-renewed in last 90 days
 * - No Review 18+ Mo: no recorded activity in 18+ months
 * - Monoline Auto: auto insurance only (cross-sell target for home)
 * - Monoline Home: home insurance only (cross-sell target for auto)
 * - New Client 90 Days: onboarded in last 90 days
 * - High Value: total premium $5,000+
 * - Commercial: any commercial lines
 *
 * Each segment gets its own automated email campaign in Mailchimp.
 */

const MC_CONFIG = {
  apiKey: 'YOUR_MAILCHIMP_API_KEY',      // e.g., 'abc123-us21'
  serverPrefix: 'us21',                   // the part after the dash
  listId: 'YOUR_LIST_ID',                 // 10-char audience ID
  epicExportFolderId: 'YOUR_FOLDER_ID'    // same Drive folder as commission script
};

function syncEpicToMailchimp() {
  const clients = loadEpicClientExport_();
  if (clients.length === 0) {
    Logger.log('No client data found. Check Epic export folder.');
    return;
  }

  const today = new Date();
  let updated = 0, created = 0, errors = 0;

  clients.forEach(client => {
    if (!client.email || !client.email.includes('@')) return;

    // Build merge fields for Mailchimp
    const mergeFields = {
      FNAME: client.firstName,
      LNAME: client.lastName,
      PHONE: client.phone,
      LINES: client.linesOfBusiness,
      PREMIUM: client.totalPremium.toString(),
      EXPIRY: client.nextExpiry || '',
      TENURE: client.tenureYears.toString(),
      LASTACT: client.lastActivity || '',
      STATUS: client.status
    };

    // Determine tags based on client data
    const tags = [];
    const daysToExpiry = client.nextExpiry
      ? Math.floor((new Date(client.nextExpiry) - today) / 86400000)
      : 999;
    const daysSinceActivity = client.lastActivity
      ? Math.floor((today - new Date(client.lastActivity)) / 86400000)
      : 999;

    if (daysToExpiry <= 60) tags.push('Renewal 60 Days');
    if (client.status === 'lapsed') tags.push('Lapsed 90 Days');
    if (daysSinceActivity >= 540) tags.push('No Review 18+ Mo');
    if (client.lineCount === 1 && client.linesOfBusiness.includes('Auto'))
      tags.push('Monoline Auto');
    if (client.lineCount === 1 && client.linesOfBusiness.includes('Home'))
      tags.push('Monoline Home');
    if (client.tenureDays <= 90) tags.push('New Client 90 Days');
    if (client.totalPremium >= 5000) tags.push('High Value');
    if (['BOP','GL','WC','Commercial Auto'].some(l =>
      client.linesOfBusiness.includes(l))) tags.push('Commercial');

    // Upsert to Mailchimp
    const subscriberHash = Utilities.computeDigest(
      Utilities.DigestAlgorithm.MD5,
      client.email.toLowerCase().trim()
    ).map(b => (b + 256) % 256).map(b =>
      ('0' + b.toString(16)).slice(-2)).join('');

    const payload = {
      email_address: client.email.toLowerCase().trim(),
      status_if_new: 'subscribed',
      merge_fields: mergeFields,
      tags: tags
    };

    try {
      mcApiCall_('PUT',
        '/lists/' + MC_CONFIG.listId + '/members/' + subscriberHash,
        payload);
      updated++;
    } catch (e) {
      Logger.log('Error updating ' + client.email + ': ' + e.message);
      errors++;
    }
  });

  Logger.log('Sync complete: ' + updated + ' updated, ' + errors + ' errors');
}

function mcApiCall_(method, endpoint, payload) {
  const url = 'https://' + MC_CONFIG.serverPrefix
    + '.api.mailchimp.com/3.0' + endpoint;
  const options = {
    method: method,
    contentType: 'application/json',
    headers: {
      'Authorization': 'Basic ' + Utilities.base64Encode(
        'anystring:' + MC_CONFIG.apiKey)
    },
    muteHttpExceptions: true
  };
  if (payload) options.payload = JSON.stringify(payload);

  const response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() >= 400) {
    throw new Error('Mailchimp API error: ' + response.getContentText());
  }
  return JSON.parse(response.getContentText());
}

function loadEpicClientExport_() {
  const folder = DriveApp.getFolderById(MC_CONFIG.epicExportFolderId);
  const files = folder.getFilesByType(MimeType.CSV);
  const clients = [];

  // Get the most recent file
  let latestFile = null;
  let latestDate = new Date(0);
  while (files.hasNext()) {
    const f = files.next();
    if (f.getDateCreated() > latestDate) {
      latestDate = f.getDateCreated();
      latestFile = f;
    }
  }
  if (!latestFile) return clients;

  const csv = Utilities.parseCsv(latestFile.getBlob().getDataAsString());
  const today = new Date();

  csv.slice(1).forEach(row => {
    if (row.length < 8) return;
    const effectiveDate = new Date(row[7] || today);
    clients.push({
      firstName: (row[0] || '').split(',')[1]?.trim() || row[0],
      lastName: (row[0] || '').split(',')[0]?.trim() || '',
      email: (row[1] || '').trim(),
      phone: (row[2] || '').trim(),
      linesOfBusiness: (row[3] || ''),
      lineCount: (row[3] || '').split(',').length,
      totalPremium: parseFloat(row[4]) || 0,
      nextExpiry: row[5] || '',
      lastActivity: row[6] || '',
      status: (row[8] || 'active').toLowerCase(),
      tenureYears: ((today - effectiveDate) / 31536000000).toFixed(1),
      tenureDays: Math.floor((today - effectiveDate) / 86400000)
    });
  });
  return clients;
}Copy

Email Templates per Segment (set up in Mailchimp as automations)

Expected open rate improvement: Generic newsletters: 18.4%. Renewal-triggered emails: 28–34%. Monoline cross-sell: 24–29%. Lapsed outreach: 31–38% (people open these out of curiosity). The birthday cards are great — add a “your policy renews in [X] days” line when applicable and they become the highest-converting email in your stack.

We analyzed the coverage data visible in Applied Epic sessions across the discovery period. Based on observed policy types, client counts, and industry benchmarks for independent P&C agencies, here’s what your book is leaving on the table:

Cross-Sell Mining SQL Query (Applied Epic data export)

-- Cross-Sell Mining Query for Applied Epic Data Export
-- Run against your Epic client/policy export CSV
-- Identifies monoline clients with highest cross-sell potential

-- AUTO-ONLY HOUSEHOLDS (no home policy)
SELECT
  c.client_id,
  c.client_name,
  c.phone,
  c.email,
  p.written_premium AS auto_premium,
  p.effective_date,
  DATEDIFF(CURDATE(), MAX(a.activity_date)) AS days_since_last_contact
FROM clients c
JOIN policies p ON c.client_id = p.client_id
  AND p.line_of_business = 'Personal Auto'
  AND p.status = 'Active'
LEFT JOIN policies ph ON c.client_id = ph.client_id
  AND ph.line_of_business = 'Homeowners'
  AND ph.status = 'Active'
LEFT JOIN activities a ON c.client_id = a.client_id
WHERE ph.policy_id IS NULL  -- no active home policy
GROUP BY c.client_id
ORDER BY p.written_premium DESC;

-- COMMERCIAL WITHOUT CYBER LIABILITY
SELECT
  c.client_id,
  c.client_name,
  c.phone,
  c.email,
  GROUP_CONCAT(DISTINCT p.line_of_business) AS current_lines,
  SUM(p.written_premium) AS total_premium,
  COUNT(DISTINCT p.policy_id) AS policy_count
FROM clients c
JOIN policies p ON c.client_id = p.client_id
  AND p.status = 'Active'
  AND p.line_of_business IN ('BOP', 'General Liability', 'Workers Comp', 'Commercial Auto')
LEFT JOIN policies pc ON c.client_id = pc.client_id
  AND pc.line_of_business = 'Cyber Liability'
  AND pc.status = 'Active'
WHERE pc.policy_id IS NULL
GROUP BY c.client_id
HAVING total_premium >= 3000  -- focus on accounts worth the effort
ORDER BY total_premium DESC;

-- PRIORITIZED OUTREACH LIST (weighted scoring)
SELECT
  c.client_id,
  c.client_name,
  c.phone,
  c.email,
  COUNT(DISTINCT p.line_of_business) AS current_lines,
  SUM(p.written_premium) AS total_premium,
  DATEDIFF(CURDATE(), MIN(p.effective_date)) / 365.0 AS years_as_client,
  -- Higher score = higher priority
  (SUM(p.written_premium) / 1000)
  + (DATEDIFF(CURDATE(), MIN(p.effective_date)) / 365.0 * 2)
  - (COUNT(DISTINCT p.line_of_business) * 5)
  AS cross_sell_score
FROM clients c
JOIN policies p ON c.client_id = p.client_id
  AND p.status = 'Active'
GROUP BY c.client_id
HAVING current_lines <= 2  -- monoline or two-line clients
ORDER BY cross_sell_score DESC
LIMIT 50;Copy

Nobody is working this list because nobody has time to build it. The Personal Lines CSRs are buried in carrier portals. The Office Manager is maintaining a spreadsheet. The producers are waiting on proposals. Every finding above this one, when fixed, creates the capacity to work the cross-sell pipeline.

How to Launch the Cross-Sell Campaign This Week

Outreach Scripts (copy and use)

PHONE SCRIPT: AUTO-ONLY → ADD HOME
"Hi [NAME], this is [CSR] from Lakeview Insurance. I was
reviewing your account and noticed we have your auto coverage
but not your homeowners. A lot of our clients don't realize
that bundling auto and home saves $400-800 per year — and
you'd get a single point of contact for everything.

Would you be open to a quick home quote? I just need about
5 minutes and your property address. If the numbers don't
work, no pressure at all."

---

PHONE SCRIPT: NO UMBRELLA
"Hi [NAME], this is [CSR] from Lakeview. Quick call — I was
reviewing your account and wanted to mention umbrella
coverage. You've got [X] in assets between your home and
vehicles, and your current liability limits cap at [X].
An umbrella policy adds $1 million in protection for about
$200-300 per year. It's genuinely the best value in
insurance. Can I add a quote to your next renewal?"

---

PHONE SCRIPT: COMMERCIAL NO CYBER
"Hi [NAME], this is [CSR] from Lakeview. I wanted to flag
something on your business coverage — your BOP doesn't
include cyber liability, and with [REASON: employee PII /
client payment data / online presence], you've got real
exposure there. A standalone cyber policy runs about
$1,200/year and covers breach notification, legal defense,
and business interruption from a cyber event.

Can I send you a quote to look at alongside your renewal?"

---

EMAIL TEMPLATE: MONOLINE CROSS-SELL
Subject: Quick question about your coverage, [FNAME]

Hi [FNAME],

I was reviewing your account and noticed you have [CURRENT LINE]
with us but not [MISSING LINE]. Most of our clients who bundle
save $[ESTIMATED SAVINGS] per year.

Want me to run a quick [MISSING LINE] quote? Takes about 5
minutes on our end and there's zero obligation.

Just reply "yes" and I'll have numbers to you by end of day.

[AGENT NAME]
Lakeview Insurance Group
[PHONE]Copy

The Business Case

Applied Epic is the most used application across all 11 team members — 9,847 sessions in 90 days, averaging 38 sessions per person per day. But usage is almost entirely read and manual data entry. The automation, workflow, and integration features are dormant.

This is not a training problem. Your team knows how to use Epic for what they need. The features they’re missing were never configured by whoever set up the system. Your team adapted by building workarounds. Those workarounds now cost 100+ hours/month across the agency.

The Fix: 8-Hour Epic Configuration Sprint

Print this checklist. Block 8 hours on a Tuesday. Work through it with your Epic admin login. Every item below is a built-in feature included in your existing Applied Epic license. Nothing here requires custom development, third-party tools, or Applied Systems professional services.

Total configuration time: 8 hours. Every feature listed above is built into Applied Epic and included in your existing license. It was never turned on. After this sprint, your team stops being the integration layer between systems that should be talking to each other.

From Applied Epic activity records, we identified 214 client households with no recorded interaction in 24+ months. No policy review, no phone call, no email, no claim — nothing. These clients are paying their premiums on autopilot. They’re also the most likely to leave when a competitor offers a lower rate, because they have no relationship reinforcement.

Built Tool: Client Review Scheduler + Outreach Templates

This script generates a prioritized weekly outreach list and auto-drafts the emails. Set it up the same way as the commission script: Extensions → Apps Script → paste → run. Trigger weekly on Monday mornings.

/**
 * Client Review Scheduler — Lakeview Insurance Group
 *
 * Reads Epic client export, identifies accounts with no activity
 * in 18+ months, generates a prioritized outreach schedule,
 * and creates Gmail draft emails ready to review and send.
 *
 * Priority scoring (higher = contact first):
 * - Inactivity length: +1 point per month since last contact (max 36)
 * - Premium value: +1 point per $500 in annual premium
 * - Monoline risk: +10 points if client has only 1 policy line
 * - Tenure bonus: +1 point per year as client (max 15)
 *
 * SETUP: Same as commission script
 * 1. Open Google Sheets → Extensions → Apps Script
 * 2. Paste this script
 * 3. Update epicExportFolderId with your Drive folder ID
 * 4. Run → Authorize
 * 5. Trigger: weekly, Monday, 7 AM
 */

const REVIEW_CONFIG = {
  epicExportFolderId: 'YOUR_FOLDER_ID',
  inactivityThreshold: 540, // 18 months in days
  outreachPerCSRPerWeek: 10,
  csrEmails: {
    'CSR 1': '[email protected]',
    'CSR 2': '[email protected]',
    'Comm CSR 1': '[email protected]',
    'Comm CSR 2': '[email protected]'
  },
  agencyPhone: '(847) 555-0147'
};

function generateReviewSchedule() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('Review Schedule');
  if (!sheet) sheet = ss.insertSheet('Review Schedule');
  sheet.clear();

  // Headers
  const headers = [
    'Priority', 'Client Name', 'Phone', 'Email', 'Lines',
    'Premium', 'Years as Client', 'Days Since Contact',
    'Suggested Action', 'Talking Points', 'Assigned To',
    'Status', 'Date Contacted', 'Notes'
  ];
  sheet.appendRow(headers);
  sheet.getRange(1, 1, 1, headers.length)
    .setFontWeight('bold').setBackground('#f5f2ec').setFontSize(10);
  sheet.setFrozenRows(1);

  // Load and filter
  const clients = loadClientExport_(ss);
  const today = new Date();

  const inactive = clients.filter(c => {
    const daysSince = c.lastActivity
      ? Math.floor((today - new Date(c.lastActivity)) / 86400000)
      : 999;
    return daysSince >= REVIEW_CONFIG.inactivityThreshold;
  });

  // Score and sort
  const scored = inactive.map(c => {
    const daysSince = c.lastActivity
      ? Math.floor((today - new Date(c.lastActivity)) / 86400000)
      : 999;
    const tenureYears = c.originalEffective
      ? ((today - new Date(c.originalEffective)) / 31536000000).toFixed(1)
      : 0;

    let priority = 0;
    priority += Math.min(daysSince / 30, 36);
    priority += c.totalPremium / 500;
    priority += c.lineCount === 1 ? 10 : 0;
    priority += Math.min(parseFloat(tenureYears), 15);
    priority = Math.round(priority * 10) / 10;

    const action = daysSince > 730
      ? 'Phone call (2+ yrs inactive)'
      : 'Email first, then call';

    const points = [];
    if (c.lineCount === 1) points.push('Monoline — discuss bundling');
    if (c.totalPremium > 3000) points.push('High-value — umbrella review');
    if (parseFloat(tenureYears) > 5) points.push(tenureYears + '-yr client — loyalty ack');
    points.push('Coverage adequacy: limits may need updating');

    return {
      priority, name: c.name, phone: c.phone, email: c.email,
      lines: c.linesOfBusiness, premium: c.totalPremium,
      tenure: tenureYears, daysSince, action,
      talkingPoints: points.join(' | '),
      assignedTo: c.linesOfBusiness.includes('Commercial')
        ? 'Comm CSR 1' : 'CSR 1'
    };
  });

  scored.sort((a, b) => b.priority - a.priority);

  // Write to sheet
  scored.forEach(c => {
    sheet.appendRow([
      c.priority, c.name, c.phone, c.email, c.lines,
      c.premium, c.tenure, c.daysSince, c.action,
      c.talkingPoints, c.assignedTo, 'Not Started', '', ''
    ]);
  });

  // Color-code by urgency
  for (let i = 2; i <= Math.min(scored.length + 1, sheet.getLastRow()); i++) {
    const days = sheet.getRange(i, 8).getValue();
    if (days > 730) {
      sheet.getRange(i, 1, 1, headers.length).setBackground('#fce4ec');
    } else if (days > 540) {
      sheet.getRange(i, 1, 1, headers.length).setBackground('#fff8e1');
    }
  }

  // Auto-size
  for (let i = 1; i <= headers.length; i++) sheet.autoResizeColumn(i);

  // Create email drafts for top 20
  const emailClients = scored.filter(c => c.action.includes('Email')).slice(0, 20);
  emailClients.forEach(c => {
    const subject = 'Quick coverage check-in, ' + c.name.split(',')[1]?.trim();
    const body = generateReviewEmail_(c);
    GmailApp.createDraft(c.email, subject, body);
  });

  Logger.log('Schedule generated: ' + scored.length + ' inactive clients, '
    + emailClients.length + ' email drafts created');
}

function generateReviewEmail_(client) {
  const firstName = (client.name.split(',')[1] || client.name).trim();
  return 'Hi ' + firstName + ',\n\n'
    + 'This is [YOUR NAME] from Lakeview Insurance. I was reviewing '
    + 'our client accounts and realized it\'s been a while since we '
    + 'last talked about your ' + client.lines + ' coverage.\n\n'
    + 'A lot can change — new vehicles, home improvements, life events '
    + '— and I want to make sure your coverage still fits. Would you be '
    + 'open to a quick 15-minute review call? No sales pitch, just '
    + 'making sure you\'re protected.\n\n'
    + 'Reply to this email or call me at ' + REVIEW_CONFIG.agencyPhone
    + ' — whatever\'s easier.\n\n'
    + 'Best,\n[YOUR NAME]\nLakeview Insurance Group';
}

function loadClientExport_(ss) {
  const folder = DriveApp.getFolderById(REVIEW_CONFIG.epicExportFolderId);
  const files = folder.getFilesByType(MimeType.CSV);
  const clients = [];

  let latestFile = null, latestDate = new Date(0);
  while (files.hasNext()) {
    const f = files.next();
    if (f.getDateCreated() > latestDate) {
      latestDate = f.getDateCreated();
      latestFile = f;
    }
  }
  if (!latestFile) return clients;

  const csv = Utilities.parseCsv(latestFile.getBlob().getDataAsString());
  csv.slice(1).forEach(row => {
    if (row.length < 8) return;
    clients.push({
      name: row[0] || '', email: row[1] || '', phone: row[2] || '',
      linesOfBusiness: row[3] || '', lineCount: (row[3] || '').split(',').length,
      totalPremium: parseFloat(row[4]) || 0, nextExpiry: row[5] || '',
      lastActivity: row[6] || '', originalEffective: row[7] || ''
    });
  });
  return clients;
}Copy

Phone Script for 2+ Year Inactive Clients

"Hi [FIRST NAME], this is [YOUR NAME] from Lakeview Insurance.
I'm calling because I was reviewing our client accounts and
realized we haven't connected in over two years. I want to
make sure your [LINES] coverage is still the right fit.

A few things I'd like to check:
- Have you added any vehicles or made home improvements?
- Are your liability limits still appropriate for your assets?
- [IF MONOLINE]: Have you considered bundling to save on premiums?

Do you have 10 minutes now, or should I call back at a better time?"Copy

The two producers logged 387 hours of desk time over 90 days (combined). That’s about 2.1 hours/day each — expected for producers who are primarily in the field. But the composition of that desk time is revealing:

38% of producer desk time is admin work — building prospect spreadsheets, pulling and formatting loss runs, scheduling appointments. This is exactly what Producer Support exists to do. The bottleneck: Producer Support currently spends 19 hours/month on proposal assembly (Finding #5). Fix proposal assembly, and Producer Support absorbs the producer admin work.

The Fix: Producer Admin Delegation + Loss Run Template

Step 1: Free up Producer Support time (depends on Finding #5). Once proposal assembly drops from 49 minutes to 8 minutes, Producer Support recovers 17 hours/month. That time gets redirected to absorbing the admin work producers are currently doing themselves.

Step 2: Establish the delegation handoff. Each producer sends a daily “request batch” email to Producer Support by 9 AM with everything they need: loss runs, prospect research, appointment scheduling. Producer Support batches and processes before noon. No more producers pulling their own loss runs one at a time in Acrobat.

Loss Run Request Template (standardized for all 5 carriers)

Subject: Loss Run Request — [INSURED NAME] — [CARRIER]

To: [CARRIER LOSS RUN EMAIL]

[CARRIER]-specific header:
- Travelers: [email protected]
- Hartford: [email protected]
- Progressive: [email protected]
- Nationwide: [email protected]
- Erie: [email protected]

Dear Loss Run Department,

Please provide a 5-year loss run for the following account:

Named Insured: [INSURED NAME]
Policy Number: [POLICY NUMBER] (if known, otherwise "New Business")
Effective Date: [CURRENT EFF DATE or "New Business"]
Lines of Business: [AUTO / HOME / BOP / GL / WC / ALL]

Please send to: [YOUR AGENCY EMAIL]
Agency Name: Lakeview Insurance Group
Agency Code: [YOUR AGENCY CODE WITH THIS CARRIER]

Thank you,
[PRODUCER SUPPORT NAME]
Lakeview Insurance Group
[PHONE]Copy

Step 3: Set up Epic pipeline dashboard for producers. In Applied Epic → Utilities → User Preferences → Home Screen Layout. For each producer, add the “Pipeline” widget showing: opportunities by stage (prospect / quoted / proposed / pending), total premium in pipeline, next actions due. Producers see their pipeline at a glance without building spreadsheets.

This isn’t a performance issue — it’s a structural one. Friday is when people take PTO, schedule appointments, or work half-days. The problem is that nobody adjusted coverage to account for the pattern. Clients don’t stop calling on Fridays.

Three Options (Your Decision — All Ready to Implement)

The data shows the gap. Pick the option that fits your culture. All three are ready to implement this week.

Applied Systems is building its own AI layer (Applied AI), but it’s not broadly available yet. In the meantime, here’s what a custom AI assistant connected to your Epic data would do — and what it would replace.

What the AI Assistant Would Handle

• “Pull up everything on the Johnson account” — Returns: all active policies, last 5 activities, upcoming renewal dates, claims history, commission data, coverage gaps. Currently requires 4–6 clicks across multiple Epic screens.
• “Who needs a renewal call this week?” — Returns: policies expiring in 30 days, sorted by premium size, with CSR assignment and last contact date. Currently requires the Office Manager’s spreadsheet.
• “Generate a COI for ABC Construction on the Smith policy” — Creates the certificate, populates the saved holder template, generates PDF, drafts the email. Currently takes 11.4 minutes.
• “What’s the status on the Henderson claim?” — Returns: carrier, claim number, current status, last update, adjuster info, next expected milestone. Currently requires a portal login.
• “Show me all monoline clients with premium over $2,000” — Returns: cross-sell target list with coverage gaps, recommended lines, and talking points. Currently has never been run.
• “Draft a proposal for the new commercial account at 123 Main St” — Pulls data from Epic, generates proposal with merge fields, writes cover letter, creates PDF. Currently takes 49 minutes.

Technical Architecture

Applied Epic AI Assistant Architecture
--------------------------------------

[User Query]
    |
    v
[AI Assistant (Claude/GPT)]
    |
    v
[Applied Epic MCP Server]
    |--- read_client(name) → client record, policies, activities
    |--- search_policies(filters) → matching policies
    |--- get_renewals(date_range) → upcoming renewals with CSR assignments
    |--- generate_certificate(policy_id, holder_id) → COI PDF
    |--- get_claim_status(claim_number) → current status, timeline
    |--- run_cross_sell_report(filters) → monoline clients, coverage gaps
    |--- create_proposal(client_id, options) → branded PDF
    |--- create_activity(client_id, type, notes) → activity note in Epic
    |
    v
[Applied Epic REST API / IVANS]
    |--- Client Management endpoints
    |--- Policy Management endpoints
    |--- Certificate Management endpoints
    |--- Claims endpoints
    |--- Activity/Workflow endpoints
    |--- Commission endpoints
    |--- Reporting endpoints

Current API status:
- Applied Epic SDK: Available to Applied partners (requires partnership agreement)
- IVANS connectivity: Available now for download/upload
- REST API: Limited availability, expanding in 2026

Model recommendations by task (with monthly cost at Lakeview's volume):
- Proposal cover letters: Claude Opus ($0.52/mo @ 23 proposals)
    → Tone matters. Opus writes the most natural insurance language.
- Claims status parsing: Gemini Flash ($0.48/mo @ 14 checks/day)
    → Speed matters, complexity is low. Flash at $0.075/1M tokens.
- Cross-sell prioritization: Claude Sonnet ($0.85/mo @ 189 accounts)
    → Reasoning + speed balance. Sonnet at $3/1M input tokens.
- Marketing segmentation: Gemini Flash ($0.32/mo @ 2,341 contacts)
    → Batch classification task. Flash is the right price point.
- Retention scoring: Claude Sonnet ($0.67/mo @ 214 accounts)
    → Nuanced priority scoring. Sonnet handles the logic well.
- Total AI compute: $2.84/mo for current volume
    (The $175-280/mo estimate in the exec summary includes headroom
    for scaling to full Applied Epic API integration when available)
- Applied AI: In development, early access program

Recommended path:
1. Implement IVANS-based automations NOW (claims, commissions, downloads)
2. Build Google Apps Script bridges for data that can be exported as CSV
3. Apply for Applied Epic API access when broadly available
4. Build full AI assistant when API access is grantedCopy

API & Integration References