Creating Complex Selections in Salesforce Marketing Cloud

Creating complex selections in Salesforce Marketing Cloud. Once your customer data has been imported to Salesforce Marketing Cloud you may have some straightforward use cases that build your communications with simple selections and filtering.  For these you can get started straight away, perhaps using Journey Builder or directly in the Studio for your relevant channel.

If your data has come from multiple sources however, you’ll probably be looking to use the power of now having that data in one place, enabling greater targeting, segmenting and personalisation options.  This is where the SQL Query functionality in Automation Studio comes in.

SQL Query Capabilities

The capabilities that exist within SQL Query are based on SQL Server 2016 and will be familiar to all SQL users.  SELECT queries can use UNION, JOIN and GROUP BY and of course WHERE, can be nested.  Conditional IF and CASE statements are available, as are many functions including MIN and MAX.   With these you are able to augment contact data, aggregate transactions and build valuable data from a combination of sources.

There are some unsupported elements of SQL within SQL Query.  These include variables, cursors, stored procedures, temporary tables and common table expressions.

Building in efficiency

The principle of temporary tables remains important. As in any SQL environment, query performance can be impacted by trying to do too much in one go, and without regard for the volume of source data queried and index considerations.  Use interim Data Extensions within separate Automation Studio steps to achieves the same outcome as a temporary table would.  Breaking down the selection into multiple steps and focussed smaller interim Data Extensions can be an aid to performance where one large query would struggle.

It can also make sense to think about common building blocks that will be used across multiple journeys.  Design the automations so that these are recalculated into Data Extensions once after each contributing data is refreshed, to be ready on demand rather than being separately coded and recalculated repeatedly for each different journey.

These building blocks will not only help minimise processing time but also ensure consistency of query across your journeys.  This results in a simplified journey selection process and can also assist in subsequent reporting and analysis.

Campaign & Response Data

SQL Query goes beyond only using a customers own set of data extensions as sources.  Marketing Cloud system data collected by the account from previous activity, is packaged up into out-of-the-box data views.  Commonly used views here include Sent, Open, Click and Bounce, Unsubscribe views for Email data and Journey information.  Other views related to SMS, Surveys and Social tracking are also found here.

The system data views contain data up to a six month history.  If a longer history is needed, then build an automation that extracts from these views within that timeframe, the end destination for this might be another Data Extension or to export and maintain a full history in another of your environments.

Output

You will need to set target Data Extensions for the output of each SQL Query.  These should be created in advance within Contact Builder, they are not able to be created directly within the SQL Query activity.  With a target Data Extension set you’ll next be prompted for the data action.  This can be either Append, Update or Overwrite, pick depending on your requirements.  Choosing the Update option will use the Key that was defined during the Data Extension configuration to identify matches.

There are three ways to run the SQL Query activities.  For a one-off piece of work there is a ‘Run Once’ option directly within SQL Query.  With one or more steps of SQL Queries in an Automation Studio activity, this can be started either with a scheduler or triggered by a file drop.

SQL Query is a powerful tool in the Salesforce Marketing Cloud kit, allowing a data processing complexity beyond simple joins and filters, that might have been out-of-reach while the source data was siloed.  As well as its use for one-off data tasks, once built by the more technical user and included within Automation Studio scheduled or triggered tasks it allows complex data elements to be continually refreshed and recalculated for use by the wider marketing team.