Sitecore ECM详细报告(Sitecore ECM Details Report)

Sitecore ECM详细报告(Sitecore ECM Details Report)

我正在使用Sitecore 6.5 ECM进行电子邮件活动。

有很多错误..但不知怎的，我设法克服它们。

现在，ECM工作得体，并且有Engagement plan来查看已Sent percentage ， Message Opened ， Message Unopened Message Opened Message Unopened等等。

但是我的客户想要Sent详细信息报告，例如Sent ， Recipients ，已Opened ， Clicked每封发送的电子邮件。

所以，我想知道我应该采取哪种方法来获得以下报告。

我应该使用EmailCampaign dll APIs获取它们还是应该直接从database查询？

我已经看过数据库了。 并且表名不容易找到。

有没有人这样做过？ 我可以有一些片段吗？

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?

最满意答案

威廉 - 你是正确的，因为你想从ECM那里得到的详细报告还没有完成，也没有开箱即用。 我知道这样的事情会在某些时候出现，但我不知道什么时候和现在，这就是你如何处理事情，因为我现在正在为客户做这件事。

基本上它是通过直接SQL调用完成的。 EngagementAutomation dll中有一个api ..但是从我的经验来看，方法签名会使对象尝试创建更加痛苦，而不仅仅是自己构建SQL调用。 而不是发布我广泛的代码..我只会告诉你数据的位置以及如何获得它。 有一个为分析提供的SQL API，但我发现尝试创建比仅使用旧的.net和sql命令更令人困惑。

我的方法是创建一些类来获取信息，例如，参与计划的GUID或消息本身的GUID。 分析数据库中有几个表可跟踪此数据。 一个是自动化和自动化状态，它跟踪订阅计划和发送的特定电子邮件（每个发送的电子邮件在AutomationStates表中都有一个条目，该表链接到自动化（这是参与计划）.AutomationStates表非常关键，将显示发送电子邮件的Sitecore用户的实际用户名。

跟踪事物（点击和那种事情）有点欺骗。 所有内容都在Pages表和PageEvent表中完成并跟踪。 它跟踪开放和点击。 通过RegisterEmailOpened url跟踪打开，并且必须在包含Querystring的UrlText字段中跟踪点击，并且您可以解析EC_AS =指向AutomationStateID GUID的该列。

还有更多内容，但这应该让你开始，让你看看如何开始这一点。

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.