Lyris
12 Feb 2021
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:
- Connect to the database over ODBC and extract data as queried with SQL
- Generate CSV files from the extracted data
- Connect to Marketing Cloud over SFTP and send it the CSV files
- 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
;