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
| EmailAddress | FirstName | Zip |
|---|---|---|
| neo@matrix.com | Neo | 94560 |
| morpheus@matrix.com | Morpheus | 00544 |
| trinity@matrix.com | Trinity | 00602 |
Store_Locations
| Store_Number | Store_Zip | Store_Phone |
|---|---|---|
| 555 | 94536 | 555-222-2222 |
| 444 | 00612 | 555-333-3333 |
Zip_Axis
| Zip | Latitude | Longitude | XAxis | YAxis | ZAxis |
|---|---|---|---|---|---|
| 00544 | 40.815400000 | -73.045600000 | 0.220696498 | -0.723925868 | 0.653624046 |
| 00602 | 18.352927000 | -67.177532000 | 0.368147699 | -0.874828304 | 0.314869354 |
| 00612 | 18.388009000 | -66.665279000 | 0.375877848 | -0.871324772 | 0.315450447 |
| 94536 | 37.566990000 | -121.982656000 | -0.419832255 | -0.672324840 | 0.609688598 |
| 94560 | 37.520602000 | -122.049566000 | -0.420878911 | -0.672252267 | 0.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
| EmailAddress | FirstName | Zip | Store_Zip | Store_Phone | Ranking | Distance |
|---|---|---|---|---|---|---|
| morpheus@matrix.com | Morpheus | 00544 | 00612 | 5553333333 | 1 | 1596 |
| neo@matrix.com | Neo | 94560 | 94536 | 5552222222 | 1 | 5 |
| trinity@matrix.com | Trinity | 00602 | 00612 | 5553333333 | 1 | 34 |
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)
- Calculating x, y, z axis values in SQL
Updates
- 2018-09-16 – Reviewed and updated the sample data, tweaked a table names on the query and re-tested it.