0

I need to extract data from Salesforce Marketing Cloud. I'm using KingswaySoft SSIS Integration Toolkit to do the ETL, but I don't know the tables that retrieve the columns I need. I've searched on the documentation, but I wasn't able to identify the proper columns.

This is how my connection manager to the source SF Marketing Cloud looks like: SF Marketing Cloud Connection Manager

KingswaySoft SSIS Integration Toolkit - Salesforce Marketing Cloud Source Editor:

KingswaySoft SSIS Integration Toolkit - Salesforce Marketing Cloud Source Editor

These are the columns I need:

  • Journey Name
  • Journey Activity Name
  • Email Sends
  • Email Delivery Rate
  • Email Bounce Rate
  • Email Open Rate
  • Email Click To Open Rate
  • Email Unique Opens
  • Email Click Rate
  • Email Unique Clicks
  • Email Unsubscribe Rate
  • Email Unique Unsubscribes
  • Subscriber_Key
  • timestamp
  • Birthdate
  • CouponCode
  • ExitDate
  • IsRedeemed
  • ClaimedDate
  • ExpirationDate
  • EngagementPhase
  • SentEngagement1
  • SentEngagement2
  • SentEngagement3
  • SentEngagement4
  • OpenedEngagement1
  • OpenedEngagement2
  • OpenedEngagement3
  • OpenedEngagement4
  • ClickedEngagement1
  • ClickedEngagement2
  • ClickedEngagement3
  • ClickedEngagement4

To extract from Salesforce Service Cloud, I've used a Chrome extension "Salesforce Inspector" where I could search the column name and would obtain the source table, but it doesn't seem to work for SF marketing cloud.

1 Answer 1

1

The system tables in Marketing Cloud are called Data views, they hold some information that you have listed, so eg. “Journey Activity Name” from your list might refer to the _JourneyActivity table.

This diagram shows all available Data Views along with column names:

enter image description here

Please refer to this article and documentation for details such as data types etc.

Some additional information can also be pulled using Tracking Extracts in Automation Studio and stored on an FTP, but not sure if this is what you are using:

Tracking extracts provide granular tracking data for import from Email Studio into external systems. Use tracking extracts to export granular data regarding several different aspects of email send jobs, such as clicks, bounces, and survey data, from Marketing Cloud. Then, import that information into an automation or system.

For details see here: https://help.salesforce.com/apex/HTViewHelpDoc?id=sf.mc_as_tracking_extract.htm&language=en_US

It’s also possible that your integration uses SOAP objects, which are listed here: https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/soap_web_service_objects.html

It seems, that some of the tables you have listed are custom tables (called Data Extensions) - for those, you will need to log into Marketing Cloud to get their names and details, as they are specific to your account.

14
  • I'm using KingswaySoft SSIS Integration Toolkit - Salesforce Marketing Cloud Source Editor to extract the data, but I don't seem to be able to access the DataViews that you mention.
    – TiagoD
    Commented Jan 17, 2023 at 15:09
  • I've added a print of some of the tables I can access, using this dataflow component from KingswaySoft SSIS Integration Toolkit - Salesforce Marketing Cloud Source Editor
    – TiagoD
    Commented Jan 17, 2023 at 15:10
  • They might be SOAP objects, I’ve added a link to my answer
    – zuzannamj
    Commented Jan 17, 2023 at 15:23
  • yes it seems that my integration uses SOAP objects, but I don't seem to find the tables I need on those SOAP objects. Can you give me the SOAP table that contains some of the columns I need to extract?
    – TiagoD
    Commented Jan 17, 2023 at 15:28
  • The link I provided lists all the SOAP objects available and their properties, there aren’t any other and there are no SOAP tables - if you’re sure that there are some tables out there that have the data you need, please reach out to someone that has access to your Marketing Cloud instance and ask them to give you a list of all Data Extension in the account
    – zuzannamj
    Commented Jan 17, 2023 at 15:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .