DATA-MINING CONTACT ACTIVITY FROM SITECORE EXM CAMPAIGNS – PART 1

Did you know that Sitecore provides an easy way to see the different recipient activity from an EXM campaign? These different contact lists can be accessed directly from Sitecore’s UI inside each email EXM campaign:

However, what if we want to reuse this information? Unfortunately, this only allows us to see the contacts. It doesn’t allow us to export them. This is where things get a little tricky and MongoDB comes in. Sitecore saves all contacts’ interactions inside MongoDB’s Interactions collection. So, in order to datamine a specific list of contacts from a campaign, we have to query this collection for interactions that match 2 criteria:

The event that corresponds to our desired contact interaction: These events are saved in MongoDB under the Pages.PageEvents.Name element of each interaction. In our example, we will use “Pages.PageEvents.Name”:”Unsubscribed”. (for a full list of the EXM interactions that Sitecore creates in MongoDB, please refer to: https://doc.sitecore.com/developers/exm/90/email-experience-manager/en/the-interactions-created-in-xdb-by-exm.html) For example:

• Sent – one interaction for every sent email message.
• Opened – one interaction when a contact opens an email message.
• Clicked – one interaction when a contact clicks a link in the email message.
• Bounced – one interaction when an email message bounces.
• Unsubscribed – one interaction when a contact unsubscribes from an email message.
• Spam – one interaction when a contact marks the email message as spam.
• Dispatch failed – one interaction when an email message could not be sent to the MTA, for example, because of an invalid email address.

The message ID of the campaign: Unfortunately, we can’t use the Campaign name directly in MongoDB since it doesn’t store this name in the interaction document. Fortunately, we can use the campaign id which can be retrieved from Sitecore’s EXM URL. For instance:

This Campaign Id corresponds to the Message Id recorded in MongoDB under: Pages.PageEvents.Data. So, in our case, we would use something like this: {“Pages.PageEvents.Data”:{$regex: ‘MessageId”:”b1628809-e30f-4932-b872-25e4003ddc15’}}

Putting these 2 elements together, we can generate our MongoDB query that would return the list of contacts that have unsubscribed from an email campaign:

db.getCollection('Interactions').find(

{ $and: [

            {"Pages.PageEvents.Name":"Unsubscribed"},

            {"Pages.PageEvents.Data":{$regex: 'MessageId":"b1628809-e30f-4932-b872-25e4003ddc15'}}

        ]}

)

And, that’s it! This will return a list of contact interactions that correspond to our EXM campaign and our desired email interaction.