Insurance marketing analytics: leads, quotes and policies

Insurance marketing analytics compares acquisition-source cohorts through quotes, bound policies and recorded agency income. Start with the leads created in one period, retain their source assignments, and follow every lead for the same length of time. Compare costs only after the CRM, policy records and campaign spending reconcile.

Fix the population and source rule

Use a stable opportunity ID representing one coverage need in one decision period. Do not use every carrier quote as a separate acquired lead. Agree the stages using the insurance quote-to-bind funnel method; this analysis adds acquisition source and spending to those established stages.

Retain the campaign or referral source recorded at acquisition. If a lead later clicks an email or meets another producer, keep that interaction separately rather than replacing the original source. Put unrecorded sources in an explicit Unknown group until the records are resolved.

The illustrative US brokerage below reviews new-business leads created in June 2026, using data through September 30, 2026. Outcomes count only when recorded within 90 days of the individual lead's creation. Every lead therefore has a complete observation window. Exclude renewals and cross-sell opportunities from this acquisition cohort, or give them separate groups.

Build one row per acquired lead

On a Leads sheet, use these columns:

Use genuine date cells. In G2 and H2, respectively, enter:

=IF(D2="",0,--AND(D2>=C2,D2<=C2+90))
=IF(E2="",0,--AND(E2>=C2,E2<=C2+90))

The flags count a lead once. In I2, restrict the income to binds inside the window:

=IF(H2=1,F2,0)

Fill down the June lead rows. Keep late outcomes visible in a separate exception list, but do not silently include them in this completed-window comparison. Investigate impossible date sequences and missing quote dates before interpreting a source's progression. Quote turnaround analysis covers the timing question in more detail.

Summarize by acquisition source

On a summary sheet, place a source name in A2. Count its lead rows using:

=COUNTIFS(Leads!$B$2:$B$9999,A2)

Google Sheets documents COUNTIFS as counting rows that meet specified criteria. Since the lead sheet is already restricted to the June cohort, this counts that source's eligible acquired population.

Sum the flags and eligible income with these formulas:

=SUMIFS(Leads!$G$2:$G$9999,Leads!$B$2:$B$9999,A2)
=SUMIFS(Leads!$H$2:$H$9999,Leads!$B$2:$B$9999,A2)
=SUMIFS(Leads!$I$2:$I$9999,Leads!$B$2:$B$9999,A2)

SUMIFS adds amounts matching the criteria, allowing the same source rule to control each total. Extend the ranges to include the full export. Covirage is not affiliated with Google.

Add campaign costs from a separate spending register. Include the same agreed cost categories for each source, such as media, campaign fees and allocated acquisition labor. Allocate shared costs once, using a documented basis.

A worked source comparison

This invented brokerage has no Unknown-source leads. Each bound lead produces one policy in this example; a report with multiple policies per lead needs an additional policy-count column. Agency income is the recorded commission and fee amount on eligible bound policies, not premium and not a statement of cash collected.

Calculate lead-to-bind conversion and acquisition cost per bound lead:

Lead-to-bind rate = Bound leads / Acquired leads

Acquisition cost per bound lead = Acquisition cost / Bound leads

Round rates to one decimal place and unit costs to cents after calculating. Total conversion is 55 / 240 = 22.9%, and cost per bound lead is 19,600 / 55 = $356.36. The $59,700 difference is 79,300 minus 19,600; it excludes servicing costs, overhead and later adjustments, so it is not operating profit.

Referral's lower unit cost warrants a review of referral capacity and client mix. It does not prove that moving the Search budget would produce the same outcome. Keep health-specific acquisition groups separate where needed; health insurance marketing analytics covers that adjacent question.

Reconcile the result and its limits

Prove that 100 + 60 + 80 = 240 leads, and 18 + 21 + 16 = 55 bound leads. Match those binds to placement records and reconcile $79,300 to the agency income register. Match $19,600 to the assigned spending rows. Resolve duplicate IDs before summing income: repeating one policy across several quote rows repeats its dollars too.

The marketing owner should own source coding and campaign costs; the placement team should confirm bind evidence. Finance should confirm the income basis and adjustments.

Where it goes wrong

• Recent leads against mature leads. Incomplete follow-up makes a new campaign appear weaker. Compare equal observation windows.
• Policies substituted for leads. Multiple placements from one lead inflate lead conversion if counted as separate acquired people or businesses.
• Premium mixed with agency income. Keep distinct amounts in distinct columns and use the same income basis across sources.
• Unresolved cases labeled losses. A lead with no bind by the cutoff is a window outcome; it may still bind later.
• Different risks mistaken for campaign lift. Line of business, state and coverage needs can change conversion without any campaign effect.

Do this on your own file in Covirage

Bring your CRM lead cohort, quote and bind records, campaign costs and agency income file to Covirage. Covirage maps the columns, its tools compute and check source comparisons, and 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 data; creating the account takes about a minute.

See Covirage for insurance brokers for the industry context.