Jump to: navigation, search

Database Views Schema

Call Reporting Database Views

The three main call reporting views within the reporting database are:

  • VW_CALLS - Records each call made into the GAAP application.
  • VW_CALL_STEPS - Records each step made by the caller.
  • VW_BUSINESS_TASKS - Records which business tasks started and ended, along with details of each instance.

The diagram below describes the relationship between the three views:

GAAP350 Ref CallReportOverview.png

These views are configured so they will not lock the database when they run. Genesys recommends that you always filter based on:

  • company_id.
  • is_test_call.
  • call_start_date.

Refer to the Useful SQL Queries section to find queries that might be useful when working with the database

VW_CALLS

This view records each call made into the GAAP application.

Calls might be physical or not. For example, if a caller started a phone call to GAAP but was transferred out of GAAP to a routing strategy and then returned. In this case, two rows are recorded into this view.

Column DataType Length Description Example
call_id
bigint Unique identifier for each call. This is an incrementing number. 10001
company_id
int Link to GAAP company identifier. The company ID can be found in the GUI (look in Administration > Companies). 3
voice_platform_session_id
nvarchar 100 Media Platform Identifier for the call within GAAP (e.g. Genesys Voice Platform). 26A92695-9557-444A-A9B2-CCB4D71C1B69-1791
voice_platform_full_call_id
nvarchar 100 Unique identifier to link multiple GAAP interactions together within a single phone call. 10KMMSG8LL37TETGHTNQOF4KK80001NV
start_site_id
int The module ID of the Inbound application from which the call started. 1
start_site_name nvarchar 100 The name of the Inbound application from which the call started. SIM Activation
call_start_time datetime The timestamp for when the call started. 2014-02-13 15:35:31.737
call_start_date
datetime The date timestamp for when the call started (time is always 00.00.00.000). 2014-02-13 00:00:00.000
call_start_hour int The hour of the day for when the call started, in 24-hour format. 15
call_end_time datetime The timestamp for when the call ended. 2014-02-13 15:36:02.640
call_end_date datetime The date timestamp for when the call ended (time is always 00.00.00.000). 2014-02-13 00:00:00.000
call_end_hour int The hour of the day when the call ended, in 24-hour format. 15
call_end_site_id int The ID of the last module accessed on the call. 3
call_end_site_name nvarchar 100 The name of the last module accessed on the call. Transfer to RS – With Announcement
call_end_block_type tinyint 1 Type of the last block accessed on the call.

Possible values are:

  • 1 – Start.
  • 2 – End.
  • 3 – Script.
  • 4 – Message.
  • 5 – Menu.
  • 6 – Custom Question.
  • 7 – Phone Transfer.
  • 8 – URL Transfer.
  • 9 – Recording.
  • 10 – Interceptor.
2
call_end_block_name nvarchar 100 The name of the last block or step accessed on the call. End Call
call_end_result nvarchar 100 The resulting outcome description. Possible values are:
  • error.
  • success.
  • hangup.
  • system hangup.
hangup
has_recent_failure bit 1 Internal flag to determine if an error or recognition failure occurred in the callflow step/block. This flag can be reset, as controlled by VUI preferences, if the succeeding Menu or Custom Question step/block is successful. 0
is_test_call
bit 1 Flag to determine if call is for the test or production version of the module. Values can be 1 for Test or 0 for production. 1
call_duration int Call duration in seconds. 30
cli nvarchar 45 Calling Line Identifier (the number that the caller is calling from). 3100
dnis nvarchar 45 Dialed number that is associated to the GAAP application. 1234
cluster_id int The ID of the GAAP server cluster that handled the call. Cluster information can be found in the GAAP GUI (look in Administration > Clusters). 1
cluster_name nvarchar 100 The name of the GAAP server cluster that handled the call. Cluster information can be found in the GAAP GUI (look in Administration > Clusters). Default Voice Cluster
cti_fields nvarchar 1000 Computer Telephony Integration (CTI) attached data. Fields are pipe separated, with each field represented as a key-value pair separated by colon. Literal colons, pipes, or percent symbols within keys or values are represented as %c, %p, or %%, respectively.

You can define the list of allowed CTI variables that are included in this field in the GAAP GUI (look in Administration > Default Server Settings).

Segment:Gold
last_menu_block_type tinyint 1 The type of the last Menu or Custom Question block accessed on the call.

Possible values are:

  • 5 – Menu
  • 6 – Custom Question
5
last_menu_block_name nvarchar 100 The name of the last Menu or Custom Question block accessed on the call. If the callflow visited the following blocks:

Welcome Message > Proceed With Activation Menu >Process Request Script > Successful Message > End Call

This value is Proceed With Activation Menu.

cli_type
smallint 1 Representation of whether the caller is using a landline or a mobile handset. Possible values are:
  • 0 - Unknown
  • 1 - Landline
  • 2 - Mobile

The list of CLI mobile number prefixes that are defined in server settings is used to determine if the CLI is a mobile number.

1
server_id int The ID of the GAAP server that handled the call. Server information can be found in the GAAP GUI (look in Administration > Servers). 1
start_channel int The channel in which the call started. Possible values are:
  • 0 - Unknown.
  • 1 – Voice.
  • 2 – Web.
  • 3 – Facebook.
  • 4 – Web with Voice.
2

VW_CALL_STEPS

Each row in this view details a single block within the callflow that the caller progressed through.

Column DataType Length Notes Example
id int Unique call step identifier within call. This is an incrementing number. 1
call_id int See VW_CALLS.call_id. 10001
call_start_site int See VW_CALLS.start_site_id. 1
call_start_date date See VW_CALLS.call_start_date. 2014-02-13
cli_type smallint 1 See VW_CALLS.cli_type. 1
company_id int See VW_CALLS.company_id. 3
is_test_call bit 1 See VW_CALLS.is_test_call. 1
site_id int The ID of the GAAP module where this block/step (in the callflow) belongs. 265
site_name nvarchar 100 The name of the GAAP module where this block/step (in the callflow) belongs. Call Initialization
is_submodule bit 1 Flag to determine if the module is flagged as an Inbound application or just a module. 1
block_type tinyint 1 Block type indicator.

Possible values are:

  • 1 – Start
  • 2 – End
  • 3 – Script
  • 4 – Message
  • 5 – Menu
  • 6 – Custom Question
  • 7 – Phone Transfer
  • 8 – URL Transfer
  • 9 – Recording
  • 10 – Interceptor
1
block_name nvarchar 100 Name of the block or step in the callflow. Start
block_detail nvarchar 500 Internal field used to store additional information (if any) about the block. For example, if this is a URL Transfer block, this field will show the module it will transfer to. (Link to module : 5)
start_time datetime Timestamp for when the callflow step/block was first visited. 2014-02-13 15:35:34.770
start_date datetime Date timestamp for when the callflow step/block was first visited (time is always 00.00.00.000). 2014-02-13 00:00:00.000
start_hour int Hour of the day when the callflow step/block was first visited, in 24-hour format. 15
end_time datetime Timestamp for when the callflow step/block ended. 2014-02-13 15:35:34.780
end_date datetime Date timestamp for when the callflow step/block ended (time is always 00.00.00.000). 2014-02-13 00:00:00.000
end_hour int Hour of the day for when the callflow step/block ended, in 24-hour format 15
duration int Duration, in seconds, spent within the step/block. 0
result nvarchar 100 Resulting outcome description. Possible values are:
  • error.
  • success.
  • hangup.
  • system hangup.
Success
result_detail nvarchar 100 Additional information relating to the result (for example, transferred telephone number). tel://123456789
error_messages nvarchar 500 Detailed error messaging (if any)
wav_filename nvarchar 200 Only applicable for a Recording block. This is the filename of the saved recording. temprecording_123456.wav
is_recording_saved bit 1 Only applies for a Recording block. This is the flag to determine if there is a wav file recording saved. Possible values are 0 if no recording is saved or 1 if a recording exists. 1
recognition_type int Internal recognition type.

Possible values are:

  • 0 – None
  • 1 – Menu
  • 2 – Custom
  • 3 – Defaults
  • 4 - Global
1
is_dtmf bit 1 Flag that indicates if block/step is DTMF enabled. Value will be set to 1 if its DTMF enabled; otherwise, this value is 0. 1
num_retries tinyint Count of no-match entries by caller in this callflow step/block. This field populates only if the caller leaves this block (i.e. doesn’t hang up). 2
num_timeouts tinyint Count of no-input entries by caller in this callflow step/block. This field populates only if the caller leaves this block (i.e. doesn’t hang up). 1
num_helps tinyint Number of times the help command was used in this callflow block/step. This field populates only if the caller leaves this block (i.e. doesn’t hang up). 1
num_repeats tinyint Number of times the repeat command was used in this callflow block/step. This field populates only if the caller leaves this block (i.e. doesn’t hang up). 1
num_recovery_attempts int Number of times the callflow step/block was visited when the caller failed to be recognized and took the GAAP recovery route. 1
num_nbest tinyint Number of best possible matches (nbest) recognized in Automatic Speech Recognition (ASR). For an answer provided via DTMF, this will always have a value of 1. 10
nbest_meaning_1 nvarchar 45 First highest match from the ASR against the SRGS grammar in context. 07712344401
nbest_rawanswer_1 nvarchar 100 First highest synonym match from the ASR against the SRGS grammar in context. Oh seven seven one two three four four four oh one
nbest_confidence_1 int Confidence scoring out of 1000 of first highest match against the SRGS grammar in context. For DTMF, this value is always 1000. 700
nbest_slots_1 nvarchar 100 First highest slot content from the ASR against the SRGS grammar in context. Type:Mobile|Number:12344401
nbest_recognition_type_1 int Internal recognition type reference for the first highest match.

Possible values are:

  • 0 – None
  • 1 – Menu
  • 2 – Custom
  • 3 – Defaults
  • 4 - Global
2
nbest_meaning_2 nvarchar 45 Second highest match from the ASR against the SRGS grammar in context. 07712344501
nbest_rawanswer_2 nvarchar 100 Second highest synonym match from the ASR against the SRGS grammar in context. Zero seven seven one two three four four five oh one
nbest_confidence_2 int Confidence scoring out of 1000 of second highest match against the SRGS grammar in context. For DTMF, this value is always 1000. 10
nbest_slots_2 nvarchar 100 Second highest slot content from the ASR against the SRGS grammar in context. Type:Mobile|Number:12344501
nbest_recognition_type_2 int Internal recognition type reference for the second highest match.

Possible values are:

  • 0 – None
  • 1 – Menu
  • 2 – Custom
  • 3 – Defaults
  • 4 - Global
2
nbest_meaning_3 nvarchar 45 Third highest match from the ASR against the SRGS grammar in context. 07712345401
nbest_rawanswer_3 nvarchar 100 Third highest synonym match from the ASR against the SRGS grammar in context. Zero seven seven one two three four five four zero one
nbest_confidence_3 int Confidence scoring out of 1000 of third highest match against the SRGS grammar in context. For DTMF, this value is always 1000. 10
nbest_slots_3 nvarchar 100 Third highest slot content from the ASR against the SRGS grammar in context. Type:Mobile|Number:12345401
nbest_recognition_type_3 int Internal recognition type reference for the third highest match.

Possible values are:

  • 0 – None
  • 1 – Menu
  • 2 – Custom
  • 3 – Defaults
  • 4 - Global
2
output_node_name nvarchar 100 Name of the path that leads to this callflow step/block. success
sequence_in_call int Given the list of callflow steps/blocks that were visited within the call, this is the position this step/block was visited within the sequence. 3
sequence_in_site int Given the list of callflow steps/blocks that were visited within the module, this is the position this step/block was visited within the sequence. 1
persona_name nvarchar 100 The name of the persona active during the current call step. An empty string is saved if using the default persona. French
channel int The channel that is being used by the caller during this call step. Possible values are:
  • 0 – Unknown
  • 1 – Voice
  • 2 – Web
  • 3 – Facebook
  • 4 – Web with Voice
2

VW_BUSINESS_TASKS

Each row in this view details the business task that was processed (started, ended, and so on) within the callflow that the caller progressed through.

Column DataType Length Notes Example
id bigint Unique business task identifier within the call. This is an incrementing number. 10008
call_id int See VW_CALLS.call_id. 10001
voice_platform_session_id nvarchar 100 See VW_CALLS.voice_platform_session_id. 26A92695-9557-444A-A9B2-CCB4D71C1B69-1791
voice_platform_full_call_id nvarchar 100 See VW_CALLS.voice_platform_full_call_id. 10KMMSG8LL37TETGHTNQOF4KK80001NV
company_id int See VW_CALLS.company_id. 3
is_test_call bit 1 See VW_CALLS.is_test_call. 1
start_site_id int The ID of the module where this business task belongs. 10
start_site_name nvarchar 100 The name of the module where this business task belongs. Payment By Full Balance
start_time datetime Timestamp for when this business task started. 2014-02-13 15:36:31.367
start_date datetime Date timestamp for when this business task started (time is always 00.00.00.000). 2014-02-13 00:00:00.000
start_hour int Hour of the day for when this business task started, in 24-hour format. 15
end_time datetime Timestamp for when this business task ended. 2014-02-13 15:36:50.367
end_date datetime Date timestamp for when this business task ended (time is always 00.00.00.000). 2014-02-13 00:00:00.000
end_hour int Hour of the day for when this business task ended, in 24-hour format 15
duration int Duration, in seconds, between when the time business task started and ended. 19
name nvarchar 100 Name of the business task (for example, postal address lookup). Payment
outcome_category tinyint 1 Outcome category identifier.

Possible values are:

  • 1 – success
  • 2 – failure
  • 3 – CPFL (customer perceived failure)
  • 4 – Unknown
  • 5 - Hangup
3
outcome_description nvarchar 100 Outcome category description. Invalid balance
details nvarchar 100 Additional information regarding the business task outcome. Balance = null
call_start_site int See VW_CALLS.start_site_id. 1
call_start_date date See VW_CALLS.call_start_date. 2014-02-13

Useful SQL Queries

This page describes SQL queries that might be useful when working with the database.

Get complete call details for a given call ID

SELECT
calls.*, call_steps.*
FROM VW_CALLS calls
INNER JOIN VW_CALL_STEPS call_steps
ON calls.call_id = call_steps.call_id
AND calls.call_id = xxx

Get complete business task details for a given call ID

SELECT
calls.*, business_tasks.*
FROM VW_CALLS calls
INNER JOIN VW_BUSINESS_TASKS business_tasks
ON calls.call_id = business_tasks.call_id
AND calls.call_id = xxx

Get complete call details for a company, filtered by call date and module version

SELECT
calls.*, call_steps.*
FROM
VW_CALLS calls,
VW_CALL_STEPS call_steps
WHERE
	calls.company_id = call_steps.company_id
	AND calls.company_id = 1
	AND calls.is_test_call = call_steps.is_test_call
	AND calls.is_test_call = 0
	AND calls.call_start_date = '2017-01-01'
This page was last edited on July 7, 2017, at 22:07.
Comments or questions about this documentation? Contact us for support!