Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

A Lyris to Marketing Cloud imaginary architecture

15 Mar 2021 🔖 architecture integration salesforce marketing cloud
💬 EN

A lot of small organizations seem to have legacy use of Lyris (now Aurea) ListManager, even as they adopt newer mass e-mailing software like MailChimp or Salesforce Marketing Cloud.

One reason it’s been so hard to migrate away from is that Lyris offers you the ability to attach “magic e-mail addresses” to mailing lists (whose membership lists can be dynamically populated from an enterprise database), meaning, for example, that the chair of the Math department at a university can simply email [email protected] from Microsoft Outlook, and it will be sent along as if they’d BCCed everyone.

For some internal groups, Microsoft Active Directory-based Office365 mailing groups can replace Lyris for this use case, but anyone in the organization can see their membership, so Lyris might still be preferred when list membership is a secret (e.g. to avoid harrassment of its members).

Today, I had a crazy idea that just might work to get Salesforce Marketing Cloud to behave more like Lyris for this use case.

Salesforce Marketing Cloud can do dynamically populated mailing lists from SQL against a database. It’s not as “type a query & go” as it is in Lyris, but it’s doable.

The classic way to populate a Salesforce Marketing Cloud (SFMC) mailing list from SQL against a database, as I understand it, is to use an ETL process to fill a Data Extension with the list members:

  1. Connect to the database over ODBC and extract data as queried with SQL
  2. Generate CSV files from the extracted data
  3. Connect to Marketing Cloud over SFTP and send it the CSV files
  4. Configure Marketing Cloud to automatically populate data extensions from the files on the SFTP server

However, Marketing Cloud doesn’t have any sort of “magic e-mail address” functionality for mailing these Data Extensions by sending e-mail to [email protected] from a trusted “administrator” e-mail address like [email protected].

What it does have, as pointed out by Adam Spriggs, is a SOAP-based Email Send Definition API that lets you pass in text for an email subject and HTML for an email body as attributes of the API call.

In Marketing Cloud jargon, he says this is known as a “user-defined send.”

So … how do we know what the subject heading & email body for this send should be?

Well … we could parse them out of an e-mail sent to a [email protected].

What if MathAlumni were a special mailbox within a Microsoft mail environment, and some sort of email monitor were watching that inbox?

Could it validate that all e-mails genuinely come from MathDean or MathDepartmentAssistant and make sure some sort of automation service gets a chance to parse the email?

Maybe.

That automation service (exactly which one = still TBD) would have to be constantly listening for notifications from Microsoft Exchange, and would have to be set up to hit the Marketing Cloud endpoint, which means it’d need to be a service you trust with your Marketing Cloud credentials.

Furthermore, you’d have to figure out a way to keep the chain of authentication unbroken enough that no one can, at any point in the integration chain, successfully spoof MathDean and get their own hacker-ey text sent out to all the math alumni.

It’s a pretty half-baked & fragile idea, and Lyris isn’t that expensive to just keep running, but … it might work?

Please comment if you actually run with this craziness & implement it securely. :)

--- ---