1

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 "get_the_email@outlook.com" as...

should_not_receive@hotmail.com - has an oppt with DONE

should_not_receive_2@gmail.com - has an oppt with Account Stage of "NONE"

Hope this makes sense!

  • 1
    You have to request permission to see the link you provided. Maybe you should rather make it viewable without permissions. – Johannes Schapdick Sep 26 '18 at 5:49
  • hey Johannes, thanks for that, have made it shareable and replied to your comment – barbera Sep 26 '18 at 6:14
3

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.
0

You can use, where clause something like below

select * 
from [YourListName] 
with (nolock) 
where [Status] Not IN ('COMPLETED','DONE') OR [Account Stage]='NONE'
  • This will update a dataextension where the values are not present, but it does not delete the values from the list. And since you cannot manipulate lists with SQL (afaik) this is not what the user expects. – Johannes Schapdick Sep 26 '18 at 6:14
0

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?

0

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. . :)

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.