Insurance KPI & Commission Control Layer
A normalized Airtable operating database for clients, payments, agents, commissions and executive KPI reporting.
The goal was to replace spreadsheet-shaped reporting with a relational operating model that could preserve source detail while supporting reliable rollups and management views.
The data model separated clients, payments, KPI periods and agents so one deal could support one or multiple commission splits without duplicating the underlying transaction.
Approved financial logic was implemented directly in the model: Net equals Gross minus carrier fee and taxes; Agent Commission equals Net multiplied by Agent Percentage; House Commission equals Net minus Agent Commission.
Interfaces surfaced multi-year client counts, payment activity, agent performance and KPI summaries without forcing users to rebuild calculations in every view.
Operational and commission data was distributed across grouped spreadsheets, multiple years and legacy structures that were difficult to import, reconcile and compare.
The project normalized clients, payments, KPI records and agents into linked Airtable tables, mapped legacy Kintone and Excel data, and implemented approved commission formulas and management interfaces.
Outcomes without invented claims
- 01Created a shared relational structure for client, payment, KPI and agent records.
- 02Preserved approved commission calculations in transparent formula fields.
- 03Supported dynamic one-to-many commission splits per deal.
- 04Enabled executive summaries and charts without relisting or duplicating source records.
What makes this publishable
- Approved data model
- Commission formula specification
- Airtable interface structure
- Migration mapping from Kintone and Excel
The project established one operating language for payments, commissions and performance while keeping every management measure traceable to the underlying records.
