Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Abandoned Carts


Guest

Recommended Posts

Have you ever wondered what customers you have that have abandoned carts (they signed up and created accounts, and then never completed the checkout process)? Well, I did. So, I determined that I had all this data in the system, and I wasn't using it to follow up and determine WHY my customers were not completing the purchase, expecially after giving me all this data. So, I created a report (after asking here and not getting an answer) that allows me to dump out all the carts with things in them, and also show me the customers name and phone number so I can take proactive customer support and call them up to find out how *I* failed to meet their needs.

 

Now, this is a hack job, but it works in that it creates an exception report that lists all the customers that have abandoned carts and have completed the creation of an account. I'm sure that someone who is a better OSC hacker than I can add the refinements that will allow it to page, select the last X number of days, etc. Here is the code. Have fun.

 

<?php
/*
 $Id$

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

 Copyright (c) 2002 osCommerce

 Released under the GNU General Public License

 Modified by JM Ivler
 Oct 8th, 2003
*/

 require('includes/application_top.php');

 require(DIR_WS_CLASSES . 'currencies.php');
 $currencies = new currencies();

?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET;
?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin
="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<
?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
   </table></td>
<!-- body_text //-->
   <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0"
cellpadding="2">
     <tr>
       <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
           <td class="pageHeading">Abandoned Cart Report</td>
           <td class="pageHeading" align="right"><?php echo tep_draw_separator(
'pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
         </tr>
       </table></td>
     </tr>
     <tr>
       <td class="smallText">
      <tr class="dataTableHeadingRow">
       <td class="dataTableHeadingContent">Customer</td>
       <td class="dataTableHeadingContent" colspan="1 width="150">Cart</td>
      </tr>

<?php
 $query1 =
     tep_db_query("select cb.customers_id as cid, cb.products_id as pid, cb.cus
tomers_basket_quantity as qty,  cb.customers_basket_date_added as bdate, cus.cus
tomers_firstname as fname, cus.customers_lastname as lname, cus.customers_teleph
one as phone from customers_basket as cb, customers as cus where cb.customers_id
= cus.customers_id order by cb.customers_basket_date_added, cb.customers_id ");
 $results = 0;
 $curcus = "";
 $tprice = 0;
 $knt = mysql_num_rows($query1);
 for ($i = 0; $i < $knt; $i++) {
    $inrec = tep_db_fetch_array($query1);
//     echo " ~~ ".$curcus." == ".$inrec['cid']." ~~ " .$inrec['fname']." ~~ ";
    if ($curcus != $inrec['cid']) {
// output line
      $cline .= "<tr><td><b>Total: </b>".$tprice."</td></tr></table></td></tr>"
;
      if ($curcus != "") echo $cline;
// set new cline and curcus
      $curcus = $inrec['cid'];
      $tprice = 0;
      $cline = "<tr><td valign=top>".$inrec['fname']." ".$inrec['lname']." ".$i
nrec['phone']."</td><td><table>";
    }
// empty the shopping cart
 $query2 =
     tep_db_query("select p.products_price as price, pd.products_name as name f
rom products as p, products_description as pd where p.products_id = '".$inrec['p
id']."' and pd.products_id = p.products_id");
 $inrec2 = tep_db_fetch_array($query2);
 $tprice = $tprice + ($inrec['qty'] * $inrec2['price']);
 $cline .= "<tr><td class=smalltext>".$inrec['qty']." x ".$inrec2['name']." @ "
.$inrec2['price']." (".$inrec['bdate'].")</td><tr>";
 }

  $cline .= "<tr><td><b>Total: </b>".$tprice."</td></tr></table></td></tr>";
  echo $cline;

?>

    </tr>
   </table>
  </td>
  </tr>
 </table></td>
<!-- body_text_eof //-->
</tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Link to comment
Share on other sites

  • Replies 211
  • Created
  • Last Reply

Top Posters In This Topic

Now that is pretty neat, JM-

Thanks for sharing with us!

 

If someone is into adding to it, being able to select a given cart from the report and actually remove it would be a handy tool (say after 3 or 6 months), as the list (and the space used) could grow quite large over time.

... if you want to REALLY see something that doesn't set up right out of the box without some tweaking,

try being a Foster Parent!

Link to comment
Share on other sites

updated and upgraded, now sorts with the latest first and then adds the users e-mail with a link so you can just pop on it and send them an e-mail... Next I'm going to add a "limiter" tom it so that you can select the number of days in history (as the prior message stated, a long list is a pain in the butt, well he didn't actually say that, but that's what I read :-) ).

 

<?php
/*
 $Id$

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

 Copyright (c) 2002 osCommerce

 Released under the GNU General Public License

 Modified by JM Ivler
 Oct 8th, 2003
*/

 require('includes/application_top.php');

 require(DIR_WS_CLASSES . 'currencies.php');
 $currencies = new currencies();

?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET;
?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin
="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<
?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
   </table></td>
<!-- body_text //-->
   <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0"
cellpadding="2">
     <tr>
       <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
           <td class="pageHeading">Abandoned Cart Report</td>
           <td class="pageHeading" align="right"><?php echo tep_draw_separator(
'pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
         </tr>
       </table></td>
     </tr>
     <tr>
       <td class="smallText">
      <tr class="dataTableHeadingRow">
       <td class="dataTableHeadingContent">Customer</td>
       <td class="dataTableHeadingContent" colspan="1 width="150">Cart</td>
      </tr>

<?php
 $query1 =
     tep_db_query("select cb.customers_id as cid, cb.products_id as pid, cb.cus
tomers_basket_quantity as qty,  cb.customers_basket_date_added as bdate, cus.cus
tomers_firstname as fname, cus.customers_lastname as lname, cus.customers_teleph
one as phone, cus.customers_email_address as email from customers_basket as cb,
customers as cus where cb.customers_id = cus.customers_id order by cb.customers_
basket_date_added desc, cb.customers_id ");
 $results = 0;
 $curcus = "";
 $tprice = 0;
 $knt = mysql_num_rows($query1);
 for ($i = 0; $i < $knt; $i++) {
    $inrec = tep_db_fetch_array($query1);
//     echo " ~~ ".$curcus." == ".$inrec['cid']." ~~ " .$inrec['fname']." ~~ ";
    if ($curcus != $inrec['cid']) {
// output line
      $cline .= "<tr><td><b>Total: </b>".$tprice."</td></tr></table></td></tr>"
;
      if ($curcus != "") echo $cline;
// set new cline and curcus
      $curcus = $inrec['cid'];
      $tprice = 0;
      $cline = "<tr><td valign=top>".$inrec['fname']." ".$inrec['lname']." ".$i
nrec['phone']."<br><a href=mailto:".$inrec['email'].">".$inrec['email']."</a></t
d><td><table>";
    }
// empty the shopping cart
 $query2 =
     tep_db_query("select p.products_price as price, pd.products_name as name f
rom products as p, products_description as pd where p.products_id = '".$inrec['p
id']."' and pd.products_id = p.products_id");
 $inrec2 = tep_db_fetch_array($query2);
 $tprice = $tprice + ($inrec['qty'] * $inrec2['price']);
 $cline .= "<tr><td class=smalltext>".$inrec['qty']." x ".$inrec2['name']." @ "
.$inrec2['price']." (".$inrec['bdate'].")</td><tr>";
 }

  $cline .= "<tr><td><b>Total: </b>".$tprice."</td></tr></table></td></tr>";
  echo $cline;

?>

    </tr>
   </table>
  </td>
  </tr>
 </table></td>
<!-- body_text_eof //-->
</tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Link to comment
Share on other sites

Well, OK... I didn't actually say that, but if you're willing to use that terminology, so am I! ;)

 

This is great - It never occured to me (there are so many other things to keep one busy) but, now that you've done such a marvelous job in creating it, I'm sure to make use of it

 

Thanks again!

... if you want to REALLY see something that doesn't set up right out of the box without some tweaking,

try being a Foster Parent!

Link to comment
Share on other sites

Added a very minor enhancement - a separator between each entry.

To add this, change line 79 from

      $cline = "<tr><td valign=top>".$inrec['fname']." ".$inrec['lname']." ".$inrec['phone']."<br><a href=mailto:".$inrec['email'].">".$inrec['email']."</a></td><td><table>";

to

      $cline = "<tr><td valign=top colspan=2><hr size=1 color=000080></td></tr><tr><td valign=top>".$inrec['fname']." ".$inrec['lname']." ".$inrec['phone']."<br><a href=mailto:".$inrec['email'].">".$inrec['email']."</a></td><td><table>";

... if you want to REALLY see something that doesn't set up right out of the box without some tweaking,

try being a Foster Parent!

Link to comment
Share on other sites

Three releases in one day... Okay, this version allows you to set the number of days into the history that you wish to view. The default is 10 days, but you can change that number and reload the page. It also has the record break addition/contribution from mugitty.

 

All-in-all I would say I'm done messing around with it. But then again, that's what I thought when I put the first version up. :-)

 

Have fun.

 

<?php
/*
 $Id$

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

 Copyright (c) 2002 osCommerce

 Released under the GNU General Public License

 Modified by JM Ivler
 Oct 8th, 2003
*/

 require('includes/application_top.php');

 require(DIR_WS_CLASSES . 'currencies.php');
 $currencies = new currencies();

?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET;
?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin
="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<?
function seadate($day) {
$ts = date("U");
$rawtime = strtotime("-".$day." days", $ts);
$ndate = date("Ymd", $rawtime);
return $ndate;
}
?>
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<
?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
   </table></td>
<!-- body_text //-->
   <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0"
cellpadding="2">
     <tr>
       <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
 <?
   $tdate = $_POST['tdate'];
   if ($_POST['tdate'] == '') $tdate = '10';
 ?>
           <td class="pageHeading">Abandoned Cart Report</td>
           <td class="pageHeading" align="right"><form method=post action=<? ec
ho $PHP_SELF;?> > <table><tr><td>Last </td><td><input type=text size=4 width=4 v
alue=<? echo $tdate; ?> name=tdate> </td><td>Days </td><td><input type=submit va
lue="New Run"> </td></tr></table></form> <?php echo tep_draw_separator('pixel_tr
ans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
         </tr>
       </table></td>
     </tr>
     <tr>
       <td class="smallText">
      <tr class="dataTableHeadingRow">
       <td class="dataTableHeadingContent">Customer</td>
       <td class="dataTableHeadingContent" colspan="1 width="150">Cart</td>
      </tr>

<?php
 $tdate = $_POST['tdate'];
 if ($_POST['tdate'] == '') $tdate = '10';
 $ndate = seadate($tdate);
//  echo $ndate; // debug line, shows the date we are seeking
 $query1 =
     tep_db_query("select cb.customers_id as cid, cb.products_id as pid, cb.cus
tomers_basket_quantity as qty,  cb.customers_basket_date_added as bdate, cus.cus
tomers_firstname as fname, cus.customers_lastname as lname, cus.customers_teleph
one as phone, cus.customers_email_address as email from customers_basket as cb,
customers as cus where cb.customers_basket_date_added >= '".$ndate."' and cb.cus
tomers_id = cus.customers_id order by cb.customers_basket_date_added desc, cb.cu
stomers_id ");
 $results = 0;
 $curcus = "";
 $tprice = 0;
 $knt = mysql_num_rows($query1);
 for ($i = 0; $i < $knt; $i++) {
    $inrec = tep_db_fetch_array($query1);
//     echo " ~~ ".$curcus." == ".$inrec['cid']." ~~ " .$inrec['fname']." ~~ ";
    if ($curcus != $inrec['cid']) {
// output line
      $cline .= "<tr><td><b>Total: </b>".$tprice."</td></tr></table></td></tr>"
;
      if ($curcus != "") echo $cline;
// set new cline and curcus
      $curcus = $inrec['cid'];
      $tprice = 0;
      $cline = "<tr><td valign=top colspan=2><hr size=1 color=000080></td></tr>
<tr><td valign=top>".$inrec['fname']." ".$inrec['lname']." ".$inrec['phone']."<b
r><a href=mailto:".$inrec['email'].">".$inrec['email']."</a></td><td><table>";
    }
// empty the shopping cart
 $query2 =
     tep_db_query("select p.products_price as price, pd.products_name as name f
rom products as p, products_description as pd where p.products_id = '".$inrec['p
id']."' and pd.products_id = p.products_id");
 $inrec2 = tep_db_fetch_array($query2);
 $tprice = $tprice + ($inrec['qty'] * $inrec2['price']);
 $cline .= "<tr><td class=smalltext>".$inrec['qty']." x ".$inrec2['name']." @ "
.$inrec2['price']." (".$inrec['bdate'].")</td><tr>";
 }

  $cline .= "<tr><td><b>Total: </b>".$tprice."</td></tr></table></td></tr>";
  echo $cline;

?>

    </tr>
   </table>
  </td>
  </tr>
 </table></td>
<!-- body_text_eof //-->
</tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Link to comment
Share on other sites

  • 2 weeks later...

Put it in the top level of your "admin" directory, you can call it with "http://yoursite.com/admin/abandoned.php" (or whatever file name you choose)

... if you want to REALLY see something that doesn't set up right out of the box without some tweaking,

try being a Foster Parent!

Link to comment
Share on other sites

Wow! I'm surprised I didn't find this jewel earlier.

 

Very Nice!

 

Shocked at the amount of *missed* sales I see. Heck just ran the last 30 days and see over $5,000..... <_<

 

I like this. I might have some ideas for this great contrib and will post when I get them together!

Link to comment
Share on other sites

Could anyone who has used this post some feedback on the results?

 

I'm interested in whether the customers minded being contacted, and if they didn't, what kind of results you got.

 

I'm a bit put off by trying this at it seems a bit like big brother watching over.

 

Thanks,

 

Jon.

Link to comment
Share on other sites

If you dont feel comfortable about it then dont make a contact. You also might want to change your privacy policy if you do want to contact them.

 

This could help make a sale in a variety of ways without having to contact a customer. Analysis is key.

Link to comment
Share on other sites

I can only tell you how it is used at edu4kids (my store) and h2opoolproducts (my partners store).

 

1) We both run the analysis every day

2) based on the size of the sale we either

a) send an e-mail (low end sales)

B) call (high end sales)

 

This is a sample e-mail that I send:

 

We noticed  that you were at our store and that you placed an <item's in cart> in your shopping cart, but then didn't complete the purchase. As we are a new store and are trying to make sure that the customer experience is a positive one, we would appreciate a moment of your time and would like to ask you if there was something that we could have done better to help you have completed the purchase? Was the price too high? Did you not like the shipping options? Was there something about the site that stopped you from completing the purchase?

 

We thank you in advance for your time in helping us help customers like you have a better shopping experience with Education 4 Kids.

 

So far I have had no negative impact from these e-mail, and two of the responses have lead to me adding FAQ items.

 

As for phone calls, at least 20% have led to converted sales.

 

One person was "upset" that I was "big brother"-like (an e-mail), but when I followed it up with a phone call and we talked, they understood and while they didn't make that sale (they had bought the items elsewhere) they did come back in and make another purchase.

 

This tool is much like "User Tracking" in that it provides insight on what you are doing well, and what you aren't. In fact, one of the next things I'm thinking of "tying" to the code is the ability (if you have user tracking installed) to launch a window which shows the clickpath captured by "User Tracking" so you can see what is actually going on with the users session before you contact the user.

 

Even if you don't use this to contact the customer, knowing what is getting left unsold is s great way to determine where you should be focusing your specials. :-)

 

Oh yes, one minor defect in the code that I found (and since I wrote it, I can spill the beans). I didn't do a very nice join in the SQL so, if you have a person who came into the shop, loaded the cart on Monday, left, came back on Wednesday and added some items and then left again, they show up as two separate entries rather than one entry. But then again, I said this was a hack. :-)

Link to comment
Share on other sites

Great!! I asked for something like this a while ago, but I don't have the knowledge to create it.

 

How would one add a link from the admin to this page so you don't have to type in the URL each time? (Apologies if this is already in the contrib instructions - I haven't actually looked yet)

Link to comment
Share on other sites

^Forget the above, I found it.

 

But it might be an idea to alter the readme text to say specifically where in the boxes/reports.php the link code should go. Non programmers wouldn't know where to put it.

 

Perhaps change it to say.

Find:

if ($selected_box == 'reports') {
   $contents[] = array('text'  =>

 

and immediately after it insert:

'<a href="' . tep_href_link(FILENAME_UNSOLD_CARTS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_UNSOLD_CARTS . '</a><br>' .

Link to comment
Share on other sites

Hallo,

 

first of all - verry good contribution.

 

Now like everytime:

 

1. Would be very good to have the prices incl. TAX

2. A button beside each bascet to delete the information from database

3. the format of the date also in german format dd.mm.yyyy

 

Reason for my whish:

 

I want to copy and paste the text to a mail and send it with a lovly letter to the owner.......

Thanks Matthias

Link to comment
Share on other sites

YES!

 

Just installed this this afternoon, sent off my emails and already had one customer come back and not only purchase the items in her basket but also added ?25 more!!

 

Thank you for this!

Link to comment
Share on other sites

Ok...I have an idea based on comments here.

 

How about a contact button which will send emails to users with predefined text. I don't see the sense in making a copy and paste from a bunch of emails when basically the email will be the same each time. The email can even pull certain info like the cart items to remind the shopper of what it was they were looking for.

 

Anyone care to code it?

Link to comment
Share on other sites

This is a great contribution but when I go into my admin and reports this is ontop of the page Configuration

 

 

'' . BOX_REPORTS_UNSOLD_CARTS . '

' .

 

And when you click on it, it comes up with a page not found error.

 

Can anyone tell me what I have done wrong?

 

Thanks

 

Mike

Link to comment
Share on other sites

Ok...I have an idea based on comments here.

 

How about a contact button which will send emails to users with predefined text. I don't see the sense in making a copy and paste from a bunch of emails when basically the email will be the same each time. The email can even pull certain info like the cart items to remind the shopper of what it was they were looking for.

 

Anyone care to code it?

Working on a separate table to the database called contact_carts. Adding the following functionality.

 

1) checkboxes to contact via e-mail

2) standard text to be sent that includes the cart items

3) different colour username if contacted already

4) ability to *not* show already contacted

 

Give me a week or two and then I'll have the latest upgrades.

 

Considering adding a new feature that allows you to back-notify anyone who has something in their cart who you have the e-mail on when you put an item on sale. Might make this a separate application though.

Link to comment
Share on other sites

Hallo,

 

first of all - verry good contribution.

 

Now like everytime:

 

1. Would be very good to have the prices incl. TAX

2. A button beside each bascet to delete the information from database

3. the format of the date also in german format dd.mm.yyyy

 

Reason for my whish:

 

I want to copy and paste the text to a mail and send it with a lovly letter to the owner.......

1) the system pulls the current price, not even the sales price (if it exists). Pricing is for you, so you can determine if you want to contact them, and how. As for adding tax... I have seen the headache tax is. It's too freaking painful. Theres VAT, and no-VAT and then tax based on the codes... Since the prices are just for you, so you can determine if you want to contact the customer, and how, adding that information is really overkill for the application.

 

2) Why would you want to "delete" information from the cart? The idea is to get the sale to happen, not eliminate the potential for the sale. Heck, they may come back a year later and make the purchase based on what they had in their cart...

 

3) the date format is taken as stored from the tables - feel free to run the appropriate php date -format function to suite it to your needs.

Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
×
×
  • Create New...