[ic] Looking for a way to send a follow-up e-mail 7days after
shipment
Josh Lavin
josh at myprivacy.ca
Thu Apr 19 14:09:21 EDT 2007
On Apr 18, 2007, at 6:24 PM, Kevin Walsh wrote:
> "Eric Chamberlain" <eric at voxilla.com> wrote:
>> I'm trying to figure out how to send a follow-up e-mail to
>> customers a
>> week or so after their order ships. The text should be customized
>> based
>> on what items were in their order.
>>
>> Can anyone point me in the right direction?
>>
> I'd just do this with a daily Interchange cron job.
>
> * Select all orders where the shipping date is < the current
> date + 7
> days and a new transactions.order_followup column is set to a
> false
> value. When each order has been followed-up, set the new column
> to a true value, so that it is not selected and re-processed
> later.
>
> * For each selected order, loop through the orderlines to make a
> table, or whatever. Do this inside an [email] tag, so that the
> results are sent to the user. You don't need to simply dump the
> order into the email, of course - you could use the information
> to customise the email text in any way you see fit, as you
> suggested.
>
> You could equally do the above using a plain Perl script, called from
> cron, but it's always nice to be able to make use of Interchange's
> markup language for selecting orders, templating and sending the
> emails etc., rather than reinventing the wheel.
I've been doing this to send out satisfaction surveys several days
after an order is shipped. I just use a perl script run once a day by
cron. Kevin's suggestions are probably better, but here's my code FWIW.
Watch out for breaking lines.
At end of etc/receipt.html:
[comment] start survey records [/comment]
[seti timeplus12][convert-date adjust="12" format="%Y-%m-%d"][/
convert-date][/seti]
[if value b_fname][tmp first][value b_fname][/tmp][tmp last][value
b_lname][/tmp][else][tmp first][value fname][/tmp][tmp last][value
lname][/tmp][/else][/if]
[flag type=write tables="survey_postship1"]
[perl tables="survey_postship1"]
my $db = $Db{survey_postship1};
my $status = $db->set_slice(
$Values->{mv_order_number},
{
fname => $Scratch->{first},
lname => $Scratch->{last},
email => $Values->{email},
date => $Scratch->{timeplus12},
}
);
$status = $status ? 'SUCCESS' : 'FAILED to set in db';
return;
[/perl]
Perl script:
#!/usr/bin/perl
use DBI;
use POSIX qw(strftime);
use strict;
my $host = 'localhost';
my $database = 'blah';
my $user = 'blah';
my $password = 'blah';
my $company = 'Blah Company';
my $subcompany = 'Company formatted for Subject';
my $mailprog = "/usr/sbin/sendmail -t";
my ($code, @delete_uids) = '';
my $dbh = DBI->connect("dbi:mysql:$database:$host", $user, $password);
my $sth = $dbh->prepare('SELECT s.code, s.fname, s.lname, s.email,
s.date, o.description, t.status FROM survey_postship1 as s, orderline
as o, transactions as t WHERE s.code=o.order_number AND
s.code=t.order_number GROUP BY s.code');
$sth->execute() or die $dbh->errstr;
while (my @data = $sth->fetchrow_array()) {
my $code = $data[0];
my $fname = $data[1];
my $lname = $data[2];
my $email = $data[3];
my $date = $data[4];
my $item = $data[5];
my $status = $data[6];
if (&is_time($date,$status)) {
&mail_user($code,$email,$fname,$lname,$item);
push (@delete_uids, $code);
}
}
foreach $code (@delete_uids) {
my $sql = q{
DELETE FROM survey_postship1
WHERE code = ?
};
$sth = $dbh->prepare($sql);
$sth->execute($code);
}
$sth->finish();
$dbh->disconnect;
sub is_time {
my $date = $_[0];
my $status = $_[1];
my $current_date = strftime("%Y-%m-%d",localtime);
my $yesno;
if ( ($current_date ge $date) && ($status eq 'shipped') ) {
$yesno = 1;
} else {
$yesno = 0;
}
return ($yesno);
}
sub mail_user {
my ($code, $email, $fname, $lname, $item) = @_;
open (MAIL, "|$mailprog") || die "Can't open $mailprog $_";
print MAIL "To: $email\n";
print MAIL "From: Sum Gui <webmaster\@blahco.com>\n";
print MAIL "Subject: $subcompany order $code follow-up\n\n";
print MAIL <<MAIL_TEXT__;
Dear $fname,
Thank you for your recent order. We hope you are pleased with the
items you receieved. If your order has not been delivered yet, or
you have any problems with it, please visit our Help page:
http://www.blahco.com/help
We are constantly striving to please our customers. Please help
us improve by completing our short 7-question survey. It should
take you 1-3 minutes.
http://s.blahco.com/3
At the end of the month, one respondent will be randomly
selected to win a \$25 gift certificate.
As a reminder, one of the items you ordered was:
$item
Thanks again for shopping at blahco.com!
Sincerely,
Sum Gui
$company
MAIL_TEXT__
close(MAIL);
}
--
Josh Lavin
Kingdom Design http://www.kingdomdesign.com/
More information about the interchange-users
mailing list