Geolocation in the Salesforce Marketing Cloud

How about targeting your Salesforce Marketing Cloud subscribers by zip code? All it takes is a zip code data and a query. There’s a few bits and pieces in the SFMC documentation about it (you’ll need to scroll down to the Geo-Targeting section).  I thought it would be helpful to see a use-case. This example calculates the closest store for each subscriber.

You’ll need:

  • 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

Subscriber Data Sample

EmailAddressFirstNameZip
neo@matrix.comNeo11111
morpheus@matrix.comMorpheus11112
trinity@matrix.comTrinity11113

Store Location Data Sample

Store_NumberStore_ZipStore_Phone
55511115555-222-2222
44411116555-333-3333

Zip code Data Sample

ZipLatitudeLongitudeXAxisYAxisZAxis
1111145.429654-65.9824260.28563807-0.641024400.71238935
1111245.427139-65.9953440.28550626-0.641117350.71235855
1111345.427138-65.9953440.28550627-0.641117360.71235853
I’ve seen people simply store the latitude and longitude for each zip and then calculate the X, Y, and Z values in the SQL query. However, I prefer calculating those columns beforehand. The queries will be faster and cleaner.

Target Data Extension

EmailAddressFirstNameZipStore_ZipStore_PhoneDistance
neo@matrix.comNeo1111111115555-222-22224
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:
Data Extensions
Query Activities
Import Activities
Microsoft Transact-SQL (T-SQL) Reference
Distances between Zips (for verification)

Leave a Reply

5 Comments on "Geolocation in the Salesforce Marketing Cloud"

Notify of
avatar
Sort by:   newest | oldest | most voted
Daniel
Guest

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

Daniel
Guest

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

Tom Porter
Guest

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

Hmm, maybe relative to radius of Earth??

wpDiscuz