Marketing Cloud SQL statement

Question

I'm trying to run an exclusion SQL across a list I have. I have three separate brands in this list with customers that can sit across 2 or 3 of these. I want to remove customer and their opps completely from the list if they reach two criteria's.

An example of what the list looks like (with relevant fields) is below. https://docs.google.com/spreadsheets/d/1GKbmXediDU1JCyUkUT7Hh5bmo0E1vxGaIGrdEYw99hc/edit?usp=sharing

I want to exclude any customer from the list if:

a) their Status is marked as either "COMPLETED" or "DONE" OR b) their Account Stage is marked as "NONE"

In the example provided there should only be 4 customer opps returned for the email "[email protected]" as...

[email protected] - has an oppt with DONE

[email protected] - has an oppt with Account Stage of "NONE"

Hope this makes sense!

Answer 1

Removing from Lists directly is a fairly complex item and would likely require API calls, not SQL. This will add to the complexity and to the resource draw. SQL in SFMC is used more for Data Extensions and Data Views.

My recommendation is to switch the sending object to a Data Extension. This is actually fairly easy to do.

You would need to Query the _ListSubscribers DataView(DV) to gather the SubscriberID and then JOIN it with the _EnterpriseAttribute DV (Enterprise 2.0 accounts only) to get all the appropriate fields from your lists.

1. Create a Data Extension (DE) with all the appropriate field names, types and lengths - mimicking your list.
2. Create the SQL Query

For example:

SELECT
  l.SubscriberKey,
  e.enterpriseAttribute1,
  e.enterpriseAttribute2
FROM _ListSubscribers l
INNER JOIN _EnterpriseAttribute e
ON  l.SubscriberID = e._SubscriberID
WHERE l.ListID = yourListIDHere
AND (
     Status NOT IN ('COMPLETED', 'DONE')
     OR Account Stage = 'NONE'
)

Then the DE will contain all the correct Profile Attributes and will exclude those you want removed.

---

If you NEED to keep it in a list, you can still accomplish this, it just adds a bit more to it.

You would need to add 2 steps to your automation:

1. Create the DE as above
2. Create the SQL Query
3. Create a 'Data Extract' activity using the 'Data Extension Extract' type and aim it at the DE you just created above and drop it to the import folder on your SFTP.
4. Create an 'Import Activity' to grab the file you just extracted above and import it into your list. You would need to set the 'Action' in this import to OVERWRITE so it will remove all the fields you want excluded.

Answer 2

First, I would recommend you move from Lists to Data Extensions, because:

1. Data extensions (DEs) in general provide more options, see this comparison for more info.
2. Salesforce seems more focused on supporting DEs than Lists. In fact at a SFMC conference for partners there was mention that Lists may eventually become deprecated. I have since steered my clients away from their use.

Now you don't have to use SQL right away. If your data is all stored within one DE, you could actually create a data filter. (In fact, you can also create filters for profile attributes used in lists, but again, I recommend the use of DEs.)

However, in reality you will often start data across several DEs. In that case, and if you are not familiar with SQL, it might actually be worth using a third-party tool (e.g. DESelect) so you can just drag-and-drop (and don't have to learn SQL or spend a lot of time on it).

If you do want to use SQL, in the case of your example it would be something like:

SELECT * FROM <Data Extension Name> 
WHERE
  (Status = 'COMPLETED' OR Status = 'DONE') AND
  "Account Stage" = 'NONE'      

If you are going to SQL more often, do check the SFMC docs on the subject. Note that it's possible to find a lot of information about SQL in general on the web, but keep in mind that "SQL support for the SQL Query Activity is based on SQL Server 2005 capabilities," (cf. the SFMC's SQL reference page) so the exact syntax may vary with what you find online.

P.S.: I already requested access to the file you shared. Know that you can change the settings so that "Anyone with the link can view" (cf. . :)

Answer 3

You can use, where clause something like below

select * 
from [YourListName] 
with (nolock) 
where [Status] Not IN ('COMPLETED','DONE') OR [Account Stage]='NONE'

Answer 4

This may sound like a very low-tech solution, but couldn't you create a sendable filtered list from your master list with the criteria you've set as the filter?