Marketing attribution in a spreadsheet: Excel and Google Sheets formulas to match sales to leads

Marketing attribution in a spreadsheet is two exports and a few helper columns: clean each phone to ten digits, lowercase each email, find each customer's earliest lead dated on or before the sale with MINIFS, return its source, and total by channel with a Direct / Unknown row. VLOOKUP fails here: it returns the first row, not the earliest lead.

Most businesses that sell by phone or in person already have the two files that answer which marketing brought in each sale: a list of closed sales from the invoicing or job software, and a list of calls and forms with a source column. What they lack is the join. This post is that join, formula by formula, for Excel and Google Sheets.

It is the hands-on version of our guide to marketing attribution for small business, which explains why the match is the right method and what to do with the answer. Here the job is narrower: get the formulas right, because a lookup that quietly returns the wrong row produces a table that looks finished and credits the wrong channel.

The two files and the columns you need

You need a sales sheet with the sale date, customer name, phone, email and amount, and a leads sheet with the lead date, name, phone, email and source. Phone and email are the match keys, dates decide which lead gets the credit, and the source column is the answer.

Columns A to E come straight from the exports; everything from F on is a formula you fill down. Keeping phone and email in the same columns on both sheets means the cleaning formulas are identical on both. Three things to get right before any formula runs:

• Export leads from well before the sales period. A customer who first called eleven months ago cannot match a lead file that starts last month. Take at least a year of leads for a quarter of sales; our post on the CRM export lists the other fields that go missing.
• Dates must be real dates. Type =COUNT(Leads!A:A) somewhere: it counts numbers only, and spreadsheets store dates as numbers, so it should equal the number of lead rows. If it is 0, the dates came in as text and every MINIFS below will return 0. Convert them with DATEVALUE in a helper column.
• Every lead needs a source. Fill blank source cells with "No source recorded" rather than leaving them empty. An empty source returned by a lookup looks like no match at all and sends a real lead's sale to the wrong bucket.

Normalize phone numbers with a formula

A phone key is the number reduced to its digits, with the US and Canada country code 1 removed, so (555) 010-2030, 555.010.2030 and +1 555 010 2030 all become 5550102030. In Google Sheets and Excel for Microsoft 365, REGEXREPLACE strips the non-digits; in Excel 2021 and 2024, TEXTJOIN, MID and SEQUENCE do.

Column F holds the digits. Use whichever line fits your software, fill it down both sheets, and leave the original phone untouched in column C so you can always see what was exported:

• Google Sheets and Excel for Microsoft 365: =REGEXREPLACE(C2&"", "[^0-9]", ""). The pattern [^0-9] means any character that is not a digit, and each one is replaced with nothing. The &"" turns a phone stored as a number into text first, which Google's version requires.
• Excel 2021 and Excel 2024: =IF(C2="", "", LET(ch, MID(C2, SEQUENCE(LEN(C2)), 1), TEXTJOIN("", TRUE, IF(ISNUMBER(FIND(ch, "0123456789")), ch, "")))). SEQUENCE numbers every position in the cell, MID takes one character from each, FIND keeps only the ones that appear in 0123456789, and TEXTJOIN glues them back together. The IF skips empty cells.

Column G turns the digits into the key: =IF(F2="", "", VALUE(IF(AND(LEN(F2)=11, LEFT(F2, 1)="1"), RIGHT(F2, 10), F2))). An 11-digit number starting with 1 loses the 1, because no US or Canadian area code starts with 1, so a leading 1 can only be the country code. VALUE makes every key a number on both sheets, so the lookups below compare a number with a number and never a number with text that only looks like one.

One warning before any formula: open the CSV through the import dialog with the phone column typed as text, or a number can lose a leading zero or, if it is long enough, be rewritten in scientific notation and lose digits for good. Our post on phone normalization covers that failure, extensions, and numbers from more than one country, none of which a 10-digit rule handles.

Normalize email with LOWER and TRIM

An email key is the address in lowercase with surrounding spaces removed: =LOWER(TRIM(D2)) in column H of both sheets. "Priya.Shah@Example.com " with a trailing space and "priya.shah@example.com" are the same customer, and an exact comparison treats them as strangers until both are cleaned.

Lowercasing is safe because the domain part of an address is not case-sensitive and almost no provider treats the part before the @ that way either. Do not go further in a spreadsheet: removing dots or plus tags is right for some providers and wrong for others, and a rule applied to every address will merge people who are not the same.

Find the earliest lead with MINIFS, then its source

First-touch attribution credits each sale to the earliest lead from the same customer. In a spreadsheet that is two steps: MINIFS finds the earliest lead date for the sale's phone key, on or before the sale date, and XLOOKUP finds that date among the customer's leads and returns the source.

• Sales I2, earliest phone lead: =IF(G2="", 0, MINIFS(Leads!A:A, Leads!G:G, G2, Leads!A:A, "<"&(A2+1))). The IF matters: a sale with no phone has an empty key, and an empty criterion is read differently by Excel and Google Sheets, so it can match leads that have no phone either. A missing phone should match nothing, and the IF makes sure it does.
• Sales J2, phone source: =IF(I2=0, "", XLOOKUP(I2, FILTER(Leads!A:A, Leads!G:G=G2), FILTER(Leads!E:E, Leads!G:G=G2))). The two FILTERs cut the lead list down to this customer's rows, and XLOOKUP finds the earliest date among them and returns the source beside it.

These work as written in Google Sheets, Microsoft 365, Excel 2021 and Excel 2024. A 0 in column I means no lead; if the column is formatted as a date it shows as a day in 1899 or 1900, which is the spreadsheet's way of writing zero. Two leads from the same customer on the same day go to whichever row comes first, which is rare enough to check by hand.

Why VLOOKUP gives the wrong answer

VLOOKUP and a plain XLOOKUP both return the first matching row in the file, not the earliest lead. Their answer depends on how the export happened to be sorted. If leads are listed newest first, as many CRMs export them, each sale gets the customer's most recent lead instead of the first one.

The tempting formula is =XLOOKUP(G2, Leads!G:G, Leads!E:E, ""), or its VLOOKUP equivalent with the columns rearranged. It finds the customer and returns a source, and every row of the result looks plausible. But it has no idea which lead came first, and nothing on the sheet says so. Sort the lead file differently and the totals change.

You could sort the leads oldest first and rely on the default search order, and that does find the earliest lead, until somebody re-sorts the sheet or pastes next month's leads at the bottom. MINIFS does not care about order. That is the reason to spend one extra column on it.

Leads dated after the sale must not get credit

A lead dated after the sale did not cause it. It is the customer calling back about a delivery, a warranty or the next job. The criterion "<"&(A2+1) keeps only leads dated before the end of the sale day, so a lead logged later the same day still counts and one from next week never does.

Writing "<="&A2 looks more natural and works only when both columns are plain dates. Many lead exports carry a time as well, and a call at 9:40 on the morning of the sale is later than midnight, so a same-day call would be thrown out. Adding one day and using < keeps the whole sale day. For the same reason, do not round lead dates down in a helper column you then forget about.

This is a different question from first touch against last touch. Both models choose among leads that came before the sale. If you want the most recent lead instead, swap MINIFS for MAXIFS with the same arguments and keep the date criterion. Our post on attribution models covers which question each model answers. Whichever you pick, write it in the report.

Fall back to email when the phone does not match

A customer who filled in a web form may have given an email and no phone, or a different phone from the one on the invoice. When the phone finds nothing, run the same two steps on the email key, then combine the answers into one channel column with Direct / Unknown as the last resort.

• Sales K2, earliest email lead: =IF(OR(J2<>"", H2=""), 0, MINIFS(Leads!A:A, Leads!H:H, H2, Leads!A:A, "<"&(A2+1))). It runs only when the phone found nothing and the sale has an email.
• Sales L2, email source: =IF(K2=0, "", XLOOKUP(K2, FILTER(Leads!A:A, Leads!H:H=H2), FILTER(Leads!E:E, Leads!H:H=H2))).
• Sales M2, channel: =IF(J2<>"", J2, IF(L2<>"", L2, "Direct / Unknown")).

Phone first, then email, is a simplification. A customer whose earliest lead was an email-only form and who later called gets the call's source under this rule. If that pattern is common in your business, compute both dates for every sale and take the smaller one that is not 0. For most phone-led businesses the email step rescues a handful of web-form customers and changes little else.

Duplicates in either file

Duplicate leads do not change who gets the credit, because MINIFS picks one earliest lead however many rows a customer has. They do inflate lead counts, so flag repeats before counting leads by channel. Duplicate sales are worse: they add revenue that never happened, and they must be removed before the summary.

• Leads I2, first or repeat: =IF(G2<>"", IF(A2=MINIFS(A:A, G:G, G2), "first", "repeat"), IF(H2<>"", IF(A2=MINIFS(A:A, H:H, H2), "first", "repeat"), "no contact")). Each person's earliest row says first and the rest say repeat. Count leads with =COUNTIFS(Leads!E:E, "Google Ads", Leads!I:I, "first").
• Sales, in a spare column: =COUNTIFS(B:B, B2, A:A, A2, E:E, E2) returns 2 or more when the same customer, date and amount appear twice. Check each one against the invoice or job number before deleting: two identical jobs on one day happen, and a re-exported invoice is far more common.

A person who called once and filled in a form once with no phone on it still counts as two leads under this flag, because the two rows share no key. Our post on duplicate leads covers deduplicating on the person rather than the record, and why one customer is so often three leads.

The summary: SUMIFS by channel, with Direct / Unknown

The summary is one row per channel with revenue from =SUMIFS(Sales!E:E, Sales!M:M, A2) and sales from =COUNTIFS(Sales!M:M, A2), where A2 holds the channel name. Type the channel list yourself, include Direct / Unknown, and check that the revenue column adds up to total sales.

Typing the list, rather than generating it with UNIQUE from the channel column, keeps channels that produced leads and no sales on the table at $0, which is often the most useful row in it. The check is one cell: =SUM(B2:B8)=SUM(Sales!E:E) should say TRUE. If it says FALSE, a channel name in column M is missing from the list or spelled differently.

A pivot table gives the same result with less typing: select the sales sheet, insert a pivot table, put Channel in Rows and Amount in Values as a sum, and add Customer as a count. It updates when you refresh it, and it lists only channels that have at least one sale. Either way, Direct / Unknown stays a row of its own. Spreading it across the other channels in proportion makes the table look complete and invents credit nobody earned; our post on unattributed revenue explains what that bucket actually contains.

A worked example: eight sales, ten leads

A home-services company matches eight sales from September and October 2026 against ten leads, exported newest first. Figures and names are illustrative. Six sales match, $27,200 of $35,100; two stay in Direct / Unknown. A plain first-row lookup would have moved $15,300 of the matched revenue to the wrong channels.

Row by row. Dana Ruiz has two leads, a Google Ads call on 4 August and a Facebook form on 2 September; MINIFS returns 4 August, so the sale is Google Ads. The first-row lookup returns Facebook, because the newer row sits higher in the file. Tom Becker's leads are 28 September and 20 October; the sale was 6 October, so the criterion "<"&(A2+1) excludes the October lead and the sale goes to the Business Profile. The first-row lookup credits Google Ads with a call that came two weeks after the job was sold.

Luis Moreno matches only because column G drops the leading 1 from 1-555-010-6620; without that step, 15550106620 and 5550106620 are strangers and $1,850 lands in Direct / Unknown. Priya Shah's invoice phone is not in the leads at all, but =LOWER(TRIM(D2)) turns her invoice email into priya.shah@example.com and the email step finds her web form. Grace Lee's two Google Ads rows are one person; the credit is the same either way, but the repeat flag stops her counting as two leads.

R. Okafor is the case to resist. A Facebook lead from Rita Okafor sits in the file, but the sale's phone is a different number and there is no email. A name match is a guess, so the sale stays in Direct / Unknown, and the right next step is to check the customer record for a second phone, not to count it.

The arithmetic. Google Ads is Dana's $8,400 plus Grace's $4,100, $12,500. Direct / Unknown is Ana's $2,300 plus R. Okafor's $5,600, $7,900. Matched revenue is $35,100 − $7,900 = $27,200, so 27,200 ÷ 35,100 = 77.5% of revenue is matched. Shares are each channel's revenue ÷ $35,100: 12,500 ÷ 35,100 = 35.6%, 6,900 ÷ 35,100 = 19.7%, and so on. The first-row lookup moves Dana's $8,400 to Facebook and Tom's $6,900 to Google Ads: $15,300 of $27,200, 56% of matched revenue, in the wrong channel, with totals that still add up.

Notice what the wrong version would have said: Facebook, a channel with no first-touch sales, is the second-best performer, and the Business Profile, credited with a $6,900 job, brought in nothing. Both columns sum to $35,100. The check that catches mistakes in the totals cannot catch this one, which is why the spot-checks below look at rows, not sums.

CloseRev does this match from the same two CSV files, without the helper columns. It matches each sale to leads on normalized phone and email and, by default, credits it to the source of the earliest matched lead; last touch is an option. Only high-confidence matches count automatically; medium- and low-confidence ones stay reviewable rather than counting on their own, and sales with no match stay in Direct / Unknown.

How to spot-check the match

Totals that add up prove only that nothing was lost. To know the credit is right, check rows: a sample of matched sales traced back to the raw lead file, a sample of unmatched sales searched by hand, and every phone key that matches suspiciously many leads. It takes twenty minutes a month.

1. Reconcile the totals. The summary's revenue equals =SUM(Sales!E:E), its sales count equals the number of sales rows, and =COUNT(Leads!A:A) equals the number of lead rows.
2. Trace ten matched sales. Add =RAND() in a spare column, sort by it, and take the top ten. For each, search the raw lead export for the last four digits of the phone, confirm the lead date is on or before the sale, and confirm no earlier lead was missed.
3. Search ten unmatched sales. Look for the last seven digits of each phone in the raw lead file. If you find one, a format slipped past the cleaning formula, and that is worth fixing before anything else.
4. List keys with many leads. On the leads sheet, =IF(G2="", "", COUNTIFS(G:G, G2)) shows how many rows share each phone key. Sort it largest first. A key with fifteen leads is a front desk, an office line or a phone that was typed into every blank field.
5. Compare the match rate with last month's. A sudden fall usually means a broken export or a new phone format, not worse marketing. Our post on match rates gives the ranges to expect.

Where spreadsheets break

The method works at any size; the spreadsheet does not. It slows down as rows multiply, it credits shared phone numbers to whoever called first, it tempts people to count name matches, it reports sources however people typed them, and it has to be rebuilt by hand every month with no record of what was reviewed.

• Thousands of rows. Every MINIFS and FILTER in the sales sheet scans the whole lead list for every sale. Two thousand sales against twenty thousand leads is 2,000 × 20,000 = 40 million comparisons per formula column, repeated whenever the lead list changes. Replacing whole columns such as Leads!A:A with fixed ranges such as Leads!$A$2:$A$25000 helps; it does not make the problem go away.
• Shared numbers. A spouse who called from the postcard and a partner who signed the contract share a home phone, and that match is usually right. A business line, a property manager's office or a call center that books for many customers is not one customer, and every sale on it goes to whoever called first. The key-count check above finds them; exclude those keys from matching rather than trusting them.
• Name-only matches. It is tempting to add a third lookup on name when phone and email fail. Do not count its results. Common surnames, initials and nicknames make it wrong often enough to move whole channels, and nothing on the sheet shows which matches were guesses.
• Retyped sources. "Google Ads", "PPC", "Adwords" and "Google Ads " with a trailing space are four different sources to SUMIFS and to a pivot table. Fix it at entry with a dropdown (Data, then Data validation, in both Excel and Sheets) holding your short source list. Our post on lead source tracking covers keeping that column honest.
• Monthly repetition. Every month someone pastes two new exports, extends the ranges, fills the formulas down and fixes whatever broke. A paste over column G or a filter left on turns into a wrong number nobody notices, and the decision to leave R. Okafor in Direct / Unknown lives in somebody's memory, not in the file.

None of this means the spreadsheet was a mistake. For a business with a few hundred sales a month, these formulas answer the question, and building them once teaches you exactly where your own data is weak: which exports drop phone numbers, which sources are retyped, how much revenue matches nothing. That knowledge is worth having whatever does the matching later.

Clean the keys, find the earliest lead before the sale with MINIFS, fall back to email, keep Direct / Unknown visible, and check rows as well as totals. The formula that returns the first row in the file produces a table that adds up perfectly and credits the wrong channel, and nothing on the sheet will tell you.