Analyze app store reviews in Google Sheets
=HUNCHPICK(text, options) reads an app store review and returns bug report, feature request, praise, or pricing complaint. =HUNCHSCORE(text, question, levels) then rates how severe the problem is, none to critical, so the worst bugs sort to the top of the sheet instead of the top of the app store page.
A star rating alone does not triage anything
Two one-star reviews on the same day can be a data-losing crash and a complaint about a price increase, and they need to reach two different people by two different deadlines. Filtering by star rating gives you both in one pile; a review export with no category column leaves someone reading every row to sort that out by hand.
Category
=HUNCHPICK(A2:A500, "bug report: something is broken or crashes|feature request: wants something new or different|praise: happy, nothing needed|pricing complaint: cost, billing, or refund issue|other")In Excel: =HUNCH.PICK(A2:A500, "bug report: something is broken or crashes|feature request: wants something new or different|praise: happy, nothing needed|pricing complaint: cost, billing, or refund issue|other")
pricing complaint is described as a cost, billing, or refund issue so it does not swallow every review that mentions a broken payment screen, that stays a bug report unless the review is about the price itself. Full syntax is on the HUNCHPICK reference.
Severity
=HUNCHSCORE(A2:A500, "How severe is the problem described in this review, if any?", "none|minor|serious|critical")In Excel: =HUNCH.SCORE(A2:A500, "How severe is the problem described in this review, if any?", "none|minor|serious|critical")
No fourth argument here, so this returns the weighted position, 0 (none) to 3 (critical), which is what you sort a triage list on. Add "label" as a fourth argument for a second column with the word instead of the number, for a roster a human reads. Details are on the HUNCHSCORE reference.
Real outputs
Eight reviews pulled from an app store export, category, severity number, and severity label side by side.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Review | Category | Severity | Severity label |
| 2 | Crashes every time I try to upload a photo. Lost an hour of work. Please fix. | bug report | 2.49 | serious |
| 3 | Would be great if you added dark mode. | feature request | 0.32 | none |
| 4 | Been using this for 2 years, never had an issue. Love it. | praise | 0.00 | none |
| 5 | Subscription price doubled overnight with no warning. Cancelling. | pricing complaint | 2.11 | serious |
| 6 | App freezes on the checkout screen, could not complete my order. | bug report | 2.54 | critical |
| 7 | Great app, wish there was a way to export to Excel. | feature request | 0.89 | minor |
| 8 | 5 stars, does exactly what it says. | praise | 0.00 | none |
| 9 | Charged twice for the premium upgrade, still waiting on a refund after a week. | pricing complaint | 1.94 | serious |
Pricing complaints score as severe as bugs here: the doubled subscription price comes back 2.11, serious, and the double charge 1.94, both close to the checkout-freeze bug at 2.54, critical, because losing money without warning reads as seriously as losing an hour of work. The two feature requests split further apart than their shared category suggests, dark mode at 0.32 and Excel export at 0.89, both none to minor, but neither zero: a request phrased as a real want still registers above a review with no problem in it at all.
Reviews that mix two things
Six reviews where praise, a bug, and a feature request show up in the same three sentences.
| Row | A | B | C |
|---|---|---|---|
| 1 | Review | Category | Severity |
| 2 | Love the design but it crashed twice this week and I lost my draft both times. | bug report | 2.29 |
| 3 | This used to be free, now you want $10 a month for basic features, ridiculous. | pricing complaint | 1.20 |
| 4 | Please add a widget for the home screen. | feature request | 0.12 |
| 5 | Will not open since the last update, stuck on the splash screen. | bug report | 2.90 |
| 6 | It is fine. | praise | 0.00 |
| 7 | Been asking for CSV export for two years, still nothing, but otherwise a solid app. | feature request | 1.72 |
The unusable app, stuck on the splash screen since an update, is the most severe review across both grids, 2.9 out of a possible 3, ahead of the checkout-freeze bug above. The one worth a second look is the two-year feature request: 1.72, closer to serious than minor, well above the one-line widget request at 0.12. Nothing is technically broken in either review, but the severity question asks how much of a problem this is, and a request repeated for two years with no answer reads as a bigger problem than a first-time ask, a fair reading of the word severe even outside a bug.
Triage this sprint
=SORT(FILTER(A2:D500, B2:B500="bug report"), 3, FALSE)=COUNTIFS(B2:B500, "pricing complaint")The first pulls every bug report, most severe first, using the numeric column so the order is correct; sorting on the label column instead would put them in alphabetical order, which is not the same thing. The second is a one-line count for the billing team.
=COUNTIFS(B2:B500, "feature request")Feature requests rarely need a severity sort, a plain count by week tells you whether the same request keeps coming back, which is the signal worth taking to a roadmap review.
Check that the none severity bucket lands mostly on praise reviews. If serious problems keep landing there too, tighten the severity question to mention "only if something is wrong with the app".
Limits
- It does not read the star rating or the app version mentioned in your export. Keep both as their own columns and combine them with FILTER once the category and severity columns exist.
- A short review like "it is fine" gives every column a low-signal read. That is an accurate reflection of a low-signal review, not an error to fix.
- It does not know your changelog. "Still broken after the update" needs the update date in its own column if you want to check a wave of reviews against a specific release.
- It reads one review at a time, with no count of how many other users said the same thing. A single review calling a bug critical and fifty reviews calling the same bug minor both start from the same severity question; the count across rows is what tells you which read to trust.
Copy the template to try both formulas on your own review export. The review tagging guide covers a similar topic-and-sentiment pattern for product reviews generally, and the support ticket triage guide uses the same category-plus-severity shape on a helpdesk export.
Questions
Does it read the star rating?
Does this work for both the App Store and Google Play?
Can severity come back high on a review marked praise?
How do I get the worst bugs to the top of the sheet?
Should I reply to reviews from inside the sheet?
Run it on your own column
Copy the template (or use the Excel add-in), paste your key, point the formula at your data. Every formula here has its Excel spelling underneath. 100 rows free to start, then $29 for 5,000 rows. Credits never expire and there is no subscription.
Reference: HUNCHPICK · HUNCHSCORE
More guides
- Categorize support tickets in a spreadsheet Ticket triage
- Tag customer reviews by topic in Google Sheets Review tagging
- AI formulas for Google Sheets: what returns a number, what returns text AI formulas compared
- Classify job titles in Google Sheets Job titles
- Sentiment analysis in Google Sheets with one formula Sentiment