Appendix F: Interaction Feedback Tables

Appendix F: Interaction Feedback Tables

This Interaction Feedback information provides concise and detailed descriptions of the database tables CIC uses to store and generate reports on Interaction Feedback data. This information assists you in understanding the structure and contents of Interaction Feedback tables.

For detailed information, please see the Interaction Quality Management Data Dictionary.

Survey Table

The Survey table contains an entry for surveys or survey groups. Note the table is self-referential and embeds the parent-child relationship of surveys to survey groups.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyID varchar (32) False No

ID of the survey or survey group.

4.0.106.453 4.0 Release
ParentSurveyId varchar (32) True No

ID of the parent survey or survey group.

4.0.106.453 4.0 Release
SurveyName nvarchar (255) False Depends on customer usage

Name of the survey or survey group.

4.0.106.453 4.0 Release
Note nvarchar (255) True Depends on customer usage

Notes about the survey or survey group.

4.0.106.453 4.0 Release
IsPublished int True No

1 if the survey is published else 0. Other values are not defined. Survey groups always have a 0 in this field.

4.0.106.453 4.0 Release
IsTemplate int False No

1 if the survey is a template else 0. Other values are not defined. Template surveys are stored in the library and won't have any survey results associated with them.

4.0.106.453 4.0 Release
MinAcceptableScore numeric True No

Minimal acceptable score for the survey.

4.0.106.453 4.0 Release
IsGroup int False No

1 if this entry is for a survey group else 0. Other values are not defined.

4.0.106.453 4.0 Release
Priority int True No

Priority of the survey. Null for survey groups.

4.0.106.453 4.0 Release
RankGroupId uniqueidentifier True No

ID of the rank group associated with the survey. Null for survey groups.

4.0.106.453 4.0 Release
RecordCall int True No

One if the surveyed call should be recorded else zero. Other values are not defined. Null for survey groups.

4.0.106.453 4.0 Release
Type int True No

Type of Survey

  • 0 - None.
  • 1 - Post-call survey.
  • 2 - Inbound survey.
4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyID char (32) False No

ID of the survey or survey group.

4.0.106.453 4.0 Release
ParentSurveyId char (32) True No

ID of the parent survey or survey group.

4.0.106.453 4.0 Release
SurveyName nvarchar2 (255) False Depends on customer usage

Name of the survey or survey group.

4.0.106.453 4.0 Release
Note nvarchar2 (255) True Depends on customer usage

Notes about the survey or survey group.

4.0.106.453 4.0 Release
IsPublished integer True No

1 if the survey is published else 0. Other values are not defined. Survey groups always have a 0 in this field.

4.0.106.453 4.0 Release
IsTemplate integer False No

1 if the survey is a template else 0. Other values are not defined. Template surveys are stored in the library and won't have any survey results associated with them.

4.0.106.453 4.0 Release
MinAcceptableScore number True No

Minimal acceptable score for the survey.

4.0.106.453 4.0 Release
IsGroup integer False No

1 if this entry is for a survey group else 0. Other values are not defined.

4.0.106.453 4.0 Release
Priority integer True No

Priority of the survey. Null for survey groups.

4.0.106.453 4.0 Release
RankGroupId raw (16) True No

ID of the rank group associated with the survey. Null for survey groups.

4.0.106.453 4.0 Release
RecordCall integer True No

One if the surveyed call should be recorded else zero. Other values are not defined. Null for survey groups.

4.0.106.453 4.0 Release
Type integer False No

Type of Survey

  • 0 - None.
  • 1 - Post-call survey.
  • 2 - Inbound survey.
4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_Survey Clustered SurveyID (Ascending)

Primary Key Name Column Name (sort order)
PK_Survey SurveyID (Ascending)

Foreign Key Name Column Reference Table Reference Column Indexed
FK_Survey_ParentSurveyId ParentSurveyId Survey SurveyID Yes
FK_Survey_RankGroupID RankGroupId IR_RankGroup RankGroupId Yes

Foreign Key Name Column Reference Table Reference Column Indexed
FK_Survey_ParentSurveyId ParentSurveyId Survey SurveyID Yes
FK_Survey_RankGroupID RankGroupId IR_RankGroup RankGroupId Yes

Index Name Type Column Name (sort order)
IX_Survey_SurveyName Non-clustered SurveyName (Ascending)

Index Name Column Name (sort order)
IX_Survey_SurveyName SurveyName (Ascending)

SurveyAnswer Table

The SurveyAnswer table contains answers to questions, as the result of a survey.

Constraints



Explanation of FreeFormAnswerURI

From Interaction Administrator, you can configure the base storage location of recorded comments. From there the recording is stored in a series of subfolders that are created based on the date of the recording. The FreeFormAnswerURI column only contains the date subfolders and the filename of the recording. For example, if the base recording path is D:\FeedbackComments and the recording file name is comment.wav and was recorded on 07/28/2011 at 19:18, then the value of this column would be "2011078198\comment.wav".

To further elaborate on the value of FreeFormAnswerURI, the file name in our previous example was "comment.wav". This was an over simplification. The actual file name of a recorded comment is generated from the following template:

surveysurveyId_callId_surveyQuestionId.wav

where the italicized parts should be replaced with their values from the database. The call ID is only the 10 digit form of the call ID. For example if the survey ID is 1111, the call ID is 2001490800 and the survey question ID is 2222, then the file name would be "survey1111_ 2001490800_2222.wav".

SurveyDetail Table

The SurveyDetail table contains detailed information about the result of a survey. There is one row per survey offered.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyFormID uniqueidentifier False No

ID of the survey form.

4.0.106.453 4.0 Release
EventCode int False No

Event code for how the survey ended.

  • 0 - None
  • 1 - Complete
  • 2 - Abandoned
  • 3 - Insufficient Resources
  • 4 - Opt-out
  • 5 - Error
  • 6 - NotAnswered
  • 7 - Caller Disconnect
4.0.106.453 4.0 Release
EventDetail nvarchar (255) True Depends on customer usage

Textual representation of the EventCode column. See EventCode for code and detail pairing.

4.0.106.453 4.0 Release
EventDate datetime False No

UTC Date and time of when the survey was taken.

4.0.106.453 4.0 Release
CallIdKey varchar (20) False No

Full 18 digit call ID key of the interaction.

4.0.106.453 4.0 Release
Origin int False No

Origin of the survey

  • 0 - Post-call
  • 1 - External
  • 2 - Internal
  • 3 - Transfer
  • 4 - Outbound
4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyFormID raw (16) False No

ID of the survey form.

4.0.106.453 4.0 Release
EventCode integer False No

Event code for how the survey ended.

  • 0 - None
  • 1 - Complete
  • 2 - Abandoned
  • 3 - Insufficient Resources
  • 4 - Opt-out
  • 5 - Error
  • 6 - NotAnswered
  • 7 - Caller Disconnect
4.0.106.453 4.0 Release
EventDetail nvarchar2 (255) True Depends on customer usage

Textual representation of the EventCode column. See EventCode for code and detail pairing.

4.0.106.453 4.0 Release
EventDate date False No

UTC Date and time of when the survey was taken.

4.0.106.453 4.0 Release
CallIdKey varchar2 (20) False No

Full 18 digit call ID key of the interaction.

4.0.106.453 4.0 Release
Origin integer False No

Origin of the survey

  • 0 - Post-call
  • 1 - External
  • 2 - Internal
  • 3 - Transfer
  • 4 - Outbound
4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyDetail Non-clustered SurveyFormID (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyDetail SurveyFormID (Ascending)

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyDetail_SurveyFormID SurveyFormID SurveyForm SurveyFormId

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyDetail_SurveyFormID SurveyFormID SurveyForm SurveyFormId

Index Name Type Column Name (sort order)
IX_SurveyDetail_EventDate Clustered EventDate (Ascending)

Index Name Column Name (sort order)
IX_SurveyDetail_EventDate EventDate (Ascending)

SurveyForm Table

The SurveyForm table contains information about the result of a survey. There is one row per survey offered.

Constraints



SurveyObjectToSurveyPrompt Table

This database table associates prompts that are assigned to a survey (Invitation, Opt-in, etc.) or a question. This is a many-to-many relationship.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyObjectID varchar (32) False No

ID of the survey or question.

4.0.106.453 4.0 Release
SurveyPromptID varchar (32) False No

ID of the prompt being associated.

4.0.106.453 4.0 Release
AppCode int False No

When during the survey or question the prompt will be played.
AppCode can be one of the following values for surveys:

  • 0 - Survey Invitation
  • 1 - Survey Opt-in
  • 2 - Survey Opt-Out
  • 3 - Survey Welcome
  • 4 - Survey Finish
  • 5 - Survey Help
  • 6 - Survey Bad Score

Or one of the following values for questions:
  • 7 - Question Prompts
  • 8 - Question Help
  • 9 - Question No Input
  • 10 - Question No Match
  • 11 - Save Comment
  • 12 - Re-Record Comment
  • 13 - Re-Play Comment
4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyObjectID char (32) False No

ID of the survey or question.

4.0.106.453 4.0 Release
SurveyPromptID char (32) False No

ID of the prompt being associated.

4.0.106.453 4.0 Release
AppCode integer False No

When during the survey or question the prompt will be played.
AppCode can be one of the following values for surveys:

  • 0 - Survey Invitation
  • 1 - Survey Opt-in
  • 2 - Survey Opt-Out
  • 3 - Survey Welcome
  • 4 - Survey Finish
  • 5 - Survey Help
  • 6 - Survey Bad Score

Or one of the following values for questions:
  • 7 - Question Prompts
  • 8 - Question Help
  • 9 - Question No Input
  • 10 - Question No Match
  • 11 - Save Comment
  • 12 - Re-Record Comment
  • 13 - Re-Play Comment
4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyObjectToSurveyPrompt Clustered SurveyPromptID (Ascending)
SurveyObjectID (Ascending)
AppCode (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyObjectToSurveyPrompt SurveyPromptID (Ascending)
SurveyObjectID (Ascending)
AppCode (Ascending)

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyObjectToSurveyPrompt_SurveyPromptID SurveyPromptID SurveyPrompt SurveyPromptID Yes

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SRVOBJTOSRVPRMPT_SRVPRMPTID SurveyPromptID SurveyPrompt SurveyPromptID Yes

Index Name Column Name (sort order)
ix_srvobjtosrvprmpt_srvprmptid surveypromptid (Ascending)

SurveyPrompt Table

The SurveyPrompt table contains information about prompts available for use.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyPromptID varchar (32) False No

ID of the survey prompt.

4.0.106.453 4.0 Release
PromptType int False No

Type of prompt.

  • 0 - None
  • 1 - File-based Prompt
  • 2 - Handler-based Prompt
  • 3 - TTS-based Prompt
4.0.106.453 4.0 Release
PromptName nvarchar (255) True Depends on customer usage

Name of the prompt.

4.0.106.453 4.0 Release
RecordingFile nvarchar (255) True Depends on customer usage

Filename of the recorded file. All survey prompts are located at I3ICResourcesSurveyPrompts on the IC server.

4.0.106.453 4.0 Release
PromptText nvarchar (1024) True Depends on customer usage

Text-to-speech that will play if the prompt is unavailable.

4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyPromptID char (32) False No

ID of the survey prompt.

4.0.106.453 4.0 Release
PromptType integer False No

Type of prompt.

  • 0 - None
  • 1 - File-based Prompt
  • 2 - Handler-based Prompt
  • 3 - TTS-based Prompt
4.0.106.453 4.0 Release
PromptName varchar2 (255) True Depends on customer usage

Name of the prompt.

4.0.106.453 4.0 Release
RecordingFile nvarchar2 (255) True Depends on customer usage

Filename of the recorded file. All survey prompts are located at I3ICResourcesSurveyPrompts on the IC server.

4.0.106.453 4.0 Release
PromptText nvarchar2 (1024) True Depends on customer usage

Text-to-speech that will play if the prompt is unavailable.

4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyPrompt Clustered SurveyPromptID (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyPrompt SurveyPromptID (Ascending)


SurveyQstnToQuestionCategory Table

The SurveyQstnToQuestionCategory maps questions to question categories. This is a many-to-many relationship.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionCategoryId varchar (32) False No

ID of the survey question category.

4.0.106.453 4.0 Release
SurveyQuestionId varchar (32) False No

ID of the survey question.

4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionCategoryId char (32) False No

ID of the survey question category.

4.0.106.453 4.0 Release
SurveyQuestionId char (32) False No

ID of the survey question.

4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyQstnToQstnCategory Clustered SurveyQuestionId (Ascending)
SurveyQuestionCategoryId (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyQstnToQstnCategory SurveyQuestionId (Ascending)
SurveyQuestionCategoryId (Ascending)

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyQstnToQuestionCategory_SurveyQuestionCategoryId SurveyQuestionCategoryId SurveyQuestionCategory SurveyQuestionCategoryId Yes
FK_SurveyQstnToQuestionCategory_SurveyQuestionId SurveyQuestionId SurveyQuestion SurveyQuestionID Yes

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SRVQSTNTOQSTNCAT_SRVQCATID SurveyQuestionCategoryId SurveyQuestionCategory SurveyQuestionCategoryId Yes
FK_SRVQSTNTOQSTNCAT_SRVQSTNID SurveyQuestionId SurveyQuestion SurveyQuestionID Yes

Index Name Type Column Name (sort order)
IX_SurveyQstnToQuestionCategory_SurveyQuestionCategoryId Non-clustered SurveyQuestionCategoryId (Ascending)

Index Name Column Name (sort order)
IX_SRVQSTNTOQSTNCAT_SRVQCATID SurveyQuestionCategoryId (Ascending)

SurveyQuestion Table

The SurveyQuestion table contains the definition of questions in a survey or questions that are available to be added to a survey.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionID varchar (32) False No

ID of the survey question.

4.0.106.453 4.0 Release
SurveyID varchar (32) True No

ID of the survey that this question belongs to. Null if this question does not belong to a survey because it is in the library.

4.0.106.453 4.0 Release
QuestionParentId varchar (32) True No

ID of the parent question.

4.0.106.453 4.0 Release
QuestionName nvarchar (255) False Depends on customer usage

Name of the question.

4.0.106.453 4.0 Release
QuestionText nvarchar (255) True Depends on customer usage

Description of the question.

4.0.106.453 4.0 Release
MinScore numeric False No

Minimum score that can be given to the question.

4.0.106.453 4.0 Release
MaxScore numeric False No

Maximum score that can be given to the question.

4.0.106.453 4.0 Release
IsOptional int False No

1 if the question is optional else 0. Other values are not defined.

4.0.106.453 4.0 Release
IsTemplate int False No

1 if the question is a template else 0. Other values are not defined. Template questions are stored in the library and won't have any survey associated with them.

4.0.106.453 4.0 Release
QuestionType int False No

The type of question.

  • 0 - None.
  • 1 - True / False
  • 2 - Multiple Choice
  • 3 - Numeric Range
  • 4 - Free-form (recording)
  • 5 - information
4.0.106.453 4.0 Release
Weight numeric False No

Weight given to the question when calculating a survey's score.

4.0.106.453 4.0 Release
Sequence int True No

Sequence number of the question within the survey. Numbering starts at zero.

4.0.106.453 4.0 Release
Timeout int True No

Timeout, in seconds, when waiting for a response to the question.

4.0.106.453 4.0 Release
Retries int True No

Number of retries the caller gets to answer the question.

4.0.106.453 4.0 Release
Options nvarchar (255) True No

TUI options for recording comments. The Options field is a pipe delimited string for TUI options for a free form recording. The pipe delimited components are as follows:

  • Terminate Keys - A comma delimited set of keys to signify the end of a recording.
  • Escape Key - Key to escape from making a recording.
  • Save Key - Key to confirm the recording should be saved.
  • Re-Record Key - Key to re-record the comment.
  • Replay Key - Key to replay the recording just made.
  • Confirm Recording - One to confirm the recording after it's been made. Zero to not confirm the recording.

An example of an options string might be:
*,#,1,2,3,4,5,6,7,8,9,0|*|1|3|2|0
4.0.106.453 4.0 Release
MinAcceptableScore numeric False No

The minimum score that a question can have and still be considered acceptable (implemented?)

4.0.106.453 4.0 Release
FailSurveyScore numeric True No

The score at which a question can cause an entire survey to fail (implemented?)

4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionID char (32) False No

ID of the survey question.

4.0.106.453 4.0 Release
SurveyID char (32) True No

ID of the survey that this question belongs to. Null if this question does not belong to a survey because it is in the library.

4.0.106.453 4.0 Release
QuestionParentId char (32) True No

ID of the parent question.

4.0.106.453 4.0 Release
QuestionName nvarchar2 (255) False Depends on customer usage

Name of the question.

4.0.106.453 4.0 Release
QuestionText nvarchar2 (255) True Depends on customer usage

Description of the question.

4.0.106.453 4.0 Release
MinScore number False No

Minimum score that can be given to the question.

4.0.106.453 4.0 Release
MaxScore number False No

Maximum score that can be given to the question.

4.0.106.453 4.0 Release
IsOptional integer False No

1 if the question is optional else 0. Other values are not defined.

4.0.106.453 4.0 Release
IsTemplate integer False No

1 if the question is a template else 0. Other values are not defined. Template questions are stored in the library and won't have any survey associated with them.

4.0.106.453 4.0 Release
QuestionType integer False No

The type of question.

  • 0 - None.
  • 1 - True / False
  • 2 - Multiple Choice
  • 3 - Numeric Range
  • 4 - Free-form (recording)
  • 5 - information
4.0.106.453 4.0 Release
Weight number False No

Weight given to the question when calculating a survey's score.

4.0.106.453 4.0 Release
Sequence integer True No

Sequence number of the question within the survey. Numbering starts at zero.

4.0.106.453 4.0 Release
Timeout integer True No

Timeout, in seconds, when waiting for a response to the question.

4.0.106.453 4.0 Release
Retries integer True No

Number of retries the caller gets to answer the question.

4.0.106.453 4.0 Release
Options nvarchar2 (255) True No

TUI options for recording comments. The Options field is a pipe delimited string for TUI options for a free form recording. The pipe delimited components are as follows:

  • Terminate Keys - A comma delimited set of keys to signify the end of a recording.
  • Escape Key - Key to escape from making a recording.
  • Save Key - Key to confirm the recording should be saved.
  • Re-Record Key - Key to re-record the comment.
  • Replay Key - Key to replay the recording just made.
  • Confirm Recording - One to confirm the recording after it's been made. Zero to not confirm the recording.

An example of an options string might be:
*,#,1,2,3,4,5,6,7,8,9,0|*|1|3|2|0
4.0.106.453 4.0 Release
MinAcceptableScore number False No

The minimum score that a question can have and still be considered acceptable (implemented?)

4.0.106.453 4.0 Release
FailSurveyScore number True No

The score at which a question can cause an entire survey to fail (implemented?)

4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyQuestion Clustered SurveyQuestionID (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyQuestion SurveyQuestionID (Ascending)

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyQuestion_SurveyID SurveyID Survey SurveyID Yes

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyQuestion_SurveyID SurveyID Survey SurveyID Yes

Index Name Type Column Name (sort order)
IX_SurveyQuestion_QuestionName Non-clustered QuestionName (Ascending)
IX_SurveyQuestion_Sequence Non-clustered Sequence (Ascending)
IX_SurveyQuestion_SurveyID Non-clustered SurveyID (Ascending)

Index Name Column Name (sort order)
IX_SurveyQuestion_QuestionName QuestionName (Ascending)
IX_SurveyQuestion_Sequence Sequence (Ascending)
IX_SurveyQuestion_SurveyID SurveyID (Ascending)

SurveyQuestionBranching Table

The SurveyQuestionBranching table contains the information for question branching. There is one entry in this table for each configured branch.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionBranchingID uniqueidentifier False No

ID of the survey question branching entry

4.0.106.453 4.0 Release
SurveyQuestionID varchar (32) False No

The ID of the question to which this branching entry corresponds

4.0.106.453 4.0 Release
OperatorType int True No

Operator type of the branching condition

  • 0 - Continue to Next
  • 1 - Less Than
  • 2 - Less Than Or Equal
  • 3 - Equal
  • 4 - Greater Than Or Equal
  • 5 - Greater Than
  • 6 - Between
  • 7 - Not Equal
  • 8 - Skip
4.0.106.453 4.0 Release
Operand1Value int True No

The operand1 of the branching operator

4.0.106.453 4.0 Release
Operand2Value int True No

The operand2 of the branching operator

4.0.106.453 4.0 Release
TargetQuestionID varchar (32) True No

The question ID to jump to if the condition is met

4.0.106.453 4.0 Release
Sequence int True No

The sequence of the branching entry

4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionBranchingID raw (16) False No

ID of the survey question branching entry

4.0.106.453 4.0 Release
SurveyQuestionID char (32) False No

The ID of the question to which this branching entry corresponds

4.0.106.453 4.0 Release
OperatorType integer True No

Operator type of the branching condition

  • 0 - Continue to Next
  • 1 - Less Than
  • 2 - Less Than Or Equal
  • 3 - Equal
  • 4 - Greater Than Or Equal
  • 5 - Greater Than
  • 6 - Between
  • 7 - Not Equal
  • 8 - Skip
4.0.106.453 4.0 Release
Operand1Value integer True No

The operand1 of the branching operator

4.0.106.453 4.0 Release
Operand2Value integer True No

The operand2 of the branching operator

4.0.106.453 4.0 Release
TargetQuestionID char (32) True No

The question ID to jump to if the condition is met

4.0.106.453 4.0 Release
Sequence integer True No

The sequence of the branching entry

4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyQuestionBranching Clustered SurveyQuestionBranchingID (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyQuestionBranching SurveyQuestionBranchingID (Ascending)

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyQuestionBranching_SurveyQuestionID SurveyQuestionID SurveyQuestion SurveyQuestionID Yes
FK_SurveyQuestionBranching_TargetQuestionID TargetQuestionID SurveyQuestion SurveyQuestionID Yes

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SRVQBRANCHING_SURVEYQSTNID SurveyQuestionID SurveyQuestion SurveyQuestionID Yes
FK_SRVQBRANCHING_BRANCHQSTNID TargetQuestionID SurveyQuestion SurveyQuestionID Yes

SurveyQuestionCategory Table

The SurveyQuestionCategory table contains the definition of question categories.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionCategoryId varchar (32) False No

ID of the survey question category.

4.0.106.453 4.0 Release
CategoryName nvarchar (255) False Depends on customer usage

Name of the category.

4.0.106.453 4.0 Release
QuestionSemantics int True No

Meta data about the questions in this category. Internal Use Only.

4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionCategoryId char (32) False No

ID of the survey question category.

4.0.106.453 4.0 Release
CategoryName nvarchar2 (255) False Depends on customer usage

Name of the category.

4.0.106.453 4.0 Release
QuestionSemantics integer True No

Meta data about the questions in this category. Internal Use Only.

4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyQuestionCategory Non-clustered SurveyQuestionCategoryId (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyQuestionCategory SurveyQuestionCategoryId (Ascending)


Index Name Type Column Name (sort order)
IX_SurveyQuestionCategory_QuestionSemantics Non-clustered QuestionSemantics (Ascending)

Index Name Column Name (sort order)
IX_SurveyQuestionCategory_QuestionSemantics QuestionSemantics (Ascending)

SurveyQuestionEnum Table

The SurveyQuestionEnum table contains enumerations for true/false and multiple choice questions. It also contains min/max values for numeric questions.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionEnumId varchar (32) False No

ID of the survey question enumeration.

4.0.106.453 4.0 Release
SurveyQuestionID varchar (32) False No

ID of the question the enumeration value belongs to.

4.0.106.453 4.0 Release
EnumSequence int False No

Sequence number of the value within the enumeration. Numbering starts at zero.

4.0.106.453 4.0 Release
EnumName nvarchar (255) False No

Name of the enumeration value.

4.0.106.453 4.0 Release
EnumText nvarchar (255) True Depends on customer usage

Description of the enumeration value.

4.0.106.453 4.0 Release
EnumScore numeric True No

Score given if this enumeration value is selected.

4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SurveyQuestionEnumId char (32) False No

ID of the survey question enumeration.

4.0.106.453 4.0 Release
SurveyQuestionID char (32) False No

ID of the question the enumeration value belongs to.

4.0.106.453 4.0 Release
EnumSequence integer False No

Sequence number of the value within the enumeration. Numbering starts at zero.

4.0.106.453 4.0 Release
EnumName nvarchar2 (255) False No

Name of the enumeration value.

4.0.106.453 4.0 Release
EnumText nvarchar2 (255) True Depends on customer usage

Description of the enumeration value.

4.0.106.453 4.0 Release
EnumScore number True No

Score given if this enumeration value is selected.

4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyQuestionEnum Clustered SurveyQuestionEnumId (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyQuestionEnum SurveyQuestionEnumId (Ascending)

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyQuestionEnum_SurveyQuestionID SurveyQuestionID SurveyQuestion SurveyQuestionID Yes

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SURVEYQSTNENUM_SURVEYQSTNID SurveyQuestionID SurveyQuestion SurveyQuestionID Yes

Index Name Type Column Name (sort order)
IX_SurveyQuestionEnum_EnumSequence Non-clustered EnumSequence (Ascending)
IX_SurveyQuestionEnum_SurveyQuestionID Non-clustered SurveyQuestionID (Ascending)

Index Name Column Name (sort order)
IX_SURVEYQSTNENUM_ENUMSEQUENCE EnumSequence (Ascending)
IX_SURVEYQSTNENUM_SURVEYQSTNID SurveyQuestionID (Ascending)

SurveyRule Table

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
RuleID varchar (32) False No

ID of the survey rule.

4.0.106.453 4.0 Release
SurveyID varchar (32) False No

ID of the survey the rule applies to.

4.0.106.453 4.0 Release
AttributeId varchar (50) True No

Attribute name required by some rule types. (See the Explanation of Value Fields.)

4.0.106.453 4.0 Release
Operand int False No

Operand to apply to this rule.

  • 0 - None.
  • 1 - Equals.
  • 2 - Less Than.
  • 3 - Greater Than.
  • 4 - Not Equal.
4.0.106.453 4.0 Release
IntegerValue int True No

Integer value required by some rule types. (See the Explanation of Value Fields.)

4.0.106.453 4.0 Release
StringValue nvarchar (512) True Depends on customer usage

String value required by some rule types. (See the Explanation of Value Fields.)

4.0.106.453 4.0 Release
DateValue datetime True No

Date value required by some rule types. (See the Explanation of Value Fields.)

4.0.106.453 4.0 Release
RuleType int True No

Type of rule that dictates which values apply.

  • 0 - None.
  • 1 - Every Nth.
  • 2 - Max Per Day.
  • 3 - Call Attribute
  • 4 - Call Attribute with Regular Expression.
  • 5 - Retry on Opt-Out.
  • 6 - DateTime
4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
RuleID char (32) False No

ID of the survey rule.

4.0.106.453 4.0 Release
SurveyID char (32) False No

ID of the survey the rule applies to.

4.0.106.453 4.0 Release
AttributeId varchar2 (50) True No

Attribute name required by some rule types. (See the Explanation of Value Fields.)

4.0.106.453 4.0 Release
Operand integer False No

Operand to apply to this rule.

  • 0 - None.
  • 1 - Equals.
  • 2 - Less Than.
  • 3 - Greater Than.
  • 4 - Not Equal.
4.0.106.453 4.0 Release
IntegerValue integer True No

Integer value required by some rule types. (See the Explanation of Value Fields.)

4.0.106.453 4.0 Release
StringValue nvarchar2 (512) True Depends on customer usage

String value required by some rule types. (See the Explanation of Value Fields.)

4.0.106.453 4.0 Release
DateValue date True No

Date value required by some rule types. (See the Explanation of Value Fields.)

4.0.106.453 4.0 Release
RuleType integer True No

Type of rule that dictates which values apply.

  • 0 - None.
  • 1 - Every Nth.
  • 2 - Max Per Day.
  • 3 - Call Attribute
  • 4 - Call Attribute with Regular Expression.
  • 5 - Retry on Opt-Out.
  • 6 - DateTime
4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyRule Clustered RuleID (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyRule RuleID (Ascending)

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyRule_SurveyID SurveyID Survey SurveyID Yes

Foreign Key Name Column Reference Table Reference Column Indexed
FK_SurveyRule_SurveyID SurveyID Survey SurveyID Yes

Index Name Type Column Name (sort order)
IX_SurveyRule_SurveyId Non-clustered SurveyID (Ascending)

Index Name Column Name (sort order)
IX_SurveyRule_SurveyId SurveyID (Ascending)

Explanation of Value Fields

Rule

Rule Type

Operand

AttributeId

IntegerValue

StringValue

DateValue

Calls routed to these workgroups

3 (Call attribute)

1 (Equals)

Eic_WorkgroupName

0

Workgroup Name

NULL

From the following callers (ANI)

3 (Call attribute)

1 (Equals)

Eic_RemoteTnRaw

0

Phone Number

NULL

Calls to these numbers

3 (Call attribute)

1 (Equals)

Eic_LocalTnRaw

0

Phone Number

NULL

Every Nth Call

1 (Every nth)

0 (None)

NULL

Nth Call

NULL

NULL

Max number of surveys per day

2 (Max per day)

0 (None)

NULL

Max surveys

NULL

NULL

Between these times

6 (DateTime)

0 (None)

NULL

0

StartTime|EndTime||||||

NULL

Between these dates

6 (DateTime)

0 (None)

NULL

0

||StartDate|EndDate||||

NULL

During these weekdays

6 (DateTime)

0 (None)

NULL

0

||||Weekdays||

NULL

During these week/ days of the month

6 (DateTime)

0 (None)

NULL

0

||||||DayWeek|

NULL

During these days of the month

6 (DateTime)

0 (None)

NULL

0

|||||Days||

NULL

During these months

6 (DateTime)

0 (None)

NULL

0

|||||||Months

NULL

The call has Attribute set to Value

3 (Call attribute)

1 (Equals)

Attribute Name

0

Attribute Value

NULL

Note that the date values on some rules consist of a pipe delimited string. Each rule uses one or two fields of that pipe delimited list with the rest of the values being empty. For reference, the following are all of the fields in that delimited string.

ST|ET|SD|ED|DA|DY|DW|MO

ST – Start Time

ET – End Time

SD – Start Date

ED – End Date

DA – Day of theWeek

DY – Day of the Month

DW – Day of the Week and Week of the Year

MO – Month of the Year

For the rule 'Between these times' we use the Start and End Time fields. These fields should be in 24-hour time with no separator between the hour and minute. For example, the value of a time range between 9:00 and 17:00 would be "0900|1700||||||".

For the rule 'Between these dates' we use the Start and End Date fields. These fields consist of the year followed by the month followed by the day. For example, the value for a date range between 08/01/2011 and 08/31/2011 would be "||20110801|20110831||||".

For the rule 'During these weekdays' we use the Day of the Week field. This field consists of a three letter acronym for the day of the week. Multiple days are split by a semi-colon. For example, the value of the days of the week Monday through Friday would be "||||mon;tue;wed;thu;fri|||".

For the rule 'During these week/days of the month' we use the Day of the Week and Week of the Year field. This field consists of a three letter abbreviation for the day of the week followed by a number that represents the week of the year. Multiple day/week values are separated by a semi-colon. For example, the value of the first Monday and the third Friday would be "||||||mon1;fri3|".

For the rule 'During these days of the month' we use the Day of the Month field. This field consists of a numeric value for the day. Multiple days are separated by a semi-colon. For example, the value of the first and fifteenth of the month would be "|||||1;15||".

For the rule 'During these months' we use the Month of the Year field. This field consists of a three letter acronym for the month. Multiple months are split by a semi-colon. For example, the value of January through March would be "|||||||jan;feb;mar".

Tables for Feedback Server Use Only

The following tables are for internal use only and are used in the internal workings for the Feedback Server.

SurveyConfigLog Table

The SurveyConfigLog table consists of changes made to surveys. It holds at most two years of data.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
ConfigChangeObjectType int False No

The part of the survey that was changed, e.g. question, prompt, rule (See the following Explanation of ConfigChangeObjectType.)

4.0.106.453 4.0 Release
ConfigChangeType int False No

The type of change made to a survey, e.g. Add, Update, Delete. (See the following Explanation of ConfigChangeType.)

4.0.106.453 4.0 Release
ConfigTimeStampUTC datetime False No

UTC timestamp for the event

4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
ConfigChangeObjectType integer False No

The part of the survey that was changed, e.g. question, prompt, rule (See the following Explanation of ConfigChangeObjectType.)

4.0.106.453 4.0 Release
ConfigChangeType integer False No

The type of change made to a survey, e.g. Add, Update, Delete. (See the following Explanation of ConfigChangeType.)

4.0.106.453 4.0 Release
ConfigTimeStampUTC timestamp False No

UTC timestamp for the event

4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyConfigLog Clustered ConfigTimeStampUTC (Ascending)
ConfigChangeType (Ascending)
ConfigChangeObjectType (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyConfigLog ConfigTimeStampUTC (Ascending)
ConfigChangeType (Ascending)
ConfigChangeObjectType (Ascending)


SurveyUserObjectSecurity Table

The SurveyUserObjectSecurity table consists of a computed set of security rights for users currently logged into Interaction Center Business Manager.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
IndivID char (22) False Yes

4.0.106.453 4.0 Release
ObjectType int False No

4.0.106.453 4.0 Release
ObjectName nvarchar (255) True No

4.0.106.453 4.0 Release
ObjectID int True No

4.0.106.453 4.0 Release
ExtObjectID varchar (32) False No

4.0.106.453 4.0 Release
UserRight int False No

4.0.106.453 4.0 Release
SiteIdentifier int False No

4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
IndivID char (22) False Yes

4.0.106.453 4.0 Release
ObjectType integer False No

4.0.106.453 4.0 Release
ObjectName nvarchar2 (255) True No

4.0.106.453 4.0 Release
ObjectID integer True No

4.0.106.453 4.0 Release
ExtObjectID char (32) False No

4.0.106.453 4.0 Release
UserRight integer False No

4.0.106.453 4.0 Release
SiteIdentifier integer False No

4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyUserObjSecurity Clustered SiteIdentifier (Ascending)
ObjectType (Ascending)
IndivID (Ascending)
ExtObjectID (Ascending)

Primary Key Name Column Name (sort order)
pk_surveyuserobjectsecurity SiteIdentifier (Ascending)
ObjectType (Ascending)
IndivID (Ascending)
ExtObjectID (Ascending)


SurveyWork Table

The SurveyWork table contains temporary data used when a search is performed from Interaction Center Business Manager.

Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SessionID varchar (32) False No

4.0.106.453 4.0 Release
ItemID uniqueidentifier False No

4.0.106.453 4.0 Release
Data nvarchar (255) True No

4.0.106.453 4.0 Release
Column Data Type (Size) Is Nullable Contains PII Description Last Change Version Last Change Reason
SessionID char (32) False No

4.0.106.453 4.0 Release
ItemID raw (16) False No

4.0.106.453 4.0 Release
Data nvarchar2 (255) True No

4.0.106.453 4.0 Release

Constraints

Primary Key Name Type Column Name (sort order)
PK_SurveyWork Clustered SessionID (Ascending)
ItemID (Ascending)

Primary Key Name Column Name (sort order)
PK_SurveyWork SessionID (Ascending)
ItemID (Ascending)


Index Name Type Column Name (sort order)
IX_SurveyWork_ItemID Non-clustered ItemID (Ascending)

Index Name Column Name (sort order)
IX_SurveyWork_ItemID ItemID (Ascending)