Targeting Catalog Requestors - Revision history

Russ horton: /* Catalogs Sent */

2013-07-22T17:57:37Z

Catalogs Sent

← Older revision		Revision as of 17:57, 22 July 2013
Line 41:		Line 41:
	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:		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:

	<source lang="~~SQL~~">		<source lang="tSQL">
	Select DISTINCT c.* from CUST c		Select DISTINCT c.* from CUST c
	where ID in		where ID in

Russ horton at 17:57, 22 July 2013

2013-07-22T17:57:29Z

← Older revision		Revision as of 17:57, 22 July 2013
Line 7:		Line 7:
	=Catalogs Unsent=		=Catalogs Unsent=

	<source lang="~~SQL~~">		<source lang="tSQL">
	SELECT DISTINCT c.*		SELECT DISTINCT c.*
	FROM CUST c		FROM CUST c
Line 19:		Line 19:
	'''NOTE: Much of this may no longer necessary as these capabilities have been built into version 6.0's Catalog Request processing.'''		'''NOTE: Much of this may no longer necessary as these capabilities have been built into version 6.0's Catalog Request processing.'''

	<source lang="~~SQL~~">		<source lang="tSQL">
	SELECT DISTINCT C.*		SELECT DISTINCT C.*
	FROM CUST C		FROM CUST C

Russ horton: /* Catalogs Unsent */

2011-03-28T17:47:20Z

Catalogs Unsent

← Older revision		Revision as of 17:47, 28 March 2011
Line 10:		Line 10:
	SELECT DISTINCT c.*		SELECT DISTINCT c.*
	FROM CUST c		FROM CUST c
	WHERE ~~id in~~ (~~select distinct~~ relates_to		WHERE C.ID IN (SELECT DISTINCT relates_to
	~~from~~ todo ~~where~~ type = '05' and ~~done~~ = 0)		FROM todo
			WHERE type = '05' AND (done = 0 and void = 0))
	</source>		</source>

Line 22:		Line 23:
	FROM CUST C		FROM CUST C
	WHERE C.ID in		WHERE C.ID in
	(SELECT DISTINCT relates_to FROM todo WHERE type = '05' AND done = 0)		(SELECT DISTINCT relates_to FROM todo WHERE type = '05' AND (done = 0 and void = 0))
	and C.ID not in		and C.ID not in
	(SELECT v.cust_num FROM invoice v		(SELECT v.cust_num FROM invoice v

Russ horton: /* Catalogs Sent */

2011-02-05T01:23:32Z

Catalogs Sent

← Older revision		Revision as of 01:23, 5 February 2011
Line 41:		Line 41:

	<source lang="SQL">		<source lang="SQL">
	Select c.* from CUST c		Select DISTINCT c.* from CUST c
	where ID in		where ID in
	(Select DISTINCT relates_to from todo where type = '05' and done = 1 and date_creat > '2009-03-06')		(Select DISTINCT relates_to from todo where type = '05' and done = 1 and date_creat > '2009-03-06')
	and c.price_cat = '01'		and c.price_cat = '01'
	</source>		</source>

Russ horton: /* Catalogs Unsent */

2011-02-05T01:23:14Z

Catalogs Unsent

← Older revision		Revision as of 01:23, 5 February 2011
Line 8:		Line 8:

	<source lang="SQL">		<source lang="SQL">
	~~select~~ c.*		SELECT DISTINCT c.*
	~~from cust~~ c		FROM CUST c
	~~where~~ id in (select distinct relates_to		WHERE id in (select distinct relates_to
	from todo		from todo where type = '05' and done = 0)
	where type = '05' and done = 0)
	</source>		</source>

Russ horton: /* Catalogs Sent */

2009-11-05T18:57:58Z

Catalogs Sent

← Older revision		Revision as of 18:57, 5 November 2009
Line 42:		Line 42:

	<source lang="SQL">		<source lang="SQL">
	Select c.* from ~~cust~~ c		Select c.* from CUST c
	where ID in		where ID in
	(Select DISTINCT relates_to from todo where type = '05' and done = 1 and date_creat > '2009-03-06')		(Select DISTINCT relates_to from todo where type = '05' and done = 1 and date_creat > '2009-03-06')
	and c.price_cat = '01'		and c.price_cat = '01'
	</source>		</source>

Russ horton: /* Catalogs Sent */

2009-11-05T18:57:39Z

Catalogs Sent

← Older revision		Revision as of 18:57, 5 November 2009
Line 43:		Line 43:
	<source lang="SQL">		<source lang="SQL">
	Select c.* from cust c		Select c.* from cust c
	where id in		where ID in
	(~~select distinct~~ relates_to from todo where type = '05' and done = 1 and date_creat > '2009-03-06')		(Select DISTINCT relates_to from todo where type = '05' and done = 1 and date_creat > '2009-03-06')
	and c.price_cat = '01'		and c.price_cat = '01'
	</source>		</source>

Russ horton: /* Catalogs Unsent */

2009-11-05T18:56:27Z

Catalogs Unsent

← Older revision		Revision as of 18:56, 5 November 2009
Line 17:		Line 17:

	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})		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.		'''NOTE: Much of this may no longer necessary as these capabilities have been built into version 6.0's Catalog Request processing.'''

	<source lang="SQL">		<source lang="SQL">

Russ horton: /* Catalogs Unsent */

2009-11-05T18:51:09Z

Catalogs Unsent

← Older revision		Revision as of 18:51, 5 November 2009
Line 16:		Line 16:


	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}) <Much of this is no longer necessary as these capabilities have been built into version 6.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.

	<source lang="SQL">		<source lang="SQL">
Line 35:		Line 36:
	datediff(day, date_creat, now()) <= 30)		datediff(day, date_creat, now()) <= 30)
	</source>		</source>


	=Catalogs Sent=		=Catalogs Sent=

Russ horton: New page: =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 independent...

2009-11-05T18:50:17Z

New page: =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 independent...

New page

=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=

<source lang="SQL">
select c.*
from cust c
where id in (select distinct relates_to
from todo
where type = '05' and done = 0)
</source>

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}) <Much of this is no longer necessary as these capabilities have been built into version 6.0.>

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C
WHERE C.ID in
(SELECT DISTINCT relates_to FROM todo WHERE type = '05' AND done = 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)
</source>

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

<source lang="SQL">
Select 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'
</source>