Review SQL Examples
Learning Objectives
After completing this unit, you’ll be able to:
- Recognize the functions of a SQL statement.
- Include a time frame in a streaming insights SQL statement.
- Find resources to help build SQL.
Streaming Insights in Data Cloud
The best way to learn to build insights with SQL is to review example statements. Once you can identify what each section of a SQL statement does, you can recognize patterns that can be used to build your own statements. Before we view examples, it’s important to know that there are two types of insights: Calculated and streaming. Calculated insights are used to query and create complex calculations based on stored data, while streaming insights are queries based on real-time data.
And with streaming insights, you can:
- Generate time series analytics on continuously moving data.
- Find useful patterns, and share the insights with other apps via Data Actions.
- Author using the insights builder and SQL.
- Use with Java Database Connectivity (JDBC) API and visualizations tools such as Tableau.
SQL Examples of Calculated Insights
Let’s start with a few calculated insights examples. This first one calculates email engagement data found within Marketing Cloud and groups it with unified individual profile data.
Let’s break down each section of this SQL statement.
Section 1
What It Does: Calculate the count of emails opened according to unified individuals, also called the customer ID.
Section 2
What It Does: Locate this information in the email engagement DMO.
Section 3
What It Does: In this step, connect the email engagement DMO with the individual identity Link DMO. Connect them using the foreign keys of source record ID and individual ID and respective key qualifier attributes, and join based on email engagement equaling an email open. Also connect this data with the unified individual DMO based on the ID and unified record ID.
Section 4
What It Does: Group this information based on customer ID.
Before we move on, there’s one more thing to note about the previous example. The way UnifiedIndividual object is related to an engagement object (such as EmailEngagement) is with a bridge object (such as Individual Identity link), which contains the mapping of Unified Individual ID and Individual ID.
Next let’s look at another SQL example using a ranking function. This statement calculates customer spend and then uses it to rank customers based on the spend across all of the Unified Individuals.
Let’s break this statement down further.
Section 1
What It Does: Across all unified individuals, rank each customer based on total spend.
Section 2
What It Does: Look for this info in the Sales Order DMO.
Section 3
What It Does: Join data from the sales order DMO to the individual identity link DMO on customer ID and individual ID, and their respective key qualifier attributes. Join with some matching data in the unified individual DMO based on unified record ID and ID.
Section 4
What It Does: Group this information based on customer ID.
Section 5
What It Does: Include less than 1000 customers according to their total spend amount.
Streaming Insights
Now that we’ve reviewed some examples for calculated insights, let’s switch to build SQL for streaming insights. Building a streaming insight with SQL is similar to building a calculated insight, except you need to consider a window of time.
Streaming Insights SQL Example
Let’s look at an example that shows page views within a time frame of 5 minutes.
The noticeable difference in this SQL statement from the calculated insights are the WINDOW commands. These define how your results are grouped—in this example, within 5 minutes.
And here is an example result of this expression.
START_C
| END_C
| CUSTOMER_ID_C
| PRODUCT_C
| PAGE_VIEWS_C
|
---|---|---|---|---|
12
| 12.05 | 1 | HK0012
| 1
|
12.05
| 12.1 | 2 | JK0078
| 2
|
12.1
| 12.15 | 3 | HK0078
| 1
|
Let’s see another example.
Let’s break down each section of this statement.
Section 1
What It Does: Find the sum of the orders placed based on the MobileApp events streaming source between a start and end time.
Section 2
What It Does: Use the MobileApp events streaming source.
Section 3
What It Does: Group the results into 5 minute aggregations according to product ID and include info about: order quantity placed, product, start and end time identified.
Build Your Statements
Now that you have the basic concepts and some examples, explore the various options you have when building your insights. There are many more functions that you can add to your SQL statements to further your results.
- Check out some specific SQL Rules in Calculated Insights.
- Explore our repository of SQL examples in a Data Cloud Git Hub Repo.
Finally, you can visit the Data Cloud Insight help page to learn how to create calculated insights. And with that, you’re ready to unlock the power of a SQL query within Data Cloud.
Resources
- Salesforce Help: Calculated Insights
- Salesforce Help: Using ANSI SQL Statements in Data Cloud
- Salesforce Help: General SQL Rules in Calculated Insights
- Salesforce Help: Using the JDBC Driver in Data Cloud
- External: Salesforce GitHub, Data Cloud Calculated Insights