Methodology
Where the data comes from, how each number is calculated, and how to trace any finding back to the reviews behind it.
Data sources & dates
The dashboard covers 4,179 Amazon reviews written between Aug 10, 2009 and May 12, 2026. Reviews come from two Amazon exports, which were merged.
| File | Exported | Rows | Contents |
|---|---|---|---|
| Amazon_Review.xlsx | May 22, 2026 | 2,970 | Amazon review export (file 1 of 2) |
| amazon_reviews_data.csv | May 22, 2026 | 1,945 | Amazon review export (file 2 of 2) |
| Devoted_Creations_Products.xlsx | May 22, 2026 | — | Product catalogue: ASIN to product name |
| 2026 Alina Devoted Ai project (4).xlsx | May 21, 2026 | — | Product metadata: category, collection, DHA level, price |
The two exports hold 4,915 rows together. 500 of them belong to one listing that was deliberately left out (Ed Hardy Coconut Kisses 13.5oz, ASIN B009YKK78C). Not a Devoted Creations product, and its volume would skew brand-level numbers.
When each step ran
| Cleaning and merging | May 23, 2026 | Python script: merges the two review exports and attaches product metadata |
| Star-rating statistics | Aug 9, 2026 | Python script: plain arithmetic on star ratings |
| Per-product AI summaries | Aug 10, 2026 | Claude Haiku 4.5 reads every review of a product |
| Brand-level AI summary | Aug 9, 2026 | Claude Haiku 4.5 reads the per-product summaries |
| Issue counts and matrix | computed live from the reviews every time the site is built | Fixed keyword rules, no AI |
Reviews by year written
Reviews span many years, so the issue pages also show how many mentions fall in the last 24 months of data, so you can tell a live problem from an old one.
Deduplication
The original pipeline merged the two exports without a dedup step, so they were checked separately for this page. Results:
- The two exports cover different listings (29 and 30 ASINs, 0 shared) and share 0 review texts. Nothing was double-counted by the merge.
- Across all 4,415 reviews, the same reviewer posting the same text on the same day happens 5 times (6 extra copies): Kimberly Roberts posted “The video showcases the product in use.The video guides you through product setup.The video compares multiple products.T” on 2 listings (Famous Faces, Famous Faces) on May 15, 2024. L. Sanchez posted “The video showcases the product in use.The video guides you through product setup.The video compares multiple products.T” on 2 listings (Halo Beam Cream, Halo Beam Cream) on Sep 2, 2023. Sarah posted “The video showcases the product in use.The video guides you through product setup.The video compares multiple products.T” on 2 listings (Halo Beam Cream, Halo Beam Cream) on Aug 4, 2023. Ripptide808 posted “The video showcases the product in use.The video guides you through product setup.The video compares multiple products.T” on 2 listings (Halo Beam Cream, Halo Beam Cream) on Dec 5, 2023. Kendra Wolfe posted “Stinks” on 3 listings (Nantucket Nectar, Barefoot Beachwood, Coco Creamsicle) on Nov 16, 2025.
- Product pages count such a review once on each listing it was posted to. Portfolio-wide totals (the issue matrix and executive summary) count it once, which leaves 4,409 unique reviews.
- Amazon does not pool these reviews across listings: no review text longer than a sentence appears on two different ASINs.
Sentiment score
The 0–100 sentiment score on each product page is not a formula. An AI model (Claude Haiku 4.5) reads every review of a product and gives one score for how customers feel overall: 0 is extremely negative, 100 extremely positive. It is meant to pick up tone the stars miss, such as a 3★ review that is mostly happy or a 5★ review with a serious warning in it.
Because it is a model’s judgement, it was checked against something plain: the product’s average star rating. Across the 58 products the two agree closely (correlation 0.87, where 1.0 would be perfect agreement). 1 product's score is 20+ points away from what the stars alone suggest:
- Podz Eyewear Soft Tanning Goggles: score 18, stars 2.98★ (which would map to about 50).
The star-based figures everywhere else (average rating, % positive = 4–5★, % negative = 1–2★) are simple arithmetic on the star ratings, with no AI involved.
Issue detection
Issue and opportunity counts come from a fixed list of keyword rules run over the review text, with no AI involved. That makes every count reproducible and every review behind it viewable. The rules:
- Issues are only looked for in the 1,269 reviews rated 1–3★, and opportunities only in the 2,904 rated 4–5★. Without that limit, happy customers of tingle lotions (“it burns, that’s how you know it works”) were counted as skin-reaction complaints.
- A match is skipped when a negation sits right before it (“no streaks”, “didn’t burn”).
- Amazon’s video-player text, which gets captured inside some reviews, is removed before matching.
- Each review counts once per issue however many trigger words it contains. One review can count toward several issues.
- Counts cover Devoted Creations products only. The 4 third-party products tracked for comparison appear on the issue pages but are not in any total.
Accuracy check. Keyword rules can misfire, so their accuracy was measured. An independent reviewer read a random sample of 25 matched reviews per category (Oct 19, 2026) and judged whether each one really belonged. Three categories scored below 70% on the first check (price/value, fast results, cheaper than the salon). Those three, plus four whose misfires were easy to fix, were rewritten and measured again on a fresh sample. The figures below are the current rules. Price/value is the smallest category, and 13 of its 25 matches (third-party products included) were checked.
| Category | What counts | Accuracy |
|---|---|---|
| Skin reactionsIssue · 1–3★ | Mentions of a physical reaction: burning, rash, hives, itching, allergic reaction, blisters, breakouts, swelling, irritation, or a medical visit. | 96%24 of 25 correct |
| Authenticity concernsIssue · 1–3★ | Customer says the product is fake, counterfeit, tampered, used, expired, watered down, or different from the salon version. | 84%21 of 25 correct |
| Packaging / dispenserIssue · 1–3★ | Pump or dispenser failures, leaks, spills, or a cap, seal, bottle or box that arrived broken, open or missing. | 100%25 of 25 correct |
| Fragrance complaintsIssue · 1–3★ | Negative words about the smell (bad, chemical, overpowering, stinks), or a headache from it. A neutral or positive mention of scent does not count. | 96%24 of 25 correct |
| Product effectivenessIssue · 1–3★ | Customer says it didn't work: no color, no difference, did nothing, waste of money. | 84%21 of 25 correct |
| Streaking / orange / stainingIssue · 1–3★ | Streaks, an orange tone, blotchy or uneven color, or staining of skin, hands, sheets or clothes. | 92%23 of 25 correct |
| Price / size valueIssue · 1–3★ | Customer complains specifically about price or size: overpriced, too expensive, too small, smaller than expected. A general "not worth it" does not count, because it usually means the product failed. | 77%10 of 13 correct |
| Scent customers loveOpportunity · 4–5★ | 4–5★ review praising the smell. | 96%24 of 25 correct |
| Fast, dark resultsOpportunity · 4–5★ | 4–5★ review describing speed or depth of color: shades darker, results after the first session, very dark, deep tan. Generic "great tan" does not count. | 80%20 of 25 correct |
| Cheaper than the salonOpportunity · 4–5★ | 4–5★ review saying it costs less than elsewhere, usually the salon. Plain "great price" with nothing to compare against does not count. | 100%25 of 25 correct |
| Moisturizing / skin feelOpportunity · 4–5★ | 4–5★ review praising softness, hydration or a non-greasy feel. | 92%23 of 25 correct |
Read every count as approximate to that accuracy. Each issue page highlights the exact phrase that triggered every match, so any single review can be checked.
Isolated complaints vs recurring trends
For each product and issue, a finding is recurring when at least 3 separate reviews raise it, spread across at least 2 different months. Anything less is an isolated report. Requiring different months means one bad shipment or one person posting several times can’t pass as a trend.
Priority in the issue matrix follows a fixed rule as well:
- High: a safety issue (skin reactions) that is recurring on any product, or an issue raised in ≥12% of 1–3★ reviews.
- Medium: raised in ≥6% of 1–3★ reviews, or an authenticity issue that is recurring on any product, because counterfeit stock damages trust well beyond its share of complaints.
- Low: everything else.
- Opportunities: High when praised in ≥20% of 4–5★ reviews, Medium when ≥5%.
Audiences (marketing takeaways)
A review is placed in an audience when the reviewer says something that puts them there. Reviews of every star rating count, so an audience’s average rating can be compared with the brand average. One review can belong to several audiences. A product is only named as an audience’s favourite once at least 8 reviews from that audience mention it, its rating from them beats the brand average, and it has no recurring skin-reaction pattern.
- Outdoor / sun tanners: Mentions tanning outside: outdoors, in the sun, at the beach or pool.
- Tanning-bed users: Mentions a tanning bed, sunbed or indoor tanning.
- Repeat buyers: Says they buy it again, have used it for years, or are on another bottle.
- Salon customers: Mentions a tanning salon (usually comparing where they used to buy).
- Fair or sensitive skin: Describes their skin as fair, pale or sensitive.
- First-time tanners: Says it is their first time, they are new to tanning, or a beginner.
AI summaries vs counted numbers
The written findings on each product page (“What customers love”, “Customer feedback”, action items) are AI summaries. Numbers inside them, like “12+ reports”, are the model’s own estimate from reading the reviews, not a count. The issue matrix and each product page’s “Issue evidence” panel give the counted number, with a link to every review behind it.
Example: Famous Faces, skin reactions
AI summary said: “Causes skin irritation, burning, rashes, and breakouts on face and acne-prone skin”
Counted: 10 reviews out of 245 (4.1%), written between Jun 2014 and Mar 2026, so recurring.
Source ASINs
Every Amazon listing included, with how many of its reviews are in the data and the dates they cover.