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_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.

Download as PDF