We have a synchronized data extension from Sales Cloud coming into our SFMC instance. In the Opportunities table, it is possible for a single customer to have multiple Opportunities in various stages, depending on which dealership received the lead. We have Opportunity stages such as New, In-Process, Dormant, Deposit Received, etc.

What I'm trying to do is write a query that will only bring in emails from Opportunities in a "Dormant" stage but that also does not have an opportunity based on the same email in either "In-Process" or "Deposit Received"

Any guidance on how to approach this would be greatly appreciated. Thanks !

  • Welcome to SFSE. Can you please share, what you have tried so far to achieve your goal - e.g. the SQL query you have built, but which isn’t working as expected. Commented Mar 27, 2019 at 19:13
  • I guess that's the problem...I'm not even sure where to start. My initial thought was to pull all the data into a Python app, clean it there, and then export that into a data extension with the API but if there is a "cleaner" solution than that, I'd much rather go that route.
    – B. Greene
    Commented Mar 27, 2019 at 19:16
  • 2
    You need to build a destination Data Extension with the fields you want to populate. Then create a SQL Query activity in Automation Studio, joining your opportunity DE with contacts or leads, selecting the data from the Synchronised Data Extensions Commented Mar 27, 2019 at 19:20

2 Answers 2


You would have to roll up your sleeves a bit and write the SQL yourself. But here is something to get you started.

  • Create a query activity in Automation Studio
  • Write a JOIN query for Opportunity to OpportunityContactRole and JOIN on OpportunityId
  • Then JOIN OpportunityContactRole to Contact on ContactId
  • Use a WHERE clause where the Stage is “Dormant”
  • Also AND NOT EXISTS using a nested SELECT statement WHERE Stage is “In-Process” OR “Deposit Received”

Salesforce Objects Relationships


Okay this will depend a bit on your data model, but if you're trying to get emails from Contacts then they are most likely linked to Accounts, and similarly Opportunities are linked to accounts.

In that case you could write a query like this:

SELECT *    // all fields
// renaming your synchronized DE to something easier to read, i.e. "Accounts"
FROM Salesforce_Accounts__c Accounts    
  // relate Opportunities to Accounts  
  INNER JOIN Salesforce_Opportunities__c Opportunities    
    // explain how they are linked
    Accounts.Id on Opportunities.AccountId   
  INNER JOIN Salesforce_Contacts__c Contacts
    Accounts.Id on Contacts.AccountId
// now let's filter
  Opportunities.Stage = "Dormant"

All of this you'll need to put into a "Query Activity" (see the docs) and you'll need to put the result in another DE that you need to create.

However, there's a much easier way to go about it if you don't want to get into SQL, although you will need to get a 3rd party app called DESelect. Then you could do the same with drag-and-drop which would look a bit like this:

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .