Lead to customer match template
By the Analistable team · Updated · 3 min read
The workbook has a Leads sheet, a Customers sheet and a Summary. Paste your lead export and customer export, keep email in column B of both, and the formulas flag which leads became customers, pull their revenue, list customers with no lead record, and total conversion rate and revenue by lead source.
Part of our guide: How to join spreadsheets on a common column
What the template does
Marketing keeps leads in one system and finance or the shop keeps customers in another. The question everyone asks — which sources actually produce paying customers? — needs the two lists joined on a shared key. This template uses the email address as that key, because it is the one field both exports nearly always contain. It is a ready-made version of the approach in how to match HubSpot contacts to Stripe customers, built with formulas that work in Excel 2016 onwards and in Google Sheets.
Sheet-by-sheet guide
| Sheet | You fill in | Formulas add |
|---|---|---|
| Instructions | Nothing | — |
| Leads | Lead ID, Email, Source, Created | Customer? (Yes/No), Revenue |
| Customers | Customer ID, Email, First order, Revenue | Lead found? (Yes/No) |
| Summary | Source names in column A | Leads, Customers, Conversion, Revenue |
How to fill it in
- Export leads from your CRM or form tool and paste the rows into Leads, starting in A2. Keep the column order: ID, email, source, created date.
- Export customers from your billing or shop system and paste into Customers from A2, with email in column B and total revenue in column D.
- Type each lead source you use into Summary column A, spelled exactly as it appears in the Leads sheet.
- Fill the formula columns down if you have more than the sample rows. Ranges run to row 100; extend them for bigger lists.
How the formulas work
Each lead looks for its email in the customer list. COUNTIFS ignores case, and TRIM removes stray spaces that would otherwise break the match (see why trailing spaces break matches):
=IF(COUNTIFS(Customers!$B$2:$B$100,TRIM(B2))>0,"Yes","No")
=SUMIFS(Customers!$D$2:$D$100,Customers!$B$2:$B$100,TRIM(B2))The Summary counts converted leads per source and divides by all leads from that source:
=COUNTIFS(Leads!$C$2:$C$100,A2,Leads!$E$2:$E$100,"Yes")
=IFERROR(ROUND(C2/B2,3),0)Worked example
The sample has eight leads and five customers. Four leads converted; one customer (zoe@field.com) has no lead record, which the Customers sheet flags.
| Source | Leads | Customers | Conversion | Revenue |
|---|---|---|---|---|
| Google Ads | 3 | 2 | 0.667 | 1800 |
| Webinar | 3 | 1 | 0.333 | 800 |
| Referral | 2 | 1 | 0.5 | 3400 |
Limits and when to move on
- Email is the only key. People who sign up with a work address and pay with a personal one will not match; check the unmatched rows by name before trusting the rate. Joining on email addresses safely covers the clean-up.
- Revenue is summed per email, so a duplicate customer row counts twice. Remove duplicates in the customer export first.
- For tens of thousands of rows, or when you also want campaign, deal stage and order date in one answer, load both exports into Analistable and ask, for example, which source produced the most revenue in its first 90 days.
Frequently asked questions
- Does the match care about upper and lower case?
- No. COUNTIFS and SUMIFS compare text without regard to case, so ANNA@north.co and anna@north.co match. Spaces do matter, which is why the formulas wrap the lead email in TRIM.
- Can I match on customer ID instead of email?
- Yes, if both systems store the same ID. Put that ID in column B of both sheets instead of the email and the formulas work unchanged.
- Does it work in Google Sheets?
- Yes. Upload the .xlsx to Google Drive and open it with Google Sheets; every formula uses functions both programs support.
- How do I see leads that never bought?
- Filter the Leads sheet on Customer? = No. That list is the starting point for follow-up or for checking whether the lead used a different email.