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. – Lukas Lunow Mar 27 '19 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 Mar 27 '19 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 – Lukas Lunow Mar 27 '19 at 19:20

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.