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

[Nookster]

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

[burt]

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.

[Nookster]

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 :)!

[Mort-lemur]

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?

[burt]

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".

[Nookster]

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 @_@!

[MrPhil]

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.

[♥toyicebear]

Click me>>

[burt]

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!