Import Product Reviews in Magento via SQL

Customers are more likely to make purchasing decisions when they see a product that has good reviews.
Also, some search engines give sites with product reviews a higher ranking than those without.

Since Magento doesn’t support default functionality for importing product reviews, we came up with a quick approach using SQL.

Database Schema for Product Reviews

Magento 1/Magento 2 has two group tables for reviews

• review* tables
• rating* tables

review* table structure

This group comprises of following tables:

• review
• review_detail
• review_entity
• review_entity_summary
• review_status
• review_store

And the ERD looks like

In this group of tables, the review_detail table stores the basic information of the reviews(title, detail, nickname, customer_id, etc.). The review table stores the product id & the review status. The review_entity_summary table summarizes the reviews of the product(number of reviews, overall score, etc.)

rating* table structure

This group comprises of following tables:

• rating
• rating_entity
• rating_option
• rating_option_vote
• rating_option_vote_aggregated
• rating_store
• rating_title

And the ERD looks like

The most important table in this group is rating_option_vote, which holds information like IP address, rating value(1 – 5), type of rating, customer id, product id, etc. The rating_option_vote_aggregated table summarizes the rating info like how many votes have been cast, overall percentage, etc. And the rating table includes rating attributes likes Quality, Value, Price & Rating.

Import Product Reviews via SQL

As you know the product reviews are one of the driving factors of sales, you might be interested in importing product reviews from other platforms.

You can find lots of paid extensions for importing product reviews but here I will be sharing the SQL approach for importing product reviews.

Step 1: Prepare SQL

You can prepare the SQL script below. You can edit the values as per your requirement(note the variables marked with — Edit values).

-- INSERT REVIEW
-- Edit values
SET @PRODUCT_ID 		= 123;
SET @STORE_ID 			= 1;
SET @CUSTOMER_ID 		= NULL;
SET @REVIEW_TITLE 		= 'Lorem Ipsum';
SET @REVIEW_DETAIL 		= 'Neque porro quisquam est qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit...';
SET @REVIEW_RATING 		= 5; -- Between 1 to 5
SET @REVIEW_NICKNAME 	= 'John Doe';
SET @REVIEW_CREATED_AT 	= '2019-07-15'; -- OR date in YY-mm-dd HH:ii:ss format

-- No need to Edit
SET @REVIEW_ENTITY_ID   = (SELECT entity_id FROM rating_entity WHERE entity_code = 'product'); -- 1: product, ...
SET @REVIEW_STATUS_ID   = (SELECT status_id FROM review_status WHERE status_code = 'Pending'); -- 1: Approved, 2: Pending, 3: Not Approved

INSERT INTO review SET created_at = @REVIEW_CREATED_AT, entity_id = @REVIEW_ENTITY_ID, entity_pk_value = @PRODUCT_ID, status_id = @REVIEW_STATUS_ID;
SET @REVIEW_ID = (SELECT LAST_INSERT_ID());
INSERT INTO review_detail SET review_id = @REVIEW_ID, store_id = @STORE_ID, title = @REVIEW_TITLE, detail = @REVIEW_DETAIL,	nickname = @REVIEW_NICKNAME, customer_id = @CUSTOMER_ID;
INSERT INTO review_store SET review_id = @REVIEW_ID, store_id = 0;
INSERT INTO review_store SET review_id = @REVIEW_ID, store_id = @STORE_ID;
INSERT INTO rating_option_vote SET option_id = 5, remote_ip = '', remote_ip_long = 0, customer_id = @CUSTOMER_ID, entity_pk_value = @PRODUCT_ID, rating_id = @REVIEW_ENTITY_ID, 
review_id = @REVIEW_ID, percent = 100, value = @REVIEW_RATING;

If you want to check the related reviews data for a product, you can use this SQL

SET @PRODUCT_ID = 123;
SELECT * FROM review WHERE entity_pk_value = @PRODUCT_ID;
SELECT * FROM review_detail WHERE review_id IN (SELECT review_id FROM review WHERE entity_pk_value = @PRODUCT_ID);
SELECT * FROM review_store WHERE review_id IN (SELECT review_id FROM review WHERE entity_pk_value = @PRODUCT_ID);
SELECT * FROM review_entity_summary WHERE entity_pk_value = @PRODUCT_ID;
SELECT * FROM rating_option_vote WHERE entity_pk_value = @PRODUCT_ID;
SELECT * FROM rating_option_vote_aggregated WHERE entity_pk_value = @PRODUCT_ID;

Step 2: Execute the SQL

Once you prepare the SQL with required values, you can execute it via SQL client (PHPMyAdmin, MySQL CLI or any Server-Side script)

Step 3: Moderate the Reviews

The above SQL script imports the reviews in “Pending” status.
You can moderate those imported reviews from Admin.

1. On the Admin sidebar, click MARKETING > User Content > Pending Reviews
2. In the list, click a pending review to view the details, and edit if necessary.
3. To approve a pending review, change the Status from “Pending” to “Approved”. To reject a review, select “Not Approved”.
4. When complete, tap Save Review.

BONUS

For the sake of brevity, I have shown how to import product reviews for one product. But you can use some server-side script to read the data from CSV and loop over the rows and import via above SQL.