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
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
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.