Search code examples
sitecoresitecore6sitecore-dmssitecore-ecm

Sitecore ECM Details Report

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.

enter image description here

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?

Solution

  • 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.