Calculating Total Score by Opportunity with Salesforce Marketing Cloud SQL

If you’re using Activation Studio Score Designer for Marketing Cloud and Salesforce Sales Cloud together, you might need to calculate the total score for all contacts associated with an opportunity in Salesforce. Marketing Cloud SQL is a powerful and flexible way to combine data from different Salesforce objects and do calculations on them. In this article, we’ll explain how to use Marketing Cloud SQL to calculate the total score by opportunity.

Step 1: Where to find the Data Extension for the Contact Scores

First, you’ll need to find the data extension in Marketing Cloud that contains the contact scores. Activation Studio created this data extension for you. This data extension includes the SubscriberKey and Score fields. The SubscriberKey field should be set to the Contact ID from Salesforce, and the Score field should be set to the score for that contact. You can find the Data Extension here: Data Extensions > ActivationStudio > AS_Score_Designer > AS_Scores > AS_Combined_Scores

Don’t forget to create a new data extension to store the results including the fields Opportunity_Salesforce.Id, Opportunity_Salesforce.Name and TotalScore.

Step 2: Write the Marketing Cloud SQL Query

Once you’ve found the data extension, you can write the Marketing Cloud SQL query that will calculate the total score by opportunity. When using the Marketing Cloud Connector the objects will have similar names. Here’s an example query:

SELECT Opportunity_Salesforce.Id,Opportunity_Salesforce.Name, SUM(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
INNER JOIN Opportunity_Salesforce ON Account_Salesforce.Id = Opportunity_Salesforce.AccountId
GROUP BY Opportunity_Salesforce.Name,Opportunity_Salesforce.Id

This query uses the SUM() function to calculate the total score for all contacts associated with each opportunity. It joins the Contact_Salesforce, AS_Combined_Scores, Account_Salesforce, and Opportunity_Salesforce objects together, linking them by their respective IDs. Finally, the results are grouped by Opportunity_Salesforce.Id and Opportunity_Salesforce.Name.

Step 3: Run the Query and Analyze the Results

Once you’ve written the query, you can run it in Marketing Cloud to calculate the total score by opportunity. The results will show you the opportunity ID, opportunity name, and total score for all contacts associated with each opportunity. You can use these results to analyze the effectiveness of your campaigns and identify areas for improvement.