Inbound Email Report Data Model
Contact Expert v6.1 for Skype for Business Server
These measures are based on the ACEReport.bi.EmailCases view that is finally reading most of it's data from **ACESystem.dbo.EMAILIN **table. Some data as handling time and business tag are retrieved from other tables.
Inbound email report package measure descriptions
| Measure name | Description | Group | Format |
|---|---|---|---|
| Closed 1 day after receiving | Number of emails closed on the day next to the receive day. This is not the same as closed within 48 hours! | EmailCases | #,0 |
| Closed 2 days after receiving | Number of emails closed 2 days later after receive day, | EmailCases | #,0 |
| Closed 3 days after receiving | Number of emails closed 3 days later after receive day, | EmailCases | #,0 |
| Closed in 1_2 SLA | Number of email cases closed over the threshold but below 2 * threshold: "Service level (hours)" | EmailCases | #,0 |
| Closed in 1_2 SLA (%) | [Closed in 1_2 SLA] / [Email cases in Closed state] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Closed in 2_3 SLA | Number of email cases closed over 2 * threshold but below 3 * threshold: "Service level (hours)" | EmailCases | #,0 |
| Closed in 2_3 SLA (%) | [Closed in 2_3 SLA] / [Email cases in Closed state] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Closed in 3_4 SLA | Number of email cases closed over 3 * threshold but below 4 * threshold: "Service level (hours)" | EmailCases | #,0 |
| Closed in 3_4 SLA (%) | [Closed in 3_4 SLA] / [Email cases in Closed state] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Closed in 4_5 SLA | Number of email cases closed over 4 * threshold but below 5 * threshold: "Service level (hours)" | EmailCases | #,0 |
| Closed in 4_5 SLA (%) | [Closed in 4_5 SLA] / [Email cases in Closed state] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Closed in SLA | Number of email cases closed within the threshold defined on the Parameters tab: "Service level (hours)" | EmailCases | #,0 |
| Closed in SLA (%) | [Closed in SLA] / [Email cases in Closed state] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Closed more than 3 days after receiving | Number of emails closed more than 3 days later after receive day, | EmailCases | #,0 |
| Closed on receive date | Number of emails closed on the receive day, i.e. on the same day. This is not the same as closed within 24 hours! | EmailCases | #,0 |
| Closed over 5 SLA | Number of email cases closed over 5 * the threshold defined on the Parameters tab: "Service level (hours)" | EmailCases | #,0 |
| Closed over 5 SLA (%) | [Closed over 5 SLA] / [Email cases in Closed state] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Closed over SLA | Number of email cases closed over the threshold defined on the Parameters tab: "Service level (hours)" | EmailCases | #,0 |
| Closed over SLA (%) | [Closed over SLA] / [Email cases in Closed state] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Completion time (hh:mm:ss) | Time spent (in hours) between receiving the email and closing its case by an agent in hours:minutes:seconds format. | EmailCases | #,0 |
| Completion time (hours) | Time spent (in hours) between receiving the email and closing its case by an agent = [Completion time (in seconds)] * 3600. Closing the case does not necessarily mean that a reply mail had been sent (junk mail etc.). | EmailCases | #,0.0 |
| Email cases closed last year | Number of email cases closed in the previous year, i.e. in the year prior of running the report. | EmailCases | #,0 |
| Email cases closed this year | Number of email cases closed in this year, i.e. in the year of running the report. | EmailCases | #,0 |
| Email cases in Closed state | Emails that had been already processed by the agents. It does not mean that the agent had sent a reply. He might have closed the task without sending a reply email (junk mail etc.) | EmailCases | #,0 |
| Email cases in Closed state (%) | [Email cases in Closed state] / [Email cases] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Email cases in New state | Emails that had not been seen by an agent yet, i.e. they are "unread" emails. | EmailCases | #,0 |
| Email cases in New state (%) | [Email cases in New state] / [Email cases] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Email cases in Open state | Number of emails not yet delivered to agents, more precisely = [Email cases in New state] + [Email cases in Processing state] + [Email cases in Unknown state] | EmailCases | #,0 |
| Email cases in Open state (%) | [Email cases in Open state] / [Email cases] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Email cases in Processing state | Emails that are currently under process by an agent. This number should be zero outside of working hours. Emails stuck in processing state should be checked by an administrator, | EmailCases | #,0 |
| Email cases in Processing state (%) | [Email cases in Processing state] / [Email cases] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Email cases in Unknown state | Emails that are in a status not clearly identified by reporting. This number should be zero for current emails. Unknown status might sign that the customer or the campaign had been deleted from the database. | EmailCases | #,0 |
| Email cases in Unknown state (%) | [Email cases in Unknown state] / [Email cases] * 100 % | EmailCases | 0.0 %;-0.0 %;0.0 % |
| Email tasks | Number of email tasks. An email might have been rescheduled and handled multiple times by the same or by different agents until the case is finally closed (with either sending zero, one or multiple replies). | EmailCases | #,0 |
| Email cases | Number of customer emails received into Contact Expert. Rescheduled or transferred emails are counted as a single email. | EmailCases | #,0 |
| Handle time (hh:mm:ss) | Time agents spent dealing with the email case. It is the sum of their task times in hours:minutes:seconds format. It excludes the time the email spent in queue or waited in rescheduled status. | EmailCases | #,0 |
| Handle time (sec) | Time (in seconds) agents spent dealing with the email case. It is the sum of their task times. It excludes the time the email spent in queue or waited in rescheduled status. | EmailCases | #,0 |
Dimensions
Dimensions are data based on which your facts can be sliced, grouped. broken down. The following dimensional data are available in this report package:
- ReceiveDate
- CompletionDate
- Agents
- Campaigns
When building reports, the selection of the appropriate date reference is crucial. "Email cases" displayed against "ReceiveDate" will show emails received on specific dates. On the other hand "Email cases" displayed against "CompletionDate" will show email cases completed (either by sending reply or not, but not rescheduled) on specific dates. Graphs built the first way will possibly show non zero values for weekends (customers do send emails on Saturday and Sunday), but charts built the second way will show no values for the weekends, if the CC is closed.
For a comprehensive description of the dimension tables, see BI Reporting Dimensions.
Parameters
Global parameters are affecting the calculations of some of the measures. One does not have to go into Power Pivot and change hard wired numbers or strings in the measure calculations. Instead these parameter values can be changed directly from Excel.
Due to current MS Power Pivot limitations changes of the parameter values might become effective only after saving and re-opening the excel file.
| Parameter | Value | Description |
|---|---|---|
| Service level (hours) | 8 | Target duration the emails should be handled (in hours). Measures depending on this parameter are [Closed in SLA], [Closed in 1_2 SLA], [Closed in SLA %] and similar. |
Sample reports
Several sample reports are provided in the package:
- Summary information
- Received emails per months
- Received emails per weeks
- Handle time per queues
- Status of emails received on specific dates
- Service level of the emails received on specific dates
- Service level of email cases completed on specific dates