Menu
Activation Studio
  • Home
  • Solutions
    • ---
      • Segment Designer
      • Score Designer
      • Interaction Designer
    • ---
      • Marketing Cloud Segmentation
      • Lead management
      • Journey tracking
  • Pricing
    • Get quote
    • FAQ
  • Contact
Activation Studio Activation Studio
  • Home
  • Solutions
    • ---
      • Segment Designer
      • Score Designer
      • Interaction Designer
    • ---
      • Marketing Cloud Segmentation
      • Lead management
      • Journey tracking
  • Pricing
    • Get quote
    • FAQ
  • Contact
Demo
Popular Search stitch sfmc subssalesforceparameter managementrfm modelabandoned cart

Getting started

  • Using Data Designer in Contact Builder
  • Setup subscriber filter business unit
  • Adding Score and Interaction Designer to Data Designer
  • Setting up roles & rights

Segment Designer

  • Creating your first segment Marketing Cloud

Score Designer

  • Calculating Total Score by Opportunity with Salesforce Marketing Cloud SQL

Interaction Designer

  • How to setup interaction tracking SFMC?
  • How to stitch visitors and SFMC subscribers?
  • Using parameter management Marketing Cloud
  • Using Postman to test interactions in Interaction Designer

Use cases

  • Setting up abandoned cart in Salesforce Marketing Cloud
  • Understanding RFM Analysis and Building an RFM Model in Marketing Cloud
  • Calculating Total Score by Opportunity with Salesforce Marketing Cloud SQL

FAQ

  • How does lead conversion to a Contact work in Marketing Cloud?
  • How often are scores per subscriber refreshed?
  • Marketing Cloud Automation Studio and Activation Studio?
  • Home
  • Docs
  • Score Designer
  • Calculating Total Score by Opportunity with Salesforce Marketing Cloud SQL

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.

What are your Feelings
Still stuck? How can we help?

How can we help?

Harvest Tech Labs

© All rights reserved.
Powered by Harvest Tech Labs.

Salesforce Marketing Cloud

Available on AppExchange Salesforce

Info

  • Contact us

  • Book a demo

  • Interactive Product Demo

  • Get a quote

  • Pricing

  • Privacy policy

  • 30 day free trial

Discover

  • Development roadmap

  • News

  • Releases

Learn

  • What is Salesforce Marketing Cloud?

  • Knowledge base

More

  • Harvest Digital

  • Direct Messenger

Activation Studio
Leonard Springerlaan 29
9727KB, Groningen
the Netherlands

+31850049792