Analyze customer reviews by topic

Data analyst · Time: 20 min · Research

Tools that fit this task

• ChatGPT $20/mo
• Claude $20/mo
• Gemini in Google Sheets $14/mo
• Google AI Studio Free
• Zapier $19.99/mo
• Grok $8/mo

Prompt

Role: you are a customer experience analyst.
Context: below are {{number}} customer reviews of a company that {{what it does}} for {{period}} from {{sources}}. Columns: date | source | rating | text.
Task: identify the topics mentioned in the reviews, count mentions and the share of negative ones for each, and give three verbatim quotes with row numbers. Separately, pick out reviews that describe a specific process failure and group them by stage (order, payment, delivery, support, product). Quotes must be verbatim, with the row number.
Format: a table topic | mentions | share negative | three quotes with numbers. Then a table of failures by stage. At the end, three actions with the biggest effect, justified with numbers.
Reviews:
{{reviews}}

Steps

1. Collect reviews for the period in one table: date, source, rating, text, without author names
2. Give the assistant the table with a prompt to identify topics, sentiment and the frequency of each topic
3. Get a list of topics with the number of mentions, the share of negative ones and three quotes for each
4. Check five random reviews: are the topic and sentiment identified correctly
5. Ask it separately to pick out reviews describing a specific process failure and group them by stage
6. Define three actions for the most frequent negative topics with an owner and a deadline

How to check the result

A spot check of five reviews confirmed topic and sentiment, the three main negative topics have actions with deadlines, and the analysis is repeated monthly with the same prompt

Pitfalls

• The model invents quotes that are absent from the reviews: require verbatim quotes and check them
• Reviews with customer names and contacts are anonymized before uploading

Data that stays out of public AI tools

• CRM and ERP exports with client and employee names: anonymize them before uploading to a public service
• Company financials before publication: into a service only inside a corporate environment or with placeholder names
• API keys to systems and databases: keep them in secrets, never in cells or prompts

Other tasks for data analysts

• Design a KPI dashboard for the business owner
• Analyze budget variances, actual versus plan
• Get a spreadsheet formula from a plain-language request
• Build a competitor price review
• Build a 12-week cash flow forecast
• Explain the numbers to the business owner
• Summarize a long PDF
• Build a quarterly results presentation
• Run a quick counterparty check before a deal

AI tools for data analysts · Prompt: Analyze customer reviews by topic