Query for the report that lists the number of alert notifications for each day (delivered not more than a week ago)
Metric Insights stores information about delivery within the week.
Use the following sample query (SQL statement) if you need report that lists the number of alert notifications (distinct emails / SMS messages / smartphone alerts) for each day (Data Source = Dashboard DB (SQL)).
But it does not give results on Alerts which were delivered more than week ago.
SELECT CAST(IFNULL(eq.created, eqdl.created) AS DATE) AS date, IF(aeen.is_alert_phone_ind IS NULL,NULL, IF(aeen.is_alert_phone_ind = 'Y', 'phone tray', IF(aeen.is_sms_ind = 'Y', 'sms', IF(aeen.is_immediate_ind = 'Y', 'immediate email', 'email digest')))) AS `delivery_method`, COUNT(*) AS amount FROM alert_event_email_notification AS aeen LEFT JOIN `email_queue` AS `eq` ON eq.email_queue_id = aeen.email_queue_id LEFT JOIN `email_queue_delete_log` AS `eqdl` ON eqdl.email_queue_id = aeen.email_queue_id WHERE eq.created IS NOT NULL OR eqdl.created IS NOT NULL AND aeen.user_email_notification_enabled_ind='Y' GROUP BY 1, 2;
Click to copy
Add your comment