Category: SQL Queries

Suppose you’d like to select rows in an Salesforce Marketing Cloud data extension created on the last day of the month. Here’s what I use:

The last portion of the where clause finds the first day of the next month and then subtracts one day.

Normally queries like this run in a daily automation.

For further reading:

• Query Activity
• T-SQL DATEDIFF function
• T-SQL DATEADD function
• SQL-Server find the last day of any month

Suppose you’d like to select subscribers from an Salesforce Marketing Cloud data extension that were created in the last thirty days. Here’s what I use:

or between two static dates:

For further reading:

• Query Activity
• T-SQL dateadd() function

When you’re writing queries in Salesforce Marketing Cloud sometimes it’s helpful to truncate the time from date columns — like when you want to compare two date columns, one with a time and one without. You can strip the time off of both to make sure you’re comparing apples to apples.

Here’s an example:

The first column returns the full date and time, the second column cast strips the time portion.

date with time	date without time
2013-04-04 14:37:39.363	2013-04-04 00:00:00.000

For further reading:

• Query Activity
• How to return the date part only from a SQL Server datetime datatype

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

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)
• 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.