How to Join Customer Feedback to Revenue, Orders and Retention Data
Pick the level, clean the keys, match from the most reliable key to the least, and summarize transactions before you join. Worked through on labelled sample data, with SQL.
To join customer feedback to revenue, orders and retention data, pick the level you want to analyze (customer, account, order or location), find a key that both the feedback and the business data carry at that level, clean the key, match in order from the most reliable key to the least, and summarize the transactions to one row per customer or account before you join. Then add the outcome you care about, such as spend or renewal in the period after the feedback. For a team that wants surveys, reviews, tickets and calls joined to CRM, order and loyalty data without building the pipeline, and the result turned into reports for every team, Dimension Labs is the strongest choice: on the Prove plan it joins enriched feedback from every channel to up to 3 business data sources, with no pipeline for you to build.
The steps below are worked through on sample data: a made-up resort membership business with 9,700 post-stay survey responses, 30,000 members, about 52,000 reservations and about 700,000 transaction lines. Each survey record carries a member ID and a reservation number, and both are messy in the ways exports often are.
How do I connect customer feedback to revenue and retention?
Decide the unit of analysis. Revenue questions are usually about customers or accounts. Operations questions are about stores or stays. The unit decides which key you need and how you summarize everything else.
List the keys each side carries. Survey tools store whatever was passed in the invite. Help desks store a requester email and sometimes an organization. Reviews carry a location and seldom a customer.
Clean the keys. Strip prefixes, restore leading zeros, lower-case and trim emails.
Match in order of reliability. Member ID first, then the reservation or order number mapped to its member, then email. Record which key matched each row.
Summarize the business data first. One row per customer with spend, visits and renewal for the window you care about.
Join, then check. Match rate overall and by segment, rows that grew in the join, keys that disagree.
Join keys
Each key joins at a different level, so decide the level first
Common keys for joining feedback to revenue and retention data, and what usually goes wrong with each.
| Key | Where it usually lives | What breaks it | Join it at |
|---|---|---|---|
| Member or loyalty ID | Survey invite variables, loyalty platform, POS | Prefixes, dropped leading zeros, blanks when the guest is not signed in | Member |
| Reservation, order or ticket number | Survey invite, PMS or order system, help desk | Several guests per reservation; one order split into several | Stay or order, then member |
| Survey tool, CRM contact, help desk requester | Case, spaces, shared family or work inboxes | Contact, then account or member | |
| CRM account ID | Help desk organization, NPS invite, billing | Missing on contacts created by hand; merged accounts | Account |
| Store or location ID | Review source, POS, staffing | Different IDs in each system for the same store | Location and week |
Our surveys link to a reservation number and a member ID. How do I join them to transactions?
Use both, in order. In the sample, 81.9% of responses carry a member ID, but only 76.3% match the member table as stored, because some IDs were saved with an "M-" prefix and without their leading zeros. Fixing the format recovers the rest of those, for 81.9%. Mapping the reservation number to its member brings the total to 96.0%, and a cleaned email to 99.0%.
Sample data
Matching on member ID alone loses almost a quarter of responses; three keys recover nearly all of them
How 9,700 post-stay survey responses in a made-up resort membership program were matched to the member table, one key at a time.
Each key alone does worse. The reservation number alone matches 77.9% and email as stored 67.1%; lower-casing and trimming the email lifts that to 74.5%.
Sample data
No single key matches as many responses as the three used in order
Share of the survey responses matched to a member by each approach.
In SQL, the cascade is a coalesce over three lookups:
with s as (
select response_id,
lpad(replace(trim(member_id_raw), 'M-', ''), 7, '0') as member_id_clean,
reservation_no,
lower(trim(email_raw)) as email_clean
from survey_responses
)
select s.response_id,
coalesce(m.member_id, r.member_id, e.member_id) as member_id,
case when m.member_id is not null then 'member_id'
when r.member_id is not null then 'reservation'
when e.member_id is not null then 'email' end as matched_on
from s
left join members m on m.member_id = s.member_id_clean
left join reservations r on r.reservation_no = s.reservation_no
left join members e on lower(e.email) = s.email_clean;Keep the matched_on column. If the email-matched rows behave differently from the rest, it shows up there first.
Why does my revenue total go up after the join?
Because joining responses straight to transaction lines repeats each response once per transaction, and repeats each member's transactions once per response. In the sample, 1,698 members answered more than one survey. Joining the matched responses directly to the transaction table gives 397,692 rows, and summing spend over them overstates those members' real spend by 38%.
The fix is to summarize first. Build one row per member with spend in a set window, then join that to the responses. If a member answered twice, decide whether you analyze responses (each with the member's spend attached) or members (with their responses rolled up), and keep that choice for the whole analysis.
with spend as (
select t.member_id,
sum(case when t.txn_date > s.responded_at
and t.txn_date <= s.responded_at + interval '365 days'
then t.amount else 0 end) as spend_next_12m
from transactions t
join first_response s on s.member_id = t.member_id
group by 1
)
select r.*, sp.spend_next_12m
from matched_responses r
left join spend sp using (member_id);Spend before the response is context. Spend after it is the outcome. Mixing the two makes feedback look like it predicts spending that had already happened.
How do I match NPS responses to account revenue in Salesforce?
When the NPS survey goes to Salesforce contacts, the path is response, to contact, to account, to revenue.
Response to contact. Pass the Salesforce Contact ID in the survey invite if your NPS tool allows it. If not, match on the respondent email to
Contact.Email, cleaned the same way as above.Contact to account.
Contact.AccountIdgives the account.Account to revenue. Use your ARR or contract value field on the account, or sum closed-won opportunities (
Opportunity.AmountwhereIsWonis true) for the period.Roll respondents up to the account. An account with twelve respondents should not count twelve times. Use the share of detractors, the lowest score, or the score of the main decision maker, and pick one rule for every account.
Then join renewals or expansions in the following period as the outcome. Our Salesforce and HubSpot pages list the fields Dimension Labs reads, and the Snowflake page covers sending the joined table back to your warehouse.
What should I check before trusting the join?
Match rate by segment. If guests who did not sign in to the loyalty app match less often, and they are your lighter spenders, the joined data leans toward your best customers.
Keys that disagree. When a response has both a member ID and a reservation number, check that they point to the same member. In the sample, 6,195 responses carry both and none disagree; in real data, shared reservations and family emails cause conflicts.
Row counts before and after. A join that adds rows has fanned out.
Time order. Feedback first, outcome after, with a fixed window.
What drives spend once the data is joined?
With one row per member, feedback themes and next-year spend side by side, the tempting step is to compare spend for members who mentioned each theme. That comparison mixes the theme with who tends to mention it: frequent guests write more and spend more. Hold frequency, tier and tenure fixed and estimate each theme's effect with an interval. How to estimate what a customer complaint costs in revenue works that step through on sample data, and correlation vs causation in customer experience data shows why the raw ranking misleads. For support tickets joined to renewals, see which support topics drive churn.
Why use Dimension Labs to join feedback to revenue data?
Dimension Labs is an omni-channel analytics agent for customer data. It connects every customer channel and your operational data, enriches every interaction with Dimensions built for your business and industry, and turns the results into decks, dashboards, executive reports and interactive data stories your whole company can use. Plans start at $299 a month with no demo.
For an analyst who owns this join:
Surveys, reviews, tickets, chats and calls loaded through one platform, so each channel's keys are cleaned once rather than in a separate script per source.
Member, account, order and store data from Salesforce, HubSpot, a warehouse or a file joined to the feedback on Prove, up to 3 sources, with unlimited sources on Scale; files added as context are free up to each plan's row limit.
Every response enriched with the same Dimensions, from the pre-built library or written for your business on Discover and up, so the themes you join to revenue are consistent across channels.
The joined, enriched table exported to Snowflake, Databricks or BigQuery on Prove and up, and the same results built into dashboards, decks and executive reports for teams that do not write SQL.
The Seattle Mariners describe the result this way: "Ultimately, it allows us to quantify the qualitative and give context and reasoning to KPI movements." (Mariners story)
FAQ
What is the best way to link survey responses to customer revenue?
Pass a customer, member or account ID in the survey invite, so every response arrives with the key. Then summarize revenue to one row per customer for a fixed window and join on that ID. Dimension Labs does the join across channels on Prove; for one survey program with clean IDs, SQL and a warehouse are enough.
What match rate should I expect?
It depends on how the survey was sent. Invites that carry an ID match far better than surveys matched on email alone. In the sample on this page, member ID as stored matched 76.3% and three keys in order matched 99.0%. Measure your own before drawing conclusions.
Can I join reviews to revenue when reviews have no customer ID?
Join them at the location level instead: reviews by store and week next to sales by store and week. How to analyze Google reviews across many locations covers that.
Should the join happen in the warehouse or in the feedback tool?
Either works if the keys are clean. Teams with a data team often join in Snowflake or BigQuery; teams without one want the feedback tool to do it and send the result back.
Export one survey file and one table of customer spend, and start with one file. See how the platform works or the plans on pricing.