New schema ININ_ANALYTICS have been created for IVR related tables, sprocs, views and triggers.
Schedulers
Following Schedulers have been introduced. Enable the 'sql server agent' service for schedulers to be running.
The IVR_AllEvents table holds all the row data of generated IVR Events.
| Column | Data Type (Size) | Is Nullable | Contains PII | Description | Last Change Version | Last Change Reason | CX Insights Name | CX Insights Type | CX Insights Folder Name |
|---|---|---|---|---|---|---|---|---|---|
| InteractionUuid | nchar (72) | False | No | Interaction Uuid (Universal Unique Identifier) is a 128-bit number used to uniquely identify Interaction. |
19.3.0.0 | IVR Analytics Feature | Interaction UUID | Attribute | IVR |
| InteractionId | bigint | False | No | A Globally Unique Identifier(GUID) to uniquely identify an interaction. |
19.3.0.0 | IVR Analytics Feature | Interaction Id | Fact | IVR |
| ANI | nvarchar (100) | False | No | ANI (Automatic Number Identification) is Originating party number or caller ID |
19.3.0.0 | IVR Analytics Feature | ANI | Attribute | IVR |
| DNIS | nvarchar (100) | False | No | DNIS (Dialed Number Identification Service) is number dialed by the calling party. It obtained from the telephone network for any inbound call on DNIS enabled lines. |
19.3.0.0 | IVR Analytics Feature | DNIS | Attribute | IVR |
| LevelName | nvarchar (400) | False | No | This stores IVR call event Level Name. Application for which this data is reported. This will be used when reporting for all activities in IVR. |
22.2.0.0 | IVR Analytics Feature | Level Name | Attribute | IVR |
| LevelNumber | tinyint (1) | False | No | Level of the statistics. Server=0, Profile=1, Schedule=2, Application/Complex Operation=3, Menu=4, Task=5,Exit Path=6 |
19.3.0.0 | IVR Analytics Feature | Level Number | Attribute | IVR |
| LevelHierarchy | nvarchar (4000) | True | No | Concatenation of the Level Names from root to the current level separated by '|'. Example 'Profile1|Schedule2|Application3'. In the case where the current node is the root (Level Number=0), there will be no parent node. |
22.2.0.0 | IVR Analytics Feature | Level Hierarchy | IVR | IVR |
| LevelIdHierarchy | varchar (4000) | True | No | Concatenation of the Level Ids from root to the current level separated by '|'. Example '0000001|0000002|000001H'. |
22.2.0.0 | IVR Analytics Feature | Level Id Hierarchy | Attribute | IVR |
| CompletionType | nvarchar (22) | True | No | Either Completed (contained call) or Transferred(non-contained call). |
19.3.0.0 | IVR Analytics Feature | Completion Type | Attribute | IVR |
| Queue | nvarchar (2000) | True | No | Target queue. Example Agent Queue or Workgroup Queue |
19.3.0.0 | IVR Analytics Feature | Queue | Attribute | IVR |
| Action | nvarchar (20) | True | No | Action within the IVR flow(NoInput or NoMatch). |
19.3.0.0 | IVR Analytics Feature | Action | Attribute | IVR |
| EventTime | datetime2 (7) | False | No | IVR call event Time (to millisecond precision) of an event when a particular level is visited. |
19.3.0.0 | IVR Analytics Feature | Event Time | Fact | IVR |
| Index Name | Type | Column Name (sort order) |
|---|---|---|
| IX_IdIndex | Clustered | InteractionId (Ascending) |
This is IVR Exit Events Table which will store all IVR exit events. This table contains last event for each interaction which contains ExitPath information in CompletionType and Queue attributes
| Column | Data Type (Size) | Is Nullable | Contains PII | Description | Last Change Version | Last Change Reason | CX Insights Name | CX Insights Type | CX Insights Folder Name |
|---|---|---|---|---|---|---|---|---|---|
| InteractionUuid | nchar (72) | False | No | Interaction Uuid (Universal Unique Identifier) is a 128-bit number used to uniquely identify Interaction. |
19.3.0.0 | IVR Analytics Feature | Interaction UUID | Attribute | IVR |
| LevelName | nvarchar (400) | False | No | This stores IVR call event Level Name. Application for which this data is reported. This will be used when reporting for all activities in IVR. |
22.2.0.0 | IVR Analytics Feature | Level Name | Attribute | IVR |
| CompletionType | nvarchar (22) | False | No | Either Completed (contained call) or Transferred(non-contained call). |
19.3.0.0 | IVR Analytics Feature | Completion Type | Attribute | IVR |
| Queue | nvarchar (2000) | True | No | Target queue. Example Agent Queue or Workgroup Queue |
19.3.0.0 | IVR Analytics Feature | Queue | Attribute | IVR |
| Action | nvarchar (20) | True | No | Action within the IVR flow(NoInput or NoMatch). |
19.3.0.0 | IVR Analytics Feature | Action | Attribute | IVR |
| Primary Key Name | Type | Column Name (sort order) |
|---|---|---|
| PK_IVR_ExitEvents | Clustered | InteractionUuid (Ascending) |
This is IVR Error Duplicate Exit Events Table which will store all IVR duplicate events. In ideal scenario, each Interaction has one exit event. If more than one exit event will be generated for a Interaction then this table will store the duplicate ExitEvent for each Interaction.
This is IVR InteractionAttribute Table which will store all IVR Interaction Attribute information.
The CallHistory RuleSet table contains details of the RuleSetID that appears in the CallHistory RuleGroup table.
| Column | Data Type (Size) | Is Nullable | Contains PII | Description | Last Change Version | Last Change Reason | CX Insights Name | CX Insights Type | CX Insights Folder Name |
|---|---|---|---|---|---|---|---|---|---|
| InteractionUuid | nchar (72) | False | No | Interaction Uuid (Universal Unique Identifier) is a 128-bit number used to uniquely identify Interaction. |
19.3.0.0 | IVR Analytics Feature | Interaction UUID | Attribute | IVR |
| InteractionId | bigint | True | No | A Globally Unique Identifier(GUID) to uniquely identify an interaction. |
19.3.0.0 | IVR Analytics Feature | Interaction Id | Fact | IVR |
| ANI | nvarchar (100) | True | No | ANI (Automatic Number Identification) is Originating party number or caller ID |
19.3.0.0 | IVR Analytics Feature | ANI | Attribute | IVR |
| DNIS | nvarchar (100) | True | No | DNIS (Dialed Number Identification Service) is number dialed by the calling party. It obtained from the telephone network for any inbound call on DNIS enabled lines. |
19.3.0.0 | IVR Analytics Feature | DNIS | Attribute | IVR |
| CompletionType | nvarchar (22) | True | No | Either Completed (contained call) or Transferred(non-contained call). |
19.3.0.0 | IVR Analytics Feature | Completion Type | Attribute | IVR |
| ExitPath | nvarchar (100) | True | No | This stores all IVR Interaction Attribute Exit Path information. Path for which this data is reported. An "*" will be used when this is a summary row of all activity for the Application and Menu. The path reported is the exit path from the menu. When the exit reason is transfer to a Queue, then the path will be reported as "Transfer WG:{Queue Name}". When the exit reason is a transfer to a user or station queue, the path will be "Transfer User" or "Transfer Station". When the exit reason is an abort of IVR, then the path will be "Abort". "Disconnect" will be used when an interaction disconnects while still in IVR. |
19.3.0.0 | IVR Analytics Feature | Exit Path | Attribute | IVR |
| Queue | nvarchar (2000) | True | No | Target queue. Example Agent Queue or Workgroup Queue |
19.3.0.0 | IVR Analytics Feature | Queue | Attribute | IVR |
| ServerName | nvarchar (60) | True | No | PureConnect Server name |
19.3.0.0 | IVR Analytics Feature | Server Name | Attribute | IVR |
| ProfileName | nvarchar (100) | True | No | Profile of the interaction. The default profile matches all interactions and custom profiles contain predefined characteristics that Attendant compares to an interaction, to decide whether to select the profile for processing. |
19.3.0.0 | IVR Analytics Feature | Profile Name | Attribute | IVR |
| InteractionDuration | float | False | No |
Interaction duration (connected time to terminated time) in seconds. Note: This value is calculated using GMT times, so it is not affected by changes in time due to daylight savings. |
19.3.0.0 | IVR Analytics Feature | Interaction Duration | Fact | IVR |
| EventTime | datetime2 (7) | True | No | IVR call event Time (to millisecond precision) of an event when a particular level is visited. |
19.3.0.0 | IVR Analytics Feature | Event Time | Fact | IVR |
| Primary Key Name | Type | Column Name (sort order) |
|---|---|---|
| PK_IVR_InterAttrib | Clustered | InteractionUuid (Ascending) |
| Index Name | Type | Column Name (sort order) |
|---|---|---|
| IX_TimeIndex | Non-clustered | EventTime (Ascending) |
This is IVR Node Attributes Table which will store all IVR Node Attributes information. This table contains each level detail information including LevelDuration. This table will be used by MSTR.
| Column | Data Type (Size) | Is Nullable | Contains PII | Description | Last Change Version | Last Change Reason | CX Insights Name | CX Insights Type | CX Insights Folder Name |
|---|---|---|---|---|---|---|---|---|---|
| InteractionUuid | nchar (72) | False | No | Interaction Uuid (Universal Unique Identifier) is a 128-bit number used to uniquely identify Interaction. |
19.3.0.0 | IVR Analytics Feature | Interaction UUID | Attribute | IVR |
| NodeId | char (7) | True | No | This is a unique identifier of the IVR node(level name). |
19.3.0.0 | IVR Analytics Feature | Node Id | Attribute | IVR |
| PreviousLevelId | char (7) | True | No | This is a unique identifier of the previously visited IVR node. |
19.3.0.0 | IVR Analytics Feature | Previous Level Id | Attribute | IVR |
| PreviousLevelName | nvarchar (400) | True | No | This is a level name of the previously visited IVR node. |
22.2.0.0 | IVR Analytics Feature | Previous Level Name | Attribute | IVR |
| LevelName | nvarchar (400) | False | No | This stores IVR call event Level Name. Application for which this data is reported. This will be used when reporting for all activities in IVR. |
22.2.0.0 | IVR Analytics Feature | Level Name | Attribute | IVR |
| LevelNumber | tinyint | False | No | Level of the statistics. Server=0, Profile=1, Schedule=2, Application/Complex Operation=3, Menu=4, Task=5,Exit Path=6 |
19.3.0.0 | IVR Analytics Feature | Level Number | Attribute | IVR |
| LevelHierarchy | nvarchar (4000) | True | No | Concatenation of the Level Names from root to the current level separated by '|'. Example 'Profile1|Schedule2|Application3'. In the case where the current node is the root (Level Number=0), there will be no parent node. |
22.2.0.0 | IVR Analytics Feature | Level Hierarchy | Attribute | IVR |
| LevelIdHierarchy | varchar (4000) | True | No | Concatenation of the Level Ids from root to the current level separated by '|'. Example '0000001|0000002|000001H'. |
22.2.0.0 | IVR Analytics Feature | Level Id Hierarchy | Attribute | IVR |
| AttLevelHierarchy | nvarchar (4000) | True | No | Concatenation of the Level Names from root to the current level separated by '|' as configured in Interaction Attendant. Example 'Profile1|Schedule2|Application3'. |
19.3.0.0 | IVR Analytics Feature | Attribute Level Hierarchy | Attribute | IVR |
| Action | nvarchar (20) | True | No | Action within the IVR flow(NoInput or NoMatch). |
19.3.0.0 | IVR Analytics Feature | Action | Attribute | IVR |
| LevelDuration | float | False | No | Level duration (next node entry time - current node entry time) in seconds.. |
19.3.0.0 | IVR Analytics Feature | Level Duration | Fact | IVR |
| EventTime | datetime2 | False | No | IVR call event Time (to millisecond precision) of an event when a particular level is visited. |
19.3.0.0 | IVR Analytics Feature | Event Time | Fact | IVR |
| Index Name | Type | Column Name (sort order) |
|---|---|---|
| IX_TimeInterIndex | Clustered | InteractionUuid (Ascending) EventTime (Ascending) |
This is IVR Retention Settings Table which will store all IVR Retention Settings information. This table contains data purge rules.
| Column | Data Type (Size) | Is Nullable | Contains PII | Description | Last Change Version | Last Change Reason | CX Insights Name | CX Insights Type | CX Insights Folder Name |
|---|---|---|---|---|---|---|---|---|---|
| ServerName | nvarchar (60) | False | No | PureConnect Server name |
19.3.0.0 | IVR Analytics Feature | Server Name | Attribute | IVR |
| PurgingEnabled | bit (1) | False | No | This is a boolean flag which allows customer to control the data purging |
19.3.0.0 | IVR Analytics Feature | Purging Enabled | Fact | IVR |
| PurgeTime | time (3) | True | No | The time in a day at which data will get purged if purging is enabled |
19.3.0.0 | IVR Analytics Feature | Purge Time | Fact | IVR |
| DaysToKeep | int (4) | True | No | The number of days for which data to be retained in the system. |
19.3.0.0 | IVR Analytics Feature | Days To Keep | Fact | IVR |
| Primary Key Name | Type | Column Name (sort order) |
|---|---|---|
| PK_IVR_RetentionSettings | Clustered | ServerName (Ascending) |
This is IVR Display Interval Table which will store all IVR Display Interval information. This table is used by Dashboards as a configuration parameter
| Column | Data Type (Size) | Is Nullable | Contains PII | Description | Last Change Version | Last Change Reason | CX Insights Name | CX Insights Type | CX Insights Folder Name |
|---|---|---|---|---|---|---|---|---|---|
| DisplayFormat | nvarchar (20) | False | No | The way we want to view IVR Analytics data: (Quarterly), (Monthly), (Weekly), (Daily). |
19.3.0.0 | IVR Analytics Feature | Display Format | Attribute | IVR |
| Primary Key Name | Type | Column Name (sort order) |
|---|---|---|
| PK_IVR_DisplayInterval | Clustered | DisplayFormat (Ascending) |
This is IVR Attendant Settings Table which will store all IVR Attendant Settings information. This table contains all the nodes of Interaction Attendant of an IC server. This table will be for MSTR to list all attendant nodes for reference.
| Column | Data Type (Size) | Is Nullable | Contains PII | Description | Last Change Version | Last Change Reason | CX Insights Name | CX Insights Type | CX Insights Folder Name |
|---|---|---|---|---|---|---|---|---|---|
| NodeId | char (7) | False | No | This is a unique identifier of the IVR node(level name). |
19.3.0.0 | IVR Analytics Feature | Node Id | Attribute | IVR |
| ServerName | nvarchar (60) | False | No | PureConnect Server name |
19.3.0.0 | IVR Analytics Feature | Server Name | Attribute | IVR |
| ParentNodeId | char (7) | True | No | This is a unique identifier of parent node |
19.3.0.0 | IVR Analytics Feature | Parent Node Id | Attribute | IVR |
| LevelName | nvarchar (400) | False | No | This stores IVR call event Level Name. Application for which this data is reported. This will be used when reporting for all activities in IVR. |
22.2.0.0 | IVR Analytics Feature | Level Name | Attribute | IVR |
| LevelNumber | tinyint | False | No | Level of the statistics. Server=0, Profile=1, Schedule=2, Application/Complex Operation=3, Menu=4, Task=5,Exit Path=6 |
19.3.0.0 | IVR Analytics Feature | Level Number | Fact | IVR |
| AttLevelHierarchy | nvarchar (4000) | True | No | Concatenation of the Level Names from root to the current level separated by '|' as configured in Interaction Attendant. Example 'Profile1|Schedule2|Application3'. |
22.2.0.0 | IVR Analytics Feature | Attribute Level Hierarchy | Attribute | IVR |
| AttLevelIdHierarchy | varchar (4000) | True | No | Concatenation of the Level Ids from root to the current level separated by '|'. Example '0000001|0000002|000001H'. |
22.2.0.0 | IVR Analytics Feature | Attribute Level Id Hierarchy | Attribute | IVR |
| LevelType | nvarchar (50) | True | No | This stores IVR call event Level Type. Application for which this data is reported. This will be used when reporting for all activities in IVR. |
19.3.0.0 | IVR Analytics Feature | Level Type | Attribute | IVR |
| Primary Key Name | Type | Column Name (sort order) |
|---|---|---|
| PK_IVR_AttendantSetting | Clustered | NodeId (Ascending) ServerName (Ascending) |