How can I extract a list of customers who did NOT purchase product X?

**dysfunctional** · 23 Jan 2013, 10:47 AM

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

**jc8125** · 23 Jan 2013, 04:55 PM

not quite sure about that one - I think we're nearing the end of my expertise.. (or lack thereof)

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.

**dysfunctional** · 23 Jan 2013, 07:07 PM

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

**dysfunctional** · 27 Jan 2013, 08:04 PM

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

**dysfunctional** · 2 Mar 2013, 02:10 PM

Sorry for bumping, but is there any way I can exclude non-newsletter subscribers to this list? Truly grateful for any advice. Thanks!

**RodG** · 2 Mar 2013, 02:29 PM

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

**jc8125** · 5 Mar 2013, 10:10 PM

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