From aa6b3363100372f847ae26f0b385a4bcdc446b32 Mon Sep 17 00:00:00 2001 From: Pradeep Nayak Date: Mon, 27 Jul 2015 23:10:26 +0530 Subject: [PATCH] CRM-16506, Fixed and improve mailing stats ---------------------------------------- * CRM-16506: Fix and improve mailing stats https://issues.civicrm.org/jira/browse/CRM-16506 --- CRM/Mailing/BAO/Mailing.php | 3 +++ CRM/Mailing/Selector/Event.php | 2 +- CRM/Report/Form/Mailing/Summary.php | 28 +++++++++++++++++++++++---- templates/CRM/Mailing/Page/Report.tpl | 5 ++++- 4 files changed, 32 insertions(+), 6 deletions(-) diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php index 2a0c25432a..be0727cf34 100644 --- a/CRM/Mailing/BAO/Mailing.php +++ b/CRM/Mailing/BAO/Mailing.php @@ -2033,6 +2033,7 @@ ORDER BY civicrm_email.is_bulkmail DESC 'unsubscribe', 'optout', 'opened', + 'total_opened', 'bounce', 'spool', ); @@ -2055,6 +2056,8 @@ ORDER BY civicrm_email.is_bulkmail DESC // CRM-1258 $row['opened'] = CRM_Mailing_Event_BAO_Opened::getTotalCount($mailing_id, $mailing->id, TRUE); $report['event_totals']['opened'] += $row['opened']; + $row['total_opened'] = CRM_Mailing_Event_BAO_Opened::getTotalCount($mailing_id, $mailing->id); + $report['event_totals']['total_opened'] += $row['total_opened']; // compute unsub total separately to discount duplicates // CRM-1783 diff --git a/CRM/Mailing/Selector/Event.php b/CRM/Mailing/Selector/Event.php index fb60eabc4c..d7a2e9fed0 100644 --- a/CRM/Mailing/Selector/Event.php +++ b/CRM/Mailing/Selector/Event.php @@ -455,7 +455,7 @@ class CRM_Mailing_Selector_Event extends CRM_Core_Selector_Base implements CRM_C 'unsubscribe' => ts('Unsubscribe Requests'), 'optout' => ts('Opt-out Requests'), 'click' => $this->_is_distinct ? ts('Unique Click-throughs') : ts('Click-throughs'), - 'opened' => $this->_is_distinct ? ts('Unique Tracked Opens') : ts('Tracked Opens'), + 'opened' => $this->_is_distinct ? ts('Unique Tracked Opens') : ts('Total Tracked Opens'), ); } return $events[$this->_event_type]; diff --git a/CRM/Report/Form/Mailing/Summary.php b/CRM/Report/Form/Mailing/Summary.php index c717bcac79..e83c3d91f2 100644 --- a/CRM/Report/Form/Mailing/Summary.php +++ b/CRM/Report/Form/Mailing/Summary.php @@ -181,12 +181,26 @@ class CRM_Report_Form_Mailing_Summary extends CRM_Report_Form { $this->_columns['civicrm_mailing_event_opened'] = array( 'dao' => 'CRM_Mailing_DAO_Mailing', 'fields' => array( + 'unique_open_count' => array( + 'name' => 'id', + 'alias' => 'mailing_event_opened_civireport', + 'dbAlias' => 'mailing_event_opened_civireport.event_queue_id', + 'title' => ts('Unique Opens'), + ), + 'unique_open_rate' => array( + 'title' => 'Unique Open Rate', + 'statistics' => array( + 'calc' => 'PERCENTAGE', + 'top' => 'civicrm_mailing_event_opened.unique_open_count', + 'base' => 'civicrm_mailing_event_delivered.delivered_count', + ), + ), 'open_count' => array( 'name' => 'event_queue_id', - 'title' => ts('Opened'), + 'title' => ts('Total Opens'), ), 'open_rate' => array( - 'title' => 'Confirmed Open Rate', + 'title' => 'Total Open Rate', 'statistics' => array( 'calc' => 'PERCENTAGE', 'top' => 'civicrm_mailing_event_opened.open_count', @@ -230,6 +244,8 @@ class CRM_Report_Form_Mailing_Summary extends CRM_Report_Form { 'unsubscribe_count' => array( 'name' => 'id', 'title' => ts('Unsubscribe'), + 'alias' => 'mailing_event_unsubscribe_civireport', + 'dbAlias' => 'mailing_event_unsubscribe_civireport.event_queue_id', ), ), ); @@ -449,14 +465,16 @@ class CRM_Report_Form_Mailing_Summary extends CRM_Report_Form { 'count' => array( 'civicrm_mailing_event_delivered_delivered_count' => ts('Delivered'), 'civicrm_mailing_event_bounce_bounce_count' => ts('Bounce'), - 'civicrm_mailing_event_opened_open_count' => ts('Opened'), + 'civicrm_mailing_event_opened_open_count' => ts('Total Opens'), + 'civicrm_mailing_event_opened_unique_open_count' => ts('Unique Opens'), 'civicrm_mailing_event_trackable_url_open_click_count' => ts('Clicks'), 'civicrm_mailing_event_unsubscribe_unsubscribe_count' => ts('Unsubscribe'), ), 'rate' => array( 'civicrm_mailing_event_delivered_accepted_rate' => ts('Accepted Rate'), 'civicrm_mailing_event_bounce_bounce_rate' => ts('Bounce Rate'), - 'civicrm_mailing_event_opened_open_rate' => ts('Confirmed Open Rate'), + 'civicrm_mailing_event_opened_open_rate' => ts('Total Open Rate'), + 'civicrm_mailing_event_opened_unique_open_rate' => ts('Unique Open Rate'), 'civicrm_mailing_event_trackable_url_open_CTR' => ts('Click through Rate'), 'civicrm_mailing_event_trackable_url_open_CTO' => ts('Click to Open Rate'), ), @@ -491,6 +509,8 @@ class CRM_Report_Form_Mailing_Summary extends CRM_Report_Form { 'open_rate', 'CTR', 'CTO', + 'unique_open_rate', + 'unique_open_count', ))) { $isError = FALSE; } diff --git a/templates/CRM/Mailing/Page/Report.tpl b/templates/CRM/Mailing/Page/Report.tpl index af9261a096..10276baf81 100644 --- a/templates/CRM/Mailing/Page/Report.tpl +++ b/templates/CRM/Mailing/Page/Report.tpl @@ -35,9 +35,12 @@ {$report.event_totals.delivered} ({$report.event_totals.delivered_rate|string_format:"%0.2f"}%) {$report.event_totals.actionlinks.delivered} {if $report.mailing.open_tracking} - {ts}Tracked Opens{/ts} + {ts}Unique Opens{/ts} {$report.event_totals.opened} {$report.event_totals.actionlinks.opened} + {ts}Total Opens{/ts} + {$report.event_totals.total_opened} + {$report.event_totals.actionlinks.opened} {/if} {if $report.mailing.url_tracking} {ts}Click-throughs{/ts} -- 2.25.1