Hi there,
I am building a report that will be fed into a CRM to identify which users has received which newsletter. In order to do that, I created the followign query:
SELECT TOP (200) EmailRecipient.RecipientId, EmailRecipient.RecipientName, EmailRecipient.RecipientEmailAddress, EmailMessage.MessageId, EmailMessage.MessageSubject, EmailAction.ActionTimestamp, EmailAction.ActionType
FROM EmailAction INNER JOIN
EmailMessage ON EmailAction.ActionMessageId = EmailMessage.MessageId INNER JOIN
EmailRecipient ON EmailAction.ActionRecipientId = EmailRecipient.RecipientId
This returns data like this:
RecipientId RecipientName RecipientEmailAddress MessageId MessageSubject ActionTimestamp ActionType
----------- -----------------------------------------------------------------------------------------------
1 My Name me@example.com 6 Email name 2015-04-06 13:36:41.200 EmailMessaging_Opened
which seems to be exactly what I want.
A few questions:
1. Is this query indeed correct?
2. What are the options for ActionType? I have seen EmailMessaging_Opened and EmailMarketing_Unsubscribe. Are there others?
3. What are the *String columns used for in the EmailAction table?
4. Where do I find other information such as the device the email was opened on?
Thanks in advance.
Imar