Amazon Reviews 2023 Electronics Reviews and Product Metadata
The Electronics snapshot from
Amazon Reviews 2023
contains 43,886,944 reviews and 1,610,012 products.
Products and reviews join by product_id.
Travel-headphone shortlist
Which travel headphones deserve a catalog review?
The request is "Wireless noise cancelling over ear headphones for long airplane flights." Eligible products cost $20 to $150 in the snapshot, have at least 100 verified reviews in 2022 and have no more than 20% low ratings among those reviews.
SQL defines the eligible products before exact nearest-neighbor search, then
joins ranked IDs to titles and review counts. The request and product vectors
were encoded before the query. Both are L2 normalized. Squared L2 distance
d converts to cosine similarity as 1 - d / 2.
WITH review_history AS (
SELECT product_id, COUNT(*) AS reviews_2022,
AVG(CASE WHEN rating <= 2 THEN 1.0 ELSE 0.0 END) AS low_rating_share
FROM reviews
WHERE review_time >= TIMESTAMP '2022-01-01'
AND review_time < TIMESTAMP '2023-01-01' AND verified_purchase
GROUP BY product_id
), eligible AS (
SELECT v.id, v.vector
FROM semantic_products_vectors v
JOIN products p ON p.product_id = v.id
JOIN review_history h ON h.product_id = v.id
WHERE v.sample_rank < 1000000
AND p.category_l3 = 'Headphones & Earbuds'
AND p.price BETWEEN 20 AND 150
AND h.reviews_2022 >= 100 AND h.low_rating_share <= 0.20
)
SELECT n.rank, p.product_id, p.title, p.price,
h.reviews_2022, h.low_rating_share,
1.0 - n.distance / 2.0 AS cosine_similarity
FROM cuvs_brute_force_knn(
dataset => (SELECT id, vector FROM eligible),
queries => (SELECT id, vector FROM intent_queries WHERE id = 0),
k => 10, metric => 'l2_expanded') n
JOIN products p ON p.product_id = n.neighbor_id
JOIN review_history h ON h.product_id = p.product_id
ORDER BY n.rank
The first two candidates expose the distinction between passing numeric filters and satisfying the request:
The query took 1.98 s.
The VELKPRO title describes office calls and an on-ear design. Its vector ranks second, but product specifications and design allow a reviewer to filter it from the travel shortlist.
A later raw-table check found 179 eligible products, and 117 had vectors in the sampled corpus. The other 62 were outside this search.
The main capture retains all ten candidates.
Which Sony listings should an editor compare?
For catalog maintenance, a separate query retrieves the ten nearest listings for each of 10,000 query products within a one-million-product corpus. An editor can then compare condition, color and accessories before treating them as alternatives.
SELECT n.query_id, q.title AS source_title, n.rank,
n.neighbor_id, p.title AS candidate_title,
1.0 - n.distance / 2.0 AS cosine_similarity
FROM cuvs_brute_force_knn(
dataset => (SELECT * FROM product_corpus_1000000),
queries => (SELECT * FROM product_queries_10000),
k => 10, metric => 'l2_expanded') n
JOIN semantic_products_text q ON q.id = n.query_id
JOIN semantic_products_text p ON p.id = n.neighbor_id
ORDER BY n.query_id, n.rank
For a midnight-blue Sony WH-1000XM4, three leading matches are:
The query took 2.39 s.
The shared model name explains why these are useful comparison candidates. Condition and bundle contents still determine the editing decision. The SQL returns 100,000 matches with their titles already joined. It does not establish that the pairs are duplicate SKUs.
Which products have a changed mix of low-rating reviews?
The query groups 99,539 sampled 2021 to 2022 headphone and earbud reviews using prepared text vectors, then compares each product's share in each KMeans group across the two years. Each denominator counts sampled reviews rated at most two stars for that product and year.
The KMeans assignments join back to review dates, product titles and original text. The linked example is the 2022 review with the smallest ID for its product and group. It is not selected as representative text.
WITH assignments AS (
SELECT id, cluster_id
FROM cuvs_kmeans(
input => (SELECT v.id, v.vector
FROM semantic_reviews_vectors v
JOIN semantic_reviews_text r ON r.id = v.id
WHERE r.category_l3 = 'Headphones & Earbuds'
ORDER BY v.id),
n_clusters => 24, n_init => 5, max_iter => 100, tol => 0.0001)
), issues AS (
SELECT r.product_id, a.cluster_id,
SUM(CASE WHEN EXTRACT(YEAR FROM r.review_time) = 2021 THEN 1 ELSE 0 END) AS n2021,
SUM(CASE WHEN EXTRACT(YEAR FROM r.review_time) = 2022 THEN 1 ELSE 0 END) AS n2022,
MIN(CASE WHEN EXTRACT(YEAR FROM r.review_time) = 2022 THEN r.id END) AS example_id
FROM assignments a JOIN semantic_reviews_text r ON r.id = a.id
GROUP BY r.product_id, a.cluster_id
), totals AS (
SELECT *, SUM(n2021) OVER (PARTITION BY product_id) AS total2021,
SUM(n2022) OVER (PARTITION BY product_id) AS total2022
FROM issues
)
SELECT p.product_id, p.title, t.cluster_id,
t.n2021, t.total2021, t.n2022, t.total2022,
100.0 * t.n2021 / t.total2021 AS share2021_pct,
100.0 * t.n2022 / t.total2022 AS share2022_pct,
100.0 * t.n2022 / t.total2022 - 100.0 * t.n2021 / t.total2021 AS change_pp,
t.example_id, r.embedding_text AS example_review
FROM totals t
JOIN products p ON p.product_id = t.product_id
JOIN semantic_reviews_text r ON r.id = t.example_id
WHERE t.total2021 >= 30 AND t.total2022 >= 30 AND t.n2022 >= 10
AND 1.0 * t.n2022 / t.total2022 - 1.0 * t.n2021 / t.total2021 > 0.10
ORDER BY change_pp DESC, p.product_id, t.cluster_id
LIMIT 10
For Altec Lansing NanoPods, group 7 changes as follows:
The query also returns one example review per product. The saved capture keeps its review ID (14707192); the text stays in the source dataset.
The query took 30.46 s.
Follow-up: check the charging explanation. The 27.92-point change raises a specific question about charging complaints. A raw-table query counts NanoPods reviews containing "charg" in the title or body. It uses all 2021 to 2022 reviews rated at most two stars, without the embedding sample, text-length filter or verified-purchase condition.
SELECT EXTRACT(YEAR FROM review_time) AS review_year,
COUNT(*) AS low_rating_reviews,
SUM(CASE WHEN LOWER(COALESCE(title, '') || ' ' || COALESCE(text, '')) LIKE '%charg%' THEN 1 ELSE 0 END) AS charging_mentions
FROM reviews
WHERE product_id = 1507685 AND rating <= 2
AND review_time >= TIMESTAMP '2021-01-01'
AND review_time < TIMESTAMP '2023-01-01'
GROUP BY EXTRACT(YEAR FROM review_time)
ORDER BY review_year;
The query took 0.12 s.
Charging language was already common in 2021. The broader source check does not support treating the group-share increase as a comparable rise in charging complaints.
The main capture records a mixed KMeans query with 20 host DataFusion boundaries. The native raw-table checks and five original matching reviews are in the follow-up capture.
Related analyses
The UMAP example projects review vectors and joins the output to original text, allowing inspection of review text alongside returned coordinates.
The KMeans and Top-K example calculates eight keyword-mention rates from the converted reviews and groups 2,384 products by those rates. It returns the three largest average rates within each of six product groups. Mentions highlight reviews for closer reading, without requiring prepared vectors.
Data scope
The source is Amazon Reviews 2023,
Electronics. The saved preparation encoded
1,010,000 products:
one million form the search corpus, and 10,000 disjoint products are queries.
It also encoded 1,000,000
reviews rated at most two stars, with at least 40 body characters, from 2021
and 2022. BGE-M3 revision 5617a9f61b028005a4858fdac845db406aefb181 produced
the vectors outside SQL. The stored 1,024-dimensional Float32 values were
L2 normalized. The queries do not encode text during execution, and the
raw-text keyword query does not require this preparation. Prices and product
attributes are from the captured catalog.
The main capture holds the executed vector-query SQL, query IDs, result rows, and preparation metadata. The follow-up capture holds the later raw-table checks and the IDs of the matching reviews. Both are historical records of the executed queries. Saved results keep product titles, IDs, ratings, dates and coordinates; review text is not reproduced on this site. Amazon Reviews 2023 is published by the McAuley Lab (Hou et al., 2024, arXiv:2403.03952) for research use.
The query timings include planning, execution and result receipt.