Contents

Overview

๐Ÿ”บ PART A. CREATE A DATA EXTENSION

๐Ÿ”บ PART B. CONFIGURE AUTHENTICATION KEY & FILE LOCATION

  • ๐Ÿ”ธ Step 1: Create Access Key

  • ๐Ÿ”ธ Step 2: Configure File Location

๐Ÿ”บ PART C. SETTING UP THE AUTOMATION PIPELINES

๐Ÿ”น PART C1 - Automation Pipeline for Daily Updates

  • ๐Ÿ”ธ Step 1: Initiate Automation

  • ๐Ÿ”ธ Step 2: Retrieve Data (SQL QUERY)

  • ๐Ÿ”ธ Step 3: The Data Extract

  • ๐Ÿ”ธ Step 4: Setup File Transfer to Tinyclues

๐Ÿ”น PART C2 - Automation Pipeline for Historical Data

  • ๐Ÿ”ธ Step 1: Initiate Automation

  • ๐Ÿ”ธ Step 2: Retrieve Data (SQL QUERY)

  • ๐Ÿ”ธ Step 3: The Data Extract

  • ๐Ÿ”ธ Step 4: Setup File Transfer to Tinyclues

๐Ÿ”บ PART D: TEST THE OVERALL SETUP

Overview

In order to leverage your data to improve Tinyclues performance, we need your Salesforce Marketing Cloud account to automatically send your email logs data to Tinyclues.

โž” Upon setup, you need to configure an automation pipeline on your Salesforce instance with appropriate settings.

โž” For data history, you need to send a first batch of email logs data

โž” Then on a daily basis, your email campaigns are automatically sent to Tinyclues

These email logs are tracking interaction with your email marketing communication: for each email sent and each customer, it will contain attributes such as the sent / open / click / unsubscribe dates.

๐Ÿ”บ PART A. CREATE A DATA EXTENSION

As a first step, you need to create a Data Extension that will be used to store email logs.

1. Click Contact Builder in the Audience Builder tab.

2. Go to the Data Extensions tab.

3. Click Create at the top right.

4. Fill out the Data Extension as follows and click Next.

5. Toggle Off for the Retention Setting. (Tinyclues manages this on its own)

6. Fill out the form with the following fields and click Complete.

  • SubscriberKey

  • EmailName

  • EmailSendDefinition

  • ListID

  • SentDate

  • OpenDate

  • ClickDate

  • BounceDate

  • BounceCategory

  • UnsubscribeDate

Your Data Extension has now been completed.

7. Click the Data Extension you just created.

8. Copy the External Key. You will need it in Part B to set up Automation.

๐Ÿ”บ PART B. CONFIGURE AUTHENTICATION KEY & FILE LOCATION

Your Salesforce Marketing Cloud account must be able to access Tinyclues' sFTP to push files containing email logs.

๐Ÿ”ธ Step 1: Create an Access Key

  1. Go to Settings โ†’ Setup โ†’ Data Management โ†’ Key Management.

2. Click Key Management on the left side and then click on the Create button.

3. Fill out the form and click Save.

  • Key Type: SSH

  • Name: Tinyclues Key

  • Key: Your Private key generated with help of your Data Ops representative

Note: Please do not tick the โ€œPublic Keyโ€ checkbox.

Please note: if your key is not in SSH format (for ex: a .txt or .ppk file), you can convert it into SSH. Learn more in this article.

๐Ÿ”ธ Step 2: Configure File Location

  1. Go to Settings โ†’ Setup โ†’ App โ†’ File Location.

2. Click File Location on the left side and then click on the Create button.

3. Fill out the form and click Save.

  • Name: To Tinyclues

  • External Key: to_tinyclues

  • Location Type: External SFTP Site

  • URL: sftp2.tinyclues.com/data/to_tc

  • Port: 22

  • Username: Your Tinyclues domain name as provided by you Data Ops representative

  • Auth Type: Auth Key

  • Auth Keys: Select the one configured in Part A.

๐Ÿ”บ PART C. SETTING UP THE AUTOMATION PIPELINES

At this stage, you need to create the automation job that will automatically push data to the Data Extension created in PART A and send this data to Tinyclues using the File Location created in PART B for all historical data and daily updates.

๐Ÿ”น PART C1 - AUTOMATION PIPELINE FOR DAILY UPDATES

๐Ÿ”ธ Step 1: Initiate automation

1. From the Home page, go to Journey Builder > Automation Studio.

2. Click New Automation.

3. Name this Automation. For example, "Tinyclues Logs."

4. Drag and drop the Schedule icon into the Starting Source.

5. Click Configure.

6. Define the Schedule Settings as follows:

6. Click Done.

๐Ÿ”ธ Step 2: Retrieve Data (SQL Query)

Note that in order to get data in the proper schema, SQL activity (rather than just a Data Extract) needs to be used. It will allow you to output one single ready-to-be-used file that will be automatically sent to Tinyclues systems.

1. In the left panel, search for SQL query icon. Drag and drop into the automation section on the right.

Note: If you do not see the SQL query operator, you may need specific access

rights. Please ask your Salesforce Marketing Cloud account administrator.

2. Click Choose.

3. Create a New Query Activity.

4. Check the Query Activity properties and click Next.

5. Copy & paste the following query into the text field:

SELECT 
s.SubscriberKey,
j.EmailName,
j.EmailSendDefinition,
f.ListID,
s.EventDate as SentDate,
o.EventDate as OpenDate,
c.EventDate as ClickDate,
b.EventDate as BounceDate,
b.BounceCategory,
u.EventDate as UnsubscribeDate
FROM _Sent s
LEFT JOIN _Job as j ON s.JobID = j.JobID
LEFT JOIN _Ftaf as f ON s.JobID = f.JobID
LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
LEFT JOIN _Click c ON s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
LEFT JOIN _Bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1
LEFT JOIN _Unsubscribe u ON s.JobID = u.JobID and s.ListID = u.ListID and s.BatchID = u.BatchID and s.SubscriberID = u.SubscriberID and u.IsUnique = 1
WHERE s.EventDate >= DATEADD(day,-3,GETDATE())

6. Click Next and select the Data Extension you created in Part A.

Make sure you have selected the Overwrite option.

7. You should end up with a Summary that looks like this:

8. Click Finish. You will get a warning about "Overwrite Data Action Selected." You can ignore this. We want to overwrite the Data Extension and only keep the latest data on there to ensure the file stays light.

๐Ÿ”ธ Step 3: The Data Extract

1. In the left panel, search for the Data Extract icon. Drag and drop into the automation scheme on the right.

2. Click Choose.

3. Click Create New Data Extract Activity.

4. Fill in the following properties:

File Pattern:

<your_company_name>_Email_Logs_%%Year%%-%%Month%%-%%Day%%.csv

Example of filename that will be generated using this pattern: Tinyclues_Client_Name_Email_Logs_2020-02-12.csv

Copy the File Naming Pattern and keep it handy, you will need it in PART C Step 4.

5. Click Next and set the following configuration parameters in the DECustomer Key field, copy the Data Extensionโ€™s External Key generated in PART A.

6. Click on Next, then Finish, then Save.

This is what the final result should look like:

๐Ÿ”ธ Step 4: Set up file transfer to Tinyclues

1. In the left panel, search for the File Transfer icon. Drag and drop it into the automation section on the right.

2. Click Choose, then Create New File Transfer Activity.

3. Set the following properties:

4. Click Next and set the following configuration settings:

  • File Pattern: must be the one created in PART B Step 1.

  • Destination: must be the File Location you have set for Tinyclues during PART B Step 2.

5. Click on Next, then Finish, then Save.

๐Ÿ”น PART C2 - AUTOMATION PIPELINE FOR HISTORICAL DATA

๐Ÿ”ธ Step 1: Initiate automation

From the Home page, go to Journey Builder > Automation Studio.

2. Click New Automation.

3. Name this Automation. For example, "Tinyclues Historical Logs."

4. Click Done.

๐Ÿ”ธ Step 2: Retrieve Data (SQL Query)

Note that in order to get data in the proper schema, SQL activity (rather than just a Data Extract) needs to be used. It will allow you to output one single ready-to-be-used file that will be automatically sent to Tinyclues systems.

1. In the left panel, search for SQL query icon. Drag and drop into the automation section on the right.

Note: If you do not see the SQL query operator, you may need specific access

rights. Please ask your Salesforce Marketing Cloud account administrator.

2. Click Choose.

3. Create a New Query Activity.

4. Check the Query Activity properties and click Next.

5. Copy & paste the following query into the text field:

SELECT

s.SubscriberKey,

j.EmailName,

j.EmailSendDefinition,

f.ListID,

s.EventDate as SentDate,

o.EventDate as OpenDate,

c.EventDate as ClickDate,

b.EventDate as BounceDate,

b.BounceCategory,

u.EventDate as UnsubscribeDate

FROM _Sent s

LEFT JOIN _Job as j ON s.JobID = j.JobID

LEFT JOIN _Ftaf as f ON s.JobID = f.JobID

LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1

LEFT JOIN _Click c ON s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1

LEFT JOIN _Bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1

LEFT JOIN _Unsubscribe u ON s.JobID = u.JobID and s.ListID = u.ListID and s.BatchID = u.BatchID and s.SubscriberID = u.SubscriberID and u.IsUnique = 1

6. Click Next and select the Data Extension you created in Part A.

Make sure you have selected the Overwrite option.

7. You should end up with a Summary that looks like this:

8. Click Finish. You will get a warning about "Overwrite Data Action Selected." You can ignore this. We want to overwrite the Data Extension and only keep the latest data on there to ensure the file stays light.

๐Ÿ”ธ Step 3: The Data Extract

1. In the left panel, search for the Data Extract icon. Drag and drop into the automation scheme on the right.

2. Click Choose and New Data Extract Activity.

3. Fill in the following properties:

File Pattern:

<your_company_name>_Email_Logs_%%Year%%-%%Month%%-%%Day%%.csv

Example of filename that will be generated using this pattern: Tinyclues_Client_Name_Email_Logs_2020-02-12.csv

4. Copy the File Naming Pattern and keep it handy, you will need it in Step 3.

5. Click Next and set the following configuration parameters in the DECustomer Key field and copy the Data Extensionโ€™s External Key generated during PART A.

6. Click on Next, then Finish, then Save.

This is what the final result should look like:

๐Ÿ”ธ Step 4: Set up file transfer to Tinyclues

1. In the left panel, search for the File Transfer icon. Drag and drop it into the automation section on the right.

2. Click Choose, then Create New File Transfer Activity.

3. Set the following properties:

4. Click Next and set the following configuration settings:

  • File Pattern: must be the one created in PART B Step 1.

  • Destination: must be the File Location you have set for Tinyclues during PART B Step 2.

5. Click on Next, then Finish, then Save.

๐Ÿ”บ PART D - TEST THE OVERALL SETUP

This part aims at ensuring the entire setup is working and should be launched in coordination with your Tinyclues Data Ops representative.

1. Click Run Once on the left-hand side of the Automation Studio

If the Run Once button is greyed, click Save again.

2. Select each step then click Run.

3. Click Run Now.

4. Click on the Activity Tab.

5. After a few minutes, you should see the Activity in Success.

If you see an error, please verify each step. If you still encounter problems, please contact us.

6. Check with your DataOps representative that the files have been correctly received.

You should now have:

โœ… Created a Data Extension

โœ… Configured an Authentication Key & File Location

โœ… Set up Automatic Pipelines for Daily Updates

โœ… Set up Automatic Pipelines for Historical Data

โœ… Tested the overall setup

For support, please contact success [at] tinyclues [dot] com.

Did this answer your question?