Formulas

Cost per lead formula: how to calculate CPL

Cost per lead (CPL) is what you paid for each lead a campaign produced.

About the Google Ads connector

The formula is a division. Almost every wrong CPL comes from the denominator, because "lead" is a word the ad platform does not define and the business often has not either.

The formula

CPL = marketing cost ÷ number of leads

In Google Sheets, with cost in column B and leads in column C:

=IF(C2=0, "", B2/C2)

The IF leaves a campaign with no leads blank instead of returning #DIV/0!. Format as currency to two decimal places.

Decide what a lead is before you count one. The usual bar is a person you could sell to who has given you a way to contact them. A form submission is evidence of a lead, not the lead itself.

A worked example

The total row divides total cost by total leads: =B5/C5, which is 3,400 ÷ 132 = 25.76. Averaging the three CPLs would give 30.00, which overweights the display campaign's twelve leads.

The common mistake

Counting every form submission as a lead.

The 132 in the table are form submissions, straight from the conversion column. Go through them and 22 turn out to be spam, job applicants, existing customers asking for support, and one person who filled the form in three times. That leaves 110 leads, and the CPL is 3,400 ÷ 110 = 30.91, not 25.76. The display campaign is usually where this bites hardest, because cheap, broad placements collect submissions from people who were never going to buy anything.

The fix is to count leads from the system that qualifies them, usually the CRM, and to match them back to the campaign that produced them. Google Ads has a route for exactly that: an imported conversion, where the CRM sends a qualified lead back to Google Ads against the click that produced it, so the Conversions column counts qualified leads rather than submissions. Source: Google Ads Help, About offline conversion imports. See Google Ads conversion tracking for how the conversion count is built in the first place.

What counts as good

It depends on what a lead is worth to you. If one lead in ten becomes a customer and a customer is worth £600.00 in gross profit, a lead is worth £60.00, and a CPL of £30.91 is comfortable. The same CPL on leads that close one in fifty is not. CPL is a step on the way to CPA and CAC, and it is those that decide whether the campaign is paying.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Cost and Conversions for every campaign in a tab of its own, for example one called Google Ads. If your conversion action is the qualified lead step, the Conversions column is your lead count; if it is the raw submission, keep the qualified count in a column of your own on the formulas tab.

Fresh Sheets replaces that tab's contents on every refresh, so keep your formulas on a separate tab. This version finds each column by its heading, so it still works if you add a field and the columns move:

=SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Cost", 'Google Ads'!1:1, 0))) / SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Conversions", 'Google Ads'!1:1, 0)))