Unica Campaign: Target Customer Based On Proximity to a Location

Whether you are preparing for a major store grand opening or a special program, at some point every database marketer has had to pull a list of households or individuals based on proximity to a known location.

As a former marketer at Macy's Central, it was very typical of our department to pull list, sometimes adhoc, for local promotions. In all cases we leveraged state, zip and county data in our system to select contacts based on their proximity to the event. When Macy's was once divided into several districts it was easier (although not by much) for each corporate district to insert neighboring counties (in proximity to event location) in a Unica campaign and pull their own list. But if you are structured like most companies, there is typically a single national office pulling campaign lists. With that said, its difficult to pull list based on contact's locality without having detailed information or intimate knowledge of the surrounding areas (neighboring counties, zips, states-when event is on the border).


Well, have you ever tried leveraging longitudinal and latitudinal data to select contacts within a given radius of your event's location?

As a marketer you can do this in Campaign, independently of your hosting company or internal IT department and you can earn some serious brownie points with your manager!

It's pretty simple. Let's say you are a preparing an email or direct mail campaign for a local promotion. The promotion is only good at a given location (maybe at a store grand opening). The location is Charlotte, North Carolina (which is not too far from the border of South Carolina). Well, you wouldnt want to pull contacts based on state because you'd miss out on contacts near the border of South and North Carolina while picking up someone in Raleigh, 2hrs away from the location! It also wouldnt be feasible to look up neighboring counties and zip codes because everytime you pull a similar campaign you'd have to do the same leg work.

Instead, start out by selecting contacts based on state (yes, i know, i already told you not to do that but bare with me). Once you have a smaller subset of your database, you can refine this group based on distance from event location by applying 'Zip 50' logic to this group. In a select box, using raw sql (dont get scared, I'll provide the sql below) apply a trigonometric calculation to pick up only those records within a certain distance of the event. The guessing game is eradicated. You can finally choose contacts based on their exact proximity to an event and not based on some estimate. It is also a highly transferable solution. You can take the same logic and just pop it into another campaign. O Joy!

Below is the basic trigonometric calculation. Feel free to comment or contact me at mass.it.consulting@gmail.com if you have any issues implementing this solution.

select distinct t.master_individual_id (audience level)
from  <temptable> t
inner join individual_table base with (nolock)
on t.individual_id = base.individual_id
inner join address_table s with (nolock)
on base.primary_address_id = s.address_id
where coalesce(s.longitude,'x') <> 'Unknown' and coalesce(s.latitude,'x') <> 'Unknown'
and
case when s.zip_code = (event location zip code) then 0.0
when s.longitude = (event location longitude) and s.latitude = (event location latitude) then 0.0 else
acos(cos(radians(s.latitude))*cos(radians(s.longitude))*cos(radians(event location latitude))*cos(radians(event location latitude)) + cos(radians(s.latitude))*sin(radians(s.longitude))*cos(radians(b.event location latitude))*sin(radians(event location longitude)) + sin(radians(s.latitude))*sin(radians(event location latitude))) * 3963.1 end
>= 50 (mean within 50 miles, you can change it)
order by 1