Has anyone successfully imported MSA data? We'd like to use this data in segmentation in order to pull prospect lists based on an MSA region.
We were told we could import the data into the Geographic Areas (TR_GEO_AREA) table, and that there's a process that runs on the backend to tie the constituent's address to the MSA region.
What we've found it that the process has each key programmed line by line to update each address based on the zip code. So it appears to us we would have to update the table, then rewrite the whole process. Has anyone already done this and would be willing to walk us through how you did it? Or if you've found a more efficient way to write the process instead of programming line by line?
I think we've done something similar to what you're wanting. We have a number of custom regions we've added to TR_GEO_AREA (kind of similar to how you're wanting MSA regions). We then uploaded into our database a custom mapping table where we mapped a large list of 5-digit ZIP codes to these custom Geo Area values.
We then updated LP_UPDATE_GEOAREA to reference this mapping table to see if there's a match, which avoids having to hard-code individual ZIPs into that procedure.
DECLARE @mapping_area INTSET @mapping_area = (SELECT geo_area FROM LTR_GEO_AREA_MAPPING WHERE zip_code = (substring(@postal_code, 1, 5)))
UPDATE t_address SET geo_area = CASE
...
WHEN country = 1 and @mapping_area IS NOT NULL THEN @mapping_area
ENDWHERE address_no = @address_no
So possibly something like this could work for you. You can send me a message if you want more details.