Re: How can I extract a list of customers who did NOT purchase product X?
Quote:
Originally Posted by
jc8125
your results may vary, but this seems to be working on a few test shops i've tried:
Code:
select DISTINCT customers_email_address, customers_id, customers_country from orders where customers_id NOT IN (select DISTINCT o.customers_id from orders o, orders_products op where o.orders_id = op.orders_id and products_id = "114")
On my test db, there are 11 customers who have ordered. 2 of them have ordered that product, 9 haven't. When I run the above query, I get the list of the 9 who haven't ordered the item.
Wow, this script worked for me. Now I get a list of customers who did not purchase the product in question. Awesome!
But, when I "Select all" in phpmyadmin and click "Export" (to csv), instead of getting a normal .csv I get a csv file formatted to html.
When I rename it to html and run it in a browser it says :
Code:
mysqlserver - DBcrashdiet - zen_orders
Error
SQL query: DocumentationEdit Edit
SELECT DISTINCT o.customers_id FROM zen_orders o , zen_orders_products op WHERE ((`zen_orders`.`customers_email_address` = '[email protected]' AND `zen_orders`.`customers_id` = 51 AND `zen_orders`.`customers_country` = 'Germany') OR (`zen_orders`.`customers_email_address` = 'emailadress##########################' AND `zen_orders`.`customers_id` = 37 AND `zen_orders`.`customers_country` = 'United Kingdom') OR (`zen_orders`.`customers_email_address` = 'cobey####################.au' AND `zen_orders`.`customers_id` = 103 AND `zen_orders`.`customers_country` = 'Sweden') OR (`zen_orders`.`customers_email_address` = 'sapphirehaze_##########################' AND `zen_orders`.`customers_id` = 58 AND `zen_orders`.`customers_country` = 'Australia') OR (`zen_orders`.`customers_email_address` = 'ludwig.halldin##########################' AND `zen_orders`.`customers_id` = 154 AND `zen_orders`.`customers_country` = 'Sweden') OR (`zen_orders`.`customers_email_address` = '[email protected]' AND `zen_orders`.`customers_id` = 135 AND `zen_orders`.`custom[...]
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
(I did some changes to not reveal my customers e-mail addresses)
I've done loads of csv-exports before but this time I can't seem to get it right.
As always I'd truly appreciate any help I can get. Thank you
Regards Peter
Re: How can I extract a list of customers who did NOT purchase product X?
not quite sure about that one - I think we're nearing the end of my expertise.. (or lack thereof) :blink:
I tried it on my end, and using your method, got the same goofy results.. This isn't a fix, but I am able to get a valid csv file if i:
- run the query
- change values so that all results are displayed on page (show: xxx rows, starting at row 0, headers every 0 rows)
- scroll down to bottom, instead of checking all and selecting export, click the "Query result operations->export". on next page I selected CSV and left all other defaults. this generates a valid csv file.
no idea why this way works but a normal export doesn't.. but it seems to do the trick on my end.
Re: How can I extract a list of customers who did NOT purchase product X?
Oh my dear god, your method works for me. Thank you jc8125 & RodG for taking your time! Been wanting to do this for ages. So glad to finally have it solved. You made my day :) Thanks!!!
Best regards Peter
Re: How can I extract a list of customers who did NOT purchase product X?
Hmm I faced another little issue here :). Is there any way I can skip the users who does not subscribe to our newsletter?
I would feel a little bad for forcing my campaigns on non-subscribers :)
Regards Peter
Re: How can I extract a list of customers who did NOT purchase product X?
Sorry for bumping, but is there any way I can exclude non-newsletter subscribers to this list? Truly grateful for any advice. Thanks!
Re: How can I extract a list of customers who did NOT purchase product X?
Quote:
Originally Posted by
dysfunctional
Sorry for bumping, but is there any way I can exclude non-newsletter subscribers to this list? Truly grateful for any advice. Thanks!
Yes, there will be/is a way, but it means updating the code snippet so that it will only include the customers who's customers_id can be found in the 'Subscribers' table.
Due to my previous examples not having the expected/desired results, I fear that I'll not have any more luck in creating a suitable code snippet for you (so I'm not even going to try). Sorry.
Cheers
Rod
Re: How can I extract a list of customers who did NOT purchase product X?
don't have time to test right now, but something like:
select DISTINCT customers_email_address, customers_id, customers_country from orders where customers_id NOT IN (select DISTINCT o.customers_id from orders o, orders_products op where o.orders_id = op.orders_id and products_id = "114") and customers_id NOT IN (select customers_id from customers where customers_newsletter = 1)
should do the trick..
ps - this started out as a fairly simple query, and since then we've added some additional lookups/elements. it's now getting a little clunky, and could be cleaned up to use a proper (and more efficient) JOIN statement, instead of trying to cobble together all the tables/fields involved. That said, it works as is - and if you're just running it manually from time to time, and you're not going through a list of 50k customers, "ugly" coding shouldn't cause any major issues. (not like we're doing any update/inserts anyway).
