Sitecore ECM Details Report

Question

I am using Sitecore 6.5 ECM for my email campaign.

There are quite a number of bugs.. but somehow I managed to overcome them.

Now, ECM is working decently and it has the Engagement plan to view how many percentage is Sent, Message Opened, Message Unopened and so forth..

But my clients want a details report.. like Sent, Recipients, Opened, Clicked for every email sent.

So, I am wondering which method should I pursue to get the following report.

Should I use EmailCampaign dll APIs to get them or should I query directly from database?

I already took a look at the database. and The table names aren't easy to find out.

Has anyone done this before? Can I have some snippets to do this?

Answer 1

William - You are correct in that detailed reports like you want from ECM have not yet been done and are not available out of the box. I know that something like this will be available at some point, but I don' know when and for now, this is how you can go about things because I'm doing this exact thing right now for a client.

Basically it's something that is being done via a straight SQL call. There is an api in a EngagementAutomation dll.. but from my experience, the method signatures take objects that it's more of a pain to try to create than to just build the SQL calls myself. Rather than post up my code which is extensive.. I'll just tell you where the data is and how you can go about getting it. There is a SQL API provided for analytics, but I found it to be more confusing to try to create than to just use old school .net and sql commands myself.

My approach has been to create classes that get back information given something like the GUID for the Engagement plan or the GUID for the Message itself. There are a couple of tables in the analytics database that track this data. One is the Automation and AutomationStates which tracks Engagement Plans and the specific emails that are sent (each email sent has an entry in the AutomationStates table which links to the Automation (which is the engagement plan). The AutomationStates table is pretty key and will show the actual username of the Sitecore user that was sent the email.

Tracking things (clicks and that sort of thing) is a little tricking. Everything is done and tracked in the Pages table and the PageEvent table. It tracks opening and clicks. Opening is tracked via a RegisterEmailOpened url and clicks will have to be tracked in the UrlText field which has the Querystring included and you can parse that column for EC_AS= which points to the AutomationStateID GUID.

There's much more to this, but this should get you started and let you see how to get started on this.

Answer 2

I got some results..

just want to check with you whether they are correct?

I will get the CampaignID for the respective ECMs from Campaigns table.

Then I will get AutomationID for that CampaignID from Automation table..

After that, I will get the respective row from AutomationStates table by using the AutomationID that I got from Automation table.

The number of Rows that I get for each AutomationID is the number of emails that ECM sent.

And if the VisitorID is NULL that recipient hasn't opened the email.

If there is a value in VisitorID, that email can be counted as Opened.

For Click through, I thought I could use the StateID and StateName from AutomationStates table as I saw some old data of the StateName has value called Unproductive Visitors

But when I click through my newly sent ECM, that field doesn't change anything.

So, I am wondering whether I should user Visits table or Pages and PagesEvents table for Click through.

Please advise.

Thanks for your guidance.