James Lamb - Subscriber Preferences Management


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:

Detailed map of subscription management solution

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

Schema table

  • 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

Extract Configuration

File Transfer

File transfer setup

Import

Import configuration

All Rights Reserved
Made with by your fellow SFMC users.
All Rights Reserved
Made with by your fellow SFMC users.