Which leads never became customers?

The data

In a spreadsheet

Converted  =COUNTIF(Customers[Email], [@Email]) > 0
Never      =FILTER(Leads[Email], NOT(Leads[Converted]))
Rate       =COUNTIFS(Leads[Source], A2, Leads[Converted], TRUE) / COUNTIFS(Leads[Source], A2)

In SQL

SELECT l.source,
       COUNT(*) AS leads,
       SUM(c.email IS NOT NULL) AS customers,
       ROUND(100.0 * SUM(c.email IS NOT NULL) / COUNT(*), 0) AS conversion_pct
FROM leads l
LEFT JOIN customers c ON c.email = l.email
GROUP BY l.source;

Before acting on it

• Leads convert over time — compare leads old enough to have had a chance (for example, created more than 60 days ago).
• People often buy with a different email than they used as a lead; match on company domain as a second pass for B2B.
• Small numbers swing wildly: one referral lead isn't a 100% conversion channel.

Ask it in Analistable: “What share of leads from each source became customers within 90 days?” For ad campaigns specifically: matching Google Ads conversions to CRM deals.

Time to convert

For leads that did convert, the time between lead creation and first order shows how long a fair comparison window should be:

Days to convert =MINIFS(Orders[Date], Orders[Email], [@Email]) - [@[Created on]]

If most conversions happen within 45 days, leads younger than that shouldn't count as “never converted” yet. Report conversion for leads created at least that long ago.

What to do with the list

• Unconverted leads from the best-converting source are the warmest to follow up.
• Sources with many leads and no customers may be attracting the wrong audience — check targeting before increasing spend.
• Remove leads who asked not to be contacted before exporting a follow-up list.

A second pass on company domain

Domain        =LOWER(TEXTAFTER([@Email], "@"))
Domain match  =AND(NOT([@Converted]), COUNTIF(Customers[Domain], [@Domain]) > 0)

For B2B leads, a colleague often signs the contract with a different address. Matching the domain (lune.fr, hart.co.uk) catches those, but exclude free email domains such as gmail.com or outlook.com, which would match unrelated people. In Excel 2019, use =LOWER(MID(A2, FIND("@", A2) + 1, 100)) instead of TEXTAFTER. Review domain matches by hand before counting them as conversions.

Overall numbers in the example

Half the leads came from ads and none converted. That is a question about targeting or follow-up — but with three leads, check again when the sample is bigger before cutting the budget.

Troubleshooting

Re-run the match monthly on the full lead list, not just new leads — leads from earlier months keep converting. Related reading: matching HubSpot contacts to Stripe customers.

Frequently asked questions

How do I find leads that didn't convert?

Filter leads whose email has a COUNTIF of zero in the customer list, or use a left join where the customer side is NULL.

How do I calculate conversion rate by lead source?

For each source, divide the number of converted leads by the total number of leads.

What if customers use a different email than their lead?

Add a second match on company domain or name, and review those matches by hand.

Should I match on company domain?

As a second pass for B2B leads, yes, but exclude free email domains and review matches by hand.

How old should leads be before I count them?

Older than the time most converting leads take to buy, so recent leads aren't counted as lost.

Can I do this in Google Sheets?

Yes. COUNTIF works the same, and QUERY can group the converted flag by source.

Related guides

• Why don't these two reports add up to the same total?
• Which customers are new this month?
• Which orders were refunded but still shipped?
• Which products are most often bought together?
• Which products are returned more than average?