如何在Marketing Cloud中对List执行Lookup操作？

1. 使用SQL Query查询List数据 #

SFMC的List基础数据存在系统数据视图List中，订阅者与List的关联关系存在ListSubscribers里，你可以在Automation Studio或Query Studio中通过SQL查询：

• 通过LIST ID查询List基本信息

SELECT ListID, ListName, ExternalKey, Description
FROM List
WHERE ListID = 12345 -- 替换为你的目标LIST ID

• 通过外部键查询List下的订阅者数据

SELECT s.SubscriberKey, s.EmailAddress, s.Status
FROM ListSubscribers ls
JOIN Subscribers s ON ls.SubscriberID = s.SubscriberID
WHERE ls.ListID = (SELECT ListID FROM List WHERE ExternalKey = 'YOUR_LIST_EXTERNAL_KEY') -- 替换为你的List外部键

注意：运行前确认你有对应数据视图的访问权限，查询结果可存入数据扩展供后续使用。

2. 使用AMPscript实时查询 #

如果需要在邮件、Cloud Page等场景动态获取List数据，用AMPscript的Lookup/LookupRows函数就能实现：

• 获取单个List的基础信息

/* 替换为你的LIST ID */
SET @targetListID = 12345
SET @listDetails = Lookup("List", "ListName,ExternalKey,Description", "ListID", @targetListID)

/* 提取具体字段值 */
SET @listName = Field(@listDetails, "ListName")
SET @listExternalKey = Field(@listDetails, "ExternalKey")

• 获取List下的所有订阅者

SET @listExternalKey = "YOUR_LIST_EXTERNAL_KEY"
/* 先通过外部键获取对应的ListID */
SET @targetListID = Lookup("List", "ListID", "ExternalKey", @listExternalKey)
/* 查询该List下的订阅者关联记录 */
SET @listSubscribers = LookupRows("ListSubscribers", "ListID", @targetListID)

IF RowCount(@listSubscribers) > 0 THEN
  FOR @i = 1 TO RowCount(@listSubscribers) DO
    SET @subRow = Row(@listSubscribers, @i)
    SET @subscriberID = Field(@subRow, "SubscriberID")
    /* 如需更多订阅者信息，可关联Subscribers表查询 */
    SET @subscriberInfo = Lookup("Subscribers", "EmailAddress,SubscriberKey", "SubscriberID", @subscriberID)
  NEXT @i
ENDIF

3. 使用REST API查询（外部系统集成场景） #

如果要在外部系统中获取SFMC的List数据，可通过REST API实现：

1. 先通过OAuth 2.0认证获取访问令牌
2. 通过ListID查询List信息

GET /hub/v1/lists/{your-list-id}
Host: your-subdomain.rest.marketingcloudapis.com
Authorization: Bearer your-access-token

3. 通过外部键查询List信息

GET /hub/v1/lists?$filter=externalKey%20eq%20'your-list-external-key'
Host: your-subdomain.rest.marketingcloudapis.com
Authorization: Bearer your-access-token

4. 查询指定List下的订阅者

GET /hub/v1/lists/{your-list-id}/subscribers
Host: your-subdomain.rest.marketingcloudapis.com
Authorization: Bearer your-access-token

内容的提问来源于stack exchange，提问作者Marketing cloud Geek