Saturday, February 9, 2013

M2ePro eBay Customer Feedback Import

If you use the M2ePro eBay extension for Magento, you'd probably love to import all of your eBay customer feedback into your site's product reviews.  Here's how:


1) make sure that you have turned on "feedbacks" sync in your M2ePro setup for eBay.  That'll pull down all of the customer feedback into your local Magento database.

2) open up phpMyAdmin or whichever MySQL admin tool you use and run the following SQL statements.  Note: you need to set the variables defined in bold:

Change Log:
9/13/2013 - Updated to support multi-store setups
8/28/2013 - Fixed issue where only existing product ratings were being updated
4/28/2013 -  Updated to include logic for ratings

<--------------------------------------------------begin--------------------------------------------------------------------->

/*SET VARIABLES */
set @start_date = '2000-01-01';
set @rating_id = 4; /*Go to Catalog->Ratings and Reviews->Manage Ratings and add or locate the rating ID you'd like to populate.  Works with only one, so I'd suggest creating an "overall rating"*/
/* DELETE ALL EBAY REVIEWS*/
delete from review_store
where review_id in (select review_id from review_detail where detail like '%ebay customer%');

delete from review
where review_id in (select review_id from review_detail where detail like '%ebay customer%');

delete from rating_option_vote 
where review_id in (select review_id from review_detail where detail like '%ebay customer%');

delete from review_detail
where detail like '%ebay customer%';

/*INSERT FEEDBACK*/
insert into review (created_at, entity_id, entity_pk_value, status_id)
select mef.buyer_feedback_date, 1, mei.product_id, 1
from m2epro_ebay_item mei, m2epro_ebay_feedback mef
where mei.item_id = mef.ebay_item_id and mef.buyer_feedback_date >= @start_date
and mef.buyer_name not in ('robkelly_1');

/*UPDATED*/
insert into review_detail (review_id, store_id, title, detail, nickname)
select r.review_id, w.website_id /*@store_id*/, mef.buyer_feedback_text,
    'ebay customer',mef.buyer_name
from review r, m2epro_ebay_item mei, m2epro_ebay_feedback mef, catalog_product_website w 
where mei.item_id = mef.ebay_item_id
    and r.created_at = mef.buyer_feedback_date and r.entity_pk_value = mei.product_id
    and w.product_id = mei.product_id
    and mef.buyer_feedback_date >= @start_date;

/*INSERT RATING*/
insert into rating_option_vote (option_id, review_id, percent, value, remote_ip, remote_ip_long, 
    customer_id, entity_pk_value, rating_id)
select 20, r.review_id, 
    case mef.buyer_feedback_type 
        when 'Negative' then 0
        when 'Neutral' then 50
        when 'Positive' then 100
    end,
    case mef.buyer_feedback_type 
        when 'Negative' then 1
        when 'Neutral' then 2.5
        when 'Positive' then 5
    end,
        '169.254.90.78', '1314586281', NULL,
        mei.product_id, @rating_id
from review r, m2epro_ebay_item mei, m2epro_ebay_feedback mef
where mei.item_id = mef.ebay_item_id 
    and r.created_at = mef.buyer_feedback_date and r.entity_pk_value = mei.product_id
    and mef.buyer_feedback_date >= @start_date;

/*UPDATED*/
insert into review_store (review_id, store_id)
select r.review_id, w.website_id /*@store_id*/
from review r, m2epro_ebay_item mei, m2epro_ebay_feedback mef, catalog_product_website w 
where mei.item_id = mef.ebay_item_id and w.product_id = mei.product_id
    and r.created_at = mef.buyer_feedback_date and r.entity_pk_value = mei.product_id
    and mef.buyer_feedback_date >= @start_date;

insert into review_entity_summary (entity_pk_value, entity_type, store_id)
select entity_pk_value, 1, w.website_id /*@store_id*/
from rating_option_vote, catalog_product_website w 
where entity_pk_value not in (select entity_pk_value from review_entity_summary)
and w.product_id = entity_pk_value;

update review_entity_summary res
set res.reviews_count = (select distinct count(r.review_id) from review r, review_store rs,catalog_product_website w 
                                where r.entity_pk_value = res.entity_pk_value and res.entity_pk_value = w.product_id
                                and rs.review_id = r.review_id and rs.store_id = w.website_id group by r.entity_pk_value);


DELETE FROM rating_option_vote_aggregated;

INSERT INTO rating_option_vote_aggregated (rating_id, entity_pk_value, vote_count, vote_value_sum, percent, store_id) 
select distinct @rating_id, entity_pk_value , (select count(*) from rating_option_vote where rov.entity_pk_value = entity_pk_value), 
    (select sum(percent) from rating_option_vote where rov.entity_pk_value = entity_pk_value), 
    (select sum(percent) from rating_option_vote where rov.entity_pk_value = entity_pk_value)/(select count(*) from rating_option_vote where rov.entity_pk_value = entity_pk_value), 
    w.website_id
from rating_option_vote rov, catalog_product_website w 
where rov.entity_pk_value = w.product_id
group by entity_pk_value, w.product_id;

UPDATE review_entity_summary res, rating_option_vote_aggregated rova
SET res.rating_summary = rova.percent

WHERE res.entity_pk_value = rova.entity_pk_value AND res.store_id = rova.store_id;


<--------------------------------------------------------end----------------------------------------------------------------->

That should do it.  All of your ebay feedback should be mapped to your product reviews.



Donnie
http://www.allamericanhats.com - welding hats (welder caps), biker caps, skull caps and do rags.

6 comments:

  1. Replies
    1. Nils,

      Sorry for such a late response. I updated the SQL to address the issue you are running into.

      Thanks,

      Donnie

      Delete
  2. Hey does this work also with M2EPro 6.x?

    ReplyDelete
  3. Seems great! I am a little scared to try it. Does it auto approve the review, or allow for moderation through admin? I would not want feedback that references eBay to import and apply itself without the chance to regulate it.

    ReplyDelete
  4. with M2ePro 6.X and magento 1.7.0.2 i see this error "#1062 - Duplicate entry '55058-1' for key 'PRIMARY' "

    ReplyDelete
  5. Hi,

    Code looks good but we are running into this error anytime I try and run it. If I comment that out it will fail further down. Any ideas? M 1,7,02 and the current version of m2e.

    Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`mag`.`review_detail`, CONSTRAINT `FK_REVIEW_DETAIL_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE)

    ReplyDelete