How can I extract a list of customers who did NOT purchase product X?
Hi all! :)
I've been struggling for some time to find a way of doing this but still no luck. Would be truly grateful for some help.
I want to send a special offer to customers who did not already purchase product X.
With Apsona Shopadmin I can get a list of customers who DID purchase product x, but I can't seem to find a way to do the opposite.
I don't want to annoy customers by sending them offers on products they already have bought. Plus, they bought it a long time ago, without the special offer, so it's probably better they don't even get to know about it :).
Is there any way I can do this? I only need is the e-mail address of each customer. Is it possible to do a SQL query for this?
A solution for this would truly make my day. Thanks a million in advance.
Best regards Peter
Re: How can I extract a list of customers who did NOT purchase product X?
Try this:
SELECT `customers_email_address` FROM `orders` WHERE `orders_id` = ANY
(SELECT `orders_id` FROM `orders_products` WHERE `products_name` NOT LIKE '%productx%')
Change 'productx' to suit.
Cheers
Rod
Re: How can I extract a list of customers who did NOT purchase product X?
You may find this a little better: (it'll avoid the dupe emails)
SELECT DISTINCT `customers_email_address` FROM `orders` WHERE `orders_id` IN
(SELECT `orders_id` FROM `orders_products` WHERE `products_name` NOT LIKE '%productX%')
Cheers
Rod
Re: How can I extract a list of customers who did NOT purchase product X?
Hi RodG! I'm deeply thankful for your suggestion! I'm not sure I can get it to work though.
The ID of the product in question has "114" and the name is "The Savage Playground (CD) *Pre-Order*"
I've tried the following changes :
WHERE `products_name` NOT LIKE '%The Savage Playground (CD) *Pre-Order*%'
and
WHERE `products_name` NOT LIKE '%The Savage Playground (CD) *Pre-Order*%'
and even
WHERE `products_id` NOT LIKE '114' / WHERE `products_id` NOT LIKE '%114%'
I do get a long list of users but some of them did actually buy this specific product :).
I'm not so good with SQL so any further help would once again be truly appreciated.
Thank you
Regards Peter
Re: How can I extract a list of customers who did NOT purchase product X?
Quote:
Originally Posted by
dysfunctional
Hi RodG! I'm deeply thankful for your suggestion! I'm not sure I can get it to work though.
The ID of the product in question has "114"
< snip >
I'm not so good with SQL so any further help would once again be truly appreciated.
Try the following.
Code:
SELECT DISTINCT `customers_email_address` FROM `orders` WHERE `orders_id` IN
(SELECT `orders_id` FROM `orders_products` WHERE `products_id` != '114');
Cheers
Rod
Re: How can I extract a list of customers who did NOT purchase product X?
Thank you again RodG!
I still get the exact same list of customers where some of them actually did buy the product at some point. They may have a few orders not containing this specific product though and maybe this query lists those orders?
I don't know :(. Any other ideas?
And it this would be even better if the list could contain customers country too, next to the e-mail address. I tried to accomplish this but my knowledge in SQL is way too poor obviously :)
Best regards
Peter
Re: How can I extract a list of customers who did NOT purchase product X?
Quote:
Originally Posted by
dysfunctional
I still get the exact same list of customers where some of them actually did buy the product at some point. They may have a few orders not containing this specific product though and maybe this query lists those orders?
Yes. That'll be it. The SQL query to take these into account would be quite complex. It will probably be quicker for you to manually identify and delete those email addresses than it would for me to nut out a suitable query.
OTOH, try this:
Code:
SELECT distinct `customers_id`, `customers_email_address`,`customers_country` from `orders` join `orders_products` on `products_id` != '114'
Quote:
Originally Posted by
dysfunctional
And it this would be even better if the list could contain customers country too, next to the e-mail address. I tried to accomplish this but my knowledge in SQL is way too poor obviously :)
If the code above doesn't work as expected, the older code can be modified as follows to include the country.
Code:
SELECT DISTINCT `customers_email_address`, `customers_country` FROM `orders` WHERE `orders_id` IN
(SELECT `orders_id` FROM `orders_products` WHERE `products_id` != '114' )
Cheers
Rod
Re: How can I extract a list of customers who did NOT purchase product X?
Thank you for your continued support RodG :). You have no idea how much I appreciate it :).
Unfortunately I still have no luck :(
Your first suggestion actually doesn't do anything - when I run it in phpmyadmin it loads for a few seconds, then it stops :(
The second one gives the same results as the one I've already tried.
How can it be so hard? Haha :)
Cheers
Peter
Re: How can I extract a list of customers who did NOT purchase product X?
Quote:
Originally Posted by
dysfunctional
when I run it in phpmyadmin it loads for a few seconds, then it stops :(
It stops??? Any error messages? What do you see on screen when it stops? How long is 'a few seconds'? How many records do you have in your orders and orders_products tables?
Quote:
Originally Posted by
dysfunctional
The second one gives the same results as the one I've already tried.
Does that mean it doesn't show the countries? (that is the only difference you'll see). You'll need to manually delete the emails that aren't needed.
Quote:
Originally Posted by
dysfunctional
How can it be so hard? Haha :)
Truthfully, it should be quite easy. It is only 'hard' because I don't have a suitably populated database to test/check/experiment with. All the queries I've supplied appear to be producing valid outputs from my limited test data. I may have other ideas/suggestions, but I'll need your responses to my questions before I can decide if any of them will be suitable/relevant.
Cheers
Rod
Re: How can I extract a list of customers who did NOT purchase product X?
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.
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).
