Geolocation in the Salesforce Marketing Cloud

If you have zip code data for your subscribers and store locations in your Marketing Cloud account, you can use a SQL Query to determine distances between them. With this method you can determine which store is closest to each of your subscribers.

There are a few bits and pieces about geolocation or geo-targeting in the official SFMC documentation, but I think it’s much more helpful to see an example start to finish.

Here are the pieces:

  • A subscriber data extension that includes a zip code column
  • A store data extension that includes a zip code column
  • A zip code data extension that includes X, Y, and Z-axis columns
  • A query that writes to a new data extension

Store_Customers

EmailAddressFirstNameZip
neo@matrix.comNeo94560
morpheus@matrix.comMorpheus00544
trinity@matrix.comTrinity00602

Store_Locations

Store_NumberStore_ZipStore_Phone
55594536555-222-2222
44400612555-333-3333

Zip_Axis

ZipLatitudeLongitudeXAxisYAxisZAxis
0054440.815400000-73.0456000000.220696498-0.7239258680.653624046
0060218.352927000-67.1775320000.368147699-0.8748283040.314869354
0061218.388009000-66.6652790000.375877848-0.8713247720.315450447
9453637.566990000-121.982656000-0.419832255-0.6723248400.609688598
9456037.520602000-122.049566000-0.420878911-0.6722522670.609046658

The Zip_Axis data above has been limited to those zip codes in the example.

I’ve seen people simply store the latitude and longitude for each zip and then calculate the X, Y, and Z values on the fly in the SQL query. However, I prefer calculating those columns beforehand. The queries will be faster and easier to read. You can do that with a query like this:

Store_Customers_Closest_Store

EmailAddressFirstNameZipStore_ZipStore_PhoneRankingDistance
morpheus@matrix.comMorpheus0054400612555333333311596
neo@matrix.comNeo9456094536555222222215
trinity@matrix.comTrinity00602006125553333333134
You can certainly add more store-related columns to this data extension. For simplicity, I only included a few columns.

Geolocation Query

This query pulls it all together. The round function and case statements do the dirty work of the calculations between the two zip codes. The ranking and partition part picks out the closest store per subscriber.

For further reading

Updates

  • 2018-09-16 – Reviewed and updated the sample data, tweaked a table names on the query and re-tested it.

14
Leave a Reply

avatar
5 Comment threads
9 Thread replies
3 Followers
 
Most reacted comment
Hottest comment thread
6 Comment authors
Micky AroraAmyAdam SpriggsTom PorterDaniel Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Daniel
Guest
Daniel

Hey Adam, great write up, huge thanks for this! What’s your preferred method for calculating the x, y, z coordinates?

Daniel
Guest
Daniel

I found this to be directional in case anyone is interested…
http://stackoverflow.com/questions/19235623/

Daniel
Guest
Daniel

I actually don’t think this is the correct way to find the XYZ coordinates. Adam, do you have a preferred method?

Tom Porter
Guest
Tom Porter

Adam, Silly question, but what units are your XYZ coordinates in? Most solutions I have seen use ether Kilometers or Miles and all tend to be numbers in the thousands.

Wonderful and useful post!

Tom Porter
Guest
Tom Porter

Hmm, maybe relative to radius of Earth??

Amy
Guest
Amy

What if I want to target subscribers in a particular zip code area? For example, I want to send an email to people in zip code range 90000- 91000. So, I only need email addresses. What will my query look like?

Daniel
Guest
Daniel

Is there a missing ‘, distance’ between lines 20 and 21 above? I’m getting an error saying the distance column is invalid. Also, the target data extension doesn’t have a column for the x.ranking, so I just removed that from the query when I pasted it. Not sure if that’s what is causing the issue?

Micky Arora
Guest
Micky Arora

Hi Adam,
I’m using this query and it’s producing results but is calculating wrong distances. I have checked the coordinates and recalculated Xaxis Y axis Z axis link you mentioned above but the end result is still incorrect. Can you calculate distance using same query between Zip 94536 and 94560.
It’s showing 330 for me when in actuality, it’s only 6 miles