Skip to main content

Salesforce Marketing Cloud

KORE Team
  • Updated

Overview

Salesforce Marketing Cloud (SFMC) provides businesses with professional-level email marketing software. Marketers can use email personalization to maintain a relationship with their customers beyond apps and websites.

Using this DWA integration, you can track KPIs, better understand how your customers are interacting with your brand, and tie marketing activities back to individual sale transactions and CRM accounts.

 

Setup

Several steps are required to enable this integration.

Phase one

1. Contact your KORE Success Manager. They will provide you with:

  • an OpenSSH key (file)
  • SFTP information
  • a passphrase

2. Open a support ticket with Salesforce and ask them to enable Data Extension Extracts for your SFMC instance. (This is required so we can create a Data Extract activity within an Automation to extract the records from a Data Extension.)

3. Create an installed package in your Marketing Cloud account. This will allow KORE to use the SFMC API to download data into DWA. When you reach the "Add Component" step in SFMC's instructions, select API Integration and choose the server-to-server type.

4. Provide the following scope privileges:

Scope Setting
Access Offline Access
Email Read
OTT Read
Push Read
SMS Read
Social Read
Web Read
Documents and images Read
Automations Read, Write, Execute
Journeys (none)
Audiences Read
List and subscribers Read, Write
Data Extensions Read, Write
File locations Read
Ticketing events Read
Subscriptions

Read

 

5. Using our secure information portal, provide these details to your KORE Success Manager:

  • Client ID
  • Client Secret
  • Authentication Base URI
  • REST Base URI
  • SOAP Base URI
  • MID

To find the MID, click on your organization's name in the header.

6. Open the SFMC setup page (this can be found by clicking your user icon in the header) and navigate to Administration > Data Management > Key Management.

Click the Create button and enter the following values:

  • Key Type: SSH
  • Name: KORE DWA Integration OpenSSH Key
  • External Key: kore_dwa_integration_openssh
  • Key: (Click Browse and select the OpenSSH Key file provided by KORE)
  • Passphrase: (Enter the passphrase provided by KORE)

Click Save when finished.

7. Navigate to Administration > Data Management > File Locations.

Click Create and enter the following values:

  • Name: KORE DWA Integration External SFTP
  • External Key: kore_dwa_int_ext_sftp
  • Description: KORE DWA Integration External SFTP Folder
  • Location Type: (Select "External SFTP Site")
  • URL: (Enter the Host URL included in the SFTP details KORE provided)
  • Port: 22
  • Username: (Enter the username included in the SFTP details KORE provided)
  • Auth Type: (Select "Auth Key")
  • Auth Keys: (Select "KORE DWA Integration OpenSSH Key" from the list)
  • Passphrase: (Enter the passphrase provided by KORE)

Click Save when finished.

Once the above steps are complete, KORE will use the API to create several items in SFMC which are required to export data to DWA:

  • Data Extensions
    • KORE_DWA_AllSubscriber_Curr
    • KORE_DWA_ListSubscriber_Curr
    • KORE_DWA_Sent_Event
  • Automations
    • AUT_KORE_DWA_Extract_All_Subscribers
    • AUT_KORE_DWA_Extract_List_Subscribers
    • AUT_KORE_DWA_Sent_Event_Data_View
  • SQL Query Activities
    • QRY_KORE_Extr_List_Subs
    • QRY_KORE_Extr_All_Subs
    • QRY_KORE_Sent_Event_Data_View

Due to limitations of the SFMC API, you must create additional Data Extract and File Transfer Activities and assign them to the Automations:

Phase two

Modify SFMC Automations for All Subscriber records:

  1. Create Data Extensions – this step will be performed by KORE DWA automatically via the API. This will need to be completed before the following steps can be completed. A KORE DWA team member will notify the client when this is done.

  2. Create Activities for ‘All Subscribers’ Automation:
    1. Navigate to ‘Journey Builder’ -> ‘Automation Studio’ from the main menu and then click ‘Activities’.



    2. The ‘SQL Query’ Activity that populates the Data Extension with the list of Subscribers will be created by the KORE DWA Team via the API

    3. Create the ‘Data Extract’ Activity to extract the records in the data extension into the SFMC Safehouse, which is a holding location before the file is exported to the external SFTP Folder.
      1. Create a new Activity like the previous step, except this time select ‘Data Extract’ Activity
      2. Fill in the Properties tab with the following values and click ‘Next’:
        - Name: DEXT_KORE_DWA_All_Subscribers
        - External Key: DEXT_KORE_DWA_All_Subscribers
        - File Naming Pattern: KORE_DWA_All_Subscribers_%%Year%%%%Month%%%%Day%%%%Hour%%%%Minute%%%%Second%%.csv
        - Description: Data Extension Extract for All Subscribers
        - Extract Type: Data Extension Extract
      3. Fill in the Configuration tab with the following values and click ‘Next’:
        - Column Delimiter: ,
        - DECustomer Key: KORE_DWA_AllSubscriber_Curr
        - Has Column Headers: Checked
        - Text Qualified: Checked
        - Uses Line Feed: Not Checked
      4. Review the summary and Click ‘Finish’

    4. Create the ‘File Transfer Activity to transfer the file created from the previous activity to the external KORE SFTP Folder.
      1. Create a new Activity like the previous step, except this time select ‘File Transfer’ Activity
      2. Fill in the Properties tab with the following values and click ‘Next’:
        - Name: FT_KORE_DWA_All_Subscribers
        - External Key: FT_KORE_DWA_All_Subscribers
        - File Action: Move a File From Safehouse
        - Description: Extract All Subscriber file to KORE DWA SFTP Folder
      3. Fill in the Configuration tab with the following values and click ‘Next’:
        - File Naming Pattern: KORE_DWA_All_Subscribers_%%Year%%%%Month%%%%Day%%%%Hour%%%%Minute%%%%Second%%.csv
        - Destination: KORE DWA Integration External SFTP
      4. Review the summary and Click ‘Finish’

  3. Modify the Automation for All Subscribers. The Automation is the job that will run all the activities created in the previous step. The Automation will be created by the KORE DWA Team via the API with the first ‘SQL Query’ activity added to it:
    1. Click on the ‘Overview’ tab in Automation Studio and click on the Automation named ‘AUT_KORE_DWA_Extract_All_Subscribers’
    2. Drag a ‘Data Extract’ activity into Step 2 and drag a ‘File Transfer’ activity into Step 3 (as shown below) and click ‘Save’


    3. If the ‘Starting Source’ is not already populated with a grey Schedule box, drag the green ‘Schedule’ icon from the top left into the blank circle under the ‘Starting Source’ column, just to the left of ‘Step 1’ and click ‘Save’


    4. The final step is to populate the blank Activity boxes dragged over in step ‘B’. Click ‘Choose’ on the blank ‘‘Data Extract’ activity box, select the activity called ‘DEXT_KORE_DWA_All_Subscribers’ and click ‘Done’, the ‘Data Extract’ box will now be populated.
    5. Do the same for the ‘File Transfer’ activity box:
      - For ‘File Transfer’, select activity ‘FT_KORE_DWA_All_Subscribers’ and click ‘Save’ and the Automation is now created successfully.

 

Modify SFMC Automations for List Subscriber records:

Please refer to screenshots in the previous section as the steps are exactly the same with only entered values differing.

  1. Create Data Extensions – this step will be performed by KORE DWA automatically via the API. This will need to be completed before the following steps can be completed. A KPRE DWA team member will notify the client when this is done.

  2. Create Activities for ‘List Subscribers’ Automation:
    1. Navigate to ‘Journey Builder’ -> ‘Automation Studio’ from the main menu and then click ‘Activities’.

    2. The ‘SQL Query’ Activity that populates the Data Extension with the list of List Subscribers will be created by the KORE DWA Team via the API

    3. Create the ‘Data Extract’ Activity to extract the records in the data extension into the SFMC Safehouse, which is a holding location before the file is exported to the external SFTP Folder.
      1. Create a new Activity like the previous step, except this time select ‘Data Extract’ Activity
      2. Fill in the Properties tab with the following values and click ‘Next’:
        - Name: DEXT_KORE_DWA_List_Subscribers
        - External Key: DEXT_KORE_DWA_List_Subscribers
        - File Naming Pattern: KORE_DWA_List_Subscribers_%%Year%%%%Month%%%%Day%%%%Hour%%%%Minute%%%%Second%%.csv
        - Description: Data Extension Extract for List Subscribers
        - Extract Type: Data Extension Extract
      3. Fill in the Configuration tab with the following values and click ‘Next’:
        - Column Delimiter: ,
        - DECustomer Key: KORE_DWA_ListSubscriber_Curr
        - Has Column Headers: Checked
        - Text Qualified: Checked
        - Uses Line Feed: Not Checked
      4. Review the summary and Click ‘Finish’
    4. Create the ‘File Transfer Activity to transfer the file created from the previous activity to the external KORE SFTP Folder.
      1. Create a new Activity like the previous step, except this time select ‘File Transfer’ Activity
      2. Fill in the Properties tab with the following values and click ‘Next’:
        - Name: FT_KORE_DWA_List_Subscribers
        - External Key: FT_KORE_DWA_List_Subscribers
        - File Action: Move a File From Safehouse
        - Description: Extract List Subscriber file to KORE DWA SFTP Folder
      3. Fill in the Configuration tab with the following values and click ‘Next’:
        - File Naming Pattern: KORE_DWA_List_Subscribers_%%Year%%%%Month%%%%Day%%%%Hour%%%%Minute%%%%Second%%.csv
        - Destination: KORE DWA Integration External SFTP
      4. Review the summary and Click ‘Finish’
  3. Modify the Automation for List Subscribers. The Automation is the job that will run all the activities created in the previous step. The Automation will be created by the KORE DWA Team via the API with the first ‘SQL Query’ activity added to it
    1. Click on the ‘Overview’ tab in Automation Studio and click on the Automation named ‘AUT_KORE_DWA_Extract_List_Subscribers’
    2. Drag a ‘Data Extract’ activity into Step 2 and drag a ‘File Transfer’ activity into Step 3 and click ‘Save’
    3. If the ‘Starting Source’ is not already populated with a grey Schedule box, drag the green ‘Schedule’ icon from the top left into the blank circle under the ‘Starting Source’ column, just to the left of ‘Step 1’ and click ‘Save’
    4. The final step is to populate the blank Activity boxes dragged over in step ‘B’. Click ‘Choose’ on the blank ‘Data Extract’ activity box, select the activity called ‘DEXT_KORE_DWA_List_Subscribers’ and click ‘Done’, the ‘Data Extract’ box will now be populated.
    5. Do the same for the ‘File Transfer’ activity box:
      - For ‘File Transfer’, select activity ‘FT_KORE_DWA_List_Subscribers’ and click ‘Save’ and the Automation is now created successfully.

Modify SFMC Automations for Sent Event Data View records:

Please refer to screenshots in the previous section as the steps are exactly the same with only entered values differing.

  1. Create Data Extensions – this step will be performed by KORE DWA automatically via the API. This will need to be completed before the following steps can be completed. A KORE DWA team member will notify the client when this is done.

  2. Create Activities for ‘Sent Event Data View’ Automation:
    1. Navigate to ‘Journey Builder’ -> ‘Automation Studio’ from the main menu and then click ‘Activities’.

    2. The ‘SQL Query’ Activity that populates the Data Extension with the list of Sent Events will be created by the KORE DWA Team via the API

    3. Create the ‘Data Extract’ Activity to extract the records in the data extension into the SFMC Safehouse, which is a holding location before the file is exported to the external SFTP Folder.
      1. Create a new Activity like the previous step, except this time select ‘Data Extract’ Activity
      2. Fill in the Properties tab with the following values and click ‘Next’:
        - Name: DEXT_KORE_DWA_Sent_Event
        - External Key: DEXT_KORE_DWA_Sent_Event
        - File Naming Pattern: sent_event_v2_%%Year%%%%Month%%%%Day%%%%Hour%%%%Minute%%%%Second%%.csv
        - Description: Data Extension Extract for Sent Events
        - Extract Type: Data Extension Extract
      3. Fill in the Configuration tab with the following values and click ‘Next’:
        - Column Delimiter: ,
        - DECustomer Key: KORE_DWA_Sent_Event
        - Has Column Headers: Checked
        - Text Qualified: Checked
        - Uses Line Feed: Not Checked
      4. Review the summary and Click ‘Finish’
    4. Create the ‘File Transfer Activity to transfer the file created from the previous activity to the external KORE SFTP Folder.
      1. Create a new Activity like the previous step, except this time select ‘File Transfer’ Activity
      2. Fill in the Properties tab with the following values and click ‘Next’:
        - Name: FT_KORE_DWA_Sent_Event
        - External Key: FT_KORE_DWA_Sent_Event
        - File Action: Move a File From Safehouse
        - Description: Extract Extract Sent Event file to KORE DWA SFTP Folder
      3. Fill in the Configuration tab with the following values and click ‘Next’:
        - File Naming Pattern: sent_event_v2_%%Year%%%%Month%%%%Day%%%%Hour%%%%Minute%%%%Second%%.csv
        - Destination: KORE DWA Integration External SFTP
      4. Review the summary and Click ‘Finish’
  3. Modify the Automation for Sent Event Data. The Automation is the job that will run all the activities created in the previous step. The Automation will be created by the KORE DWA Team via the API with the first ‘SQL Query’ activity added to it
    1. Click on the ‘Overview’ tab in Automation Studio and click on the Automation named ‘AUT_KORE_DWA_Sent_Event_Data_View’
    2. Drag a ‘Data Extract’ activity into Step 2 and drag a ‘File Transfer’ activity into Step 3 and click ‘Save’
    3. If the ‘Starting Source’ is not already populated with a grey Schedule box, drag the green ‘Schedule’ icon from the top left into the blank circle under the ‘Starting Source’ column, just to the left of ‘Step 1’ and click ‘Save’
    4. The final step is to populate the blank Activity boxes dragged over in step ‘B’. Click ‘Choose’ on the blank ‘Data Extract’ activity box, select the activity called ‘DEXT_KORE_DWA_Sent_Event’ and click ‘Done’, the ‘Data Extract’ box will now be populated.
    5. Do the same for the ‘File Transfer’ activity box:
      - For ‘File Transfer’, select activity ‘FT_KORE_DWA_Sent_Event’ and click ‘Save’ and the Automation is now created successfully.

ERD and data dictionary

(This information is provided on a best-effort basis without guarantees. For clarity, some column names may be omitted from this ERD.)

Download data dictionary (Excel file)

Table descriptions

Category(Folders)
The Category table contains assets in any folder known by your users, or move an existing asset into a folder. The asset stores the folder information in an attribute called category.

Email
The Email table represents an email in Marketing Cloud. This table contains assets of the old way to create email templates in Marketing Cloud.

Template based email
The Email table represents an email in Marketing Cloud. This table contains assets of the new way to create email templates in Marketing Cloud. The end user can create email templates by using the drag and drop features.

Html email
The Email table represents an email in Marketing Cloud. This table contains assets of the new way to create email templates in Marketing Cloud. The end user can create email templates by using a pre-built HTML script.

Send
The Send table represents a middle table between the email template tables to the events table. It contains metadata information about email templates as well as aggregation fields.

List
The List object is a marketing list of subscribers.

List subscriber
The List Subscriber table is a bridge table between the List and Subscribers table

Subscriber
The Subscriber table is a single instance of a person subscribed to receive email or SMS communication. We have created a field named 'team_subscriber' with flag 'Flase' and 'True' to differentiate the subscribers that are already deleted and existing active ones. 
If the team_subscriber = 'Flase' then the subscribers are deleted in marketing cloud.
If the team_subscriber = 'true' then the subscribers are active in marketing cloud.

Click event
The Click Event table contains time and date information, as well as a URL ID and a URL, regarding a click on a link contained in a message.

Bounce event
The Bounce Event Table contains SMTP and other information pertaining to the specific event of an email message bounce.

Open event
The Open Event object contains information about the opening of a message send by a subscriber.

Unsub event
he Unsub Event object contains information regarding a specific unsubscribe action taken by a subscriber.

Link send
The Link Send table provides information about a link in a send.

Triggered send
The Triggered Send table represents a specific instance of a triggered email send.

Campaign
The Campaign table contains information about a campaign in Marketing Cloud. Use this object to perform discrete tasks as part of an automated process

 

Example queries

Get a list of email templates and end user actions totals:

SELECT 
	e.id
	, e.name
	, s.sent_date
	, e.legacy_data_legacy_id
	, s.id AS send_id
	, s.number_sent
	, sent_event.event_sends
	, s.unique_opens
	, open_event.event_opens
	, s.number_delivered
	, s.sent_date
	, s.unique_opens
	, s.unique_clicks
	, s.unsubscribes
	, s.hard_bounces
	, s.soft_bounces
	, s.other_bounces

FROM marketing_cloud.template_based_email e					
/*	
There are mainly 3 main template tables:
--	email 					- use email.id to join marketing_cloud.send
--	template_based_email 	- use legacy_data_legacy_id to join marketing_cloud.send
--	html_email 				- use legacy_data_legacy_id to join marketing_cloud.send
*/
	JOIN marketing_cloud.send s
		ON e.legacy_data_legacy_id = s.email_id
	LEFT JOIN
		(SELECT send_id, count(*) AS event_sends
		FROM marketing_cloud.sent_event
		GROUP BY send_id) sent_event
		ON s.id = sent_event.send_id
		
	LEFT JOIN
		(SELECT send_id, count(*) AS event_opens
		FROM marketing_cloud.unsub_event
		GROUP BY send_id) unsub_event
		ON s.id = unsub_event.send_id
		
	LEFT JOIN
		(SELECT send_id, count(distinct subscriber_key) AS event_opens
		FROM marketing_cloud.open_event
		GROUP BY send_id) open_event
		ON s.id = open_event.send_id
		
ORDER BY s.sent_date DESC;

List email templates of the second subfolder structure level:

select distinct
	  c.description
	, c.id
	, c.name "Folder level 0"
	, c1.name "Folder level 1"
	, c2.name "Folder level 2"
	, tbe_2.name "Template Name Leve 2"
from marketing_cloud.category c 

	left join marketing_cloud.category c1
		on c.id = c1.parent_id 
			
	left join marketing_cloud.category c2
		on c1.id = c2.parent_id 

		left join marketing_cloud.template_based_email tbe_2
			on c2.id = tbe_2.category_id 		

WHERE tbe_2.name is not null			
;

Show subscriber emails and all lists:

SELECT DISTINCT
  l.id as list_id
, l.list_name
, l.category as list_category
, l.type as list_type
, l.description list_description
, l.list_classification 

, ls.status as list_subscriber_status
, ls.list_type
, ls.add_method
, ls.subscriber_type

, sb.subscriber_key
, sb.email_address
, sb.status as subscriber_status
, sb.first_name
, sb.middle_initial
, sb.last_name
, sb.title
, sb.address_1
, sb.address_2
, sb.city
, sb.state
, sb.country
, sb.zip_code
, sb.mobile_phone
, sb.team_subscriber

FROM marketing_cloud.list l 

	INNER JOIN marketing_cloud.list_subscriber ls
		ON l.id = ls.list_id

	INNER JOIN marketing_cloud.subscriber sb
		ON ls.subscriber_id = sb.subscriber_id

WHERE l.id = '18725610';

 

KORE Schema

As part of this integration, KORE will model your SFMC data in our standard data model (KORE schema) for summary and reporting tables and views. These summary tables and views are designed to provide a more user friendly data structure and have been optimized to power reporting and analytics.

 

Reporting

As part of this integration, KORE will provide you with our standard email marketing reporting package. These reports are built in Tableau and can be accessed via your site on Tableau Server.