0

I have a group of email addresses I'd like to see which lists they're currently subscribed to, they're in "DE Name".

I've tried the following SQL but the result is several duplicate rows and all status values = active. I have 10 lists/publication lists I'd like to add, but to keep things simple, I'm only showing 2.

The from and resulting data extensions are not sendable and don't use a Primary Key field.

select
a.EmailAddress,
a.FirstName,
a.LastName,
b.Status as ListName1,
c.Status as ListName2

from [DE Name] as a
join _ListSubscribers as b on a.EmailAddress = b.EmailAddress
join _ListSubscribers as c on a.EmailAddress = c.EmailAddress

where (b.listID = #######
or c.listID = #######)
  • I am assuming that you want multiple rows for each subscriber? I would conquer it like this. select a.EmailAddress, a.FirstName, a.LastName, b.Status as ListName from [DE Name] as a inner join _ListSubscribers as b on a.EmailAddress = b.EmailAddress where b.listid in ('#######','#######') The where contains your 10 List – Johannes Schapdick Oct 19 '18 at 4:30
1

In your output data extension, creat the necessary fields as below and make EmailAddress and ListName primary keys.

The below SQL should list you all subscribers in your DE Name alongside the ListName and their status in that List

select 
a.EmailAddress, 
a.FirstName, 
a.LastName,
l.ListId,
l.ListName,
l.Status

from [DE Name] as a 
Left join _ListSubscribers as l on a.EmailAddress = l.EmailAddress

You could then perform a filter to find those contacts with the subscription to the relevant list.

If you wanted to have a single contact record, and a pivot of lists they are subscribed to; then the approach I would take is to store the list names in a concatenated string (single field - text unlimited) called ListNames. You would otherwise need to create a data extension field for each/every list you create and this would get unmanageable.

e.g. johndoe@abc.com | John | Doe | ListA,ListC,ListD

Below is example code - you might need to play about with it. Basically you are wanting to use the XML path to pivot the results into a string

SELECT 
    a.EmailAddress, 
    a.FirstName, 
    a.LastName,
    SUBSTRING(
       (SELECT ',' + ListNames.ListName
        FROM _ListSubscribers 
        INNER JOIN [DE Name] ON on a.EmailAddress = l.EmailAddress
        FOR XML PATH('')),2,200000) AS ListNames 
   FROM   [De Name]
   INNER JOIN _ListSubscribers ON a.EmailAddress = l.EmailAddress                
   WHERE  ( ListNames.Status = 'Active' ) 
   GROUP  BY [DE Name].EmailAddress
  • This worked but it created a new row for every list the subscriber was on. Is it possible to have a single row per subscriber, with the status of every list in a new column? The output data extension has a column for each list available in the account. Thanks for your help! – Kyle Szabo Oct 19 '18 at 20:47

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.