[ic] Looking for a way to send a follow-up e-mail 7days after shipment

[Josh Lavin]

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/