customer_review_aggregation v1.0.0

name: customer_review_aggregation description: Pull reviews from every platform into one coded view: normalize exports from Google, Yelp, Trustpilot, app stores, and other sites into one schema, code themes with a checked codebook, compute rating trends over time, rank the complaints worth fixing first, and write a monthly review report a team can act on. Triggers on aggregating reviews, review reporting, rating trend analysis, coding review themes, or "what are customers complaining about" across platforms.

Customer Review Aggregation

Purpose

One product gets reviewed in many places, and each platform shows only its own slice. This skill merges those slices into one dataset, codes what reviewers are actually talking about, tracks how ratings move over time, and turns the result into a short monthly report with a fix first list. It is platform neutral: any source that can be exported, copied, or pulled through an API into a CSV can join the view.

Workflow

1. Inventory the sources. List every platform where the business or product is reviewed: Google Business Profile, Yelp, Trustpilot, app stores, Amazon, Facebook, industry sites, and the company support inbox if it holds review style feedback. For each one record how the data comes out (export, API, manual copy), the date range it covers, and whether ratings, text, dates, and owner responses are included. Record the pull date. Platform dates are taken as given: some platforms update a review date when the reviewer edits, so a pull is a snapshot, not a permanent record.

1. Normalize into one schema. Convert every source into one CSV with these columns: platform, review_id, date (YYYY-MM-DD), rating, rating_scale, title, text, segment (location or product, if the business has more than one), verified, responded. Keep the review text verbatim. Never edit, trim for tone, or correct a review in the dataset: the text is evidence, and a report built on edited quotes will not survive a spot check. Keep the original files untouched beside the normalized one.

1. Validate and reconcile. Count rows in, rows kept, and rows dropped, with a reason per drop: bad or missing rating, missing platform or date, exact duplicate. A duplicate is the same platform and review_id, or the same platform, date, and text when no id exists. Flag suspected cross posts: identical text on more than one platform. One person posting the same review in three places is one opinion with three listings. Report both totals, all reviews and the total excluding suspected cross posts, and say which total each decision uses.

1. Build or refresh the codebook. Adapt the thematic analysis method of Braun and Clarke to review text: read a sample closely, draft codes from what reviewers actually say, group codes into themes, review each theme against the coded extracts, then write a definition for every theme. Each codebook row needs a theme name, a definition, include when and exclude when rules, an example, keywords for first pass coding, exclusion keywords, and a severity weight from 1 to 3 that reflects harm when the thing goes wrong (safety, money lost, or total failure rates 3). Start from references/codebook-starter.csv and cut or add themes until it fits the business. A theme with no exclusion rule will swallow its neighbours, so write the boundary against the closest theme first.

1. Code the reviews. Run scripts/review_aggregate.py for the keyword first pass, then hand check the coded sample. Coding rules:

• A review can carry several themes. Count theme mentions per review once per theme.
• Theme percentages use coded reviews as the denominator, and the report says so. A review with three themes is in three denominators.
• An uncoded review goes to other only after a human reads it. A growing other pile means the codebook is missing a theme.
• Spot check at least 30 coded reviews per run against the codebook definitions. Fix keyword and exclusion lists when the check finds systematic misses, then rerun.

1. Check coding quality when a second coder exists. Have the second coder independently code a shared sample of 30 to 50 reviews with one primary theme each. Run python3 scripts/review_aggregate.py kappa coder_a.csv coder_b.csv. It prints percent agreement, Cohen kappa, and every disagreement. Treat kappa at or above 0.70 and agreement at or above 80 percent as the internal quality gate: these are team targets, not a law of the method. Below the gate, the disagreements are the agenda: read them, sharpen the definitions that caused them, and recode before reporting.

1. Compute trends. The script reports, overall and by month, platform, and segment: count, weighted mean, the full star distribution, negative share (2 stars or below on a 5 point equivalent), positive share (4 or above), change vs prior period, and response coverage. Read references/trend-worksheet.md before interpreting. Three rules do most of the work:

• Never average platform averages. Pool the reviews and divide by the total count.
• Never report a mean without its n and distribution. A 3.0 built from half 1 star and half 5 star reviews is a split audience, not a mediocre product.
• Check the mix before calling a pooled move real. If a low rated platform grew its share of volume, the pooled mean falls even when every platform held steady.

1. Rank what to fix first. The script scores each theme: priority score equals negative mentions times the severity weight, plus a rising flag when the theme negative share grew by 5 or more percentage points between the last two periods. Work the list in score order, then apply judgement the score cannot hold: a theme with a small count but a safety or money harm outranks a bigger comfort complaint, and a theme the team can fix this month outranks one waiting on a supplier. Every item on the final list names the problem, the numbers behind it, a proposed fix, an owner, and a recheck date with the number that should move.

1. Write the monthly report. Use references/monthly-report-template.md. Keep it to what changed, what it means, and what gets fixed, with short verbatim quotes as evidence for the top themes, each tagged with platform and date. Praise gets one short section: the themes where positive reviews cluster are practices worth protecting while the fixes land. End with the method note: pull date, sources, date range, codebook version, coder, double coding result if any, and which total the numbers use.

1. Keep the cadence honest. Pull on the same day each month, keep the codebook under version control, and never change a theme definition mid trend without noting the break in the report. When a definition changes, old and new numbers are not comparable, and the report must say which months used which version.

Worked example

assets/sample_reviews.csv holds 45 synthetic rows across Google, Yelp, Trustpilot, and the App Store for July to September 2026, including one exact duplicate, one suspected cross post, and one row with no rating. Run:

python3 scripts/review_aggregate.py aggregate assets/sample_reviews.csv --codebook references/codebook-starter.csv --out assets/sample-output

Verified output, also saved in assets/sample-output/: 43 reviews analysed, 42 excluding the suspected cross post, 2 dropped (1 exact duplicate, 1 bad rating). Overall mean 3.05 with negative share 44.2 percent. September mean 2.86, down 0.21 from August, with negative share up to 50.0 percent. Support ranks fix first (8 mentions, all negative, severity 2, priority 16), then quality (4 negative mentions at severity 3, priority 12), then price (priority 8, rising flag set). Response coverage 62.8 percent. Kappa check on the bundled pair assets/coder_a.csv and assets/coder_b.csv prints 87.5 percent agreement and kappa 0.84 with one disagreement listed (k2, support vs quality).

Output Contract

One run produces:

• The normalized reviews CSV, with the source files kept untouched
• A validation count: rows in, rows kept, rows dropped by reason, suspected cross post groups
• summary.json and report.md from the script: overall, by month, by platform, and by segment stats with counts beside every mean
• The theme table in fix first order with priority scores and rising flags
• The monthly report following the template, with a method note and an appendix of unanswered negative reviews

Operating Rules

• Ratings from prompted in app requests are not the same population as unsolicited site reviews. Apple limits its system rating prompt to three showings per app in a 365 day period, and prompt timing shapes who answers. Segment app store ratings apart before comparing them with open review sites.
• Compliance boundaries under the US FTC rule on consumer reviews and testimonials: never create, buy, or sell fake reviews, including reviews from people who did not have the experience; never condition an incentive on a review expressing a particular sentiment; disclose insider relationships clearly when staff or owners review; never present a company controlled site as independent; never suppress negative reviews or threaten reviewers to get one removed. Aggregation is read only work: this skill never posts, edits, flags, or replies to a review on any platform.
• Reviewer names and personal details stay out of reports. Quote the text a decision needs, tag it with platform and date, and leave the person out of it.
• A platform average copied from a public page is a claim about a snapshot. Prefer the pulled data, print the n your number came from, and date every figure.
• When volume is thin, say so and pool periods instead of ranking on noise. A month with 6 reviews supports a direction, not a verdict.