Automating Many-to-Many Insurance Agent Hierarchies via Custom NPN Objects and Python Cleanup Loops
At a health-insurance web broker, many-to-many relationships between agents and agencies kept overwriting each other in the CRM. We built a Custom NPN Object architecture in HubSpot to hold those relationships. Python cleanup loops in Operations Hub resolve the API race conditions and keep multi-tiered commission reporting accurate.
Executive Summary
Context
A health-insurance 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), then deployed Python-based automation to manage the many-to-many associations it holds. A daily cleanup loop catches records orphaned by API sync delays and links them.
Tech Stack
- Marketing Hub Enterprise
- Operations Hub Professional
- Custom ExchangeCompare API
- Mixpanel
- 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
The ExchangeCompare integration pushed data in batches, and the ordering of those batches, more than their size, caused the race conditions we kept running into. An Agent record would reach HubSpot while its associated Agency record did not arrive for another 12 to 24 hours. In a standard setup that association simply fails, leaving the agent orphaned in the CRM.
Reporting had its own problem. When we tried HubSpot's native roll-up properties to total enrollments across the hierarchy, from agent to parent agency to root agency, the system double-counted. It aggregated a direct agent's enrollments and the agency's roll-up total at the same time. Set against the firm's internal Mixpanel dashboards, the HubSpot figures looked invented.
Our Approach
We stopped relying on HubSpot's native Associate Objects workflow actions for the initial sync. The data arrived flattened: each agent record held the IDs of its agencies while the agencies themselves did not yet exist in the CRM. Rather than let the association fail, we built a secondary cleanup script in Operations Hub. The script, a Reattempt Agent-to-Agency Association workflow, queries every unassociated agent record at 3:00 AM daily and checks whether the Agency record has arrived. If it has, the script stitches the two together; if not, it marks the record for another attempt the next day.
For the double-counting we dropped recursive roll-ups altogether and moved to manual, filter-based list aggregations. Using the Root ID as the single source of truth for the top of the hierarchy removed the duplicate counts. The CRM's totals now match the internal database.
This engagement is part of our systems integration work for finance & insurance organisations.
Impact
Relational Architecture Deployed
A Custom NPN Object now maps the many-to-many relationships between agents and agencies, a structure roughly 30% of agents needed since they work with more than one agency. The sync no longer overwrites any data.
Automated Multi-Tiered Downlines
Python scripts now map each direct agency to its Parent and Root counterparts automatically, giving accurate commission and enrollment tracking across all three layers of the hierarchy.
Dynamic Engagement Labeling
An Ops Hub scoring engine tracks agent activity and assigns a "Most Engaged" association label, which lets the marketing team deduplicate outreach so a message reaches only the most active record tied to a given NPN.
A 14-Day Onboarding Pipeline for New Agents
An onboarding pipeline now moves agents from zero to their first enrollment, with automated round-robin assignment and workflow tracking of sales touchpoints tracking every new sign-up against its 14-day SLA.
We bypassed HubSpot's native roll-ups to avoid double-counting and built custom reports that filter only on the Root Agency ID, so enrollments now count once, at the top of the hierarchy.
An Agent record used to reach HubSpot 12 to 24 hours before its Agency record, so the association failed and left the agent orphaned. Now the Reattempt Agent-to-Agency Association script runs at 3:00 AM daily. It links each unassociated agent if the Agency has arrived, and otherwise marks it for the next day. Hierarchy totals use the Root ID as the single source of truth, so the CRM totals now match the internal database.
FAQ
