Coupons & Dormant Customers

[ksoup]

We sent out a "we miss you" coupon to the dormant customers > 3 months. It seems that active customers ... who just purchase last week... received the coupon also.

How can we trouble shoot this?

[Kim]

How did you separate the groups?

[Ajeh]

The physical query on this is in the query_builder table ... if this is of help to you ...

[ksoup]

Thank you :) Interesting thing to note....

***Note: I have not changed the query in the database :) ...The following info is from using the query seperately in mysql ***

With the date clause taken out of this query, we have 4,168 customers that are subscribed to our newsletter and have a matching customer id on the customers table and orders table.

Adding the Date back and only changing the greater than/less than yields the following results....

Using the query with the Dormant Customers
o.date_purchased < subdate... gives a figure of 3,296

Change the date clause to o.date_purchased > subdate gives a figure of 1,868.

If you add those two together you get a total of 5164 not 4168.
That is a difference of 996 customers that seem to be doubled up.


Thoughts? This could account for the reason active customers got the coupon for dormant customers.

The physical query on this is in the query_builder table ... if this is of help to you ...

[ksoup]

Hi Kim, We used the send coupon mod on the store... you have the drop down option to choose Active ... All subscribers... Dormant > 3months... We chose dormant.

How did you separate the groups?

[ksoup]

I have looked at the query until I am now blue in the face.

It just doesn't seem plausible that a customer can be both active and dormant, yet in this query, there are customers considered dormant who are active and just purchased last week.

[ksoup]

This is now to a point of frustration. We have a standard we miss you coupon. Due to the fact that this query doesn't work right, we now have mad customers, because, they got the coupon and cannot use it because it is a one time use per customer coupon.

We have answered many emails giving the customers a new code, but it looks embarassing.

They say...why do you say " we miss you" when we bought last week?!

Coupon was sent via the send email > DORMANT CUSTOMERS in the admin. The last time it was sent (WHICH WAS OVER 3 MONTHS AGO - TO PREVENT THIS VERY PROBLEM), the same thing happened...current customers got the coupon, too...but we honored it.

If you have no suggestions for why this query sends to our current/active customers, also.... we simply cannot trust that portion of the admin.

Please advise.

[DrByte]

I too see similar numeric discrepancies when running the query with < and >.
However, when I view the raw data returned by each, the dates are "correct".

I suspect the issue is related to the fact that the orders table is a one-to-many relationship with the customers table, and thus what's happening is that customers who have ordered more than once are showing up in both sides of the query.

Need to work up an outer join... hmm.....

[DrByte]

Try this via phpMyAdmin:

REPLACE INTO query_builder ( query_id , query_category , query_name , query_description , query_string ) 
VALUES ( '3', 'email,newsletters', 'Dormant Customers (>3months) (Subscribers)', 'Subscribers who HAVE purchased something, but have NOT purchased for at least three months.', 
'select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o 
WHERE c.customers_id = o.customers_id 
AND c.customers_newsletter = 1 
GROUP BY c.customers_email_address 
HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) 
ORDER BY c.customers_lastname, c.customers_firstname ASC');

This now also excludes customers who have purchased within the last 3 months...removing the overlap.

Once you've updated your database with the updated query, you should be able to re-run your report/mailing etc and get the right data.

[ksoup]

Dr. Byte, You simply Rock!! I had tried to use the max option in the select area of the query and it wasn't working.

Thank you so much for your time and expertise!!! You are GREATLY appreciated. :)

The numbers add up!! If I take out the date statement, I get the total of what is returned with the date statement in and just changing the data from dormant to active customers (switching the greater than less than sign). :)