Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Guest

Recover Cart Sales

Recommended Posts

since installing this contribution (which is great other than this problem) whenever i search for a customer name i get
1064 - You have an error in your SQL syntax near 'where c.customers_lastname like '%NAME%' or c.customers_firstname like '%NAME%' at line 3

 

select count(*) as total from customers c, address_book a, sources s, customers_info i where s.sources_id = c.entry_source_id and i.customers_info_id = c.customers_id and c.customers_id = a.customers_id and c.customers_default_address_id = a. address_book_id where c.customers_lastname like '%NAME%' or c.customers_firstname like '%NAME%' or c.customers_email_address like '%NAME%'

 

[TEP STOP]

where NAME = the name searched for.

That error shouldn't be related to RCS, because you can't search for a customer in it. Sounds like you are getting this error in another part of OSC like the Customer Admin. If so this can't be related to RCS unless during the install you mistakely changed something dealing with the customer admin.

Share this post


Link to post
Share on other sites
you don't delete with recover carts. It's a sales tool. If you feel a need to "delete" a customer record, just delete the customer.

JM -- apparently Willross released a 1.4 version of RCS on March 31st 2004 and he says in the comments that he "Added 'delete' feature in two places" He doesn't say where those two places are and I am not using 1.4.

 

Saletco -- You may want to see if you can contact Willross some how, see if he put contact info in the readme of 1.4. Anything realted to changes in 1.4 I do not know anything about and it sounds like JM has it used it either.

Share this post


Link to post
Share on other sites
I just installed this contribution but it seems to be displaying sold carts as well as unsold carts. Is this normal?

Also when I click on the customer's name under the tool i get an error:

1064 - You have an error in your SQL syntax near 'where c.customers_lastname like '%Noland%' or c.customers_firstname like '%Nolan' at line 3

 

select count(*) as total from customers c, address_book a, sources s, customers_info i where s.sources_id = c.entry_source_id and i.customers_info_id = c.customers_id and c.customers_id = a.customers_id and c.customers_default_address_id = a. address_book_id where c.customers_lastname like '%Noland%' or c.customers_firstname like '%Noland%' or c.customers_email_address like '%Noland%'

This gives more info that the one I previous replied to. However RCS just gives it's customer request to:

 

/admin/customers.php

 

The search is then being handled by that file. The RCS tool just provides a LAST name for the search. You would have to trouble shoot the customers.php file as to why it doing this. I have tried and cannot replicate this issue.

Share this post


Link to post
Share on other sites
Ok, I just installed this contribution and did a test email.

 

The text sent has no formatting to it. Is this normal?

 

Thanks for any advice

It is sent as PLAIN TEXT and has only new lines for formating. If when you get the message it should have empty lines seperating information so that it is easier to read. This is normal and the safest way to send the message so that everyone can read it.

 

If it is coming in some other way, check to see what method your server is using to send messages and see if can possibly be changing the message, or check your e-mail software you use to view it to see if it is removing lines or something.

Share this post


Link to post
Share on other sites
Guest
you don't delete with recover carts. It's a sales tool. If you feel a need to "delete" a customer record, just delete the customer.

JM -- apparently Willross released a 1.4 version of RCS on March 31st 2004 and he says in the comments that he "Added 'delete' feature in two places" He doesn't say where those two places are and I am not using 1.4.

There is NO reason to mess with deletions. Since I started the application, including testing and such, the entire data table is 31 bytes and 1140 records. It's 2 int(11) and a varchar(8). It's NOTHING! I designed it KISS. To do the most with the least. In fact, I so under designed it that it's almost useless for the reporting system, which is why reporting on the application sucks!

 

Also, today I responded to a PM with some data about the application in use. included below for people who want to get an understanding of how the application actually functions as a part of my store. I run the application every other day using the static e-mail message that was updated by Aalst (with a few minor mods by me, but in line with the philosophy that this is a "support" e-mail, not a sales).

 

---

 

I do know that the way I have set the system up, I have a 10-15% recover rate on carts that have been abandoned in which I have contact information.

 

So, in the last 100 days I have had 428 carts left with contact information that I have contacted. 64 of those contacted have become paying customers (completed sales since contact). If my average net is $15 per cart recovered, that comes to $960 in income created by about 3 minutes work every other day. In terms of a return on investment that is $6.40/minute. B)

 

I could focus on what I am still losing because I don't have contact data, but I would rather focus on things that generate income rather than not.

 

---

 

As for reporting. I spent about a month looking at what I could do to generate a better report set. The truth is that the small size of the database doesn't allow for complex reporting that I wanted to be able to do since I don't capture enough data to effectively generate the report.

 

A true analytical report would do the following:

 

1) Permit me to look at X number of days

2) Show people that were contacted, the data of contact, and the value of their cart at the time of contact (also what was in it) as well as what was in it at the time of the completed sale, and any visits after the contact and what was added.

3) Provide totals for each of these fields.

 

This would show how effective the tool was at closing the initial sale, and then how effective the tool was at increasing (or decresing) the final sale against what was in the cart before the sale completed (at contact).

 

The next application, after this, would be to add a new feature to the RCS contact component that would look at what was in the customers cart when a contact e-mail is being sent, and then look at "what other people purchased" and see if any of those items were cross referenced between the items in the cart (ie: the customer has "bean bags" and "pens" in their cart and people who bought both "bean bags" and "pens" had also bought white boards) and point this out as well (expecially if "white boards" were currently on sale) to see if there is a possible way to use the tool not only to recover the cart, but to make an upsale suggestion.

 

But, as I stated, without having the data in the database (which should never need to be cleaned out, but if you want to write a cleanout routine, it should be one that goes through the entire database and drops all data associated to a customer_id from all tables when you remove the customer_id from the customers table) there is really no way to make an effective reporting tool as described above, and without that there will be no way to create an effective upsale tool as part of RCS.

Edited by Johnson

Share this post


Link to post
Share on other sites

Sorry for the previous post as a new topic...I meant to add it to this thread...

 

Hi,

 

This is the best tool yet...one customer recovered a couple of months ago has become my #1 customer! Many thanks for Jim and everyone who has tweaked and contributed along the way...

 

I added the 'Dear' piece and emailed myself just now, and I'm wondering if something changed in the email or if I'm imagining it -- didn't the email used to show the price of the item(s) with hyperlinks back to the product info page?

 

Thanks

 

Lee

Share this post


Link to post
Share on other sites
I added the 'Dear' piece and emailed myself just now, and I'm wondering if something changed in the email or if I'm imagining it -- didn't the email used to show the price of the item(s) with hyperlinks back to the product info page?

It has No Hyperlinks and No Prices since I been involved in with RCS. It is just a simple plain text message. Feel free to add those features if you wish.

Share this post


Link to post
Share on other sites
It has No Hyperlinks and No Prices since I been involved in with RCS. It is just a simple plain text message. Feel free to add those features if you wish.

Thanks for the response. May it was in the older scart that I saw that. I think I'll try adding the link...just to remind them what it was that captured their imagination in the first place.

 

I'm also going to change my 'reset' button to one labeled 'delete cart'. I know should be obvious, especially for someone liek me who has used this for a while, but in my early morning fog, a accidently deleted a cart that I meant to email!

 

P.S. Has anyone seen a hack to customers.php that adds a notation like the recover carts report to show which 'customers' who registered actually purchased? I can probably do it myself by following this mod's code, but hey, why reinvent the wheel? Thanks!

Share this post


Link to post
Share on other sites
I'm also going to change my 'reset' button to one labeled 'delete cart'. I know should be obvious, especially for someone liek me who has used this for a while, but in my early morning fog, a accidently deleted a cart that I meant to email!

Again I don't recommend running a RCS version with delete features.

P.S. Has anyone seen a hack to customers.php that adds a notation like the recover carts report to show which 'customers' who registered actually purchased? I can probably do it myself by following this mod's code, but hey, why reinvent the wheel? Thanks!

This is off topic but here is how you do it:

 

First you need to add a new line to the language file stats_customers.php in at least the language(s) you use. (admin/includes/languages/english/) Find the line:

define('TABLE_HEADING_CUSTOMERS', 'Customers');

Then add this line below it:

define('TABLE_HEADING_ORDERS', 'Orders');

Next you need to edit the file /admin/stats_customers.php and find the line (around line 55):

<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>

Then add this line below it:

<td class="dataTableHeadingContent" align=center><?php echo TABLE_HEADING_ORDERS; ?></td>

Then find this next line (around line 60):

$customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC";

Replace that line with this one:

$customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum, c.customers_id as cid from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC";

Next find the following line (around line 69):

while ($customers = tep_db_fetch_array($customers_query)) {

Right after this line add the following lines:

$customers_query_numords = tep_db_query("select orders_id from " . TABLE_ORDERS . " where customers_id ='".$customers['cid']."' ");
$orders = tep_db_num_rows($customers_query_numords);

Finally find this line (around line 80):

<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $customers['customers_lastname'], 'NONSSL') . '">' . $customers['customers_firstname'] . ' ' . $customers['customers_lastname'] . '</a>'; ?></td>

And add the following line after it:

<td class="dataTableContent" align=center><?php echo $orders; ?></td>

Presto... you know have a new column on your Customer Orders-Total report showing who your best customers are by dollar amount, and also showing the number of orders they have made to get that dollar amount.

 

I hope that helps you...

Share this post


Link to post
Share on other sites
Guest
I'm also going to change my 'reset' button to one labeled 'delete cart'. I know should be obvious, especially for someone liek me who has used this for a while, but in my early morning fog, a accidently deleted a cart that I meant to email!

Don't delete carts

 

If you want to make a delete routine, do it as part of a customer cleanup routine, not part of a SALES TOOL!

 

P.S. Has anyone seen a hack to customers.php that adds a notation like the recover carts report to show which 'customers' who registered actually purchased? I can probably do it myself by following this mod's code, but hey, why reinvent the wheel? Thanks!

 

it's called customer-cleanup and I've been playing with it. It actually allows you to set up a system that removes customers from your database, and cleans up carts, etc. As for knowing who your best customers are...

 

http://forums.oscommerce.com/index.php?showtopic=68185

Share this post


Link to post
Share on other sites

I receive an error

dvbhard/catalog/admin/FILENAME_REPORT_RECOVER_CART_SALES was not found on this server. Is there a file missing I do not have it in v1.4


I'm not a coder just a splicer.

Share this post


Link to post
Share on other sites

Recover cart is great,

But one thing if someone was in the process of making a purchase

who's to know they wouldn't have come back anyway.

So how can you prove that this email method is effective?

 

----------------------------

I'm using recover cart in hopes of having a copy of an order if it failes between

credit card company and the site,

ie credit card company says you have a sale. but you have no record?

There are many reasons why this can happen. no cookies, closed browser, lag so-on

 

So far the only answers i have found is this

Recover cart and ordercheck

 

ordercheck kinda works but it has to many features not needed and they dont match the database if you make changes, they also dont cross over descriptions. I'm sure a little work could get ordercheck mod to work. for the lost orders

 

Recover cart is not a fix

 

ie someone makes a purchase, this order is not logged with your site only the creditcard company, you dont have clue, what was ordered. So you look at recover cart, great. But the customer has been back before you and delete the old items thinking they have ordered allready. they may even make a new order.

Now you have no idea of the order, you post them the wrong items or email them,

saying how sorry you are, but you lost order info... Another happy customer?

 

Seem that a cross between Recover cart and ordercheck

would do the trick.

 

Anyone have an answer.

Share this post


Link to post
Share on other sites

Also how do you flush the scart ??? I see no buttons for flushing

Edited by RI Downlink

I'm not a coder just a splicer.

Share this post


Link to post
Share on other sites
Recover cart is great,

But one thing if someone was in the process of making a purchase

who's to know they wouldn't have come back anyway.

So how can you prove that this email method is effective?

There is a date stamp for the cart, I always wait at least 1 day before sending them a message. This prevents me from contacting soming in the process of shopping, and if someone maybe coming back that same day.

 

The chances of someone saving the cart for a future purchase is very very low. However as the message is worded they would not be offended by it and would most likely just let you know they are planning on returning.

 

Also how do you flush the scart ??? I see no buttons for flushing

That is because it doesn't take up hardly any space in the DB and there is no REAL need to delete or "flush" the information that RCS adds. The carts being saved in the DB is a function of the CORE OSC and is not something that RCS added.

 

The scart Table only containts two int(11)'s and a varchar(8) and I have been running this for over 6 months and I only have 4k of data in that table. I think if you look back a few pages of this topic you will see where JM posted how much space his scart table holds and he created this tool and the previous report and has more data in there than anyone and it actual space was used was nothing.

 

If you want to flush it, which I don't recommend, you can use what ever tool your host provides most likely phpMyAdmin and then just "Empty" the scart table.

 

-Aalst

Share this post


Link to post
Share on other sites

Hello I have installed the RecoverCartSales version 1.4 and I have a problem with the attributes, the price I know that one does not calculate well but it is that they appear to me triplicates underneath the name of the product. Somebody can help me? To somebody it has happened and it knows to him how to fix it?

Share this post


Link to post
Share on other sites
I have a problem with the attributes, the price I know that one does not calculate well but it is that they appear to me triplicates underneath the name of the product.

...

It either does not appear the special prices.

I believe it still displays the full price in the tool as a reference, it was a much more simple approach. However, when the e-mail is sent no pricing is sent since it is a customer service (soft sell) message.

 

As for the triplicate issue I do not really understand. Can you provide a link to a screen shot of the issue?

Share this post


Link to post
Share on other sites

I want to make a change, I have this:

 

$dest_country = $order->delivery['country']['iso_code_2'];

 

and it shows:

 

Shipping to ES

 

but I want that I show this:

 

Shipping to Spain

 

 

What change I would have to do?

 

Thanks very much

Share this post


Link to post
Share on other sites
I want to make a change, I have this:

 

$dest_country = $order->delivery['country']['iso_code_2'];

 

and it shows:

 

Shipping to ES

 

but I want that I show this:

 

Shipping to Spain

 

 

What change I would have to do?

 

Thanks very much

:blink:

 

Didn't you mean to post that in the Ship in Cart thread...?

 

Here: Ship in Cart thread

 

:rolleyes:

Share this post


Link to post
Share on other sites

First off, great mod. :) Love the simplicity of it all, works perfectly. One of the few mods I have installed that didn't require that I edit the code to get it to work.

 

Now this will seem like a stupid question (worthy of a 'who cares?!' response I suppose) but anyway ...

 

Can someone give me a hand in fixing the HTML for the recover_cart_sales.php so that the heading hits the top bar like everything else? It's got an extra 2 rows (1 of them split into 2 columns) above the "Recover Cart Sales" heading that shouldn't be there. Extremely minor I know, but I'm an anal perfectionist. lol

Edited by Acheron

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×