Storing SFMC AuditEvents in a Data Extension

Audit Events are worthwhile to explore in Salesforce Marketing Cloud (SFMC) if you’re in a vertical that includes tighter roles and permissions. You can supplement your audit scheme by retrieving these Audit Events via REST and writing them to a Data Extension (DE).

First, you’ll need to enable the feature here: Email Studio > Admin > Security Settings > Enable Audit Trail Data Collection > Yes

After you’ve collected data for a few days, create a Script Activity like this and set it to run on the desired interval in an Automation.

You’ll need to add your target Data Extension’s Category ID, MID, REST API Tenant, Client Id and Client Secret.

Two-Click Unsubscribe with AMPscript

With additional scrutiny on outbound links in corporate inboxes these days, one-click unsubscribe pages aren’t always a good idea — any visit from a click will result in an immediate action. In the case of a one-click unsubscribe page, it’s an opt-out.  That’s no good.

Here’s an example of a two-click unsubscribe page that requires an additional click to confirm the subscriber’s desire to unsubscribe (or not).

Visit from email



The code

As always, if you have any feedback, questions or find any issues with this, feel free to drop me note on the contact page or via comment down below.

Recursively Republishing Triggered Send Definitions

I do love well though-out SFMC email architectures, especially ones that are modular. One of the drawbacks of using the AMPscript reference blocks for sharing content across email definitions is that, while the actual changes are done in only a few places, getting the actual send definitions updated requires an additional step — republishing. This goes for Journey emails, also.

Here’s a script that I wrote to automate pausing, publishing and starting Triggered Send Definitions (TSD) by folder. It’ll recursively traverse the folder structure if you have a bunch of nested folders. Results of the process are logged in a Data Extension. To get started, create a Data Extension with this schema:

Name: republish_trigger_log
External Key: republish_trigger_log

Here’s the script — just update line 10 with the parent folder’s Category ID.

This script starts at the CategoryID specified on line 10 and loops down through all of the child folders.

NOTE: You can find the Category ID by inspecting the URL at the bottom of your browser when you hover over the folder in SFMC.

When it encounters and “Active” Triggered Send Definition, it pauses, publishes and starts it. Results of those three actions are recorded in republish_trigger_log.

I recommend running this Script Activity in an unscheduled Automation.

Retrieving and Storing Aggregated Triggered Send Data

Here’s another one of my favorite applications of WSProxy — retrieving and storing aggregated TriggeredSendSummary data in a Data Extension using a Script Activity.

This data is helpful in that it shows aggregated send data for all Triggered Sends — traditional and Journey Builder ones.  A new row is added every time a Triggered Send Definition is published, so it’s also a historical record of publishing events — along with the performance of each “version” of the Triggered Send Definition.  The sum of each of the activity counts represents what you would see in Tracking for a particular Triggered Send Definition.

One nice benefit is that the results also contain the number of emails queued to be sent at a specific point in time.  So if you retrieve this data hourly (with some additional configuration), you could monitor the sending queue over time.

(If you’re looking for something like this for TXM Email sends, you’re stuck with the queue metrics endpoint and aggregating the activity yourself.)

To get started, create a Data Extension with this schema:

Name: TriggeredSendSummary
External Key: TriggeredSendSummary

Next, create a Script Activity in Automation Studio like this.

This script does a Describe of a couple of SOAP objects to get the retrievable properties, sets a filter (it’s like a where-clause in SQL), iterates through the TriggeredSendSummary Object results, does a few more related retrieves for specific attribute, and finally upserts the results into the specified data extension. If you don’t want to retrieve all records every time, you can update the filter criteria on line 81 and get more specific. I’ve generally not restricted it, because it’s not that much data to retrieve.

Finally, schedule this to run on an interval appropriate for your needs.

Once you have data, you can toolbar-download it as needed, query it, export it with an Extract Activity, or lookup and display it in an email or CloudPage with AMPscript.

Salesforce Marketing Cloud Engineer Skills

I’ve spent a fair amount of time thinking about the skill progression/path for someone who’s doing engineering work in the Salesforce Marketing Cloud platform.

This obviously doesn’t apply to everyone working in the platform, but for those of you doing this kind of work in agencies, I think it can be a helpful guide for taking the next step. Certification tests have their place for sure, but these are what I’ve found to be the practical benchmarks.

Recruiters, hiring managers: this is what people like me do in Marketing Cloud.

Download as a CSV

BeginnerEmail StudioAMPscriptBasic Syntaxdemonstrates a working knowledge of AMPscript syntax
BeginnerEmail StudioAMPscriptBasic Functionsdemonstrates a working knowledge of AMPscript functions, what they are and generally what they are used for
IntermediateEmail StudioAMPscriptPersonalization and conditionalsunderstands fault-tolerant methods for personalization and can build conditionals with multiple criteria
IntermediateEmail StudioAMPScriptDebuggingknows how to use the process of elimination and debugging output to troubleshoot AMPscript runtime errors
IntermediateEmail StudioAMPscriptLoopingcan retrieve data from a data extension outside of the send/view context and iterate through and output the results
IntermediateEmail StudioAMPscriptPayload parsingcan demonstrate how to parse a JSON or XML payload and output HTML in a loop
AdvancedEmail StudioAMPscriptAPI functionsis familiar with the SOAP Objects and Methods and how to interact with them using AMPscript
AdvancedEmail StudioAMPscriptSalesforce CRM functionscan demonstrate how to interact with SF CRM using AMPscript API functions
BeginnerEmail StudioSQLBasicsunderstands basic SQL syntax, select statements, datatypes and where clauses
BeginnerEmail StudioSQLJoins/unionsunderstands SQL data relationships and how to produce a single result from multiple data extensions
IntermediateEmail StudioSQLMarketing Cloud data modelis familiar with the system data views and their relationships and can build queries to retrieve and/or summarize subscriber and activity data
IntermediateEmail StudioSQLConversionsknows how the datatypes in SFMC match to T-SQL data types and can convert between them using cast and convert functions.
IntermediateEmail StudioSQLDate mathcan demonstrate how to efficiently select rows between two dates and can calculate past and future dates
IntermediateEmail StudioSQLConditional field valuescan demonstrate the use of case, coalesce, isnull, isnumeric, isdate to conditionally set column values
AdvancedEmail StudioSQLDeduplicating and aggregatingknows how to deduplicate rows in a data extension and the various methods for aggregating data within the constraints of SFMC
AdvancedEmail StudioSQLOptimization & Data Normalizationknows all reasons why queries can fail and can articulate how to address each one, familiar with normalized relational database models
BeginnerEmail StudioHTMLBasicsunderstands the basic anatomy of an HTML document (DOM), also containers, links and other email related elements, can manually re-indent code
IntermediateEmail StudioHTMLContainerscan build a table with multiple columns and rows and understands inline vs block-level elements
IntermediateEmail StudioEmail HTMLPlatform targetingfamiliar with how to target code for specific email platforms
IntermediateEmail StudioCSSBasicsknows how styles cascade and can code external and inline CSS to alter the appearance of HTML elements
IntermediateEmail StudioCSSMedia queriescan translate CSS media query syntax to alter HTML elements for various sized display sizes
IntermediateEmail StudioJavaScriptBasicsunderstands the general JS syntax and data structures and can implement basic HTML5 field validation
IntermediateEmail StudioJavaScriptForms and librariescan craft a basic AJAX form submission and is familiar with common JS libraries
IntermediateEmail StudioServer-side JavaScriptBasicsunderstands when to use Server-side JavaScript and what libraries are available for interacting with SFMC
AdvancedEmail StudioServer-side JavaScriptHelper functionscan retrieve Subscriber and Data Extension data using the Server-side JavaScript helper functions in the context of a CloudPage
AdvancedEmail StudioServer-side JavaScriptContent Syndicationknows how to retrieve content from outside of SFMC and store it for use in SFMC
IntermediateContact BuilderBasicsunderstands how to configure data extensions for use in Journey Builder and MobileConnect
IntermediateJourney BuilderBasicsfamiliar with the methods for starting someone on a journey and how to configure data source for decision splits
IntermediateCloudPagesHTML/CSS Basicsunderstands responsive design and coding
IntermediateCloudPagesOne-Click Unsubscribe pagescan configure a page that accepts the send context and successfully logs an unsub event and displays a confirmation message
IntermediateCloudPagesCode Resource pagescan build a code resource page that returns a JSON object to a referring page
AdvancedCloudPagesData Capture Formsknows how form posts and AJAX work in CloudPages and use the API functions to capture data in SFMC data extensions
AdvancedCloudPagesCustom Preference Centerscan build API calls with AMPscript or Server-side JavaScript to implement a custom preference center
IntermediateMobileConnectBasicsknows how to create dynamic content in MobileConnect messages
AdvancedMobileConnectScriptingcan script MobileConnect messages to read data from a data extension or write data to a data extension
IntermediateSocial StudioBasicsunderstands the basics of Social Studio and knows how to create audiences in Email Studios
IntermediateIntegrationsSales/Service CloudMarketing Cloud Connectfamiliar with how the data models are different between SFMC and SF CRM, what is created when they are integrated
IntermediateIntegrationsAPIREST/SOAP APIcan make a test API call using SOAP or REST from outside of the SFMC platform
AdvancedIntegrationsAPIConsultationhas a level of familiarity with the SFMC web services to consult with a client who is designing an API integration with SFMC
AdvancedIntegrationsAPITransactional Messaging APIcan create a transactional messaging send definition for Email and SMS and can demonstrate how to register webhooks for reporting
AdvancedIntegrationsAPIContent Block SDKknows how to configure a custom content block in an external host and integrate it into Content Builder
AdvancedIntegrationsAPICustom Journey Builder Activitycan build a custom Journey Builder activity that integrates with a third-party system
AdvancedIntegrationsGeneralEinstein Recommendationsunderstands how to configure foundational Einstein recommendations data in SFMC and can advise client teams with tracking integrations

I’d love to hear from you about this. What would you add or remove? Leave a comment below.