Criteria by SCF of Zip Code - Extraction Manager

 Take this piece:  AND e.postal_code like '100%'

and substitute with this:

AND LEFT(postal_code,3) in ('100','101','104','112')

~Dan

Hi Dan,

This was helpful and pulled the SCFs that I was looking for.  However, since it will be part of an extraction - how can I flip that query to be "not like" and use it as a suppression to essentially suppress every zip code not starting with those SCFs?

Thank you for the help!

Chuck Buchanan

Please disregard, I figured out how to do this! Of course it was as easy as changing "in" to "not in".

In case anyone else is interested, here is the manual query (identifying all primary addresses not in those SCFs).

Select Distinct a.customer_no
 From t_customer a (NOLOCK)
JOIN vs_address e (NOLOCK) ON a.customer_no = e.customer_no
 Where  IsNull(a.inactive, 1) = 1
 AND e.primary_ind in  ('Y')
AND LEFT(postal_code,3) not in ('100','101','104','112','115','116','117','118')

Simple, change it to:

AND LEFT(postal_code,3) NOT IN ('100','101','104','112')

and make that a suppression segment.

Hey Chris. I've been tasked with finding STBs in specific SCFs. I thought I could just go to TSTATS but I don't see a zip code criteria. I came across a few of your postings and it seems like you do this a lot. I need to get Single Ticket Buyers that reside in 100-101, 112,152,170,172,190-191,194,199-201,205-212,214-218,220-232,234-236,240,252,275,331,342, 481,606,750, 902,941,945, & 980-981.

Can I do a manual edit in List Manager somehow? I found this that you posted a few years ago

Select Distinct a.customer_no
From t_customer a (NOLOCK)
JOIN vs_address e (NOLOCK) ON a.customer_no = e.customer_no
Where IsNull(a.inactive, 1) = 1
AND e.primary_ind in ('Y')
AND LEFT(postal_code,3) not in ('100','101','104','112','115','116','117','118')

Hi Adria,

Are you looking for high level stats on customers in those SCFs or an actual list of customers with their contact details for a mailing? 

If you want stats for an analysis, the easiest way, as you mentioned, would be T-Stats.  However, the criteria there is only for 5-digit postal code.  You can find this in the folder “const sum”, then “general” and “postal code”.  There you could slice and dice it with whatever your criteria is for STBs to get counts of how many constituents are in each postal code.  There is no out-of-the box way beyond this to get at stats by 3-dight SCF so what I’d recommend (and this is a bit more advanced using Excel) is to export your T-Stats report to excel and using split to columns to drop the last two digits of each postal code and then pivot table by the 3-digit SCFs left.     

If you’re looking for a list of customers for a mailing you’d use the SQL code you mentioned as a 'manual edit' segment in Extraction Manager, this is much cleaner than using List Manager where you’d need to have all of your criteria in one manual edit list.  You would create one segment in Extraction Manager with the below code and edit the highlighted portion to list out each and every SCF one-by-one surrounded by the apostrophes and a comma after each.  (Make sure the SCFs you have with hyphens are listed out i.e. 205-212 as ‘205’,’206’,’207’ etc.)

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)

JOIN vs_address e (Nolock) ON e.customer_no = a.customer_no

Where 1 = 1

AND e.primary_ind in  ('Y')

AND LEFT(postal_code,3) not in ('100','101','102','103')

Then mark that segment as an exclusion segment by checking the box under “suppr flag”.  This segment will then pull everyone in the database not on your desired SCFs and exclude them. 

Next build an inclusion segment (or multiple segments) below of the STBs you need and the only constituents that will be picked up will be the ones in those SCFs.  Once you’ve counted this you can save to list manager and extract via an output set.   

Doing this in Extraction Manager also has the added benefit of copying that segment into new extractions in the future to save time (which I've done hundreds of times with my SCF segment).  

Does this help?  Let me know if you have further questions.

All the best,

Chuck Buchanan