Export a month of survey comments to a sheet, strip out names, and have an AI label each comment with a theme from a fixed list and a severity score. Let the spreadsheet count the labels and rank themes by mentions, severity and effort, then give the top four or five fixes to named people with dates.
The split matters. Chat assistants read comments well and count them badly, so the AI labels and the sheet tallies. A fixed theme list keeps this month's counts comparable with next month's, which is how you find out whether a fix worked.
Start from the comments, not the average score
A score says how guests felt. The comments say why. Export one row per response with a response number, the stay month, nights, room type, the score if you collect one, and the written answers in their own columns. Then spend five minutes cleaning: delete name, email and booking-reference columns, search the comments for names and phone numbers, replace any staff name with [staff], remove blank answers and add a comment ID (C001, C002 and so on).
Last month a 34-room hotel's post-stay survey drew 96 written comments (every figure here is illustrative). Of those, 58 contained at least one complaint or suggestion and 38 were pure praise. If you are still writing the survey, two open questions do more than ten tick-boxes: "What is the one thing we should fix?" and "What should we never change?". Everything below runs on those two answers.
Comments are personal data. Use a business plan, or a personal plan with model training switched off, and delete the export when the month's analysis is done.
The quality of the comments depends on who gets asked and when. Send the survey to every guest within a day of checkout, while the stay is fresh, with one reminder at most. Do not filter the list to guests you think were happy: it flatters the results, and for review invitations it is against platform rules, as Google's policy on selectively soliciting positive reviews makes clear. Make the survey one screen on a phone, with the two open questions first and the score optional, and add an optional tick-box for guests who would like to be contacted about what they wrote.
A fixed theme list stops the labels drifting
Left alone, an assistant invents labels. This month's "Noise" becomes next month's "Sleep disturbance", and the counts can no longer be compared. Give it a closed list of 15 to 20 themes that follow the stay in order: arrival, room, bathroom, sleep, food, staff, facilities, value. Split a theme whenever its fixes differ, so breakfast food and breakfast queues are separate, as are room and bathroom cleanliness. Keep "Other" for single oddities and read it each quarter for a theme that deserves its own line.
| Theme | What counts | What does not |
|---|---|---|
| Hot water and shower | Temperature, pressure, slow drains | Cleanliness of the bathroom |
| Check-in and room readiness | Waits, a room not ready, the wrong room type | How staff spoke to the guest |
| Breakfast food | Quality, choice, dietary options | Queues and waiting |
| Breakfast queue and service | Waits, empty trays, too few staff | Taste or choice |
| Cleanliness (room) | Dust, bedding, bins, smells | Anything in the bathroom |
| Cleanliness (bathroom) | Hair, limescale, towels, grout | Water temperature |
| Wi-Fi and TV | Drops, speed, channels, remotes | Phone signal |
| Staff manner | Warmth, rudeness, helpfulness | Speed of check-in (that is readiness) |
| Value for money | Price set against what was received | A single disliked item |
Keep the same list for survey comments and, later, for public reviews, so the two can be added together.
The labelling prompt, and the rows that came back
You are coding guest survey comments for a 34-room hotel. Use ONLY the themes in
this list. Never invent a theme. If nothing fits, use Other.
THEMES: Noise; Cleanliness (room); Cleanliness (bathroom); Bed and pillows;
Heating and air control; Hot water and shower; Wi-Fi and TV; Check-in and room
readiness; Check-out and billing; Breakfast food; Breakfast queue and service;
Staff manner; Parking; Location; Value for money; Facilities; Room size and
layout; Other.
For EACH theme a comment mentions, return one row:
comment_id | theme | sentiment | severity | quote
Sentiment is Positive or Negative only. Severity applies to Negative rows:
1 = a minor annoyance, 2 = spoiled part of the stay, 3 = would stop the guest
returning, or a safety issue. Leave severity blank for Positive rows.
The quote is the shortest phrase that supports the label (12 words at most),
copied exactly.
Read sarcasm carefully: "lovely to be woken by drilling at 7am" is negative.
If you are unsure of a label, add ? after the theme.
Return the table only, with no commentary.
COMMENTS:
C001: ...
C002: ...Paste 40 to 50 comments at a time rather than all 96; long lists tend to produce sloppier labels, so check that on your own data. Asking for one row per theme matters, because it turns a comment with two points into two countable rows. This is a slice of what came back, with my check beside each row (illustrative):
| ID | Theme | Sentiment | Severity | Quote | My check |
|---|---|---|---|---|---|
| C014 | Check-in and room readiness | Negative | 2 | waited 40 minutes in the lobby | Correct |
| C014 | Staff manner | Positive | lovely staff | Correct | |
| C031 | Hot water and shower | Negative | 3 | no hot water two mornings running | Correct |
| C040 | Noise | Positive | super quiet hotel | Wrong. The comment reads "Great, a hairdryer-loud neighbour at 6am. Super quiet hotel!" It is Negative, severity 2. | |
| C047 | Atmosphere | Positive | lovely atmosphere | Wrong. Not on the list, so change it to Other. | |
| C052 | Bed and pillows | Negative | 1 | pillows too flat | Incomplete. The guest also wrote "the room was freezing", so add Heating and air control, Negative, 2. |
Three rows in six needed attention, which is a normal first pass. The sarcasm error would have counted a complaint as praise, the invented theme would have split a future count, and the missing second theme would have understated a real problem. Spot-check 20 labelled rows every month. If more than three of the 20 are wrong, tighten the definitions in the theme table and rerun the batch before you count anything.
Let the sheet count, not the chat
Paste the labelled rows into a tab called Labels, with the five columns in A to E. On a second tab called Tally, list the themes down column A and let formulas do the counting. A pivot table does the same job without formulas: theme in the rows, count of comment ID in the values, sentiment filtered to Negative. If you would rather not write either, ask an assistant to produce them for your own column letters, or see the ChatGPT for Excel guide for how.
Negative mentions B2: =COUNTIFS(Labels!B:B, A2, Labels!C:C, "Negative")
Severity points C2: =SUMIFS(Labels!D:D, Labels!B:B, A2, Labels!C:C, "Negative")
Average severity D2: =IFERROR(C2/B2, 0)
Effort (1 to 3) E2: entered by the owner, never by the AI
Priority score F2: =C2/E2One reconciliation check catches most typos: the Negative mentions column should add up to the number of Negative rows in Labels. If it falls short, a theme was spelled differently somewhere, and the missing rows are hiding from your count.
The month's tally, and why the loudest complaint ranks fifth
Here is the whole month, sorted by priority score. Each negative mention scores 1, 2 or 3 severity points, and the score is the points divided by effort. (That is the same as mentions multiplied by average severity, divided by effort.) The owner enters the effort column on a three-level scale: 1 is a staff decision or a small purchase that can happen within a week, 2 is a trade call-out or a spend of a few hundred dollars within a month, and 3 is building work, new equipment or anything longer than a month. The score is a ranking aid and nothing more, so adjust it if it surprises you for a good reason.
| Rank | Theme | Mentions | Points | Average | Effort | Score |
|---|---|---|---|---|---|---|
| 1 | Hot water and shower | 9 | 25 | 2.8 | 1 | 25.0 |
| 2 | Breakfast queue and service | 12 | 19 | 1.6 | 1 | 19.0 |
| 3 | Check-in and room readiness | 8 | 16 | 2.0 | 1 | 16.0 |
| 4 | Cleanliness (bathroom) | 6 | 15 | 2.5 | 1 | 15.0 |
| 5 | Noise | 14 | 29 | 2.1 | 3 | 9.7 |
| 6 | Wi-Fi and TV | 9 | 16 | 1.8 | 2 | 8.0 |
| 7= | Heating and air control | 5 | 9 | 1.8 | 2 | 4.5 |
| 7= | Bed and pillows | 6 | 9 | 1.5 | 2 | 4.5 |
| 9 | Value for money | 4 | 6 | 1.5 | 3 | 2.0 |
| 10 | Parking | 4 | 5 | 1.3 | 3 | 1.7 |
| Other (single mentions) | 5 | not scored | ||||
| All negative mentions | 82 |
By raw count the order would be noise, breakfast queues, then hot water and Wi-Fi level, and noise would have taken the first slot on the fix list. Scored, it falls to fifth, because mending it means building work or glazing, while a cold shower is a plumber's visit that guests rated close to a stay-spoiler (average severity 2.8). Two rules sit on top of the arithmetic. A safety issue skips the ranking and goes to maintenance the same day. And any theme with an average severity of 2.5 or more gets a look even if it ranks low; bathroom cleanliness, with only six mentions, would have qualified on that rule alone.
The one-page fix list: owners, dates and a measure
A ranked table does nothing until someone is named. The owner of the hotel, or the general manager, takes the top of the ranking into a ten-minute meeting and writes four to five fixes in this form (illustrative):
| Fix | Owner | Due | Cost | How we will know |
|---|---|---|---|---|
| Replace the mixing valve on the second floor and reset the boiler timer | Maintenance lead | This Friday | One call-out | Hot-water mentions fall next month |
| Open breakfast at 07:00, not 07:30, and add a second toaster | Restaurant manager | Next Monday | A small purchase | Breakfast queue mentions fall |
| Housekeeping tells the desk which rooms are ready by 11:00 and works arrivals first | Head housekeeper | Two weeks | None | Room-readiness mentions fall |
| Add a bathroom line to the room inspection sheet | Head housekeeper | One week | None | Bathroom mentions fall |
"Improve breakfast" is not a fix; "open at 07:00 and add a second toaster" is. Everything else goes in a parked list with a review date. The third row has its own method: allocating rooms by arrival time shows how to order the day so the early arrivals get clean rooms first. The first row belongs in a request log with an owner and a chase date, which is what logging, prioritising and chasing maintenance requests sets up.
Four things a finding can turn into
Not every ranked theme is a repair job, and treating them all as one produces a fix list nobody finishes. Sort each finding into one of four buckets before it reaches the list.
| Bucket | The test | This month's examples | What goes on the list |
|---|---|---|---|
| Fix now | Effort 1 and near the top of the ranking | Hot water, breakfast queues, room readiness, bathroom checks | An owner, a date and a measure |
| Change the promise | Cannot be mended soon, but the surprise can be avoided | Street noise, a photo that flatters a small room | New booking-page wording, a question at booking, earplugs at the desk |
| Plan and budget | Effort 3 and enough mentions to matter | Secondary glazing for the street-facing rooms | A quote, a decision date and a line in next year's budget |
| Log and watch | Fewer than five mentions and low severity | Parking, value for money | Keep counting. Act at five mentions in a month, or three months running. |
The five-mention trigger is a working rule for a hotel of this size, not a law. A hotel with 100 rooms collects more comments and can set it higher.
Fixes that are really expectation problems
Some top themes cannot be mended this month, or at all, and the right answer is to stop guests being surprised. Street noise is the clearest case. Its line on the fix list reads: ask light sleepers at booking whether they want a room at the back, say plainly on the booking page that street-facing rooms hear traffic until late evening, keep earplugs at the desk and get a quote for secondary glazing so the real fix has a date. A photo that flatters a small room works the same way. The copy changes, not the building, and an assistant can redraft the lines, as in ChatGPT prompts for hotel guest emails and rate copy.
Before: Quiet, comfortable rooms close to everything.
After: Rooms at the back are quiet. Rooms facing the street hear traffic until late evening, so tell us your preference when you book and we will do our best to give it.
The after version loses a few bookings from guests who wanted a promise, and it saves the hotel from the three-star comment that follows an unmet one.
Praise is data too
Run the same pivot on the Positive rows. In this month's illustration it gave staff manner 41 mentions, location 24, breakfast food 19 and room cleanliness 17. That list is what you protect: if a cost-cutting idea touches breakfast staffing, 19 mentions of the food are the cost of getting it wrong. Pass the staff praise to the team in the same week, because it is the cheapest motivation available. If you want to quote a guest on your website, add a tick-box to the survey asking whether you may, since the words belong to the guest, and quote only those who said yes.
The monthly repeat, in about 40 minutes
- Export and clean (10 minutes): new comments only, identifiers removed, comment IDs added.
- Label in batches (5 minutes): the same prompt and the same theme list as last month.
- Spot-check 20 rows (10 minutes): correct any errors and rerun if more than three are wrong.
- Tally and rank (5 minutes): paste into the sheet, check the reconciliation total, enter effort scores.
- Fix meeting (10 minutes): update last month's list, then choose this month's four or five.
Month two is where the method pays. Here is the illustrative follow-up for the six themes that mattered:
| Theme | Month 1 | Month 2 | What the comments say |
|---|---|---|---|
| Hot water and shower | 9 | 2 | Valve replaced; the two mentions are from stays before the repair |
| Breakfast queue and service | 12 | 5 | The earlier start helped; a second peak at 08:30 remains |
| Check-in and room readiness | 8 | 5 | Better on weekdays, not yet on Saturdays |
| Cleanliness (bathroom) | 6 | 4 | The inspection line is not yet used on every floor |
| Noise | 14 | 13 | Untouched; the glazing quote is pending |
| Wi-Fi and TV | 9 | 9 | Not on this month's list |
One good month is not proof. Close an item only after two clean months in a row, and read the quotes beside the numbers, because the quotes explain what to do about the second breakfast peak. The stays also matter: comments about the cold showers in month two came from guests who stayed before the repair, so they do not count against it.
Severity 3 comments should not wait for the monthly meeting
Once a week, filter the Labels tab to severity 3 and hand those rows to the duty manager. A guest who says they would not return, or who describes something unsafe, is a recovery chance with a clock on it, and a monthly meeting is too slow. In the illustrative month, 12 of the 82 negative mentions carried severity 3: seven about hot water, three about bathroom cleanliness and two about noise. That is three comments a week for one person to read.
You can only reply if the survey is not anonymous, which is the argument for the optional contact tick-box. When a reply is possible, treat it as any other complaint, with the same ladder and the same limits. When it is not possible, the comment still counts in the tally, and the fix it points to is the only reply the guest will get.
Public reviews can join the same sheet as a second source. Add a column for where each comment came from, paste review text under the same theme list, and read the two sources side by side. Reviews can skew towards the extremes, so a theme that shows up in both sources is the one to fix first. Check the terms of each review site before you copy text in bulk, and keep the reviewer's name out of the sheet.
A one-page note for the owner, written from the tally
Once the sheet has counted, an assistant is useful again for the short note that goes to the owner or general manager. Give it the two tables, not the comments, and keep its brief tight.
Here are this month's tally and last month's, as tables. Write a one-page note
for the owner: the three fixes to prioritise and why, the praise to protect, and
what changed since last month. Use only numbers that appear in the tables. If a
theme changed by two mentions or fewer, call it flat, not a trend. Do not suggest
fixes that are not in the fix column; put any other ideas on a separate line
called "ideas to test".The note that came back was clear and mostly right, with one slip worth catching (illustrative):
Hot-water complaints fell from 9 to 2 after the valve was replaced, and breakfast queues fell from 12 to 5. Room readiness improved from 8 to 5 but remains a Saturday problem. Noise is getting worse at 13 mentions. Staff manner remains the most praised theme at 41 mentions.
Noise went from 14 to 13, which is flat, so "getting worse" is wrong, and it is exactly the sort of line an owner repeats in a meeting. The instruction about two-mention changes was in the prompt and still got ignored once. That is why the note is a draft: compare each number with the table, and correct the wording before it travels.
Where survey comments mislead you
- Only the extremes reply. The tally shows what the very happy and the very annoyed chose to write, not the average guest's opinion.
- One guest, three channels. The same complaint can arrive in the survey, a review and an email. De-duplicate by stay before counting, or one guest becomes three mentions.
- Price complaints are not always about the stay. A "value for money" comment often reflects the rate paid. Check the rate code before you cut a service to answer it.
- Small numbers. Four mentions in a month at 34 rooms is a note in the log, not a reason to redesign a lobby.
- Scores and comments can disagree. A 9 out of 10 can carry a sharp complaint inside it, so read the comments of high scorers too.
- Staff names. Keep them out of the AI, deal with criticism of a person privately, and share praise openly. A complaint about conduct belongs in the complaint process, with the approach in service-recovery scripts for guest complaints.
For a wider look at survey analysis in any business, see how to analyse customer feedback surveys with AI; the hotel-specific parts above are the theme list, the severity scale and the fix meeting.
Questions about turning survey comments into fixes
How many comments do I need before the tally means anything?
There is no magic number. For a hotel of 30 or so rooms, a month of 60 to 100 comments is enough to see repeating themes. Treat a theme with five or more mentions as worth a look and a single mention as something to log. That is a working rule, so adjust it to your volume.
Can the AI read comments in other languages?
Usually well enough to label a theme, but keep the original text beside any translation and have a fluent colleague check the comments that drive an important decision. Ask the assistant to label the comment as written and to flag any it was unsure about rather than guess.
Is it safe to paste guest comments into an AI chat?
Only after you remove names, emails, phone numbers and booking references, and only in a business plan or a personal plan with model training switched off. Comments can contain private details, so treat the export as confidential and delete it when the analysis is done.
Should I tell guests what changed?
Yes, briefly. A line in your next guest email such as 'you told us the shower ran cold, so we replaced the valve' shows the survey leads somewhere and gives future guests a reason to answer. Only claim fixes that have actually been completed.
Further reads
- Reputation Management System for Hotels: Review Tools Compared — Review tools compared, for comments that arrive on public sites.
- Hotel Email Marketing With AI: Past-Guest Segments and Subject Lines — Tell past guests what you fixed, with consent.
- Event Feedback Survey: Write It and Summarise the Answers With AI — Another survey-to-summary workflow, for events.
- AI Consulting for Hospitality: What a Hotel Should Ask Before Hiring — Twelve questions to put to a hotel AI consultant, the month-one deliverables to expect, and a sample plan with numbers you can test.
- Hotel Front Desk Scripts: Can AI Role-Play Difficult Guests? — How to turn your own guest disputes into AI role-play drills for front desk staff, with an in-character prompt, a scoring sheet and trainer notes.
- Is It OK to Edit Hotel Photos With AI? Fair Edits vs Misleading — Fixing light and clutter is fair; adding views, rooms or amenities is not. A hotel owner's test, the platform rules and a photo edit log to keep.
- Museums and AI: Turn Visitor Feedback Into Next Season's Plan — How a small museum can sort a season of visitor comments into themes with AI, check the tags, and turn the counts into five fixes for next season.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: Google's user-contributed content policy (selectively soliciting positive reviews); the theme list, scores and monthly figures are illustrative; privacy settings and plan terms should be checked on your own AI plan.