Analyze Service Data with Calculated Insights

Calculate multidimensional metrics on your data within Data Cloud. You can create metrics at the profile, segment, and population levels. For example, evaluate performance at the channel level or use product metrics to understand purchasing and browsing behavior.

Required Editions

Available in: All Editions supported by Data 360. See Data 360 edition availability.

A Data Cloud architect can write SQL expressions or use the SQL Insight Builder to create metrics and dimensions from mapped objects and fields. Then using Data Explorer, you can view Data Cloud data from your data lake objects (DLOs), data models objects (DMOs), or calculated insight objects (CIOs).

Create a Calculated Insight

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

There are two example expressions.

In Data Cloud, on the Calculated Insights tab, click New.
From the Data Space dropdown, select a data space (which may be set to default).
Click Create with SQL and click Next.
Enter the calculated insight name and an optional description. The API name field is auto-filled.

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.

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

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

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.
Click Activate.
From the Schedule dropdown, select Not Scheduled.
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.

View in Data Explorer

In Data Explorer, you can view selected Data Cloud data from your data lake objects (DLOs), data models objects (DMOs), calculated insight objects (CIOs), or data graphs.

Go to the Data Explorer tab in Data Cloud.
If you don't see Data Explorer, contact your admin to enable the permissions.
Select the appropriate data space and then select Calculated Insights from the Object dropdown. Then search for the insight you created.