Weekly Customer Feedback Digest

Setup and handoffs

Complete these in order. Availability reflects the reviewed setup; provider plans can change.

1. 1Google SheetsFree tier

   Collect and clean the week's feedback

   Paste every comment into one sheet, give each a stable ID and strip names, emails and order numbers before anything reaches an AI tool.

   Open Google Sheets

   ↓ Handoff in

   Exports from your form, review site and support inbox for the last seven days.

   Exact handoff out ↓

   One table: `comment_id | date | channel | comment text`. IDs (C001, C002...) never change in later steps.

   Numbered setup

   1. 1.1Create a sheet with the four columns above.
   2. 1.2Paste each source's export below the last row.
   3. 1.3Number comments C001, C002... in order.
   4. 1.4Search for @, phone numbers and names and remove them.
   5. 1.5Delete exact duplicates and empty rows.

   Human approval point

   The owner confirms no personal data remains before copying the table.

   If this connection fails

   • Exports use different date formats: convert all to YYYY-MM-DD before numbering.
   • One comment covers several issues: keep it whole; the coding step can assign two themes.
2. 2ChatGPTFree tier

   Code every comment against a fixed theme list

   Give the assistant your theme list and the table. It must assign themes, not invent them, and flag comments that fit nothing.

   Open ChatGPT

   ↓ Handoff in

   The cleaned table plus your theme list (start with 6–10 themes; keep it the same every week).

   Exact handoff out ↓

   A table: `comment_id | theme | second theme | sentiment | short reason`. Every input ID appears exactly once.

   Numbered setup

   1. 2.1Write your theme list once and save it next to the sheet.
   2. 2.2Open a new chat and paste the prompt below.
   3. 2.3Paste the theme list and the whole table.
   4. 2.4Check that the row count matches your sheet.
   5. 2.5Paste the result back into a new tab.

   Complete AI prompt

   You are coding customer feedback for a weekly digest.
   
   RULES
   1. Use ONLY the themes in THEME LIST. Never create a new theme.
   2. If a comment fits no theme, use the theme UNMATCHED and explain why in the reason.
   3. Every comment_id in the input must appear exactly once in the output. Do not merge, skip or renumber rows.
   4. Sentiment is one of: negative, mixed, neutral, positive.
   5. The reason is at most 12 words and must quote or paraphrase the comment, never guess intent.
   
   OUTPUT a table only:
   comment_id | theme | second theme (or -) | sentiment | reason
   
   Then one line: "Rows in: X · Rows out: X · Unmatched: Y".
   
   THEME LIST:
   [PASTE THEMES]
   
   FEEDBACK TABLE:
   [PASTE comment_id | date | channel | comment text]

   Open in ChatGPT

   Copy ChatGPT prompt

   Human approval point

   The owner spot-checks ten random rows and every UNMATCHED row before counting.

   If this connection fails

   • Row counts differ: rerun with fewer comments per batch (about 150).
   • Many UNMATCHED rows: add a theme to the list for next week, not mid-week, so counts stay comparable.
3. 3Google SheetsFree tier

   Count themes and write the digest

   Count themes with a pivot table, compare with last week, then draft the digest from the counts and the coded comments.

   Open Google Sheets

   ↓ Handoff in

   Coded table, last week's counts and the original comments.

   Exact handoff out ↓

   A digest of under 300 words: top themes with counts and change, three quoted comments per top theme with IDs, and one proposed action each.

   Numbered setup

   1. 3.1Insert a pivot table: rows = theme, values = count of comment_id.
   2. 3.2Add last week's counts in the next column and a change column.
   3. 3.3Pick three representative comments per top-3 theme by ID.
   4. 3.4Write the digest, quoting comments exactly.
   5. 3.5Share with the team after approval.

   Human approval point

   The owner checks each quote against the original row and approves proposed actions before sharing.

   If this connection fails

   • A theme jumps suddenly: check whether one channel exported twice.
   • Quotes do not match: copy them from the original sheet, never from the AI output.

Test safely before real data

1. 1Write 20 invented comments with known themes, including two that fit nothing.
2. 2Run the coding prompt and check every row against your expected theme.
3. 3Confirm the row counts match and UNMATCHED comments are flagged.
4. 4Only then use a real week of feedback.