Data 360: Calculated Insight using SQL for Segment

For Calculated Insights created using SQL (not Visual Builder), there is no "Show in Builder" option under the dropdown menu. 

If you edit the SQL and save the Calculated Insight, you must republish the Calculated Insight to get the new result. Also, you can't edit the SQL if there is a structural change, such as changing or removing an output field; the recommended solution is to create a new CI.

What is the SQL requirement in a Calculated Insight?

Syntax

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_]>

1. Must include a Profile Key

The output must contain a key that can be related back to the segment population, depending on whether your segment is built on Unified Individual or Individual.

2. Must include at least one aggregated measure

Calculated Insights require aggregation such as: COUNT(), SUM(), MAX(), MIN(), AVG().

3. Must have a GROUP BY

Sample SQL:

SELECT 

UnifiedIndividual__dlm.ssot__Id__c AS unifiedindividual_id__c, 

UnifiedIndividual__dlm.ssot__FirstName__c AS first_name__c, 

UnifiedIndividual__dlm.ssot__LastName__c AS last_name__c, 

ssot__EmailEngagement__dlm.ssot__EngagementChannelActionId__c AS channel__c,

COUNT(ssot__EmailEngagement__dlm.ssot__EmailMessageId__c) AS count_of_email_engagement__c, 

MIN(ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c) AS first_date__c,

MAX(ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c) AS last_date__c  

FROM ssot__EmailEngagement__dlm 

JOIN ssot__Individual__dlm ON (ssot__EmailEngagement__dlm.ssot__IndividualId__c = ssot__Individual__dlm.ssot__Id__c) 

JOIN IndividualIdentityLink__dlm ON (ssot__Individual__dlm.ssot__Id__c = IndividualIdentityLink__dlm.SourceRecordId__c) 

JOIN UnifiedIndividual__dlm ON (IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c) 

WHERE (ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c >= date_sub(current_date(), 7)) 

GROUP BY unifiedindividual_id__c, first_name__c, last_name__c, channel__c

Sample data from the above query to show how the JOIN works:

ssot__EmailEngagement__dlm.ssot__IndividualId__c = 003xxx or 00Qxxx

ssot__Individual__dlm.ssot__Id__c = 003xxx or 00Qxxx

IndividualIdentityLink__dlm.SourceRecordId__c = 003xxx or 00Qxxx

IndividualIdentityLink__dlm.UnifiedRecordId__c = c434453739ba80ff0cee6df928b19cde 

UnifiedIndividual__dlm.ssot__Id__c = c434453739ba80ff0cee6df928b19cde

In the Segment

Create a segment, type the CI name under the Search Attributes box, and use the aggregated "measure" attribute in the Include tab.

Can I run SQL from Calculated Insight in Query Editor?

Calculated Insight SQL and Query Editor SQL are similar, but there are some differences;

1.  When filtering by date field

In Query Editor: 

WHERE (ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c >= DATE_ADD('day', -7, CURRENT_DATE)) 

OR 

WHERE (ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c >= CURRENT_DATE - INTERVAL '7' DAY) 

In Calculated Insight: 

WHERE (ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c >= date_sub(current_date(), 7)) 

2. Attribute name must end with __c in CI

In Query Editor

SELECT UnifiedIndividual__dlm.ssot__FirstName__c AS first_name

In Calculated Insight: 

SELECT UnifiedIndividual__dlm.ssot__FirstName__c AS first_name__c

3. The ORDER BY clause is not supported in CI