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

[dysfunctional]

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

[RodG]

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

[RodG]

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

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

[RodG]

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.

SELECT DISTINCT `customers_email_address` FROM `orders` WHERE `orders_id` IN
(SELECT `orders_id` FROM `orders_products` WHERE `products_id` != '114');

Cheers
Rod

[dysfunctional]

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

[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?

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:

SELECT  distinct `customers_id`, `customers_email_address`,`customers_country` from  `orders`  join `orders_products` on `products_id` != '114'

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.

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

[dysfunctional]

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

[RodG]

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?

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.

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

[jc8125]

your results may vary, but this seems to be working on a few test shops i've tried:

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.