How can I bulk search Subscriber Keys for imported lists to Marketing Cloud?

Question

I work at an organization that frequently hosts webinars. We use marketing cloud to send generic, full subscriber list emails promoting these events. But we also use it to send follow-up emails to those who attended the events.

We often need to create a specialized list of registrants to a program, which I can pull from our website into a .csv file. However, to import it as a list into marketing cloud, it requires 1) an email address and 2) subscriber key. My issue is that I cannot figure out how to search multiple subscriber keys at once. I know how to search one at a time in the "all subscribers" section, but that becomes incredibly tedious when we have lists of 200 to 400 people.

So my problem is these registrants are part of our all subscribers, but sometimes I want to send to a more specific list. However, to send to such a list, I need to find all of their subscriber keys. How can I do this without searching one at a time?

Answer 1

There are multiple ways to solve for this in SFMC; depends on your setup and data pipelines. Depending on how your data is managed, the users might or might not have a Subscriber Key or Salesforce ID

It sounds like you have a Webinar list, let's call it webinar.csv with a list of email addresses:

webinarE[email protected] [email protected] [email protected]

In my experience, you can match against Salesforce ID (if they exist in your Salesforce CRM) or use Email Address as the Subscriber Key.

1. You can use the _ListSubscribers Data View list in Marketing Cloud and run an automation with the SQL query below. (Sorry in advance, not sure why the SQL isn't formatting)

SELECT 
a.EmailAddress,
b.SubscriberKey 
FROM [WebinarList] AS a
INNER JOIN _ListSubscribers AS b 
ON a.EmailAddress = b.EmailAddress

This should returned all of the matching SubscriberKey's/SalesforceID's for the email addresses in your [WebinarList] Data Extension.

2. For the emails that do not get returned, use EmailAddress as their SubscriberKey. Not ideal, but you manage it the best you can.

In the end you might end up with a customer list like the one below, where the first two records do have a match to SubscriberKey and the last does not.

SubscriberKey,EmailAddress
003xxxxxxxxxx,[email protected]
003xxxxxxxxxx,webinarEmai[email protected] 
[email protected],[email protected]

I've a history with managing lists in and out of Salesforce for 100+ million customers, it's not clean. Got to find the "best" solution at the time.

Answer 2

I would create a data extension and import the webinar .csv file into that data extension.

You can read the reference here to know why the data extension type is more flexible and has more advantages.

For example, this is the way I would do:

1. Create a DE call "webinar collection" and it has a field call "Email" and import the webinar .csv file into that DE.
2. Create a DE call "attendee list", this DE includes fields SubscriberKey and EmailAddress and this one will be used for your send later
3. Write a SQL query to get information from "webinar collection" DE and _Subscribers system data view to fill into the targeted DE "attendee list".

E.g. This is the SQL query in a MC child BU:

SELECT
c.SubscriberKey,
c.EmailAddress
FROM

(SELECT
a.SubscriberKey,
a.EmailAddress,
ROW_NUMBER() OVER (PARTITION BY a.EmailAddress ORDER BY a.SubscriberKey ASC) AS row_num
FROM
ENT._Subscribers AS a
INNER JOIN
[webinar collection] AS b
ON a.EmailAddress = b.Email
WHERE
a.Status = 'Active') AS c

WHERE c.row_num = 1

Basically, this SQL query will get the first row in the _Subscribers system data view matching with an email address in the "webinar collection" DE so we can avoid sending to an email address multiple times if there are multiple subscribers with the same email address in MC system.