Sample Power Pivot Report Packages
Contact Expert v6.1 for Skype for Business Server
Overview
In this chapter you will find information about the sample BI report packages created for Contact Expert. These packages are not installed by the standard CE installers, but can be downloaded from the Knowledge Base directly:
| Version | Date | Min. CE release | Change description | Notes |
|---|---|---|---|---|
| V56R20160802 | 2 Aug 2016 | CE 5.5.0 | Initial version released with CE 5.6 | |
| V57R20170407 | 7th of April 2017 | CE 5.5.0 | Nomenclature updated | |
| V59R20170703 | 10th of October 2017 | CE 5.7.1 | Call Detail report added Agent Performance report updated with break type report |
Please run the following SQL update script on BI views for versions older than Contact Expert 5.9 |
| V59R20170703 | 10th of October 2017 | CE 5.7.1 | Agent BI view's extended with CustomerID, ConversationID, SDUID, allowing to create custom report referencing interactions and customer from the agent state report Note: Newly introduced fields have to be update manually in the Excel Power Pivot Model. |
Please run the following SQL udapte script on BI views for version older than Contact Expert 5.10 |
| V59R20181128 | 28th of November 2018 | CE 5.7.1 | Agent Performance Excel file update to contain filters for 2018, 2019, 2020. The BI views take their calendar and time dimensions from separate excel files (available for download), detailed in BI Reporting Dimensions article. In case these excel files are missing from the system the date filters cannot be updated. | Please run the following SQL udapte script on BI views for version older than Contact Expert 5.10 |
Versions for CE 5.5 can be downloaded from Sample Power Pivot Report Packages.
After downloading and unzipping the file, go to page Getting started with the sample BI Excel report packages for information about how to connect the report packages to your actual Contact Expert installation.
Why calling these packages "samples"?
The Excel Power Pivot BI packages are planned to be used out-off-the-box. You just define the connection to your Contact Expert database, load you first data and go. However these packages are much more than a collection of fixed reports. The reports and even their underlying data model can be changed using all the flexibility of Microsoft Excel and Power Pivot. It is very much likely that you will want to add or remove columns from the reports, change grouping, create completely new pivot tables, charts or unstructured reports. Some of you will possibly want to create your own measures based on your specific calculations and use them in the reports.
So take these report packs as they are and use them immediately, but feel free to take them as samples and create your own custom reports based on them. We will constantly provide new version of the sample report files based on information from customers and based on changes in the Contact Expert application. We will add new articles regularly to our "How to..." collection with BI reporting tags so that you will be able to learn tips and tricks about MS BI technology and about the data structure of Contact Expert.
Brief list of the report packages
Answering different questions generally requires working with different information sets. That's why we created separate sample report packages for each identified, well defined set of questions:
- Inbound Voice Reports - information about incoming voice call interactions, including service level information, queue times, abandon rates etc.
- Inbound Email Reports - information about incoming email interactions as status, completion time etc.
- Outbound Voice Campaign Reports - information about campaign progress, business success indicators etc.
- Agent Performance Reports - about agent status reports, media independent task handling information etc.
- Call Detail Records Reports - information about call details and their outcomes
New packages will be added in later releases. The existing packages might be improved based on experiences.
Inbound Voice Reports
Use this sample package if you are interested about information about incoming call interactions.
Calls might been dropped before reaching an agent. They might be overflown based on queue conditions or if an agent is not answering the call in a predefined short period. Calls also might been transferred to another agent or consultant. Because of these the actual calls might be divided into separate overflown or transferred call segments or call legs. Be aware when you use or create reports to use the proper call or call leg based measures. For example [Calls Handled] measure counts all calls that had been answered at all, i.e. by at least one agents. On the other hand [Handled call legs] counts how many times call segments had been answered, possibly counting some transferred calls multiple times. The very first call leg generally has a special role. [Answer duration of primary call leg (%)] determines average speed to answer (ASA), but excludes transferred calls answer times from the calculation. [Answer duration (%)] on the other hand is calculated based on all call legs including transferred ones.
Further readings:
Inbound Email Reports
Use this sample package if you are interested about information about incoming email interactions.
The challenge with offline interactions like email is that they might hang in the contact center for several hours and potentially for days before handled. Emails arriving lat Friday might take 3 days to handle. Agent might even reschedule the case before finally closing it and that again could easily caused several days delay. It means that defining the date for the report is challenging. That is why these report packages uses two date reference:
- Receiving date is the date when the email was downloaded by Contact Expert from the email box defined in the email gateway
- Completion date is the date when the case is finally closed by the agent either by sending a reply or not. The process until completion might include zero or more reschedule actions.
When building reports, the selection of the appropriate date reference is crucial. "Email cases" displayed against "Receive date" will show emails received on specific dates. On the other hand "Email cases" displayed against "Completion date" 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.
Further readings:
Outbound Voice Campaign Reports
Use this sample package if you are interested how outbound voice campaigns works.
In general campaigns are fed by campaign records referring to customers and containing some additional data used by scripting. Responsible people are interested about how many of those records had been already processed, and what are the success rate of the calls regarding customer connections and other business related indicators. It adds extra complexity if data is loaded into the campaign multiple times. In this case it might be interesting to see the same results not only for the complete campaign, but also broken down by individual data loads (batches).
The report package also provides access to campaign specific script data results, but that requires some extra data definitions o the database side as the script data (CRDS) structure might vary campaign by campaign.
Further readings:
Agent Performance Reports
Use this sample package if you are interested in agent utilization and in agent performance figures.
These reports provide data about the time agents spent in different statuses. They also show number of tasks per agent and per campaigns (queues). Agent performance can also be tracked based on selected business tags.
Further readings:
Call Detail Records Reports
Use this sample package if you would like to obtain extended information on both inbound and outbound calls.
These reports provide data about calls from duration and outcome perspective, for instance the length of different call statuses (ring, call, talk duration), reason codes and business tags used for each call.
Further information: