CiviMail Mailing Summary - Filtering and Taking Action from Advanced Search, and Linking to Mailing Reports

This issue is linked to BlueBird 4718. Please track hours at: http://senatedev.senate.state.ny.us/issues/show/4718
--------------------------------------------------------------------------------------------------------------------------------------------------------
These improvements are designed to give users the ability to search for contacts within various types of mailing results, take action on those contacts, and link the Mailing Summary with existing CiviReport mailing reports. For example, a user may want to send a follow-up mailing to recipients who clicked a URL. Or they might want to send (postal) letter to recipients whose email address is bouncing. etc.

===================================================
1. Add links to Advanced Search from the Mailing Summary Page
===================================================
Clicking the 'Report' action from Scheduled and Sent Mailings listing takes you to the mailing summary page ("CiviMail Report: $mailingTitle") - civicrm/mailing/report?mid=78&reset=1. The Delivery Summary section of this report contains 8 rows with links to simple lists of contacts + email addresses (e.g. civicrm/mailing/report/event?event=opened&mid=78&force=1). These lists are sortable but there's no way to filter them, or search / take action - so they are of limited usefulness.

1.1 Change field type for "mailing_name" field in Advanced Search "Mailings" pane to use the crmasmSelect multiple select widget. Probably should change field name to "mailing_id". The option list for this select are all mailings with Completed, Running or Cancelled status. Mailing should be sorted by mailing 'name' column and the option labels should include Completed Date (mailing_job.end_date). Example: "CiviCRM SF Meetup Invite :: Jan 28, 2012".

1.2 Modify Advanced Search query code to support querying against 1 or more specific mailing ID's rather than match by full or partial mailing name string.

1.3 Add a new single checkbox filter to the Mailing Pane - "Unsubscribe Requests" (mailing_unsubscribe) When checked this filter returns any contacts who have unsubscribed in response to a mailing included in the search query (by mailing id or date range). This should use the same select criteria as currently used for CRM/Mailing/Page/Event with event=unsubscribe (but not include opt_out events).

1.4 Add a new single checkbox filter to the Mailing Pane - "Opt-out Requests" (mailing_optout) When checked this filter returns any contacts who have unsubscribed or opted-out in response to a mailing included in the search query (by mailing id or date range). This should use the same select criteria as currently used for CRM/Mailing/Page/Event with event=unsubscribe (but include opt_out events only).

1.5 Add a new single checkbox filter to the Mailing Pane - "Forwards" (mailing_forward) When checked this filter returns any contacts who have forwarded a mailing included in the search query (by mailing id or date range). This should use the same select criteria as currently used for CRM/Mailing/Page/Event with event=forward.

1.6 Allow all filters in Mailing Pane to be passed into Advanced Search via URL (GET parameters) - i.e. &mailing_delivery_status=Y (or N). This includes:

• mailing_id
• mailing_date_low_display
• mailing_date_high_display
• mailing_delivery_status
• mailing_open_status
• mailing_reply_status
• mailing_click_status
• mailing_unsubscribe
• mailing_optout
• mailing_forward

• • • NOTE: I noticed that the values for the mailing_delivery_status radio buttons seem backwards. Successful has value of "N", and Bounced has value of "Y". Please check whether this is actually a bug in the code or ?? If the filter is actually looking for bounces, and "N" means "not bounced", then consider renaming the field to "bounce_status".

1.7. Modify Delivery Summary table statistics (CRM/Mailing/Page/Report) to separate unsubscribe requests from optout requests. Currently these are combined under the "Unsubscribe Requests" statistics. This means adding a row to this table to display Optout Requests, and I think you'll need to modify the query used to get the count for Unsubscribe Requests (since it's probably counting unsubs and opt outs and combining the totals).

1.8 Create links in Delivery Summary (CRM/Mailing/Page/Report) to a corresponding CiviReport (see #2 below), AND to Advanced Search. Screenshot is attached.

• Add a 3rd column to the table. For the first 9 rows this column contains two action-item links: Report | Advanced Search (3rd column is empty for last 4 rows, eg. Scheduled Date, Status, Start Date, End Date)
• Advanced Search link is suppressed if user does not have at least 'view all contacts' permission
• Advanced Search link passes mailing_id, and other appropriate parameters + force=1 - such that Advanced Search will return the corresponding contact records. Example: Clicking Advanced Search in the "Tracked Opens" row passes mailing_id + mailing_open_status=Y.

NOTE: Not sure how to deal with query params for the 1st row - "Intended Recipients" - Advanced Search link?? Might make sense to implement third possible value for mailing_delivery_status - A=All ??

• Report link passes appropriate query string parameters to the corresponding CiviReport (see section 2 below)

=================================================================================
2. Link Delivery Summary Statistics to New and Existing CiviReport Mailing Reports and Fix Report Bugs
=================================================================================

2.1 Create new report - "Mail Detail Report" to provide reporting on Intended and Successful Deliveries, Unsubscribes and Opt-outs, Replies and Forwards

Report Columns:

• Contact Name (required) - please grab sort_name for this and link to contact/view.
• Email (required)
• Phone
• Mailing (default = checked)
• Delivery Status (default=checked)
• Unsubscribe (default=checked)
• Opt-out (default=checked)
• Replied
• Forwarded to Email

Filters:

• Contact Name
• Mailing (sort by Mailing Name and include mailing_job.end_date - as in crmasmSelect above) - or we could implement the same crmasmSelect here ??
  • Fix the Mailing select widget for all mailing reports in the same way. Currently they are sorted from oldest to newest (which is not too useful since new mailings are on the bottom of the list), and they don't show mailing date.
• Email (Recipient)
• Delivery Status : any, Successful, Bounced
• Unsubscribed: any, Yes, No
• Opted-out: any, Yes, No
• Replied: any, Yes, No
• Forwarded: any, Yes, No

2.2 Link the "Report" action in the following rows in the Delivery Summary table (CRM/Mailing/Page/Report) to the new "Mail Detail Report", passing the query string (GET) parameters listed to filter the report properly:

• Intended Recipients - filter params: mailing_id, delivery_status=any
• Successful Deliveries - filter params: mailing_id, delivery_status=Successful (or 1 ?? depending on filter value)
• Forwards - fiter parms: mailing_id, forward=Yes (or 1 ?? depending on filter value)
• Replies - fiter parms: mailing_id, reply=Yes (or 1 ?? depending on filter value)
• Unsubscribe Requests - fiter parms: mailing_id, unsubscribe=Yes (or 1 ?? depending on filter value)
• Opt-out Requests - fiter parms: mailing_id, optout=Yes (or 1 ?? depending on filter value)

2.3 Link the report action for rows that already have a corresponding CiviReport, and verify that the filters are working properly.
2.3.1 "Tracked Opens" Report action links to Mail Opened Report (Report/Form/Mailing/Opened.php)

• replace required First and Last Name columns in this report with required Contact Name which uses sort_name and links to contact/view (same as new Mailing Detail report above, and most of our other reports).
• fix the query to return UNIQUE OPENS per mailing (i.e. do a DISTINCT on either contact_id or email_id from event queue - not sure which will make sense in the query). Currently we include a row for each event_opened even though users will "open" the same email several times just by browsing through emails in their email client. The report should only show 0 or 1 open per recipient.
• fix Mailing Name select filter (as in Mail Detail report above)

2.3.2 "Click-throughs" Report action links to Mail Clickthrough Report (Report/Form/Mailing/Clicks.php)

• replace required First and Last Name columns in this report with required Contact Name which uses sort_name and links to contact/view (same as new Mailing Detail report above, and most of our other reports).
• fix Mailing Name select filter (as in Mail Detail report above)

2.3.3 "Bounces" Report action links to Mail Bounce Report (Report/Form/Mailing/Bounce.php)

• replace required First and Last Name columns in this report with required Contact Name which uses sort_name and links to contact/view (same as new Mailing Detail report above, and most of our other reports).
• fix Mailing Name select filter (as in Mail Detail report above)

2.4 Once we have the Report and Search links in place and tested, we can remove the links in the Delivery Summary table to the deprecated mailing/report/event pages. These pages will give us a pretty nice way to double-check the new Search and Report link counts - so we should keep them in place until after the 1st round of QA.