Send Email to Customers Question

[Limitless]

The query I need wants to select customers that haven't made a purchase and group by date based on when they signed up. Basically the two elements I posted earlier in this thread need to be combined with another join statement?

Select
  customers_info.customers_info_date_account_created,
  customers.customers_lastname,
  customers.customers_firstname,
  orders.date_purchased
From
  customers_info Inner Join
  customers On customers_info.customers_info_id = customers.customers_id
  Inner Join
  orders On orders.customers_id = customers.customers_id
Where
  orders.date_purchased <= Date(Date_Sub(Now(), Interval 14 Day)) And
  customers_info.customers_info_date_account_created <= Date(Date_Sub(Now(),
  Interval 14 Day))
Group By
  customers_info.customers_info_date_account_created

[philip937]

nearly. i think this is what im after, need to test later.

Select
customers_info.customers_info_date_account_created,
customers.customers_lastname,
customers.customers_firstname,
orders.date_purchased
From
customers_info Inner Join
customers On customers_info.customers_info_id = customers.customers_id
Inner Join
orders On orders.customers_id = customers.customers_id
Where
orders.date_purchased IS NULL AND
customers_info.customers_info_date_account_created BETWEEN (NOW() - INTERVAL 14 DAY) AND (NOW() - INTERVAL 7 DAY)

thanks for the pointers :)

[Limitless]

nearly. i think this is what im after, need to test later.

Select
customers_info.customers_info_date_account_created,
customers.customers_lastname,
customers.customers_firstname,
orders.date_purchased
From
customers_info Inner Join
customers On customers_info.customers_info_id = customers.customers_id
Inner Join
orders On orders.customers_id = customers.customers_id
Where
orders.date_purchased IS NULL AND
customers_info.customers_info_date_account_created BETWEEN (NOW() - INTERVAL 14 DAY) AND (NOW() - INTERVAL 7 DAY)

thanks for the pointers :)

The problem is 'orders.date_ordered' will not be null, because only if they ordered is there an entry in the orders table.

I think you need to look at customers.customers_id and compare that with orders.customers_id and if they don't exist in orders table, get that list.

[philip937]

The problem is 'orders.date_ordered' will not be null, because only if they ordered is there an entry in the orders table.

I think you need to look at customers.customers_id and compare that with orders.customers_id and if they don't exist in orders table, get that list.

exactly so we are saying if it returns NULL then they havent ordered, therefore we want their email address to email them?
that is taken directly from the default value
'Send email to customers that havent yet made a purchase'

so this is what we want it to do.

select all customers that registered between the period -14 days - -7 days ago that havent yet ordered.

[philip937]

this is the default entry in the 1.5.1 install:

SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL

this returns all customers that have not yet made a purchase.

my objective was exacly this but to only return customers that registered 1 week ago i.e between -14 days and -7 days period.

[Limitless]

this is the default entry in the 1.5.1 install:

SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL

this returns all customers that have not yet made a purchase.

my objective was exacly this but to only return customers that registered 1 week ago i.e between -14 days and -7 days period.

1.5 is different then, you need customers_info table and 'date_purchased' is NULL returns no customers, because the field is not NULL, the whole record won't exist in the orders table.

[philip937]

im confused now..

if the default query works currently to call all the customers, then doing the same thing and adding the table join to only select customers that registered between those dates whats the problem?

[Limitless]

im confused now..

if the default query works currently to call all the customers, then doing the same thing and adding the table join to only select customers that registered between those dates whats the problem?

They aren't a 'customer' with 'date_ordered' entry if they have never placed an order.

You will need a nested select statement in your WHERE/WHERE NOT EXIST clause to determine that the customers_id is in the customer table but NOT in the orders table.

[philip937]

Assignable it's testing it against the table join not the actual table so the entries will be there but the fields will be null against customers with no orders..?

[Limitless]

That is not what I saw when testing for null. It returned no customer_id records.