Amazon Review Data: Spotting Trends and Fake Reviews

Introduction

Amazon is the leading provider of cloud computing and has a number of interesting open data sets which you can experiment with. I wanted to try them out with a new product my company has developed, so I've been looking at those data sets. One of the most recognizable is Amazon's own review data which is documented at https://registry.opendata.aws/amazon-reviews/

Approach

I wanted to see what sort of questions the review data could answer. At the end of this article, in "How I did it," I'll show the steps to access the data, but for now you will just need to know some SQL to follow the queries used.

Here are the questions I wanted to answer, and the results — note that unless otherwise specified each result came back in less than half a second.

How Do Ratings Vary With verified_purchase?

Those with a verified_purchase are on average rated 0.13 higher:

select verified_purchase,
cast(avg(cast(star_rating as decimal(5,4))) as decimal (3,2)) avg_rating,
count(*)
from opt_reviews
group by 1
order by 1; 

How Do Ratings Vary With Vine Membership?

Vine reviews are on average 0.13 lower than non-Vine (you can read more about what Amazon Vine is at https://www.amazon.co.uk/gp/vine/help, if you aren't familiar with it):

select verified_purchase,
cast(avg(cast(star_rating as decimal(5,4))) as decimal (3,2)) avg_rating,
count(*)
from opt_reviews
group by 1
order by 1;

How Do Ratings Vary With Marketplace (i.e. Region)?

UK reviewers are the most positive (light relief compared to Brexit, perhaps), with an average rating of 0.18 higher than the most negative reviewers, who are from the US marketplace:

select marketplace,
cast(avg(cast(star_rating as decimal(5,4))) as decimal (3,2)) avg_rating,
count(*)
from opt_reviews
group by 1
order by 1;

How Have Ratings Altered Over Time?

Discounting the time-traveller who submitted a 5.0 rating review in 1973 according to the data, reviews have generally reduced ratings over time from 1995 to 2004, then oscillated somewhat before improving slightly between 2011 and 2015.

Perhaps this is because people were delighted with any sort of e-commerce offering in the early days of Amazon, then became more demanding; then recent years have seen a material improvement in their experience (including products, ordering, and delivery process).

A cynic might wonder if Amazon has tended to make more positive reviews available — I could not find any literature indicating what percentage of all reviews this data set covers:

select "YEAR",
cast(avg(cast(star_rating as decimal(5,4))) as decimal (3,2)) avg_rating,
count(*)
from opt_reviews
group by 1
order by 1;

Has Verification of Purchases Changed Over Time for Reviews?

select "YEAR", 
sum(case when verified_purchase = 'Y' then 1 else 0 end) verified_reviews, 
(sum(case when verified_purchase = 'Y' then 1 else 0 end) * 100.00) / count(*) percent_verified 
from opt_reviews 
group by 1 
order by 1;

We can see how the number of verified reviews increases each year, and its percentage of all reviews is generally increasing too.

How Do Ratings Vary by Product Category?

It appears that Digital Software is the most disappointing category for reviewers, averaging a massive 1.2 points worse than the impeccable Gift Card category:

select product_category,
cast(avg(cast(star_rating as decimal(5,4))) as decimal (3,2)) avg_rating,
count(*)
from opt_reviews
group by 1
order by 2;

How Do Customers Report the Helpfulness of Different Star Rating Reviews?

It seems that people consider negative reviews to be more helpful than positive reviews, possibly because they perceive them as valuable in preventing the purchase of an unsuitable product, or because they highlight issues which might be relevant to whether or not the customer then goes on to purchase the product:

select star_rating,
cast(avg(cast (helpful_votes as decimal (18,4))) as decimal (16,2)) AVG_HELP,
count(*)
from opt_reviews
group by 1
order by 1;

Looking at Reviewer Behavior

We can look at things from a more customer-centric point of view, which is more expensive in terms of query time (because we have to do a lot more processing to aggregate over a lot of different reviewers).

As a result, the query below took just under 14 seconds, compared to all the previous ones which ran in less than 0.5 seconds — it is looking across all reviewers to see how many reviews they have done, how many product categories those reviews span, the average rating they give, and how many ratings at each level they have given.

We also check how helpful their average review is, how many Vine reviews they have, and how many of their purchases are verified:

select customer_id, 
count(*), 
count(distinct product_category) cats, 
cast(avg(cast(star_rating as decimal(5,4))) as decimal (3,2)) avg_rating, 
cast(avg(cast (helpful_votes as decimal (18,4))) as decimal (16,2)) avg_help, 
sum(case when star_rating = 1 then 1 else 0 end) one, 
sum(case when star_rating = 2 then 1 else 0 end) two, 
sum(case when star_rating = 3 then 1 else 0 end) three, 
sum(case when star_rating = 4 then 1 else 0 end) four, 
sum(case when star_rating = 5 then 1 else 0 end) five, 
sum(case when vine = 'Y' then 1 else 0 end) vine_reviews, 
sum(case when verified_purchase = 'Y' then 1 else 0 end) verified_purchases 
from opt_reviews 
group by 1 
order by 2 desc;

The results show:

• you would like your product to be reviewed by customer_id 50122160 rather than customer 43430756, as on average that will give you a star rating 1.65 higher.
• the reviewer with the highest number of reviews is not a Vine reviewer and has only one verified purchase. You might not want to give their reviews too much credence though, as they seem to be happy with almost every product (maybe they are just an optimist, or perhaps they have some incentive to give good reviews). Note that their average number of helpful votes per review is in line with other reviewers though, which is somewhat surprising.
• the other very optimistic reviewer, with id 7080939, appears to be treated with more cynicism by other customers; their average helpful votes per review is the lowest of the top 20 reviews.
• the people with the most helpful votes per review have a better range of star ratings and a higher number of verified purchases than the two very positive reviewers mentioned above.

Switching to look for quality rather than quantity, we can examine customers whose reviews average the highest number of helpful votes:

select customer_id,
count(*),
count(distinct product_category),
cast(avg(cast(star_rating as decimal(5,4))) as decimal (3,2)) avg_rating,
cast(avg(cast (helpful_votes as decimal (18,4))) as decimal (16,2)) avg_help,
sum(case when star_rating = 1 then 1 else 0 end) one,
sum(case when star_rating = 2 then 1 else 0 end) two,
sum(case when star_rating = 3 then 1 else 0 end) three,
sum(case when star_rating = 4 then 1 else 0 end) four,
sum(case when star_rating = 5 then 1 else 0 end) five,
sum(case when vine = 'Y' then 1 else 0 end) vine_reviews,
sum(case when verified_purchase = 'Y' then 1 else 0 end) verified_purchases
from opt_reviews
group by 1
having count(*) > 100
order by 5 desc
limit 10;

The main thing I could see from this is that these users tended to have a much lower number of reviews, and in general had a higher percentage of verified purchases.

Which Review Is the Most Helpful in Amazon?

According to this data, the most helpful review in this data set is a positive one for an Amazon product — it is for the original Kindle, with the review submitted in 2007.

However, note the duplicate rows in the data:

• with review R2JF7E91JJVHAT for "Fifty Shades of Grey" appearing twice.

• ...and the same for review R1LT38SAC0FA4G for the same novel in digital form.

I've verified these anomalies existed in the underlying S3 Parquet data, and notified AWS of the issue, but haven't had any feedback yet. The duplication does not exist in the CSV version of the same data set, so something has clearly gone wrong when generating the Parquet version.

There are some other anomalies present in the review text as well (parts of the review text are duplicated, apparently most commonly for long review text) — again, this is evident upon inspection of the underlying Parquet files, and AWS has been notified.

select customer_id, review_id, product_id, product_title, star_rating,
helpful_votes, total_votes, vine, verified_purchase from opt_reviews
order by helpful_votes
desc limit 15;

Unusual Reviewer Behavior

We've already seen that some reviewers generate a very large number of reviews, and sometimes those are very largely five-star reviews.

Looking for other unusual behaviours we can see, for example, instances of reviewers repeatedly reviewing the same product. I was actually surprised that this was even allowed (have not verified if that is still the case):

select customer_id, product_id, count(*) 
from opt_reviews 
group by 1, 2 
having count(*) > 1 
order by 3 desc;

As we can see above, one product was reviewed 220 times by one reviewer, and 114 times by another reviewer. In the case of the second row above, the 220 reviews occurred between 2003-11-10 and 2003-12-04. Presumably, the reviewer kept thinking of other compliments for this revolutionary product, and could not edit any of their existing reviews for it. You certainly have to admire their persistence.

We can also check for reviewers who are a bit lazy and repeatedly use the same review headline:

select customer_id, review_headline, count(*) 
from opt_reviews 
where char_length(review_headline) > 10 
group by 1, 2
having count(*) > 1
order by 3 desc;

Some reviewers really like to reiterate their point, so they submit multiple reviews for the same product, all with the same headline:

select customer_id, product_id, review_headline, count(*) 
from opt_reviews 
where char_length(review_headline) > 10 
group by 1, 2,3 
having count(*) > 1 
order by 4 desc;

How I Did It

The product I was using was Kognitio's AWS Marketplace offering, which allows you to simply but quickly analyze data stored in Amazon's S3 storage and elsewhere. You can find the link to the product here. From that page, I brought up a system with 4 nodes, each with 64GB RAM and 16 cores, and a spot price of around $4.23 per hour. You also need a tool capable of connecting to Kognitio and running SQL — pretty much any third party tool will be able to do this if you install the Kognitio ODBC or JDBC driver, but I used Kognitio's own SQL submission tool called Console which you can download here.

I then ran the following SQL to create an external table allowing me to query the review data stored in S3. Note that the data is stored in Parquet format, which is an industry standard, and contains information about column names and types to allow them to be automatically generated in the external table:

create external table reviews from s3_parquet
target 'uri_location s3://amazon-reviews-pds, uri_path /parquet/, fmt_filename_partitions product_category, repeatable_results yes, ignore_invalid_records 1';

In the above:

• 'uri_location' and 'uri_path' tell the software where the data can be found.
• the 'fmt_filename_partitions' setting provides information on what the Parquet files in S3 are partitioned on (product_category in this case).
• 'repeatable_results yes' indicates the data isn't changing so you will get the same results if you re-access the data in S3 multiple times in a query.
• 'ignore_invalid_records 1' specifies that any records that cannot be loaded should be ignored (the failure to load them is logged for further investigation).

I can then query that external table immediately, for example, the following query returns in a few seconds as it can leverage the fact that the data is already partitioned on product_category to eliminate irrelevant partitions:

select count(*)
from reviews
where product_category = 'Camera';

To get better query performance, we can generate an in-memory image of the data, then run against that rather than continually accessing S3. So I defined a view showing the fields I was interested in, and also mapping them to the most appropriate types rather than relying on the types inferred from Parquet — note that I had to quote the column name "YEAR" below, as that is a reserved word in Kognitio as with many databases:

create view opt_reviews as
select cast(marketplace as char(2)) marketplace,
cast(customer_id as bigint) customer_id,
cast(review_id as char (16)) review_id,
cast(product_id as char (12)) product_id,
cast(product_parent as bigint) product_parent, 
product_title, star_rating, helpful_votes, total_votes, 
cast(vine as char(1)) vine, 
cast(verified_purchase as char(1)) verified_purchase, 
review_headline, review_date, "YEAR", product_category 
from reviews;

Then I created a memory image of that view, which took a few minutes as it has to scan all the data in S3 and transfer it to memory:

create view image opt_reviews;

The result was an image with just over 160 million rows in it.

At this point I could ask (hopefully) more interesting questions about the data, as shown earlier in the article — let me know if there are any other questions you'd like answered, or try this out yourself.