We’ve been recently tasked with onboarding a client with TrustPilot, and getting it integrated within their WordPress/WooCommerce site. Now, TrustPilot is sold as a very easy to use tool with a drag and drop interface, that, it is… but, integration with variable products and trying to format csv files in the correct way isn’t so easy for the unseasoned business owner – and this is where we come in.
It’s worth noting, Product Reviews is an add-on to the standard TrustPilot package. You can’t import product reviews unless you pay TrustPilot for this feature.
We thought we’d share this simple bit of code, as I’m sure it’ll save hunting round and crafting something new just like us. It isn’t pretty, but does the job perfectly well.
SELECT replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(`comment_author`, 'yahoo', ''), 'hotmail', ''), '.co.uk', ''), '.com', ''), '@', ''), '.', ' '), '0', ''), '9', ''), '8', ''), '7', ''), '6', ''), '5', ''), '4', ''), '3', ''), '2', ''), '1', '') AS 'Comment_Auth', `post_title` AS 'Product', `ID` AS 'ID', `comment_content` AS 'Review', `wp_commentmeta`.`meta_value` AS 'Rating', DATE_FORMAT(`comment_date`, '%Y-%m-%dT%TZ') AS date
FROM `wp_comments`
INNER JOIN `wp_posts` ON `comment_post_ID`=`ID`
INNER JOIN `wp_commentmeta` ON `wp_commentmeta`.`comment_id`=`wp_comments`.`comment_ID`
WHERE `comment_author` != 'WooCommerce'
AND `wp_posts`.`post_type` = 'product'
AND `wp_commentmeta`.`meta_key` = 'rating'
AND `comment_approved` = '1';This code needs to be run on your MySQL server, it’ll generate the data which you will need to export to .csv with text qualifiers escaped as “ and field delimiters as ; – just like their example shows
If you are wondering why it contains all the replace lines, we’ve found that when your users opt for a username that might be an email address, or their name with their year of birth below, it just cleanses it slightly, for security – and to stop those customers calling to complain their personal data is in plain view.
It is worth noting, it’ll still be a good idea to manually check the data before uploading to trust pilot – some little gremlins might still get through.
Now, if the above looked a little alien, and you need some help getting your Product Reviews imported, give us a call and we can arrange to do this for you.
