Sunday 25 March 2012

Using Outbound E-mail but managing contacts from an external system

Outbound E-mail (and Audience Manager)


Since the first time I "met" TMCS, I must say I have been quite fond of it. With 2009, "he" changed his name into Outbound E-mail, which definitely helped to clarify what it was all about! Outbound E-mail (and Audience Manager) were now integrated in the SDL Tridion GUI! And some new capabilities were also added.

I really like the business strategy behind using Outbound E-mail. The Web channel is definitely important when engaging users, but we should not forget how powerful the e-mail channel still is these days. For instance, a lot of promotions are greatly successful because existing or potential customers are approach via e-mail. And not only can they target their customers but also measure their (clicking) actions on the email, etc.

Just a little note on the relation between Outbound E-mail and Audience Manager. Quite often, we talk about Outbound E-mail implying the use of Audience Manager too. They are two separate modules, but work together to bring the e-mails and e-mail campaigns to the customers. End users experience work with both modules transparently, as if it was one. For further clarification, read below a line by SDL live documentation on their scope:

Audience Manager enables marketing departments to gather information about audiences, such as interests and characteristics. Outbound E-mail is for marketing departments that need to implement and manage e-mail communication campaigns.

Having said that, let me get back to my original goal here, write about my experiences with OE (I will use the acronym from now on). First round of OE coming!!!



Context and requirements


This article describes a special use case for OE, in which OE is used to send e-mails to customers but contacts are managed in a separate CRM system.
  • The customer uses SDL Tridion 2009 SP1
  • The customer aims to minimise the number of mail sent when maximising the number of e-mails sent. This will traduce into a reduction of costs.
  • Contacts are not managed within OE, but in a CRM system considered as main entry point for contact data.
  • Bounces need to be managed. Contacts with a hard bounce should get the letter per mail instead of e-mail.
  • Statistics data should be gathered and sent to the primary system too.


Managing contacts


A process must be setup to bring the new or updated contacts from the CRM to the OE database. This can be done directly in interaction with the back-end server (as there are no organization requirements against it), therefore the solution will not make use of the front-end database for contacts and the synchronization service will not be required.

Please note that OE editors will not manage the contacts from within SDL Tridion itself, the contact updates will always take place in the CRM system.

The requirements state the contacts changes CRM will be transferred to SDL Tridion once a day. The process should be automated by using an application that interacts with Tridion using the Outbound E-mail 2009 SP1 Content Management API. Since it can run once a day, the application should extract from the external system the contacts changes occurred in the previous 24h, write them into a csv file with the correct format and then use the.NET API to import contacts from the csv file into Audience Manager. This is the preferred way of importing contacts in SDL Tridion as it minimizes the connections to the database and performs well.

SDL recommends running this application out of working hours, to minimize the impact on performance for Tridion users.


Managing bounces

Extracting contacts with bounces

When a mailing is sent, OE gathers statistics on failures. The bounce status property result of the failure does not belong to a mailing but to a contact.  The statistics of a mailing do not make a distinction between soft and hard bounces. You will need to export the contacts that got a failed flag and check their bounce status to know what the problem was.

This export action can be directly done through the GUI. Select the mailing, click on the Statistics tab, click on Failed, click on Export Contacts on the ribbon toolbar.

After some investigation, it has been concluded that the part of the API that does this is internal domain for Tridion 2009. It is allowed to use internal domain API’s but potential problems encountered would not be fully supported. Consequently, SDL recommends approaching it via a query on the database.

Thanks to Gertjan Assies for providing the exact query:

-- set the MAILINGID to the mailing for which you want to get the information
DECLARE @MAILINGID INT
SET @MAILINGID = 4
-- values for EMAILSTATUSGROUP can be looked up in the EMAILSTATUS_TYPES table
DECLARE @EMAILSTATUSGROUP INT
SET @EMAILSTATUSGROUP = 3 -- 3 meaning email failed and/or bounced
SELECT
C.*
FROM CONTACTS C
INNER JOIN
(
    SELECT C1.ID
    FROM CONTACTS C1
    WHERE C1.ID IN
    (
        SELECT E.CONTACT_ID
        FROM EMAILS E
        INNER JOIN EMAILSTATUSES ES ON E.ID = ES.EMAIL_ID             
        WHERE E.MAILING_ID = @MAILINGID
        GROUP BY E.CONTACT_ID
        HAVING MAX(ES.STATUS_ID) IN (
                                SELECT ID
                                FROM EMAILSTATUS_TYPES
                                WHERE GROUP_ID = @EMAILSTATUSGROUP
                                     )
      )
) TEMP ON TEMP.ID = C.ID
-- end query

Notes on upgrade to SDL Tridion 2011

 Tridion Audience Management TOM.NET API for SDL Tridion 2011 does expose as public API the ability to export the contacts out of failed statistics of an email.

It would be implemented using the OutboundEmailStatisticsFilter class and its properties EmailStatus and MailingUri.

 

Dealing with soft and hard bounces

The customer plans to deal with soft and hard bouncers differently. It is possible to implement a solution for this requirement, but consider the following statements before taking further decisions:

  • A soft bounce status does not always relate directly with an email not received within the current Tridion version.  There are situations like an “Out of the office” reply which also leads into soft bounce, while the email has actually been received in this case. This malfunction is already logged to be fixed.
  • A Soft bounce status is never set back to No bounce automatically, unless it is explicitly changed (via the API). We can illustrate this through an example:
  1. Mailing M1 fails with soft bounce for Contact A (say his inbox was full)
  2. The system sets the bounce status of Contact A to Soft bounce
  3. Contact A cleans his inbox
  4. Mailing M2 is sent to Contact A
  5. M2 succeeds to be sent to Contact A
  6. Bounce status of Contact A is still soft bounce

If the customer decides to stick to their requirements, every mailing should be configured to be sent to contacts with no bounce or soft bounce. For every mailing to create, this could be a flow to follow:
  1. A mailing is created
  2. A mailing is configured to be sent only to contacts with no bounce or soft bounce.
  3. The distribution list is chosen
  4. Filter the contacts from the distribution list who already have a hard bounce status and send the data to the CRM system in order to have letters sent via mail to these contacts.
  5. A mailing is sent
  6. If the mailing statistics show failures, export those contacts into a distribution list (DL1), make a copy of the mailing, replace its distribution list by the newly created DL1 and send the mailing.
    • Note that you have to do a copy of the mailing to be able to try to send a mailing to the same contact again.
  7. If failures are encountered, send the relevant data to the CRM in order to have letters sent via mail to these contacts.

Statistics


As stated in the requirements, the primary CRM system should contain all the data available for statistics within OE.

A procedure should take care of sending this data from the OE. At the same time, the CRM should be ready to receive this data. This involves an extension of the current data model on the CRM side to make sure statistics are associated to every contact.

8 comments:

  1. Cool stuff, Monica! Nice to see a connection between CRM and WCM and good point on the integration point with the SQL script. Though seemingly lower level, I imagine it's "loose" enough to survive an upgrade (with some possible tweaks) better than internal APIs shifting around.

    ReplyDelete
  2. Hi Monica. Nice stuff. Do you know how to intercept the Tracking URL for a contact , so I may use an external system to compose and send emails
    Raj

    ReplyDelete
    Replies
    1. Hey Raj,
      Before getting to an answer... what is the reasoning or need behind that? You could have the content created somewhere else but then import that content and then create Mailings using our Component/Page Template (manually or by API)

      Cheers,
      Mónica

      Delete
  3. Hi Monica,
    Our email deliverability is less than expected. Only 70 percent of confirmation emails are actually going to the user (blacklist etc). I want to use Eloqua to send the confirmation emails. All I need is the Tracking URL for an email address and I can use an Eloqua form to capture this and send an email to the user.
    Eloqua email deliverability is more guaranteed
    Raj

    ReplyDelete
    Replies
    1. Hi Raj,
      The Tracking URL is used to track clicks on any link on an email. Still, you seem to be interested on the confirmation URL link only? There is a specific link for that only. Let me know if thats the only one you need

      Mónica

      Delete
  4. You have remarked very interesting details ! ps nice website . “Wisdom is the supreme part of happiness.” by Sophocles.
    android application development company

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete