mail2 survey responses in List Manager

David,

The surveys are saved in local tables that are installed when you start using mail2 Surveys, so you can easily set up List Manager criteria based off those tables. They should be listed in the mail2 documentation.

Michele

The criteria set up documentation states “Criteria must either reference data entries that include constituent ID or sub-entity data entries that can be tied back to a parent entity that includes a constituent ID.”

The problem I'm running into it that none of the mail2 tables use the constituent ID. I emailed John Voight at Lynch and he told me that all of the responses are keyed on the email address of the respondent. I'm trying to find a way to tie the responses in these tables back to the constituent ID.

The criteria set up documentation states “Criteria must either reference data entries that include constituent ID or sub-entity data entries that can be tied back to a parent entity that includes a constituent ID.”

The problem I'm running into it that none of the mail2 tables use the constituent ID. I emailed John Voight at Lynch and he told me that all of the responses are keyed on the email address of the respondent. I'm trying to find a way to tie the responses in these tables back to the constituent ID.

Hi David,

We also had a back and forth with John. There should be customer_nos in LTR_M2_SURVEY_RESPONDENTS.

I take it you are creating your own local view?  You need to join LTR_M2_SURVEY_RESPONDENTS to T_EADDRESS on the email address and get your consitutent ID that way. LTR_M2_SURVEY_RESPONDENTS would join LTX_M2_SURVEY_RESPONSES on local_contact_id and that joins LTR_M2_SURVEYS on local_survey_id so you know which survey is being queried.

Bear in mind that responses are tied to the email address so it is possible more than one constituent record will be tied to a set of survey responses.  If that's OK with you, fine else, you will need to make a decision which record is used e.g. more recent constituent ID or more recent last_update_dt etc.

For some reason, the constituent ID isn't in my LTR_M2_SURVEY_RESPONDENTS table. Here are the columns it has:

• local_contact_id
• mail2_contact_id
• response_start
• response_end
• contact_email
• parent_key
• is_complete
• local_survey_id

The local_contact_id and mail2_contact_id values don't refer to the corresponding constituent id's for the email listed in the same row. I've checked this table via the client in System Tables and directly in the SQL Server.

Kevin, are you talking about using List Manager or creating a custom report?

Hi David,

Have you had a chance to create views in SQL before?  They are virtual tables which makes referencing data easier particularly if it is spread across more than one table.  This would enable you to get the survey results tied to a constituent ID (hence making the joins between all those tables).  You would call this view something like LV_PHOENIX_THEATRE_SURVEY_EVITA (if you wanted to do this for your production of Evita, for example). 

Then, in System Tables, go to T_KEYWORD to create your list criteria elements where you reference the view you have created.

If you need help with creating a view, feel free to email me.

Cheers,

Kevin

Would anyone know what would prevent the local_contact_id in LTX_M2_SURVEY_RESPONSES from being filled in when the survey is completed? There are 94 rows in the LTR_M2_SURVEY_RESPONDENTS table regarding this particular local_survey_id but only 24 DISTINCT local_contact_id #'s in LTX_M2_SURVEY_RESPONSES. The rest of the lines in the table for this particular survey have a NULL value in local_contact_id.

We have seen this and it was an issue on Mail2's end. You may want to reach out to them to make sure all is working as expected.

Travis