Local SEO & Google Business ProfileReviews strategy for local businesses · Lesson 9 of 17
Hands-on: turning reviews into insights and operations
Video lecture
Hands-on: turning reviews into insights and operations
The narrated lecture is in production
Every chapter is scripted and ready. Browse the chapters and read the full transcript now — the video will appear here when it’s published.
Chapters
Transcript of the narration, chapter by chapter.
0:00 Reviews → insights → operations
Businesses pay research agencies to find out what customers think. Meanwhile, customers are writing it down for free, every week, in public. They're called reviews. In this lecture you'll learn to turn reviews into insights: theme them with a few spreadsheet formulas or a short Python script, optionally add AI classification with checks, and turn what you find into operational fixes that show up in future reviews.
0:29 Why it matters
Why does this matter? Because reading reviews one at a time gives you feelings, not patterns. Aggregated monthly, they show which services people praise, what frustrates them, which behaviours delight, and which locations lag. That helps operations fix causes, helps marketing use the language customers actually use, and helps local SEO, because reviews that naturally mention services reinforce relevance.
0:55 Start simple
Here's the key idea. Start simple and transparent. Think of it like sorting post into labelled pigeonholes. Each theme is a pigeonhole with a few keywords on the label. Waiting: wait, queue, late, delay, slow. Staff: friendly, rude, helpful. Price: expensive, value, cost. Parking. Cleanliness. It's crude, but anyone can understand and check it, and that's what makes people trust the results.
1:22 In Sheets
In Google Sheets, put review text in column B, and add a column per theme using REGEXMATCH on the lower case text. If the review mentions wait, queue, late, delay or slow, the waiting column shows one. Then COUNTIFS gives you, for example, negative waiting mentions: reviews with the waiting theme and a rating of three or less. And add local language keywords where your customers review in Arabic or Urdu. The lesson has the formulas ready to paste.
1:56 In Python
For bigger volumes, use the Python version with pandas. It reads a CSV with location, date, rating and text, flags each theme with a regular expression, groups by location, month, theme and whether the review was negative, and writes a monthly summary. It's about twenty lines. The output is a table you can chart: negative waiting mentions at branch two, by month, for example.
2:24 Example 1 (simple)
Worked example one, simple. A café in Manchester themes its last eighty reviews in a sheet. The results are clear: lots of positive mentions of friendly staff, but most negative reviews mention the wait on weekend mornings, and three mention confusing parking. The owner adds a second barista on weekend mornings, uploads photos of the car park entrance, and adds a parking line to the profile description and website. Simple, fast, and all driven by what customers actually said.
2:58 Example 2 (illustrative)
Worked example two, realistic and illustrative. A car service centre in Sharjah with three branches themes fourteen months of reviews. Branch B's negative waiting mentions double on Thursdays and Fridays. Branch A's reviews often praise clear explanation of costs. The owner adds a booking only express lane at Branch B on busy days, trains Branch B's advisers on Branch A's cost explanation routine, and turns we explain every cost before we start into a service description and a post.
3:32 Close the loop
Two months later, Branch B's negative waiting mentions fall, and the phrase explained everything starts appearing in its reviews. That's the loop you want: insight, owner, action, measure, and evidence in future reviews. Put it in a table. For each insight, name the owner, the action, and how you'll measure it. Without owners, insights just become interesting slides.
3:57 Adding AI classification
What about AI? Keyword themes miss nuance. Didn't have to wait at all matches wait. An LLM can classify each review into your fixed theme list with sentiment, and do it well. If you use one: keep the theme list fixed, ask for structured JSON, send only the review text, spot check a sample every month, and never let the output trigger customer facing actions automatically. Run keywords and AI side by side at first. Where they disagree is where you learn the most.
4:34 Use customer language honestly
Reviews are also a source of customer language. The phrases people use, like explained everything, same day repair, great with kids, or female doctor available, are often the words other customers search with, and the facts AI answers repeat. Use them honestly. Add them to service names and descriptions when they describe something you genuinely offer. Turn recurring complaints into FAQs about parking, waiting times or pricing. Show what customers praise in posts and photos. And use anonymised positive reviews in staff training as examples of what great looks like.
5:13 30-minute monthly routine
Here's a thirty minute monthly routine that keeps this alive. Five minutes: refresh the review export and re run the formulas or script. Five minutes: compare this month's theme counts with the three month average for each location. Five minutes: pick the top negative and top positive theme per location. Ten minutes: update the insight, owner, action and measure table. And five minutes: send a three line summary to branch managers. Small, regular and owned. That's what turns reviews from noise into improvement.
5:49 Watch me do it: theme a year of reviews
Watch me do it. I'll theme a year of reviews for a three branch car service centre in Sharjah. First, I export the reviews into a CSV with location, date, rating and text. Next, I open the Python script and look at the theme dictionary. I add two themes that matter for this business: pricing clarity, with words like quote, estimate and explained, and waiting, with wait, delay and slow. I also add a few Arabic keywords, because about a third of reviews are in Arabic. Then I run it. It flags each review, groups by branch, month and theme, and splits positive and negative mentions. I open the output in a sheet and build a pivot table. Branch B's negative waiting mentions jump on Thursdays and Fridays. Branch A has lots of positive pricing clarity mentions. Now I spot check twenty reviews tagged waiting, because keywords miss context. Three say things like no waiting at all, which I note. The pattern still holds. Then I fill the action table: insight, owner, action, measure. Branch B manager adds a booking only express lane on busy days. Branch A's service advisers train Branch B on how they explain quotes. Finally, I set the measure: negative waiting mentions at Branch B over the next two months.
7:22 Common mistakes
Common mistakes. Reading reviews one by one and never aggregating. Treating keyword counts as precise sentiment analysis. Sending customer personal data to AI tools unnecessarily. And collecting insights without assigning owners and measures. Also remember where reviews come from: collect only what you need, the text, rating, date and location, and follow your privacy notice.
7:46 Recap
Recap. Reviews are free customer research. Theme them with a transparent keyword dictionary in Sheets or Python, add AI classification carefully if you need nuance, and close the loop with owners, actions and measures you can see in future reviews. Try this now. Take your last fifty reviews, add four theme columns in a sheet, and find the one negative theme you could fix this month.
Reviews are free customer research
Every review is a customer telling you, unprompted, what mattered. Aggregated monthly, reviews show which services people praise, what frustrates them, which staff behaviours delight, and which locations lag. That feeds operations (fix the causes), marketing (use the language customers use) and local SEO (reviews that naturally mention services reinforce relevance).
Step 1: Export or collect reviews
Options depend on your setup: copy recent reviews manually for a single location; use a review management platform's export; or, for developers with approved access, the Google Business Profile APIs. Keep only what you need — review text, rating, date, location — and follow your privacy notice.
Step 2: Theme reviews with a simple, transparent method
Start with a keyword-based theme dictionary you can explain to anyone. In Google Sheets, with review text in column B:
Theme: waiting =IF(REGEXMATCH(LOWER(B2), "wait|queue|late|delay|slow"), 1, 0)
Theme: staff =IF(REGEXMATCH(LOWER(B2), "friendly|rude|helpful|staff|team"), 1, 0)
Theme: price =IF(REGEXMATCH(LOWER(B2), "price|expensive|cheap|value|cost"), 1, 0)
Theme: parking =IF(REGEXMATCH(LOWER(B2), "parking|park"), 1, 0)Summarise per theme and rating band with COUNTIFS, for example negative waiting mentions: =COUNTIFS(Themes!C:C, 1, Reviews!C:C, "<=3"). Add Arabic and Urdu keywords where your customers review in those languages (for example انتظار for waiting in Arabic).
Step 3: Python for larger volumes
import re
import pandas as pd
THEMES = {
"waiting": r"wait|queue|late|delay|slow",
"staff": r"friendly|rude|helpful|staff|team",
"price": r"price|expensive|cheap|value|cost",
"cleanliness": r"clean|dirty|hygien",
}
df = pd.read_csv("reviews.csv", parse_dates=["date"]) # columns: location,date,rating,text
df["text_l"] = df["text"].fillna("").str.lower()
for theme, pattern in THEMES.items():
df[theme] = df["text_l"].str.contains(pattern, regex=True)
df["month"] = df["date"].dt.to_period("M")
df["negative"] = df["rating"] <= 3
summary = (df.melt(id_vars=["location", "month", "negative"], value_vars=list(THEMES),
var_name="theme", value_name="mentioned")
.query("mentioned")
.groupby(["location", "month", "theme", "negative"])
.size().unstack("negative", fill_value=0)
.rename(columns={False: "positive_mentions", True: "negative_mentions"}))
print(summary.tail(20))
summary.to_csv("review_themes_by_month.csv")Step 4: Optional LLM classification — with checks
Keyword themes miss nuance ("didn't have to wait at all" matches wait). An LLM can classify each review into your fixed theme list with sentiment. If you use one, keep the theme list fixed, ask for JSON output, send only the review text, spot-check a sample every month, and never let the model's output trigger customer-facing actions automatically. Keyword and LLM results can run side by side; where they disagree is where you learn most.
Step 5: Turn insights into operations
| Insight | Owner | Action | Measure |
|---|---|---|---|
| Negative "waiting" mentions rising at Branch 2 on weekends | Branch manager | Add weekend staff / booking slots | Negative waiting mentions next 2 months |
| Customers praise "same-day repair" | Marketing | Add as a service + feature in posts and website | Clicks on service page; review mentions |
| Parking confusion | Operations + web | Parking photos, attribute, FAQ | Parking complaints; AI answer accuracy |
Step 6: Feed insights back into local SEO and marketing
Reviews are also a source of customer language. The phrases people use ("explained everything", "same-day repair", "great with kids", "female doctor available") are often the words other customers search with and the facts AI answers repeat. Use them — honestly — in:
- Service names and descriptions on the profile and website, when they describe something you genuinely offer.
- FAQs that answer the questions behind recurring complaints (parking, waiting times, pricing clarity).
- Posts and photos that show what customers praise (a photo of the children's treatment room, the express lane).
- Staff training, using anonymised positive reviews as examples of what "great" looks like.
Never copy review text into your own marketing in a way that misrepresents it, never edit what a customer said, and follow advertising rules if you quote reviews in ads (quote accurately and don't cherry-pick in a misleading way).
A monthly review-insights routine (30 minutes)
1. Refresh the review export and re-run the theme formulas or script (5 min)
2. Compare this month's theme counts with the 3-month average per location (5 min)
3. Pick the top negative theme and top positive theme per location (5 min)
4. Update the insight → owner → action → measure table (10 min)
5. Share a 3-line summary with branch managers (5 min)Worked example (illustrative)
A three-branch car service centre in Sharjah themes 14 months of reviews. Branch B's negative "waiting" mentions double on Thursdays and Fridays; Branch A's reviews often praise "clear explanation of costs". The owner adds a booking-only express lane at Branch B on busy days, trains Branch B's advisors on Branch A's cost-explanation routine, and turns "we explain every cost before we start" into a service description and a post. Two months later Branch B's negative waiting mentions fall, and the phrase "explained everything" begins appearing in its reviews.
Common mistakes
- Reading reviews one at a time and never aggregating.
- Treating keyword counts as precise sentiment analysis.
- Sending customer personal data to AI tools unnecessarily.
- Collecting insights without assigning owners and measures.
Key takeaways
- Aggregated reviews are free customer research for operations, marketing and local relevance.
- Start with a transparent keyword theme dictionary in Sheets or pandas, including local-language keywords.
- LLM classification can add nuance if themes are fixed, output is structured and samples are checked.
- Every insight needs an owner, an action and a measure — ideally visible in future reviews.
Check your understanding
Quick questions to lock in the lesson. They don’t count towards your certificate.
Put it into practice
Collect 50–100 recent reviews for a business, theme them with the Sheets formulas or the Python script, and write three insight → owner → action → measure rows.
Enrol for free to save your progress
Reading is always free. Enrol to keep your place, take the final assessment and earn a verifiable certificate.