CiviCRM Community Forums (archive)

Hi there

We do have a 3.1.5 Installation here and we'd like to create mailings based on criteria such as:

- all recipients who did or who did not open a previous mailing
- all recipients who did or who did not click an embedded link in a previous mailing

I think such a thing would be possible with the "CRM_search_mail_ID_clicktrough"?! Is this correct? Or is there another way to achieve this?

Unfortunately we cannot test this feature (CRM_search_mail_ID_clicktrough), because all we get is the following error:
warning: include_once(CRM/search/mail/ID/clicktrough.php) [function.include-once]: failed to open stream: No such file or directory in /kunden/207524_6900/test/mein.kampagnenforum.ch/sites/all/modules/civicrm/CRM/Contact/BAO/SearchCustom.php on line 77.
warning: include_once() [function.include]: Failed opening 'CRM/search/mail/ID/clicktrough.php' for inclusion (include_path='.:/kunden/207524_6900/test/mein.kampagnenforum.ch/sites/all/modules/civicrm:/kunden/207524_6900/test/mein.kampagnenforum.ch/sites/all/modules/civicrm/packages:.:/usr/local/lib/php') in /kunden/207524_6900/test/mein.kampagnenforum.ch/sites/all/modules/civicrm/CRM/Contact/BAO/SearchCustom.php on line 77.
Are there any suggestions what we could do to either resolve this issue and/or make a selection of a mailing's recipients based on their open/click-rate.

Thanks and all the best from Zurich
Roman

If you have some SQL knowledge:

SELECT DISTINCT e.contact_id FROM
civicrm_mailing_event_opened eo
JOIN civicrm_mailing_event_queue eq ON eo.event_queue_id = eq.id
JOIN civicrm_mailing_job mj ON eq.job_id = mj.id
JOIN civicrm_mailing m ON mj.mailing_id = m.id
JOIN civicrm_email e ON eq.email_id = e.id
WHERE m.id = [mailingid]

You can run another query to put the result set in a group, I have a module that let's you select a mailing and put the people who opened the emails to a group. I'll probably release it somewhere if it generate some interests.