SQL is a very critical skill for anybody who wants to make a successful career working in Marketing Cloud. In SFMC, data extensions and Contact Builder use a relational database. There comes SQL which provides a good way to execute queries and retrieve data for reporting or segmenting audiences. As a Marketing Cloud consultant, developer or data analyst, good knowledge of SQL will help you in combining two or more data extensions, finding hidden values or meaningful data that exist in several data extensions, building data extensions for targeted email campaigns, creating SQL queries for journeys and automation, automating data imports and a lot more. Even as a manager, Knowledge of SQL will help you in preparing quick data and reports on the health and performance of your campaigns to be presented to senior management.
When I first got the opportunity to work on Marketing Cloud, I did not know SQL. At that time I started learning it by first doing a course on Udemy. The Complete SQL Bootcamp 2022: Go from Zero to Hero. This course was easy to digest for a first-timer and gave me a good understanding and knowledge of SQL. After that, I did additional learning by watching relevant content on Youtube and reading blog posts of Marketing Cloud experts. It is not difficult to learn SQL however if you don’t use it regularly you won’t be able to gain proficiency in it.
Resources to Learn SQL for Marketing Cloud:
- Trailhead: https://trailhead.salesforce.com/content/learn/modules/marketing-cloud-data-management/query-data-with-sql
- Salesforce.org: Marketing Cloud SQL Basics Youtube Video
- SFMC SQL Basics: https://mateuszdabrowski.pl/docs/sql/sfmc-sql-basics/
- Adam Spriggs’ Blog: https://sprignaturemoves.com/category/salesforce-marketing-cloud/sql-queries/
- SQL Query examples: https://help.salesforce.com/s/articleView?id=sf.mc_as_sql_query_examples.htm&type=5
- SQL Reference: https://help.salesforce.com/s/articleView?id=sf.mc_as_sql_reference.htm&type=5
- Optimizing a SQL Query Activity: https://help.salesforce.com/s/articleView?id=sf.mc_as_optimize_the_query_activity.htm&type=5
Examples of some Basic SQL Statements
SELECT: This statement is used to select specific data from a table in the database. For example, you might use a SELECT statement to select the name and email address of all subscribers who have signed up for a particular email list.
SELECT name, email
FROM subscribers
WHERE email_list = 'newsletter'
WHERE: This clause is used to filter the results of a SELECT statement by specifying a condition that must be met. For example, you might use a WHERE clause to only select subscribers who have opened a specific email in the past month.
SELECT name, email
FROM subscribers
WHERE email_list = 'newsletter'
AND last_opened_email > DATEADD(month, -1, GETDATE())
GROUP BY: This clause is used to group the results of a SELECT statement by one or more columns. For example, you might use a GROUP BY clause to group subscribers by the state they live in.
SELECT state, COUNT(*) as subscriber_count
FROM subscribers
GROUP BY state
HAVING: This clause is used to filter the results of a SELECT statement after they have been grouped using the GROUP BY clause. For example, you might use a HAVING clause to only select groups of subscribers who have received more than 10 emails in the past month.
SELECT state, COUNT(*) as subscriber_count
FROM subscribers
GROUP BY state
HAVING COUNT(*) > 10
ORDER BY: This clause is used to sort the results of a SELECT statement in ascending or descending order by a specific column. For example, you might use an ORDER BY clause to sort subscribers by the date they joined your email list.
SELECT name, email, join_date
FROM subscribers
WHERE email_list = 'newsletter'
ORDER BY join_date ASC
Some more examples of SQL queries
Select all subscribers who have a “VIP” status and live in the state of California:
SELECT name, email, state
FROM subscribers
WHERE status = 'VIP' AND state = 'CA'
In this example, the data extension is called “subscribers” and it has columns for the name, email, and state of each subscriber.
Select all subscribers who have opened a specific email in the past month and group them by the city they live in:
SELECT city, COUNT(*) as opened_email_count
FROM subscribers
WHERE last_opened_email > DATEADD(month, -1, GETDATE())
GROUP BY cityIn this example, the data extension is called “subscribers” and it has a column for the city of each subscriber, as well as a column that tracks the date they last opened an email.
Select all subscribers who have made a purchase in the past year and order them by the amount they spent, from highest to lowest:
SELECT name, email, total_spent
FROM subscribers
WHERE last_purchase_date > DATEADD(year, -1, GETDATE())
ORDER BY total_spent DESC
In this example, the data extension is called “subscribers” and it has columns for the name, email, and total amount spent by each subscriber. It also has a column that tracks the date of their last purchase.