Sitecore ECM op-tin and opt-out roles dates

Question

I want to know is there any way to get user subscrition / unsubscrition to email campaign ?

Is it saved in one of databases/tables in MSSQL ?

Answer 1

The user's subscription is driven by the users role, but It is possible to get the users subscriptions in ECM, You just have to use the api.

You can get the contact from the email address:

string fullName = commonDomain + "\\" + Util.AddressToUserName(username);

var contact = Contact.FromName(fullName);
var subscriptions = contact.GetSubscriptions();

Once you have a contact you can call the GetSubscriptions() method which will return the recipient lists the user is signed up to. There are a host of other methods you can call on a contact and if there is a a way to get the date unsubscribed/subscribed it will be here.

If not reflect Sitecore.EmailCampaign.dll and keep looking! There might be some extra information in the automation states table in the Analytics database. More info on automation state here:

https://www.sitecore.net/learn/blogs/technical-blogs/sitecore-magnified/posts/2013/09/ecm-automation-states-magic.aspx

Also noticed there is a method GetUnsubscribersStatistics on the Sitecore.Modules.EmailCampaign.Core.Analytics.AnalyticsHelper class. This will have the date of unsubscription.

Answer 2

If you use the approach with opting in and out being determined on the fact if user is in role, then it is stored in the aspnet_UsersInRoles table in your core database. This table does not keep the information when role was assigned to the user. That's why you cannot get information when user subscribed or unsubscribed to email campaign.

The only thing you can check is if user is in the role:

user.IsInRole(roleName)