| Author |
 Topic  |
|
OrientalOutpost
Advanced Member
China
290 Posts Pre-sales questions only (More Details...)
|
 Posted - 01/07/2020 : 16:52:17
|
I like to see the lifetime value of a customer when I open a new order. So I wrote a routine for this a few years ago that initially just found repeat customers and linked to their past orders. I think this version is ready for prime time.
With this, you can see the basics of those former orders (links to orders, addresses, amounts). This is good if you need to double-check when the address they entered on a current order seems funky (check how they entered it last time), or you want to see what they bought before to check if it matches what they are getting now. Those details are way at the bottom, out of the way unless you need them. The fact they are a repeat customer and their lifetime value is at the top.
It cross-references by name, email, and mailing address. So it catches most repeat customers, even if they enter their name differently, change an email address, or move. If they change all 3, all bets are off.
In incorders.php around line 1581 in the current file, find this:
if($isprinter && ! @isset($packingslipheader)) $packingslipheader=@$invoiceheader;
And add this after:
// Next Gary's special check for repeat customers
if(!$isprinter) {
$noted = FALSE;
$rSQL = "SELECT ordID,ordName,ordAddress,ordAddress2,ordCity,ordState,ordZip,ordCountry,ordShipName,ordShipAddress,ordShipAddress2,ordShipCity,ordShipState,ordShipZip,ordShipCountry,ordTotal,ordDate FROM orders WHERE ordEmail='" . escape_string($alldata["ordEmail"]) . "' OR ordName='" . addslashes($alldata["ordName"]) . "'";
if(addslashes($alldata["ordZip"]) != '') $rSQL .= " OR (ordAddress='" . addslashes($alldata['ordAddress']) . "' AND ordZip='" . addslashes($alldata['ordZip']) . "')";
if(addslashes($alldata["ordShipAddress"]) != '' && addslashes($alldata['ordShipZip'] != '')) $rSQL .= " OR (ordShipAddress='" . addslashes($alldata['ordShipAddress']) . "' AND ordShipZip='" . addslashes($alldata['ordShipZip']) . "')";
$repeatSQL = ect_query($rSQL) or ect_error();
while($result = ect_fetch_assoc($repeatSQL)){
if($alldata['ordID'] != $result['ordID']) {
if($result['ordName'] !='' && !$noted) {
print ' <span style="color:red"> REPEAT CUSTOMER</span>';
$noted = TRUE;
}
$recordsTable .= '<tr><td><a href="adminorders.php?id=' . $result['ordID'] . '" target="_new">' . $result['ordID'] . '</a></td><td>' . $result['ordName'] . '</td><td>' . $result['ordAddress'] . '</td><td>' . $result['ordAddress2'] . '</td><td>' . $result['ordCity'] . '</td><td>' . $result['ordState'] . '</td><td>' . $result['ordZip'] . '</td><td>' . str_replace('United States of America','USA',$result['ordCountry']) . '</td><td>' . $result['ordShipName'] . '</td><td>' . $result['ordShipAddress'] . '</td><td>' . $result['ordShipAddress2'] . '</td><td>' . $result['ordShipCity'] . '</td><td>' . $result['ordShipState'] . '</td><td>' . $result['ordShipZip'] . '</td><td>' . str_replace('United States of America','USA',$result['ordShipCountry']) . '</td><td>' . $result['ordTotal'] . '</td><td>' . substr($result['ordDate'],0,10) . '</td></tr>';
}
$lifetimeValue = $lifetimeValue + $result['ordTotal'];
}
if(ect_num_rows($repeatSQL) > 1) print '<h2>Lifetime Value: $' . $lifetimeValue . '</h2>';
ect_free_result($repeatSQL);
}
// End of repeat customer routine
At the very end of incorders.php, add this:
<?php print '<table class="cobtbl">' . $recordsTable . '</table>';?>
https://www.OrientalOutpost.com |
|
dbdave
ECT Moderator
USA
8600 Posts |
Posted - 01/07/2020 : 17:35:25
|
I too have something like this for a while.
It's .asp though.
I use graphical icons that are dynamic with a number count next to them.
We match name, email, and IP.
We also indicate if the customer has a client account and if they were logged into it when placing the order.
The buttons are clickable. You can click to view matched orders, or the client account button will take you to that clients account.
We also show the client notes when they exist.
NO MATCHES FOUND
MATCHES FOUND - 16 to email, 7 to name, 10 to IP and the red key indicates the customer has a client account, but was not logged in. Green key would indicate they were logged in.
Perhaps if there is interest, ect might consider this a native feature... (hint hint...  ) |
 |
|
DLSS
ECT Moderator
Canada
3853 Posts |
|
dbdave
ECT Moderator
USA
8600 Posts |
Posted - 01/08/2020 : 08:49:35
|
I do use Mike's tool and it works pretty good, but we wanted some expanded features.
We needed to match orders not just on email, but also name and IP.
We also added the client login stuff.
Mike's tool is a great product and easy to install and will probably work for most users.  |
|
|
Sinbad
Administrator
65840 Posts |
|
| |
Topic |
|
|
|
See repeat customers and lifetime value
