SQL

1- Two joins queries

2- Customers who spent above the average

3- Customers who spent below the average

4- Upsert in SQL

5- Query a Query

6- Do you SQL? Salesforce Marketing Cloud

7- Select and From Clause

8- Where Clause

9- Count function

10- Numeric function

Using a SQL Query to Retrieve and Segment Data

SQL query operations retrieve and unify data across data extensions and system data views. You can, for example, create a subscriber list, view open and send history, or explore automation and journey health.

A query activity works great for tidy structured datasets with pre-existing audiences. Salesforce recommend utilizing your own internal data warehouse or an application like Salesforce Customer Data Platform to unify and segment a massive dataset.

Preparing Your SQL Query Activity

Consider the information you want to collect and how you want to use it. For recommendations on how to get started, look at Salesforce help query examples below:

Creating a SQL Query Activity

Create a SQL query activity in Automation Studio that retrieves the data you’re looking for and add it to an automation.

Then, optimize your query to avoid timeouts and failures.

  • Build a SQL Query Activity
  • Optimizing a SQL Query Activity
  • SQL Reference
  • Trailhead: Query Data with SQL

Maintaining Query Health

Create SQL query activities and automations to evaluate the status of all your automations, including SQL query activities.


You can also configure email or Slack notifications for automation faults, allowing you to respond swiftly to failures.

  • Data View: Automation Instance
  • Data View: Automation Activity Instance
  • Get Automation Studio Notifications

Addressing Failed Queries

Determine the cause of your query’s failure and then troubleshoot it. Optimize the query if it timed out.

  • Automation Studio Errors
  • Optimizing a SQL Query Activity
  • Restart a Suspended SQL Query Activity

Resources

Data extracts and API requests are two alternative methods for retrieving data from Marketing Cloud. Salesforce suggests Salesforce Customer Data Platform if you need to unify and segment your large data collection.

  • Build a SQL Query Activity
    In Automation Studio, build a SQL query activity that retrieves the data that you’re interested in, and then add it to an automation.
  • Optimizing a SQL Query Activity
    SQL query activities time out after 30 minutes. You can prevent timeouts and failures by optimizing your SQL query activities. If your query consistently runs longer than 10 minutes, we recommend using a different tool, such as Salesforce Customer Data Platform, to transform your data.
  • Use Intermediate Tables to Optimize a Query
    Split large queries into smaller components to use with Automation Studio’s query activity.
  • Data Views
    Query data views using Automation Studio for up to six months of Marketing Cloud subscriber and journey information.
  • SQL Query Examples
    Learn how to build effective SQL queries in Automation Studio by following use cases.
  • SQL Reference
    Get SQL query information for Automation Studio’s SQL Query activity at a glance.
  • Restart a Suspended SQL Query Activity
    When a SQL Query activity encounters a system error or fails 24 consecutive times, we suspend it. To find out why your query is suspended, check the action log.