End-to-End Automation for a Leading US Insurance Brokerage

One of the largest US insurance brokerage firms ran its core operations — policy management, commissions tracking, lead generation, CRM upkeep — on spreadsheets and manual effort. We replaced that with a tightly integrated ecosystem: five interconnected automations plus self-service web applications that let employees run tasks like PDF generation, data validation, and lead updates on demand.

The problem

Every core workflow depended on manual steps. Billing reminders were tracked by hand, commissions reconciled across disconnected sheets, and leads sorted and assigned one at a time. Errors slipped through, turnaround times stretched, and each new agent or policy added more administrative load.

What we built

Five automations covering policy billing, commissions tracking, lead management, and CRM updates, plus custom web applications for employees and agents. Everything runs across the tools the brokerage already uses: Google Sheets, GoHighLevel (GHL), Google Drive, and OptSoft.

How it works

Policy management and billing

Agents enter new client information into a Google Sheet, which triggers automated data extraction. The system calculates billing reminders from each client's policy date and the firm's internal rules (such as a Wednesday cycle), sending alerts five days in advance. Each record flows into GoHighLevel, prompting targeted text messages and personalized handwritten card orders on predefined schedules. Errors and exceptions are logged automatically, so no client or billing event is missed.

Commissions tracking and reporting

The system consolidates records from three Google Sheets and cross-verifies each one against GHL in real time. Predefined rules tag policies by stage and commission eligibility; mismatches trigger instant alerts. Summaries of commissions owed, paid, and pending are generated automatically — no manual reconciliation.

CRM integration and document generation

New leads flow in from GHL, kept separate from agent-driven leads. The Melissa API verifies and standardizes each address. The system then generates a tailored PDF for each of five lead categories, uploads it to Google Drive, and appends the shareable link to the lead. Finalized leads transfer to OptSoft, while descriptive stats — conversion rates, source distributions — populate Google Sheets for stakeholders.

Lead retrieval and geographic processing

Inbound emails are parsed for new leads, extracting contact and geographic details in real time. Each lead is standardized, assigned to the correct territory or agent by predefined geographic boundaries, then routed to dedicated sheets automatically.

Indeed scraping and government portals

Targeted scraping pulls prospective insurance-sector leads from Indeed. The system logs into secure state government insurance portals, completes the required forms, and retrieves phone numbers for each lead. Numbers are cleaned, validated, and de-duplicated before landing in Google Sheets, with live dashboards showing scraping progress.

Self-service web apps

Alongside the scheduled automations, custom web applications give employees on-demand tools:

• PDF generation — agents create documents directly, without waiting for a scheduled run.
• Data validation — address and phone verification, triggered on the fly.
• Progress dashboards — lead assignments, billing reminders, and commission statuses at a glance.

These apps reduce dependence on IT queues, shorten turnaround times, and let teams solve problems without waiting on anyone.

Impact

• Automating repetitive tasks cut the staffing needed for data entry and manual checks.
• Automated validation and fail-safe logging brought missed leads and billing oversights near zero.
• The architecture absorbs growing lead and policy volumes without new headcount.
• Agents get instant tools; clients get timely billing alerts and personalized outreach.