Jump to content

Archived

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

Nookster

MySQL Query to show customers who haven't purchased anything.

Recommended Posts

Hi guys,

 

I've been trying to figure this one out for about 2 hours now, how can I issue a MySQL query to the database that will show me customers who have an account but have not made an order?

 

Thanks a lot in advance,

 

Steve

Share this post


Link to post
Share on other sites

SELECT customers_id, customers_firstname, customers_lastname, customers_email_address
FROM customers
WHERE customers_id 
NOT IN (
SELECT customers_id
FROM orders
)

 

Explanation:

 

Select the information you need from the customers table (remember that all customers here have an account),

THEN only show those details IF the customers_id is not found in the customers_id column of the orders table.


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

SELECT customers_id, customers_firstname, customers_lastname, customers_email_address
FROM customers
WHERE customers_id
NOT IN (
SELECT customers_id
FROM orders
)

 

Explanation:

 

Select the information you need from the customers table (remember that all customers here have an account),

THEN only show those details IF the customers_id is not found in the customers_id column of the orders table.

 

Thanks a lot! And thank you for the explanation :)!

Share this post


Link to post
Share on other sites

This has got me thinking, would it be possible to modify this to delete the customers who have never purchased, say after a set period of time to keep the customer database up to date?


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

It would be a lot more lines of code as you would need to do some joins on address_book, customers_info. Then apply it to orders newer than X date.

 

The answer is though; "yes".


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

This is why I was asking, I was going to try to build a contribution that will let you do this, but I am a complete amateur, got to learn sometime though right? I figured out that I would have to do joins but couldn't really get my head around it when I tried.

 

How would I also get it to return the date that the user joined to return with each first name last name and email address, Perhaps I'm not the best person to be trying to do this @_@!

Share this post


Link to post
Share on other sites

I wouldn't immediately give the boot to purchase-less customers. First, I'd send them an email saying that we've noticed that you signed up as a customer, but haven't purchased anything. Is there something preventing you from making a purchase? Have you had any problems with using the site, that we could help you with? Are shipping terms or costs a problem [consider offering lower cost shipping options, such as Parcel Post]? Are you unable to make use of any of our payment options [offer COD, check, money order]? Etc., etc. If a few of these convert into paying customers, you'll come out ahead. Plus, their email addresses (if current) can receive newsletters and mailings (provided they agreed to that when they signed up, so you wouldn't be violating anti-spam laws).

 

If they don't purchase anything even after this, then pull the plug on them.

Share this post


Link to post
Share on other sites

SELECT c.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address, ci.customers_info_date_account_created, ci.customers_info_number_of_logons, ci.customers_info_date_of_last_logon
FROM customers c
LEFT JOIN customers_info ci
ON c.customers_id = ci.customers_info_id
WHERE c.customers_id
NOT IN (
SELECT customers_id
FROM orders
WHERE date_purchased > DATE_SUB(NOW(), INTERVAL 1 YEAR)
)
ORDER BY customers_id

 

That might do it (phpmyadmin). You can change the interval to any number and any of HOUR DAY WEEK MONTH YEAR.

 

NOTE: I have not tested this!


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

×