Survey merge template
By the Analistable team · Updated · 2 min read
Paste survey responses and your customer list on separate sheets. INDEX/MATCH on email adds customer ID, segment and region to every response, unmatched respondents show Not found, the customer list flags who did not respond, and a Summary gives response count, average score and percent recommending per segment.
Part of our guide: How to join spreadsheets on a common column
What the template does
Survey tools give you answers; your CRM knows who the respondents are. Merging them on email lets you read results by segment instead of as one average, and shows which customers stayed silent. It is a left join in spreadsheet form, explained in the joining spreadsheets guide and in matching survey responses to a customer list.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Customers | Email, Customer ID, Segment, Region, Responded? | Yes (first four) |
| Responses | Email, Score (1–5), Would recommend, Customer ID, Segment, Region | Yes (first three) |
| Summary | Segment, Responses, Avg score, Recommend % | Segment names only |
How to fill it in
- Paste your customer export on Customers with email in column A.
- Paste the survey export on Responses: respondent email, score and the yes/no answer. Copy the lookup formulas in D:F down.
- List your segments on Summary, keeping the Not found row so unmatched responses stay visible.
- Copy the Responded? formula on Customers down so you can see who still needs a reminder.
How the formulas work
Each lookup trims the email and returns Not found rather than #N/A:
=IFERROR(INDEX(Customers!$C$2:$C$500,MATCH(TRIM(A2),Customers!$A$2:$A$500,0)),"Not found")The segment summary uses AVERAGEIFS and COUNTIFS on the merged columns:
=IFERROR(ROUND(AVERAGEIFS(Responses!$B$2:$B$500,Responses!$E$2:$E$500,A2),2),0)
=IFERROR(ROUND(COUNTIFS(Responses!$E$2:$E$500,A2,Responses!$C$2:$C$500,"Yes")/B2*100,1),0)MATCH ignores case, so CAT@contoso.com finds cat@contoso.com. TRIM removes stray spaces in the response, the most common cause of misses (trailing spaces break lookups).
Worked example
| Segment | Responses | Avg score | Recommend % |
|---|---|---|---|
| Enterprise | 3 | 3.67 | 66.7 |
| SMB | 3 | 4.33 | 66.7 |
| Mid-market | 1 | 4 | 100 |
| Not found | 1 | 3 | 100 |
| All responses | 8 | 3.88 | 75 |
| Customers with no response | 1 |
The overall average of 3.88 hides an Enterprise score of 3.67, which is the kind of gap the merge exists to show.
Limits and when to move on
- Anonymous surveys cannot be merged; you need an email or ID collected with the response.
- Small segments give noisy averages: one unhappy response moves a three-person segment a long way.
- If a person answered twice, both responses count. Remove duplicates first or keep the latest per email.
- For several survey waves, combine the waves first, or load the waves and the customer list into Analistable and ask how each segment's score changed between waves.
Frequently asked questions
- Can I merge on customer ID instead of email?
- Yes, if the survey captured it (for example as a hidden field). Change column A on both sheets to the ID; the formulas stay the same.
- Why does a respondent show Not found?
- They used an email that is not on the customer list, such as a personal address. Check them by hand; do not drop them silently.
- Does this work with Google Forms responses?
- Yes. Paste the response sheet's email and answer columns into Responses. See matching Google Forms responses to a master list.