Send email option

[mydanilo]

I run a wholesale site and oftern prospective customers register and then never place an order.

I am trying to get a filter for all registered customers that never ordered. I have tried to modify the sample sql but failed. Lack of knowledge.

I tried to export all customers...I want to export all customer data and need to filter the ones that registered but never ordered. I manage to export the customers but I can not find where the flag is for Number of Orders.

I tried to find the table that holds the "Number of Orders: 0" but could not find it. It is not in the customer table. Does anybody know how I can generate a list of customers that have "Number of Orders: 0"?

[Chuckl]

If a customer has ordered something their customer name and customer id will be in the orders table.

Here is the query builder query for newsletter subscribers who have not ordered anything for 3 months

select c.customers_email_address,
c.customers_lastname, c.customers_firstname from TABLE_ORDERS o, TABLE_CUSTOMERS c where c.customers_newsletter = '1' AND c.customers_id =
o.customers_id and o.date_purchased < subdate(now(),INTERVAL 3 MONTH) GROUP
BY c.customers_email_address order by c.customers_lastname,
c.customers_firstname ASC

That should get you some of the way.

[mydanilo]

Thank you Chuckl.

But my problem is that I have a lot of registered customers that have NEVER ordered ever. How do I get a email list to follow up on just them?

[Chuckl]

Ouch! He wants me to dream up SQL select statements on a friday afternoon?
And ones using negative logic...

OK, probably something like

Select customers_email_address, customers_first_name, customers_last_name from TABLE_CUSTOMERS where c.customers_id NOT IN (select customer_id from TABLE_ORDERS);

Note that the above is only likely to work on MySQL 5. I don't think earlier versions supported sub queries. If you are using MySQL 4, we need to embark into the murky world of left joins,which I always get wrong.

Something like

Select c.customers_email_address, c.customers_first_name, c.customers_last_name from TABLE_CUSTOMERS c LEFT JOIN TABLE_ORDERS d on (c.customer_id = d.customer_id) where d.customer_id is null;

And at that point I surrender

[mydanilo]

Chuckl,

This is the error I get with both of your sql

1146 Table 'xxxxxxxx_zcx.TABLE_CUSTOMERS' doesn't exist
in:
[Select c.customers_email_address, c.customers_first_name, c.customers_last_name from TABLE_CUSTOMERS c LEFT JOIN TABLE_ORDERS d on (c.customer_id = d.customer_id) where d.customer_id is null;]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

Any more ideas? I appreciate your help.

[Chuckl]

Yup, they were illustrative rather than exact to be copied. Where are you executing this?
These are SQL queries for the query builder in Zen Cart, if you are executing them from the SQL patch section inAdmin or a SQL prompt, you would need to replace TABLE_CUSTOMERS etc with the actual name of the table, e.g. customers.
TABLE_CUSTOMERS is a Zen Cart constant defined in one of the Zen Cart startup files - database_tables.php. I use it because it would include any prefixes on the tables for your site.
As a result, it is usable only inside Zen Cart, it has no meaning in a normal SQL query.

[mydanilo]

Sorry my ignorance, but where is the query builder located? I tried it in the SQL patches.

[Chuckl]

Much discussion on the subject here:

http://www.zen-cart.com/forum/showthread.php?t=62591

You would need to create an insert statement to run in the SQL patch or similar to insert the new query into the query builder table. That is what allows you to use it in the email or newsletter send.
So, take one of the examples that have the correct 'audience' email newsletters or both, give it a name - Tight-fisted Customers or whatever, and replace the SQL query with your new one.