Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Inactive Users/Customers


Recommended Posts

I'm looking for a contribution to delete customers who either haven't purchased in a while or perhaps haven't purchased at all.  I'd like to clean up my database a bit.   I found this contribution....

 

Inactive Users

 

and was wondering if anyone is using it and if so what you thought of it.   If not, what are you using if anything?

 

Dan

Link to comment
Share on other sites

Be careful about deleting old customer records. They could be useful for sending ticklers to remind them that you exist and would welcome them back. Some may have forgotten about signing up with you. Just don't run afoul of any anti-spam laws or "don't send me any marketing emails" choices they've made. I don't know about Canada, but in the U.S., you have a "pre-existing" relationship with this customer, so it would not be legally considered spamming to remind them that you haven't seen them in a while, unless they have explicitly told you they don't want to receive such emails. You might add something in your email to let them do a "one click" removal from your database (from their point of view, even if you have to actually delete them manually), if they don't want to receive any future emails from you.

 

I would also be leery of any code that goes ahead and does the deletion automatically -- if something went wrong, it might wipe out your entire customer database! At the very least, I would only use something that first lists all the proposed deletions (including last activity date), and asks me for permission to delete them.

Link to comment
Share on other sites

@@Dan Cole
Sending an email as @@MrPhil points out, might get you a sale or two, especially if you can target these inactive customers with a small discount code or free gift or such...

That said, if you have already considered this and thought not to...run (please note, this is completely untested and might be to harsh for your needs!;

Get rid of all customers who never made an order

delete from customers where customers_id not in (select customers_id from orders)

Now get rid of supplementary details of these customers

delete from address_book where customers_id not in (select customers_id from customers)
delete from customers_info where customers_info_id not in (select customers_id from customers)
Link to comment
Share on other sites

Thanks guys... :thumbsup:

 

I haven't looked at our spam regulations recently but I seem to recall they had to be customers within the last two years and I'm thinking about cleaning up things prior to that so I should still retain the customers I can market to.   I'll have a look at the Database Optimizer also...I didn't realize that would do what I want and you can be sure I'll backup my database before messing with this.  :rolleyes:

 

Dan

Link to comment
Share on other sites

@@Jack_mcs will your Database Optimizer also delete customers with orders but who haven't ordered within a certain period of time?  I looked at the add on history and it refers to customers who haven't ordered at all but not customers who have placed orders at one time or another.  It also refers to needing to set up a cron job so I'm left wondering if it can just be run from time to time?

 

Dan

Link to comment
Share on other sites

@@Dan Cole Here is the list of what it does. When it is installed, there will be a section in Tools where you can run it manually so a cron job is not required. There are settings for each of the options to control when that option is used. So removing customer sessions can be set to 4 days while removing old customers can be set to a year, or vice versa.

 

  • Perform Analyze  Cleans up the keys in the tables. Improves the speed of the database.
  • Perform Optimize  Defragments the database and reduces query times in some cases.
  • Remove Customer Sessions  Removes entries from the customer basket and customer basket attributes tables based on the configuration setting.
  • Remove Old Customers  Removes entries from the customers and address book tables based on the configuration setting and whether the customer has ever logged in.
  • Remove Product Notifications  Removes entries from the products notification table when the customer and/or product no longer exist.
  • Remove CC Numbers  Removes credit card numbers from the orders table based on the configuration setting.
  • Remove Orphan Address Book Entries  Removes entries from the address book table that are not related to a customer.
  • Remove Sessions  Removes entries from the sessions table based on the configuration setting.
  • Remove User Tracking (if that addon is installed)

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

@@Jack_mcs  Thanks Jack....I'm still not clear on this but I'll fire it up and we'll see....

 

 

I looked at the add on history and it refers to customers who haven't ordered at all but not customers who have placed orders at one time or another.

 

Dan

Link to comment
Share on other sites

I'm sorry, I didn't understand what you were asking but I see I missed that in your original post too. No, it doesn't delete customers that have ordered before. It did do that in one of the versions but users asked that I remove that. So I added a check to see if an order had been placed and to skip the deletion if they had. That could be taken out if you want that ability back in. Just let me know and I will look up the code to remove.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

No problem Jack and I would love to have the option to delete customers if they haven't order for a period of time. 

 

Dan

Link to comment
Share on other sites

I wouldn't simply delete anyone who created an account but never ordered, without also checking that it was quite a while ago. I could see someone saying, "I'm not quite ready to order, but this site looks good so I'll go ahead and create an account." Then they come back a week later and their account is gone. They'll be gone, too!

Link to comment
Share on other sites

@@Dan Cole In the admin/includes/modules/database_optimizer_common.php file, remove the following line:

if (tep_db_num_rows($orders_query) == 1) continue; //skip customers with orders

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

I wouldn't simply delete anyone who created an account but never ordered, without also checking that it was quite a while ago. I could see someone saying, "I'm not quite ready to order, but this site looks good so I'll go ahead and create an account." Then they come back a week later and their account is gone. They'll be gone, too!

 

@@MrPhil I was thinking those who haven't ordered or logged in for a couple of years but we'll see what flexibility Jacks addon provides.  The ideal length of time probably depends on the type of business you're in and the typical frequency of the orders that are usually placed.

 

Dan

Link to comment
Share on other sites

@@Jack_mcs

 

 

@@Dan Cole In the admin/includes/modules/database_optimizer_common.php file, remove the following line:

if (tep_db_num_rows($orders_query) == 1) continue; //skip customers with orders

 

Thanks again Jack...I'll post any further questions or comments regarding your add-on in the support thread.

 

Dan

Link to comment
Share on other sites

 

@@Dan Cole

Sending an email as @@MrPhil points out, might get you a sale or two, especially if you can target these inactive customers with a small discount code or free gift or such...

 

That said, if you have already considered this and thought not to...run (please note, this is completely untested and might be to harsh for your needs!;

 

Get rid of all customers who never made an order

delete from customers where customers_id not in (select customers_id from orders)

Now get rid of supplementary details of these customers

delete from address_book where customers_id not in (select customers_id from customers)
delete from customers_info where customers_info_id not in (select customers_id from customers)

 

How would I do this for, say, customers with no orders who haven't logged in since December 2012?

osCommerce user since 2003! :thumbsup:

Link to comment
Share on other sites

UNTESTED, use at your own risk.

 

Select customers who have never ordered anything and are more than two years old

 

select customers_id from customers left join customers_info on customers_id = customers_info_id where customers_id not in (select customers_id from orders) and customers_info_date_account_created >= DATE_SUB(customers_info_date_account_created, INTERVAL 2 YEAR)

 

Select customers who have never ordered anything and have not logged in since Dec 2012

 

select customers_id from customers left join customers_info on customers_id = customers_info_id where customers_id not in (select customers_id from orders) and customers_info_date_of_last_logon > '2012-12-31 23:59:59'

 

Link to comment
Share on other sites

Newsletter modules would be perfect for this but I have never find developed community addon modules in this theme.

when you clean customers it depends on fake email addresses too and this option is more difficult with Mailer-Daemons.

The best simple way to use a letter managment service (Mailchimp) with inactive exported customer list where you can inform about customers. If the email address is fake (has Mailer-Daemon) then simple you can delete it.

 

Send customers an asking/gift/coupon letter with unsubscribe/delete options and after it manage your inactive user list.

 

Reimport customers data into a table sheet (Excell vs Calc) and you can write direct sql delete queries with incompetent customers emails into table cells. Copy and run queries.

:blink:
osCommerce based shop owner with minimal design and focused on background works. When the less is more.
Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store.

Link to comment
Share on other sites

How cool would it be to have an admin page where shopowner can make chained SQL queries, then press a button and those customers who match are shown.  Eg;

 

show me;

customers who are female 

AND

have ordered 1 time

AND

have not ordered for 2 years

 

or

 

show me;

customers who have a birthday in the next 30 days

 

or

 

show me;

customers who have written a review

 

stuff like this.  At the side of the list, have tickbox for each customer...

 

If the box is ticked...send an email to them.

 

Could go further and have the ability to create multiple emails with placeholders...so shopowner could choose which email is appropriate...

Press send...

 

At this point the system replaces the placeholders with the customer data, eg (of an email with placeholders)

 

 

Hey *FNAME*, you have an account at *SHOPNAME* but haven't logged in for ages.  Have a look at some of our most recent products;
 
<table>
*each PRODUCTS*
  <tr>
    <td>*PRODUCT_IMG*</td>
    <td>*PRODUCT_NAME*</td>
    <td>*PRODUCT_PRICE*</td>
  </tr>
*/each*
</table>
 
<p>Here's a 20% off coupon;  abc-123-def-456</p>
<p>Claiming your 20% is simple, just insert the code after you add product(s) to your shopping cart.</p>
 
<hr>
<p>This email was sent from *SHOPNAME*. You created your account on 04/09/2012, we hope to see you again soon!.</p>
<p>Not Interested?  You can unsubscribe *UNSUB* from emails, or delete *DELETE* your account entirely.</p>

 

and then the email received would be something like

 

 

Hey Mickey, you have an account at Mouse Supplies but haven't logged in for ages.  Have a look at some of our most recent products;
 
<table>
  <tr>
    <td><img src="images/mouse_ears.jpg"></td>
    <td>Mouse Ears</td>
    <td>$19.99</td>
  </tr>
  <tr>
    <td><img src="images/mouse_trap.jpg"></td>
    <td>Mouse Trap Game</td>
    <td>$9.99</td>
  </tr>
</table>
 
<p>Here's a 20% off coupon;  abc-123-def-456</p>
<p>Claiming your 20% is simple, just insert the code after you add product(s) to your shopping cart.</p>
 
<hr>
<p>This email was sent from Mouse Supplies.  You created your account on 04/09/2012, we hope to see you again soon!.</p>
<p>Not Interested?  You can <a href="http://www.shop.com/whatever.php">unsubscribe</a> from emails, or  <a href="http://www.shop.com/whatever.php">delete</a> your account entirely.</p>

 

Wouldn't that be super cool?  

Link to comment
Share on other sites

  • 2 months later...

As they say...a word to the wise, if you are using the order editor be careful relying on the last login date to remove customer data.  I just discovered that the order editor doesn't update the last login date (at least not with the version I use) when orders are placed manually.  Fortunately I discovered that on my test database. :) 

 

Dan

Link to comment
Share on other sites

  • 2 months later...

 

@@Dan Cole In the admin/includes/modules/database_optimizer_common.php file, remove the following line:

if (tep_db_num_rows($orders_query) == 1) continue; //skip customers with orders

 

What if a customer doesn't have 1 but 2 orders ?  so not :            ==1       but            >=1             ?

 

if (tep_db_num_rows($orders_query) >= 1) continue; //skip customers with orders

 

Peter

Link to comment
Share on other sites

@@pmsmiers That code isn't used anymore. A different way is used in the latest version. But in the older version that used it, the query was just checking for the existence of orders so the only possible results were 0 and 1.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...