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

In our previous post, we learnt how we can extract specific contact interactions from an EXM email campaign from MongoDB. This can probe to be very useful for instances of Sitecore 8 and lower where xConnect is not available (For more information, please see: https://doc.sitecore.com/developers/91/sitecore-experience-platform/en/xconnect-client-api-overview.html). However, what if we want to dig a little further into this data. In this post, we will explore additional MongoDB queries that will help us extract additional EXM information and help us understand a little better how Sitecore stores this data in MongoDB. So, fire up Robomongo/Robo 3T (or your favorite MongoDB client) and let’s get started!

Robo 3T displaying the Interactions MongoDB Collection

Get a list of unique Campaign IDs of EXM Email Campaigns:

db.getCollection('Interactions').distinct('CampaignId', {"Pages.PageEvents.Name":"Email Sent"})

Get a list of Interactions by EXM Message ID

db.getCollection('Interactions').find(
{"Pages.PageEvents.Data":{$regex: 'MessageId":"e78d9271-5074-46a9-b59c-c2e3b8897e93'}}
)

Get a list of all EXM Interactions for a specific email

db.getCollection('Interactions').find(
{ $and: [
{"Pages.PageEvents.Name":"Campaign"},
{"Pages.PageEvents.Data":{$regex: 'Email":"test@mail.com'}}
]}
)

Get a list of Interactions by EXM Message Id and the user interaction

Email Sent:

db.getCollection('Interactions').find(
{ $and: [
{"Pages.PageEvents.Name":"Email Sent"},
{"Pages.PageEvents.Data":{$regex: 'MessageId":"e78d9271-5074-46a9-b59c-c2e3b8897e93'}}
]}
)

Email Opened:

db.getCollection('Interactions').find(
{ $and: [
{"Pages.PageEvents.Name":”Email Opened”},
{"Pages.PageEvents.Data":{$regex: 'MessageId":"e78d9271-5074-46a9-b59c-c2e3b8897e93'}}
]}
)

Email Clicked:

db.getCollection('Interactions').find(
{ $and: [
{"Pages.PageEvents.Name":{$regex: 'Click Email Link'}},
{"Pages.PageEvents.Data":{$regex: 'MessageId":"e78d9271-5074-46a9-b59c-c2e3b8897e93'}}
]}
)

As you can see, data-mining EXM information from MongoDB is pretty straight forward once we know what we are looking for inside the Interactions collection inside the Sitecore Analytics database in MongoDB.

Additional tip!
If we need to export these interactions in csv format, we can create a toCSV function in Robomongo by creating a .robomongorc.js file in Robomongo’s home directory with the following code. After restarting Robomongo, we can add .toCSV() at the end of any of the queries to get a CSV file with our results!

DBQuery.prototype.toCSV = function(deliminator, textQualifier) 
{
var count = -1;
var headers = [];
var data = {};
var cursor = this;
deliminator = deliminator == null ? ',' : deliminator;
textQualifier = textQualifier == null ? '\"' : textQualifier;

while (cursor.hasNext()) {
    var array = new Array(cursor.next());
    count++;
    for (var index in array[0]) {
        if (headers.indexOf(index) == -1) {
            headers.push(index);
        }
    }
    for (var i = 0; i < array.length; i++) {
        for (var index in array[i]) {
            data[count + '_' + index] = array[i][index];
        }
    }
}

var line = '';
for (var index in headers) {
    line += textQualifier + headers[index] + textQualifier + deliminator;
}

line = line.slice(0, -1);
print(line);
for (var i = 0; i < count + 1; i++) {

    var line = '';
    var cell = '';
    for (var j = 0; j < headers.length; j++) {
        cell = data[i + '_' + headers[j]];
        if (cell == undefined) cell = '';
        line += textQualifier + cell + textQualifier + deliminator;
    }
    line = line.slice(0, -1);
    print(line);
}
}

One Reply to “DATA-MINING CONTACT ACTIVITY FROM SITECORE EXM CAMPAIGNS – PART 2”

Leave a Reply

Your email address will not be published. Required fields are marked *