Engagement - Best Practices for Exporting and Retrieving Data (SQL, API, Extract, Reports)

Quick Reference

SQL Query Activity

• Query Activities allow for data to be retrieved from Data Extensions as well as Data Views within the Marketing Cloud Engagement.
• Data Views can be used to gather sending metrics or subscriber actions within the past 6 months, such as Clicks, Sends, Open, Unsubscribes, etc.
• Query Activities are not able to export data from Data Extensions or Data Views, however they do allow clients the flexibility to blend information from multiple Data Extension/Data View sources and write to a single Data Extension. This Data Extension then can be extracted via a Data Extract to the FTP or exported manually.

⚠️ Data View Retention Limit: 6 Months

All Data Views only allow for the previous 6 months of data to be returned. This is standard for all Marketing Cloud Engagement tenants and is not configurable. This limit is to provide optimum performance while running Query Activities. For any data needs beyond 6 months from now, please utilize Data Extracts, such as the Tracking Extract.

• Strengths:
  • When used in limited joins and efficient SQL, Data Views allow for easy access to timely tracking data that can then be leveraged by other processes in the Marketing Cloud Engagement.
  • Flexibility and customization when it comes to manipulating data and populating new Data Extensions.

• Weaknesses:
  • Timeouts
    • Query Activities are subject to a 30min timeout.
    • If multiple processes (Imports, Sends, Filters, other Query Activities, etc) are using Data Extensions all at once, performance is slowed and more likely to cause a SQL timeout.
    • Complex logic and many joined tables increase the likelihood of a timeout.
• Resources
  • Optimize SQL Query Activity Performance in Marketing Cloud Engagement
  • Use SQL Query Activity
  • Use Intermediate Tables to Optimize a Query

API

The Soap and Rest API services provide access to different data within the Marketing Cloud Engagement. To read more, check out the Intro to Marketing Cloud Engagement APIs Developer Document.

• Rest API
  • Certain customer interactions are able to be logged and retrieved using the Event Notification Service. Use cases include password resets, get order confirmations, log in using two-factor authentication, and other events.
  • Tracking information as you would see in the Data Views and Tracking Extracts is not accessible through Rest API.
    • Event Notification Service (ENS)
    • Getting Started w/ ENS

• Soap API
  • It is important to use the proper tool for getting data out of the Marketing Cloud Engagement. For example, it is possible for a developer to build and API process which could retrieve all Opens for an account within the last 6 months. If your account sends heavily to large audiences, there may be hundreds of thousands of records returned through this process. The API calls to perform this is slow and cumbersome on other processes in the account. Although the API may be able to complete the task eventually, a Data Extract or SQL Query Activity would be able to complete it faster and are the proper tools for the job. The API may be an appropriate tool, depending on volume, to perform rolling updates after the initial historical load is completed via an Extract.
  • Technical examples are available through the Technical Articles section of the Soap Web Service Guide.
  • Soap Objects contain tracking information tied to email sends, administration details pertaining to processes or users in an account, and more. Most of these objects are retrievable through Soap. For a complete list of Soap objects please review the ‘Objects’ sub-tab under the Soap Web Service Guide. For a breakdown of which of those objects can be retrieved, review the Supported Operation for Object and Methods page.
• Strengths:
  • Ability to gather data programmatically.
  • Stream updates continuously.
• Weaknesses:
  • Not ideal for large data pulls or historical load type uses.
  • Customer side and Salesforce side timeouts can occur when used for large sets of data.

Email Studio - My Tracking

• Through the Marketing Cloud Engagement UI, aggregate tracking information can be exported so a user can understand things such as total sends, opens, clicks, forwards, etc. The granular subscriberKeys which were sent to can also be exported from the My Tracking view.
• Strengths:
  • Quick access to high level send metrics.
• Weaknesses:
  • Manual retrieval that cannot be automated.
  • Limited data available. 
• Resources:
  • View Tracking Results for an Email Send
  • Understand Timing and Tracking

Analytics Builder

Two types of reports included: Standard Reports and Intelligence Reports. These reports are useful for collecting aggregate level data, not subscriber specific. These reports can be downloaded to the FTP folder or emailed, and several can be viewed directly in the UI. They can also be saved as report definitions and scheduled to run automatically.

• Standard Reports
  • Available to all customers automatically.
  • Have pre-defined filters and output.
  • Parameters and output vary per report.
  • No date limit on how far back data can be pulled.
• Strengths
  • User friendly
  • pre-made and available to all customers
  • Pulls data from all time
  • Can schedule to run automatically
• Weaknesses
  • Unable to customize output or filters
  • Pulls mostly aggregate level data
• Intelligence Reports
  • Free
  • Powered by an automation that compiles data from the account. Because of this, the data available for reports can go up to 24 months back, depending on when the automation started running
  • Can create pivot tables.
• Strengths
  • Not as easy as standard reports, but still drag and drop interface for ease of use.
  • No code required.
• Weaknesses
  • Depends on data from the Automation. If the Automation is broken or new, it does not have information.
  • Data in reports is always 24 hours behind
  • From the date of provisioning, 90 days prior or data is available. 
  • Data is stored for max of 2 years of historical data.
• Intelligence Reports Advanced
  • Require a purchase/provisioning.
  • Powered by an automation that compiles data from the account. Because of this, the data available for reports can go up to 24 months back, depending on when the automation started running
  • Can create pivot tables.
  • Customizable dashboards and query granular data.
  • Cross channel capabilities.
• Strengths
  • Not as easy as standard reports, but still drag and drop interface for ease of use.
  • No code required.
  • Several pre-made templates and a blank template to fully customize output and filters.
• Weaknesses
  • Depends on data from the Automation. If the Automation is broken or new, it will not have information. Max of 2 years of historical data.
  • Data in reports is always 24 hours behind
  • Requires purchase/provisioning
  • From the date of provisioning, 90 days prior or data is available. 
• Resources:
  • Analytics Builder

Data Extract Activity

• Automation studio activity that creates zipped files for usage outside Marketing Cloud Engagement
• There are a handful of default Extract Types:
  • Data Extension Extract: Extract the contents of a Data Extension
  • Contacts Without Channel Addresses: Extract contact keys of contacts who have not been assigned a channel (and do not have a mobile number/email address)
  • Tracking Extract: Extract granular tracking data from Email Studio
• Strengths:
  • Customizable
  • Granular, malleable data
  • Can be easily exported from MC
  • Flexible scheduling/automation options
  • Can access nearly all historical tracking data from account activation
• Weaknesses:
  • Require setup/technical know-how
  • Tracking Extracts have stricter time constraints/30 day limitations on retrievable data
  • Parameters may require more frequent adjustments compared to standard reporting option simplicity