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
0 Comments
Add your comment