Creating Segments in Salesforce Marketing Cloud

 

Segmenting data in a Marketing Automation context is essential. Enabling marketers to do so without involving the IT department or having to acquire extensive database knowledge themselves is key to successful marketing execution.

Paul’s story

Meet Paul - a creative marketer working with Salesforce Marketing Cloud. On his journey he faces some challenges with the segments he wants to create in order to personalize his custermer’s communication in the best way possible. Browse through the slides and get to know his story.

Are you like Paul and want to create segments using complex filter criteria in Marketing Cloud, but don’t have the technical expertise to quickly write SQL statements to do so? Check out the possibilities below and select the solution that fits your needs and helps you achieve your goals in a breeze.

Filtered Data Extensions

The easiest way to create segments with out-of-the-box tools in Salesforce Marketing Cloud is using Data Filters that create filtered versions of your data extensions. With a straightforward drag-and-drop user interface you can build your filters based on the fields you already have in a data extension. It is even possible to filter based on data that is available in another connected data extension using Data Relationships.

When running a filter this creates a selection based on the current data available, that is not automatically refreshed. The Filtered Data Extensions can be refreshed via the user interface or added to an automation in Automation Studio when stored as a Data Filter. That way you can ensure your segments are always up to date when you need them to be.

Fig.1 - Creating a Data Relationship
Fig.2 - Filter with Data Relationship

Data Relationships

With Data Relationships in Email Studio you can set up connections between different data extensions. Let’s say you have Contacts (persons) that are linked to Accounts (companies) via the column AccountId on the Contact record. You can connect them via a Data Relationship by selecting the corresponding fields. After the setup is done, the relationship can be used in the drag-and-drop interface of data filters.

If your Account data extension stores the number of employees working for that company, you can now filter the Contacts data extension by that number. With the data filter shown in Fig.2 and the created relationship from Fig.1, you get your result data extension populated with the contacts that are linked to accounts with at least 100 employees.

Even though you can now combine two data extensions for your filter, your resulting data extension will always contain all fields of your source data extension. It isn’t possible to add fields of the connected data extension or remove columns of the source. When sticking to the standard solutions Marketing Cloud provides, this is only possible using SQL queries in Automation Studio.

Measures

Fig.3 - Creating a Measure

If you need engagement data like opens, clicks and bounces as well - maybe even related to a specific send job - you can create Measures in Email Studio, that allow you to filter based on that data. If you only require general engagement data, there are some default measures you can use. Just imagine the data from the measure residing in a separate data extension that you connect with a data relationship. This data is collected/queried from the system data views that can also be used in SQL queries. For more information check out the entity-relationship diagram for data views which I created a while ago.

DESelect

For most marketers the out-of-the-box filters that can be configured using the user interface alone aren’t sufficient. In order to still have the possibility to create the desired segments and add data from multiple data extensions while being able to do everything via an easy to use user interface, external tools are required. I checked out DESelect (to be specific I used the DESelect Advanced edition) which is a payed but affordable app from AppExchange, that promises exactly these capabilities.

What it does

Fig.4 - Example filter using DESelect (click image to enlarge)

DESelect lets you create your segments based on data extensions and data views. When connecting multiple data sources, the records selected to be part of the segment can be defined in multiple different ways (like you can do with different JOIN-statements in SQL). The great thing about this is, that it is even visualized using Venn diagrams. Therefore it is clear to the user which rows will be selected and which aren’t.

Another feature that I find quite valuable are picklists. Within the admin interface you can select a column of a data extension and define it as a picklist. You can then manually add possible values or let DESelect fetch all the currently used values. For every filter that you define from now on, you can select one of the actual values from a dropdown. That feature reduces to error probability when creating selections and it simplifies the process, as there is no need to check the possible values yourself - maybe even in another system.

Storing specific values in an additional column based on formulas or custom logic is also possible and very helpful. You have the possibility to add the following to your result:

  • fixed value
  • fixed value based on some logic (e.g. if a value is in a certain range → write values LOW, MEDIUM or HIGH to the result)
  • formula based value (e.g. date difference, timestamp, time in different timezone)

All of the selections you create are translated to SQL statements in the background and stored as SQL activities in Automation Studio. This allows you to set up automatic refreshes for your segments, as with the standard data filters and manually created SQL activities.

For a full overview of the features, editions, pricing etc. check out the following links:

What it doesn’t do (yet)

DESelect’s target group is marketers, therefore the tool is also designed with them in mind. So it isn’t the right tool for heavy lifting required for extensive data analysis. Here is a list of additional features I’m missing in the current version - a lot of them already on the roadmap:

  • Aggregation and grouping (count, average, minimum, maximum, etc.) → on the roadmap for the next months
  • Selections based on aggregate functions (in SQL that would be a GROUP BY with HAVING) → on the roadmap for the next months
  • Time zone aware filters (e.g. birthday today in timezone GMT+2) → on the future roadmap

Should you have a look at it?

I’ve seen a lot of marketers struggle with the segmentation capabilities in Marketing Cloud due to the lack of extensive SQL skills. Therefore I do recommend having a look at DESelect as it can reduce the effort required for building segments as well as the IT involvement in the marketing campaign execution process.

SQL Queries

If the mentioned possibilities still aren’t enough for your use cases I’m afraid you’ll have to make yourself familiar with SQL queries. Once you have done that, there are a lot of options and (nearly) everything is possible. But you have to consider some limitations:

  • Marketing Cloud only lets you work with SELECT-statements, so you need to familiarize yourself with the different data actions available (overwrite, append, add and update) to reach your goals
  • There is a time limit of 30 minutes per SQL query execution, so keep that in mind and break down larger operations into multiple queries

For specific functions and syntax, have a look at specific Microsoft SQL tutorials, as Salesforce Marketing Cloud uses that under the hood.

Affiliation to DESelect

At the time of writing this article I was neither working for DESelect nor receiving any compensation for this post. I just got free access to a demo account to try out the tool, to be able to share my experience with you. If you have any questions or concerns, please let me know via email or LinkedIn.

Written on November 19, 2020