SFMC Query Studio Demystified: 5 Critical Use Cases
In the expansive digital marketing realm, data-driven strategies have emerged as the cornerstone of success. Within this landscape, Salesforce Marketing Cloud’s Query Studio is a formidable tool, empowering marketers to unlock their data’s full potential.
Whether you’re a creative marketer seeking to tailor campaigns or a technical expert aiming to dive deep into data intricacies, this guide is tailored precisely for you. Prepare to delve into a world of expert tips, best practices, and techniques that will supercharge your marketing campaigns.
A Closer Look at Salesforce Marketing Cloud’s Query Studio
Salesforce Marketing Cloud’s Query Studio is a dynamic tool designed to run SQL queries directly on your Marketing Cloud data. What sets it apart from Salesforce Marketing Cloud’s traditional SQL environment is its user-friendly interface. It provides marketers with the capability to fetch, preview, and transform data in real-time, all within an interface resembling SQL Server Studio or MySQL Workbench.
Before we explore Salesforce Marketing Cloud’s Query Studio, it’s essential to grasp the existing features and functionalities within Salesforce Marketing Cloud, as well as the limitations that come with these standard features. Understanding this context will underscore why solutions like Query Studio enhance your marketing campaigns significantly.
The Power of Salesforce Marketing Cloud
Salesforce Marketing Cloud (SFMC) is renowned for its robust suite of digital marketing tools designed to empower marketers in their data-driven endeavors. At its core, SFMC offers a range of features that allow users to interact with and manipulate data:
Data Extensions Filters: These are akin to tables in traditional databases, storing rows of data with defined columns. Marketers use them to segment and target their audience based on various attributes. Filtering data within these extensions is straightforward, with a user-friendly interface even newcomers can navigate.

Basic SQL Query Functionality: SFMC provides a basic interface to run SQL queries for those familiar with SQL. This feature is handy for complex data manipulations extending beyond simple filtering. Marketers can extract, transform, and load (ETL) their data to suit specific campaign needs

Automation Studio: This is where the magic of automation happens. Marketers can set up workflows to automate tasks like data imports, data filtering, and even running SQL queries at scheduled intervals.

However, while these features are powerful, they come with their limitations:
- Single Data Extension Filtering: One significant limitation of Salesforce Marketing Cloud’s native capabilities is the restriction to filter within a single data extension at a time. Combining or comparing data across multiple data extensions is a challenge for the native tools, particularly when trying to gain a comprehensive view of customer interactions across different data sources.
- Performance of Filters: Filtering data within large data extensions can be sluggish, leading to delays in campaign launches or data analysis tasks. For marketers working in real-time or with tight schedules, these delays can impact the effectiveness and timeliness of their campaigns.
- Limitations in Joining Data: Salesforce Marketing Cloud’s Data Extension Filters make it challenging to cross-join data extensions, hindering marketers from gaining a holistic view of their data.
- Complexity of Advanced Queries: While basic filtering and data manipulation are straightforward, creating advanced SQL queries requires a deeper understanding of the language, which can be a barrier for marketers without an SQL background.
- Lack of Real-time Result Viewing for SQL Queries: A significant constraint with Salesforce Marketing Cloud’s native SQL capabilities is the absence of real-time result previews. When an SQL query is executed, the output is directed and stored in a separate data extension instead of being immediately displayed for review. This fragmented approach can be cumbersome and time-consuming, particularly when iterative query adjustments are needed.
- Performance Issues: Running complex queries on large data extensions can lead to performance problems, including slow query execution or timeouts.
Given these limitations, there was a clear need in the digital marketing landscape for a solution that seamlessly combined user accessibility with sophisticated data manipulation. Enter Query Studio for Salesforce Marketing Cloud.
Query Studio: The Game-Changer
Query Studio isn’t just another tool in the marketer’s toolkit; it’s the linchpin that connects marketers to their data more intuitively and efficiently. Specifically crafted to address the challenges of real-time result viewing and multi-data extension filtering, Query Studio allows users to run SQL queries and instantly preview the results. This real-time feedback loop accelerates the data exploration, enabling marketers to make quicker, data-informed decisions.
Furthermore, Query Studio’s design inherently tackles the challenge of filtering across multiple data extensions, providing a more holistic view of customer data without cumbersome steps. By offering an environment that marries the simplicity of user-friendly interfaces with the power of advanced SQL capabilities, Query Studio is the beacon for those looking to elevate their data-driven marketing strategies.
Key Features & Benefits: Unveiling Query Studio’s Power
As we explore the world of Query Studio, it’s clear that this tool redefines how we engage with and comprehend our data within Salesforce Marketing Cloud. Let’s take a closer look at the essential features that make Query Studio an invaluable asset for tech-savvy marketers:
Interactive Querying: Gone are the days of navigating SQL queries in the dark. Query Studio empowers you to execute SQL commands and instantly witness the results. This real-time feedback accelerates your data exploration and cultivates a more intuitive grasp of your data manipulations.
Data Preview: Have you ever made a change and immediately wished you hadn’t? Query Studio’s data preview feature ensures you remain in control. Before committing to any alterations, you can preview the data, guaranteeing that your modifications align with your intentions. This not only reduces errors but also elevates data integrity.
Data Export: Data isn’t bound to a single platform. Query Studio simplifies data export, whether you want to share insights with a colleague or conduct further analysis using another tool. You can effortlessly export your results with just a few clicks, facilitating seamless cross-platform data utilization.
Syntax Highlighting: SQL, while powerful, can be intricate. Query Studio’s syntax highlighting feature enhances the readability of your SQL code by distinguishing commands, variables, and values using color codes. This makes the code easier to read and aids in swiftly identifying errors, ensuring smooth query execution.
Validation Tests: One of Query Studio’s standout features is its built-in validation tests. Before running a query, the platform conducts a series of checks to ensure the integrity and correctness of your SQL code. This proactive measure identifies potential errors or inconsistencies, enabling you to address them before execution. Not only does this save time by preventing erroneous outputs, but it also instills confidence, knowing that your queries are meticulously vetted for accuracy. In a realm where precision reigns supreme, this validation feature is a game-changer, ensuring your data manipulations are effective and error-free.
Reusable Templates: Efficiency takes center stage. Recognizing that many SQL queries are recurrent, Query Studio offers a feature to save frequently used queries as templates. This means you don’t have to rewrite or reconfigure a query you’ve employed before. Load the template, make the necessary adjustments, and you’re ready to roll, conserving time and effort.
Unlocking the Potential of Your Data
Data takes on diverse forms and structures in the vast ecosystem of Salesforce Marketing Cloud. At its core are Data Extensions akin to tables in traditional databases. They neatly store rows of data with defined columns, enabling marketers to segment and target their audience based on various attributes. However, the data landscape within Salesforce Marketing Cloud is more intricate than these extensions alone.
An often-underestimated gem lies in Data Views, which are system-generated, read-only tables offering a glimpse into the historical tracking data of your account. These tables capture crucial metrics such as email sends, opens, clicks, bounces, etc. Essentially, they unveil the backstage of subscriber engagement, enabling marketers to glean insights into past interactions and behaviors.
With Query Studio, marketers can seamlessly interact with Data Extensions and Views. This dual capability creates more comprehensive data exploration:
- Interacting with Data Extensions: When you launch a query in Query Studio, you’re instructing the platform to retrieve or modify data from these extensions. Moreover, leveraging the power of SQL, you can seamlessly join multiple data extensions, filter records, or even craft new calculated fields, enriching your data landscape and providing more nuanced insights for your campaigns.
- Tapping into Data Views: Query Studio’s ability to query Data Views opens up a world of analytical possibilities. Marketers can analyze historical engagement trends, segment subscribers based on past interactions, and forecast future behaviors. Marketers curate more informed and impactful campaigns by blending insights from Data Views with current data from Data Extensions.
In essence, Query Studio’s versatility in handling both Data Extensions and Data Views ensures that marketers attain a 360-degree view of their data, paving the way for more profound insights and data-driven decision-making.
The Foundation: Understanding SQL
Before delving into the practical applications and intricacies of SQL within Query Studio, let’s embark on a journey to grasp the fundamentals of SQL, or Structured Query Language.
What is SQL?
At its essence, SQL serves as a language that facilitates communication with databases. Think of it as the bridge connecting human queries with data-driven responses. Whether you seek to retrieve specific data, update values, or delete records, SQL equips you with the tools to achieve these tasks.
Basic SQL Operations:
- SELECT: This command retrieves data from a database. For instance, to view all records from a table named ‘Subscribers,’ you’d use: SELECT * FROM Subscribers;
- INSERT: It permits the addition of new records to a table. For instance, to add a new subscriber, you might use: INSERT INTO Subscribers (FirstName, LastName) VALUES (‘John’, ‘Doe’);
- UPDATE: This command modifies existing records. If you wish to change John Doe’s last name to ‘Smith,’ you’d use: UPDATE Subscribers SET LastName = ‘Smith’ WHERE FirstName = ‘John’;
- DELETE: It removes records from a table. To remove John Smith from ‘Subscribers,’ you’d execute: DELETE FROM Subscribers WHERE FirstName = ‘John’ AND LastName = ‘Smith’;
Understanding these foundational SQL operations lays the groundwork for harnessing the full potential of Query Studio within Salesforce Marketing Cloud.
Delving into SQL Joins:
SQL Joins are pivotal in merging data from multiple tables (or data extensions) based on related columns. They are essential tools in SQL, enabling you to create comprehensive datasets from fragmented information.
Table of Contents
Visual Representation
SQL Query
Description


Select <Field List>
FROM DataExtensionA A
LEFT JOIN DataExtensionB B
ON A.Key = B.Key
Select <Field List>
FROM DataExtensionA A
LEFT JOIN DataExtensionB B
ON A.Key = B.Key
WHERE B.Key IS NULL
LEFT JOIN (or LEFT OUTER JOIN): These two options return all records from the left table, and the matched records from the right table. Unmatched records from the right table will appear as NULL.

Select <Field List>
FROM DataExtensionA A
INNER JOIN DataExtensionB B
ON A.Key = B.Key
INNER JOIN: Fetches rows from both tables that satisfy the given condition. It focuses on the commonality between two tables.


Select <Field List>
FROM DataExtensionA A
FULL OUTER JOIN DataExtensionB B
ON A.Key = B.Key
Select <Field List>
FROM DataExtensionA A
FULL OUTER JOIN DataExtensionB B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL
FULL JOIN (or FULL OUTER JOIN): Combines the results of both LEFT and RIGHT joins. It returns all records when there’s a match in either the left or the right table.


Select <Field List>
FROM DataExtensionA A
RIGHT JOIN DataExtensionB B
ON A.Key = B.Key
Select <Field List>
FROM DataExtensionA A
RIGHT JOIN DataExtensionB B
ON A.Key = B.Key
WHERE A.Key IS NULL
RIGHT JOIN (or RIGHT OUTER JOIN): Opposite of the LEFT JOIN. It returns all records from the right table, and the matched records from the left table.
As we venture deeper into SQL, it’s crucial to recognise that Salesforce Marketing Cloud’s Query Studio introduces its own unique twists. While the fundamental principles of SQL remain intact, Query Studio deviates slightly from traditional SQL conventions. Here, what we traditionally regard as ‘databases’ are represented as data extensions or data views. Furthermore, operations like ‘INSERT,’ ‘UPDATE,’ and ‘DELETE,’ which are standard in traditional SQL, take on a different configuration within Query Studio.
When you save your SQL query within Salesforce Marketing Cloud, these operations are configured as settings. Additionally, SQL queries in Query Studio do not terminate with ‘;’ as in traditional SQL. Certain advanced commands/functions may also have restrictions but worry not – the validation messages in Query Studio are usually informative, guiding you through these differences.
As we delve further into this journey, you’ll become well-versed in navigating these distinctions, ensuring you can fully leverage Query Studio for your data-driven marketing initiatives.
Data Extensions: The Backbone of Your Data Structure
Data extension structures are pivotal in shaping how you interact with your audience data within Salesforce Marketing Cloud. While each organization may have unique data configurations, a typical data hierarchy emerges, which many Salesforce Marketing Cloud clients adopt. This hierarchy streamlines data management and lays the foundation for intricate data manipulations using tools like Query Studio.
Let’s explore this conceptual data hierarchy:

- In Salesforce Marketing Cloud, our Data Hierarchy is stored under Data Extensions, and a visual representation of the interconnectivity of each data extension can be visualized below.

- Accounts: These form the backbone of any business data structure, representing the companies or entities you engage with, including partners, competitors, customers, or other stakeholders.
- Contacts: Individuals associated with these accounts, such as employees, representatives, or any person linked to an account.
- Contracts: Legal agreements between your company and these accounts detailing the terms of your business relationship.
- Customers: These are the individuals or entities purchasing your products or services, essentially the lifeblood of any business.
- Emails To Exclude: This is a critical data extension, particularly in email marketing, containing a list of contacts who have opted out or should not receive certain communications.
- Opportunities: These represent potential sales or deals, tracking the possibility of a contract or sale to a customer.
- Orders: Confirmed customer requests for products or services, signifying a successful opportunity conversion.
- Order Line Items: These provide granular details of each order, breaking down the products or services requested.
- Products: The items or services offered to customers, forming the core of what your business sells or provides.
While we won’t delve into the intricate configurations of each data extension and its fields, it’s essential to understand each entity’s relationship between data extensions. This understanding serves as a blueprint for how we join data and, more importantly, ensure we obtain the correct selection results.
With this understanding in place, we’re poised to demonstrate the capabilities of Query Studio. Using this typical data hierarchy, we’ll craft SQL queries to join, query, and build audience segments, showcasing the tool’s prowess in real-world scenarios.
Data Hygiene: Fuelling Your Marketing Machine
Just as a well-oiled machine operates at its best with clean fuel, your marketing strategies thrive on clean, accurate data. This is where the critical concept of data hygiene comes into play.
Understanding Data Hygiene: Data hygiene is the meticulous process of ensuring that a dataset is accurate but also consistent and usable. It involves identifying and rectifying errors, inconsistencies, and inaccuracies in data. In marketing, good data hygiene is akin to the compass guiding your campaigns to reach the right audience at the right time and with the right message.
The GDPR Perspective: Introducing regulations like the General Data Protection Regulation (GDPR) has elevated data hygiene from a best practice to a legal obligation. GDPR mandates that organizations handle personal data carefully, emphasizing accuracy and relevance. Regular data cleansing is not just advisable; it’s essential to remain compliant, avoid substantial fines, and, most importantly, maintain the trust of your customers.
Query Studio: Your Data Cleansing Ally
Salesforce Marketing Cloud’s Query Studio isn’t just a querying tool; it’s a potent instrument for data hygiene. Thanks to its robust SQL capabilities, you can:
- Identify and eradicate duplicate records
- Correct or transform data values
- Standardize data formats
- Validate and correct data types
For instance, if an email field contains values that don’t resemble email addresses or a date field has entries in multiple formats, these inconsistencies can be swiftly identified and rectified using SQL queries in Query Studio
The Crucial Role of Correct Data Types
Data isn’t merely about values; it’s about the context these values represent. An incorrect data type can distort this context. For instance, treating a numeric value as a text string can hinder mathematical operations. Ensuring that each field in your dataset has the correct data type is paramount for accurate analysis and operations.
In the world of data, there’s a fundamental principle: “The quality of your input determines the quality of your output.” This idea succinctly emphasises that the results you obtain are intricately linked to the quality of the data you start with. If your data is filled with mistakes, variations, or inaccuracies, these issues will ripple through all your marketing endeavours, potentially undermining their impact.
Use Case #1: COUNTing Records
In many scenarios, marketers require a quick overview of the volume of records within a specific data extension or the expected result from a SQL query. This snapshot allows you to gauge the size of a potential audience or data selection without running an entire data selection. Counting records provides vital insights into the data extension’s size and possible applications.

This SQL query returns a single value, ‘TotalRecords,’ representing the total number of records within the ‘Customers’ data extension. The same logic can be applied to any other data extension within your hierarchy to obtain a count of its records. It’s worth noting the aesthetics of Query Studio’s syntax colors and line numbers, which prove invaluable when validation errors occur, helping you quickly spot mistakes and typos.
Upon clicking the ‘Run’ button, Query Studio enters a processing queue on the Salesforce Marketing Cloud server. The processing time depends on various factors, including data volume and the time of day. Peak times may result in longer processing waits.
Use Case #2: WHERE Statement – Filtering our Data
In the world of data, precision is vital. Whether you’re homing in on a specific audience segment, dissecting product sales, or tracking account activities, the ability to filter data is indispensable. SQL’s WHERE statement is your precision instrument, enabling you to set conditions that filter and retrieve precisely the data you need from a data extension.
Building upon our previous example of counting records, let’s now put the WHERE statement to work by counting how many records exist in the ‘DESelect_DEMO_Customers’ data extension where the ‘Country’ field is ‘Canada’.

The WHERE clause, in SQL parlance, acts as the gatekeeper, allowing only records that meet specified conditions to pass through. These conditions can range from straightforward, like gathering all customers from a particular country, to more intricate, involving multiple criteria, such as country and gender.
As we delve deeper into SQL filtering, you’ll inevitably encounter scenarios where multiple conditions must be satisfied simultaneously, or any of several conditions will suffice. This is where the versatile AND and OR operators come into play:
AND Operator: This operator enters the scene when you need records that meet more than one condition, and all of these conditions must be true. For instance, if you’re in search of customers from Canada who are also male, both conditions must hold.

OR Operator: Conversely, the OR operator steps in when you filter based on multiple conditions, and it’s sufficient for any of these conditions to be true. For example, if you’re seeking customers from Canada or Ireland, satisfying either condition will fetch the record.

But a word of caution is in order here. Combining these operators requires deliberate care. Misusing or misordering them can lead to unintended results. For instance, without careful bracketing or logical sequencing, you might unwittingly retrieve records that don’t match your intended criteria.
For instance, imagine you’re trying to gather contacts from New York with an ‘Active’ status or contacts from Los Angeles, irrespective of their status. A poorly constructed query could net active contacts from cities other than New York, leading to data inaccuracies.
As a challenge and a learning opportunity, try to construct what the SQL query for this scenario would look like before reading on. If you’re grappling with the technical aspects of SQL, feel free. Keep your commitment to learning, as practice makes perfect. Stay tuned as we’ll discuss alternative tools that can simplify your data operations without a need for deep SQL knowledge.
The correct answer is:

In this query, the thoughtful use of parentheses ensures that conditions are evaluated in the precise order needed to yield the desired results.
While the AND and OR operators are potent tools for refining your data queries, they should be handled with care. Proper structuring and a clear grasp of the logic you wish to implement are vital to ensure the accuracy of your results.
Use Case #3: FIELDS and Output
As we venture further into the world of SQL, it’s essential to grasp that numbers alone sometimes don’t paint the complete picture. The actual data within fields is often the key to understanding your audience or your business. Visualizing the data fields can unveil profound insights, whether you’re delving into customer demographics, scrutinizing product details, or dissecting account activities.
In Query Studio, making these data fields visible is a straightforward process. You can achieve this by adding the names of the data extension fields you wish to appear in the SELECT statement of your SQL query.

The selected results are then displayed in the output window, with records typically presented ten at a time for easier readability.

However, in Salesforce Marketing Cloud, field names sometimes contain spaces, reserved words, or special characters. While these naming conventions might be more human-readable, they can pose challenges during SQL querying. Additionally, there are situations where you might need to customize or transform the output, like combining fields or renaming them for clarity.

As shown earlier, dealing with field names containing spaces or special characters in SQL requires enclosing them in square brackets [ ] to ensure they’re recognized correctly. Furthermore, SQL provides the ability to rename (or alias) the output fields using the AS keyword. This is handy when you want the output to have a different name than the source field.
When saving the results of a query to a destination data extension in Salesforce Marketing Cloud, the destination data extension must contain fields with the same names specified in the query output. Mismatches between the output and destination fields can lead to errors or failed query executions.
You can also use SQL’s AS term when merging fields from your source data extension into a single output field. For example, you might want to concatenate [First Name] and [Last Name] into a single field called [Full Name]. The SQL query to achieve this is relatively straightforward.

This query takes the First Name and Last Name fields, combines them with a space in between, and outputs the result as Full Name.
Understanding these nuances in a platform like Salesforce Marketing Cloud’s Query Studio can significantly enhance your data querying capabilities, allowing for more tailored and precise outputs that suit your needs.
Use Case #4: SAVE your Query and Results
Now that you’ve delved into crafting SQL queries in Query Studio, it’s time to explore how to save your queries. This step is essential if you intend to reuse your queries in Automation Studio for routine audience updates.
Saving your query is straightforward. Just click on the ‘Save As’ button and give your query a meaningful name. However, there are a couple of critical considerations when doing this.
First, you must select a destination data extension where you want to store the results. It’s essential to ensure that this data extension contains the fields or data schema you’ve specified in your SELECT statement. This alignment between your query’s output and the destination data extension is crucial for smooth execution.

While there’s an ‘Export in Contact Builder’ feature that theoretically should create the data extension for you, it’s worth noting that it can sometimes be finicky. As a best practice, it’s often more reliable to manually create the data extension schema, ensuring it matches your query’s output.
After you’ve saved your query, you’ll also need to decide on the Data Action to take. This is a pivotal step that determines how the results of your query will interact with existing data. There are three distinct Data Actions to choose from: Overwrite, Update, and Append. Each action serves a specific purpose and can significantly impact your data.
Overwrite:
- Purpose: This action replaces the entire content of a target data extension with the results of your SQL query.
- Use Case: Consider a scenario where you have a monthly list of active subscribers, and at the beginning of each month, you want to refresh this list based on new activity. To achieve this, you can utilize the ‘Overwrite’ action. This action essentially swaps out the old data with the new data, guaranteeing that your list stays up-to-date and precise.
Update:
- Purpose: Instead of replacing all the data, the ‘Update’ action modifies only specific records in the target data extension based on the results of your SQL query.
- Use Case: Suppose you have a data extension of customers, and you’ve recently run a campaign where some customers updated their contact information. Instead of replacing the entire data extension, you’d use the ‘Update’ action to modify only those records with new information, leaving the rest untouched.
Append:
- Purpose: This action adds the results of your SQL query to the end of an existing data extension without altering the current records.
- Use Case: Imagine you have a data extension of newsletter subscribers, and after a recent event, you’ve gathered a new list of interested participants. Using the ‘Append’ action, you can add these new subscribers to your existing list without affecting the original subscribers.
Choosing the right Data Action is pivotal. An incorrect choice can lead to data loss, duplication, or inaccuracies. For instance, using ‘Overwrite’ when ‘Append’ was intended could erase valuable data. Conversely, appending data without checking for duplicates might lead to redundancy.
So, when saving your query and specifying the Data Action, consider the nature of your data and the intended outcome carefully. This decision ensures that your data remains accurate and aligns with your marketing objectives.
Use Case #5: DATEDIFF – Calculating time intervals between dates, plus Birthdays/Anniversaries
Whether you’re deciphering the time lapse between a customer’s initial interaction and their purchase, scrutinizing the timing of email dispatches, or commemorating your customer’s birthdays, the ability to compute date differences holds immense value. This is precisely where the DateDiff function in SQL steps in.
The DateDiff function is a potent tool for computing the disparity between two dates and presenting the outcome in the units you designate, such as days, months, or years. It proves incredibly useful when you aim to glean insights into customer behaviors or make data-driven choices grounded in time intervals.
However, there are some considerations to bear in mind. Firstly, the DateDiff function operates with ‘Date/Time’ fields, so maintaining consistent date formatting is imperative. Additionally, it necessitates three parameters in the correct sequence: the unit (e.g., day, month, or year), the commencement date, and the culmination date.

Let’s delve into an illustration. Picture this: you wish to ascertain how many days transpire between the placement of an order and its delivery. By computing the variance between the ‘Order Date’ and the ‘Delivery Date,’ you can acquire this data. This calculation serves to enlighten you on delivery durations and empowers you to apprise customers about any potential delays.

Now, let’s tackle a fun and practical scenario. How about identifying customers who are celebrating their birthday today? Sending personalized birthday greetings or offers is a fantastic way to engage with your customers and build loyalty. But here’s the twist – birthdays happen every year, and you don’t want to congratulate someone multiple times in a year!
To do this, you’ll need to leverage the DateDiff function creatively. Instead of comparing the birthday field directly to today’s date, which includes the year, you’ll calculate the difference in years between the birthday and today. If this difference, when added to the birthday, equals today’s date (excluding the year), you’ve got a match.
In simpler terms, this condition helps you identify people who are celebrating their birthday today, regardless of which year they were born. It’s a smart way to handle anniversaries like birthdays.

You’ve got some handy date functions at your disposal in SQL. For instance, there’s GetDate(), which fetches the current date and time based on your system’s clock. It’s perfect for when you need the current timestamp.
In substitution to GetDate, there’s GetUTCDate(), and this one is particularly valuable for global applications. It provides the date and time in Coordinated Universal Time (UTC), which remains consistent regardless of time zones or daylight-saving changes. This is essential when you want to work with a standardized time reference across different regions.
So, you see, SQL equips you with the tools to work with time and dates effectively, no matter where your data or users are located. Functions like YEAR(), MONTH(), and DAY() are also handy when you need to extract specific date components, like the year or month.
As you can see, we are starting to expand further our knowledge of SQL with some additional functions:
- GetDate() : This function returns the current date and time based on the system’s clock. When executed, this will return the current date and time, including hours, minutes, seconds, and milliseconds. It’s commonly used when you need to compare a date in your data to the current date and time.
- GetUTCDate() : Whilst not used in our Query above, it’s an important one to call out. Unlike GETDATE(), which returns the current date and time based on the system’s local settings, GETUTCDATE() provides a consistent time reference that isn’t influenced by time zones or daylight-saving changes. Therefore, we can use GetUTCDate() in substitution to GetDate() as it returns the current date and time based on the Coordinated Universal Time (UTC), which is essentially the world’s standard time reference.
- YEAR(), MONTH(), DAY() : These individual functions are particularly useful when you want to isolate or compare just a portion of a date e.g. Year, Month, Day.
As we’ve learned earlier, the WHERE function can be used be used to filter or match the results, and within the WHERE criteria we can also use DateDiff and other SQL functions.
In our example, The WHERE condition is checking if the sum of the difference in years between the Birthday and the current date (DateDiff(year, Birthday, GETDATE())) and the year of the Birthday (YEAR(Birthday)) is equal to the current year (YEAR(GETDATE())).
Using our example:
- Difference in years: 33
- Year of Birthday: 1990
- Sum: 33 + 1990 = 2023
- Current year: 2023
Likewise, the WHERE condition checks if the day and month of the Birthday field match today’s day and month, ensuring that only those celebrating their birthday today are retrieved.
The purpose of this condition is to identify records where the day and month of the Birthday match the current day and month, but without considering the year. This is a clever way to identify anniversaries, like birthdays, which occur on the same day and month every year but have a different year.
Let’s blow your mind even further – say you want to calculate the age of the customer and store it in the data extension. With the knowledge you’ve so far learned, how do you think you’d go about this?
It’s pretty simple, as you already have all of the code above, and you should be familiar with the AS function for renaming fields – remember?

These date functions are fundamental tools in SQL, especially when working with date-time data. They allow for precise extraction and manipulation of specific components of dates, enabling detailed analyses and comparisons. In Salesforce Marketing Cloud’s Query Studio, understanding and effectively using these functions can greatly enhance data querying capabilities, allowing for more tailored outputs and insights.
When combined, as seen in the birthday example, these functions can be used to craft complex conditions and criteria, ensuring that you can extract exactly the data you need, no matter how specific or nuanced your requirements might be.
A Quick Use Case Summary
Our journey into the world of SQL and Salesforce Marketing Cloud’s Query Studio has been nothing short of fascinating. We’ve touched the surface of its vast potential through the examples and use cases explored here.
While these scenarios provide a solid foundation for your data-driven marketing endeavors, SQL, and Query Studio offer an ocean of possibilities. The depth and versatility of SQL, combined with the power of Query Studio, are limitless.
Limitations of Query Studio
While Salesforce Marketing Cloud’s Query Studio offers valuable features for marketers, it’s crucial to understand its limitations to work effectively within its boundaries or explore alternative solutions when necessary. Let’s take a closer look at some important limitations to keep in mind:
- Query Length:
- Constraint: Queries in Query Studio have a character limit. Extremely lengthy or complex queries might not run or even fit within the tool.
- Implication: Query writers need to be concise and efficient in their query construction. If a query is too long, it may need to be broken down into multiple smaller queries or optimized for brevity. Here, it is necessary to know SQL well enough that you can make any necessary query, no matter how complex, in as few strings as possible.
- Execution Time:
- Constraint: There’s a maximum time limit (typically 30 minutes) for how long a query can run in Query Studio. If a query exceeds this, it will time out and fail to execute.
- Implication: This limitation can be challenging when working with large datasets or performing complex data manipulations. Users may need to optimize their queries for performance, break them down into more minor queries, or consider running them during off-peak times.
- Complex Joins:
- Constraint: While SQL allows for joining multiple tables or data extensions, doing so with large data extensions in Query Studio can be resource-intensive and slow.
- Implication: Joining multiple large data extensions can increase execution time (risking timeouts) and consume more resources, potentially affecting other processes. Users should strategically plan joins, ensuring they are necessary and optimized for performance.
- Error Feedback:
- Constraint: Query Studio might not always provide detailed error messages, making it challenging to pinpoint issues in a query.
- Implication: Debugging and troubleshooting can become time-consuming. Users may need to rely on their SQL knowledge or seek external resources to understand and resolve errors.
- Data Volume:
- Constraint: Query Studio is primarily designed for querying, not bulk data processing. It might not be the most efficient tool when dealing with millions of records.
- Implication: Users should consider other tools or platforms within Salesforce Marketing Cloud for extensive data processing tasks or break down tasks into more manageable chunks.
- Technical Language and Complexity:
- Constraint: SQL is a Programming Language, and Query Studio is just an SQL Query execution tool. Users require a steep learning curve to upskill on SQL, functions, and practices.
- Implication: The technicalities can become overwhelming for many non-developers, and mistakes can easily be made (e.g. selecting the wrong audiences).
Understanding these limitations is crucial for the efficient use of Query Studio. In some cases, users may find that more advanced tasks or working with extensive datasets are better suited for other tools or platforms within Salesforce Marketing Cloud or external data processing solutions.
Simplifying with DESelect
DESelect provides a valuable solution for marketers looking to simplify the process of data querying and manipulation in Salesforce Marketing Cloud. It bridges the gap between traditional marketing practices and the complexities of SQL, making data-driven marketing more accessible and efficient.

Circling back to the benefits of Query Studio, DESelect has similar functions.
Query Studio Benefit: Interactive Querying
DESelect Segment’s drag-and-drop interface is designed for marketers, allowing them to visually build complex queries without the need for extensive SQL knowledge. There’s SQL under the hood driving each query, but this intuitive approach makes it easier for marketers to engage with data and create targeted segments.
Benefit: Data Preview
Review data before submitting a query, previewing elements such as how many records your query will return, a detailed view of the created data extension, and any errors that would hinder results.
Benefit: Data Export
DESelect seamlessly integrates with Salesforce Marketing Cloud, ensuring smooth data flow between the platforms. This integration streamlines workflows and enhances productivity for marketers.
Benefit: Validation Tests
DESelect optimizes queries in the background, ensuring they run efficiently. This reduces the risk of query timeouts and allows marketers to focus on deriving insights from their data rather than troubleshooting performance issues.
Benefit: Reusable Templates
DESelect admins use segmentation templates for commonly run campaigns, in effect saving users’ time and preventing mistakes. Once you have templates in place, you can reuse them for similar segments in the future.
Here are some key ways that DESelect Segment improves on Query Studio:
Advantage: The Scope of Advanced Segmentation
DESelect Segment offers powerful segmentation features that are both user-friendly and robust. Marketers can create intricate data segments for various purposes, such as targeted campaigns, A/B testing, and audience analysis, leading to more personalized and effective marketing efforts.
Advantage: Guided Workflows
DESelect provides guided workflows that walk users through the data querying and manipulation process step by step. This guidance reduces errors and enhances efficiency, making it suitable for users of all skill levels.
Overall, DESelect empowers marketers to make the most of their data within Salesforce Marketing Cloud, whether they are SQL experts looking for optimized performance or newcomers seeking a more user-friendly interface. It’s a valuable tool for data-driven marketing that makes complex tasks more accessible and efficient.
How to Get Started with DESelect
Beginning your journey with DESelect is a straightforward plug-and-play process designed to quickly get you started harnessing the power of your data within Salesforce Marketing Cloud.
DESelect works with a list of certified implementation partners, to provide teams new to Marketing Cloud with top-tier solutions and expertise within the Salesforce ecosystem.
Learn how to find the best solution for your segmentation needs.

William McMahon
CEO & Founder at Gravitai, William has over 15 years experience in the CRM world as both a top UK Salesforce Partner and Ex-ExactTarget Principal Solutions Architect.
DESelect | Best Salesforce Marketing Cloud Solutions Blog | How to Write SQL: The Most Common Queries in SFMC
Structured Query Language (SQL) is a powerful programming language designed for managing data within relational databases. Similar to Excel, these databases organize information in tables, utilizing rows and columns to represent distinct data attributes and their relationships. SQL enables users to execute commands for storing, updating, removing, searching, and retrieving data, contributing to efficient database management.
Widely embraced across diverse applications, SQL is favored by data analysts and developers for its seamless integration with various programming languages, such as Java. Its user friendly nature, employing common English keywords, makes SQL accessible for learners and facilitates the creation of high-performance data processing applications.
For those who want to learn how to write SQL, we will review concepts specific to Marketing Cloud, the parts of a query, and applying these queries to common use cases.
RELATED: Script Activity – The underrated SFMC powerhouse
What is SQL used for in Salesforce Marketing Cloud?
In Salesforce Marketing Cloud, data extensions act like tables for storing and organizing data. They help structure information within the platform, allowing users to interact with and manipulate data in these extensions, in turn facilitating smooth integration into marketing efforts and analytics.
These extensions can store diverse data like customer details, preferences, and buyer behavior. SQL queries offer a robust way for marketers to efficiently extract, filter, and organize data within these extensions.
SQL Essential Tasks
Querying and Manipulating Data
Querying with SQL empowers marketers and administrators to pinpoint precise data among complex datasets and manipulate it, enabling users to transform and organize information to align with marketing goals. This capability not only streamlines analysis and operations but also ensures extracted data is structured effectively for activation. Ultimately, SQL in Salesforce Marketing Cloud goes beyond surface-level activity, unlocking a more powerful understanding of the customer base for data-driven marketing strategies.
Extracting and Filtering Data
SQL queries serve as a strategic tool, allowing users to sift through large volumes of information and focus solely on relevant data, simplifying the complex process of data extraction. The versatility of SQL is evident in its filtering capabilities, enabling marketers to pinpoint specific criteria crucial to their objectives.
Whether isolating data based on demographics, customer preferences, or behavioral patterns, SQL’s filtering functionality offers precise control that not only streamlines data extraction but also ensures the relevance of extracted information.
By leveraging SQL for data extraction and filtering in Salesforce Marketing Cloud, users optimize workflows, removing the need to manually hunt through data extensions. This targeted approach not only saves time but also ensures the extracted data is complete and accurate.
Organizing Data
SQL efficiently organizes data, offering robust tools beyond data retrieval. It establishes a coherent structure within databases, aligning with the analytical and reporting needs of marketers and administrators. This structured format ensures data is stored for effective analysis, fundamental for extracting meaningful insights and informed decision-making.
SQL enables the creation of easily navigable databases optimized for marketing strategies, laying the foundation for streamlined reporting. This organized data architecture enhances overall efficiency in data management, empowering users to make confident, data-driven decisions within Salesforce Marketing Cloud.
Segmentation of Audiences in Marketing Campaigns
In Salesforce Marketing Cloud, knowing how to write SQL queries plays is pivotal to marketers aiming to finely segment their customer base. SQL facilitates the strategic division of customers into distinct groups based on factors like demographics, preferences, and previous interactions.
SQL’s instrumental role in audience segmentation lies in its ability to analyze and interpret diverse datasets. Marketers craft SQL queries to pinpoint specific criteria, allowing for the creation of targeted segments that move beyond broad categorizations, offering a nuanced understanding of the customer base.
Segmentation criteria can vary widely, from geographic location to purchase history or engagement levels. With SQL’s capabilities, marketers gain the flexibility to define these criteria precisely, ensuring each segment is tailored to unique characteristics and behaviors.
Once audiences are segmented, marketers can deploy highly targeted and relevant marketing messages to each group. This level of personalization enhances campaign effectiveness, as messages resonate more directly with the specific interests and needs of each segment. Ultimately, SQL’s role in audience segmentation within Salesforce Marketing Cloud becomes a cornerstone for delivering impactful and tailored marketing experiences to diverse customer groups.
Facilitating Personalized Communication
SQL emerges as a powerful tool for marketers aiming to enhance their communication strategies. Through strategic use of SQL, marketers craft campaigns that go beyond generic messaging, focusing on targeted and personalized communication through specific audience criteria.
SQL’s ability to interact with customer attributes tailors campaigns to individual preferences. Marketers often use SQL queries to identify specific customer segments based on demographics, purchase history, or engagement patterns, allowing for the creation of campaigns that speak directly to the unique characteristics and interests of each segment. This personalized communication resonates more deeply with these audiences, establishing a meaningful connection more likely to drive conversions and purchases.
In essence, SQL’s role in Salesforce Marketing Cloud goes beyond data manipulation – it becomes a catalyst for elevating communication strategies, where each message is not just targeted but genuinely resonant with the diverse needs of the audience.
How to Write SQL: The Parts of a Query
In a SQL query used for Salesforce Marketing Cloud, several key components come together to retrieve, manipulate, or manage data. Here’s an overview of the essential parts:
SELECT Clause
Specifies the columns or fields to be retrieved from the database. For example, SELECT FirstName, LastName would retrieve data from these specific columns.
FROM Clause
Specifies the source table or data extension from which the data is being retrieved. For instance, FROM Contacts indicates that the data is sourced from the “Contacts” table or data extension.
WHERE Clause
Conditions that filter the rows of data returned by the query. It allows you to specify criteria, such as WHERE Age > 25, to retrieve only records where the age is greater than 25.
ORDER BY Clause
Determines the order in which the results are presented. For instance, ORDER BY CreatedDate DESC would arrange the results based on the “CreatedDate” column in descending order.
GROUP BY Clause
Groups rows that have the same values into summary rows, typically used with aggregate functions like COUNT, SUM, AVG. For example, GROUP BY Country groups data by the “Country” column.
HAVING Clause
Works in conjunction with GROUP BY and allows filtering on grouped results. It’s conditional, similar to the WHERE clause, but applied after the grouping has occurred. For example, when grouping by country, HAVING COUNT(CustomerID) > 5 will only include results where there are more than five results for a given country.
JOIN Clause
Specifies how tables or data extensions are related to each other. For instance, INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID would join the “Customers” and “Orders” tables based on the “CustomerID” column.
LIMIT/OFFSET Clause
Controls the number of rows returned and can be useful for paginating results. For example, LIMIT 10 OFFSET 20 would retrieve 10 rows, starting from the 21st row (offsetting the first 20).
Now you know how to write SQL. These components collectively form a query, allowing users to interact with and retrieve specific subsets of data from Salesforce Marketing Cloud databases or data extensions.
Essential SQL queries used in SFMC
Selecting Data
To retrieve specific columns from a Data Extension:
```sql
sql
SELECT FirstName, LastName, Email
FROM ContactDataExtension
```
Filtering Data
To filter data based on a specific condition:
```sql
sql
FROM PurchaseDataExtension
WHERE PurchaseAmount > 100
```
Joining Data
To combine data from two Data Extensions based on a common field:
```sql
sql
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
```
Grouping and Aggregating Data
To group data and calculate aggregates (e.g., total purchases per customer):
```sql
vbnet
SELECT CustomerID, COUNT(OrderID) AS TotalOrders, SUM(OrderAmount) AS TotalAmount
FROM OrderDataExtension
GROUP BY CustomerID
```
Ordering Data
To sort data in ascending or descending order:
```sql
sql
SELECT ProductName, Price
FROM ProductDataExtension
ORDER BY Price DESC
```
Limiting Results
To retrieve a specific number of rows:
```sql
sql
SELECT
FROM SubscriberDataExtension
LIMIT 10
```
Combining Filters
To apply multiple conditions using AND or OR:
```sql
sql
SELECT
FROM CustomerDataExtension
WHERE Age > 25 AND Country = 'USA'
```
SQL is essential for creating the right audience, but how do you personalize content to ensure you create the right message? Learn the basic overview of AMPscript.

Ensure SFMC Doesn’t Skip Leap Day Birthdays from Offers
How would you feel if your birthday was forgotten for multiple years in a row and you feel left out on your special day? That’s exactly the feeling most companies give their customers who were born on the infamous Leap Day – February 29th.
Companies often overlook Leap Day birthdays due to its rarity. February 29th happens once every four years – 2.5 times per decade. Many people never even meet a person with this birthday, so it’s easy to miss when marketers set up their birthday campaigns.
How about your birthday or anniversary campaigns?
Did you think about leap years? If not, it is time to change that.
You may think that the group of people affected is probably very small – this might be true, but spending just a little time to include Leap Day birthdays in your marketing campaigns can generate great reactions from your customers!
If you take that special date into account but only send an email the day February 28th with your usual birthday wishes plus a coupon, it won’t have a huge effect. However, if you combine that with dynamic content in your email, you can create personalized communications that drive action.
As an example,
“We’d never forget your birthday, even if there isn’t a Leap Day this year. We’re sure others do, and therefore we celebrate your special day even more – instead of the usual 10% off, you’ll get 5% on top of that. Happy Birthday!”
would put a smile on any customer’s face and strengthen the customer relationship for sure! And isn’t that what it’s all about?!
How can you select the relevant people in your birthday campaign segmentation?
There’s three rules that allow us to find out if a given year is a leap year:
- If the year is evenly divisible by 4 – check step 2; if not, it isn’t a Leap Year
- If the year is evenly divisible by 100 – check step 3; if not, it is a Leap Year
- If the year is evenly divisible by 400 – it is a Leap Year; if not, it isn’t a Leap Year
So after following this criteria, if the current year is not a leap year, then you need to check your audience for people born on February 29th already a day early.
Note: If you’re wondering why a year divisible by 100 but not 400 isn’t considered a Leap Year, the length of the solar year is slightly less than 365.25 days. By skipping the leap year every 100 years (but not every 400 years), we can keep our calendar in closer alignment with the solar year. For example, the year 2000 was a leap year (divisible by 400), but the year 1900 was not (only divisible by 100).
And this is how it’s done using SQL
In order to check if something is evenly divisible you can use the so-called “Modulus” operator or Mod-function in SQL. This will return the remainder of one number divided by the other.
Everything else that needs to be done is comparing the date parts (month, day and year) to the ones of today (using the getdate function) – once for the current date and in case we found out that this year isn’t a leap year, we also check for February 29th if it is still the 28th. That’s all that’s needed.
SELECT
SubscriberKey,
Birthdate,
Email,
FirstName,
LastName
FROM
Your_Segment
WHERE
(
(
DAY(Birthdate) = DAY(GETDATE())
AND
MONTH(Birthdate) = MONTH(GETDATE())
)
OR
(
(
CASE
WHEN DATEPART(year, GETDATE()) % 4 <> 0 THEN 0
ELSE
CASE
WHEN DATEPART(year, GETDATE()) % 100 = 0 THEN
CASE
WHEN DATEPART(year, GETDATE()) % 400 = 0 THEN 1
ELSE 0
END
ELSE 1
END
END
) = 0
AND
DAY(GETDATE()) = 28
AND
DAY(Birthdate) = 29
AND
MONTH(GETDATE()) = 2
AND
MONTH(Birthdate) = 2
)
)
Note: Depending on the relevant time zone and in which time zone the dates are, you might need to use the “AT TIME ZONE” function in combination with the getdate function call. For better readability this is omitted in the example.
If you use DESelect, follow this easy step:
When creating a new Selection in DESelect, all it takes is adding an advanced custom value to the Target Data Extension. Just copy and paste the SQL code into the field. Here’s the step-by-step.
- Add New Value
- Name your value – in this case ‘Birthday’ makes sense
- Select the option to ‘Apply formula to a field’
- Select the option to ‘Apply any function’ – this will let you add freehand SQL code
- Paste above SQL code into the open field and save
It’s that easy!
Conclusion
Even if it won’t have the biggest business impact, going the extra mile to show a subscriber you care builds trust. And all that with just a little SQL required in addition to what you normally do for your birthday segments – and as you now have the code in this article it is now a quick fix.

Markus Dang
Markus is a guest author at DESelect. He's a Marketing Cloud Unit Lead & Solution Architect. He is also a Salesforce Marketing Cloud Champion.

Why Technical Marketing Cloud Users Love DESelect, Pt. 2
Salesforce Marketing Cloud’s nearly infinite data storage makes the novice marketer’s head spin. Such highly technical, complicated processes take time to complete and leave users anxious over the effects of potential mistakes.
Marketing Cloud expert Markus Dang previously demoed some of his favorite admin features in DESelect Segment to reduce the risk and time requirements associated with routine audience segmentation.
In this article, Markus reviews more of his favorite features, designed to automate many of the complex SQL JOINs and INNER JOIN queries that can frustrate any user.
Templates
Part one of this series focused on how Marketing Cloud admins can best set safeguards for less-technical marketers. Now we move more into the DESelect core – complex audience segmentation.
Whereas data sets allow more technical marketers to prepare data for tasks like joins and relationships between data extensions, templates get used later in the segmentation process.
Templates are used when you have similar Selections that rely on complex criteria. It’s a straightforward process.
- Create a Selection
- Save it as a template
- Adjust the filters with new selections going forward
Now teams don’t have to repeat every task repeatedly when creating similar or routine segmentations.
As you can see, data sets act more like expansive data extensions, quickly giving marketers the complex data necessary for advanced segmentation. They can then convert the overall Selection created from these data extensions or data sets into a template, so later segmentations will immediately complete most steps.
As Markus mentions, DESelect Selections use the INNER JOIN of SQL, replacing the need for technical marketers to write any code for these highly complex segmentations.
Data Views
Data views are system-generated data extensions in Marketing Cloud containing behavioral information on how subscribers interact with campaigns, such as communication sends, email opens, unsubscribes or links clicked.
In DESelect, all data views start with an underscore for easy reference when segmenting. You can access all 24 data views in Marketing Cloud via DESelect.
Waterfall Selections have the following benefits:
- Create more complex segmentations when you also want the original results of each contributing Selection
- Avoid the timeouts common when handling large data quantities in Marketing Cloud
- Easily use complex filters that may otherwise timeout
With the ability to upload highly complex Selections into Journey Builder, marketers receive granular segmentations in minutes, ensuring ultra-personalization without stereotypical time requirements.
Conclusion
Rather than have developers create endless SQL JOINs, DESelect Segment ensures your team launches campaigns with the desired level of personalized, on time.
Markus isn’t our only fan. See why the CMO of Simplify Compliance loves DESelect’s intuitive segmentation features.
(Spoiler: With DESelect, campaign output grew significantly, campaign production has become much quicker, and marketers now segment without reliance on the IT department.)

Audience personalization in Salesforce Marketing Cloud heavily relies on the UNION SQL (Structured Query Language) clause for data manipulation, analysis, and segmentation. This complex code has been a language many creative marketers struggle to master, making it the “French” of marketing ops.
What better way to understand how DESelect solves this problem other than demo videos? Keep reading to get to the video content below.
The Traditional Importance of UNION and SQL Overall
SQL is the language of Marketing Cloud. Without a strong understanding, users and teams typically face several challenges, including:
- Data Retrieval: Difficulty accessing and using data needed to make informed decisions
- Data Analysis: Absent UNION or SQL knowledge altogether, users struggle to segment, aggregate, and filter data to gain necessary insights into customer behavior
- Data Segmentation: Marketers use SQL to segment their audience based on various criteria, such as behavior, demographics, and purchase history
- Automated Campaigns: Marketers rely on SQL to define the conditions that trigger automated, personalized campaigns
- Data Management: SQL allows users to import, export, and manipulate large amounts of data
Often, teams rely on technical marketers and IT professionals to fill these roles. However, too great a reliance limits the effectiveness of Salesforce Marketing Cloud’s capabilities and distracts these employees from primary functions, leading to reduced output and efficiency across the entire team.
Worst case scenario: marketers do not know enough about SQL to create correct UNION sequences. Still, they have to try anyway, leading to hours of lost productivity waiting on IT ticket resolution to iron out mistakes.
How Marketing Cloud Architects Make Life Easier for Marketers and Limit Mistakes
Recreational bowlers are all too familiar with lane bumpers, designed to prevent novices from spending every turn helplessly watching gutterball after gutterball. CRM architects can set similar safeguards in Marketing Cloud to ensure all marketers effectively segment without UNION or SQL errors.
Some effective tactics:
- Training: Frequent training sessions help users become more familiar with SQL and practical use cases. Supplemental training materials, such as tutorials and guides, help users learn at their own pace.
- Templates: Create pre-built templates as a starting point for campaigns, segments, and other tasks. These templates should be designed to minimize the need for manual data manipulation and SQL coding.
- Process Documentation: Develop and share clear process documentation that explains the steps to execute everyday tasks within Salesforce Marketing Cloud.
- Validation Rules: Implement validation rules to help prevent data errors and ensure data entry in a consistent, correct format. This reduces the risk of data quality issues and improves the accuracy of data-driven insights.
- Quality Assurance: Implement a review-and-approve workflow or automated quality assurance process to ensure that all campaigns, segments, and data manipulations are reviewed and approved by an experienced user before being executed.
By following these strategies, architects help ensure Marketing Cloud users of all skill sets can execute their tasks effectively and confidently, reducing the risk of errors and mistakes.
DESelect: The Bowling Bumpers of Marketing Cloud
With DESelect Segment, Marketing Cloud users of all backgrounds no longer worry about UNION or SQL when creating audiences. The tool provides intuitive drag-and-drop features, replacing the heavy code otherwise required.
With this ease comes peace of mind for Marketing Cloud admins, as DESelect provides many of the guideposts mentioned above. Below are two of the most popular features that ensure creative marketers quickly and accurately create their audience segmentations for campaigns.
Data Sets
With data sets, an experienced technical marketer or admin intimately familiar with the data model can prepare a data set with multiple data extensions and set the relationships between each. This way, marketers can segment based on multiple data extensions without necessarily knowing how to connect them – and still get the data needed.
To connect data extensions, you can create a custom relation in the DESelect admin panel or import one from an external source. Marketing Cloud and DESelect expert Markus Dang demonstrates a use case connecting sample Orders with Products Ordered.
Once the relations have been defined across data extensions, you are ready to define the data set.
To summarize, data sets give marketers pre-filled segmentation tailored to the common campaign requirements of your organization. With these templates in hand, marketers eliminate much of the time requirements and prevent UNION SQL errors.
Picklists
Another popular DESelect admin feature, picklists, simplify data entry for marketers and remove the room for typos when creating segmentation filters. Picklists also reduce the need to remember complex or technical values, further protecting against mistakes.
Marketers can then use picklists in all subsequent segmentations involving the selected data extension. Picklists can be enabled or disabled depending on current conditions.
Once defined, admins can relabel values to make for easier use. Once set up, marketers will see a dropdown list to choose from, enabling faster, more accurate segmentations.

In other words, marketers don’t need to know the values beforehand because DESelect retrieves them.
Conclusion
With Marketing Cloud already such a complex platform, providing guard rails helps ensure creative marketers quickly get their campaigns in front of the right audience without bogging down technical teams with requests and distractions.
We will cover more DESelect admin features in Part Two. In the meantime, if you want to learn more, see how Pon Automotive uses DESelect data sets (and other built-in templates) to speed up campaign operations and onboard new employees quicker.

How to do aggregations in Salesforce Marketing Cloud? - DESelect Blog
Would you like to be able to find the number of orders each customer has made in a certain period of time? Or calculate the average order value for each customer? Or maybe you want to count the number of emails sent to each subscriber in the last 30 days? All that and more is possible with the aggregation functionality in Salesforce Marketing Cloud.
In this article, we will explain how to use aggregations in Salesforce Marketing Cloud (SFMC) using SQL and DESelect.
How to use aggregations in SFMC using SQL
SQL is great at aggregating data the way you might in a pivot table in Excel. The functions are similar to what you’ll find in Excel or certain analytical tools. For instance, The GROUP BY statement groups rows that have the same values as summary rows, like “find the number of customers in each country”.
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()), to group the result-set by one or more columns. Here you can find a brief explanation of what each function means:
- AVG – calculates the average of a set of values
- COUNT – counts rows in a specified table or view
- MIN – gets the minimum value in a set of values
- MAX – gets the maximum value in a set of values
- SUM – calculates the sum of values.
In this article, we’re going to dig deeper into the COUNT aggregation that allows you to do a count on a data extension or data view and store the results in your target data extension.
In the scenario we’re going to demonstrate, we’d like to know the number of times each subscriber has opened an email in the last 30 days. On top of that, we’d also like to limit the result to the first 20% of subscribers.
Here’s how the SQL query for the following scenario will look.

(SQL query using Aggregations)
In our example, we’re using the Customers data extension along with the data view _Open. Normally, an aggregation is only a part of the segmentation a marketer has to perform. However, for non-technical marketers it can sound like a challenge. Fortunately, there are alternatives to writing SQL in SFMC if you want to use aggregations… like DESelect!
How to use aggregations in SFMC using DESelect
In the video below you can find a step-by-step guide to using aggregations in DESelect.
For convenience, DESelect offers different screens depending on the level of the marketer’s maturity. For instance, if you don’t know how your data model is set up exactly, you can use the ‘Basic’ screen, where the relations between your data extensions are already pre-defined. In the video, for example we’re using this screen to create an aggregation. However, if you are more confident with your data model, you can use the advanced screen. You can read more about how to create a segment using the advanced aggregation screen here.
If you’d like to book a live demo to see how DESelect avoids the need for code in your advanced segmentation, one of our experts would love to help you.
Conclusion
In this article, we explained how to use aggregations in Salesforce Marketing Cloud using SQL and the no-code alternative, DESelect. If you want to see DESelect live and leverage aggregations with an easy drag-and-drop, book a demo with one of our experts.
- How to use aggregations in SFMC using SQL
- How to use aggregations in SFMC using DESelect
- Conclusion
Latest Articles
-
Your Essential Guide to Dreamforce 2025 Highlights and Insights Attend Dreamforce 2025 Dreamforce 2025 is shaping up to be one of the most impactful events in Salesforce’s history. Scheduled to take place at the Moscone Center in downtown San Francisco, this three-day event attracts tens of thousands of attendees from the Salesforce ecosystem, developers and […]September 4, 2025A Practical Salesforce Implementation Guide Implementing Salesforce isn’t just about installing software. It’s about getting your systems, teams, and data aligned to support better customer experiences and business results. When done right, a Salesforce implementation can boost efficiency, support growth, and provide a clearer view of your customers. Our Salesforce implementation guide walks you through […]September 3, 2025Making Sense of Salesforce Contact Builder Salesforce Contact Builder is the engine that helps bring customer data together inside Marketing Cloud. If you want to send the right message to the right person at the right time, this is where it all starts. Contact Builder allows marketers to collect, organise, and connect contact data from […]July 30, 2025How Consumer Brands Can Avoid Email Overload and Choice Paralysis Consumer brands operating multiple labels under one corporate umbrella often face a unique set of challenges when it comes to email marketing, particularly when a large multibrand company attempts to manage email campaigns across its various brands simultaneously. One of the biggest pitfalls is overwhelming potential buyers […]July 29, 2025
Join our newsletter to receive updates and helpful SFMC guides.

How to combine data from 2 data extensions with or without using SQL
How would you find subscribers who’ve made no purchases or customers who’ve made orders, if the information is located in separate data extensions in Salesforce Marketing Cloud? For this you need to have a basic understanding of how your data works
In this article, we explain how to combine data from two data extensions, along with the concept of JOINs in Salesforce Marketing Cloud (SFMC). We’ll do this by reviewing how you can combine data using SQL, as well as DESelect.
Data extension 101: How to combine data extensions in Salesforce Marketing Cloud using SQL
There are four main ways you can create a relationship between data extensions in SFMC using SQL. These are called JOINs.

(Data relationship using JOINs)
Before creating a JOIN between two data extensions, you need to understand how you want the data to be connected. You can choose to connect two data extensions (DEs) that have the same field. In our example, in the Customers DE it could be a Customer Id, which is a unique number that helps to identify them.

(Customers data extension)
You can also have a DE with Orders, where you can use a Customer Id field. This way you know what customer made what order. However, to send a communication to this customer, we need to combine the information about the order with his personal information, which is stored in another data extension.

(Orders data extension)
Now, let’s go back to the four JOINs. Using the INNER JOIN we can find customers who have made an order. The SQL query for this scenario will look like this.

(SQL query in SFMC)
Since there is a common Customer Id field, it allows us to select only those customers who have made the orders.
For the purposes of this article, we are going to continue using Customers DE and Orders DE as examples. Let’s tackle FULL JOIN now. This obtains all matching records from both data extensions whether the other data extension matches or not. So, if there are fields in Customers DE that do not match in Orders DE, or if there are fields in Orders DE that do not have matches in Customers DE, those fields will be listed regardless.
The LEFT JOIN will bring you all the records from the Customers DE and then the corresponding data from the Orders DE. The RIGHT JOIN will do the same but now with the Orders DE since we chose it second. Now onto the remaining scenarios…
Using our example data extensions, for the LEFT JOIN, you would like to find Customers who have made the Order and those who did not, and for instance, target them with the campaign to encourage their first purchase.
For the example of the RIGHT JOIN let’s take an example of Customers and Subscribers data extensions. Applying the RIGHT JOIN would enable you to identify the subscribers who aren’t yet customers, and target them accordingly.
Combine any data extension in Salesforce Marketing Cloud using DESelect
Understanding data relationships is not the easiest part of segmentation in Marketing Cloud, but then writing code for it can be even less appealing. This is where DESelect comes in.
This solution allows you to combine data extensions by dragging and dropping them on top of each other. Our tool then lets you combine the DEs using a visual representation of the JOINS.

(Create data relationships in DESelect)
In this video we show you how to create relationships between data extensions in Salesforce Marketing Cloud using both SQL and DESelect.
Conclusion
We hope that this article explained the basic combinations of any data extension in the Salesforce Marketing Cloud. In most cases, more than two data extensions are involved in this process. Imagine writing an SQL query for a relationship between 12 DEs? Yes, that’s right, sounds like a lot of work.
If you’d like to book a live demo to see how DESelect avoids the need for code in your advanced segmentation, one of our experts would love to help you.
- How to combine data from 2 data extensions with or without using SQL
- Data extension 101: How to combine data extensions in Salesforce Marketing Cloud using SQL
- Combine any data extension in Salesforce Marketing Cloud using DESelect
- Conclusion
Latest Articles
-
Your Essential Guide to Dreamforce 2025 Highlights and Insights
Your Essential Guide to Dreamforce 2025 Highlights and Insights Attend Dreamforce 2025 Dreamforce 2025 is shaping up to be one of the most impactful events in Salesforce’s history. Scheduled to take place at the Moscone Center in downtown San Francisco, this three-day event attracts tens of thousands of attendees from the Salesforce ecosystem, developers and […]September 4, 2025A Practical Salesforce Implementation Guide
A Practical Salesforce Implementation Guide Implementing Salesforce isn’t just about installing software. It’s about getting your systems, teams, and data aligned to support better customer experiences and business results. When done right, a Salesforce implementation can boost efficiency, support growth, and provide a clearer view of your customers. Our Salesforce implementation guide walks you through […]September 3, 2025SalesForce Contact Builder
Making Sense of Salesforce Contact Builder Salesforce Contact Builder is the engine that helps bring customer data together inside Marketing Cloud. If you want to send the right message to the right person at the right time, this is where it all starts. Contact Builder allows marketers to collect, organise, and connect contact data from […]July 30, 2025How Consumer Brands Can Avoid Email Overload and Choice Paralysis
How Consumer Brands Can Avoid Email Overload and Choice Paralysis Consumer brands operating multiple labels under one corporate umbrella often face a unique set of challenges when it comes to email marketing, particularly when a large multibrand company attempts to manage email campaigns across its various brands simultaneously. One of the biggest pitfalls is overwhelming potential buyers […]July 29, 2025Join our newsletter to receive updates and helpful SFMC guides.

How to use date filters in Marketing Cloud - DESelect Blog
Imagine you’re creating a campaign to target a set of people that performed a certain action X time ago. In our example, we’d like to find people who attended an event 6 months ago and inform them about a new event coming up. But how can you do this in Salesforce Marketing Cloud? Out-of-the-box filters are no use when you want to find people from a certain period of time, so what’s left?
One option you can make use of is to write SQL queries. As you might already know, if you want to perform advanced segmentation in SFMC, you need to understand how SQL works. Some time ago we wrote a short guide on how to make use of SQL queries in Salesforce Marketing Cloud, which you can read here.
So, how will your query look if you want to find people who attended your event six months ago? Note that the examples we’re going to use most likely will not represent your own data model. We’re using them to show how the query can be technically done.
In our example, we have a data extension for Events with dates, and another extension for Event attendees.
Example data extensions:

(Events data extension)

(Event Attendees data extension)
Filter on a date with SQL
Then you need to proceed to Automation Studio, located under Journey Builder and choose the tab Activities, where you can create a new SQL activity. Using the data extensions we have, your query will look like this:

(SQL query example)
You have to combine two data extensions using INNER JOIN. That way we know that we are combining only the attendees who have actually been to the event. We combine them using an event attendees Id. Then we have to actually find the people who have attended the six-month-old event. For that, we query the relative date on the last line.
This is a rather simple query, however, even just querying the date can get messy if you’re not well acquainted with the information.
Alternatively, you can fall back to using Excel for this. First, you need to extract the data extensions into CSV files to modify them; filter the rows, and then create a lookup to join data together across tables. You use the lookup function to look up a value in a one-column or one-row range, and retrieve a value from the same position in another one-column or one-row range. You can learn more about this functionality and the example here. Of course, working with large data sets in this way is far from ideal due to the opportunities for human error. Furthermore, Excel needs time to process large data sets.
Filter on a date with DESelect
If you’re wondering whether there’s an alternative to writing code to segment Salesforce Marketing Cloud audiences or to Excel spreadsheets, the answer is yes. You can leverage the drag-and-drop SFMC segmentation solution, DESelect.
This walkthrough video will show you how to tackle the same activity in DESelect. Read on for a step-by-step guide.
First, you need to open DESelect, under the AppExchange icon on the tab bar. Then on the right-hand side, you click on the button and create a new selection. We refer to segments as selections in DESelect.
On the next screen, you choose the source data extensions, which in our scenario are Events and Event Attendees. On the left side, you can find all the available data extensions and Salesforce Marketing Cloud data views. The only thing you need to do is to drag-and-drop them to the right. Then drop one extension on top of the other.
You’ll see the following on-screen.

(Create a relationship in DESelect)
Here you define the relationship between two data extensions. As mentioned, you need Event Attendees Id from the Events data extensions and the Id from the Event Attendees data extensions. Note that you only need Events with matching Event attendees. You will note that this looks similar to the SQL JOIN function. This, however, is easier to understand, and no requires no code.
Then you need to move on to the actual date filters. Choose the fields from your data extensions that you want to filter. In the example below, you choose the Event date field from the Events data extension. Select is before and choose the relative date 6 months before today.

(Date filter in DESelect)
Apart from choosing the before today option, you can also choose the before now option. What’s the difference between these two?
Today is used to refer to a date value that contains a year, a month and a day without any time information.
Now is used to refer to a date value that in addition to the year, month, and day, information also contains hours, minutes, seconds, and milliseconds.
In this example, the exact time information isn’t relevant, so you can choose before today.
You can move on to the next step of target definition. At this step you choose where your segment will be saved. You can populate an existing data extension with it or create a data extension on the fly.

(Target definition in DESelect)
You can create a data extension right here in DESelect, and then simply choose what fields you would like to see in it. In this example, we chose Id of the Event Attendees, their Contact Id, the Name of the events they attended, and the Event Date.
The last step is to run a preview. The preview in DESelect doesn’t yet populate your data extension with the filtered data; it shows the first 20 results of the future selection. This gives you an opportunity to tweak and modify the selection if needed. Once you’re satisfied with the result, you can press run, which runs the selection in Salesforce Marketing Cloud, populating your data extension.
Conclusion
We hope this article has helped with your understanding of how to filter a date in Salesforce Marketing Cloud without the need for code. If you want to try DESelect to solve your date filtering needs and much more, book a demo with one of our professionals here.
- Filter on a date with SQL
- Filter on a date with DESelect
- Conclusion
Latest Articles
-
Your Essential Guide to Dreamforce 2025 Highlights and Insights
Your Essential Guide to Dreamforce 2025 Highlights and Insights Attend Dreamforce 2025 Dreamforce 2025 is shaping up to be one of the most impactful events in Salesforce’s history. Scheduled to take place at the Moscone Center in downtown San Francisco, this three-day event attracts tens of thousands of attendees from the Salesforce ecosystem, developers and […]September 4, 2025A Practical Salesforce Implementation Guide
A Practical Salesforce Implementation Guide Implementing Salesforce isn’t just about installing software. It’s about getting your systems, teams, and data aligned to support better customer experiences and business results. When done right, a Salesforce implementation can boost efficiency, support growth, and provide a clearer view of your customers. Our Salesforce implementation guide walks you through […]September 3, 2025SalesForce Contact Builder
Making Sense of Salesforce Contact Builder Salesforce Contact Builder is the engine that helps bring customer data together inside Marketing Cloud. If you want to send the right message to the right person at the right time, this is where it all starts. Contact Builder allows marketers to collect, organise, and connect contact data from […]July 30, 2025How Consumer Brands Can Avoid Email Overload and Choice Paralysis
How Consumer Brands Can Avoid Email Overload and Choice Paralysis Consumer brands operating multiple labels under one corporate umbrella often face a unique set of challenges when it comes to email marketing, particularly when a large multibrand company attempts to manage email campaigns across its various brands simultaneously. One of the biggest pitfalls is overwhelming potential buyers […]July 29, 2025Join our newsletter to receive updates and helpful SFMC guides.

This is the second part of a 3-part series on segmentation in Salesforce Marketing Cloud (SFMC):
- Read the first part here: How to filter a data extension in Salesforce Marketing Cloud using filters (SFMC segmentation – part 1/3)
- Read the third part here: How to segment Data Extensions in Salesforce Marketing Cloud using DESelect (SFMC segmentation – part 3/3)
If you want to create a campaign in Salesforce Marketing Cloud (SFMC) that sets you apart, there is a big chance you’ll need to face a complex segmentation process. One of the solutions that SFMC proposes is SQL, Structured Query Language, which is quite an advanced method of segmenting. The creation of queries in SFMC offers a possibility to filter the information in DEs and target in a more sophisticated way.
In short, a query is an activity of getting relevant information from one or multiple data extensions and saving it to a target data extension. The process of SQL query creation is a task that requires some level of experience and time. Moreover, the process of a query activity execution in the SFMC environment can take up to 30 minutes – though this would be the case only for very complex queries.
A 3-step guide to segmenting data in SFMC using SQL queries
Step 1. Create a Data Extension
You need to create a target data extension, which you would like to use for your future campaign. For instance, the data extension below contains four fields that need to be populated with specific information from other data extensions:

Step 2. Create a SQL Query
In order to create a SQL Query, you should follow the steps below:
- Go to ‘Journey Builder’
- Click on ‘Automation Studio’
- Go to ‘Activities’ and click on ‘Create Activity’
- Choose ‘SQL Query’ from the options

Now you can start writing a SQL query:
- use ‘select’ from where you choose the field names you would like to define
Note: They have to be separated by a comma except for the last field.
- choose ‘from’, where you define from which DE you are extracting the data
- validate syntax to check for the mistakes in the query
Note: Only selected fields that exactly match the name of the columns in your target DE will be populated.

How can you create a (slightly) more advanced SQL queries in SFMC?
SQL queries can be very advanced to fulfill all the requirements of a specific campaign. There are many possibilities to adapt the query to your needs.
For instance:
- You can rename fields using ‘as’. (e.g. your initial DE contains a field ‘email’ but your target DE ‘email address’)

- Use ‘where’ to indicate you will specify criteria. (e.g. you have a field with a type of a company and you would like to filter only on the ‘prospect’ type)

- You can combine criteria using ‘and’, ‘or’ and ‘not’ (e.g. you would like to filter not only on the ‘prospect’ companies but also on the ones who are classified as ‘clients’ in the ‘type’ field).

Do you want to know how to filter on data in DE’s in SFMC without SQL? Read our previous article on how to filter on data using data filters in SFMC!
For creating even more advanced segmentation, you can also use joins. The ‘join’ clause is used for combining data from multiple DEs. Here are four different kinds of joins:

- (Inner) join: Returns records that have matching values in both tables
- Left (outer) join: Return all records from the left table, and the matched records from the right table
- Right (outer) join: Return all records from the right table, and the matched records from the left table
- Full (outer) join: Return all records when there is a match in either left or right table
Note: The ‘on’ clause is used to describe how the DEs are related to each other, usually based on an ID or sometimes an Email Address.

Don’t want to bother with joins and writing SQL queries manually? You can also create advanced segments with simple drag-and-drop, using DESelect.
Step 3. Target Data Extension
After your query is finished and it is validated you can move on to the next step. Here, you need to find your target data extension, which you want to populate with the query. Then decide how you want to add the data:
- Appending data means adding new data to target data extension
- Updating the data means matching data when it is found or adding new data if there is none
- Overwriting means replacing or overwriting the current data in the target data extension
Should you use SQL queries in Salesforce Marketing Cloud?
If you want to do more advanced segmentation on data extensions in SFMC then SQL queries can be an option for you. They allow a lot of freedom and can cover very specific needs. However, it also means that you need to have sufficient technical skills to write those queries. Otherwise, you would need to ask for the help of more technical colleagues (e.g. those in the IT department), which slows down the process of campaign creation or consider a drag-and-drop alternative like DESelect.
- A 3-step guide to segmenting data in SFMC using SQL queries
- How can you create a (slightly) more advanced SQL queries in SFMC?
- Should you use SQL queries in Salesforce Marketing Cloud?
Latest Articles
-
Your Essential Guide to Dreamforce 2025 Highlights and Insights
Your Essential Guide to Dreamforce 2025 Highlights and Insights Attend Dreamforce 2025 Dreamforce 2025 is shaping up to be one of the most impactful events in Salesforce’s history. Scheduled to take place at the Moscone Center in downtown San Francisco, this three-day event attracts tens of thousands of attendees from the Salesforce ecosystem, developers and […]September 4, 2025A Practical Salesforce Implementation Guide
A Practical Salesforce Implementation Guide Implementing Salesforce isn’t just about installing software. It’s about getting your systems, teams, and data aligned to support better customer experiences and business results. When done right, a Salesforce implementation can boost efficiency, support growth, and provide a clearer view of your customers. Our Salesforce implementation guide walks you through […]September 3, 2025SalesForce Contact Builder
Making Sense of Salesforce Contact Builder Salesforce Contact Builder is the engine that helps bring customer data together inside Marketing Cloud. If you want to send the right message to the right person at the right time, this is where it all starts. Contact Builder allows marketers to collect, organise, and connect contact data from […]July 30, 2025How Consumer Brands Can Avoid Email Overload and Choice Paralysis
How Consumer Brands Can Avoid Email Overload and Choice Paralysis Consumer brands operating multiple labels under one corporate umbrella often face a unique set of challenges when it comes to email marketing, particularly when a large multibrand company attempts to manage email campaigns across its various brands simultaneously. One of the biggest pitfalls is overwhelming potential buyers […]July 29, 2025Join our newsletter to receive updates and helpful SFMC guides.