Automatic Emails Requesting a Product Review

[Guest]

Greetings,

Is anyone familiar with an existing module that would automatically email my customers asking that they review a product?

 

What I am looking for in particular is a contribution which I can program to email a customer who has purchased a product in my store at a set time from purchase (like 2 weeks after shipping), asking that they rate the product. I would like for a link to be included in the email taking the user directly to the product page where they would rate the product.

 

If anyone knows of an existing module, or one that I could modify to do this, I'd greatly appreciate you letting me know.

 

Thanks in Advance.

Goodebox

[♥kymation]

Did you ever get anywhere with this? The total lack of replies is a pretty strong hint, but you never know. If not, I need to write something to do this. Please post if you are interested.

 

Regards

Jim

[ckrueger]

That sounds completely cool!

 

 

 

Greetings,

Is anyone familiar with an existing module that would automatically email my customers asking that they review a product?

 

What I am looking for in particular is a contribution which I can program to email a customer who has purchased a product in my store at a set time from purchase (like 2 weeks after shipping), asking that they rate the product. I would like for a link to be included in the email taking the user directly to the product page where they would rate the product.

 

If anyone knows of an existing module, or one that I could modify to do this, I'd greatly appreciate you letting me know.

 

Thanks in Advance.

Goodebox

[♥kymation]

I went ahead and hacked up a prototype of this. It will automatically send an email to every customer after a given number of days (Number can be set in Admin > Configuration > My Store.) The text of the email is contained in a template for ease of customization. I'll package this all up as a Contribution once it's been debugged.

 

First, make the following changes to your database:

INSERT INTO `configuration` VALUES (NULL, 'Days delay for review email', 'REVIEW_REMINDER_DELAY', '10', 'Number of days to wait before sending a review reminder email', 1, 999, now(), now(), NULL, NULL);
ALTER `orders` ADD `reminder_sent` tinyint(1) NOT NULL default '0' AFTER `orders_status`

Next, copy this code into a file and name it review_mail.php:

#!/usr/local/bin/php
<?php
/*
 $Id: review_mail.php,v 1.0 2007/10/14 jck Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2007 osCommerce

 Released under the GNU General Public License
*/


// Include the neccesary files and settings
 // Set the path to the osCommerce catalog from this file 
 $path_rel = '../';	 

 // Set the local configuration parameters - mainly for developers
 if (file_exists ($path_rel . 'includes/local/configure.php')) include ($path_rel . 'includes/local/configure.php');

 // include server parameters
 require ($path_rel . 'includes/configure.php');

 // set php_self if not already set
 if (!isset($PHP_SELF)) $PHP_SELF = $HTTP_SERVER_VARS['PHP_SELF'];

 // include the list of database tables
 require($path_rel . 'includes/database_tables.php');

 // include the database functions
 require($path_rel . 'includes/functions/database.php');

 // make a connection to the database... now
 tep_db_connect();

 // set the application parameters
 $configuration_query = tep_db_query ('select configuration_key as cfgKey,
										   configuration_value as cfgValue
									from ' . TABLE_CONFIGURATION
								  );
 while ($configuration = tep_db_fetch_array($configuration_query)) {
define ($configuration['cfgKey'], $configuration['cfgValue'] );
 }

 // define general functions
 require($path_rel . 'includes/functions/general.php');
 require($path_rel . 'includes/functions/html_output.php');

 // incorporate the email class
 require($path_rel . 'includes/classes/email.php');
 require($path_rel . 'includes/classes/mime.php');

 // charset for emails
 define ('CHARSET', 'iso-8859-1');

 // Use the secure server for links if available, otherwise use HTTP
 $link_server = HTTP_SERVER;
 if (HTTPS_SERVER != '') {
$link_server = HTTPS_SERVER;
 }
// End of generic setup section


// Change the settings in this section as needed to match your store
 // Set the variables that will be the same for all emails
 $from_name = STORE_NAME . ' Customer Service';   // Change if a different FROM is desired
 $from_address = EMAIL_FROM;	   // Default is your store's FROM email; change if needed
 $site_domain = $_SERVER['SERVER_NAME']; // Your site domain; Must be of the form 'sample.com'
 $subject = sprintf ('Your recent %s order', STORE_NAME);   // Change if desired
 $priority = 3; // Email priority: 1 is high, 3 normal, 5 low
 // The following are also used to send a message to the store owner when this script runs
 $send_notification = true;			// Send an email to the Admin when this script runs
 $notice_subject = $site_domain . ' Review Email Run';  // Whatever subject you want to see
 $notice_message = "The " . $site_domain . " review reminder script finished execution on \n"; 
 $notice_message .= date ("d M Y H:i:s") . "\n\n";			  // When the script finished
// End of section to change


// Start the email reminder process  
 // Get the date past which we don't want to send reminders (too soon)
 $timestamp = time() - (REVIEW_REMINDER_DELAY * 24 * 60 * 60); // Delay in days convert to seconds

 // Get the Orders ID of all shipped orders prior to that date that have not already been emailed
 $orders_query = tep_db_query ("select customers_name,
									customers_email_address,
									orders_id,
									last_modified
							 from " . TABLE_ORDERS . "
							 where orders_status = '3'
							   and reminder_sent = '0'
						  "); 
 if (tep_db_num_rows ($orders_query)) { // Check if there are any orders

$orders_count = 0;
while ($orders_array = tep_db_fetch_array ($orders_query) ) { // Step through the orders
  if (strtotime ($orders_array['last_modified']) < $timestamp) { // Delay time has expired
	$orders_count++;
	// Set the customer/order data
	$customers_name = $orders_array['customers_name'];
	$customers_email_address = $orders_array['customers_email_address'];
	$orders_id = $orders_array['orders_id'];
	// Get the products info for the order
	$products_query = tep_db_query ("select products_name,
											products_model,
											products_id
									 from " . TABLE_ORDERS_PRODUCTS . "
									 where orders_id = '" . $orders_id . "'
								  ");
	if ($products_qty = tep_db_num_rows ($products_query)) { // There have to be products, but check for errors anyway
	  // Set plural forms where needed in the text
	  $each = 'the';
	  $link = 'link';
	  $products = 'product';
	  if ($products_qty > 1) {
		$each = 'one or more of the';
		$link = 'links';
		$products = 'products';
	  }
	  // Get a list of products and links to the review page for each
	  $products_string = '';
	  while ($products_array = tep_db_fetch_array ($products_query) ) { // Step through the products
		// Add each product and each review URL to a list
		$products_string .= $products_array['products_model'] . ' ' . $products_array['products_name'] . "\n";
		$products_string .= '  ' . $link_server . '/product_reviews_write.php?products_id=' . $products_array['products_id'] . "\n";
	  } //  while ($products_array
	} // if (tep_db_num_rows

	// Placeholder text from the text file to be replaced
	$search_array = array (
	  '%%customer_name%%',
	  '%%each%%',
	  '%%link%%',
	  '%%product_list%%',
	  '%%products%%',
	  '%%store_name%%',
	  '%%store_email%%',
	  '%%store_url%%',
	  '%%year%%'
	);
	// Replacement text -- must match the above array
	$replace_array = array (
	  $customers_name,
	  $each,
	  $link,
	  $products_string,
	  $products,
	  STORE_NAME,
	  $from_address,
	  HTTP_CATALOG_SERVER,
	  date ('Y')
	);

	// Get the message text
	$text_file = basename ($PHP_SELF, ".php") . '.tpl';
	$file_string = trim (implode ("", file ($text_file) ) );
	$message = str_replace ($search_array, $replace_array, $file_string); // Replace the generic text with custom variables

	// Send email message
	tep_mail ($customers_name, $customers_email_address, $subject, $message, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);

	// Mark the order to show the email was sent
	tep_db_query ("update " . TABLE_ORDERS . "
				   set reminder_sent = 1
				   where orders_id = " . $orders_id
				 );
  } // if (mktime
} // while ($orders_array
 } // if (tep_db_num_rows

// Email the admin when the script has run
 if ($send_notification) {
$notice_message .= $orders_count . " email messages were sent. \n"; // Add number sent to the message
// Send email message to the store owner
tep_mail(STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS, $notice_subject, $notice_message, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);
 }

?>

Next, copy the following code into a file and name it review_mail.tpl:

Dear %%customer_name%%

Thank you for your recent purchase from %%store_name%%. If you have any questions or concerns about your order, please let us know.

We would like to invite you to help others make an informed decision about our products. Please take a moment to rate and review %%each%% %%products%% that you ordered. Please be as honest as possible when giving your comments, whether positive or negative.

To review the %%products%% you ordered, please use the following %%link%%:

%%product_list%%

Thanks for shopping with us, and we hope to see you again soon.

%%store_name%% Customer Service
%%store_email%%
%%store_url%%

(c)1998-%%year%%, %%store_name%%

 

Both of these files should go into a folder named automatic. Upload that folder into your catalog directory.

 

Testing:

I would recommend running this on a test store until you have it working to your satisfaction. If you are running on a live store, make this database change first:

UPDATE `orders` SET `reminder_sent` = `1`

Now go make a test order using your own email address. Then use your database management software to change the orders_status column to 3 and the last_modified column to at least 11 days ago (for your test order only, if you are using a live store.)

 

To test this code, point your browser to the php file: http://www.sample.com/automatic/review_mail.php (change the URL to match your store.) You should see the first line of the file repeated at the top of your screen, and no error messages. You should also get an email to the store owner telling you that the script has run and sent one email, and a reminder email to the email address you used for the account that placed the order.

 

Once this is running properly, you need to set up a CRON job to make it run automatically. Once a day should be satisfactory for most stores. Use the full path to the above PHP file as the job to run. Note that the first line of the PHP file has to point to your copy of PHP. The line used in the file will work in most cases, but yours may be different. This will not affect manual operation, but it will prevent the CRON script from working properly. If you are having problems getting this to run automatically, this is the first thing to check. If you have command line access, running which php should return the location. If you are running Unix/Linux/etc. I have no idea on a Windows box. If you are running on a shared hosting service, ask your host where they have installed PHP.

 

Please post your results and/or questions here. I need victims ... err, beta testers. Yeah, those. :-"

 

Regards

Jim

[Richard Bailey]

This is just what I have been looking for, for years!!!

 

Thank you!!

 

When I run:

 

UPDATE `orders` SET `reminder_sent` = `1`

 

I get:

 

SQL query:

UPDATE `orders` SET `reminder_sent` = `1`

MySQL said: Documentation
#1054 - Unknown column '1' in 'field list' 

 

Any ideas?

 

Rich

Edited November 10, 2007 by Richard Bailey

[♥kymation]

I forgot to add a comment to this thread linking to the final version. See the link in my sig for the contribution download.

 

Regards

Jim