SQL Query Examples

Learn how to build effective SQL queries in Automation Studio by following use cases.

Note

• To use these examples, you need a working knowledge of SQL.
• These examples aren’t comprehensive. Be creative, combine use cases, and check out the Trailhead and Stack Exchange communities for more ideas.
• You can copy and paste the code samples, but you must configure them for your setup.
• Data views are covered in a separate, adjacent section of the documentation.

• Query: Bounce History
  Find a history of individual bounce responses, and identify how to resolve them.
• Query: Journey Builder Bounced Email Messages
  Create a list of contacts to send direct mailers to, based on bounced email messages from Journey Builder.
• Query: Journey Builder Sends by Email Across Versions
  Aggregate all Journey Builder email sends across multiple versions of the same journey. Or modify this query to find opens, clicks, bounces, complaints, or unsubscribes across versions during a specific time frame.
• Query: Journey Builder Sends in Last 24 Hours
  Find subscribers who were sent an email from a journey in Journey Builder within the last 24 hours. Or, modify this query to find opens, clicks, bounces, complaints, or unsubscribes for an email from Journey Builder during a specific time frame.
• Query: Opens in Last 30 Days
  Find all unique opens for a specific email over the last 30 days. Or, modify this query to find sends, clicks, bounces, complaints, or unsubscribes for an email during a specific time frame.
• Query: Subscriber Status
  Find subscribers on a list and their statuses. Or, find only subscribers with a specific status, such as active or unsubscribed.
• Query: Subscribers by Date or Time Frame
  Find subscribers who were added within a specific date or range of dates.
• Query: Subscribers in a Publication or Suppression List
  Find subscribers who were placed on a publication or suppression List. Or, find only subscribers on the list with a specific status, such as active or unsubscribed. If you have problems retrieving suppression list data, contact Salesforce Customer Support to activate this feature.
• Query: Subscribers with No Opens or Clicks
  Find subscribers who haven’t opened or clicked an email. Use this query for jobs with fewer than 500,000 subscribers. For larger jobs, use intermediate tables.
• Query: Top Bounces for a Job
  Find the top bounces by percentage for a specific job. Use this query for jobs with fewer than 5,000,000 subscribers.