Analyze Sales Data with Calculated Insights

Create a Calculated Insight

Before creating a calculated insight, use identity resolution to produce unified profiles.

1. In Data Cloud, on the Calculated Insights tab, click New.
2. From the Data Space dropdown, select a data space (which may be set to default).
3. Click Create with SQL and click Next.
4. Enter the calculated insight name and an optional description. The API name field is auto-filled.
5. Replace the example expression with this example SQL query: Unified Individual Counts by Sales Object.

   SELECT UnifiedIndividual__dlm.ssot__Id__c AS Customer_Id__c
       ,approx_count_distinct(iil.Contact_Id__c) AS Contact_Count__c
       ,approx_count_distinct(iil.Lead_Id__c) AS Lead_Count__c
       ,approx_count_distinct(iil.ssot__AccountId__c) AS Account_Contact_Count__c
       ,approx_count_distinct(iil.ssot__DataSourceId__c) AS Salesforce_Org_Count__c
   FROM UnifiedIndividual__dlm
   /* The sub-query is required to perform case statements prior to aggregating */
   LEFT JOIN (
       SELECT IndividualIdentityLink__dlm.UnifiedRecordId__c AS UnifiedRecordId__c
      	 ,IndividualIdentityLink__dlm.SourceRecordId__c AS SourceRecordId__c
      	 ,IndividualIdentityLink__dlm.ssot__DataSourceId__c AS ssot__DataSourceId__c
      	 ,CASE
      		 WHEN IndividualIdentityLink__dlm.ssot__DataSourceObjectId__c = 'Contact'
      			 THEN IndividualIdentityLink__dlm.SourceRecordId__c
      		 ELSE NULL
      		 END Contact_Id__c
      	 ,CASE
      		 WHEN IndividualIdentityLink__dlm.ssot__DataSourceObjectId__c = 'Lead'
      			 THEN IndividualIdentityLink__dlm.SourceRecordId__c
      		 ELSE NULL
      		 END Lead_Id__c
      	 ,AccountContact.ssot__AccountId__c AS ssot__AccountId__c
       FROM IndividualIdentityLink__dlm
       LEFT JOIN (
      	 SELECT DISTINCT ssot__AccountContact__dlm.ssot__IndividualId__c AS ssot__IndividualId__c
      		 ,ssot__AccountContact__dlm.ssot__AccountId__c AS ssot__AccountId__c
      	 FROM ssot__AccountContact__dlm
      	 ) AccountContact ON IndividualIdentityLink__dlm.SourceRecordId__c = AccountContact.ssot__IndividualId__c
       WHERE IndividualIdentityLink__dlm.ssot__DataSourceId__c LIKE 'Salesforce_00D%'
       ) iil
       /* "Data Source ID like Salesforce_00D%" identifies Salesforce CRM sources. 00D is the prefix for the Org ID object type.
        * This is optional but it adds greater certainty that this is a Salesforce Org source ID from the connector.*/
       ON UnifiedIndividual__dlm.ssot__Id__c = iil.UnifiedRecordId__c
   GROUP BY Customer_Id__c

   This calculated insight contains one record per unified individual. It shows how often each person appears as a contact, lead, and account; and how often they appear across multiple orgs. To work, it requires the Account, Contact, and Lead objects that are installed as part of the Sales Cloud starter bundle. It also requires that the identity resolution process has been run successfully.
6. To check whether your SQL expression is valid, click Check Syntax. If it isn’t valid, you can’t continue. Double-check your expression and try again.
7. Click Activate.
8. From the Schedule dropdown, select Not Scheduled.
9. Click Enable.
   You can schedule a calculated insight, but for these use case examples, run them one time manually.

The calculated insight appears on the record home page.

Note To learn more about creating insights in Data Cloud, take the trail Enhance Data in Data Cloud.