SFMC: Query Based Data Extension Help

Question

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 !

Answer 1

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”

Answer 2

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
WHERE
  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: