E-mail activities are not explicitly supported for reporting or analysis out of the box. However, the infrastructure required to generate useful reports has been provided. Therefore, queries for displaying summarized information about a particular activity would read the COUNT column of the EMLRCPTS table for a given emlpromo_id. This would give the analyzer the number of e-mail addresses to which the system attempted to send e-mail, and the information regarding the events that occurred after this e-mail was initially sent from the EMLRPTEVTS table. The following types of information can thus be created:
- Total number of e-mail messages attempted.
(the COUNT column value)
- Percentage of e-mail messages which have bounced so far.
(select count(*) from EMLRPTEVT where emlpromo_id = ID and BOUNCED is not null) / COUNT
- Percentage of e-mail messages opened so far.
(select COUNT(*) from EMLRPTEVT where emlpromo_id = ID and OPENED is not null) / COUNT
- Percentage of e-mail messages that have resulted in the recipient clicking
on a URL within the message so far, also known as a clickthrough event.
(select count(*) from EMLRPTEVT where emlpromo_id = ID and CLICKED is not null) / COUNT
- Percentage of e-mail messages that have not bounced or been opened (no
recipient action taken).
COUNT - (select count(*) from EMLRPTEVT where emlpromo_id = ID and OPENED is not null or BOUNCED is not null) / COUNT
- Percentage of e-mail messages that have been opened but not clicked on.
(select count(*) from EMLRPTEVT where emlpromo_id = ID and OPENED is not null and CLICKED is null)
In these examples, ID refers to the ID of the specific e-mail activity for which you are generating a report. Also, be aware that the examples illustrate only the pseudo SQL code required to generate the reports.
Furthermore, by using the data stored in the EMLRCPTS table, one can report exactly which users (and e-mail addresses) bounced back compared to those successfully brought to a clickthrough event. This data could be used in a future e-mail activity designed to convert a higher percentage of customers to a clickthrough event by offering better discounts or incentives, or to request users to update their e-mail addresses the next time they register (in the case of bounced e-mails), or to target more aggressively.