Alumni Engagement Metrics Calculated Insights Definitions

Use these sample calculated insights when you set up Alumni Engagement Metrics.

Required Editions
Available in: Lightning Experience
Available in: Performance, Unlimited, and Developer Editions with Education Cloud and the Agentforce for Education add-on license. Einstein generative AI is available in Lightning Experience. To purchase the Agentforce for Education add-on, contact your Salesforce account executive.

Example: Comprehensive Using Individual

SELECT ssot__Individual__dlm.ssot__Id__c as IndividualId__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1 
    WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1 
    WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1 
    WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1 
    ELSE 0
END) as EngagementActivities__c,
CASE WHEN
    SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1 
        WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1 
        WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1 
        WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1 
        ELSE 0
    END) > 0 THEN 'Engaged'
    ELSE 'Not Engaged' 
END as EngagementStatus__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1
    ELSE 0
END) as CommunicationActivities__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1
    ELSE 0
END) as ExperientialActivities__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1
    ELSE 0
END) as PhilanthropicActivities__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1
    ELSE 0
END) as VolunteerismActivities__c,
CASE WHEN SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1
        ELSE 0
    END) > 2 THEN 'High'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1
        ELSE 0
    END) > 1 THEN 'Medium'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1
        ELSE 0
    END) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as CommunicationStatus__c,
CASE WHEN SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1
        ELSE 0
    END) > 2 THEN 'High'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1
        ELSE 0
    END) > 1 THEN 'Medium'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1
        ELSE 0
    END) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as ExperientialStatus__c,
CASE WHEN SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1
        ELSE 0
    END) > 2 THEN 'High'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1
        ELSE 0
    END) > 1 THEN 'Medium'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1
        ELSE 0
    END) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as PhilanthropicStatus__c,
CASE WHEN SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1
        ELSE 0
    END) > 2 THEN 'High'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1
        ELSE 0
    END) > 1 THEN 'Medium'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1
        ELSE 0
    END) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as VolunteerismStatus__c
FROM (
    SELECT ssot__EngagementAction__dlm.ssot__IndividualId__c as IndividualId__c,
    ssot__EngagementAction__dlm.ssot__Id__c as RecordId__c,
    ssot__EngagementAction__dlm.ssot__EngagementCategory__c as EngagementType__c
    FROM ssot__EngagementAction__dlm
    UNION
    SELECT ssot__AccountContact__dlm.ssot__IndividualId__c as IndividualId__c,
    ssot__GiftTransaction__dlm.ssot__Id__c as RecordId__c,
    'Philanthropic' as EngagementType__c
    FROM ssot__GiftTransaction__dlm
    INNER JOIN ssot__AccountContact__dlm
        ON ssot__GiftTransaction__dlm.ssot__DonorId__c = ssot__AccountContact__dlm.ssot__AccountId__c
) as SubQuery1
INNER JOIN ssot__Individual__dlm
    ON SubQuery1.IndividualId__c = ssot__Individual__dlm.ssot__Id__c
GROUP BY ssot__Individual__dlm.ssot__Id__c

Example: Comprehensive Using Unified Individual

SELECT UnifiedIndividual__dlm.ssot__Id__c as IndividualId__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1 
    WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1 
    WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1 
    WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1 
    ELSE 0
END) as EngagementActivities__c,
CASE WHEN
    SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1 
        WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1 
        WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1 
        WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1 
        ELSE 0
    END) > 0 THEN 'Engaged'
    ELSE 'Not Engaged' 
END as EngagementStatus__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1
    ELSE 0
END) as CommunicationActivities__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1
    ELSE 0
END) as ExperientialActivities__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1
    ELSE 0
END) as PhilanthropicActivities__c,
SUM( CASE
    WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1
    ELSE 0
END) as VolunteerismActivities__c,
CASE WHEN SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1
        ELSE 0
    END) > 2 THEN 'High'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1
        ELSE 0
    END) > 1 THEN 'Medium'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Communications' THEN 1
        ELSE 0
    END) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as CommunicationStatus__c,
CASE WHEN SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1
        ELSE 0
    END) > 2 THEN 'High'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1
        ELSE 0
    END) > 1 THEN 'Medium'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Experiential' THEN 1
        ELSE 0
    END) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as ExperientialStatus__c,
CASE WHEN SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1
        ELSE 0
    END) > 2 THEN 'High'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1
        ELSE 0
    END) > 1 THEN 'Medium'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Philanthropic' THEN 1
        ELSE 0
    END) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as PhilanthropicStatus__c,
CASE WHEN SUM( CASE
        WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1
        ELSE 0
    END) > 2 THEN 'High'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1
        ELSE 0
    END) > 1 THEN 'Medium'
    WHEN SUM( CASE 
        WHEN SubQuery1.EngagementType__c = 'Volunteerism' THEN 1
        ELSE 0
    END) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as VolunteerismStatus__c
FROM (
    SELECT ssot__EngagementAction__dlm.ssot__IndividualId__c as IndividualId__c,
    ssot__EngagementAction__dlm.ssot__Id__c as RecordId__c,
    ssot__EngagementAction__dlm.ssot__EngagementCategory__c as EngagementType__c
    FROM ssot__EngagementAction__dlm
    UNION
    SELECT ssot__AccountContact__dlm.ssot__IndividualId__c as IndividualId__c,
    ssot__GiftTransaction__dlm.ssot__Id__c as RecordId__c,
    'Philanthropic' as EngagementType__c
    FROM ssot__GiftTransaction__dlm
    INNER JOIN ssot__AccountContact__dlm
        ON ssot__GiftTransaction__dlm.ssot__DonorId__c = ssot__AccountContact__dlm.ssot__AccountId__c
) as SubQuery1
INNER JOIN IndividualIdentityLink__dlm
    ON SubQuery1.IndividualId__c = IndividualIdentityLink__dlm.SourceRecordId__c
INNER JOIN UnifiedIndividual__dlm ON
    IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c
GROUP BY UnifiedIndividual__dlm.ssot__Id__c

Example: Unified Individual Id on Engagement Action

SELECT UnifiedIndividual__dlm.ssot__Id__c  as UnifiedIndividualId__c,
COUNT( ssot__EngagementAction__dlm.ssot__Id__c ) as VolunteerismActivities__c,
CASE WHEN COUNT( ssot__EngagementAction__dlm.ssot__Id__c ) > 2 THEN 'High'
    WHEN COUNT( ssot__EngagementAction__dlm.ssot__Id__c ) > 1 THEN 'Medium'
    WHEN COUNT( ssot__EngagementAction__dlm.ssot__Id__c ) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as VolunteerismStatus__c
FROM ssot__EngagementAction__dlm
INNER JOIN IndividualIdentityLink__dlm
    ON IndividualIdentityLink__dlm.SourceRecordId__c = ssot__EngagementAction__dlm.ssot__IndividualId__c
JOIN UnifiedIndividual__dlm
    ON UnifiedIndividual__dlm.ssot__Id__c = IndividualIdentityLink__dlm.UnifiedRecordId__c
WHERE ssot__EngagementAction__dlm.ssot__EngagementCategory__c = 'Volunteerism'
GROUP BY UnifiedIndividualId__c

Example: Unified Individual Id on Gift Transaction

SELECT UnifiedIndividual__dlm.ssot__Id__c  as UnifiedIndividualId__c,
COUNT( ssot__GiftTransaction__dlm.ssot__Id__c ) as PhilanthropicActivities__c,
CASE WHEN COUNT( ssot__GiftTransaction__dlm.ssot__Id__c ) > 2 THEN 'High'
    WHEN COUNT( ssot__GiftTransaction__dlm.ssot__Id__c ) > 1 THEN 'Medium'
    WHEN COUNT( ssot__GiftTransaction__dlm.ssot__Id__c ) > 0 THEN 'Low'
    ELSE 'Not Applicable' 
END as PhilanthropicStatus__c
FROM ssot__GiftTransaction__dlm
INNER JOIN ssot__AccountContact__dlm
    ON ssot__GiftTransaction__dlm.ssot__DonorId__c = ssot__AccountContact__dlm.ssot__AccountId__c
INNER JOIN IndividualIdentityLink__dlm
    ON IndividualIdentityLink__dlm.SourceRecordId__c = ssot__AccountContact__dlm.ssot__IndividualId__c
JOIN UnifiedIndividual__dlm
    ON UnifiedIndividual__dlm.ssot__Id__c = IndividualIdentityLink__dlm.UnifiedRecordId__c
GROUP BY UnifiedIndividualId__c

Example: Individual ID

SELECT  
    COUNT( VolunteeringActivity_dlm.Id_c ) as count__c,
    ssot_Individual_dlm.ssot_Id_c  as indvid__c
FROM
    VolunteeringActivity__dlm
LEFT JOIN
    ssot_Individual_dlm
ON
    ssot_Individual_dlm.ssot_Id_c= VolunteeringActivity_dlm.Individual_c
GROUP BY
    indvid__c

Example: Gift Transaction and Unified Individual

SELECT
    UnifiedIndividual__dlm.ssot__Id__c AS unified_individual_id__c,
    CASE
        WHEN COUNT(ssot__GiftTransaction__dlm.ssot__DonorId__c) > 5 THEN 'high'
        ELSE 'low'
    END AS status_of_giving__c,
    (
        COUNT(ssot__GiftTransaction__dlm.ssot__DonorId__c)
    ) AS field_for_count__c
FROM
    ssot__GiftTransaction__dlm
    LEFT OUTER JOIN ssot__AccountContact__dlm ON (
        ssot__GiftTransaction__dlm.ssot__DonorId__c = ssot__AccountContact__dlm.ssot__AccountId__c
    )
    LEFT OUTER JOIN IndividualIdentityLink__dlm ON (
        ssot__AccountContact__dlm.ssot__IndividualId__c = IndividualIdentityLink__dlm.SourceRecordId__c
        AND ssot__AccountContact__dlm.KQ_IndividualId__c = IndividualIdentityLink__dlm.KQ_SourceRecordId__c
    )
    JOIN UnifiedIndividual__dlm ON (
        IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c
    )
GROUP BY
    unified_individual_id__c