Automating Many-to-Many Insurance Agent Hierarchies via Custom NPN Objects and Python Cleanup Loops
A health-insurance web broker manages 16M+ enrollees. Many-to-many agent and agency relationships kept overwriting each other in the CRM. A Custom NPN Object architecture in HubSpot fixed that. Python cleanup loops in Operations Hub resolve the API race conditions. They also lock in accurate multi-tiered commission reporting.
Executive Summary
Context
A healthcare.gov certified web broker needed to migrate from ActiveCampaign to HubSpot while integrating a custom ExchangeCompare API. They were managing over 85,000 legacy records and hundreds of carrier relationships, and they had to do it while preparing for the high-volume Open Enrollment Period (OEP).
What We Built
We built a Custom Object architecture centered on the National Producer Number (NPN). We deployed Python-based automation to manage many-to-many associations. A daily "cleanup loop" catches orphaned records caused by API sync delays and links them.
Tech Stack
- Marketing Hub Enterprise, Operations Hub Professional, Custom ExchangeCompare API, Mixpanel, and Python.
This isn’t a fit for 1:1 agent-agency relationships. It also isn’t a fit when you lack Operations Hub Professional to run the custom code blocks that association retries need.
The Challenge
Data volume wasn’t the deeper issue. Timing was. The ExchangeCompare integration pushed data in batches, so we frequently ran into race conditions. An Agent record would hit HubSpot, but the API wouldn’t create the associated Agency record for another 12 to 24 hours. In a standard setup, the association would just fail, and the agent would sit orphaned in the CRM.
Reporting added its own mess. When we tried native HubSpot roll-up properties to track total enrollments across a hierarchy, agent to parent agency to root agency, the system double-counted. It aggregated the direct agent’s enrollments and the agency’s roll-up total at the same time. When we compared those numbers to the firm’s internal Mixpanel dashboards, the HubSpot data looked like a hallucination.
Our Approach
We stopped relying on HubSpot’s native "Associate Objects" workflow actions for the initial sync. The data came in flattened: the agent record contained the IDs for their agencies, but the agencies themselves didn’t exist in the CRM yet. Instead of letting the sync fail, we built a secondary cleanup script in Operations Hub. That script, named "[S2] Reattempt Agent to Agency Association," was built to be patient. It queries every unassociated agent record at 3:00 AM daily. It checks whether the Agency record has finally arrived. If it has, it stitches them together. If not, it marks the record for a retry the next day.
We also solved the double-counting issue by dropping recursive roll-ups. We shifted the strategy to manual, filter-based list aggregations instead. Using the "Root ID" as the sole source of truth for the hierarchy’s top level stripped out the duplicate counts and finally got the CRM to match the internal database.
Impact
Relational Architecture Deployed
We mapped complex many-to-many agent/agency relationships using a Custom NPN Object. That fixed a critical database limitation. 30% of agents needed affiliation with multiple different agencies. The sync no longer overwrote any data.
Automated Multi-Tiered Downlines
We built a hierarchy system with Python scripts. It automatically maps direct agencies to their "Parent" and "Root" counterparts. That gives accurate commission and enrollment tracking across three distinct layers of the organization.
Dynamic Engagement Labeling
We built an Ops Hub scoring engine. It tracks agent activity and assigns "Most Engaged" association labels. That let the marketing team deduplicate outreach, so marketing messages went only to the most active record tied to a specific NPN.
14-Day Onboarding SLAs Met for Every Sign-Up
We launched an onboarding pipeline. It pushed agents from zero to their first enrollment. The build included automated round-robin assignment and workflow tracking of sales touchpoints. It kept 14-day SLAs on track for every new sign-up.
We bypassed HubSpot’s native roll-ups to avoid double-counting. We built custom reports that filter only on the "Root Agency ID." Enrollments now count once, at the top of the hierarchy.
A Custom Object architecture bridging complex many-to-many agent and agency relationships within HubSpot. It utilizes native Operations Hub Python logic to manage API sync delays and automated record associations. This intervention resolves standard CRM data overwrites and ensures accurate multi-tiered commission reporting for health insurance brokers.
FAQ
