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. There are two types of insights based on Structured Query Language (SQL): calculated and streaming. Calculated insights allow for complex calculations based on stored data, and streaming insights are created based on real-time data. As noted in the Create Insights in Data Cloud, there are various ways to build new insights in Data Cloud. SQL is one of them. 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 Salesforce Data Cloud. For example, a Marketing Cloud 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: 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, 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 queries for a customer’s total amount spent.

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_add(current_date(), 365.0))
GROUP BY
customer_id__c, kq_customer_id__c

Let’s break each of these sections down further.

Section 1

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

What It Does: 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.

Section 2

FROM ssot__SalesOrder__dlm

What It Does: Identifies the source of the data (a sales order DMO).

Section 3

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, ‘’)

What It Does: 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 IFNULL() 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.

Section 4

WHERE
ssot__SalesOrder__dlm.ssot__CreatedDate__c <= date_add(current_date(), 365.0))

What It Does: 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.

Section 5

GROUP BY
customer_id__c, kq_customer_id__c

What It Does: Determines your dimension or how you want to group your data (by customer ID and 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.

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
  

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.

SELECT CASE
WHEN
SUM(SALESORDER__dlm.grand_total_amount__c) < 100 THEN 'Low Spender'
End as Spend_Type__c

What It Does: When a customer spends less than $100, they are identified as a low spender. When a 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)

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

SELECT ROUND(Salesorder_dlm.total_amount, 2)

What It Does: 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.

More Functions

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.

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.

Resources

• Salesforce Help: Calculated Insights
• Salesforce Help: Using ANSI SQL Statements in Data Cloud
• Salesforce Help: General SQL Rules in Calculated Insights
• Salesforce Help: Unified Link Objects and Relationships to Unified Objects
• External: Salesforce GitHub, Data Cloud Calculated Insights

Back to Top