• Replies 11 replies
  • Subscribers 961 subscribers
  • Views 181 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
 
 
Parents
  • $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
     
Reply
  • $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
     
Children
No Data