• Replies 10 replies
  • Subscribers 337 subscribers
  • Views 141 views
  • Users 0 members are here
Related

Best Practice for Zip Code Criteria

Brandi Sellers
$organization
I'm trying to set up a few lists for my team to be able to pull donors in a certain zip code and am struggling with the appropriate way to truly include everyone in those zip codes. We store both the standard five and five plus four zips in our instance of Tessitura and when I choose the criteria of Like with the 5 digit zip code it seems to skip anyone with the plus four. When I add the wild card indicator to the end of the 5 digits I'm looking for it includes anyone with those five digits in a row. Any advice?
  • Former Member
    Former Member $organization
    Hi Brandi, Postal Code Like xxxxx% seems to be pulling both types. Not sure why that is not the case with your settings. Ahmet Unal, IS Manager UMSL - Touhill PAC
  • $organization in reply to Former Member
    Thanks for your response! I think the challenge with that as a value is that if the last four digits combined with the end of the zip result in the same value, it's pulling them as well. I basically am looking for people that have the exact same first five digits and I don't care what the last four are. I'm guessing I'll be going to SQL for this one.
  • Former Member
    Former Member $organization in reply to Brandi Sellers
    What I gave earlier should give a list with zip codes that start with "xxxxx" and does not care what comes after them. I thought that was what you needed. No?
  • $organization in reply to Brandi Sellers
    Hi Brandi, Can you post the query your list is creating? I.e. click on "manual query" and copy and paste the code? Thanks, Gawain
  • $organization in reply to Former Member
    You're correct, that does seem to be working; however, I'm unable to find the inactive address option to include in the criteria set. Any hints?
  • Former Member
    Former Member $organization in reply to Brandi Sellers
    You may want to look at the fields under "Address Information" folder, especially "Address Exists" and "Primary Address Flag". Hope this helps.
  • Former Member
    Former Member $organization in reply to John Trimble
    John, the online map tool is very handy. Your code, too. Thanks for sharing!
  • $organization
    I'm sure there are a million ways to get at this in Tessitura but this is how I do it using the manual edit function on segment criteria in List Manager and Extraction Manager. For the five digit zip code: Typically this is in an extraction so I do an exclusion segment of NOT IN and the desired codes so that it excludes everyone in the database not having those zip codes. That way all of the inclusion segments below in my extraction are not manual and have various levels of unrelated and sometimes complex criteria. You could obviously do this as an IN if you just want a simple list of just those zip codes. 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,5) not in ('10026','10027') Usually we isolate geography by the less granular three digit SCF and here's the code for that., again with the NOT IN but it could be reversed for a quick list. 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 ('063','064','065','066','067','068','069','070','071','072','073','074','075','076','077','078','079','085','086','087','088','089','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119') Let me know if you have any questions. Chuck Buchanan
  • $organization in reply to Charles Buchanan
    Let me try that again without the crazy formatting that just happend: I'm sure there are a million ways to get at this in Tessitura but this is how I do it using the manual edit function on segment criteria in List Manager and Extraction Manager. For the five digit zip code: Typically this is in an extraction so I do an exclusion segment of NOT IN and the desired codes so that it excludes everyone in the database not having those zip codes. That way all of the inclusion segments below in my extraction are not manual and have various levels of unrelated and sometimes complex criteria. You could obviously do this as an IN if you just want a simple list of just those zip codes. 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,5) not in ('10026','10027') Usually we isolate geography by the less granular three digit SCF and here's the code for that., again with the NOT IN but it could be reversed for a quick list. 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','104','105') Let me know if you have any questions. Chuck Buchanan
  • $organization

    Here is some code I use for zip codes within a radius, which includes code for getting the first five of plus-fours.  Just skip going on the internet to get your zips and insert instead the ones you picked by some other method.  The <hyphen hyphen> comments out in Tessitura;  I usually leave my comments for whomever comes across my query later.  

     

     

    Before you go to Tessitura, get the zips in the selected radius. Just leaving this in for anyone trying to do something with constituents in a geographic area.

    --http://www.freemaptools.com/find-zip-codes-inside-radius.htm 

     

    --save as csv, so (depending on what you open the file with--use simplest possible text viewer) you have comma separated text just as it appears below.  If you open in Excel, the commas convert to fields, and if you open in notepad, they convert to one zip per line, and for this you need the commas.   

     

     

    In Tessitura,  the zips go between the parentheses near the end of this query:

     

    Select Distinct a.customer_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
    JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where

    a1.inactive in ('n')
    --only active addresses for this list  substitute a1.primary_ind in ('Y') for primary addresses only      remove the line altogether to include inactive addresses

    and a1.postal_code LIKE '[0-9][0-9][0-9][0-9][0-9]'
    --only numeric codes because it throws errors if you have text in the postal code. You can change 0-9 to A-Z as needed for foreign codes. 

    and  left (a1.postal_code,5)
    --no accidental offsets into the plus 4 digits a la 77075-0015 = 75001
    in (75001,75023,75025,75026,75040,75042,75044,75045,75046,75047,75048,75049,75074,75075,75080,75081,75082,75083,75085,75086,75093,75094,75230,75231,75238,75240,75243,75248,75251,75252,75254,75287,75023,75024,75025,7502675074,75075,75086,75093,75094,75082,75044,75040,75048,75056,75034,75035,75006,75234,75229,75244,75254,75240,75225,75205,75204,75220, 75225, 75229, 75230, 75244))
    as e ON e.customer_no = a.customer_no

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Gawain Lavers
    Sent: Thursday, January 19, 2017 2:43 PM
    To: John Trimble <J.Trimble@dbdt.com>
    Subject: Re: [Tessitura Technical Forum] Best Practice for Zip Code Criteria

     

    Hi Brandi, Can you post the query your list is creating? I.e. click on "manual query" and copy and paste the code? Thanks, Gawain

    From: Brandi Sellers <bounce-brandisellers7592@tessituranetwork.com>
    Sent: 1/19/2017 3:01:02 PM

    Thanks for your response! I think the challenge with that as a value is that if the last four digits combined with the end of the zip result in the same value, it's pulling them as well. I basically am looking for people that have the exact same first five digits and I don't care what the last four are. I'm guessing I'll be going to SQL for this one.




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!