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

Lyris

12 Feb 2021 🔖 integration
💬 EN

TODO: INTRO Last I left off … kyle … Matthew/Adam? told me not to do it …

Lyris (now Aurea) ListManager is mass e-mailing software, a bit like MailChimp or Salesforce Marketing Cloud. However, it goes back to … 1994?, and rather than running in the cloud, it’s made for a company to install on its own servers. It consists of a web-accessible user interface and a back-end database running on the Microsoft SQL Server database management system.

ListManager lets you configure a mailing list to dynamically pull its membership from a SQL query against one of your company’s databases.

The classic way to populate a Salesforce Marketing Cloud (SFMC) mailing list from SQL against a database 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

If you have hundreds of SQL-based Lyris lists, it could take quite a while to manually copy & paste all of their queries into your ETL tool. For

In this post, I’ll propose a script to save a little bit of time.


Sadly, another Lyris feature is the ability to have a “magic e-mail address” for each list. When an e-mail is received at that address from a member of the list who’s flagged as an “administrator,” that e-mail gets redirected as a sort of bulk-BCC to every member of the list. This can be handy for quick “Brownies in room 104!” or “heads up: our web site will be down this weekend” notes to internal distribution groups whose member e-mail addresses don’t require “unsubscribe” tracking compliant with anti-spam laws.

Marketing Cloud doesn’t have that feature, so it’s not clear that Lyris can be completely migrated away from.

;WITH


LISTS_VIEW AS (
SELECT
  lists_.ListID_, 
  lists_.Name_ AS 'ListName', 
  lists_.DescShort_, 
  lists_.Keywords_, 
  qMembers.MemberCount,
  qSenders.SenderCount,
  CASE WHEN (lists_.SyncConnID_ IS NULL) THEN ('Manual') ELSE ('Database Sync') END AS 'Membership_Type',
  lists_.SyncConnID_, 
  lyrDbConnections.Site, 
  lyrDbConnections.ConnName, 
  lyrDbConnections.SelectQuery,
  lists_.SyncDeleteMode_, 
  lists_.SyncInsertMode_, 
  lists_.SyncMaxMalformed_,
  lists_.SyncNextTime_,
  lists_.SyncPreviousSuccessTime_,
  lists_.SyncPreviousTime_,
  lists_.SyncSchedule_,
  lists_.SyncTimeRule_,
  lists_.SyncUpdateMode_,
  lists_.SyncWeekDay_,
  lists_.SyncWindowEnd_,
  lists_.SyncWindowStart_
FROM lists_
LEFT OUTER JOIN lyrDbConnections ON lists_.SyncConnID_ = lyrDbConnections.ConnID
LEFT OUTER JOIN (SELECT DISTINCT List_, COUNT (MemberID_) OVER (PARTITION BY List_) AS 'MemberCount' FROM members_) qMembers ON lists_.Name_ = qMembers.List_
LEFT OUTER JOIN (SELECT DISTINCT List_, COUNT (MemberID_) OVER (PARTITION BY List_) AS 'SenderCount' FROM members_ WHERE members_.IsListAdm_ = 'T') qSenders ON lists_.Name_ = qSenders.List_
)


, SEGMENTS_VIEW AS (
SELECT
  SubsetID_ AS 'SegmentID', 
  Name_ AS 'SegmentName', 
  Desc_, 
  List_, 
  ClauseFrom_, 
  ClauseSelect_, 
  ClauseWhere_,
  LISTS_VIEW.*
FROM subsets_
INNER JOIN LISTS_VIEW ON subsets_.List_ = LISTS_VIEW.ListName
)


, LIST_MEMBERS_VIEW AS (
SELECT
  members_.MemberID_, 
  members_.EmailAddr_, 
  members_.IsListAdm_, 
  members_.List_
FROM members_
)


, PASSWORDED_DISTINCT_LIST_MEMBERS_VIEW AS (
SELECT DISTINCT
  members_.EmailAddr_
FROM members_
WHERE members_.Password_ IS NOT NULL
)


, USERS_VIEW AS (
SELECT
  PeopleID_,
  EmailAddr_,
  Name_,
  ServerAdm_,
  Site_,
  SiteAdmin_,
  WhatSites_
FROM people_
)


SELECT *
FROM SEGMENTS_VIEW
;
--- ---