Blog · Finance metrics and formulas · Sales teams
Cost per lead divides campaign spending by consistently defined leads. Work through campaign costs, duplicate removal, spreadsheet formulas, and the reconciliation that proves the total.
Cost per lead is campaign spending divided by the valid leads that spending generated. A campaign costing $15,000 with 300 valid leads has a CPL of $50, provided the spending and leads use the same attribution rule and reporting period.
Cost per lead = Eligible campaign spending / Valid attributed leads
The numerator answers what you paid to generate the inquiries. The denominator answers what counts as an inquiry and which campaign receives credit. Write both definitions above the report before comparing channels.
For a media buying decision, use media spending. For a campaign budget review, include the direct creative, agency, or event costs you intend to evaluate. Neither basis is useful without a label. Moving agency costs out of the report would lower the result without creating another lead.
A lead is also different from a customer. Keep the broader cost of winning customers in customer acquisition cost, rather than presenting this campaign ratio as fully loaded customer economics.
Use a spending export with campaign ID, service period, cost category, currency, and amount. Use a CRM export with a stable lead ID, creation date, attributed campaign ID, and eligibility status. Keep rejected records with their exclusion reason so someone can reproduce the count.
Choose one reporting basis. This example uses campaigns run in May 2026 and leads created during that month, attributed to those campaigns under a single campaign-credit rule. It excludes later leads from this particular monthly report. All costs are USD and relate to those campaigns.
Match on campaign ID, not a campaign name that someone can edit. Aggregate the spending separately before combining the campaign totals with the lead totals. Attaching a campaign's full spend to every lead and then summing the combined file would repeat the cost.
Decide whether existing customers, employees, test submissions, and uncontactable inquiries qualify before calculating. Preserve an exclusion category for unmatched campaign IDs; quietly discarding those rows makes the reconciliation impossible.
A US commercial equipment supplier includes direct media, creative, and campaign support costs. The support column includes agency, event, or referral administration costs assigned to the campaign.
| Campaign | Media (USD) | Creative (USD) | Support (USD) | Campaign cost (USD) |
|---|---|---|---|---|
| Search | 12,000 | 1,800 | 1,200 | 15,000 |
| Webinar | 6,000 | 1,500 | 2,500 | 10,000 |
| Referral | 2,000 | 0 | 3,000 | 5,000 |
| Total | 20,000 | 3,300 | 6,700 | 30,000 |
The lead export contains submissions rather than ready-to-use unique counts. Apply duplicate removal first, then classify the remaining invalid inquiries. That order makes the exclusion groups mutually exclusive.
| Campaign | Submissions | Duplicate submissions removed | Other invalid inquiries removed | Valid leads |
|---|---|---|---|---|
| Search | 345 | 25 | 20 | 300 |
| Webinar | 125 | 10 | 15 | 100 |
| Referral | 220 | 10 | 10 | 200 |
| Total | 690 | 45 | 45 | 600 |
Now divide each campaign's cost by its valid lead count.
| Campaign | Cost (USD) | Valid leads | Calculation | CPL (USD) |
|---|---|---|---|---|
| Search | 15,000 | 300 | 15,000 / 300 | 50.00 |
| Webinar | 10,000 | 100 | 10,000 / 100 | 100.00 |
| Referral | 5,000 | 200 | 5,000 / 200 | 25.00 |
| Total | 30,000 | 600 | 30,000 / 600 | 50.00 |
Amounts per lead are rounded to cents after division. Keep the underlying values unrounded for totals and subsequent comparisons.
In Excel, prepare a Spend sheet with campaign ID in column A, service date in B, and eligible cost in C. Prepare a deduplicated Leads sheet with campaign ID in A, lead creation date in B, and eligibility in C. Put a campaign ID in summary cell A2 and the reporting start and next-period start dates in H1 and I1.
Campaign cost in B2 is:
=SUMIFS(Spend!$C$2:$C$10000,Spend!$A$2:$A$10000,A2,Spend!$B$2:$B$10000,">="&$H$1,Spend!$B$2:$B$10000,"<"&$I$1)
Microsoft documents that SUMIFS adds values meeting multiple criteria. Here those criteria select the campaign and its service dates.
Valid leads in C2 are:
=COUNTIFS(Leads!$A$2:$A$10000,A2,Leads!$B$2:$B$10000,">="&$H$1,Leads!$B$2:$B$10000,"<"&$I$1,Leads!$C$2:$C$10000,"Valid")
COUNTIFS counts rows meeting all supplied criteria. It does not make repeated IDs unique, so duplicate removal must happen before this formula. Extend every range to cover the complete export.
CPL in D2 is:
=IF(C2=0,"N/A",B2/C2)
For the total across the example's three summary rows, use =SUM(B2:B4)/SUM(C2:C4). Covirage is not affiliated with Microsoft.
First, rebuild the lead count: 690 submissions minus 45 duplicates minus 45 other invalid inquiries equals 600 valid leads. Each submission belongs to one of those categories, and each valid lead receives one campaign assignment in this report.
Second, tie the $30,000 cost to the selected spending export. The component totals also agree: $20,000 plus $3,300 plus $6,700 equals $30,000.
Third, multiply each unrounded CPL by its lead count. Search contributes $15,000, Webinar $10,000, and Referral $5,000, rebuilding the cost total. The lead-weighted average is therefore $50. The simple average of $50, $100, and $25 is $58.33 and does not represent the combined campaigns.
Referral generated the lowest CPL in this example. That alone does not justify expanding its budget: available volume, qualification, and eventual sales can differ. Use cost per qualified lead for the next stage and cost per lead by channel when the allocation across channels is the question.
An outside comparison needs the same cost scope, lead event, geography, and period. The guide to average cost per lead by industry explains those benchmark checks; an unmatched industry average is not a budget target.
Bring a campaign spending file and a sample of your own CRM export. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.
See Covirage for sales teams for the wider context.
Start with the decision you are making. Media-only CPL includes advertising spend; a broader campaign CPL can include creative, agency, and event costs. Label the basis and allocate shared costs consistently so a scope change does not look like better performance.
A lower CPL helps only when the leads have comparable value. A campaign can produce inexpensive inquiries that rarely qualify or buy. Read CPL alongside qualification, opportunity conversion, and later customer economics before moving the budget.
Divide total eligible campaign spending by total valid leads after applying the agreed attribution and duplicate rules. Do not average campaign CPLs directly. A simple average gives a small campaign the same influence as a large campaign.
Report the spend and a zero lead count, and show CPL as undefined or not applicable. Do not show zero dollars per lead. Confirm that tracking, matching, and the reporting window are complete before interpreting the absence of leads.