Analyze Service 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 an example SQL query. Both examples require that the starter bundle has been installed and the identity resolution process has been run successfully.
   1. Unified Individual Counts by Service Object: This calculated insight contains one record per unified individual. It shows how often each person appears as a contact, as an account, and the number of orgs in which the individual has opened cases.

      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.ssot__AccountId__c) AS Account_Contact_Count__c 
          ,approx_count_distinct(iil.CaseDataSource__c) AS Case_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 ,AccountContact.ssot__AccountId__c AS ssot__AccountId__c ,AccountContact.CaseDataSource__c AS CaseDataSource__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 ,ssot__Case__dlm.ssot__DataSourceId__c 
              AS CaseDataSource__c 
            FROM ssot__AccountContact__dlm 
            LEFT JOIN ssot__Case__dlm 
              ON ssot__AccountContact__dlm.ssot__AccountId__c = ssot__Case__dlm.ssot__AccountId__c ) 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 

   2. Unified Individual Recent Cases per Unified Individual: This calculated insight contains one record per unified individual and includes a count of the cases opened by that person in the last 30, 60, and 90 days.

      SELECT UnifiedIndividual__dlm.ssot__Id__c AS Customer_Id__c 
          ,approx_count_distinct(iil.Bucket30__c) AS Case_Count_30_Days__c 
          ,approx_count_distinct(iil.Bucket60__c) AS Case_Count_60_Days__c     
          ,approx_count_distinct(iil.Bucket90__c) AS Case_Count_90_Days__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 
              ,CASE 
                  WHEN DATEDIFF(CURRENT_DATE (), ssot__Case__dlm.ssot__CreatedDate__c) <= 30 
                      THEN ssot__Case__dlm.ssot__Id__c 
                  ELSE NULL END AS Bucket30__c 
              ,CASE 
                  WHEN DATEDIFF(CURRENT_DATE (), ssot__Case__dlm.ssot__CreatedDate__c) > 30 
                      AND DATEDIFF(CURRENT_DATE (), ssot__Case__dlm.ssot__CreatedDate__c) <= 60 
                      THEN ssot__Case__dlm.ssot__Id__c 
                  ELSE NULL END AS Bucket60__c 
              ,CASE 
                  WHEN DATEDIFF(CURRENT_DATE (), ssot__Case__dlm.ssot__CreatedDate__c) > 60 
                      AND DATEDIFF(CURRENT_DATE (), ssot__Case__dlm.ssot__CreatedDate__c) <= 90 
                      THEN ssot__Case__dlm.ssot__Id__c 
                  ELSE NULL 
                  END AS Bucket90__c 
           FROM IndividualIdentityLink__dlm 
           INNER JOIN ssot__Case__dlm 
           ON IndividualIdentityLink__dlm.SourceRecordId__c = ssot__Case__dlm.ssot__AccountContactId__c 
                  WHERE CURRENT_DATE () <= date_add(ssot__Case__dlm.ssot__CreatedDate__c, 91) 
          ) iil ON UnifiedIndividual__dlm.ssot__Id__c = iil.UnifiedRecordId__c 
      GROUP BY Customer_Id__c

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.