Learn SQL Concepts

Learning Objectives

After completing this unit, you’ll be able to:

Learn SQL terms and concepts.
Understand the SQL structure of Data Cloud insights.

Insights in Data Cloud

Your data is brought into Salesforce Data Cloud from multiple sources. Insights give you a way to create new metrics, and organize, group, and manipulate data in your account. These insights are based on Structured Query Language (SQL). There are two types of SQL-based insights that can be built: calculated and streaming. Calculated insights allow for complex calculations based on stored data, and streaming insights are created based on real-time data. While you don’t have to be an expert in SQL to build insights, it is helpful to know how SQL is used to create them. This module covers basic SQL concepts and provides examples to help you create insights in your account.

Terms for Building Insights

First, let’s cover some common terms related to insights.

Data Streams

Data Streams are data sources brought into Data Cloud. For example, a Marketing Cloud Engagement customer data extension.

Data Model Objects (DMOs)

Data streams are mapped to a data model according to DMOs. Common DMOs include sales order, party, engagement data, and so on.

Attributes

Attributes are fields of information found in a data stream. For example, an attribute can be a person’s first name or customer ID.

Measures

Measures contain aggregated values of attributes, like the total amount spent or average order amount.

Dimensions

Dimensions contain qualitative values that can be used to categorize a measure. For example, if you want to see every customer’s total amount spent, the customer ID could be a dimension associated with the measure of the total amount spent.

Foreign Key

A foreign key is a column in a relational database that provides a link between data sources—for example, a customer ID number.

Fully Qualified Key

A fully qualified key (FQK) is a composite key that contains a source key such as a contact ID from CRM or Subscriber Id from Marketing Cloud Engagement, and a key qualifier. Use fully qualified keys to avoid key conflicts when data from different sources is harmonized in the Data Cloud data model.

Primary Key

A user-selected unique identifier of a record—for example, a customer email address or a product SKU.

SQL Structure

Now that you’re familiar with the terms, let’s look at the SQL keywords and structure used to build Data Cloud insights. SQL is written as a statement or expression that is made up of keywords.

These keywords include:

SELECT: Choose the Field API name of the attribute and what calculation you want to perform.

FROM: Identify the Object API Name of the data source you want to search.

JOIN (optional): Use this keyword to also search data from another data source based on specified criteria.

WHERE (optional): Use this keyword to include a conditional statement about the data.

GROUP BY: Identify how you want to organize or categorize the measure you selected.

Here is how the SQL statement is written.

SELECT <Attributes>, <Aggregation[_Measures_]>
FROM <Data Model Object>
JOIN [Inner | Left | Right | Full] <Data Model Object> [Optional]
WHERE <predicate on rows> [Optional]
GROUP BY <columns[_Dimensions_]>

Example

Here is an example that is looking for the total amount spent for all customers in the last 365 days.

SELECT
SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) AS customer__spend__c,
ssot__Individual__dlm.ssot__Id__c AS customer__id__c
ssot__Individual__dlm.KQ__Id__c AS kq__customer_id__c
FROM ssot__SalesOrder__dlm
JOIN ssot__Individual__dlm
ON (ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = ssot__Individual__dlm.ssot__Id__c)
AND
IFNULL(ssot__SalesOrder__dlm.KQ__SoldToCustomerID__C, '') = IFNULL (ssot__Individual__dlm.KQ__Id__c , '')
WHERE
ssot__SalesOrder__dlm.ssot__CreatedDate__c >= date_sub(current_date(), 365.0))
GROUP BY
customer_id__c, kq_customer_id__c

Let’s break each of these sections down further.

Statement What It Does

SELECT

SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) AS customer_spend__c,

ssot__Individual__dlm.ssot__Id__c AS customer_id__c

ssot_Individual__dlm.KQ_Id__c AS kq_customer_id__c

Defines the information you are looking for (customer spend) and how you want to aggregate that measure (sum).

When a metric (customer spend) is calculated, it is stored as a measure customer_spend__c in the system. Similarly, customer_id__c is stored as a dimension.

FROM

FROM ssot__SalesOrder__dlm

Identifies the source of the data (a sales order DMO).

JOIN

ssot__Individual__dlm

ON

(ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = ssot__Individual__dlm.ssot__Id__c)

AND

IFNULL(ssot__SalesOrder__dlm.KQ_SoldToCustomerId__c, ‘’) =IFNULL(ssot__Individual__dlm.KQ_Id__c, ‘’)

Combines the data from another DMO (an individual DMO) with the sales order DMO.

ON determines the connection point or foreign key between your DMOs. In this example, the foreign key between the two DMOs includes two fields - the party ID and key qualifier field from the SalesOrder DMO, and the ID and corresponding key qualifier field from the Individual DMO. Including the key qualifier attributes in the table join ensures data correctness.

Use the ISNULL() function in case key qualifiers have not been set up on all the underlying DLOs. There may be a mix of blank and IFNULL() will create consistency to improve the accuracy of the JOIN.

WHERE

ssot__SalesOrder__dlm.ssot__CreatedDate__c >= date_sub(current_date(), 365.0))

Identifies what data you want to or don’t want to include. In this example, the query shouldn’t include customers who spent money more than 365 days from today’s current date.

GROUP BY

customer_id__c,kq_customer_id__c

Determines your dimension or how you want to group your data (by customer ID & by key qualifier attribute for customer ID).

Data Model Objects

The first part of the SQL expression focuses on selecting attributes and measures based on a data model object (DMO). So knowing your data model is critical. Let’s review some common DMOs and associated measurable attributes.

DMO	How It’s Used	Measurable Attribute Example
Party	Defines who you have relationships with, typically customers. Party includes unified individual profiles, which are often used in calculated insights.	Birthdate in 7 days
Product	Defines anything you plan to sell or any part of a product to track for service purposes.	Women’s shoes over $50
Sales Order	Defines revenue, opportunities, purchases, and sales orders.	Cart value over $500
Engagement Data	Defines interactions or activities of customers, typically related to email engagement.	At least 5 email clicks
Case Data	Defines any logged issues or support tickets.	Open tickets >1 this past year

SQL Functions

You’ve learned about SQL terms, structure, and your data—it’s time to add in more complex functions to query and manipulate data further. Let’s review some available functions in more detail.

SQL Joins

To understand SQL joins, let’s review what data would be provided in your query based on the type of join. We use two sample DMOs to describe the following four join types.

DMO 1: Sales Order
DMO 2: Individual Customers

Inner Join, Left Join, Right Join, and Full Join.

	INNER JOIN or JOIN	LEFT [OUTER] JOIN	RIGHT [OUTER] JOIN	FULL [OUTER] JOIN
Description	Return records that have matching values in both DMOs.	Exclude the records we don’t want from DMO 2.	Exclude the records we don’t want from DMO 1.	Return all matching records from both DMOs.
Result	Specific sales orders for specific customers.	All sales orders and sales orders for specific customers.	All customers and a few sales orders for specific customers.	All sales orders and all customers, including some matching records.

Curious how DMOs link with unified profiles? Check out this help page for more information on linking and joining data.

Case

Another common SQL function is the CASE statement, which can be used to create new dimensions. CASE can be used to return a value when a condition is met.

Statement	What It Does
SELECT CASE WHEN SUM(SALESORDER__dlm.grand_total_amount__c) < 100 THEN 'Low Spender' End as Spend_Type__c	When a customer spends less than $100, they are identified as a low spender.

When a Calculated Insights condition is true, it stops reading and returns the result. If no conditions are true, it returns the value in the ELSE clause. If there’s no ELSE part and no conditions are true, it returns NULL.

Formatting Functions

Sometimes you need to do some clean-up functions before creating your final calculated insight. You can do this through formatting functions. Here is an example using the ROUND function to round a value to a specified decimal place.

Here is the specific syntax for this function.

ROUND(expression, number of decimal places)

Example

Here is an example using that expression to round a sales order.

Statement	What It Does
`SELECT ROUND(Salesorder_dlm.total_amount, 2)`	Instead of having a total customer spend display as $143.5555. This function would round to 2 decimal places and the outcome would be $143.56.

There are many more functions that you can add to your SQL statements to further refine your results. It’s helpful to identify your use case, and then work through possible functions that can get you to your desired data.

Visit Using SQL Statements in Data Cloud to learn all available functions.

Next Up: Review Examples

Now that you have an understanding of basic SQL concepts and know the structure, let’s check out some examples in the next unit to help you solve your own use cases.