Blog · How-to guides · Insurance
Insurance customer analytics compares the client relationship across policies, renewals and service effort. This workflow builds a client-level comparison from brokerage exports and checks that policy and revenue totals remain intact.
Insurance customer analytics compares a client's full relationship across policy holdings, renewal outcomes, brokerage revenue and service effort. Build one client summary from matched exports, while keeping the underlying policy counts available for checks.
This prevents a common reporting mistake: a client with several policies appears several times in a policy export, but remains one relationship. The client view should reveal that distinction rather than hide it behind an overall average.
Agree what counts as a client with the account-management team. For a commercial brokerage, that might be the business account that owns the relationship. Related legal entities can remain separate or roll into a parent relationship, but the rule must be explicit and consistent.
An insurance contract is a different unit. The Washington Office of the Insurance Commissioner defines an insurance policy as the entire written insurance contract. A policy identifier therefore cannot stand in for the brokerage's client identifier.
Use an approved mapping between policy and client identifiers. Investigate missing identifiers, reused account codes and policies assigned to multiple relationships before comparing results. Retain clients that close every policy so the opening population does not silently shrink.
The worked comparison below follows existing clients throughout one reporting quarter. New relationships opened during the quarter belong in a separate acquisition cohort. Added policies for an existing client still belong to that client's relationship.
Use opening and closing policy snapshots, a renewal-outcome summary, the brokerage revenue ledger and a service-time summary. Each needs the agreed client identifier and the relevant reporting dates. Define the renewal opportunity population separately from the complete policy population.
Collapse each export to one row per client before combining the summaries. Joining every policy row directly to every service entry repeats both policies and hours. Client-level totals prevent that multiplication, while the original exports remain available to prove the figures.
For holdings, preserve policy type as well as total count. For service effort, use logged hours for a defined activity scope, such as account administration and renewal support. Keep unassigned work visible rather than spreading it silently across clients.
Here, brokerage revenue means the commissions and fees recorded for these relationships during the quarter, after included adjustments. It is not the clients' total premium. Revenue and service hours cover the same reporting window, including work for a relationship that subsequently leaves.
A fictional US brokerage has these five opening commercial relationships. Closing holdings include policies added during the quarter. Renewal counts refer only to the agreed opportunities due within the reporting window.
| Client | Opening policies | Closing policies | Renewals due | Renewed | Brokerage revenue (USD) | Service hours |
|---|---|---|---|---|---|---|
| Alder Metals | 3 | 2 | 2 | 1 | 9,000 | 18 |
| Beacon Supply | 1 | 2 | 1 | 1 | 4,800 | 12 |
| Cedar Foods | 2 | 0 | 2 | 0 | 1,200 | 8 |
| Delta Fabrication | 4 | 4 | 2 | 2 | 16,000 | 20 |
| Evergreen Services | 1 | 1 | 0 | 0 | 3,000 | 10 |
| Total | 11 | 9 | 7 | 4 | 34,000 | 68 |
Put the input columns in A:G and the client rows on rows 2:6. Add an active-relationship flag in H, revenue per service hour in I and the supplied renewal outcome rate in J:
H2 =IF(C2>0,1,0)
I2 =IF(G2=0,"",F2/G2)
J2 =IF(D2=0,"",E2/D2)
Copy down. Evergreen has no renewal rate because none of its policies is due in the selected window. Its continuing relationship is still part of the client comparison. For replacement-policy treatment and the detailed distinction between denominators, use client versus policy retention.
To assemble revenue from a client-coded summary with identifiers in A and amounts in B:
=SUMIFS(Revenue!$B$2:$B$1000,Revenue!$A$2:$A$1000,A2)
Microsoft's SUMIFS documentation explains how the function adds values meeting specified criteria. Filter the source to the reporting window first, or add date criteria using the source's date column. Covirage is not affiliated with Microsoft.
The closing policy mix adds useful context to the relationship totals:
| Client | Property policies | Liability policies | Workers' compensation policies | Closing total |
|---|---|---|---|---|
| Alder Metals | 1 | 1 | 0 | 2 |
| Beacon Supply | 1 | 1 | 0 | 2 |
| Cedar Foods | 0 | 0 | 0 | 0 |
| Delta Fabrication | 1 | 2 | 1 | 4 |
| Evergreen Services | 0 | 1 | 0 | 1 |
| Total | 3 | 5 | 1 | 9 |
Each row must equal its closing-policy count in the first table. The mix totals also reconcile: 3 property policies plus 5 liability policies plus 1 workers' compensation policy equals 9.
Four of the five opening clients remain active, or 80.0%. Those active clients hold 9 policies, giving 2.25 policies per active client. The formulas are:
=SUM(H2:H6)/COUNTA(A2:A6)
=SUM(C2:C6)/SUM(H2:H6)
=SUM(F2:F6)/SUM(G2:G6)
The final formula gives $34,000 divided by 68, or $500.00 of brokerage revenue per recorded service hour. Alder produces $500.00 per hour, Beacon $400.00, Cedar $150.00, Delta $800.00 and Evergreen $300.00. These are workload comparisons, not fully allocated profit figures. Currency ratios are rounded to two decimals; rates to one decimal.
The client view changes the conversation. Beacon's policy count grows while Cedar's relationship closes entirely. Alder remains active despite an unsuccessful renewal outcome. Those are different account-management situations even when they appear together in a policy-level loss report.
Use coverage intelligence for the broader relationship framework. Policy holdings here describe recorded business; they do not establish whether a client needs additional insurance.
The opening and closing policy totals must tie to their source snapshots. The net policy change is 9 minus 11, or a decrease of 2. Investigate additions, closures and replacements in the policy-level movement report if that movement does not reconcile.
The revenue total must tie to the selected ledger accounts, and the hours to the service summary. Revenue per hour must be computed from those totals, not the average of client ratios. Renewal outcomes also reconcile to the separate source: 4 renewed opportunities out of 7 due is 57.1%, using unrounded inputs.
Account management owns the relationship review. Finance approves the revenue scope, and the service manager checks whether effort was recorded consistently. Customer care analytics can examine the service categories behind a high workload; it is a separate investigation from the relationship comparison.
Bring client-level policy, renewal, brokerage revenue and service summaries with consistent identifiers and dates. Covirage maps the columns, and its tools compute and check the relationship comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Covirage for insurance brokers for the supporting business context.
A client is the relationship your brokerage manages; a policy is an insurance contract associated with that relationship. One client can hold several policies. Count clients using an agreed relationship identifier and policies using distinct policy identifiers, keeping both denominators visible.
Use the measure that answers the question. Premium describes the insurance business placed, while this service-effort comparison uses the brokerage's recorded commissions and fees. Label each field explicitly and reconcile brokerage revenue to the ledger before drawing conclusions about account economics.
No. More policies can accompany more revenue, more service work or both. Compare revenue, policy mix and recorded effort together. Policy count alone does not establish profitability, and revenue per service hour does not include every cost of serving the relationship.
Keep them in the relationship and holdings analysis, but leave their renewal rate blank. A client without a policy due for renewal has no renewal opportunity in that period. Counting that client as a renewal success or failure changes the denominator incorrectly.