How to match survey responses to your customer list

Join and segment

Plan  =XLOOKUP(LOWER(TRIM([@Email])), Customers[Email key], Customers[Plan], "Not a customer")
Group =IF([@Score] >= 9, "Promoter", IF([@Score] >= 7, "Passive", "Detractor"))

NPS for Pro = 15/25 − 3/25 = 60% − 12% = 48. Starter = 35% − 25% = 10. Enterprise = 37.5% − 37.5% = 0 — with only 8 responses, treat that figure with caution.

Response rate and non-respondents

Responded       =COUNTIF(Responses[Email key], [@[Email key]]) > 0
Response rate % =COUNTIFS(Customers[Plan], [@Plan], Customers[Responded], TRUE) / COUNTIFS(Customers[Plan], [@Plan])

A low response rate in one segment can make its NPS misleading, so report the two together.

Several survey waves

Stack each wave's export with a Wave column, then compare a customer's score across waves with XLOOKUP on email + wave. Customers whose score dropped by 3 or more points are worth a call.

Anonymous surveys can't be joined to customer data — and shouldn't be. Only join when respondents were told their answers would be linked to their account.

Open-text answers

Free-text answers become more useful once they're joined to customer data: filter detractors on your highest plan and read their comments first. A simple keyword flag (=ISNUMBER(SEARCH("price", [@Comment]))) for recurring topics lets you count themes by segment.

Common problems

• Respondents using a personal email that isn't on the customer list — match the remainder by name and company.
• Duplicate responses from the same person — keep the latest per email.
• Small segments: an NPS from fewer than about 30 responses moves a lot with each answer, so show the response count next to it.

Step by step

1. Export responses with the respondent's email or a customer ID, the score, any comment and the submission date.
2. Export the customer list with email, customer ID, plan, signup date and region.
3. Add an email key with LOWER and TRIM to both tables.
4. Remove repeat responses, keeping the latest per email.
5. Bring plan and other fields into the responses with XLOOKUP, then build the NPS table with COUNTIFS.

Responses   =COUNTIFS(Responses[Plan], [@Plan])
Promoters   =COUNTIFS(Responses[Plan], [@Plan], Responses[Group], "Promoter")
Detractors  =COUNTIFS(Responses[Plan], [@Plan], Responses[Group], "Detractor")
NPS         =ROUND(([@Promoters] - [@Detractors]) / [@Responses] * 100, 0)

In Excel 2019, replace XLOOKUP with =IFERROR(INDEX(Customers[Plan], MATCH(LOWER(TRIM([@Email])), Customers[Email key], 0)), "Not a customer"). In Google Sheets, XLOOKUP and COUNTIFS work as shown; with a Form-linked responses tab, refer to the column ranges rather than table names.

Second example: score by customer tenure

Joining the signup date lets you check whether new customers score differently from long-standing ones. Add Tenure = DATEDIF([@[Signup date]], [@[Response date]], "m") and group it into bands.

Under 3 months: 30% − 40% = −10. 3–12 months: 50% − 20% = 30. Over 12 months: 47.8% − 8.7% ≈ 39. The 73 responses here are the same ones as in the plan table, cut a different way. A low score among new customers often points to onboarding rather than the product itself.

Troubleshooting

Checking and repeating

The responses in the segment table must add up to the deduplicated response count, and promoters plus passives plus detractors must equal responses in each row. For each new wave, append the export with its Wave value rather than overwriting, so trends per customer stay available. The join itself is the pattern in finding matches between two lists; to ask questions such as "which plan's detractors mention price?" across the files without building formulas, Analistable runs the query in your browser.

Which identifier to match on

Most survey tools can pass a value from the link into a hidden field, though the setting has a different name in each. It's worth setting up before the next wave, because it removes most of the matching work.

Doing it in SQL

With the responses and customers loaded as tables, NPS by plan is one query (DuckDB or SQLite):

SELECT COALESCE(c.plan, 'Not a customer') AS plan,
       COUNT(*) AS responses,
       ROUND(100.0 * (SUM(r.score >= 9) - SUM(r.score <= 6)) / COUNT(*)) AS nps
FROM responses r
LEFT JOIN customers c ON LOWER(TRIM(c.email)) = LOWER(TRIM(r.email))
GROUP BY 1
ORDER BY responses DESC;

On the 73 responses in the plan table, this returns Starter 40 and 10, Pro 25 and 48, Enterprise 8 and 0. Deduplicate repeat responses before running it, in the same way as for the spreadsheet method.

Common mistakes

• Comparing NPS between segments without showing response counts.
• Dropping non-customers silently, which hides how many respondents couldn't be matched.
• Joining on a customer list exported months after the survey, so plans reflect today rather than the survey date.

Acting on the results

Send each account owner the list of detractors on their accounts with the comment and score, and agree a follow-up date. Mark who was contacted in the joined table, so the next wave can compare scores for customers who were called with those who weren't.

Frequently asked questions

How do I link survey answers to customer data?

Join the survey export to your customer list on a normalised email address with XLOOKUP or Power Query Merge.

How do I calculate NPS by segment?

For each segment, NPS = percentage of promoters (scores 9–10) minus percentage of detractors (0–6).

How do I find customers who didn't respond?

Mark each customer whose email has a COUNTIF of zero in the responses.

What is a good response rate?

It varies by channel and audience. More important is whether response rates are similar across segments, so results aren't skewed.

Can I join responses that don't include an email?

Only if they carry another unique identifier, such as a customer ID passed in the survey link.

How do I deal with several responses from one customer?

Keep the latest per email for NPS, but keep earlier waves in a stacked table if you want to track how each customer's score changes.

Related guides

• How to match timesheet hours to projects and invoices
• How to merge GA4 data with ad cost data
• How to return every match in one cell with TEXTJOIN
• VLOOKUP vs XLOOKUP vs INDEX MATCH: which should you use?
• How to use XLOOKUP with another workbook