Analyze Cancel and Lapse Rates by Lead Source in Excel

Learn how to see which lead sources are driving solid long term customers and which ones are more likely to cancel or lapse. In this lesson, you will compare cancel and lapse rates by lead source in Excel, highlight above or below average performance, and build a clear chart you can share with your team to guide smarter marketing and acquisition decisions.

Download the Excel file used in this tutorial:

Step By Step Instructions

How to Calculate Cancel and Lapse Rates by Lead Source in Excel

This tutorial shows how to calculate canceled and lapsed policy rates by lead source in Excel. Once you know these rates, you can connect them with Lifetime Value (LTV) and Acquisition Cost (CPA) to determine how much you can spend to acquire a new customer from each channel.

1. Build a Unique List of Lead Sources

The Lead Source field is in Column M of your dataset.
Use the UNIQUE() function on Column M to remove duplicates so each source appears only once.
If you see a “0” at the bottom, that’s because the formula references blank cells below the data.
To clean it up:
- Highlight the list
- Press Ctrl + C, then Ctrl + Shift + V to paste values only
You can also sort the list alphabetically by going to Data → A to Z.

2. Create the Policy Status List (Active, Lapsed, Canceled)

Policy status is stored in Column AK.
Use UNIQUE() to list the different values (Active, Lapsed, Canceled Midterm).
Copy and paste values with Ctrl + Shift + V.
Rearrange to keep the order consistent: Active → Lapsed → Canceled Midterm.

3. Use COUNTIFS to Count Policies by Lead Source and Status

You’ll now count how many Active, Lapsed, and Canceled policies each lead source has.

Use the COUNTIFS() function with two criteria:

Lead Source = selected lead source
Policy Status = selected status

Structure:

=COUNTIFS(LeadSourceRange, LeadSourceCell, PolicyStatusRange, StatusCell)

To make your formulas work when dragged across and down:

Lock the dataset columns (use $M:$M and $AK:$AK).
Lock columns and rows where needed so as you drag:
- Horizontally → the lead source stays fixed but the status changes
- Vertically → the status stays fixed but the lead source changes

Use $ signs to control what stays fixed:

$ before a column letter locks the column.
$ before a row number locks the row.

4. Calculate the Cancel and Lapse Rate

Once you have counts for each status, calculate the cancel and lapse rate for each lead source.

Formula:

=(Lapsed + Canceled) ÷ (Active + Lapsed + Canceled)

Format the result as a percentage with Ctrl + Shift + 5.
Adjust decimals to show the precision you want.
This value represents the percentage of policies that were canceled or lapsed for each lead source.

5. Calculate the Overall Average Cancel and Lapse Rate

To find the overall benchmark:

=(Σ Lapsed + Σ Canceled) ÷ (Σ Active + Σ Lapsed + Σ Canceled)

Use F4 to lock ranges in this formula.
If you want to repeat the average value in multiple rows (for a chart line), enter =cell_above in the next cells and drag down.

6. Build the Combo Chart with a Secondary Axis

You now have a table with:

Counts of Active, Lapsed, and Canceled
Cancel and Lapse Rate (%)
Average benchmark (optional)

To visualize:

Highlight your summary table.
Go to Insert → Recommended Charts → All Charts → Combo.
Use a stacked column for counts (Active, Lapsed, Canceled).
Add Cancel and Lapse Rate and Average as line series on a secondary axis.

This makes the small percentage values visible next to larger count values.

7. Format the Chart

Rename the chart to “Cancel and Lapse Rate by Lead Source.”
Format the average line as a gray dashed line for a benchmark look.
To remove decimals from chart labels:
- Double-click the axis or label
- Go to Number → Decimal Places → 0
You can keep decimals in the cells but hide them in the chart.

8. Optional Formatting Tweaks

Remove categories you don’t want by deleting rows or series.
Delete specific labels if they clutter the chart.
Adjust fonts, sizes, and colors for better presentation.

The result is a professional chart showing:

Each lead source’s Active, Lapsed, and Canceled volumes
Cancel and Lapse Rate percentage
Whether each lead source performs above or below the overall average

This is critical for deciding where to focus your marketing and retention efforts.

FAQ

Analyzing Cancel and Lapse Rates by Lead Source

Q1. What are cancel and lapse rates in insurance analytics?
Cancel and lapse rates show the percentage of policies that are either canceled midterm or allowed to lapse instead of renewing. These metrics help you understand the quality of your business and how well you are retaining customers over time.

Q2. Why should I analyze cancel and lapse rates by lead source?
When you break cancel and lapse rates down by lead source, you can see which channels bring in loyal, long term clients and which channels tend to produce short lived policies. That insight helps you decide where to increase spend and where to cut back.

Q3. How does this analysis help with customer acquisition cost and lifetime value?
If you know how often policies from each lead source cancel or lapse, you can estimate the lifetime value of those customers. That makes it much easier to set a realistic customer acquisition cost and avoid overpaying for low quality leads.

Q4. Can I build this cancel and lapse view into my Excel dashboard?
Yes. Once you have your counts and rates by lead source, you can plug them into an Excel dashboard and use charts to compare performance. This works well alongside other insurance KPIs, such as written premium, retention, or renewal rate.

Q5. What is the benefit of using a combo chart with a secondary axis?
A combo chart with a secondary axis lets you show cancel and lapse rates as a line while keeping policy counts as columns. This makes it easy to see both the volume of business and the quality of that business in a single visual.

Q6. Can I reuse this approach for other KPIs like renewal or retention rates?
Absolutely. The same structure can be used to compare renewal rate, retention rate, or even claim frequency by lead source. Any KPI that varies by channel can be broken out in this way to guide better marketing and sales decisions.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Databoards

Analysis & Development