Category: Salesforce Marketing Cloud

Tips for the technical side of Salesforce Marketing Cloud.

Determining Business Unit Subscriber Status

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

Further reading

SSH Keys for SFTP in SFMC

Posted onCategoriesAutomation, Salesforce Marketing Cloud

If you want to use your own SSH keys for use in SFTP transfers to and from SFMC, then here’s what you need to know.

SFMC pushing files to third-party SFTP via SSH

SFMC Third-party

Private SSH Key

Specifications

  • RSA
  • 2048-bit
  • OpenSSH format
  • Can be created with or without passphrase using with ssh-keygen or puttygen

Installation

  • SSH option must be enabled by SF Support
  • Upload key file as SSH type in Setup > Admin > Data Management > Key Management

Public SSH Key

Specifications

  • See Private SSH Key specifications

Installation

  • Specific to third-party key management system

Third-party pushing files to SFMC SFTP via SSH

SFMC Third-party

Public SSH Key

Specifications

  • See Private SSH Key specifications

Installation

  • Create a new SFTP Account for connection in Setup > Admin > Data Management > FTP Accounts
  • SSH option must be enabled by SF Support
  • Upload key file as SSH type in Setup > Admin > Data Management > Key Management

Private SSH Key

Specifications

  • RSA
  • 4096-bit
  • PEM format only
  • With or without passphrase
  • Can be created with ssh-keygen or puttygen

Installation

  • Specific to third-party key management system

 

For the first scenario (pushing files from SFMC to a third-party server), here’s how you can generate an SFMC-friendly SSH key pair with ssh-keygen.
Just simply open a command prompt and issue these commands (this is specific to Windows, but it’s very similar on a Mac):

> ssh-keygen -t rsa -b 2048
Generating public/private rsa key pair.
Enter file in which to save the key (C:\DEFAULTUSERPATH/.ssh/id_rsa): c:\YOURDESTINATIONPATH\testkey
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in c:\YOURDESTINATIONPATH\testkey.
Your public key has been saved in c:\YOURDESTINATIONPATH\testkey.pub.
The key fingerprint is:
(redacted)
> dir /b testkey*
testkey
testkey.pub
> ren testkey testkey.ppk

 

For the second scenario, (pushing files from a third-party server to SFMC), here’s how to get it in the correct format for SFMC with ssh-keygen:

> ssh-keygen -t rsa -b 4096 -m PEM
Generating public/private rsa key pair.
Enter file in which to save the key (C:\DEFAULTUSERPATH/.ssh/id_rsa): c:\YOURDESTINATIONPATH\testkey
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in c:\YOURDESTINATIONPATH\testkey.
Your public key has been saved in c:\YOURDESTINATIONPATH\testkey.pub.
The key fingerprint is:
(redacted)
> dir /b testkey*
testkey
testkey.pub
> ssh-keygen -e -f testkey.pub > testkey-ssh2.pub
> ren testkey testkey.ppk

Timezones in SFMC

Posted onCategoriesSalesforce Marketing Cloud

Dates/times displayed in the Marketing Cloud interface are adjusted based on the logged-in user’s timezone. The same display adjustment applies to any scheduling configured in Automations or Journeys.

SFMC user time zones

However, dates and times in Marketing Cloud are stored UTC-6 (Central Standard Time with no daylight savings). System data views and tracking data are all stored in this timezone.

You should always assume that “Today” and “Now” are UTC-6.

The query engine in SFMC does support timezone adjustments using the AT TIME ZONE directive. So if you need to adjust UTC-6 timestamps, this is a good place to start.

If you need to accommodate subscriber-specific timezones for sends, you’ll need a “home” timezone or offset for each subscriber and a timezone offset reference data source. Naveen VM has outlined a good end-to-end example.

The only outlier (as far as I know) is Personalization Builder/Einstein Recommendations. Those dates are displayed and recorded in UTC-0.

Did I miss any other TZ weirdsies? Let me know down in the comments.

Reference:

My Baby Shark AMPscript Challenge Variations

Posted onCategoriesAMPScript, Salesforce Marketing Cloud

It’s been a few months since the judging wrapped up for the Baby Shark AMPscript Challenge on HowToSFMC. The goal was to use AMPscript to output the lyrics of Baby Shark (EARWORM WARNING). There were a few of categories for submissions, but I read them all as “with as little code as possible”. Obstinate fixation… engage!

To be completely honest, I spent way too much time obsessing over my own solution — enjoying every moment. Shortest I got on my own was 260 characters. Here are my 7 variations:

1a. 260 characters, compressed

1b. 260 characters, uncompressed

2. Single loop & mod

3. Single loop, mod & math

4. Double loop, string parse

5. String char loop

6. String concatenate loop

7. Multiple concat loop

What I learned

  • The output and outputline functions weirdly require other functions like v or concat.
  • The variable isn’t required for the for-loop next directive
  • I should never, ever write something like this for a client.
  • There are some incredibly talented people in this developer community! I was amazed at some of the entries. The whole thing was super-difficult to judge.

Retrieving and Storing Aggregated Send Data

Posted onCategoriesSalesforce Marketing Cloud, Server-side JavaScript

One of the great things about the WSProxy library for Server-Side JavaScript in Salesforce Marketing Cloud (SFMC) is that it has reinvigorated the use of SOAP API objects in the platform. SOAP objects are still foundational to understanding the SFMC data model.

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

(BTW, check out what’s available — it’s everything in the Recent Email Sending Summary Report, plus some more details.)

Simply create a Script Activity in Automation Studio like this. Before the first run, update the TargetDECustomerKey and TargetDECategoryID values for your preferred Data Extension and folder. I generally use Send for the Data Extension name and customer key. As noted in the script, you can comment out Data Extension creation part after the first run.

This script sets a filter (it’s like a where-clause in SQL), iterates through the Send Object results, and upserts them into the specified Data Eextension. If you don’t want to retrieve all sends every time, you can uncomment the filter criteria and get more specific. I’ve generally not restricted it by date, because it’s not that much data to retrieve.

Finally, schedule this to run on an interval appropriate for your needs. At least daily is good.

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.