Send Email to Customers Question

On the send email to customers page, on the drop down you get various options to send bulk emails.

I would like to contact customers that have never comeplted a purchace but not ALL of them. just ones in say the past week.

Is there a way to change what ever query pulls the customers that havent made a purchase to limit them by this sort of period of time? perhaps a good one would be 'customers that havent made a purchase last week', so 7 days from 14 days ago?

Many Thanks,

Phil

I think I have a query that should deal with the dates bit, will this work?

WHERE Date BETWEEN (NOW() - INTERVAL 14 DAY) AND (NOW() - INTERVAL 7 DAY)

Quote:

---

Originally Posted by philip937

I think I have a query that should deal with the dates bit, will this work?

WHERE Date BETWEEN (NOW() - INTERVAL 14 DAY) AND (NOW() - INTERVAL 7 DAY)

---

So I think I should be able to use this:
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

and i need hep merging the two above?? anyone?

im assuming i need to join the customers_info table so I have a date (customers_info_date_account_created) to reference but unsure how to do this|?

You could utilize the mailbeez addon for this and/or the 'recover cart' plugin.

Might be easier than rolling your own.

Quote:

---

Originally Posted by Limitless

You could utilize the mailbeez addon for this and/or the 'recover cart' plugin.

Might be easier than rolling your own.

---

installing a whoe mod rather compared to adding one query to a built in feature?

my preference is using the send mail page hence why I am trying to establish the correct sql syntax to pull the desired customer info.

The mod has many more marketing features than 'just' what you are trying to do.

Perhaps download the mod and look through it for help with your queries?

Quote:

---

Originally Posted by Limitless

The mod has many more marketing features than 'just' what you are trying to do.

Perhaps download the mod and look through it for help with your queries?

---

I am aware of its functionality as ive looked at it before. however I am not interested in these features. I simply want to send an email to a specified group of customers based on a query I insert into query builder. cheers anyway, i just need some help with the table joins

Code:

---


Select
  customers.customers_firstname,
  customers.customers_lastname,
  customers.customers_email_address,
  orders.customers_name,
  orders.date_purchased

From
  customers Inner Join
  orders On orders.customers_id = customers.customers_id
  where date_purchased <= DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))

---

Quote:

---

Originally Posted by Limitless

Code:

---


Select
  customers.customers_firstname,
  customers.customers_lastname,
  customers.customers_email_address,
  orders.customers_name,
  orders.date_purchased

From
  customers Inner Join
  orders On orders.customers_id = customers.customers_id
  where date_purchased <= DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))

---

---

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?

Quote:

---

Originally Posted by philip937

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?

---

Code:

---

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

---

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

Quote:

---

Originally Posted by 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 :)

---

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.

Quote:

---

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.

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.

Quote:

---

Originally Posted by 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.

---

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.

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?

Quote:

---

Originally Posted by 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?

---

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.

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

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

so you did this:

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

in your myphpadmin and it returned nothing?

Quote:

---

Originally Posted by philip937

so you did this:

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

in your myphpadmin and it returned nothing?

---

That one returned data...

so somehow I need the customers_info table joined to this query and then add the time span the the WHERE clause.

I HAVE IT WORKING :)

this seems to do the trick:
Select
customers_info.customers_info_date_account_created,
customers.customers_lastname,
customers.customers_firstname,
customers.customers_email_address
From
customers_info Inner Join
customers On customers_info.customers_info_id = customers.customers_id
left 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);

I thinnk you were getting no results on your previous suggesting is that you were selecting the order date. instead we are just narrowing results of the select where the is no order date.

I have noticed on the default query is has something like this:
select distinct c.customers_firstname as customers_firstname

the as bit, is this so it renames the column and therefore when the code to send email runs it picks customers_firstname ?