Email Marketing Contact Data

SHANE EBERT asked on April 24, 2017 22:14

Hello,

I have succesfully launched multiple email campaigns, and can view the reports on who opened and clicked on the emails.

When I navigate to view who opened the emails, I only see their email address even though that contact has a name, phone, company associated with the contact. When I go to export the contacts, it still only has the email address when I go to export raw data.

How can I setup the ability to see the other data (name, phone, company) associated with the contacts.

Thanks

Correct Answer

SHANE EBERT answered on May 4, 2017 15:26

I got some help and found a solution

Select Distinct
ActivityType, 
ActivityTitle,
ActivityContactID AS ActivityContactIDContactID,
ContactID AS ActivityContactIDContactID,
ContactFirstName,
ContactLastName,
ContactEmail,
ContactCompanyName,
ContactBusinessPhone,
ContactMobilePhone,
ContactJobTitle,
ContactCity,
ContactZIP,
ContactStateID
FROM 
OM_Activity A
Inner Join
OM_Contact C
ON A.ActivityContactID=C.ContactID

At the end I put in

Where ActivityTitle = 'Opened marketing email 'Title of Email''

This gave me the email I wanted to show the opens from along with all the other contact info associated with the contact in Kentico.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on April 24, 2017 22:53

Assuming you're using version 10, if you have a subscriber, they need to be added to the subscriber list from a contact and not added as an individual subscriber. Look in your recipient list, it should show "Contact 'First Last'" as a subscriber name if they are a contact. If they aren't a contact it will just show their first and last name.

0 votesVote for this answer Mark as a Correct answer

SHANE EBERT answered on April 25, 2017 02:19

I am using version 10. I added the contact to a contact group with their name, phone, email, company. Then I added the contact group to "recipients" while in the email feed.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on April 27, 2017 15:19

Ok I understand what you're doing now. So when you look at the Email>Reports>Opens, that list is drawn from the Newsletter_OpenedEmail table. The table has emails addresses and issue id in it. So you'd need to go back and create a view of your own that joins the opens email field with the newsletter subscriber to get all that other info.

0 votesVote for this answer Mark as a Correct answer

SHANE EBERT answered on May 2, 2017 07:11 (last edited on May 2, 2017 07:11)

I have no SQL experience, but this is as far as I got.

I figured that a UNION ALL would work, but I keep running into an error. After doing some research, it appears that I need to list the other columns in the tables. Though, I don't even know where to find the tables with their columns. Am I on the right track?

SELECT t2.ContactID
FROM
    OM_Contact AS t2
    LEFT JOIN OM_Activitype AS t1
    ON 
            t2.ContactID = t1.ActivityContactID
WHERE t1.ActivityContactID Is Null;

SELECT ActivityContactID, ActivityType FROM OM_Activity
UNION ALL
SELECT t2.ContactID, t2.Null as ActivityType
FROM
    OM_Contact AS t2
    LEFT JOIN OM_Activity AS t1
    ON 
            t2.ContactID = t1.ActivityContactID
WHERE t1.ActivityContactID Is Null;

0 votesVote for this answer Mark as a Correct answer