From NewHaven Software Wiki

Current revision as of 17:57, 22 July 2013

Overview

Users of CMS may wish to target catalog requestors, either sent or unsent, which is currently not a mail list filter option. The following custom filters can be used independently or in a compound filter in order to specifically target the customers that have active or completed Catalog requests.

To use these, just create a new filter in CMS and select the 'Write own SQL' checkbox (bottom of the screen.) There you would paste the SQL shown below, make any modifications needed (e.g. change the date) and then save. As with any filter, you should then test the filter by running a Quick Count so you can see how many results are returned and also spot check those results by double-clicking on displayed customers to review their record.

Catalogs Unsent

SELECT DISTINCT c.* 
FROM CUST c 
WHERE C.ID IN (SELECT DISTINCT relates_to 
             FROM todo 
             WHERE type = '05' AND (done = 0 and void = 0))

A more elaborate version of this looks at all pending catalog requests and then excludes the customer if they have been sent a catalog in the last 30 days or received an order in the last 60 days that included a catalog (referencing such a product with the code 'catalog' {change to suit}) NOTE: Much of this may no longer necessary as these capabilities have been built into version 6.0's Catalog Request processing.

SELECT DISTINCT C.*
FROM CUST C
WHERE C.ID in
(SELECT DISTINCT relates_to FROM todo WHERE type = '05' AND (done = 0 and void = 0))
and C.ID not in
(SELECT v.cust_num FROM invoice v
INNER join itemsord i
ON i.order_id = v.order_id and i.invoice_id = v.invoice_id
WHERE i.prod_code = 'catalog' and i.sent_qty > 0 
and DATEDIFF(DAY, v.order_dt, now()) <= 60)
and C.ID not in
(SELECT RELATES_TO 
FROM todo 
WHERE type = '05' AND done = 1 and
DATEDIFF(DAY, date_creat, now()) <= 30)

Catalogs Sent

This filter, on the other hand, looks at everyone who has placed a catalog request since a specified date and is in the Retail '01' price category:

SELECT DISTINCT c.* FROM CUST c
WHERE ID in
(SELECT DISTINCT relates_to FROM todo WHERE type = '05' and done = 1 and date_creat > '2009-03-06')
and c.price_cat = '01'