• Replies 11 replies
  • Subscribers 961 subscribers
  • Views 179 views
  • Users 0 members are here
Related

Dynamic list by zip code

Anh Le
$organization
Hi everyone,
 
This isn’t exactly an Analytics question, but this seems like the group that might have an answer to a question that is stumping me!
 
I’d like to have a dynamic list of any patrons in our database who live within 50 miles of our theater. It would be really handy for multiple functions of our department to have this list automatically refresh on a daily basis. That’s approximately 210 zipcodes but I can only think of how to pull this list as an extraction (with each zipcode + wild card characters for the possible four digit extension as its own criteria), which wouldn’t allow me to make it dynamic.
 
Has anyone come up with a solution for how to do this? Thanks in advance for any insight you can share!
 
Anh Le | Director of Marketing & PR
Opera Theatre of Saint Louis | ExperienceOpera.org
ale@opera-stl.org | (314) 963-4294
 
A picture containing shape Description automatically generated
 
 
  • $organization

    Hi Anh,

    I would build it in list manager. You can set List Manager lists to be dynamic.

  • $organization
    Anh Le said:
    I’d like to have a dynamic list of any patrons in our database who live within 50 miles of our theater.

    If you have SQL access to your database, I'd recommend updating the geo_location column in your T_ADDRESS table. With that, creating a dynamic list by distance would be straightforward.

    (We get monthly updates of Zip codes (among other data) from zip-codes.com, which includes latitude and longitude values, which I use to populate the geo_location data.)

  • $organization

    Hi Anh,

    At Mount Vernon , we have a  group called Neighborhood friends which are members who live in the surrounding zip codes. We use Geo Areas to determine who is in the group. As people move in and out of the area they are added or removed when there primary address is updated.  Extended Services help us set up the update portion of the program. The zip codes are added as table in SQL . 

    With Geo Area, you can use that to build a dynamic list. 

    We also added a Geo Area of all constituent within 100 miles of Mount Vernon. The one drawback is an address can only have one Geo Area. 

  • $organization
    Hi Tim,
     
    Now I feel like I’m asking a really dumb question – but given the limitations on multiple List Manager criteria, how would you suggest setting up a list to capture 210 specific zip codes? Am I missing something incredibly obvious?
     
    Anh Le | Director of Marketing & PR
    Opera Theatre of Saint Louis | ExperienceOpera.org
    ale@opera-stl.org | (314) 963-4294
    .
     
     
  • $organization
    You can create a row in TR_GEO_AREA (or more than one row, if you want to define certain area codes preferentially). In our case, we created a row for every county in Florida.
     
    Once you decide which areas are meaningful for you, update the LP_UPDATE_GEOAREA procedure to include your new data. We have a huge CASE WHEN clause added that defines geo area for every zip code in Florida.
     
    select @geoarea =
    CASE
      when @country = 1 and @postal_code like '32003%' then 19
      when @country = 1 and @postal_code like '32004%' then 61
      when @country = 1 and @postal_code like '32006%' then 19
      when @country = 1 and @postal_code like '32007%' then 60
    <<>> 
      when @country = 1 and @postal_code like '34997%' then 51
      when @country = 1 and @postal_code like '99999%' then 0
      when @country = 1 and @postal_code like '00000%' then 0
    else 5
    END
     
    It would then be easy to make a dymanic list that pulled people with active addresses in certain geo areas.
     
    Lucie
     
  • $organization in reply to Anh Le

    I have built one with consecutive zip codes, but I think the suggestion of Geo locations is actually a better approach. 

  • $organization
    Thank you for this, Lucie and Chris! I don’t know SQL but I’m going to see if our development department can assist me in implementing this.
     
    Anh Le | Director of Marketing & PR
    Opera Theatre of Saint Louis | ExperienceOpera.org
    ale@opera-stl.org | (314) 963-4294
    .
     
     
  • $organization in reply to Chris Jensen

    Chris,

    I am curious how once you have the geo_location loaded, how do you select records wtihin a certain radius using the geo_location? 

    Thanks,

    Seth

  • $organization in reply to Seth Ghitelman
    Seth Ghitelman said:
    I am curious how once you have the geo_location loaded, how do you select records wtihin a certain radius using the geo_location? 

    Most recently I've used this data to find Edu constituents within X miles of the Guthrie for e-mail promotions. Please see a code sample attached. This example wasn't used in a dynamic list, but could be modified to do so.

  • $organization in reply to Chris Jensen

     For the data from zip-code.com which of their services are you using and what is the level of accuracy of the geolocation lat. lon. data?

    • Is it a centroid of a zip code
    • Is it to the building, or at least approximate neighborhood. zip-code.com seem to have a zip+4 service.

    Do you also use any of the Census data that they are including in their service?

  • $organization in reply to Tom Brown (Past Member)
    Tom Brown (Past Member) said:
    Is it a centroid of a zip code

    I assume so. 

    Tom Brown (Past Member) said:
    Do you also use any of the Census data that they are including in their service?

    Yes. We've done Zip updates to keep TR_CITYSTATE up-to-date for over ten years now, were happy with that, but noticed their Zip data includes congressional districts, which Devo had a need for. That eventually led us to upgrade our subscription to include Census data. We've downloaded the 2010 data, and have been informed that 2020 data will be arriving this month.