Skip to main content
Popular Search abandoned cartstitch sfmc subsparameter management

Calculating total score per account

Activation Studio calculates a score per subscriber based on the lead scoring rules. If you want to start an Account Based Marketing (ABM) journey, you probably also want to know what the total score per account is. This can easily be setup with a couple of steps.

Step 1: Create a new data extension to store the total score per account

  • Create a brand new data extension
  • Create these fields within the data extension:
    • SubscriberKey (text)
    • AccountId (text)
    • AccountName (text)
    • Acount_TotalScore (decimal, 18,0)
    • Account_CreationDate (date)

Step 2:Create a SQL activity

  • Go to Automation Studio >> Activities
  • Create a new SQL Activity
  • Copy and paste the SQL below
SELECT 
    Account_Salesforce.Id AS AccountId,
    AS_Combined_Scores.SubscriberKey,
    CAST(accSub.TotalScore AS DECIMAL) AS Account_TotalScore,
    Account_Salesforce.Name AS AccountName,
    Account_Salesforce.CreatedDate as Account_CreationDate
FROM 
    Contact_Salesforce
    INNER JOIN AS_Combined_Scores ON Contact_Salesforce.Id = AS_Combined_Scores.SubscriberKey
    INNER JOIN Account_Salesforce ON Contact_Salesforce.AccountId = Account_Salesforce.Id
    INNER JOIN Opportunity_Salesforce o ON Account_Salesforce.Id = o.AccountId 
    INNER JOIN (
        SELECT 
            Account_Salesforce.Id AS AccId,
            SUM(AS_Combined_Scores.Score) AS TotalScore
        FROM Contact_Salesforce
        INNER JOIN AS_Combined_Scores ON Contact_Salesforce.Id = AS_Combined_Scores.SubscriberKey
        INNER JOIN Account_Salesforce ON Contact_Salesforce.AccountId = Account_Salesforce.Id
        GROUP BY Account_Salesforce.Id
    ) accSub ON Account_Salesforce.Id = accSub.AccId

The example SQL above is only valid if you have Salesforce synchronized data extensions. If you have other objects, you need to change these to the respective object and fieldnames.

  • Navigate to Contact Builder >> Data Designer
  • Create a new Attribute Group
  • Link Contact Key to SubscriberKey (One to one)
  • Press save

Step 4: Creating a segment based on account score

  • Navigate to Activation Studio
  • Create a new segment
  • Drag in the field ‘Account_TotalScore’

Optional next steps are syncing the account total score back to Sales Cloud. And don’t forget to actually create an engaging ABM journey.