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.
Saturday, February 9, 2013
Sunday, January 6, 2013
Disabled PayPal IPN when using Magento and eBay
The Problem
If you use the same PayPal account to collect payment for multiple stores (Magento, eBay, etc...), you may encounter an issue in which IPN is constantly being disabled on the PayPal side. If you're struggling with this, the following may help.Here's what's happening: You've setup your PayPal "Notification URL" to point to Magento's IPN listener (https://{mydomain.com}/paypal/ipn (or something to that affect)). The other storefront (like eBay) uses this same Notification URL you setup in PayPal. So, with the Notification URL pointing to https://{mydomain.com}/paypal/ipn specifically for Magento, when you get an order from your eBay store, PayPal tries to send an IPN message to your Magento listener. Magento doesn't know what to do with it since the order didn't originate here so an error message is sent back to PayPal. After a handful of these, PayPal disables IPN for your account.
The Solution
Magento sends the "Notification URL" to PayPal and that overrides whatever has been setup in your PayPal settings, so nothing to do as far as Magento goes. What we need to do is to create another "listener" for the other store... this one will be a null/void listener, but will send a "success" message back to PayPal when called. We'll set the "Notification URL" in PayPal to point to this null listener. As stated before, Nofication URLs are baked into Magento requests and this null Notification URL will be ignored for all Magento IPNs and that'll hum along just fine.- Over at paypaltech.com, there's a utility that will create a IPN listener script. Go to https://www.paypaltech.com/SG2/ and copy the generated file (UPDATE: file has been removed. See below. Copy everything between the <-start-> and <-end-> in order to create your null_ipn.php file). Paste this text into a file named null_ipn.php in the root of your Magento web folder.
- Go to PayPal and log in. Next, select "My Account" on the tab. Next, navigate to profile->My Selling Tools->Instant Payment Notifications->Edit Settings. For the "Notification URL", use the file that you just created (http://{yourdomain.com}/null_ipn.php
That's it. IPNs received by Magento will be posted to your Magento orders and IPNs received by the null listener will send back a success message to PayPal. No more disabled IPN every few days.
Note: there are other ways to deal with these stray IPN notifications if you're so inclined... check them out at https://www.paypaltech.com/SG2/
I hope this helps,
webmaster, All American Hats
<--------------start null_ipn.php ------------------>
<?php
// Revision Notes
// 11/04/11 - changed post back url from https://www.paypal.com/cgi-bin/webscr to https://ipnpb.paypal.com/cgi-bin/webscr
// For more info see below:
// https://www.x.com/content/bulletin-ip-address-expansion-paypal-services
// "ACTION REQUIRED: if you are using IPN (Instant Payment Notification) for Order Management and your IPN listener script is behind a firewall that uses ACL (Access Control List) rules which restrict outbound traffic to a limited number of IP addresses, then you may need to do one of the following:
// To continue posting back to https://www.paypal.com to perform IPN validation you will need to update your firewall ACL to allow outbound access to *any* IP address for the servers that host your IPN script
// OR Alternatively, you will need to modify your IPN script to post back IPNs to the newly created URL https://ipnpb.paypal.com using HTTPS (port 443) and update firewall ACL rules to allow outbound access to the ipnpb.paypal.com IP ranges (see end of message)."
// read the post from PayPal system and add 'cmd'
$req = 'cmd=_notify-validate';
foreach ($_POST as $key => $value) {
$value = urlencode(stripslashes($value));
$req .= "&$key=$value";
}
// post back to PayPal system to validate
$header = "POST /cgi-bin/webscr HTTP/1.1\r\n";
$header .= "Content-Type: application/x-www-form-urlencoded\r\n";
$header .= "Content-Length: " . strlen($req) . "\r\n\r\n";
//If testing on Sandbox use:
//$fp = fsockopen ('ssl://www.sandbox.paypal.com', 443, $errno, $errstr, 30);
$fp = fsockopen ('ssl://ipnpb.paypal.com', 443, $errno, $errstr, 30);
if (!$fp) {
// HTTP ERROR
} else {
fputs ($fp, $header . $req);
while (!feof($fp)) {
$res = fgets ($fp, 1024);
if (strcmp ($res, "VERIFIED") == 0) {
// check the payment_status is Completed
// check that txn_id has not been previously processed
// check that receiver_email is your Primary PayPal email
// check that payment_amount/payment_currency are correct
// process payment
// echo the response
echo "The response from IPN was: <b>" .$res ."</b><br><br>";
//loop through the $_POST array and print all vars to the screen.
foreach($_POST as $key => $value){
echo $key." = ". $value."<br>";
}
}
else if (strcmp ($res, "INVALID") == 0) {
// log for manual investigation
// echo the response
echo "The response from IPN was: <b>" .$res ."</b>";
}
}
fclose ($fp);
}
?>
<----------------end null_ipn.php ----------------->
Using a Wishlist to Build Your Magento Newsletter Template
Building a Magento Newsletter using a Wishlist
With Magento you can build newsletters using straight up HTML. It's a very nice feature but building out a nice newsletter with enticing product images and "buy now" links can be tedious to do by hand. Here's an easy way to get a good template that has a grid of products with customer convenience hyperlinks.The Wishlist
Any customer can create a list of all of their favorite products using the wishlist functionality built into Magento. The wishlist "send to" functionality sends a copy of a very nicely-formatted product ad of the wishlist to an your e-mail account... full HTML.- Login to the Magento store frontend and add the products you'd like in your newsletter to your wishlist.
- Click the "Share Wishlist" in order to send to your email address.
- If you use something like sendgrid or mailchimp as your outgoing SMTP service, you're going to get a lot of injected redirects into your HTML. You'll need an alternate way of getting the email to yourself. I temporarily disabled the awesome plugin from aschroder.com that sends out SMTP/gmail. After disabling, the email was sent from my web server instead and was clean.
Getting the HTML from the Wishlist email to your newsletter
- In your email client, locate the wishlist email and view source (In gmail that's called "show original" and is available from the drop-down menu). Select all of the HTML in the email and copy to the clipboard.
- Some e-mail clients such as gmail insert funny characters into the HTML. If you notice a lot of "=0A" or "=3D" in the HTML you copied, go to http://www.convertstring.com/EncodeDecode/QuotedPrintableDecode in order to "decode". Once decoded, you'll want to copy the converted text to the clipboard.
Creating the Newlsetter
- Just paste the HTML into your Magento newsletter template. Adjust and beautify as needed.
I hope this helps!
Webmaster, All American Hats
Subscribe to:
Posts (Atom)