Historical database (VHT_RPT) schema
The contents of the QDump and HistoricalOut tables in the VHT_RPT database contains information on the data the On-Premise Callback system collects. This information can be used to create custom reports, or to maintain On-Premise Callback data.
The VHT_RPT database contains the following tables:
- ThirdPartyInteractionData - captures unique call IDs (External Tracking IDs)
- HistoricalOut - stores dial attempt records that represent each time a call was attempted for out-dial
- QDump - stores individual call records that represent the entire course of a call
- SecondChanceOffer - stores the call records for a call in queue when offered a Second Chance callback
For example, assume that a caller requests a callback, but when On-Premise Callback attempts to call them back, the caller does not answer. When On-Premise Callback makes a second attempt, the line is busy. On the third attempt, the caller answers. In this case, On-Premise Callback records three separate records in the HistoricalOut table - one for each callback attempt - and records a single entry for the entire call sequence in the QDump table.
ThirdPartyInteractionData table
The ThirdPartyInteractionData table captures unique call IDs (External Tracking ID) when using the VXML Interaction Server (VIS). The recording of this unique call lD to the database helps troubleshoot issues and extends the reporting capabilities. It is also used for matching On-Premise Callback data to external reporting data. Values stored are Genesys UUID, Avaya UCID, and Cisco router call key. The following table shows the fields in the third party interaction Data table and examples of the type of data that is recorded to the table:
| CALLID | Name | Value |
| V000000000000000007021309524559 | ExternalTrackingID | 00001000138136025411259 |
| V000000000000000007146532328552 | ExternalTrackingID | 00012000852014801485277 |
| V000000000000000007441125528665 | ExternalTrackingID | 00012100008655202152008 |
HistoricalOut table
The HistoricalOut table stores dial attempt records that represent each time a call was attempted for out-dial. The following table describes the fields in the HistoricalOut table:
| Key | Field name | Data type | Length | Allows nulls | Definition |
| SiteName | nvarchar | 50 | Y | This is the name of the site associated the installation of Callback. Each Queue Manager can support one site. | |
| QueueGroup | nvarchar | 50 | Y | A logical grouping of queues at a single site or at an enterprise level. | |
| QueueName | nvarchar | 255 | Y | The name of a business queue. | |
| Y | CALLID | char | 32 | Unique call identifier assigned by On-Premise Callback Queue Manager. | |
| Y | OutTimeStart | datetime | 8 | The time when an outbound dial attempt is initiated. | |
| ConnectTime | datetime | 8 | Y | The time when an outbound dial attempt is connected with a person. | |
| OutTimeFinish | datetime | 8 | Y | The time when an outbound dial attempt is disconnected. | |
| CallProgressResult | char | 3 | Y | N/A See OutFate which replaced this. | |
| PhoneNumber | char | 27 | Y | The phone number collected by On-Premise Callback from the caller to be used for the callback. | |
| OutFate | char | 3 | Y | The result of an outbound dial attempt. | |
| VHTLineNumber | smallint | 2 | Y | The number of the On-Premise CallbackIVR port that the call was treated on. | |
| PilotNumber | char | 45 | Y | A unique number passed to On-Premise Callback by the ACD/PBX that is assigned to a queue or application to be given treatment. | |
| OutCallType | tinyint | 1 | Y | The type of callback requested. 1 = ASAP, 2 = Scheduled, 3 = Rapport. | |
| QueueID | char | 255 | Y | A unique number assigned to each queue or application to be given On-Premise Callback treatment. | |
| ScriptNumber | smallint | 2 | Y | Represents the directory of prompts used when interacting with the caller (e.g. English or Spanish) in systems using multiple IVR profiles. In systems using one VXML IVR profile, this field represents the directory of prompts that would have been used in multiple IVR profile systems. | |
| QueueMode | nvarchar | 255 | Y | Indicates the Queue Mode that was active for the call. | |
| AgentResult | nvarchar | 255 | Y | Shows success or failure to indicate the result of an Agent Priority call. |
QDump table
The QDump table stores individual call records that represent the entire course of a call. The following table describes the fields in the QDump table:
| Key | Field name | Data type | Length | Allows nulls | Definition |
| SiteName | nvarchar | 50 | Y | This is the name of the site associated with the installation of Callback. Each Queue Manager can support one site. | |
| QueueGroup | nvarchar | 50 | Y | A logical grouping of queues at a single site or at an enterprise level. | |
| QueueName | nvarchar | 255 | Y | The name of a business queue. | |
| Y | CALLID | char | 32 | Unique call identifier. | |
| FinalFate | char | 3 | Y | A code that represents the ultimate outcome of the call. | |
| FinalFateTime | datetime | 8 | Y | The time that a call completes. | |
| History | nvarchar | 100 | Y | A listing of all the fate codes associated with the call. | |
| InTimeStart | datetime | 8 | Y | The time when On-Premise Callbackis first introduced to a call, usually through a CTI route request. | |
| InTimeFinish | datetime | 8 | Y | The time when On-Premise Callback is finished processing a call on its inbound IVR port or completes routing the call to queue. | |
| IVRTimeStart | datetime | 8 | Y | The time when a call lands on a On-Premise Callback IVR port and is given treatment. | |
| PhoneNumber | char | 27 | Y | The number collected by On-Premise Callback from the caller. | |
| ANI | char | 255 | Y | Automatic Number Identification, provided by the network carrier. | |
| NumBusyAttempts | tinyint | 1 | Y | The number of times that a callback attempt resulted in a busy signal. | |
| NumNoAnsAttempts | tinyint | 1 | Y | The number of times that a callback attempt resulted in a no answer. | |
| NumTryAgainAttempts | tinyint | 1 | Y | The number of times that a callback attempt resulted in something other than a busy signal or a no answer. | |
| MaxRetries | tinyint | 1 | Y | The total number of callback attempts. | |
| InitialCBTime | datetime | 8 | Y | The time of the first callback attempt. | |
| InitialCBMode | tinyint | 1 | Y | The type of callback that was dialed out initially. 1 = ASAP,2 = Scheduled, 3 = Rapport. | |
| AnnouncedEWT | int | 4 | Y | The estimated wait time that was announced to the caller, rounded up to the nearest 60 seconds. Also represents the lower EWT when speaking a range. | |
| AgentsStaffed | smallint | 2 | Y | The number of agents staffed in the queue or application at the time when the call was introduced to Callback. | |
| OpMode | tinyint | 1 | Y | The Operation Mode the queue is in at the time of new call arrival. | |
| PilotNumber | char | 45 | Y | A unique number passed to On-Premise Callbackby the ACD/PBX that is assigned to a queue or application to be given treatment. | |
| InCallType | tinyint | 1 | Y | N/A. | |
| OutCallType | tinyint | 1 | Y | The type of callback requested. 1 = ASAP, 2 = Scheduled, 3 = Rapport. This matches the OutCallType in HistoricalOut. | |
| LastName | char | 25 | Y | N/A. | |
| FirstName | char | 25 | Y | N/A. | |
| VHTLineNumber | smallint | 2 | Y | The number of the On-Premise CallbackIVR port that the call was treated on. | |
| InResult | char | 3 | Y | A code that represents the result of the initial On-Premise Callback route request or the choice of the customer if the call was given On-Premise Callback treatment. | |
| InFate | char | 3 | Y | A code that represents the fate of the inbound call. This is legacy information. InResult should be used instead. | |
| QueueID | char | 255 | Y | A unique number assigned to each queue or application to be given On-Premise Callback treatment. | |
| ScriptNumber | smallint | 2 | Y | Represents the directory of prompts used when interacting with the caller (such as English or Spanish) in systems using multiple IVR profiles. In systems using one VXML IVR profile, this field represents the directory of prompts that would have been used in multiple IVR profile systems. | |
| InTimeStartMonth | tinyint | 1 | Y | Represents the month when a call is introduced to the On-Premise Callback system, usually through a CTI route request. | |
| InTimeStartDay | tinyint | 1 | Y | Represents the day of the week that a call is introduced to the On-Premise Callback system, usually through a CTI route request. | |
| InTimeStartSlot | tinyint | 1 | Y | Represents the 30-minute interval when a call is introduced to the On-Premise Callback system, usually through a CTI route request. | |
| FinalDisconnectTime | datetime | 8 | Y | The time when an agent is finished speaking with a caller and disconnects if On-Premise Callback is monitoring agents. Otherwise this is the time that a call is sent to an agent. | |
| AgentAnswerTime | datetime | 8 | Y | The time when an agent answers a call. | |
| CallbackPunctuality | int | 4 | Y | The difference between the spoken EWT and actual callback time. | |
| RouteToQTime | datetime | 8 | Y | The time when On-Premise Callbackroutes the call to the ACD Holding Queue. | |
| ApplicationID | char | 30 | Y | A unique number passed to On-Premise Callbackthrough the Aspect subtype that is assigned to a queue or application to be given treatment. | |
| UpperEWT | int | 4 | Y | The upper estimated wait time that was announced to the caller, when speaking a range, rounded up to the nearest 60 seconds. | |
| TransferVDN | char | 30 | Y | N/A. | |
| InternalEWT | int | 4 | Y | The estimated wait time that was calculated by Callback. | |
| ExternalEWT | int | 4 | Y | The estimated wait time that was calculated by the ACD, rounded up to the nearest minute. Also represents the lower EWT when speaking a range. | |
| NumAnsMachAttempts | int | 4 | Y | The number of times that a callback attempt resulted in reaching an answering machine. | |
| SourceApplication | nvarchar | 200 | Y | Contains name of the interface used to register the callback request. Examples include the following:
Platform Toolkit application developers can use custom application names. Refer to the Platform ToolKit Developers Guide. |
SecondChanceOffer table
The SecondChanceOffer table stores call records for a queued call when offered a second chance callback. For each Second Chance Callback offered, a new record is written to the table.
When the Second Chance Callback is registered, a new interaction is created with a new CallID; the ExternalTrackingID is the same as the original call.
The following table describes the fields in the table:
| Key | Field name | Data type | Length | Allows nulls | Definition |
| Y | Id | bigint | 8 | N | The identifier of the record. |
| CallId | char | 32 | N | The call ID associated with the call when offered a Second Chance callback. | |
| ExternalTrackingID | nvarchar | 255 | Y | The tracking ID associated with the call. The tracking ID is the same for both the original call, and the callback registered with Second Chance. | |
| SiteName | nvarchar | 50 | Y | This is the name of the site associated with the installation of Callback. Each Queue Manager can support one site. | |
| QueueID | nvarchar | 100 | Y | A unique number assigned to each queue or application to be given On-Premise Callbacktreatment. | |
| SegmentName | nvarchar | 255 | Y | Name of the segment for the call being offered Second Chance callback. | |
| RemainingWaitTime | int | 4 | Y | Amount of remaining EWT when the call was offered the Second Chance callback. | |
| CurrentQueuePosition | int | 4 | Y | Current position of the call in queue when the call was offered the Second Chance callback. | |
| Timestamp | datetime | 8 | Y | Timestamp in the format hh:mm:ss when the call was offered Second Chance callback. |
