Join policy, invoice and commission rows without duplication

One policy can have two invoices and three commission entries. A direct policy-number join produces six combined rows, repeating invoice values three times and commission values twice. The resulting table looks complete but overstates both measures. This is a grain problem rather than a rounding problem.

Define the data before the metric

One row represents: one source transaction, with summaries at a common policy-term or reconciled transaction level.

Useful fields: Policy term ID, invoice ID, invoice line ID, commission statement line ID, source transaction reference, signed amounts, transaction type, currency and allocation share.

Keep invoice and commission facts in separate ledgers. If the question is term-level income, summarize each ledger by term and then join the summaries. If the question is statement reconciliation, use source transaction references and retain unmatched items. Use an allocation only when its basis is documented and approved; do not invent links from similar amounts.

Worked example

The following records and amounts are invented to show the method. They are not customer results, industry benchmarks or a forecast of Covirage performance.

The term-level view should still contain $10,000 premium and $1,500 commission. The raw join would show $30,000 premium and $3,000 commission if each repeated amount were summed. Aggregate-first gives one term row without losing the ability to open the original source entries.

Use the result in a review

1. Choose the policy-term summary for book reporting and the statement-line view for reconciliation, keeping their purposes distinct.
2. Have finance review allocations that span several invoices, especially when returns or partial receipts are involved.
3. Retain unmatched entries as an exception list with their value rather than dropping them to obtain a neat table.

Checks before publishing

• Compare each source's amount total before and after enrichment or joining; the difference must be explained.
• Assert one row per key in every summary table used in a one-to-one join.
• Check that allocation shares sum to 100% and do not apply the full receipt to more than one invoice.

Where this analysis can mislead

A policy-term summary can hide transaction-level timing differences. It is appropriate for a book overview but insufficient to prove that individual carrier receipts or accounting balances have cleared.

Explore this question with your own data

Bring a small, authorized sample to Covirage for insurance agencies and brokers. Use the sample to discuss the fields and views your business needs. A dashboard or AI analyst can help explore this question when the required data and definitions are available; missing records still need to be resolved.

Upload sample data to check its structure. Keep unnecessary personal, claims and policyholder details out of an initial sample. The sample check does not establish that every analysis in this guide is available automatically.

Related guides

• Prepare an insurance agency sample without unnecessary identifiers
• Insurance agency export checklist: the files to prepare
• Commercial insurance broker KPIs

Reference context

These references provide terminology or governance background. The worked example and proposed review method above are original illustrations, not prescribed industry standards.

• Insurance terminology
• Insurance data context