Power BI Technical Overview
Calculations
The Calculation folder contains a range of calculations used in the PowerBI template, divided into categories for easier access. In the following section, we will explain the sub-folders and calculations.
Classification
| Field | Type | Description |
|---|---|---|
| Classification Rate | Measure: Decimal |
The percentage of conversations classified within this group compared to the total classified conversations |
| Dialogue Classified | Measure: Integer |
The total number of conversations that have been classified |
| Percentage of Total Classifications | Measure: Decimal |
The percentage of conversations classified compared to the total amount of conversations that are accepted. |
Dialogue Calculations
Action Types
| Field | Type | Description |
|---|---|---|
| Accepted Dialogue | Measure: Integer |
The total number of conversation that have the action type Accepted |
| CallBack CallBack, an IVR menu feature for voice, enables the customer to leave his / her number to be called back by an available agent during business hours. Dialogue | Measure: Integer |
The total number of conversation that have the action type Callback. This means the conversation ended in the CallBack or the Queued Callback. |
| Forwarded Dialogue | Measure: Integer |
The total number of conversation that have the action type Forwarded. This means the conversation ended with a Forward Skill. |
| Missed Dialogue | Measure: Integer |
The total number of conversation that have the action type Missed. This means the conversation was queued for longer than 5 seconds (by parameter default) and disconnected. |
| NoContact Dialogue | Measure: Integer |
The total number of conversation that have the action type NoContact. This means that in an outbound conversation the customer was called, but did not accept. |
| Overflow Dialogue | Measure: Integer |
The total number of conversation that have the action type Overflow. This means the conversation was disconnected because the OverflowThreshold was reached. |
| Prompt Dialogue | Measure: Integer |
The total number of conversation that have the action type Prompt. This means the conversation ended with an Prompt. |
| Quickdrops | Measure: Integer |
The total number of conversation that have the action type Missed. This means the conversation was queued for less than 5 seconds (by parameter default) and disconnected. |
| Voicemail Dialogue | Measure: Integer |
The total number of conversation that have the action type Voicemail. This means the conversation ended with an Anywhere365 Voicemail. |
Dialogue Types
Handle Rate
| Field | Type | Description |
|---|---|---|
| Handle Rate | Measure: Percentage |
The percentage of accepted inbound or direct conversations relative to the total number of accepted and missed inbound or direct conversations combined. |
| Handle Rate Previous Period | Measure: Percentage |
This is the same as the handle rate, but for the previous period. For example, if a 30-day period is selected, it will calculate the handle rate for the 30 days prior to that. |
| Handle Rate: Change Percentage | Measure: Percentage |
The percentage difference between the Handle Rate and the Handle Rate of the Previous period. |
Misc.
| Field | Type | Description |
|---|---|---|
| Frequent Contacts | Measure: Integer |
Calculates the total amount of callers that has contacted your contact center more times than the parameter provided. By default this parameter is set to 10, but can be increased or decreased to any number. |
| Ranked Dialogue | Measure: Integer |
Calculates the amount of dialogue that was rated (Q.M.) |
| Unique Contacts | Measure: Integer |
Calculated the unique number of contact |
Skills
| Field | Type | Description |
|---|---|---|
| First Time Right | Measure: Percentage |
The percentage of accepted conversations without a Skill Change or Transferee to the total number of accepted conversations. |
| No Skill Rate | Measure: Percentage |
The percentage of conversations with actions type No Skill relative tot the total number of conversations. |
| Skill Changed Volume | Measure: Integer |
Calculates the total amount of conversations where the Skill has been changed. |
SLA
| Field | Type | Description |
|---|---|---|
| Accepted in SLA | Measure: Integer |
The number of accepted inbound and direct conversations where the queue time is less than 30 seconds (by parameter default). |
| Declined in SLA | Measure: Integer |
The number of missed inbound and direct conversations where the queue time is between 5 and 30 seconds (by parameters default). |
| Missed (SLA Calculation) | Measure: Percentage |
The percentage of inbound and direct conversations that were missed due to queue times exceeding 5 seconds (the default parameter) relative to the total number of accepted and missed inbound and direct conversations. |
| SLA | Measure: Percentage |
The percentage of inbound and direct accepted conversation where the queue time is less than 30 seconds (by parameter default) relative to the total number of accepted and missed inbound and direct conversations. Quick drops are filter out. |
| SLA 2 | Measure: Percentage |
The percentage of inbound and direct accepted conversation where the queue time between 30 and 50 seconds (by parameters default) relative to the total number of accepted and missed inbound and direct conversations. Quick drops are filter out. |
| SLA 3 | Measure: Percentage |
The percentage of inbound and direct accepted conversation where the queue time is more than 30 seconds (by parameter default) relative to the total number of accepted and missed inbound and direct conversations. Quick drops are filter out. |
| SLA: Change Percentage | Measure: Percentage |
The percentage difference between the SLA and the SLA: Previous Period, |
| SLA: Previous Period | Measure: Percentage |
This is the same as the SLA, but for the previous period. For example, if a 30-day period is selected, it will calculate the handle rate for the 30 days prior to that. |
Time Calculations
| Field | Type | Description |
|---|---|---|
|
Decline Time |
||
| Average Decline Time | Measure: Text |
The average queue time of missed conversation in a hh:mm:ss format. |
| Average Decline Change | Measure: Text |
The difference between average queue time of missed conversations of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Decline Difference in Seconds | Measure: Integer |
The difference between average queue time of missed conversations of the current and the previous period in seconds. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
|
Initial Talk Time |
||
| Average Initial Talktime | Measure: Text |
The average initial talk time in a hh:mm:ss format. |
| Average Initial Talktime Change | Measure: Text |
The difference between average initial talk time of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Average Initial Talktime Difference in Seconds | Measure: Integer |
The difference between average initial time of the current and the previous period in seconds. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Longest Initial Talktime | Measure: Text |
The longest initial talk time in a hh:mm:ss format. |
| Longest Initial Talk Time Change | Measure: Text |
The difference between longest initial talk time of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Longest Initial Talk Time Difference in Seconds | Measure: Integer |
The difference between longest initial talk time of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
|
Queue Time |
||
| Average Queue Time | Measure: Text |
The average queue time in a hh:mm:ss format. |
| Average Queue Time Change | Measure: Text |
The difference between average queue time of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Average Queue Time Difference in Seconds | Measure: Integer |
The difference between average queue time of the current and the previous period in seconds. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Longest Queue Time | Measure: Text |
The longest queue time in a hh:mm:ss format. |
| Longest Queue Time Change | Measure: Text |
The difference between longest queue time of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Longest Queue Time Difference in Seconds | Measure: Integer |
The difference between longest queue time of the current and the previous period in seconds. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
|
Session Time |
||
| Session Time | Measure: Text |
The average session time in a hh:mm:ss format. |
|
Talk Time |
||
| Total Talktime Average | Measure: Text |
The average talk time in a hh:mm:ss format. |
| Total Talktime Change | Measure: Text |
The difference between average talk time of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Total Talktime Difference in Seconds | Measure: Integer |
The difference between average time of the current and the previous period in seconds. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Talktime queuetime comparison | Measure: Percentage |
The percentage of total talk time relative to the total of queue time and talk time combined. |
|
Transfer Time |
||
| Average Transfer Time | Measure: Text |
The average transfer time (i.e. time after a transfer)in a hh:mm:ss format. |
| Average Transfer Time Change | Measure: Text |
The difference between average transfer time of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Average Transfer Time Difference in Seconds | Measure: Integer |
The difference between average transfer time of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
Transfers
| Field | Type | Description |
|---|---|---|
| Dialogue Transferred | Measure: Integer |
The total number of conversation that have been transferred to a transferee. |
| External Transfers | Measure: Integer |
The number of conversations where the transferee Uri started with +, which is the start for a E.164 The E.164 phone number format is an international (ITU) standard for dialing telephone numbers on the Public Switched Telephony Network (PSTN). Loosely formulated, only "+" and upto 15 digits (0-9) are allowed For example: +4433221100 (For number notation/display and storage see the E.123 standard) formatted phone number. |
| Frequent Transferees | Measure: Integer |
The number of unique transferees that have been transferred to more than 5 times (by parameters default). |
| Internal Transfers | Measure: Integer |
The number of conversation of all other transfers that weren't external, UCC or Other Domain. |
| Other Domain Transfers | Measure: Integer |
The number of conversations where the transferee domain didn't match the domain of the agent. |
| Transferable Dialogue | Measure: Integer |
The number of conversations that could have had a transfer. These are all conversations that had an Initial Agent. |
| Transferrate | Measure: Percentage |
The percentage of Dialogue Transferred relative to Transferable Dialogue. |
| UCC Transfers | Measure: Integer |
The number of conversations where the transferee Uri started with "sip:ucc_", which is the standard name for a UCC endpoint. |
Volume
| Field | Type | Description |
|---|---|---|
| Dialogue Volume | Measure: Integer |
The total number of conversation |
| Dialogue Volume HeatMap | Measure: Integer |
The total number of conversation used for the HeatMap visual |
| Dialogue Volume Inside Business Hours | Measure: Integer |
The total number of conversation within Business hours and outside holidays. |
| Dialogue Volume Outside Business Hours | Measure: Integer |
The total number of conversation outside Business hours or within holidays. |
Hunts
Hunt Handle Rate
| Field | Type | Description |
|---|---|---|
| Hunt Handle Rate | Measure: Percentage |
The percentage of accepted hunts relative to the total number of accepted and missed hunts combined. |
| Hunt Handle Rate Previous Period | Measure: Percentage |
This is the same as the hunt handle rate, but for the previous period. For example, if a 30-day period is selected, it will calculate the hunt rate for the 30 days prior to that. |
| Hunt Handle Rate: Change Percentage | Measure: Percentage |
The percentage difference between the Hunt Handle Rate and the Hunt Handle Rate of the Previous period. |
Hunt Related
| Field | Type | Description |
|---|---|---|
| Accepted Hunts | Measure: Integer |
The number of unique hunts that where accepted. |
| Hunt Volume | Measure: Integer |
The number of unique hunts. |
| Missed Hunts | Measure: Integer |
The number of unique hunts where the total time hunted was longer than 5 seconds (by parameters default). |
| Quickdrop Hunts | Measure: Integer |
The number of unique hunts where the total time hunted was longer less 5 seconds (by parameters default). |
SLA Hunt Calculations
| Field | Type | Description |
|---|---|---|
| Hunts Accepted in SLA1 | Measure: Integer |
The number of accepted hunts where the total time hunted is less than 30 seconds (by parameter default). |
| Hunts Accepted in SLA2 | Measure: Integer |
The number of accepted hunts where the total time hunted is between 30 and 50 seconds (by parameters default). |
| Hunts Accepted in SLA3 | Measure: Integer |
The number of accepted hunts where the total time hunted is more than 30 seconds (by parameter default). |
| SLA Hunt | Measure: Percentage |
The percentage of accepted hunts where the total time hunted is less than 30 seconds (by parameter default) relative to the total number of accepted and missed hunts. Quick drops are filter out. |
| SLA Hunt: Previous Period | Measure: Percentage |
This is the same as the SLA Hunt, but for the previous period. For example, if a 30-day period is selected, it will calculate the handle rate for the 30 days prior to that. |
| SLA Hunt: Change Percentage | Measure: Percentage |
The percentage difference between the SLA Hunt and the SLA Hunt: Previous Period, |
Time Calculations
| Field | Type | Description |
|---|---|---|
|
Hunt Time Calculations |
||
| Average Hunt Time | Measure: Text |
The average hunt time of all hunts. |
| Hunt Difference in Seconds | Measure: Integer |
The difference between average hunt time of all hunt of the current and the previous period in seconds. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Average Decline Change | Measure: Text |
The difference between average hunt time of all hunt of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Total Hunt Time | Measure: Text |
The total hunt time of all hunts. |
|
Talk Time Calculations |
||
| Average Initial Talktime | Measure: Text |
The average initial talk time in a hh:mm:ss format. |
| Average Initial Talktime Change | Measure: Text |
The difference between average initial talk time of the current and the previous period in a hh:mm:ss format. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
| Average Initial Talktime Difference in Seconds | Measure: Integer |
The difference between average initial time of the current and the previous period in seconds. For example, if a 30-day period is selected, it will show the difference with the 30 days prior to that. |
Presence
Important
By default only presence of the last 30 days are loaded, regardless of the "Previous Days" parameter during configuration.
| Field | Type | Description |
|---|---|---|
| Call Percentage | Measure: Percentage |
The percentage of time spent InCall and not formally signed out compared to the total time spent not offline or formally signed out. |
Times Formatted
| Field | Type | Description |
|---|---|---|
| Average Discharge Time | Measure: Text |
The average time spent in discharge in a hh:mm:ss format. |
| Discharge Duration | Measure: Integer |
The total time spent in discharge in seconds. |
| Discharge Time | Measure: Text |
The total time spent in discharge in a hh:mm:ss format. |
| Presence Available | Measure: Text |
The total presence time spent as available in a hh:mm:ss format. |
| Presence Away | Measure: Text |
The total presence time spent as away in a hh:mm:ss format. |
| Presence Busy | Measure: Text |
The total presence time spent as busy in a hh:mm:ss format. |
| Presence Do Not Disturb | Measure: Text |
The total presence time spent as do not disturbed in a hh:mm:ss format. |
| Presence In Call | Measure: Text |
The total presence time spent as in call in a hh:mm:ss format. |
| Presence Time Formatted | Measure: Text |
The total presence time spent in a hh:mm:ss format. |
| Presence Time Formatted Non Blanks | Measure: Text |
The total presence time spent in a hh:mm:ss format and the blank values are filtered out. |
Quality Monitoring
| Field | Type | Description |
|---|---|---|
| Not Rated QM | Measure: Integer |
The remaining percentage of the QM Rate. |
| QM Rate | Measure: Percentage |
The percentage of QM Rated Dialogues in ratio with all accepted conversations. |
| QM Rated Dialogues | Measure: Integer |
The number of unique conversations that have a quality monitoring score. |
| Rated Questions | Measure: Integer |
The number of unique Quality Monitoring questions. |
| Total Rating | Measure: Integer |
The total number of Quality Monitoring ratings. |
| Total Rating Formatted | Measure: Integer |
The total number of Quality Monitoring ratings, but when the value is blank then - is shown. |
Agents
A sip mapping table contains all the sip addresses of all agents. This table if usually referenced when retrieving the related address with a sipmap ID.
Fields
| Field |
Type |
Description |
|---|---|---|
| ID | Integer | The unique ID of a agent |
| LineURI | Text | The phone number related to the ID |
| URI | Text | The sip address of the agent |
Classifications
The classifications table contains all the classified calls. Whenever an agent classifies the call, the summary will be stored in this table. Is used only for the classification report. This table is limited to the first five levels of classification.
Fields
| Field | Type | Description |
|---|---|---|
| ClassCombined | Text |
A text field in which all classes above are combined. Is the default value for the reporting, but can be replaced with any of the above classes if the intention is to create a report on a specific level. |
| Classification Level 1 | Text |
The first level of classification. |
| Classification Level 2 | Text |
The second level of classification. |
| Classification Level 3 | Text |
The third level of classification. |
| Classification Level 4 | Text |
The fourth level of classification. |
| Classification Level 5 | Text |
The fifth level of classification. |
| Classification Timestamp | Time |
The time when the classification was created, can be used for time intelligence. |
| Correlation ID | Text |
The unique ID of the conversation. Is used to link this table to PowerBI_Dialogue. |
| Date | Date |
The date field on which the classification was created. Used for time intelligence. |
| UCC_ID | Integer |
The unique ID of the UCC in which the classification was made. |
Contacts
A sip mapping table contains all the sip addresses of all contacts. This table if usually referenced when retrieving the related address with a sipmap ID.
Fields
| Field | Type | Description |
|---|---|---|
| ID | Integer | The unique ID of a contact |
| LineURI | Text | The phone number related to the ID |
| URI | Text | The sip address of the contact |
Dates(Table)
The dates table is designed to provide a single source of data for time-based analysis. It is constructed by creating a list of dates and adding columns with helpful time-related information, such as the day of the week, month, year, or week of the year. This makes it simpler to filter graphs and reports based on the desired date range.
Calculations /Formulas
| Field | Type | Description |
|---|---|---|
| End Previous Period | Text |
This field displays the DateKey of the period immediately preceding the one currently selected. For instance, if you have chosen to load only the last 30 days, this field will show the last DateKey of the 30 days prior. |
| Start Previous Period | Text | This field displays the DateKey of the period immediately preceding the one currently selected. For instance, if you have chosen to load only the last 30 days, this field will show the first DateKey of the 30 days prior. |
Fields
| Field | Type | Description |
|---|---|---|
| Date for Filter | Text |
A combination of the date and the day name for easy filtering. |
| DateKey | Date |
Field to create relations between multiple tables. Can be used a filter on its own to filter data from multiple tables on date. |
| Day | Integer |
The day of the month of the Datekey field as a number. |
| Day Name | Text |
The day name in text of the Datekey field. |
| Day Name Abbreviation | Text |
The abbreviation of the day name to make it possible to create smaller filter cards or slicers. |
| Day of Week | Integer |
The day of the week of the Datekey field as a number. |
| Day of Year | Integer |
The day of the year of the Datekey field as a number. |
| Year | Integer |
The year of the Datekey field as number. |
| Month | Integer |
The month of the Datekey field as number. |
| Month Name | Text |
The month name of the Datekey field as a string, can be used for filters in a more user friendly way. |
| Quarter | Integer |
The quarter of the year the Datekey field is in. |
| Week of Year | Integer |
The week of the year of the Datekey field. |
| Week of Month | Integer |
The week of the month of the Datekey field. |
| Week Number | Integer |
The week number of the Datekey field. |
Details
This table contains all the information about a conversation in the Anywhere365 environment. It is used to generate the Dialogue Detail report, which shows the steps a dialogue took in the contact center. This report provides insight into the exact route taken, including which agents were hunted, which queue positions, skills, and time intelligence.
Note
This table will only be filled when the Load Call Detail parameter is TRUE.
Fields
| Field | Type | Description |
|---|---|---|
| Correlation ID | Text |
The ID of the conversation. In powerBI_Calldetails this ID is not unique, as a conversation can and will contain multiple steps. |
| Date | Date |
The date the step of the conversation took place. |
| Date time | Date time |
The date and time the step of the conversation took place. |
| Detail Type | Text |
A preset of step types that define the step. |
| Detail ID | Integer |
Unique ID of the message. |
| Detail Message | Text |
A field that contains information about the step type, such as the sip address of the agent or queueposition. |
| ImageURL | Text |
The url that retrieves the image that is linked to the step. This image is displayed in the Dialogue Detail report and needs to be dynamic as not all conversations will be the same. |
| Timestamp | Time |
The time the step was taken in the UCC. |
| UCC ID | Integer |
The unique ID of the UCC, is used to link the table to the UCC_Name table and retrieve the name of the UCC. |
Dialogue
This table is the main component of the reporting template. It stores all conversations with their summaries, as well as queuetimes, talktimes, skillnames, start and end times, summaries on times, transferees, and contact information. Most of the reports in the template use data from this table, and some calculations in other tables refer back to it.
Calculations / Formulas
| Field | Type | Description |
|---|---|---|
| Detail: Agent | Column: Text |
Matches the agent sipmap uri with the related Agents Table. Instead of using the default initialagent column you can use this column if you decide to create custom reports that combines multiple tables with different agent columns. |
| Detail: Classification | Column: Text |
The related total classification of the PowerBI_Classifications table. |
| Detail: Contact | Column: Text | Matches the contact sipmap uri with the related Contacts Table. Instead of using the default contact column you can use this column if you decide to create custom reports that combines multiple tables with different contact columns. |
| Detail: Hunt Time | Column: Text |
Formats the total of the HuntTime to a HH:MM:SS format, which is more readable for the end user. |
| Detail: Recording Text | Column: Text |
The url to the location where the recording is stored. If there is no recording present, the field will contain "No Recording Available". |
| Detail: Skill | Column: Text |
The name of the skill that was chosen in the IVR Interactive Voice Response, or IVR, is a telephone application to take orders via telephone keypad or voice through a computer. By choosing menu options the caller receives information, without the intervention of a human operator, or will be forwarded to the appropriate Agent.. If there is no Skill present, the field will contain "No Skill Chosen". |
| Missed Reason | Column: Text |
This field explains why a conversation was not answered. Possible reasons include QuickDrop (the call was dropped within 5 seconds of entering the queue), QueueDrop (the call was dropped while other people were still waiting in the queue), Missed Hunt (an agent was hunted but did not accept the call), and No Agent (the call was first in the queue but there was no agent available to answer it). |
| Rated | Column: Text |
This field shows if the conversation was scored by the Quality Monitoring or not. |
| Hold time Column | Column: Text |
Formats the Hold time to a HH:MM:SS format, which is more readable for the end user. |
| Queue time Column | Column: Text |
Formats the QueueTime to a HH:MM:SS format, which is more readable for the end user. |
| Talk time Column | Column: Text |
Formats the TalkTime to a HH:MM:SS format, which is more readable for the end user. |
Fields
| Field | Type | Description |
|---|---|---|
| Accepted | Integer |
A field that is either empty or 1 based on if a conversation was accepted. |
| Action Type | Text |
A text field that tells you what happened with the conversation. |
| Caller | Text |
The phonenumber or sip address of the caller. |
| Callersipmapid | Integer |
The unique sipmap id of the caller. Can be used to link the table to the sipmap table for a centralized filter option. |
| Correlationid | Text |
The unique ID of the conversation. Is used to link this table to other tables. |
| Date | Date |
The date of the starttime of the conversation. Is used to link to the date table to perform time intelligence. |
| Dialogue Type | Text |
The type of dialogue the conversation is classified as. |
| Endpoint URI | Text |
The uri of the endpoint the conversation was received on. |
| Forwarded | Integer |
A field that is either empty or 1 based on if a conversation was forwarded. |
| Index | Integer |
A unique number that is used to sort correlationID based on start time in table where you do not want the start time to be displayed. |
| Initial Agent | Text |
The sipaddress or the phone number of the agent that accepted the conversation. |
| Initial Agent Sip ID | Integer |
The unique ID of the agent that accepted the conversation, which is used to retrieve the agent name from the Sipmap table. |
| Initial Talk Time | Integer |
The duration of the first conversation with an agent. In case the call is forwarded, the talktime that took place after the forward is not included. |
| Initial Transferee | Text |
The sipaddress or the phone number of the agent that received the first transfer. |
| Initial Transferee Sip ID | Integer |
The unique id of the transferee. |
| Missed | Integer |
A field that is either empty or 1 based on if a conversation was Missed. |
| Modality Type | Text |
The modality type of the conversation. |
| NoContact | Integer |
A field that is either empty or 1 based on if a conversation was outbound and was not picked up by the contact. |
| NoSkill | Integer |
A field that is either empty or 1 based on if a conversation was ended before the contact chose a Skill. |
| OutsideBusinessHours | Integer |
A field that is either empty or 1 based on if a conversation was received outside business hours or inside a holiday. |
| QM Value | Integer |
The value the contact gave to the conversation after filling in the Q.M. at the end of a conversation. |
| Quality Monitoring Value Detail | Integer |
Same as QM Value, but if the value is blank it will show -. |
| Recording Location | Text |
The url to the location where the recording is stored. If there is no recording present, the field will be empty. |
| Seconds After Transfer | Integer |
The total time in seconds that the conversation spent after transfer till finish. |
| Seconds in IVR | Integer |
An integer of the duration the conversation spent in the IVR, in seconds. |
| Seconds in Queue | Integer |
The time in seconds that the conversation was in the queue. |
| Skill Changed | Text |
The name of the skill that the conversation was transferred to. |
| Skill Chosen | Text |
The name of the skill that was chosen in the IVR. |
| Skill Final | Text |
The name of the skill where the conversation ended. |
| Skill ID | Text |
The UCC ID and name of the skill. Can be used to link to the Skill table. |
| Skilltype | Text |
A combination of the dialoguetype and the modality type. |
| Supervisor | Text |
The sip address of the supervisor of the agent that accepted the conversation. |
| Supervisor Sip Id | Integer |
The unique ID of the sip address of the supervisor. |
| Time Accepted | Date Time |
The date time stamp a conversation was accepted by an agent. |
| Time Ended | Date Time |
The date time stamp that the conversation was ended on. |
| Time Entered Queue | Date Time |
The date time stamp when a conversation entered the queue. |
| Time Started | Time |
The time stamp of the start of the conversation. |
| Time Transferred | Time |
The date time stamp a conversation was accepted by the transferee. |
| Total Session Time | Integer |
The total time in seconds the conversation lasted from start till finish. |
| Total Talk Time | Integer |
The total talk time in seconds, including talk time after transfers. |
| Total Hold Time | Integer |
The total time the conversation was put on hold in seconds. |
| Transfer Type | Text |
The type of transfer of the conversation (UCC, internal, external or other). |
| UCC ID | Integer |
The unique ID of the UCC that the conversation took place in. |
| Voicemail | Integer |
A field that is either empty or 1 based on if a conversation was pickup by a voicemail. |
| Voicemail Location | Text |
The location of the voicemail. |
Discharge
The discharge table shows all the discharge the agents have received.
Fields
| Field | Type | Description |
|---|---|---|
| Agent Sip ID | Integer |
The unique ID of the agent that had the discharge, which is used to retrieve the agent name from the Sipmap table. |
| Correlation ID | Text |
The unique ID of the conversation. Is used to link this table to other tables. |
| Discharge Duration | Decimal |
The duration in seconds that the discharge took. |
| Discharge Starttime | Date Time |
The date time stamp the discharge started. |
| Discharge UCC ID | Integer |
The unique ID of the UCC that the conversation took place in. |
Elements
The elements table is a hidden table in the online environment and by default only visible in the tables or data model tab in Power BI Desktop. This table is only used to retrieve the latest icon set for report navigation and was created as Power BI by default does not support custom icons that switch whenever the user changes themes.
Fields
| Field | Type | Description |
|---|---|---|
| Element | Text |
Name of the element/icon that is used as display in the timeline. |
| Link | Text |
The url where the image is stored and retrieved from. |
| Theme | Text |
A field that is used as a filter to switch between dark and light theme. |
Hunts
This table contains all information concerning the hunts.
Note
A call can have multiple hunts, to multiple agents, before it is accepted or missed. Therefor the total amount of hunts can differentiate from the total amount of conversations in PowerBI_Dialogue.
Fields
| Field | Type | Description |
|---|---|---|
| Accepted | Integer |
Contains a 1 if the hunt was accepted and is empty it is missed. |
| Actiontype | Text |
Either Accepted, Missed or Quickdrop depending on result. |
| Agent Sip Id | Integer |
The unique ID of the agent that was hunted. |
| Caller Id | Integer |
The unique ID of the caller for which the agent was hunted. |
| ContactSipMapId | Integer |
The unique ID of the caller for which the agent was hunted, but then retrieved from the Dialogue Table. |
| Correlation ID | Text |
The unique ID of the conversation, not the ID of the hunt. |
| Date | Date |
The date stamp when the hunt took place. |
| Dialogue Type | Text |
The type of dialogue the conversation is classified as. |
| Hold Time | Integer |
The total time the conversation was put on hold in seconds. |
| Hunt Duration | Time |
The total hunttime formatted to HH:MM:SS. |
| Hunt ID | Integer |
The unique ID of the hunt. |
| Hunt Time | Integer |
The time in seconds the hunt took before begin accepted or missed. |
| Hunt Timestamp | Time |
The time stamp of the start of the hunt. |
| Initial Talk Time Conversation | Integer |
The initial talk time in seconds of the conversation after it was accepted, in seconds. |
| Initial Talktime | Time |
The initial talktime formatted to HH:MM:SS. |
| Initial Transferee Sip ID | Integer |
The unique id of the transferee. |
| Modality Type | Text |
The modality type of the conversation. |
| Skill | Text |
The name of the skill the hunt took place in. |
| UCC_ID | Integer |
The unique ID of the UCC that the conversation took place in. |
Presence
This table contains all information concerning the presence information.
Note
This table will only be filled when the Load Presence parameter is TRUE.
Important
By default only presence of the last 30 days are loaded, regardless of the "Previous Days" parameter during configuration.
Fields
| Field | Type | Description |
|---|---|---|
| Agent Sip Id | Integer |
The unique ID of the agent. |
| Date | Date |
The date stamp of the presence change. |
| Device Type | Text |
The device type the presence changed from. |
| Duration in seconds | Integer |
The duration in seconds that the presence was active. |
| Duration in Time | Integer |
The duration that the presence was active in a hh:mm:ss format. |
| Endtime | Time |
The time stamp of the end time of the presence. |
| Endtime + Date | Date Time |
The date time stamp of the end time of the presence. |
| Formal State | Text |
The type of signin the agent was at that moment. Can be one of three options: Formal – Not Signed in, Formal – Signed and informal. |
| Location | Text |
No longer used. |
| Presence | Text | |
| Presence Row ID | Integer |
The ID of the presence change. Is unique for a presence change. |
| Presence UCC ID | Integer |
The unique ID of the UCC that the conversation took place in |
| ReasonCodeName | Text |
The reasoncode that was connected to the presence change |
| Starttime | Time |
The time stamp of the start time of the presence |
| Starttime + Date | Date Time |
The date time stamp of the start time of the presence |
QM
This table stores information related to Quality Monitoring, enabling users to view the Quality Monitoring Report. Even if the entire Quality Monitoring process is not completed, individual ratings are still available, as each rating is stored separately.
Fields
| Field | Type | Description |
|---|---|---|
| correlationid | Text |
The unique ID of the conversation. Is used to link this table to other tables. |
| Index | Integer |
The unique ID of the rating. |
| Question | Text |
If available the Text of the Quality Monitoring question is stored here. |
| QuestionNumber | Integer |
If available the Question order number is stored here. |
| Result | Integer |
The score the customer has given to the Quality Monitoring question. |
| time | Date time |
The date time stamp when the rating was received. |
| ucc_id | Integer |
The unique ID of the UCC where the rating conversation took place in. |
Skills
This table stored all information related to Skills and can be used for creating visuals and tables about Skills.
Fields
| Field | Type | Description |
|---|---|---|
| link | Text |
The UCC Id and Name of the Skill. Can be used to link to the Dialogue Table. |
| Rank | Integer |
The number of times this Skill occurs in the Dialogue table. |
| Ranking | Integer |
A ranking of skills is determined by the number of times each skill was chosen. If two skills have the same number of selections, they will receive the same rank. This ranking can be used to identify which skill is the most popular. |
| skillchosen | Text |
The name of the Skill |
| UCC Name | Text |
The name of the UCC where Skill was used on. |
| ucc_id | Text |
The unique ID of the UCC where Skill was used on. |
TimeTable
The timetable is designed to provide a single source of data for time-based analysis. It is constructed by creating a list of timestamps and adding columns with helpful time-related information, such as the hour, quarter of an hour or daypart. This makes it simpler to filter graphs and reports based on the desired time range.
Fields
| Field | Type | Description |
|---|---|---|
| Daypart | Text |
This fields show in which daypart the timestamp is. It can be Night, Morning, Afternoon or Evening. |
| Hour | Text |
This fields show in which hour the timestamp is. It is shown in a 2 digit format |
| Hour:Minutes | Text |
This field shows in which hour the timestamp is. It is shown in a hh:00 format. |
| HourKey | Integer |
This fields show in which hour the timestamp is. Can be used for linking. |
| HourText | Text |
This fields show in which hour the timestamp is. It is shown in a 2 digit format |
| Minute | Text |
This field shows in which hour and minute the timestamp is. It is shown in a hh:mm format. |
| MinuteKey | Integer |
This field shows in which minute the timestamp is. It is shown in a 2 digit format. |
| Quarter of an Hour | Text |
This fields shows in which quarter of an hour the timestamp is. It can be 0, 15, 30, or 45 |
| Second | Text |
This field shows in which hour, minute and second the timestamp is. It is shown in a hh:mm:ss format. |
| SecondKey | Integer |
This field shows in which second of a minute the timestamp is. Can be used for linking. |
| Time | Text |
This field shows in which hour, minute and second the timestamp is. It is shown in a hh:mm:ss format. |
| TimeKey | Integer |
This field shows in which second of the day the timestamp is. Can be used for linking. |
Transcript
Note
This PowerBI template does not include the transcript for Text session in the Anywhere365 WebAgent, which is available in the Dialogue Manager Database. This data for this table is only generated for WebChat for Skype for Business.
This table stores the chat transcript history, if there is any. This allows users to view the dialogue detail report for a specific chat by drilling down on the chat correlation. If no chats are present, this table and the chat transcript report can be removed.
Calculations /Formulas
| Field | Type | Description |
|---|---|---|
| Message Count Agent | Measure: Integer |
The total amount of messages the agent sent for the selected chat(s). Filters out system message. |
| Message Count Customer | Measure: Integer |
The total amount of messages the customer sent for the selected chat(s). Filters out system message. |
| Response Duration Agent | Measure: Integer |
The average in seconds of the Duration Response column, filters only on Agent messages. |
| Response Duration Contact | Measure: Integer |
The average in seconds of the Duration Response column, filters only on Customer messages. |
| Customer | Column: Text |
A column that simply generates the name of the customer for a chat message. |
| Duration Response | Column: Integer |
A calculation for the time between the message and the last message from the other party. Used for average response time in chat transcript reporting. |
| Agent Response Formatted | Measure: Text |
Reformat the Response Duration Agent in a hh:mm:ss format. |
| Customer Response Formatted | Measure: Text |
Reformat the Response Duration Customer in a hh:mm:ss format. |
| ColorCoding | Column: Text |
A column that is used to store the color code of the background. Power BI allows the color of a text field to be determined by a calculation. |
Fields
| Field | Type | Description |
|---|---|---|
| AgentIcon | Text |
The URL for loading a placeholder image of the Agent in the visual. |
| Chat Timestamp | Time |
The time value when the message was sent. |
| chatConversationID | Integer |
The ID of the chat. |
| CorrelationID | Text |
The ID of the conversation, in order for it to link to PowerBI_Dialogue. |
| CustomerIcon | Text |
The URL for loading a placeholder image of the Customer in the visual. |
| IsCustomer | Boolean |
A Boolean field that tells you if the message was sent by the customer or not. Is used to split the customer and agent from the participant column. |
| Participant | Text |
The name of the participant who sent the chat message. |
| Participant Sip |
Text |
Related sip uri based on the sipmap id. |
| Participant Sip ID | Integer |
The unique id of the participant. |
| Transcript ID | Integer |
The unique ID of the row. |
| Transcript Message | Text |
The text message that was sent by the participant during the chat. Also contains the IDR Interactive Dialogue Response (IDR) is an application to take orders via keypad or button through a computer. By choosing menu options the contact receives information, without the intervention of a human operator, or will be forwarded to the appropriate Agent. questions that the customer answers before the start of the chat, and the browser information. |
Transferee
A sip mapping table contains all the sip addresses of all transferees. This table if usually referenced when retrieving the related address with a sipmap ID.
Fields
| Field | Type | Description |
|---|---|---|
| ID | Integer | The unique ID of a transferee |
| LineURI | Text | The phone number related to the ID |
| URI | Text | The sip address of the transferee |
UCCs
The UCCs table contains all the names and IDs of the UCCs in the database
Fields
| Field | Type | Description |
|---|---|---|
| UCC ID | Integer |
The unique ID of the UCC and is used to link to multiple tables. |
| UCC Name | Text |
The name of the UCC. |
| DisplayName | Text |
The display name of the UCC. |