PCM Raw data Ticket related tables
PCM Raw data Ticket-related tables
- cm_form_field_answers
- cm_reporting_ticket_activities
- cm_reporting_ticket_form_field_choices
- cm_reporting_ticket_resolves
- cm_reporting_ticket_responses
- cm_reporting_ticket_states
- cm_tags_tickets
- cm_ticket_events
- cm_ticket_responses
- cm_tickets
- cm_jira_issues
- cm_time_trackers
cm_form_field_answers
Contains/used for: A ticket may (or may not) relate to a form. If such a relation is made, and the form has been filled out, such form choices/answers are stored in this table.
Column name | Data type | Description |
form_field_answer_id | bigint | Unique id for this record. |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
ticket_id | bigint | Ref. to the ticket, foreign key to cm_tickets. |
form_field_id | bigint | The id for the form field used. |
form_id | int | Ref. to the form used, re. table cm_forms |
ticket_number | bigint | The number of the ticket as seen, not the internal ticket_id. |
choice_id | bigint | The id for the choice made. |
parent_id | bigint | The id of the parent field (if nested) |
dte_created_at | datetime2 | The date the field was first populated |
dte_updated_at | datetime2 | The date the field had it's latest update |
form_field_name | nvarchar(500) | Name of the field where the answer is put into. |
choice_name | nvarchar(500) | This column contains the name of the choice. |
utc_transfer_timestamp | datetime2 | Date/timestamp that the data was transferred to RawData |
cm_reporting_ticket_activities
Contains/used for: A ticket can have many activities such as Team Changed, User Changed, Message Received and Message Sent etc... When such activities are processed, the activities are stored in this table.
Column name | Data type | Description |
reporting_ticket_activity_id | bigint | Unique id for this record. |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
reporting_ticket_state_id | bigint | Ref. to table cm_reporting_ticket_states. |
user_id | bigint | Ref. to table cm_users. |
activity_type | int | Ref. to table cm_activity_types |
ticket_activity_type | nvarchar(200) | Description of the ticket_activity_id |
ticket_activity_id | bigint | The ID associated to the ticket_activity |
duration | int | Time logged against the activity (for Time Tracker module) |
dte_created_at | datetime2 | The date/time recorded of the the latest activity |
dte_updated_at | datetime2 | The date/time recorded of the the latest activity |
b_forward_related | bit | Boolean. 0=false, 1=true. |
b_autoreply | bit | Boolean. 0=false, 1=true. |
utc_transfer_timestamp | datetime2 | Date/timestamp that the data was transferred to RawData |
cm_reporting_ticket_form_field_choices
Contains/used for: <To be updated!!>
Column name | Data type | Description |
reporting_ticket_form_field_choice_id | bigint | Unique id for this record. |
reporting_ticket_state_id | bigint | Ref. to table cm_reporting_ticket_states |
choice_id | bigint | <To be updated!!> |
dte_created_at | datetime2 | The date the field was first populated |
dte_updated_at | datetime2 | The date the field had it's latest update |
utc_transfer_timestamp | datetime2 | Date/timestamp that the data was transferred to RawData |
cm_reporting_ticket_resolves
Contains/used for: A ticket can have one or multiple resolutions. When such a status is processed, the resolution is stored in this table.
Column name | Data type | Description |
reporting_ticket_resolves_id | bigint | Unique id for this record. |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
resolve_time | bigint | Total accumulated time in an 'Open' state |
reporting_ticket_state_id | bigint | Ref. to table cm_reporting_ticket_states. |
b_resolved_within_target | bit | Boolean. 0=false, 1=true. |
b_without_response | bit | Boolean. 0=false, 1=true. |
b_first_resolve | bit | Boolean. 0=false, 1=true. |
user_id | bigint | Ref. to table cm_users. |
dte_created_at | datetime2 | Date/Time of the latest resolution |
dte_updated_at | datetime2 | Date/Time of the latest resolution |
utc_transfer_timestamp | datetime2 | Date/timestamp that the data was transferred to RawData |
cm_reporting_ticket_responses
Contains/used for: <To be updated!!>
Column name | Data type | Description |
reporting_ticket_response_id | bigint | Unique id for this record. |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
response_time | bigint | <To be updated!!> |
reporting_ticket_state_id | bigint | Ref. to table cm_reporting_ticket_states. |
b_responded_within_target | bit | Boolean. 0=false, 1=true. |
b_first_response | bit | Boolean. 0=false, 1=true. |
b_during_working_hours | bit | Boolean. 0=false, 1=true. |
user_id | bigint | Ref. to table cm_users. |
dte_created_at | datetime2 | Date/Time of the latest resolution |
dte_updated_at | datetime2 | Date/Time of the latest resolution |
utc_transfer_timestamp | datetime2 | Date/timestamp that the data was transferred to RawData |
cm_reporting_ticket_states
Contains/used for: A ticket can have multiple states recorded against it during it's lifecycle. When state changes occur, the state is stored in this table.
Column name | Data type | Description |
reporting_ticket_state_id | bigint | Unique id for this record. |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
ticket_id | bigint | Ref. to the ticket, foreign key to cm_tickets. |
team_id | int | Ref. to table cm_teams. |
user_id | bigint | Ref. to table cm_users. |
priority | int | Ref. to table cm_priority_id_types. |
status | int | Ref. to table cm_status_types. |
previous_state_id | bigint | The ID of the previous record describing the previous ticket state |
tag_ids | nvarchar(1000) | Comma delimeted list of ID's for tags |
category_choice_ids | nvarchar(1000) | ID of the selected 'pre-defined' category value |
dte_created_at | datetime2 | Date of the first value being recorded in a field |
dte_updated_at | datetime2 | Date of the latest value being recorded in a field |
time_in_state | int | Duration between each recorded action or state |
ticket_type | int | Ref. to table cm_status_types. |
organisation_id | bigint | Ref. to table cm_organisations. |
form_id | int | Ref. to table cm_forms. |
parent_ticket_id | bigint | Ref. to this tickets parent ticket (if any). Refs. to a record in cm_tickets. |
utc_transfer_timestamp | datetime2 | Date/timestamp that the data was transferred to RawData |
cm_tags_tickets
Contains/used for: Shows the current state of tags on a ticket.
Column name | Data type | Description |
tag_id | bigint | Unique id for this record. |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
name | nvarchar(500) | name of the tag referred to by tag_id |
ticket_id | bigint | Ref. to the ticket, foreign key to cm_tickets. |
utc_transfer_timestamp | datetime2 | Date/timestamp that the data was transferred to RawData |
cm_ticket_events
Contains/used for: A ticket can have multiple events recorded against it during it's lifecycle. When an event occurs, the event is stored in this table.
Column name | Data type | Description |
ticket_event_id | bigint | Unique id for this record. |
ticket_id | bigint | Ref. to the ticket, foreign key to cm_tickets. |
data_as_json | nvarchar(max) | |
event_type | int | Ref. to table cm_event_types. |
dte_occurred | datetime2 | Date that the ticket event occured |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
user_id | bigint | ID of the user recorded against the event. |
utc_transfer_timestamp | datetime | Date/timestamp that the data was transferred to RawData |
cm_ticket_responses
Contains/used for: A ticket can have multiple responses during it's lifecycle. When response occurs, the response data is stored in this table.
Column name | Data type | Description |
ticket_response_id | bigint | Unique id for this record. |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
ticket_id | bigint | Ref. to the ticket, foreign key to cm_tickets. |
user_id | bigint | Ref. to table cm_users. |
sec_response_time | bigint | Time between inbound message and reply |
dte_responded_at | datetime2 | Date/Time of response |
b_during_working_hours | bit | Boolean. 0=false, 1=true. |
b_first_response | bit | Boolean. 0=false, 1=true. |
renderable_id | int | ID associated to the renderable type |
renderable_type | nvarchar(200) | Description of the renderable_id |
utc_transfer_timestamp | datetime2 | Date/timestamp that the data was transferred to RawData |
cm_tickets
Contains/used for: This is the "main" table for tickets. There is one record is this table for each ticket. All ticket related data in other tables refers to a record here, either directly, or (sometimes) via other table(s).
Column name | Data type | Description |
ticket_id | bigint | Unique Ticket ID. |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
ticket_number | bigint | Ticket reference number |
customer_id | bigint | Ref. to customer, foreign key to cm_customers. |
subject | nvarchar(1000) | Ticket subject |
dte_opened | datetime | Date/Time ticket created in PCM application |
dte_closed | datetime | Date/Time ticket goes into 'Closed' status |
priority_id | int | Ref. to priority, foreign key to cm_priority_id_types. |
dte_respond_by | datetime | The 'Respond By' SLA Date/Time |
user_id | int | Ref. to table cm_users. |
team_id | int | Ref. to table cm_teams. |
| autoreply_template_id | int | Ref. to autoreply foreign key to cm_templates |
| template_id | int | Ref to foreign key to cm_templates |
form_id | int | Ref. to table cm_forms. |
initial_channel_id | int | Ref. to table cm_channels. |
initial_channel_type | nvarchar(200) | Channel Type, ie Email / SMS / API |
dte_last_inbound | datetime | Date/Time of the latest inbound conversation |
dte_last_response | datetime | Date/Time of the latest coversation response |
b_manual | bit | Boolean. 0=false, 1=true. (indicate manual ticket) |
dte_on_hold | datetime | Date/Time ticket state went on-hold |
organisation_id | bigint | Ref. to table cm_organisations. |
dte_pending | datetime | Date/Time ticket state went into 'pending' status |
puzzel_reference | nvarchar(1000) | Puzzel Contact Center (eTask Request ID) |
dte_created_at | datetime2 | Date/Time ticket was created |
n_reassigns | int | Number of times a ticket has been re-assigned |
sec_resolution_time | bigint | Time between ticket creation and resolution |
post_it_note | nvarchar(1000) | Content saved in the Post It note field |
status_id | int | Ref. to table cm_status_types |
parent_ticket_number | int | Ticket ID or the linked Parent ticket |
dte_read | datetime | Date/Time the ticket was first read by an agent |
dte_reopened | datetime | Date/Time the ticket switched from an alternatate state to 'Open' |
n_reopens | int | Number of times a ticket has been re-opened from an alternate sate |
dte_resolve_by | datetime | The 'Resolved By' SLA Date/Time |
dte_resolved | datetime | The latest 'Resolved' date on the ticket |
dte_updated_at | datetime2 | The date/time of the last ticket activity |
used_templates | nvarchar(max) | Number of uses of templates per ticket |
vip_score | int | Calculation based on SLA target (score between 0-100) |
utc_transfer_timestamp | datetime | Date/timestamp that the data was transferred to RawData |
b_resolved_within_sla | bit | Boolean. 0=false, 1=true. |
| jira_issue_id | bigint | Jira issue ID linked to a PCM ticket |
cm_jira_issues
Contains/used for: Retrieving contents of Jira issues linked to a ticket in Puzzel Case Management.
Column name | Data type | Description |
jira_issue_id | bgint | Jira issue ID linked to a PCM ticket |
tenant_id | int | Ref. to tenant, foreign key to cm_tenants. |
key | varchar | |
summary | nvarchar | Summary field of Jira issue |
browse_url | nvarchar | URL for browsing to the Jira issue |
issue_type | nvarchar | Jira issue type |
project_name | nvarchar | Name of Jira project |
status | nvarchar | Status of Jira issue |
priority | nvarchar | Priority of Jira issue |
assignee | nvarchar | Assigned user of Jira issue |
reporter | nvarchar | User who reported the Jira issue |
| dte_created_at | datetime2 | The time at which Jira issue was created |
| dte_updated_at | datetime2 | The time at which Jira issue was updated |
jira_project_id | int | Jira Project ID |
utc_transfer_timestamp | datetime2 | Date/timestamp that the data was transferred to RawData |
cm_time_trackers
Contains/used for: Recording time spent by agents working on a ticket when the 'time tracker' module is enabled.
Column name | Data type | Description |
time_tracker_id | bigint | |
tenant_id | int | |
time | int | |
ticket_id | bgint | |
user_id | bgint | |
team_id | int | |
dte_created_at | datetime2 | |
dte_updated_at | datetime2 | |
utc_transfer_timestamp | datetime2 |