Category: Salesforce Marketing Cloud - Page 3

Retrieving and Storing Aggregated Triggered Send Data

2021-12-282022-01-06Salesforce Marketing Cloud, Server-side JavaScript

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

2021-08-182021-08-19Salesforce Marketing Cloud

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

Level	Category	Subcategory	Skill	Description
Beginner	Email Studio	AMPscript	Basic Syntax	demonstrates a working knowledge of AMPscript syntax
Beginner	Email Studio	AMPscript	Basic Functions	demonstrates a working knowledge of AMPscript functions, what they are and generally what they are used for
Intermediate	Email Studio	AMPscript	Personalization and conditionals	understands fault-tolerant methods for personalization and can build conditionals with multiple criteria
Intermediate	Email Studio	AMPScript	Debugging	knows how to use the process of elimination and debugging output to troubleshoot AMPscript runtime errors
Intermediate	Email Studio	AMPscript	Looping	can retrieve data from a data extension outside of the send/view context and iterate through and output the results
Intermediate	Email Studio	AMPscript	Payload parsing	can demonstrate how to parse a JSON or XML payload and output HTML in a loop
Advanced	Email Studio	AMPscript	API functions	is familiar with the SOAP Objects and Methods and how to interact with them using AMPscript
Advanced	Email Studio	AMPscript	Salesforce CRM functions	can demonstrate how to interact with SF CRM using AMPscript API functions
Beginner	Email Studio	SQL	Basics	understands basic SQL syntax, select statements, datatypes and where clauses
Beginner	Email Studio	SQL	Joins/unions	understands SQL data relationships and how to produce a single result from multiple data extensions
Intermediate	Email Studio	SQL	Marketing Cloud data model	is familiar with the system data views and their relationships and can build queries to retrieve and/or summarize subscriber and activity data
Intermediate	Email Studio	SQL	Conversions	knows how the datatypes in SFMC match to T-SQL data types and can convert between them using cast and convert functions.
Intermediate	Email Studio	SQL	Date math	can demonstrate how to efficiently select rows between two dates and can calculate past and future dates
Intermediate	Email Studio	SQL	Conditional field values	can demonstrate the use of case, coalesce, isnull, isnumeric, isdate to conditionally set column values
Advanced	Email Studio	SQL	Deduplicating and aggregating	knows how to deduplicate rows in a data extension and the various methods for aggregating data within the constraints of SFMC
Advanced	Email Studio	SQL	Optimization & Data Normalization	knows all reasons why queries can fail and can articulate how to address each one, familiar with normalized relational database models
Beginner	Email Studio	HTML	Basics	understands the basic anatomy of an HTML document (DOM), also containers, links and other email related elements, can manually re-indent code
Intermediate	Email Studio	HTML	Containers	can build a table with multiple columns and rows and understands inline vs block-level elements
Intermediate	Email Studio	Email HTML	Platform targeting	familiar with how to target code for specific email platforms
Intermediate	Email Studio	CSS	Basics	knows how styles cascade and can code external and inline CSS to alter the appearance of HTML elements
Intermediate	Email Studio	CSS	Media queries	can translate CSS media query syntax to alter HTML elements for various sized display sizes
Intermediate	Email Studio	JavaScript	Basics	understands the general JS syntax and data structures and can implement basic HTML5 field validation
Intermediate	Email Studio	JavaScript	Forms and libraries	can craft a basic AJAX form submission and is familiar with common JS libraries
Intermediate	Email Studio	Server-side JavaScript	Basics	understands when to use Server-side JavaScript and what libraries are available for interacting with SFMC
Advanced	Email Studio	Server-side JavaScript	Helper functions	can retrieve Subscriber and Data Extension data using the Server-side JavaScript helper functions in the context of a CloudPage
Advanced	Email Studio	Server-side JavaScript	Content Syndication	knows how to retrieve content from outside of SFMC and store it for use in SFMC
Intermediate	Contact Builder		Basics	understands how to configure data extensions for use in Journey Builder and MobileConnect
Intermediate	Journey Builder		Basics	familiar with the methods for starting someone on a journey and how to configure data source for decision splits
Intermediate	CloudPages		HTML/CSS Basics	understands responsive design and coding
Intermediate	CloudPages		One-Click Unsubscribe pages	can configure a page that accepts the send context and successfully logs an unsub event and displays a confirmation message
Intermediate	CloudPages		Code Resource pages	can build a code resource page that returns a JSON object to a referring page
Advanced	CloudPages		Data Capture Forms	knows how form posts and AJAX work in CloudPages and use the API functions to capture data in SFMC data extensions
Advanced	CloudPages		Custom Preference Centers	can build API calls with AMPscript or Server-side JavaScript to implement a custom preference center
Intermediate	MobileConnect		Basics	knows how to create dynamic content in MobileConnect messages
Advanced	MobileConnect		Scripting	can script MobileConnect messages to read data from a data extension or write data to a data extension
Intermediate	Social Studio		Basics	understands the basics of Social Studio and knows how to create audiences in Email Studios
Intermediate	Integrations	Sales/Service Cloud	Marketing Cloud Connect	familiar with how the data models are different between SFMC and SF CRM, what is created when they are integrated
Intermediate	Integrations	API	REST/SOAP API	can make a test API call using SOAP or REST from outside of the SFMC platform
Advanced	Integrations	API	Consultation	has a level of familiarity with the SFMC web services to consult with a client who is designing an API integration with SFMC
Advanced	Integrations	API	Transactional Messaging API	can create a transactional messaging send definition for Email and SMS and can demonstrate how to register webhooks for reporting
Advanced	Integrations	API	Content Block SDK	knows how to configure a custom content block in an external host and integrate it into Content Builder
Advanced	Integrations	API	Custom Journey Builder Activity	can build a custom Journey Builder activity that integrates with a third-party system
Advanced	Integrations	General	Einstein Recommendations	understands 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.

Data Extension Tips & Tricks

2021-07-212021-07-21Salesforce Marketing Cloud

Here are a few Data Extension (DE) tricks to save you some time:

Faster Times

Avoid adding fields to DEs at the top or bottom of the hour. Your data center’s server load adversely affects the required back-end update tasks and increase the likelihood of your DE changes timing out.

Fewer Drill-downs

Duplicate DEs to the root folder and then move them in multiples to the final folder structure. It saves time if you don’t have to drill-down in the folder selector for every DE.

Flexible Copy

If you need to copy a DE, go through the Create process and choose Create from Existing from the Creation Method selector. This gives you an opportunity to add/update the fields — their names, data types, primary key options and default values — in the copy.

Query Studio Create

You can create generic data extensions using Query Studio. Sometimes it’s easier to create a DE with a query, copy and update it to suit. Be sure you reset or remove the 1 day retention in your copy. This works well with the Create from Existing tip.

A hearty tip-of-the-hat to my DEG team for their contributions.

Direct Links for SFMC pages

2021-06-242021-06-24Salesforce Marketing Cloud

Thought I would share these direct links to SFMC pages (in order of my usage). Can’t remember who showed me these originally, but I use them constantly when moving around the Marketing Cloud interface. These’ll work regardless of your data center/stack.

Here’s a bookmark file you can download and import into your browser of choice.

Determining Business Unit Subscriber Status

2021-01-272021-01-27Salesforce Marketing Cloud, SQL Queries

Business unit subscriber status (or business unit membership) has been one of those frustrating and elusive things in SFMC for a long time. Since the platform only stores a business unit-level status for a subscriber if they’ve been sent an email from the child business unit, the various sources of this status in the platform are not accurate.

It wasn’t until Salesforce introduced the _BusinessUnitUnsubscribes System Data View in 2019 that there was, in my opinion, an accurate source of truth for this data.

Here are my go-to queries to arrive at this data set. I’d suggest scheduling a daily Automation with these queries in the parent business unit.

Queries

All Subscribers in all business units

This query creates a row for every subscriber in every business unit. Since subscribers exist at the enterprise level, they could potentially have a send (or unsubscribe) in any child business unit.

You’ll need to update this query to add your own MIDs and business unit names.

Unsubscribes per business unit

This one sets the status for the subscriber if an explicit unsubscribe in a business unit has been recorded.

You’ll need to update the CASE statement for your own set of MIDs and business unit names.
Unsubscribes are the only deliverability status recorded at the business unit level.

Caveats

Due to the dependencies on the System Data Views, if you have more than a few million subscribers, you’ll be hard-pressed to keep these queries running under the 30-minute timeout window. In other words, this solution doesn’t scale very well if you have a large subscriber base.

All of this assumes that your business units are configured to limit unsubscribes to the business unit level (i.e. The “Subscribers will be unsubscribed from this BU only” option is selected in Setup > Business units > Unsubscribes settings).

Uses

The business unit subscriber status data is helpful in a few areas in SFMC.

Data Integrations

If Marketing Cloud is not your database of record for deliverability and status by business unit is a key data point, this can be a good source to extract and load into an external data warehouse.

Segmentation

You can use this data as a source for suppressing opt-outs so your pre-send counts more accurately reflect actual send counts.

Reporting

If you have any reporting needs relating to business unit membership and cross-over you can utilize this data source. I don’t recommend treating SFMC like a reporting platform, but the flexibility of Query Activities and Data Extensions can’t be denied.