如何用FuelSDK将Salesforce Marketing Cloud事件导入DataFrame？

问题背景 #

使用Salesforce FuelSDK从Marketing Cloud拉取审计事件（点击、退订、 bounce等）时，调用get()能获得成功响应，但直接将响应对象转换为DataFrame无效，需要正确提取事件数据并生成对应DataFrame。

用户原代码：

import FuelSDK
import ET_Client
import pandas as pd
import requests

#define local variables 
clientid= 'client_id_code_here'
clientsecret='client_secret_code_here' 
subdomain = 'mcx1k2thcht5qzdsm6962p8ln2c8'
auth_base_url = f'https://{subdomain}.auth.marketingcloudapis.com/'
rest_url = f'https://{subdomain}.rest.marketingcloudapis.com/'
soap_url=f'https://{subdomain}.soap.marketingcloudapis.com/'

#Passing config as a parameter to ET_Client constructor:
myClient = FuelSDK.ET_Client(True, False,
{'clientid': clientid, 
'clientsecret': clientsecret,
'useOAuth2Authentication': 'True',
'authenticationurl': auth_base_url,
'applicationType': 'server',
'baseapiurl': rest_url,
'soapendpoint': soap_url})

# Create an instance of the object type we want to work with:
list = FuelSDK.ET_List()

# Associate the ET_Client- object using the auth_stub property:
list.auth_stub = myClient

# Utilize one of the ET_List methods:
response = list.get()

# Print out the results for viewing
print('Post Status: ' + str(response.status))
print('Code: ' + str(response.code))
print('Message: ' + str(response.message))
print('Result Count: ' + str(len(response.results)))
# print('Results: ' + str(response.results))

debug = False
# stubObj = ET_Client(False, False, params={'clientid': clientid,
#                                                   'clientsecret': clientsecret})

getBounceEvent = ET_Client.ET_BounceEvent()
getBounceEvent.auth_stub = myClient
getBounceEvent.props = ["SendID","SubscriberKey","EventDate","Client.ID","EventType","BatchID","TriggeredSendDefinitionObjectID","PartnerKey"]
# getBounceEvent.search_filter = {'Property' : 'EventDate', 'SimpleOperator' : 'greaterThan', 'DateValue' : retrieveDate}
getResponse = getBounceEvent.get()

print(getResponse)

#pull data into dataframe
df=pd.DataFrame(getResponse)

df.head()

#load data into csv data file
df.to_csv('salesforce_bounce_events.csv', index=False)

解决方案 #

FuelSDK的get()返回的响应对象中，实际事件数据存储在results属性里，每个元素是对应事件类型的实例。需要将这些实例转换为字典列表后，再生成DataFrame。

步骤1：提取事件数据 #

遍历响应的results列表，将每个事件实例转换为包含指定字段的字典。可以利用用户预先定义的props字段来精准提取数据：

# 提取指定字段的事件数据
columns = getBounceEvent.props
event_data = []
for event in getResponse.results:
    # 逐个提取props中定义的字段值
    row = {}
    for col in columns:
        # 处理嵌套字段（如Client.ID）
        if '.' in col:
            parent_attr, child_attr = col.split('.')
            parent_obj = getattr(event, parent_attr)
            row[col] = getattr(parent_obj, child_attr) if parent_obj else None
        else:
            row[col] = getattr(event, col, None)
    event_data.append(row)

步骤2：生成DataFrame并导出 #

将字典列表传入pd.DataFrame()生成DataFrame，再导出为CSV：

# 转换为DataFrame
df = pd.DataFrame(event_data)
# 查看前几行数据
print(df.head())
# 导出到CSV
df.to_csv('salesforce_bounce_events.csv', index=False)

通用化处理不同事件类型 #

可以封装一个函数来处理不同的审计事件类型（点击、退订等）：

def fetch_event_data(client, event_class, props, filter=None):
    """
    拉取指定类型的Marketing Cloud事件数据并返回DataFrame
    :param client: ET_Client实例
    :param event_class: 事件类（如FuelSDK.ET_BounceEvent、FuelSDK.ET_ClickEvent）
    :param props: 需要提取的字段列表
    :param filter: 搜索过滤条件（可选）
    :return: 事件数据的DataFrame
    """
    event_obj = event_class()
    event_obj.auth_stub = client
    event_obj.props = props
    if filter:
        event_obj.search_filter = filter
    response = event_obj.get()
    
    if not response.status:
        print(f"拉取失败: {response.message}")
        return pd.DataFrame()
    
    # 提取数据
    event_data = []
    for event in response.results:
        row = {}
        for col in props:
            if '.' in col:
                parent_attr, child_attr = col.split('.')
                parent_obj = getattr(event, parent_attr)
                row[col] = getattr(parent_obj, child_attr) if parent_obj else None
            else:
                row[col] = getattr(event, col, None)
        event_data.append(row)
    
    return pd.DataFrame(event_data)

# 示例：拉取点击事件
click_props = ["SendID", "SubscriberKey", "EventDate", "URL", "LinkName"]
click_df = fetch_event_data(myClient, FuelSDK.ET_ClickEvent, click_props)
click_df.to_csv('salesforce_click_events.csv', index=False)

# 示例：拉取退订事件
unsub_props = ["SendID", "SubscriberKey", "EventDate", "Reason", "ListID"]
unsub_df = fetch_event_data(myClient, FuelSDK.ET_UnsubEvent, unsub_props)
unsub_df.to_csv('salesforce_unsub_events.csv', index=False)

关键说明 #

• response.results：存储实际事件数据的列表，每个元素是事件类的实例
• getattr()：安全获取对象的属性值，避免因属性不存在导致报错
• 嵌套字段处理：对于类似Client.ID的嵌套属性，需要先获取父对象再提取子属性

内容的提问来源于stack exchange，提问作者lengthy_preamble