Insurance Sales and Commission Reporting
Client:
Insurance broker consolidating policy sales and commission data from more than 30 insurance providers across approximately 100,000 active policies.
Challenge:
Sales and commission data arrived in different formats and required repeated manual preparation before reporting and commission reconciliation could begin. Analysts spent significant time collecting files, mapping insurer-specific structures, validating data, investigating discrepancies, and rebuilding reporting datasets.
Key Success Metrics:
- Reduced recurring manual reporting and reconciliation work from approximately 165 to 40 analyst hours per month, releasing more than 120 hours of analyst capacity each month.
- Automated repeatable commission calculation, validation, and reconciliation while surfacing exceptions for analyst review.
- Created one consistent management view of policy sales and commissions.
Business Challenge
Analyst time went into preparing and reconciling insurer files rather than into the volume of records itself, and management reporting had to be rebuilt each cycle.
Manual data preparation
Each insurance provider supplied sales and commission data using different file structures, field names, product definitions, reporting periods, and commission conventions. Analyst time was consumed primarily by preparing and reconciling these different sources rather than by the volume of records itself.
Commission reconciliation required manual investigation
Expected commissions had to be compared with information received from insurers. Differences in source structures and business rules meant analysts repeatedly investigated mismatches before the data could be accepted for reporting.
No consistent sales and commission view
Management reporting depended on manually prepared datasets. Policy sales and commission information had to be combined repeatedly before a reliable view of performance was available.
Architecture and Tech Stack
Insurer data was standardized on Snowflake, with commission logic in dbt and management reporting in Power BI.
Snowflake
dbt
Power BI
Implementation and Solution
The team first mapped the existing reporting process and identified where analyst time was being consumed. It then standardized insurer data, moved repeatable commission logic into the data platform, and created a consistent reporting model.
Source and process inventory
Mapped data feeds from [X] insurance providers and documented their structures, reporting cycles, key fields, commission rules, and recurring data-quality issues. The existing reporting workflow was also broken down to establish the manual effort required across preparation, validation, reconciliation, and reporting.
Central sales and commission data model
Standardized insurer-specific structures into a common model covering policies, products, sales, insurers, and commissions. Transformation logic that had previously been rebuilt manually became reusable across reporting cycles.
Automated validation and commission reconciliation
Moved repeatable validation and commission rules into dbt transformations. Expected commissions could be systematically compared with insurer-provided data, while discrepancies and exceptions were surfaced for analyst review.
Management reporting and team enablement
Connected Power BI reporting to the standardized data model, creating a consistent management view of policy sales and commissions. Worked with the client's reporting team to transfer the reporting logic and enable ongoing maintenance without rebuilding the underlying analysis manually.
Delivered by members of the Cadvel team prior to founding the company.
Results (ROI)
Repeatable preparation and reconciliation moved into the data platform, releasing analyst capacity and giving management one consistent view.
More than 120 analyst hours of capacity released each month
Recurring manual reporting and reconciliation work fell from approximately [X] to [X] analyst hours per month.
Repeatable commission calculation and reconciliation automated
Validation and commission rules run as dbt transformations, with discrepancies and exceptions surfaced for analyst review.
One consistent view of policy sales and commissions
Power BI reporting connects to the standardized data model, so policy sales and commissions no longer have to be combined manually each cycle.
Logistics & Transport
Sealand Liquids: ISO Tank Fleet Visibility and Utilization
Fleet utilization rose from 37% to 53% year on year and gross profit per tank-day by 21%, after tank status, lease costs and sales decisions were connected in one view.
Retail
Sales Data Reconciliation and Cloud Cost Assessment for an International Retail Group
$40,000 in potential monthly cloud savings identified in a six-week assessment, seven CRM systems reconciled with ERP and accounting, and key reports generated 40% faster.
Finance
Secure Data Sharing for an International Payments Technology Company
More than 4 TB shared across three core tables in one business day, with no duplicate storage and no separate data-transfer pipeline.
Manufacturing
Migrating a Manufacturing and Retail Data Warehouse from Oracle to ClickHouse
Query performance and out-of-memory failures resolved on 20 years of sales data, with stable ETL across datasets of more than 300 million rows.
Finance
Real-Time Stock Market Analytics
A streaming pipeline on Apache Kafka and ClickHouse processing about 1.5 million records a day, with roughly one-second alert latency in the demonstration setup.
