James Lamb

Everywhere I’ve worked, Subscriber Management has been a complex beast. You’ve got multiple backend systems that know about a person, you want to make sure a person is empowered to control their subscription status (not just unsubscribe), you’ve got legal issues around compliance and you’ve got the technical challenges of bounces.

At each place I’ve worked, we’ve approached that challenge from a different angle with varying levels of success, but recently I had a chance to design a system from scratch. My solution is for our particular business objectives and based on U.S. law which is far more lax than some other countries. In the case of law compliance and procedures, always make sure to check with your company’s legal team. I wish I could share more detail (because I’m pretty proud of it), but there are proprietary/confidential details that I can’t share. So I’ll cover a simplified version of this:

One automation, 20 data extensions (less if you left off development and qa), 42 queries (again, less w/o dev and qa), 1 Data Extract, 1 File Transfer and 1 Import.

The objectives

• Honor all unsubscribe requests in 10 business days or less. Hopefully much, much less.
• Allow subscribers to unsubscribe without authentication (existing landing page allows them to enter their email address and hit submit to unsubscribe. This is also linked at the bottom of all of our transactional emails and used by our Customer Service team when a person asks to be unsubscribed during a call or chat)
• Allow subscribers to subscribe or unsubscribe from within their authenticated Account Management portion of our website (applies only to their current, primary email address)
• Allow use of the native ESP (SFMC) unsubscribe method (click or reply “unsubscribe” in email)
• Get most recent instruction if they use multiple methods to give conflicting instructions (for instance, use the Account Management to subscribe and then use an email to unsubscribe)
• Include a Development flow (the right half of the flowchart) and a QA process. I won’t talk much about the Development flow (it mimics production as best as it can). The QA process allows you to get before and after snapshots for comparison. This is useful in troubleshooting. Together with the Development flow, the QA process will be instrumental in any future changes to the process.
• An email address that’s not unsubscribed is eligible to receive emails. It might or might not actually be sent any emails.
• An email address that is unsubscribed should not receive any marketing emails.
• Any new subscribers should get our current blog email (their address should be passed over to that automation to be handled)

The rules and terms

• Unique Key: Email Address. Each account must have a unique active, current email address, each email address can belong to one and only one account. (An email can’t exist on more than one account.)
• Marketing Election: The user’s indicated choice to be subscribed or not subscribed. It is an attribute of Email Address. It has a timedate stamp.
• Never use one complicated query when several simpler queries will do
• This assumes SFMC doesn’t start charging by # of DEs/amount of data in DEs
• The entire extended automation takes about 35 minutes end-to-end. With more data, additional optimizations may be necessary.

The states of an email address

• Explicit Opt-In - they have told us they wanted to receive email from us. By U.S. law, this also covers people who have a financial relationship with us (i.e., they’ve made a purchase).
• Explicit Opt-Out - they have told us they do not want to receive email from us. (They have unsubscribed.)
• Implicit Opt-In - they have not told us one way or another. Because our website predates our ESP (and the one before it), it’s possible that there are some customers (long-term paying subscribers) whose email address has never been passed to the ESP.

The inputs

Salesforce Marketing Cloud receives inputs in the following manner:

1. Subscribe. We offer opportunities on our website to subscribe to an email. Accepted email addresses are passed from the form to SFMC via API (rowset) to an “Opt-In” Data Extension. This stores the Email Address, the timestamp and the form.
2. Unsubscribe
   1. Native - SFMC offers several native methods for unsubscribe via Profile Center, Subscriber Center or by replying “unsubscribe” in an otherwise blank email. These update the “_Unsubscribe” Data View.
   2. Form - we offer a CloudPage where you can enter an email address to be unsubscribed. It is stored in an “Unsubscribe Request” Data Extension.
   3. API - there are some cases where our website determines an email address should be unsubscribed from marketing emails. These are passed by API (rowset). This does not prevent them from being able to re-subscribe if they so choose.
3. Subscribe/Unsubscribe - From our Authenticated User Preferences section on our website, there is a checkbox for “Receive Marketing Emails”
   1. When someone first visits the page, an API call is made to SFMC to determine their “Marketing Election”
      1. SFMC responds “Subscribed” - the checkbox is displayed as checked
      2. SFMC responds “Unsubscribed” - the checkbox is display as unchecked
      3. SFMC says it doesn’t know - the checkbox is displayed as checked, another API call (rowset) is made to mark their election as “Subscribed”

Tha Automation

Runs hourly

1. Query - get any new records in the past 24 hours from the _Unsubscribe Data Extension (target SuppressionMaster, update) where the record isn’t also in MarketingEmailElection in a subscribed state with a later timestamp.

   SELECT
    
       u.SubscriberKey as EmailAddress,
       MAX(u.EventDate) as dteOptOut,
       '_Unsubscribe' as strSource
    
   FROM
    
       [_Unsubscribe] u
        
   WHERE
    
       u.EventDate > DATEADD(HOUR, -25, GETDATE())
    
       AND
    
       NOT EXISTS (select 1 from [MarketingEmailElection] mee WHERE
   mee.EmailAddress = u.SubscriberKey
   AND u.EventDate < mee.dteUpdate and mee.blnOptIn = 1)
         
   GROUP BY
    
        u.SubscriberKey
   

2. Query - get any new records that were passed via the unsubscribe API (target: SuppressionMaster, update)

   SELECT
    
       s.Email as EmailAddress,
       s.[Last Modified] as dteOptOut,
       'suppression_prod' as strSource
    
   FROM
    
       [WebSuppression] s
        
   WHERE
    
       NOT EXISTS (select 1 from [MarketingEmailElection] mee WHERE
   mee.EmailAddress = s.Email
   AND s.[Last Modified] < mee.dteUpdate
   and mee.blnOptIn = 1)
   

3. Query - clear WebSuppression (could explore using SSJS to remove all rows) (target: WebSuppression, OVERWRITE)

   SELECT
    
       EmailAddress as Email
    
   FROM
    
       [SuppressionMaster]
        
   WHERE
    
       EmailAddress = 'intentionally, this will never match anything@hello.com'
   

4. Query - get all the opt-outs from the opt-out form (target SuppressionMaster, update)

   SELECT
    
       eco.EmailAddress,
       eco.[dteSubmitted] as dteOptOut,
       'ExternalCloudpageOptout' as strSource
    
   FROM
    
       [ExternalCloudpageOptout] eco
    
   WHERE
    
       NOT EXISTS (select 1 from [MarketingEmailElection] mee WHERE
   mee.EmailAddress = eco.EmailAddress AND eco.[dteSubmitted] < mee.dteUpdate
   and mee.blnOptIn = 1)
   

5. Query - Clear optout form. (similar to query #3)

6. Query - get any new unsubscribed records from _Subscribers (All Subs). Target SuppressionMaster, update

   SELECT
    
       s.EmailAddress,
       s.[DateUnsubscribed] as dteOptOut,
       '_Subscribers' as strSource
    
   FROM
    
       [_Subscribers] s
        
   WHERE
    
       s.status = 'unsubscribed'
    
       AND
    
       NOT EXISTS (select 1 from [MarketingEmailElection] mee WHERE
   mee.EmailAddress = s.EmailAddress AND s.[DateUnsubscribed] < mee.dteUpdate
   and mee.blnOptIn = 1)
   

7. Query - get all the unsubscribes from MarketingEmailElection - target SuppressionMaster, update

   SELECT
    
       EmailAddress,
       dteUpdate as dteOptOut
    
   FROM
    
       [marketing_email_election_prod]
        
   WHERE
    
       blnOptin = 0
   

   ...Take a breather...

   Ok, at this stage “SuppressionMaster” now has all of the unsubscribes from all the different places you can unsubscribe. Now, we’re going to handle all of the new “subscribe” (yes) elections.

8. Query - get all the “yes” entries from the past 72 hours from MarketingEmailElection and store in MeeYes1, OVERWRITE. (At this point, you’ve probably guessed that I’m changing the DE names from what I use in my instance.)

   SELECT
       EmailAddress,
       blnOptin,
       dteUpdate,
       strSource =
           CASE
           WHEN strSource is NULL THEN 'AcctPage'
           ELSE strSource
           END
   FROM [MarketingEmailElection] mee
   WHEREblnOptin = 1
     
    AND
     
    DATEDIFF(hour,mee.dteUpdate,GETDATE())<72
   

9. Query - now, get only the “yes” requests that are newer than the “no” (unsubscribe) requests in SuppressionMaster. Target MeeYes2, OVERWRITE.

   SELECT
    
       mee1.EmailAddress,
       mee1.blnOptin,
       mee1.dteUpdate,
       sm.dteModified,
       mee1.strSource
        
   FROM
    
       [MeeYes1] mee1
        
   INNER JOIN
        
       [SuppressionMaster] sm
           ON mee1.EmailAddress = sm.EmailAddress
            
   WHERE
    
       mee1.dteUpdate > sm.dteModified
        
       AND
        
       mee1.blnOptin = 1
            
   UNION
    
   SELECT
    
       mee1.EmailAddress,
       mee1.blnOptin,
       mee1.dteUpdate,
       NULL as dteModified,
       mee1.strSource
        
   FROM
    
       [MeeYes1] mee1
    
   WHERE
    
       NOT EXISTS (select 1 from [SuppressionMaster] sm
   where sm.EmailAddress = mee1.EmailAddress )
   

10. Query - now get all the records that are in SuppressionMaster unless they’re in MeeYes1. Target SuppressMasterTransit, OVERWRITE. We need to do this because we need to remove the new “yes” entries from SuppressionMaster.

    SELECT
     
        mee1.EmailAddress,
        mee1.blnOptin,
        mee1.dteUpdate,
        sm.dteModified,
        mee1.strSource
         
    FROM
     
        [MeeYes1] mee1
         
    INNER JOIN
         
        [SuppressionMaster] sm
            ON mee1.EmailAddress = sm.EmailAddress
             
    WHERE
     
        mee1.dteUpdate > sm.dteModified
         
        AND
         
        mee1.blnOptin = 1
             
    UNION
     
    SELECT
     
        mee1.EmailAddress,
        mee1.blnOptin,
        mee1.dteUpdate,
        NULL as dteModified,
        mee1.strSource
         
    FROM
     
        [MeeYes1] mee1
     
    WHERE
     
        NOT EXISTS (select 1 from [SuppressionMaster] sm
    where sm.EmailAddress = mee1.EmailAddress )
    

11. Query - now we write them back to SuppressMaster, OVERWRITE. At some point, I’d love to replace this with an SSJS SQL “DELETE” query instead.

    SELECT
     
        EmailAddress,
        dteOptOut,
        dteModified
         
    FROM
     
        [SuppressionMasterTransit]
    

    ...Another Break...

12. Query - now we need to make a list of the records that need to be updated in _Subscribers. Target SubscribersUpdate, OVERWRITE

    SELECT
     
        y2.EmailAddress as [Email Address],
        y2.EmailAddress as [Subscriber Key],
        'active' as status,
        y2.blnOptin,
        y2.dteUpdate,
        s.DateUnsubscribed
         
    FROM
     
        [MeeYes2] y2
         
    INNER JOIN
     
        [_Subscribers] s
        ON s.EmailAddress = y2.EmailAddress
    

13. Now that we’ve got that file, we need to Export it to our SFMC SFTP Storehouse.

14. And then transfer it from the Storehouse to the SFTP Import Directory.

15. And then import it into _Subscribers, update.

    ...Phew...

16. Get everyone from Yes1 in the past 24 hours and place into Yes3, OVERWRITE.

    SELECT
     
        yes1.EmailAddress,
        yes1.dteUpdate,
        yes1.strSource
         
    FROM
     
        [MeeYes1] yes1
         
    WHERE
     
        yes1.dteUpdate > dateadd("d",-1,GETDATE())
         
        AND
         
        yes1.blnOptin = 1
    

17. Place everyone from Yes3 into the “optin” data extension. A different process will read from this Data Extension and send the latest blog email to all new opt-ins. It’s designed to only send them the email once, even if they appear in here more than once.

    SELECT
     
        yes1.EmailAddress,
        yes1.dteUpdate,
        yes1.strSource
         
    FROM
     
        [marketing_email_election_yes1_prod] yes1
         
    WHERE
     
        yes1.dteUpdate > dateadd("d",-1,GETDATE())
         
        AND
         
        yes1.blnOptin = 1
    

The Schemas

• mee = MarketingEmailElection, MeeYes1, MeeYes3
• mee2 = MeeYes2
• ms = not explained in this simplified version for HowToSFMC
• qa1, qa2, qa3 = used in the QA process, also not explained here

SuppressionMaster and SuppressionMasterTransit

Extract

File Transfer

Import