Feed Schemas
S3 Feed Schemas
Feed Schemas
S3 Feed Schemas
The tables below provide detailed information regarding the schema for exported data files that ASAPP can make available to customers via S3.
Table: admin_activity
The admin_activity table tracks ONLINE/OFFLINE statuses and logged in time in seconds for agents who use Admin.
Sync Time: 1h
Unique Condition: company_id, rep_id, status_description, status_start_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | None | ||||
| rep_name | varchar(191) | Name of agent | John | None | None | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | None | ||||
| status_description | varchar | Indicates whether the agent was logged into Admin desktop for this period. | ONLINE | None | None | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | None | ||||
| status_start_ts | datetime | Timestamp at which this agent entered that status. | 2018-06-10 14:23:00 | None | None | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | None | ||||
| status_end_ts | datetime | Timestamp at which this agent exited that status. | 2018-06-10 14:23:00 | None | None | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | None | ||||
| status_time_seconds | double | Time in seconds that the agents spent in that status. | 2353.23 | None | None | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | None |
Table: agent_journey_rep_event_frequency
Aggregated counts of various agent journey event types partitioned by rep_id
Sync Time: 1d
Unique Condition: primary-key: rep_id, event_type, company_marker, instance_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | None | ||||
| company_marker | varchar(191) | The ASAPP company marker. | spear, aa | None | None | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | None | ||||
| event_type | varchar(191) | agent journey event type on record | CUSTOMER_TIMEOUT, TEXT_MESSAGE | None | None | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | None | ||||
| event_count | bigint | count of the agent journey event type on record | None | None | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | None | |||||
| disconnected_count | bigint | number of times that a rep disconnected for less than 1 hour | None | None | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | None | |||||
| disconnected_seconds | bigint | cumulative number of seconds that a rep disconnected for less than 1 hour | None | None | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | None |
Table: autopilot_flow
This table contains factual data about autopilot flow.
Sync Time: 1h
Unique Condition: company_marker, issue_id, form_start_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | ||||
| rep_d | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | |||||||
| rep_assigned_ts | timestamp without time zone | issue_id+rep_id+rep_assigned_ts can be used to link to the issue’s rep assignment or live agent opportunity | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | |||||
| form_start_ts | timestamp without time zone | Timestamp of autopilot form/flow being recommended by MLE or timestamp of flow sent from quick send. issue_id + form_recommended_event_ts should be unique | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | |||||
| form_dismissed_event_ts | timestamp without time zone | Timestamp of recommended autopilot form being dismissed. | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | |||||
| form_presented_event_ts | timestamp without time zone | Timestamp the autopilot form being presented to end user. | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | |||||
| form_submitted_event_ts | timestamp without time zone | Timestamp the autopilot form being submitted by end user | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | |||||
| flow_id | varchar(255) | An ASAPP identifier assigned to a particular flow executed during a customer event or request. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | ||||
| flow_name | varchar(255) | The ASAPP text name for a given flow which was executed during a customer event or request. | FirstChatMessage, AccountNumberFlow | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | ||||
| form_start_from | character varying(191) | how the flow is being sent by the agent manual: sent manually from the quick send dropdown in desk accept: sent by accept recommendation by ML server | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | |||||
| is_secure_form | boolean | Is this a secure form flow. | false | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | ||||
| queue_id | integer | The ASAPP queue identifier which the issue was placed. | 210001 | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | ||||
| asapp_mode | varchar(191) | Mode of the desktop that the rep is logged into (CHAT or VOICE). | CHAT, VOICE | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | None |
Table: convos_intents
The convos_intents table lists the current state for intent and utterance information associated with a conversation/issue that had events within the identified 15 minute time window. This table will include unended conversations.
Sync Time: 1h
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_utterance_ts | varchar(255) | The timestamp of the first customer utterance for an issue. | 2018-09-05 19:58:06 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_utterance_text | varchar(255) | The text of the first customer utterance for an issue. | ‘Pay my bill’, ‘Check service availability’ | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_intent_code | varchar(255) | The first intent code associated to the issue. | PAYBILL, COVERAGE | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_intent_code_alt | varchar(255) | The alternate first intent code, which is the second best classification of the customer’s first text message. | PAYBILL, COVERAGE | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| final_intent_code | varchar(255) | The last intent code of the flow the user navigated through, if the customer navigated through a flow, otherwise this will be the same as the first intent code | PAYBILL, COVERAGE | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| intent_path | varchar(255) | A comma-separated list of all the intent codes associated with the customer’s flow navigiation. If the user did not navigate through a flow, this will be same as the first intent code. | OUTAGE,CANT_CONNECT | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| disambig_count | bigint | The number of times a disambiguation event was presented for an issue. | 2 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| ftd_visit | boolean | A boolean value which indicates if this issue used free-text disambiguation to allow the customer to present a clearer intent, based on number of texts typed to AI. | true, false | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| faq_id | varchar(255) | The last FAQ identifier presented for an issue. | FORGOT_LOGIN_faq | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| final_action_destination | varchar(255) | The last deep-link url clicked for an issue. | asapp-pil://acme/JSONataDeepLink | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| is_first_intent_correct | boolean | If the chat went to an agent, this indicates whether the initial intent associated with the chat was correct or not, if the agent fills out this information. | true, false | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| first_rep_id | varchar(191) | The first ASAPP rep/agent identifier found in a window of time. | 123008 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: convos_intents_ended
The convos_intents_ended table lists the current state for intent and utterance information associated with a conversation/issue that have had events within the identified 15 minute time window. This table will filter out unended conversations.
Sync Time: 1h
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-11-07 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| first_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | None | None | 2018-11-07 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_utterance_ts | varchar(255) | The timestamp of the first customer utterance for an issue. | 2018-09-05 19:58:06T00:01:16.203000+00:00 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_utterance_text | varchar(255) | The text of the first customer utterance for an issue. | I need to pay my bill. | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_intent_code | varchar(255) | The first intent code associated to the issue. | PAYBILL | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_intent_code_alt | varchar(255) | The alternate first intent code, which is the second best classification of the customer’s first text message. | PAYBILL | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| final_intent_code | varchar(255) | The last intent code of the flow the user navigated through, if the customer navigated through a flow, otherwise this will be the same as the first intent code. | PAYBILL | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| intent_path | varchar(255) | A comma-separated list of all intent codes assoicated with the customer’s flow navigiation. If the user did not navigate through a flow, this will be same as the first intent code. | OUTAGE, CANT_CONNECT | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| disambig_count | bigint | The number of times a disambiguation event was presented for an issue. | 2 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| ftd_visit | boolean | A boolean value which indicates if this issue used free-text disambiguation to allow the customer to present a clearer intent, based on the number of texts typed to AI. | false, true | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| faq_id | varchar(255) | The last faq-id presented for an issue. | FORGOT_LOGIN_faq | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| final_action_destination | varchar(255) | The last deep-link url clicked for an issue. | asapp-pil://acme-mobile/protection-plan-features | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| is_first_intent_correct | boolean | If the chat went to an agent, this indicates whether the initial intent associated with the chat was correct or not, if the agent fills in this information. | true, false | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| first_rep_id | varchar(191) | The first ASAPP rep/agent identifier found in a window of time. | 123008 | None | None | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: convos_metadata
This convos_metadata table contains data associated with a conversation/ issue during a specific 15 minute window. This table will include data from unended conversations. Expect to see columns containing the app_version, the conversation_end timestamp and whether it was escalated to chat or not.
Sync Time: 1h
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| first_utterance_ts | timestamp | Date of the first utterance from a customer. | 2018-09-05 19:58:06 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| first_utterance_text | varchar(255) | The text of the first utterance from a customer. | ”Hello, please assist me” | None | None | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| issue_created_ts | timestamp | The timestamp of the “NEW_ISSUE” event for an issue. | 2018-09-05 19:58:06 | None | None | 2019-10-15 00:00:00 | 2019-10-15 00:00:00 | None | ||||
| last_event_ts | timestamp | The timestamp of the last event for an issue. | 2018-09-05 19:58:06 | None | None | 2019-09-16 00:00:00 | 2019-09-16 00:00:00 | None | ||||
| last_srs_event_ts | timestamp without time zone | The date of the last bot assisted event. | 2018-09-05 19:58:06 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| conversation_end_ts | timestamp | The time/date of the issue end. | 2018-09-05 19:58:06 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| session_id | varchar(128) | The ASAPP session identifier. It is a uuid generated by the chat backend. Note: a session may contain several conversations. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| session_type | character varying(255) | The ASAPP session type. | asapp-uuid | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| session_event_type | character varying(255) | The basic type of the session event. | UPDATE, CREATE | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| internal_session_id | character varying(255) | The ASAPP session identifier. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| internal_session_type | character varying(255) | An ASAPP session type. | asapp-uuid | None | None | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | None | ||||
| internal_user_identifier | varchar(255) | The ASAPP customer identifier while using the asapp system. This identifier may represent either a rep or a customer. Use the internal_user_type field to determine which type the identifier represents. | 123004 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| internal_user_session_type | varchar(255) | The customer ASAPP session type. | customer | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| external_session_id | character varying(255) | A client passed argument to the SDK at open chat to indicate the client session identifier. | 062906ff-3821-4b5d-9443-ed4fecbda129 | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| external_session_type | character varying(255) | A client passed argument to SDK at open chat to indicate the type of the session. | visitID | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| external_user_id | varchar(255) | The customer identifier according to the client. This will only exist if the customer was authenticated. | EECACBD227CCE91BAF5128DFF4FFDBEC | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| external_user_type | varchar(255) | The type of external user identifier. | acme_CUSTOMER_ACCOUNT_ID | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| external_issue_id | character varying(255) | The client passed argument for the issue identifier to the SDK (currently not in use). | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | |||||
| external_channel | character varying(255) | The client passed argument for the customer channel to the SDK (currently not in use). | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | |||||
| customer_id | bigint | ASAPP customer id | 1470001 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| escalated_to_chat | bigint | A boolean value indicating whether this issue was sent to an agent. false, true | 1 | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| platform | varchar(255) | A value indicating which consumer platform was used. | ios, android, web | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| device_type | varchar(255) | The last device type used by the customer for an issue. | mobile, tablet, desktop, watch, unknown | None | None | 2019-06-17 00:00:00 | 2019-06-17 00:00:00 | None | ||||
| first_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | None | None | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | None | ||||
| last_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | None | None | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | None | ||||
| external_agent_id | varchar(255) | deprecated: 2019-09-25 | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | None | ||||
| assigned_to_rep_time | timestamp | The timestamp at which the issue was assigned to a rep for the first time, if any. | 2018-09-05 19:58:06 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| disposition_event_type | varchar(255) | This field captures how the conversation with the customer was ended. | resolved, unresolved, timeout | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| disposition_ts | timestamp | The timestamp at which the rep exits the issue. | 2018-09-05 19:58:06 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| termination_event_type | varchar(255) | This field captures what ends the conversation. | customer, agent, autoend | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| disposition_notes | text | The notes which the last rep took after dispositioning the chat. | ”The customer wanted to pay his bill. We successfully processed his payment.” | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| ended_resolved | integer | 1 if the rep marked the conversation resolved, 0 otherwise. | 1, 0 | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| ended_unresolved | integer | 1 if the rep marked the conversation unresolved, 0 otherwise. | 0, 1 | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| ended_timeout | integer | 1 if the customer timed out or abandoned chat, 0 otherwise. | 0, 1 | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| ended_auto | integer | 1 if the rep did not disposition the issue and it was auto-ended. | 0, 1 | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| ended_other | integer | 1 if the customer or rep terminated the issue but the rep didn’t disposition the issue. | 0, 1 | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| app_version_asapp | varchar(255) | The version of the ASAPP API application utilized during a customer event or request. | com.asapp.api_api:-2f1a053f70c57f94752e7616b66f56d7bf1d6675 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| app_version_client | varchar(255) | The version of the ASAPP SDK utilized during a customer event or request. | web-sdk-4.0.0 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| session_metadata | character varying(65535) | The client passed in extra metadata information about the session. | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | |||||
| last_sequence_id | integer | The last sequence identifier of the issue. | 115 | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| issue_queue_id | varchar(255) | The queue identifier of the associated issue. | 20001 | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| issue_queue_name | varchar(255) | The queue name of the associated issue. | acme-wireless-english | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| csat_rating | double precision | The CSAT rating of the issue. | 400.0 | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| sentiment_valence | character varying(50) | The sentiment of the issue. | Neutral, Negative | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| deep_link_queue | character varying(65535) | The deeplink queued for the issue. | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | |||||
| end_srs_selection | character varying(65535) | The user selected button upon end_srs. | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | |||||
| trigger_link | VARCHAR | deprecated: 2020-04-25 aliases: current_page_url | None | None | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | None | |||||
| auth_state | varchar(3) | Boolean value to indicate whether the user authenticated? (not computed yet) | false, true | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| auth_external_token_id | character varying(65535) | Encrypted user identifier, provided by the client system, associated with the first authentication event for an issue. | 82EFDDADC5466501443E3E61ED640162 | None | None | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| auth_source | character varying(65535) | Source of the first authentication event for an issue. | ivr-url | None | None | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| auth_external_user_type | character varying(65535) | External user type of the first authentication event for an issue. | ACME_CUSTOMER_ACCOUNT_ID | None | None | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| auth_external_user_id | character varying(65535) | Client provided field. External user id associated with the first authentication event for an issue. | 9BE62CCD564D6982FF305DEBCEAABBB5 | None | None | 2019-05-15 00:00:00 | 2019-07-16 00:00:00 | None | ||||
| is_review_required | boolean | Boolean to determine whether an admin should review this issue. data type: boolean data type: boolean | true, false | None | None | 2019-07-24 00:00:00 | 2019-07-24 00:00:00 | None | ||||
| mid_issue_auth_ts | timestamp without time zone | The time when the user authenticated, if it occured mid issue. | 2020-01-11 08:13:26.094 | None | None | 2019-07-24 00:00:00 | 2019-07-24 00:00:00 | None | ||||
| first_rep_id | varchar(191) | The first agent/rep involved with this issue as the ASAPP rep identifier. | 60001 | None | None | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | None | ||||
| last_rep_id | varchar(191) | The last agent/rep involved with this issue as the ASAPP rep identifier. | 60001 | None | None | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | None | ||||
| external_rep_id | varchar(255) | The client handed value for a rep identifier. | 0671018510 | None | None | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| first_voice_customer_state | varchar(255) | The first assigned state of the customer if using voice. | IDENTIFIED | None | None | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | None | ||||
| first_voice_customer_state_ts | timestamp | The time when the customer was first assigned a state. 2020-01-11 08:13:26.094 | 2018-09-05 19:58:06 | None | None | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | None | ||||
| first_voice_identified_customer_state_ts | timestamp | The time when the customer was first assigned an IDENTIFIED state. | 2020-01-11 08:13:26.094 | None | None | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | None | ||||
| first_voice_verified_customer_state_ts | timestamp | The time when the customer was first assigned a VERIFIED state. | 2020-01-11 08:13:26.094 | None | None | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | None | ||||
| merged_ts | timestamp | The time when the relevant issue merged into another issue. data type: timestamp | 2020-01-11 08:13:26.094 | None | None | 2019-12-28 00:00:00 | 2019-12-28 00:00:00 | None | ||||
| desk_mode_flag | bigint | Bitmap encodes if agent handled voice-issue ASAPP desk, had engagement with ASAPP desk. bitmap: 0: null, 1: ‘VOICE’, 2: ‘DESK’, 4: ‘ENGAGEMENT’, 8: ‘INACTIVITY’ NULL for non voice issues | 0, 1, 2, 5, 7 | None | None | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | None | ||||
| desk_mode_string | varchar(191) | Decodes the desk_mode flag. Current possible values (Null, ‘VOICE’, ‘VOICE_DESK’, ‘VOICE_DESK_ENGAGEMENT’,‘VOICE_INACTIVITY’). NULL for non voice issues. | VOICE_DESK | None | None | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | None | ||||
| current_page_url | varchar(2000) | The url link stripped of parameters that triggered the enter chat event. Only populates for WEB platforms. aliases: trigger_link | https:www.acme.corp/billing/viewbill | None | None | 2020-04-24 00:00:00 | 2020-04-24 00:00:00 | None | ||||
| raw_current_page_url | The raw url link that triggered the enter chat event. Only populates for WEB platform. aliases: raw_trigger_link | None | None | 2020-04-25 00:00:00 | 2020-04-25 00:00:00 | None | ||||||
| language_code | VARCHAR(32) | Language code for the issue_id | English | None | None | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | None |
Table: convos_metadata_ended
The convos_metadata table contains data associated with a conversation/issue during a specific 15 minute window. Expect to see columns containing the app_version, the conversation_end timestamp and whether it was escalated to chat or not. This table will filter out data from unended conversations. This export removes any unended issues and any issues which contained no chat activity.
Sync Time: 1h
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| first_utterance_ts | timestamp | The date of the first utterance from a customer. | 2019-09-22T13:12:26.073000+00:00 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| first_utterance_text | varchar(65535) | The text of the first utterance from a customer. | ”Hello, please assist me” | None | None | 2019-01-11 00:00:00 | 2022-06-08 00:00:00 | None | ||||
| issue_created_ts | timestamp | The timestamp of the “NEW_ISSUE” event for an issue. | 2019-11-21T19:11:01.748000+00:00 | None | None | 2019-10-15 13:12:26.073000+00:00 | 2019-10-15 13:12:26.073000+00:00 | None | ||||
| last_event_ts | timestamp | The timestamp of the last event for an issue. | 2019-09-23T14:00:09.043000+00:00 | None | None | 2019-09-16 00:00:00 | 2019-09-16 00:00:00 | None | ||||
| last_srs_event_ts | timestamp without time zone | The date of the last bot assisted event. | 2019-09-22T13:12:26.131000+00:00 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| conversation_end_ts | timestamp | The date of the issue end. | 2019-10-08T14:00:07.395000+00:00 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| session_id | varchar(128) | The ASAPP session identifier. It is a uuid generated by the chat backend. Note: a session may contain several conversations. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| session_type | character varying(255) | A string indicating the ASAPP session type. | asapp-uuid | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| session_event_type | character varying(255) | The action taken upon the session object. | CREATE, UPDATE, DELETE | None | None | 2018-11-26 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| internal_session_id | character varying(255) | The ASAPP session id. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| internal_session_type | character varying(255) | An ASAPP session identifier type. | asapp-uuid | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| internal_user_identifier | varchar(255) | The ASAPP customer identifier while using the asapp system. This identifier may represent either a rep or a customer. Use the the internal_user_type field to determine which type the identifier represents. | 123004 | None | None | 2018-11-26 00:00:00 | 2018-12-06 00:00:00 | None | ||||
| internal_user_session_type | varchar(255) | The customer ASAPP session type. | customer | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| external_session_id | character varying(255) | Client passed argument to SDK at open chat to indicate the client session identifier. | 062906ff-3821-4b5d-9443-ed4fecbda129 | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| external_session_type | character varying(255) | Client passed argument to SDK at open chat to indicate the type of session. | visitID | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| external_user_id | varchar(255) | The customer identifier according to client. This will only exist if the customer has authenticated. | MjU0ZTRiMDQyNDVlNTcyNWNlOTljNmI1NDc2NWQzNzdmNmJmZTFjZDgyY2IwMzc3MDkwZDI5YmQwZDlkODJhNA== | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| external_user_type | varchar(255) | The type of external user id. | acme_CUSTOMER_ACCOUNT_ID | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | ||||
| external_issue_id | character varying(255) | A client passed argument to SDK. This exists only if passed in. | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | |||||
| external_channel | character varying(255) | A client passed argument to SDK. This exists only if passed in. | None | None | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | None | |||||
| customer_id | bigint | An ASAPP customer identifier. | 1470001 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| escalated_to_chat | bigint | 1 if an issue escalated to live chat, 0 if not | 1 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| platform | varchar(255) | The consumer platform in use. | ios, android, web, voice | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| device_type | varchar(255) | The last device type used by the customer for an issue. | mobile, tablet, desktop, watch, unknown | None | None | 2019-06-17 00:00:00 | 2019-06-17 00:00:00 | None | ||||
| first_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| last_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| external_agent_id | varchar(255) | deprecated: 2019-09-25 | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| assigned_to_rep_time | timestamp | Timestamp when the issue was first assigned to an agent (if any). | 2018-09-05 19:58:06T16:14:57.289000+00:00 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| disposition_event_type | varchar(255) | A field which captures how the conversation ended. | resolved, unresolved, timeout | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| disposition_ts | timestamp | The timestamp at which a rep exits the issue. | 2018-09-05 19:58:06T16:14:57.289000+00:00 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| termination_event_type | varchar(255) | This field captures if who or what ended the conversation. | customer, agent, autoend | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| disposition_notes | text | Notes which the last rep took after dispositioning the chat. | ”The customer wanted to pay his bill. We successfully processed his payment.” | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| ended_resolved | integer | A binary value indicating if the rep marked the conversation resolved. | 1, 0 | None | None | 2019-04-30 00:00:00 | 2019-05-01 00:00:00 | None | ||||
| ended_unresolved | integer | A binary value of 1 indicates if the rep marked the conversation unresolved. | 0, 1 | None | None | 2019-04-30 00:00:00 | 2019-05-01 00:00:00 | None | ||||
| ended_timeout | integer | A binary value of 1 indicates if the customer timed out or abandoned the chat. | 0, 1 | None | None | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | None | ||||
| ended_auto | integer | A binary value of 1 indicates if the rep did NOT disposition the issue and it was auto-ended. | 0, 1 | None | None | 2019-04-30 00:00:00 | 2019-05-01 00:00:00 | None | ||||
| ended_other | integer | A binary value of 1 indicates if the customer or rep terminated the issue but the rep didn’t disposition the issue. | 0, 1 | None | None | 2019-04-30 00:00:00 | 2019-05-01 00:00:00 | None | ||||
| app_version_client | varchar(255) | The ASAPP SDK version utilized during a customer event or request. | web-sdk-4.0.2 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| app_version_asapp | varchar(255) | The version of the ASAPP API application utilized during a customer event or request. | com.asapp.api_api:-b393f2d920bb74ce5bbc4174ac5748acff6e8643 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| session_metadata | character varying(65535) | This field contains client passed in extra metadata information about the session. | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | |||||
| last_sequence_id | integer | The last sequence identifier of the issue. | 25 | None | None | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| issue_queue_id | varchar(255) | The queue identifier of the associated issue. | 2001 | None | None | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| issue_queue_name | varchar(255) | The queue name of the associated issue. | acme-mobile-english | None | None | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| csat_rating | double precision | The customer satisfaction rating of the issue. | 400.0 | None | None | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| sentiment_valence | character varying(50) | The sentiment of the issue. | Neutral, Negative | None | None | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | None | ||||
| deep_link_queue | character varying(65535) | The deeplink queued for the issue. | None | None | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | None | |||||
| end_srs_selection | character varying(65535) | The user selected button upon end_srs. | None | None | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | None | |||||
| trigger_link | VARCHAR | deprecated: 2020-04-25 aliases: current_page_url | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | |||||
| auth_state | varchar(3) | A binary value of 1 indicates if the user was authenticated. | 0, 1 | None | None | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | None | ||||
| auth_external_token_id | character varying(65535) | A client provided field. Encrypted user ID from client system associated with the first authentication event for an issue. | 82EFDDADC5466501443E3E61ED640162 | None | None | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| auth_source | character varying(65535) | The source of the first authentication event for an issue. | ivr-url | None | None | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| auth_external_user_type | character varying(65535) | An external user type of the first authentication event for an issue. | ACME_CUSTOMER_ACCOUNT_ID | None | None | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| auth_external_user_id | character varying(65535) | A client provided field. External user identifier associated with the first authentication event for an issue. | 9BE62CCD564D6982FF305DEBCEAABBB5 | None | None | 2019-05-15 00:00:00 | 2019-07-16 00:00:00 | None | ||||
| is_review_required | boolean | A flag indicating if a conversation review is required. data type: boolean | true, false | None | None | 2019-07-24 00:00:00 | 2019-07-24 00:00:00 | None | ||||
| mid_issue_auth_ts | timestamp without time zone | The time when the user was authenticated, if it occured mid-issue. | 2020-01-18T03:43:41.414000+00:00 | None | None | 2019-07-24 00:00:00 | 2019-07-24 00:00:00 | None | ||||
| first_rep_id | varchar(191) | The first agent/rep involved with this issue. | 60001 | None | None | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | None | ||||
| last_rep_id | varchar(191) | The last agent/rep involved with this issue. | 60001 | None | None | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | None | ||||
| external_rep_id | varchar(255) | The rep/agent identifier from the client perspective. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| first_voice_customer_state | varchar(255) | The first assigned state of the customer. | IDENTIFIED, VERIFIED | None | None | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | None | ||||
| first_voice_customer_state_ts | timestamp | The time when the customer was first assigned a state. | 2020-01-18T03:43:41.414000+00:00 | None | None | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | None | ||||
| first_voice_identified_customer_state_ts | timestamp | The time when the customer was first assigned a IDENTIFIED state. | 2020-01-18T03:43:41.414000+00:00 | None | None | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | None | ||||
| first_voice_verified_customer_state_ts | timestamp | The time when the customer was first assigned a VERIFIED state. | 2020-01-18T03:43:41.414000+00:00 | None | None | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | None | ||||
| merged_ts | timestamp | The time when the relevant issue_id merged into another issue. data type: timestamp | 2020-01-18T03:43:41.414000+00:00 | None | None | 2019-12-28 00:00:00 | 2019-12-28 00:00:00 | None | ||||
| desk_mode_flag | bigint | Bitmap encodes if agent handled voice-issue ASAPP desk, had engagement with ASAPP desk. bitmap: 0: null, 1: ‘VOICE’, 2: ‘DESK’, 4: ‘ENGAGEMENT’, 8: ‘INACTIVITY’ NULL for non voice issues | 0, 1, 2, 5, 7 | None | None | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | None | ||||
| desk_mode_string | varchar(191) | Decodes the desk_mode flag. Current possible values (Null, ‘VOICE’, ‘VOICE_DESK’, ‘VOICE_DESK_ENGAGEMENT’,‘VOICE_INACTIVITY’). NULL for non voice issues. | VOICE_DESK | None | None | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | None | ||||
| current_page_url | varchar(2000) | The url link stripped of parameters that triggered the enter chat event. Only populates for WEB platforms. aliases: trigger_link | https:www.acme.corp/billing/viewbill | None | None | 2020-04-25 00:00:00 | 2020-04-25 00:00:00 | None | ||||
| raw_current_page_url | The raw url link that triggered the enter chat event. Only populates for WEB platform. aliases: raw_trigger_link | None | None | 2020-04-25 00:00:00 | 2020-04-25 00:00:00 | None |
Table: convos_metrics
The convos_metrics table contains counts of various metrics associated with an issue/conversation(e.g. “attempted to chat”, “assisted”). The table contains data associated with an issue during a given 15 minute window. The convos_metrics table will include unended conversation data.
Sync Time: 1h
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| first_utterance_ts | timestamp | The date of the first utterance from a customer. | 2019-05-16T02:47:13+00:00 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-11-06 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| platform | varchar(255) | The platform which was used by the customer for a particular event or request (web, ios, android, applebiz, voice). | web, ios, android, applebiz, voice | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| device_type | varchar(255) | The last device type used by the customer for an issue. | mobile, tablet, desktop, watch, unknown | None | None | 2019-06-18 00:00:00 | 2019-06-18 00:00:00 | None | ||||
| assisted | tinyint(1) | A value of 1 if assigned to a rep AND a rep responded, otherwise the field will be 0. | 0, 1 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| total_handle_time | double | The cumulative time in seconds spent by reps working on an issue. This represents the time between assignment and the disposition event. | 168.093 | None | None | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| total_lead_time | double | The cumulative time in seconds spent by the customer interacting in the conversation. The time between assignment and the time of last utterance by the customer. If no utterance by customer, then the lead time is total_handle_time. | 163.222 | None | None | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| total_wrap_up_time | double | The cumulative time in seconds spent by reps wrapping up the conversation. Defined as total_handle_time minus total_lead_time. Also known as dispositioning time. | 4.871 | None | None | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| total_session_time | double | The cumulative time the CUSTOMER spent seeking resolution from reps, including the time spent in queue, plus the time between assignment and the FIRST conversation end event (whether by customer or by rep). | 190.87900018692017 | None | None | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| customer_sent_msgs | double | The number of messages the customer sent (typed and tapped). | 1, 3, 5 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| agent_sent_msgs | deprecated: 2019-09-25 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||||
| auto_generated_msgs | bigint(20) | The number of messages sent by the AI system. | 0,2 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| first_rep_response_count | bigint(20) | The number of first responses by reps, post-assignment. The count of first response by rep post-assignment. This field will increment if there are transfers and timeouts and then reassigned and a rep answers. This field will NOT increment if a rep is assigned but doesn’t get a chance to answer. | 0, 1 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| total_seconds_to_first_rep_response | bigint(20) | The total amount of time in seconds the rep spent responding to the customer. | 407.5679998397827 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| agent_response_count | deprecated: 2019-09-25 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||||
| customer_response_count | bigint(20) | The count of responses (not messages) sent by customers. | 0, 4 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| total_rep_seconds_to_respond | double | The total amount of time in seconds the reps spent responding to the customer. | 407.5679998397827 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| total_cust_seconds_to_respond | double | The total amount of time in seconds the customer spent responding to the rep. | 65.87400007247925 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| time_in_queue | double | The cumulative time in seconds spent in queue, including all re-queues. | 78.30999994277954 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| auto_suggest_msgs | bigint(20) | The number of autosuggest messages sent by a rep. | 0, 1, 3 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| auto_complete_msgs | bigint(20) | The number of autocomplete messages sent by a rep. | 0, 1, 3 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| auto_wait_for_agent_msgs | bigint | deprecated: 2019-09-25 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | |||||
| customer_wait_for_agent_msgs | bigint | deprecated: 2019-09-25 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | |||||
| attempted_chat | tinyint(1) | TinyInt value indicates if there was an attempt to connect the customer to an rep. A value of 1 if the customer receives an out of business hours message or if a customer was asked to wait for a rep. Also a value of 1 if customer was escalated to chat. deprecation-date: 2020-04-14 expected-eol-date: 2021-10-15 | 0, 1 | None | None | 2018-11-06 00:00:00 | 2019-07-26 00:00:00 | None | ||||
| out_business_ct | bigint | The number of times that a customer received an out of business hours message. | 0, 2 | None | None | 2018-11-06 00:00:00 | 2019-04-23 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_sent_msgs | bigint(20) | The number of messages a rep sent. | 0, 6, 7 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_response_count | bigint(20) | The count of responses (not messages) sent by the reps. (Note: A FAQ or send-to-flow should count as a response, since from the perspective of the customer they are getting a response of some kind.) | 0, 5, 6 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| auto_wait_for_rep_msgs | bigint(20) | The number of times a user was asked to wait for a rep. | 0, 1, 2 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| customer_wait_for_rep_msgs | bigint(20) | The number of times a user asked to speak with a rep. | 0, 1 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| hold_ct | bigint | The number of times the customer was placed on hold. This applies to VOICE only. | 0, 1, 2 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| total_hold_time_seconds | float | The total amount of time in seconds that the customer was placed on hold. This applies to VOICE only. | 180.4639995098114 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: convos_metrics_ended
The convos_metrics table contains counts of various metrics associated with an issue/conversation(e.g. “attempted to chat”, “assisted”). The table contains data associated with an issue during a given 15 minute window. This table will filter out unended conversations and issues with no activity.
Sync Time: 1h
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| first_utterance_ts | timestamp | The date of the first utterance from a customer. | 2018-09-05 19:58:06 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| platform | varchar(255) | The platform which was used by the customer for a particular event or request (web, ios, android, applebiz, voice). | web, ios, android, applebiz, voice | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| device_type | varchar(255) | The last device type used by the customer for an issue. | mobile, tablet, desktop, watch, unknown | None | None | 2019-06-18 00:00:00 | 2019-06-18 00:00:00 | None | ||||
| assisted | tinyint(1) | A binary value of 1 indicates whether this chat was assigned to a rep AND a rep responded. | 0,1 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| total_handle_time | double | The cumulative time in seconds spent by reps working on an issue. The time between an assignment and disposition event. | 718.968 | None | None | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| total_lead_time | double | The cumulative time in seconds spent by reps leading the conversation. The time between an assignment and the time of last utterance by THE CUSTOMER. If no utterance by the customer, then the lead time is the total_handle_time. | 715.627 | None | None | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| total_wrap_up_time | double | The cumulative time in seconds spent by reps wrapping the conversation. The wrap time is defined as the total_handle_time-total_lead_time. | 27.583 | None | None | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| total_session_time | double | The cumulative time the CUSTOMER spent seeking resolution from the reps, including time spent in queue, plus the time between the assignment and the FIRST conversation end event (whether by customer or by rep). | 1441.0329999923706 | None | None | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| customer_sent_msgs | double | The number of messages the customer sent (typed and tapped). | 2, 1 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| agent_sent_msgs | deprecated: 2019-09-25 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||||
| auto_generated_msgs | bigint(20) | The number of messages sent by SRS. | 5, 3 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| first_rep_response_count | bigint(20) | The number of first responses by the reps, post-assignment. The count of first response by rep, post-assignment. Ths field will increment if there are transfers and timeouts and then reassigned and a rep answers. This field will not increment if a rep is assigned but doesn’t get chance to answer. | 0, 1 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| total_seconds_to_first_rep_response | bigint(20) | The total amount of time in seconds the rep spent responding to the customer | 4.291000127792358 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| agent_response_count | deprecated: 2019-09-25 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||||
| customer_response_count | bigint(20) | The count of responses (not messages) sent by customers. | 3, 0, 8 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| total_rep_seconds_to_respond | double | The ttotal amount of time in seconds the reps spent responding to the customer. | 240.28499960899353 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| total_cust_seconds_to_respond | double | The total amount of time in seconds the customer spent responding to the rep. | 227.27100014686584 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| time_in_queue | double | The cumulative time in seconds spent in queue, including all re-queues. | 71.74499988555908 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| auto_suggest_msgs | bigint(20) | The number of autosuggest messages sent by rep. | 0, 3, 4 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| auto_complete_msgs | bigint(20) | The number of autocomplete messages sent by rep. | 0, 1, 2 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| auto_wait_for_agent_msgs | bigint | deprecated: 2019-09-25 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | |||||
| customer_wait_for_agent_msgs | bigint | deprecated: 2019-09-25 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | |||||
| attempted_chat | tinyint(1) | A binary value of 1 indicates if there was an attempt to connect the customer to a rep. Also if a customer receives an out of business hours message or if customer was asked to wait for a rep or was escalated to chat. deprecation-date: 2020-04-14 expected-eol-date: 2021-10-15 | 0, 1 | None | None | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| out_business_ct | bigint | The number of times that a customer received an out of business hours message. | 0, 1 | None | None | 2018-11-06 00:00:00 | 2019-04-23 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_sent_msgs | bigint(20) | The number of messages a rep sent. | 0, 4, 7 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_response_count | bigint(20) | The count of responses (not messages) sent by reps. Note: A FAQ or send-to-flow should count as a response, since from the perspective of the customer they are getting a response of some kind. | 0, 1, 20 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| auto_wait_for_rep_msgs | bigint(20) | The number of times a user was asked to wait for a rep. | 0, 3, 4 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| customer_wait_for_rep_msgs | bigint(20) | The number of times a user asked to speak with a rep. | 0, 1, 2 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| hold_ct | bigint | The number of times the customer was placed on hold. This field applies to VOICE. | 0, 1, 2 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| total_hold_time_seconds | float | The total amount of time in seconds that the customer was placed on hold. This field applies to VOICE. | 53.472 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2019-11-01 00:00:00 | None |
Table: convos_summary_tags
The conovos_summary_tags table contains information regarding all AI generated auto-summary tags populated by the system when a rep opens the “end chat” modal.
Sync Time: 1h
Unique Condition: company_id, issue_id, summary_tag_presented
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| queue_id | integer | The identifier of the group to which the rep (who dispositioned the issue) belongs. | 20001 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| queue_name | character varying(128) | The name of the group to which the rep (who dispositioned the issue) belongs. | acme-mobile-english | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| disposition_ts | timestamp | The time at which the rep dispositioned this issue (Exits the screen/frees up a slot). | 2020-01-18T00:21:41.423000+00:00 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| summary_tag_presented | character varying(65535) | The name of the auto-summary tag populated by the system when a rep ends an issue. The value is an empty string if no tag was populated but the rep. | ’(customer)-(cancel)-(phone)’, ‘(rep)-(add)-(account)‘ | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| summary_tag_selected_bool | boolean | Boolean field returns true if a rep selects the summary_tag_presented. | false, true | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| disposition_notes | text | Notes that the rep took when dispositioning the chat. Can be generated from free text or the chat summary tags. | ‘no response from customer’, ‘edu cust on activation handling porting requests’ | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None |
Table: csid_containment
This table tracks conversations by placing them into a customer session. A customer session may be related to more than one issue_id. This table provides consolidated data for a session based upon a 30 minute window.
Sync Time: 1h
Unique Condition: csid, company_name
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| customer_id | bigint | The customer identifier on which this session is based, after merge if applicable. | 123008 | None | None | 2018-11-06 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| external_customer_id | varchar(255) | The customer identfier in the partner system. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| csid | varchar(255) | The unique identifier of a period of continuous activity for the given customer ID starting at the given timestamp. | ‘24790001_2018-09-24T22:17:41.341’ | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| csid_start_ts | timestamp without time zone | The start time of this period of activity. | 2019-12-23T16:00:10.072000+00:00 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| csid_end_ts | timestamp without time zone | The time of most recent event in this activity session, or final event if concluded. | 2019-12-23T16:00:10.072000+00:00 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| agents_involved | deprecated: 2019-09-25 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||||
| included_issues | character varying(65535) | The issues involved in this period of customer activity. Issue identifiers will be pipe-delimited. | ‘2044970001|2045000001|2045010001’ | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| is_contained | boolean | A boolean value indicating whether reps were involved with any of the issues during this csid. | true, false | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| event_count | bigint | The number of customer (only) events active during this csid. | 21 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| fgsrs_event_count | bigint | The number of FGSRS events during this csid. | 5 | None | None | 2019-08-30 00:00:00 | 2019-08-30 00:00:00 | None | ||||
| was_enqueued | boolean | A boolean value indicating if there were enqueued events for this session. | true, false | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| rep_msgs | bigint | The number of rep (only) text messages sent during this csid. | 6 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| messages_sent | bigint | The number of customer (only) text messages typed or quick-replies clicked during this csid. | 4 | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| has_customer_utterance | boolean | A boolean value indicating the csid contains customer messages. | true, false | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| attempted_escalate | boolean | A boolean value indicating if the customer or flow tried (or succeeded) to reach a rep. | false, true | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| last_platform | VARCHAR(191) | The last known device or channel on which the customer communicated. | ANDROID, WEB, IOS | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| last_device_type | VARCHAR(191) | The last device type used by the customer | mobile, tablet, desktop, watch, unknown | None | None | 2019-06-18 00:00:00 | 2019-06-18 00:00:00 | None | ||||
| first_auth_source | character varying(65535) | The first source of the authentication event for a csid. | ivr-url | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| last_auth_source | character varying(65535) | The last source of the authentication event for a csid. | ivr-url | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| distinct_auth_source_path | character varying(65535) | A comma-separated list of all distinct source authentication events for a csid. | ivr-url, facebook | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| first_auth_external_user_type | character varying(65535) | The first external user type of the authentication event for a csid. | client_CUSTOMER_ACCOUNT_ID | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| last_auth_external_user_type | character varying(65535) | The last external user type of the authentication event for a csid. | client_CUSTOMER_ACCOUNT_ID | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| first_auth_external_user_id | character varying(65535) | A client provided field. The first external user identifier associated with an authentication event. | 64b0959a65a63dec32e1be04fe755be1 | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| last_auth_external_user_id | character varying(65535) | A client provided field. The last external user id associated with an authentication event. | 64b0959a65a63dec32e1be04fe755be1 | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| first_auth_external_token_id | character varying(65535) | A client provided field. The first encrypted user ID from client system associated with an authentication event. | 82EFDDADC5466501443E3E61ED640162 | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| last_auth_external_token_id | character varying(65535) | A client provided field. The last encrypted user ID from client system associated with an authentication event. | 82EFDDADC5466501443E3E61ED640162 | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| reps_involved | varchar(4096) | The reps that were associated with any involved issues during this activity session. If more than one rep is involved then the list is pipe-delimited. | ‘209000|2020001’ | None | None | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: csid_containment_1d
This table tracks conversations by placing them into a customer session of 24 hours. A customer session may be related to more than one issue_id.
Sync Time: 1h
Unique Condition: csid, company_name
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| customer_id | bigint | The customer identifier on which this session is based, after merge if applicable. | 123008 | None | None | 2018-01-15 00:00:00 | 2018-11-07 00:00:00 | None | ||||
| external_customer_id | varchar(255) | The customer identfier in the partner system. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| csid | varchar(255) | The unique identifier of a period of continuous activity for the given customer ID starting at the given timestamp. | ‘24790001_2018-09-24T22:17:41.341’ | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| csid_start_ts | timestamp without time zone | The start time of this period of activity. | 2019-12-23T16:00:10.072000+00:00 | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| csid_end_ts | timestamp without time zone | The time of most recent event in this activity session, or final event if concluded. | 2019-12-23T16:00:10.072000+00:00 | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| agents_involved | deprecated: 2019-09-25 | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||||
| included_issues | character varying(65535) | The issues involved in this period of customer activity. Issue identifiers will be pipe-delimited. | ‘2044970001|2045000001|2045010001’ | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| is_contained | boolean | A boolean value indicating whether reps were involved with any of the issues during this csid. | true, false | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| event_count | bigint | The number of customer (only) events active during this csid. | 21 | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| fgsrs_event_count | bigint | The number of FGSRS events during this csid. | 5 | None | None | 2019-08-30 00:00:00 | 2019-08-30 00:00:00 | None | ||||
| was_enqueued | boolean | A boolean value indicating if there were enqueued events for this session. | true, false | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| rep_msgs | bigint | The number of rep (only) text messages sent during this csid. | 6 | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| messages_sent | bigint | The number of customer (only) text messages typed or quick-replies clicked during this csid. | 4 | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| has_customer_utterance | boolean | A boolean value indicating the csid contains customer messages. | true, false | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| attempted_escalate | boolean | A boolean value indicating if the customer or flow tried (or succeeded) to reach a rep. | false, true | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| last_platform | VARCHAR(191) | The last known device or channel on which the customer communicated. | ANDROID, WEB, IOS | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| last_device_type | VARCHAR(191) | The last device type used by the customer | mobile, tablet, desktop, watch, unknown | None | None | 2019-06-18 00:00:00 | 2019-06-18 00:00:00 | None | ||||
| first_auth_source | character varying(65535) | The first source of the authentication event for a csid. | ivr-url | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| last_auth_source | character varying(65535) | The last source of the authentication event for a csid. | ivr-url | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| distinct_auth_source_path | character varying(65535) | A comma-separated list of all distinct source authentication events for a csid. | ivr-url, facebook | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| first_auth_external_user_type | character varying(65535) | The first external user type of the authentication event for a csid. | client_CUSTOMER_ACCOUNT_ID | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| last_auth_external_user_type | character varying(65535) | The last external user type of the authentication event for a csid. | client_CUSTOMER_ACCOUNT_ID | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| first_auth_external_user_id | character varying(65535) | A client provided field. The first external user identifier associated with an authentication event. | 64b0959a65a63dec32e1be04fe755be1 | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| last_auth_external_user_id | character varying(65535) | A client provided field. The last external user id associated with an authentication event. | 64b0959a65a63dec32e1be04fe755be1 | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| first_auth_external_token_id | character varying(65535) | A client provided field. The first encrypted user ID from client system associated with an authentication event. | 82EFDDADC5466501443E3E61ED640162 | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| last_auth_external_token_id | character varying(65535) | A client provided field. The last encrypted user ID from client system associated with an authentication event. | 82EFDDADC5466501443E3E61ED640162 | None | None | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | None | ||||
| reps_involved | varchar(4096) | The reps that were associated with any involved issues during this activity session. If more than one rep is involved then the list is pipe-delimited. | ‘209000|2020001’ | None | None | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: customer_feedback
The customer_feedback table contains the feedback regarding how well their issue was resolved. This table contains columns such as the feedback question prompted at issue completion, the customer response and the last rep identifier which was associated with an issue_id.
Sync Time: 1d
Unique Condition: issue_id, company_marker, last_rep_id, question, instance_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| last_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| question | character varying(65535) | The question asked of the user. | VOC Score, endSRS rating, What did the agent do well, or what could the agent have done better? (1000 character limit) | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| question_category | character varying(65535) | The question category type. | rating, comment, levelOfEffort | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| question_type | character varying(65535) | The type of question. | rating, scale, radio | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| answer | character varying(65535) | The customer’s answer to the question. | 0, 1, 17, yes | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| ordering | integer | The question ordering. | 0, 1, 3, 5 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| last_rep_id | varchar(191) | The last ASAPP rep/agent identifier found in a window of time. | 123008 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | None | ||||
| platform | varchar(255) | The platform which was used by the customer for a particular event or request (web, ios, android, applebiz, voice). | web, ios, android, applebiz, voice | None | None | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | None | ||||
| feedback_type | character varying(65535) | The type of the feedback the customer provided. | FEEDBACK_AGENT, etc | None | None | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | None | ||||
| feedback_form_type | character varying(65535) | Describes what kind of feedback form the customer filled out | ASAPP_CSAT, GBM | None | None | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | None |
Table: customer_params
The customer_params table contains information which the client sends to ASAPP. The table may have multiple rows associated with one issue_id. Clients specify the information to store using a JSON entry which may contain multiple semicolon separated (key, value) pairs.
Sync Time: 1d
Unique Condition: event_id, param_key
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| event_ts | timestamp | The time at which this event was fired. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| company_subdivision | varchar(255) | The subdivision of the company. | ACMEsubcorp | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| company_segments | varchar(255) | The segments of the company. | marketing,promotions | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| rep_id | varchar(191) | deprecated: 2022-06-30 | 123008 | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| referring_page_url | character varying(65535) | The URL from which the user came. | https://www.acme.com/wireless | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| event_id | character varying(256) | The unique identifier for the customer param payload event. | None | None | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | None | |||||
| platform | varchar(255) | The platform the customer is using to interact with ASAPP. | 08679ded-38b7-11ea-9c44-debfe2011fef | None | None | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | None | ||||
| session_id | varchar(128) | The websocket UUID associated with the current request’s session. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | None | ||||
| auth_state | boolean | Boolean value indicating if the customer is authenticated with the client. | true, false | None | None | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | None | ||||
| params | character varying(65535) | A string representation of the JSON parameters. | {\"Key1\":\"Value1\"; \"Key2\":\"Value2\"} | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| param_key | character varying(255) | A value of a specific key within the parameter JSON. | Key1 | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| param_value | character varying(65535) | The value corresponding with the specific key in param_key. | Value1 | None | None | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| current_page_url | varchar(2000) | The url page where the customer entered ASAPP chat. | https://www.asapp.com | None | None | 2021-09-16 00:00:00 | 2021-09-16 00:00:00 | None |
Table: dim_queues
The dim_queues table creates a mapping of queue_id to queue_name. This is an hourly snapshot of information.
Sync Time: 1h
Unique Condition: queue_key
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2022-01-27 00:00:00 | 2022-01-27 00:00:00 | None | ||||
| queue_key | bigint | Numeric primary key for dim queues | 100001 | None | None | 2022-01-27 00:00:00 | 2022-01-27 00:00:00 | None | ||||
| queue_id | integer | The ASAPP queue identifier which the issue was placed. | 210001 | None | None | 2022-01-27 00:00:00 | 2022-01-27 00:00:00 | None | ||||
| queue_name | character varying(191) | None | None | 2022-01-27 00:00:00 | 2022-01-27 00:00:00 | None |
Table: flow_completions
The purpose of this table is to list out the flow success information, any negation data, and other associated metadata information for all issues. From this table, one can understand the success or failure result for any issue.
Sync Time: 1h
Unique Condition: company_id, issue_id, flow_name, flow_status_ts, success_event_details
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-11-14 00:00:00 | 2019-09-12 00:00:00 | None | no | |||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| platform | varchar(255) | The customer’s platform. | web, ios, android, applebiz, voice | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP identifier for the customer. allowed_aggs: ‘count’ groupby: False | 123008 | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| external_user_id | varchar(255) | The partner’s user identifier for this customer. allowed_aggs: ‘count’ groupby: False | 64b0959a65a63dec32e1be04fe755be1 | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| customer_session_id | character varying(65535) | The ASAPP application session identifier for this customer. allowed_aggs: ‘count’ groupby: False | c5d7afcc-89b9-43cc-90e2-b869bb2be883 | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| success_rule_id | character varying(256) | The tag denoting whether the flow was successful within this issue. | LINK_RESOLVED, TOOLING_SUCCESS | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| success_event_details | character varying(65535) | Any additional metadata about this success rule. | asapp-pil://acme/grande-shop, EndSRSPositiveMessage | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| success_event_ts | timestamp without time zone | The time at which the flow success occurred. | 2019-12-03T01:43:17.079000+00:00 | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| negation_rule_id | character varying(256) | The tag denoting the last negation event that reverted a previous success. | TOOLING_NEGATION, NEG_QUESTION_NOT_ANSWERED | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| negation_event_ts | timestamp without time zone | The time at which this negation occurred. | 2019-12-03T01:49:19.875000+00:00 | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| is_flow_success_event | boolean | True if this event was not negated directly, false otherwise. | true, false | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| is_flow_success_issue | boolean | True if a success event occurred within this issue and no negation event occurred within this issue, false otherwise. | true, false | None | None | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2019-11-01 00:00:00 | None | ||||
| last_relevant_event_ts | A timestamp representing the last time a relevant event (either success or negation) was detected for this issue. This field can be used to deduplicate multiple records per issue_id. | 2020-01-02T19:13:27.698000+00:00 | None | None | 2019-12-10 00:00:00 | 2019-12-10 00:00:00 | None |
Table: flow_detail
The purpose of the flow_detail table is to list out the data associated with each node traversed during an issue lifespan. A usage of this table is to understand the path a particular issue traversed.
Sync Time: 1h
Unique Condition: event_ts, issue_id, event_type
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| event_ts | timestamp | The time of an given event. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| event_type | varchar(191) | The type of event within a given flow. | MESSAGE_DISPLAYED | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | no | |||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-08-14 00:00:00 | 2018-08-27 00:00:00 | None | no | |||
| session_id | varchar(128) | The ASAPP session identifier. It is a uuid generated by the chat backend. Note: a session may contain several conversations. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| flow_id | varchar(255) | An ASAPP identifier assigned to a particular flow executed during a customer event or request. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| flow_name | varchar(255) | The ASAPP text name for a given flow which was executed during a customer event or request. | FirstChatMessage, AccountNumberFlow | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| event_name | character varying(65535) | The event name within a given flow. | FirstChatMessage, SuccessfulPaymentNode | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | no | |||
| link_resolved_pil | character varying(65535) | An asapp internal URI for the link. | asapp-pil://acme/bill-history | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | no | |||
| link_resolved_pdl | character varying(65535) | The resolved host deep link or web link. | https://www.acme.com/BillHistory | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | no | |||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: intents
The intents table contains a list of intent codes and other information associated with the intent codes. Information in the table includes flow_name and short_description.
Sync Time: 1d
Unique Condition: code
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| code | character varying(128) | The ASAPP internal code for a given intent. | ACCTNUM | None | None | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | None | no | |||
| name | character varying(256) | The human friendly name for an intent. | Get account number | None | None | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | None | no | |||
| intent_type | character varying(128) | The hierarchy placement for this intent. | SYSTEM, LEAF, PARENT | None | None | 2018-07-26 00:00:00 | 2021-11-24 00:00:00 | None | no | |||
| short_description | character varying(1024) | A short description for the intent code. | ‘Users asking to get their account number.’, ‘Television error codes.‘ | None | None | 2018-07-26 00:00:00 | 2019-02-12 00:00:00 | None | no | |||
| flow_name | varchar(255) | The ASAPP flow code attached to this intent code. | AccountNumberFlow | None | None | 2018-12-13 00:00:00 | 2018-12-13 00:00:00 | None | no | |||
| default_disambiguation | boolean | True if the intents are part of the first “welcome” screen of disambiguation buttons presented to a customer, false otherwise. | false, true | None | None | 2018-12-13 00:00:00 | 2018-12-13 00:00:00 | None | no | |||
| actions | character varying(4096) | This field describes what action this represents for the customer interface and app. This can be “flow” for a complex form, “link” for a simply URL, “text” for some help text. A blank action implies no action or automation. | flow, link, test, NULL | None | None | 2018-12-20 00:00:00 | 2018-12-20 00:00:00 | None | no | |||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2021-04-09 00:00:00 | None | ||||
| deleted_ts | date this intent was removed from the system, if this is blank or null, the intent is still active as of this export. An intent can be “undeleted” at a later date. | NULL, 2018-12-13 01:23:34 | None | None | 2021-11-23 00:00:00 | 2021-11-23 00:00:00 | None | no |
Table: issue_callback_3d
The issue_callback table relates issues from the same customer during a three day window. This table will help measure customer callback rate. The issue_callback table is applicable only to specific clients.
Sync Time: 1h
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | ||||
| issue_created_ts | timestamp | The timestamp of when the issue ID was created. | 2018-09-05 19:58:06 | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | ||||
| issue_disconnect_ts | timestamp without time zone | The issue disconnect timestamp is the time this issue ID was disconnected. | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | |||||
| issue_cutoff_ts | timestamp without time zone | The timestamp of when the call back period expires for an issue. Configured to timeout(3 days) + issue_disconnect_ts. | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | |||||
| next_callback_issue_id | bigint | Identifier of the next issue that the same identified customer made. Next_callback_issuse must be made between issue_disconnect_ts and issue_cutoff_ts. Null if no such issue exists. | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | |||||
| next_callback_issue_created_ts | timestamp without time zone | Timestamp of when the next_callback_issue was created. | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | |||||
| time_btwn_next_callback_issue_secs | The time in seconds between issue_disconnect_ts and next_callback_issue_created_ts. | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | ||||||
| callback_prev_issue_id | bigint | The issue ID of any previous issue that the same identified customer made. Callback_prev_issue must be disconnected within a 3 day period from issue_id create_ts. This field is NULL if no such issue exists. | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | |||||
| callback_prev_issue_created_ts | timestamp without time zone | The timestamp of when a callback_prev_issue was created. | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | |||||
| callback_prev_issue_disconnect_ts | timestamp without time zone | The timestamp of when a callback_prev_issue was disconnected. | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | |||||
| time_btwn_callback_prev_issue_secs | The time in seconds between callback_prev_issue_disconnect_ts and issue_created_ts. | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | None |
Table: issue_entity_genagent
hourly snapshot of issue grain generative_agent data including both dimensions and metrics aggregated over “all time” (two days in practice).
Sync Time: 1h
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| generative_agent_turns__turn_ct | int | Number of turns. | 1 | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| generative_agent_turns__turn_duration_ms_sum | bigint | Total number of milliseconds between PROCESSING_START and PROCESSING_END across all turns. | 2 | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| generative_agent_turns__utterance_ct | int | Number of generative_agent utterances. | 2 | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| generative_agent_turns__contains_escalation | boolean | Boolean indicating the presence of a turn in the conversation that ended with an indication to escalate to a human agent. | 1 | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| generative_agent_tasks__first_task_name | varchar(255) | Name of the first task entered by generative_agent. | SomethingElse | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| generative_agent_tasks__last_task_name | varchar(255) | Name of the last task entered by generative_agent. | SomethingElse | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| generative_agent_tasks__task_ct | int | Number of tasks entered by generative_agent. | 2 | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None | ||||
| generative_agent_tasks__configuration_id | varchar(255) | The configuration version that produced generative_agent actions. | 4ea5b399-f969-49c6-8318-e2c39a98e817 | None | None | 2019-09-22 00:00:00 | 2019-09-22 00:00:00 | None |
Table: issue_entry
This table shows data about how a user began an interaction with the sdk by issue
Sync Time: 1h
Unique Condition: company_marker, issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | ||||
| issue_created_ts | timestamp | timestamp of the “NEW_ISSUE” event for an issue | 2018-09-05 19:58:06 | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | ||||
| company_id | bigint | The ASAPP identifier of the company or test data source. | 10001 | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | ||||
| entry_type | character varying(384) | initiation source of the first activity for the Issue ID was from a proactive invitation, reactive button click, deep-link ask-secondary-question, etc. examples: PROACTIVE,REACTIVE,ASK,DEEPLINK | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | |||||
| treatment_type | varchar(64) | Classification if proactive messaging handling is configured to route customer to automated flow or live agent | QUEUE_PAUSED | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | ||||
| rule_name | character varying(65535) | name of the set of logic that the customer met in order to qualify for presentation of a proactive message invitation or reactive button display | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | |||||
| is_new_conversation | boolean | if the issue is created when the customer was not already in the middle of an active issue. | None | None | 2019-11-15 00:00:00 | 2019-11-15 00:00:00 | None | |||||
| is_new_user | boolean | if this is the first issue for the customer | None | None | 2019-11-15 00:00:00 | 2019-11-15 00:00:00 | None | |||||
| current_page_url | varchar(2000) | the page that the sdk was shown on | https://www.asapp.com | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | ||||
| referring_page_url | character varying(65535) | the page that linked into the current page | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None | |||||
| client_uuid | character varying(36) | The UUID generated (that only ever lasts fifteen minutes or so) on each fresh sdk cache that can identify a unique human. For internal debbuging, it won’t go to sync (exactly as it comes from the source without any transformation) | c3944019-24d3-4887-8794-045cd61d5a22 | None | None | 2024-07-01 00:00:00 | 2021-06-01 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | None |
Table: issue_omnichannel
This table captures omnichannel tracking events related with the different platforms we have. (Initially only ABC)
Sync Time: 1h
Unique Condition: company_id, issue_id, third_party_customer_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2020-06-02 00:00:00 | 2020-06-02 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2020-06-02 00:00:00 | 2020-06-02 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2020-06-02 00:00:00 | 2020-06-02 00:00:00 | None | ||||
| omni_source | character varying(191) | The source of the information. | ‘ABC’ | None | None | 2020-06-03 00:00:00 | 2020-06-03 00:00:00 | None | ||||
| opaque_id | varchar(191) | deprecated: 2020-09-11 | ’urn:mbid:XXXXXX’ | None | None | 2020-06-03 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| external_intent | character varying(65535) | The intention or purpose of the chat as specified by the business, such as account_question. deprecated: 2020-09-11 | ’account_question’ | None | None | 2020-06-03 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| external_group | character varying(65535) | Group identifier for the message, as specified by the business, such as department name. deprecated: 2020-09-11 | ’credit_card_department’ | None | None | 2020-06-03 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| first_utterance | character varying(191) | Text of the first customer utterance for an issue. | None | None | 2020-06-03 00:00:00 | 2020-06-03 00:00:00 | None | |||||
| event_ts | timestamp | deprecated: 2020-09-11 | 2019-11-08 14:00:06.957000+00:00 | None | None | 2020-06-02 00:00:00 | 2020-06-02 00:00:00 | None | ||||
| third_party_customer_id | character varying(65535) | An encrypted identifier which is permanently mapped to an ASAPP customer. | ‘urn:mbid:XXXXXX’ | None | None | 2020-07-23 00:00:00 | 2020-07-23 00:00:00 | None | ||||
| external_context_1 | character varying(65535) | Traffic source or customer context from platforms external to ASAPP. External Context 1 includes Apple Business Chat Group ID and Google Business Messaging Entry Point. | ‘credit_card_department’ | None | None | 2020-07-23 00:00:00 | 2020-07-23 00:00:00 | None | ||||
| external_context_2 | character varying(65535) | Traffic source or customer context from platforms external to ASAPP. External Context 2 includes Apple Business Chat Intent ID and Google Business Messaging Place ID. | ‘account_question’ | None | None | 2020-07-23 00:00:00 | 2020-07-23 00:00:00 | None | ||||
| created_ts | timestamp | The date and time at which the message was sent. | ‘2019-11-08T14:00:06.95700000:00’ | None | None | 2020-07-23 00:00:00 | 2020-07-23 00:00:00 | None |
Table: issue_queues
The purpose for the issue_queues table is to capture relevant data associated with an issue in a wait queue. Data captured includes the issue_id, the enqueue time, the rep, the event type and flowname. This is captured in 15 minute windows of time.
Sync Time: 1h
Unique Condition: issue_id, queue_id, enter_queue_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| enter_queue_ts | timestamp without time zone | The time an issue entered the queue. | 2019-12-26T18:25:22.836000+00:00 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| exit_queue_ts | timestamp | The time an issue exited the queue. | 2019-12-26T18:25:28.552000+00:00 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| queue_id | integer | The ASAPP queue identifier which the issue was placed. | 20001 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| queue_name | character varying(65535) | The queue name which the issue was placed. | Acme Residential, Acme Wireless | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| abandoned | boolean | A boolean value indicating whether this issue was abandoned. | true, false | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| enqueue_time | double precision | A decimal value representing how long in seconds that the issue was in the queue. | 5.716000080108643 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| exit_queue_eventtype | character varying(65535) | The reason the customer exited the queue. | CUSTOMER_TIMEDOUT, NEW_REP | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| enter_queue_eventtype | character varying(65535) | The reason the customer entered the queue. | TRANSFER_REQUESTED, SRS_HIER_AND_TREEWALK | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| enter_queue_eventflags | bigint | The actor causing the enqueue. | (1=customer, 2=rep, 4=bot) | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| enter_queue_flow_name | character varying(65535) | The name of the flow which the issue was in before being enqueued. | LiveChatAgentsBusyFlow | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| enter_queue_message_name | character varying(65535) | The message name within the flow which the user was in before being enqueued. | someoneWillBeWithYou, shortWaitFormNode | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| exit_queue_eventflags | bigint | The actor causing the dequeue. | (1=customer, 2=rep, 4=bot) | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: issue_sentiment
The purpose of the issue sentiment table is to calculate the ‘sentiment score’ for a specific issue. This table also contains a reason for the value of the score.
Sync Time: 1d
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-07-26 00:00:00 | 2018-09-29 00:00:00 | None | ||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | None | ||||
| score | double precision | The sentiment score applied to this issue. | 0.5545974373817444, -1000.0 | None | None | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | None | ||||
| status | character varying(65535) | The reason for the sentiment score. This field may be NULL. | CONVERSATION_TOO_SHORT | None | None | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: issue_session_merge
A list of the merged issues that have occurred as a result of transferring to a queue during a cold transfer and the first issue_id associated with this new issue_id. Only relevant for VOICE. activate-date: 2024-01-17
Sync Time: 1h
Unique Condition: issue_id, session_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | None | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | None | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | None | ||||
| session_id | varchar(128) | The ASAPP session identifier. It is a uuid generated by the chat backend. Note: a session may contain several conversations. | ‘guid:2348001002-0032128785-2172846080-0001197432’ | None | None | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | None | ||||
| issue_created_ts | timestamp | Timestamp this issue_id was created. | 2018-09-05 19:58:06 | None | None | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | None | ||||
| first_issue_id | bigint | The first issue_id for this session. | 21352352 | None | None | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | None | ||||
| first_issue_created_ts | timestamp | The timestamp of the NEW_ISSUE event for the first issue_id associated with this session. | 2018-09-05 19:58:06 | None | None | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | None | ||||
| last_issue_id | bigint | The last issue_id associated with this session. | 21352352 | None | None | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | None | ||||
| last_issue_created_ts | timestamp | The timestamp of the NEW_ISSUE event for the last issue_id associated with this session. | 2018-09-05 19:58:06 | None | None | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | None |
Table: issue_type
The purpose of the issue_type table is to capture any client specific naming of issue parameters. This captures per issue the initial “issue type name” which the client has specified. This is captured in 15 minute window increments.
Sync Time: 1h
Unique Condition: company_id, customer_id, issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| prechat_survey_ts | timestamp without time zone | The timestamp when the survey was submitted to route the issue to an expert. | 2019-08-07 19:34:18.844 | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| type_change_ts | timestamp without time zone | The timestamp when the issue type was changed (e.g. escalated from question.) Null if the issue type was not changed. | 2019-08-07 19:45:57.325 | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| queue_id | integer | The identifier of the queue where the issue was routed. | 20001 | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| issue_type | character varying(65535) | The current type of the issue (question or escalation). | ESCALATION | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| initial_type | character varying(65535) | The original type of the issue when it was opened. | QUESTION | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| subsidiary_name | character varying(65535) | Name of the company to which this issue is associated. | ACMEsubsid | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| channel_type | character varying(65535) | If the issue was initially an ESCALATION, indicates the channel (voice or chat) or null otherwise. | CALL | None | None | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: knowledge_base
This table captures interactions with articles in the knowledge base. An article can be viewed, attached to a chat and marked as favorite
Sync Time: 1h
Unique Condition: company_id, issue_id, article_id, event_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| article_id | character varying(65535) | The knowledge base identifier for the article. | 5, 16580001 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| interaction | character varying(8) | An indicator of whether the article was viewed or attached to a chat. | ‘Viewed’, ‘Attached’ | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| is_favorite | Boolean value indicating whether the article is marked as a favorite or not. | TRUE, FALSE | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | |||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| event_ts | timestamp | The time of an given event. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| event_type | varchar(191) | Either Interaction events requested: (‘OPEN_ARTICLE’, ‘PAPERCLIP_ARTICLE’) or Recommendation events requested: (‘DISPLAYED’,‘AGENT_HOVERED’, ‘AGENT_CLICKED_EXTERNAL_ARTICLE_LINK’, ‘AGENT_CLICKED_THUMBS_UP’ ‘AGENT_CLICKED_THUMBS_DOWN’, ‘AGENT_CLICKED_EXPAND_CARD’, ‘AGENT_CLICKED_COLLAPSE_CARD’) | CUSTOMER_TIMEOUT, TEXT_MESSAGE | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| event_name | character varying(191) | A string that determines if the action comes from an Interaction event or a Recommendation event | ’INTERACTION’, ‘SUGGESTION’ | None | None | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2020-03-30 00:00:00 | 2020-03-30 00:00:00 | None | ||||
| rep_assigned_ts | timestamp without time zone | timestamp of the NEW_REP event | None | None | 2020-10-15 00:00:00 | 2020-10-15 00:00:00 | None | |||||
| article_category | character varying(191) | Category to distinguish between flows and knowledge base articles. REGULAR is for knowledge base articles. FLOWS is for flows recommendation. | ‘REGULAR’ | None | None | 2020-10-15 00:00:00 | 2020-10-15 00:00:00 | None | ||||
| discovery_type | character varying(256) | How article was presented/discovered. (recommendation, quick_access_kbr, favorite, search, filebrowser) | recommendation | None | None | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | None | ||||
| position | integer | Position of article recommendation when multiple recommendations are presented. Default is 1 when a single recommendation is presented. | 1 | None | None | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | None | ||||
| span_id | varchar(128) | Identifier for a recommendation. Can be used to tie a recommendation to an interaction such as HOVER, OPEN_ARTICLE. | ‘coo9c7b8-7a50-11eb-b13e-8ad0401b5458’ | None | None | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | None | ||||
| article_name | Short description of the article. | 500 | None | None | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | None | |||||
| is_paperclip_enabled | Boolean value indicating whether the article can be paperclipped. | TRUE | None | None | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | None | |||||
| external_article_id | Identifier for external article id. | 4567 | None | None | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | None |
Table: live_agent_opportunities
The live_agent_opportunities table captures metrics regarding the opportunity which was presented to the agent by the conversation. Expect to see data such as the rep_id, the customer_id and various flags such as “by_design”, “by_request”, “first_opportunity” as well as times associated with lead_time and wrapup_time. This data is captured in 15 minute window increments.
Sync Time: 1h
Unique Condition: issue_id, customer_id, opportunity_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| rep_id | varchar(191) | The identifier of the rep this opportunity was assigned to or null if it was never assigned. | 123008 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| opportunity_ts | timestamp | Timestamp of the opportunity event. | 2020-01-06 23:13:50.617 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| platform | varchar(255) | The last platform associated with the issue. | web, ios, android, applebiz, voice | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| device_type | varchar(255) | The last device type used by the customer for an issue. | mobile, tablet, desktop, watch, unknown | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| first_opportunity | boolean | Indicator of whether this is the first opportunity for this issue. | true, false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| triggered_when_busy | boolean | Indicator of whether the customer was asked if they wanted to wait for an agent. | true | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| triggered_outside_hours | boolean | Indicator of whether the customer was told they are outside of business hours. | false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| queue_id | integer | Identifier of the agent group this opportunity will be routed to. | 2001 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| queue_name | character varying(128) | Name of the queue this opportunity will be routed to. | Residential | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| intent_code | character varying(128) | The most recent intent code used for routing this issue. | SALESFAQ, BILLINFO | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| event_type | varchar(191) | The event_type of this opportunity. This can be useful to determine if this is a transfer, etc. | NEW_REP, SRS_HIER_AND_TREEWALK | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| previous_event_type | character varying(65535) | The event_type that occurred prior to this opportunity. This can be useful to determine if the customer was previously transferred or timed out. | SRS_HIER_AND_TREEWALK | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| flow_name | varchar(255) | The flow associated with the routing intent, if any. | ForceChatFlow | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| by_request | boolean | Indicator of whether the customer explicitly request to speak to an agent (i.e. intent code has an AGENT as a parent). | true, false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| by_end_srs | boolean | Indicator of whether this opportunity occurred because of a negative end srs response. | true, false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| by_api_error | boolean | Indicator of whether this opportunity occurred because of an error in partner API. | true, false | None | None | 2019-10-21 00:00:00 | 2019-10-21 00:00:00 | None | ||||
| by_design | boolean | Indicator of whether intent_code is not null AND not by_request AND not by_end_srs AND not by_api_error. Note this includes cases where a flow sends the customer to an agent if it has not successfully solved the problem. (ex: I am still not connected after a reset my router flow.) | true, false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| by_other | boolean | Catch all indicattor for all cases that are not by request, design or end_srs. This generally happens if we are missing the intent code, either because of an API error or because of a data bug. | true, false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| enqueued_ts | timestamp | The time which this opportunity was sent to a queue, or null if it never was enqueued. | 2020-01-06 23:13:50.617 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| exit_queue_ts | timestamp | Time at which the customer exited the queue. | 2020-01-06 23:13:50.617 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| abandoned_ts | TIMESTAMP | The datetime when the customer abandoned the queue. | 2020-01-06 23:13:50.617 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| assigned_ts | timestamp | The time this opportunity was assigned to a rep, null if never assigned. | 2020-01-03T18:54:45.140000+00:00 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| escalation_initiated_ts | timestamp | The lesser of enqueued and assigned time, null if never escalated. | 2020-01-06 23:13:50.617 | None | None | 2019-06-04 00:00:00 | 2019-06-04 00:00:00 | None | ||||
| rep_first_response_ts | TIMESTAMP | The time when a rep first responded to the customer. | 2020-01-06 23:13:50.617 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| dispositioned_ts | timestamp | The time at which the rep dispositioned this issue (Exits the screen/frees up a slot). | 2020-01-06 23:13:50.617 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| customer_end_ts | timestamp without time zone | The time at which customer ended the issue, if the customer ended the issue. | 2020-01-06 23:13:50.617 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| disposition_event_type | varchar(255) | The event or person which ended the issue. | resolved, timedout | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| cust_utterance_count | bigint | Count of customer utterances from issue_assigned_ts to dispositioned_ts | 4 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| rep_utterance_count | bigint | Count of rep utterances from issue_assigned_ts to dispositioned_ts | 5 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| cust_response_ct | int | Total count of responses by customer. Max of one message following a rep message counted as a response. | 3 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| rep_response_ct | int | Total count of responses by agent. Max of one message following a customer message counted as a response. | 10 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| is_ghost_customer | boolean | True if the customer was assigned to a rep but never responded to the rep. | true, false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| handle_time_seconds | double precision | Time in seconds spent an agent working on a particular assignment. Time between assignment and disposition event | 824.211 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| lead_time_seconds | double precision | Time in seconds spent by an agent leading the conversation. Time between assignment and time of last utterance by THE CUSTOMER. If no utterance by customer, Lead time is total_handle_time. | 101.754 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| wrap_up_time_seconds | double precision | Time in seconds spent by an agent wrapping up the conversation. Defined as total_handle_time-total_lead_time. | 61.989 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| accepted_wait_ts | timestamp without time zone | Timestamp at which the customer was sent a message confirming they had been placed into a queue. | 2019-09-11T14:15:59.312000+00:00 | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| is_transfer | boolean | Indicator whether this opportunity is due to a transfer. | true, false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| is_reengagement | boolean | Indicator whether this opportunity is due to the user returning from a timeout. | true, false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| is_conversation_initiation | boolean | Indicator of whether this opportunity is from a conversation initiation (i.e. not from transfer or reengagement). | true, false | None | None | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| from_queue_id | bigint | The identifier of the group from which the issue was transferred. | 30001 | None | None | 2019-12-18 00:00:00 | 2019-12-18 00:00:00 | None | ||||
| from_queue_name | character varying(191) | The name of the group from which the issue was transferred. | service, General | None | None | 2019-12-18 00:00:00 | 2019-12-18 00:00:00 | None | ||||
| from_rep_id | bigint | The identifier of the rep from which the issue was transferred. | 81001 | None | None | 2019-12-18 00:00:00 | 2019-12-18 00:00:00 | None | ||||
| is_check_in_reengagement | boolean | Is this opportunity due to the user coming back within a 24h period after being timed-out for not answering a check-in prompt on time. | true | None | None | 2020-01-14 00:00:00 | 2020-01-14 00:00:00 | None | ||||
| desk_mode_flag | bigint | Bitmap encodes if agent handled voice-issue ASAPP desk, had engagement with ASAPP desk. bitmap: 0: null, 1: ‘VOICE’, 2: ‘DESK’, 4: ‘ENGAGEMENT’, 8: ‘INACTIVITY’ NULL for non voice issues | 0, 1, 2, 5, 7 | None | None | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | None | ||||
| desk_mode_string | varchar(191) | Decodes the desk_mode flag. Current possible values (Null, ‘VOICE’, ‘VOICE_DESK’, ‘VOICE_DESK_ENGAGEMENT’,‘VOICE_INACTIVITY’). NULL for non voice issues. | VOICE_DESK | None | None | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | None | ||||
| merged_from_issue_id | bigint | The issue id before the merge | 21352352 | None | None | 2020-06-30 00:00:00 | 2020-06-30 00:00:00 | None | ||||
| merged_ts | timestamp | the time the merge occurred | 2019-11-08T14:00:06.957000+00:00 | None | None | 2020-06-30 00:00:00 | 2020-06-30 00:00:00 | None | ||||
| exclusive_phrase_auto_complete_msgs | bigint | Count of utterances where at least one phrase autocomplete was accepted/sent and no other augmentation was used. | None | None | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | None | |||||
| autopilot_ending_msgs_ct | integer | Number of autopilot endings | 2 | None | None | 2024-04-19 00:00:00 | 2024-04-19 00:00:00 | None |
Table: queue_check_ins
Exports for each 15 min window of Queue Check in events
Sync Time: 1h
Unique Condition: company_id, issue_id, customer_id, check_in_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | None | ||||
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | None | ||||
| check_in_ts | timestamp without time zone | Timestamp at which the check in message was prompted to the customer. | 2018-06-10 14:23:00 | None | None | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | None | ||||
| wait_time_threshold_ts | timestamp without time zone | Timestamp at which the queue wait time threshold was reached. | 2018-06-10 14:22:58 | None | None | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | None | ||||
| check_in_result | character varying(9) | The result of the check in message, either the customer ‘Accepted’ or was ‘Dequeued’. | ‘Dequeued’ | None | None | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | None | ||||
| check_in_result_ts | timestamp without time zone | Timestamp at which the result of the check in message was received. | 2018-06-10 14:24:00 | None | None | 2020-01-02 00:00:00 | 2020-04-24 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-03-23 00:00:00 | 2019-03-23 00:00:00 | None | ||||
| wait_time_threshold_ct_distinct | bigint | Quantity of times the queue wait time threshold was reached before getting the check in message. | 2 | None | None | 2020-04-25 00:00:00 | 2020-04-25 00:00:00 | None | ||||
| queue_id | integer | The ASAPP queue identifier which the issue was placed. | 20001 | None | None | 2020-06-11 00:00:00 | 2020-06-11 00:00:00 | None | ||||
| queue_name | character varying(256) | The queue name which the issue was placed. | Acme Residential, Acme Wireless | None | None | 2020-06-11 00:00:00 | 2020-06-11 00:00:00 | None | ||||
| opportunity_ts | timestamp | Timestamp of the opportunity event | 2023-01-02 19:58:06 | None | None | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | None |
Table: quick_reply_buttons
The quick_reply_button_interaction table contains information associated with a specific quick_reply_button, its final intent and any aggregation counts over the day (e.g. escalated_to_chat, escalation_requested). Aggregated for a 24 hour period. Only ended issues are counted.
Sync Time: 1d
Unique Condition: company_id, company_subdivision, company_segments, final_intent_code, quick_reply_button_text, escalated_to_chat, escalation_requested, quick_reply_button_index
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| final_intent_code | character varying(255) | The last intent code of the flow which the user navigated. | PAYBILL | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| escalated_to_chat | bigint | 1 if an issue escalated to live chat, 0 if not. | 1 | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| escalation_requested | integer | 1 if customer was asked to wait for an agent or if a customer asked to speak to an agent. | 1 | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| quick_reply_button_text | character varying(65535) | The text of the quick reply button. | ‘Billing’ | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| quick_reply_button_index | integer | The position of the quick reply button shown. | (1,2,3) | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| quick_reply_displayed_count | bigint | The number of times this button was shown. | 42 | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| quick_reply_selected_count | bigint | The number of times this button was selected. | 42 | None | None | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: reps
The rep table contains a listing of data regarding each rep. Expected data includes their name, the rep id, their slot configuration and the rep status. This rep data is collected daily.
Sync Time: 1d
Unique Condition: rep_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| created_ts | timestamp | The timestamp of when we get an event in kafka. | 2019-02-19T21:31:43+00:00 | None | None | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | None | ||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | None | None | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | None | ||||
| crm_agent_id | varchar(255) | deprecated: 2019-09-25 | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | None | ||||
| name | varchar(255) | The rep name as imported from the CRM. | Smith, Anne | None | None | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | None | ||||
| max_slot | smallint | The number of slots or concurrent conversations this rep can have at the same time. | 4 | None | None | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | None | ||||
| disabled_time | timestamp without time zone | The time when this rep was removed from the ASAPP system. | 2019-02-27T12:56:34+00:00 | None | None | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | None | ||||
| agent_status | deprecated: 2019-09-25 | None | None | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | None | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | None | ||||
| crm_rep_id | The rep identifer from the client system. | monica.rosa | None | None | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | None | |||||
| rep_status | varchar(255) | The last known status of the rep at UTC midnight. | 80001 | None | None | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: rep_activity
The rep_activity table tracks status and slot information of each agent over time, including time spent in each status and time utilized in chats. In this table, the data is captured in 15 minute increments throughout the day. instance_ts is actually the 15-minute window in question, and is part of the primary key. It does not indicate the last time a relevant event happened as in other tables. Windows may be re-stated when information from a later window amends them, for example to account for additional utilized time.
Sync Time: 1h
Unique Condition: company_id, instance_ts, rep_id, status_id, in_status_starting_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The start of the 15-minute time window under observation. As an example, for a 15 minute interval an instance_ts of 12:30 implies activity from 12:30 to 12:45. | 2019-11-08 14:00:00 | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||
| update_ts | timestamp without time zone | The timestamp at which the last event for this record occurred. This usually represents the status end or the end of the last conversation handled in this status. | 2018-06-10 14:24:00 | None | None | 2019-12-16 00:00:00 | 2019-12-16 00:00:00 | None | ||||
| export_ts | The end of the time window for which this record was exported. This is used for de-duplicating records. | 2018-06-10 14:30:00 | None | None | 2019-12-16 00:00:00 | 2019-12-16 00:00:00 | None | |||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||
| company_subdivision | varchar(255) | The company subdivision relates to the customer issue and is not relevant to reps. Intentionally left blank. | ACMEsubcorp | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||
| company_segments | varchar(255) | The company segments field relates to the customer issue and is not relevant to reps. Intentionally left blank. | marketing,promotions | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||
| agent_name | deprecated: 2019-09-25 | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||||
| status_id | character varying(65535) | The ASAPP identifier for a given status. | OFFLINE, 1 | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||
| status_description | character varying(65535) | The human text name for a given status. | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | |||||
| orig_status_description | character varying(191) | The text of the status before alteration for disconnects. | Available, Away, Coffee Break, Active | None | None | 2020-01-07 00:00:00 | 2020-01-07 00:00:00 | None | ||||
| in_status_starting_ts | timestamp without time zone | Inside this 15m window, what time did the agent enter this status. | 2020-01-08T19:32:38.352000+00:00 | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||
| linear_ute_time | double precision | Time in seconds the agent spent handling at least one issue in this status within this 15-minute time window. | 253.34, 0.0, 5.046 | None | None | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| cumul_ute_time | double precision | The collective time in seconds the agent spent handling all issues in this status within this 15-minute time window. This time may exceed the status time due to concurrency slots. | 498.82, 0.0, 0.428 | None | None | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| unutilized_time | double precision | The time in seconds the agent spent not handling any issues in this status within this 15-minute time window. | 37.60, 0.0 | None | None | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | None | ||||
| window_status_time | double precision | The length of time which the agent was inside this status in seconds. | 0.107, 900.0 | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||
| total_status_time | double precision | Time in seconds that the agents spent in this status including contiguous time spent outside of this 15-minute time window. | 5.046, 0.107 | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||
| max_slots | integer | The number of issue slots or concurrency values which the rep set for themselves for this window. | 3, 2 | None | None | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | None | ||||
| status_end_ts | timestamp without time zone | The timestamp at which this agent exited the designated state. Note that this time may be null or after the next instance_ts, which implies that the agent did not change statuses within this 15-minute window. | 2018-06-10 14:23:00 | None | None | 2020-01-07 00:00:00 | 2020-01-07 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_name | varchar(191) | The name of this rep. Jane Doe | John | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| desk_mode | varchar(191) | The mode of the desktop which the agent is logged into. Modes include CHAT or VOICE. | ‘CHAT’, ‘VOICE’ | None | None | 2019-12-10 00:00:00 | 2019-12-10 00:00:00 | None | ||||
| last_dispositioned_ts | timestamp | Timestamp at which rep gets unassigned for a given rep status started at a given time | 2018-06-10 14:24:00 | None | None | 2024-05-29 00:00:00 | 2024-05-29 00:00:00 | None |
Table: rep_assignment_disposition
This view contains information relating to rep-disposition responses.
Sync Time: 1h
Unique Condition: company_id, issue_id, rep_id, rep_assigned_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | ||||
| rep_assigned_ts | timestamp without time zone | The timestamp at which the issue was assigned to the rep. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| disposition_event | character varying(65535) | The event type associated with the disposition event | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| disposition_notes_txt | character varying(65535) | Disposition notes associated with the disposition event | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| disposition_notes_valid | boolean | Boolean value to indicate if the notes are different than blank or null. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| crm_offered_ts | timestamp without time zone | Timestamp of the last CRM offered event. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| crm_outcome_ts | timestamp without time zone | Timestamp of the last CRM outcome event. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| crm_is_success | boolean | Boolean value to indicate if the disposition event is successfully sent to partner CRM | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| crm_error_type | character varying(65535) | This field indicates the type of an error occured in the pipeline. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| crm_error_source | character varying(65535) | This field indicates where in the pipeline the event is failed to publish. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| presented_tags | character varying(65535) | Unique list of all summary tags presented to agent for this assignment. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| selected_tags | character varying(65535) | Unique list of all summary tags selected by agent for this assignment. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| notes_presented_tags | character varying(65535) | Unique list of the summary tags presented to agent at the OTF NOTES state for this assignment. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| notes_selected_tags | character varying(65535) | Unique list of the summary tags selected by agent at the OTF NOTES state for this assignment. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| assignment_end_presented_tags | character varying(65535) | Unique list of the summary tags presented to agent at the end of assignment state. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| assignment_end_selected_tags | character varying(65535) | Unique list of the summary tags selected by agent at the end of assignment state. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| presented_tags_ct_distinct | bigint | Distinct count of all summary tags presented to agent for this assignment. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| selected_tags_ct_distinct | bigint | Distinct count of all summary tags selected by agent for this assignment. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| notes_presented_tags_ct_distinct | bigint | Distinct count of the summary tags presented to agent at the OTF NOTES state for this assignment. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| notes_selected_tags_ct_distinct | bigint | Distinct count of the summary tags selected by agent at the OTF NOTES state for this assignment. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| assignment_end_presented_tags_ct_distinct | bigint | Distinct count of the summary tags presented to agent at the end of assignment state. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| assignment_end_selected_tags_ct_distinct | bigint | Distinct count of the summary tags selected by agent at the end of assignment state. | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | |||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | None | ||||
| auto_summary_txt | character varying(65535) | Text of the automatic generative summary of this assignment, if applicable. Note that this field will be null of no auto summary could be found or if the feature is not enabled. | None | None | 2023-02-16 00:00:00 | 2023-02-16 00:00:00 | None |
Table: rep_attributes
The rep attributes table contains various data associated with a rep such as their given role. This table may not exist or may be empty if the client chooses to use rep_hierarchy instead. This is a daily snapshot of information.
Sync Time: 1d
Unique Condition: rep_attribute_id, rep_id, created_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| created_ts | timestamp | The date this agent was created. | 2019-06-24T18:02:05+00:00 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| attribute_name | character varying(64) | The attribute key value. | role, companygroup, jobcode | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| attribute_value | character varying(1024) | The attribute value associated with the attribute_name. | manager, representative, lead | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| agent_attribute_id | deprecated: 2019-09-25 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||||
| external_agent_id | varchar(255) | deprecated: 2019-09-25 | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_attribute_id | bigint | The ASAPP identifier for this attribute. | 1200001 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| external_rep_id | varchar(255) | The rep/agent identifier from the client perspective. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: rep_augmentation
The rep_augmentation table tracks a specific issue and rep; and calculates metrics on augmentation types and counts of usages of augmentation. This table will allow billing for the augmentation feature per each issue.
Sync Time: 1h
Unique Condition: issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| external_customer_id | varchar(255) | The customer identifier as provided by the client. | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | |||||
| conversation_end_ts | timestamp | The time of the conversation end event. | 2018-06-23 21:23:53 | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| auto_suggest_msgs | bigint | The number of autosuggest prompts used by the rep. | 3 | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| auto_complete_msgs | bigint | The number of autocompletion prompts used by the rep. | 2 | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| did_customer_timeout | boolean | Boolean value indicating whether the customer timed out. | false, true | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| is_rep_resolved | boolean | Boolean value indicating whether the rep marked this conversation resolved. | true, false | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| is_billable | boolean | Boolean value indicating whether the rep marked the conversation resolved after using autocomplete or autosuggest. | true, false | None | None | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | None | ||||
| custom_auto_suggest_msgs | bigint | The number of custom autocompletion prompts used by the rep (is a subset of auto_suggest_msgs). | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| custom_auto_complete_msgs | bigint | The number of custom autosuggest prompts used by the rep (is a subset of auto_complete_msgs). | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| drawer_msgs | bigint | The number of custom drawer messages used by the rep. | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| kb_search_msgs | bigint | The number of messages used from knowledge base search. | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| kb_recommendation_msgs | bigint | The number of messages used from knowledge base recommendations. | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_id | varchar(191) | Last rep_id that worked on this issue. | 123008 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| is_autopilot_timeout_msgs | Number of autopilot timeout messages. | 2 | None | None | 2020-06-11 00:00:00 | 2020-06-11 00:00:00 | None | |||||
| phrase_auto_complete_presented_msgs | integer | Count of utterances where at least one phrase autocomplete was suggested/presented. | None | None | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | None | |||||
| cume_phrase_auto_complete_presented | integer | Total number of phrase autocomplete suggestions per issue. | None | None | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | None | |||||
| phrase_auto_complete_msgs | integer | Count of utterances where at least one phrase autocomplete was accepted/sent. | None | None | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | None | |||||
| cume_phrase_auto_complete | integer | Total number of phrase autocompletes per issue. | None | None | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | None | |||||
| exclusive_phrase_auto_complete_msgs | integer | Count of utterances where at least one phrase autocomplete was accepted/sent and no other augmentation was used. | None | None | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | None |
Table: rep_convos
The rep_convos table captures metrics associated with a rep and an issue. Expected metrics include “average response time”, “cumulative customer response time”, “disposition type” and “handle time”. This data is captured in 15 minute window increments.
Sync Time: 1h
Unique Condition: issue_id, rep_id, issue_assigned_ts
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| issue_assigned_ts | timestamp without time zone | The time when an issue was first assigned to this rep. | 2019-10-31T18:37:37.848000+00:00 | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| agent_first_response_ts | deprecated: 2019-09-25 | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||||
| dispositioned_ts | timestamp | The time when the issue left the rep’s screen. | 2019-10-31T18:46:39.869000+00:00 | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| customer_end_ts | timestamp without time zone | The time at which the customer ended the issue. This may be NULL. | 2019-10-31T18:46:12.559000+00:00 | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| disposition_event_type | varchar(255) | Who or what ended an issue. | rep, customer, batch (system/auto ended), batch | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| cust_utterance_count | bigint | The count of customer utterances from issue_assigned_ts to dispositioned_ts. | 5 | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| rep_utterance_count | bigint | The count of rep utterances from issue_assigned_ts to dispositioned_ts. | 5 | None | None | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | None | ||||
| handle_time | Time in seconds a rep spent working on a particular assignment. The time between assignment and the disposition event. | 428.9 | None | None | 2019-03-19 00:00:00 | 2019-03-20 00:00:00 | None | |||||
| lead_time | The time in seconds a rep spent leading the conversation. The time between assignment and time of last utterance by THE CUSTOMER. If there is no utterance by the customer, then lead time equals the total_handle_time. | 320.05 | None | None | 2019-03-19 00:00:00 | 2019-03-20 00:00:00 | None | |||||
| wrap_up_time | The time in seconds a rep spent wrapping the conversation. Defined as total_handle_time-total_lead_time. | 3.614 | None | None | 2019-03-19 00:00:00 | 2019-03-20 00:00:00 | None | |||||
| rep_response_ct | int | The total count of responses by the rep. Max of one message following a customer message counted as a response. | 5 | None | None | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| cust_response_ct | int | The total count of responses by the customer. Max of one message following a rep message counted as a response. | 12 | None | None | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| auto_suggest_msgs | bigint | The number of autosuggest prompts used by the rep (inclusive of custom_auto_suggest_msgs). | 5 | None | None | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | None | ||||
| auto_complete_msgs | bigint | The number of autocompletion prompts used by the rep (inclusive of custom_auto_complete_msgs). | 5 | None | None | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | None | ||||
| custom_auto_suggest_msgs | bigint | The number of custom autocompletion prompts used by the rep (is a subset of auto_suggest_msgs). | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| custom_auto_complete_msgs | bigint | The number of custom autosuggest prompts used by the rep (is a subset of auto_complete_msgs). | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| drawer_msgs | bigint | The number of custom drawer messages used by the rep. | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| kb_search_msgs | bigint | The number of messages used by the rep from the knowledge base searches. | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| kb_recommendation_msgs | bigint | The number of messages used by the rep from the knowledge base recommendations. | 2 | None | None | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | None | ||||
| is_ghost_customer | boolean | Boolean value indicating if the customer was assigned a rep but never responded. | true, false | None | None | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| first_response_seconds | bigint | The total time taken by the rep to send the first message, once the message was assigned. | 26.148 | None | None | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| cume_rep_response_seconds | bigint | The total time across the assignment for the rep to send response messages. | 53.243 | None | None | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| max_rep_response_seconds | double precision | The maximum time across the assignment for the rep to send a response message. | 77.965 | None | None | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| avg_rep_response_seconds | double precision | The average time across assignment for the rep to send response messages. | 22.359 | None | None | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| cume_cust_response_seconds | bigint | The total time across the assignment for the customer to send response messages. | 332.96 | None | None | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_first_response_ts | datetime | The time when a rep first responded to the customer. | 2019-10-31T18:38:03.996000+00:00 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| hold_ct | bigint | The total count that this rep was part of a hold call. This field is not applicable to chat. | 1 | None | None | 2019-11-19 00:00:00 | 2019-11-19 00:00:00 | None | ||||
| cume_hold_time_seconds | double precision | The total duration of time the rep placed the customer on hold across the call. This field is not applicable to chat. 93.30 | None | None | 2019-11-19 00:00:00 | 2019-11-19 00:00:00 | None | |||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| client_mode | varchar(191) | The communication mode used by the customer for a given issue (CHAT or VOICE). | CHAT, VOICE | None | None | 2019-12-10 00:00:00 | 2019-12-10 00:00:00 | None | ||||
| cume_cross_talk_seconds | numeric(38,5) | Total duration of time where both agent and customer were speaking. Only relevant for voice client mode. | None | None | 2019-12-28 00:00:00 | 2019-12-28 00:00:00 | None | |||||
| desk_mode_flag | bigint | Bitmap encodes if agent handled voice-issue ASAPP desk, had engagement with ASAPP desk. bitmap: 0: null, 1: ‘VOICE’, 2: ‘DESK’, 4: ‘ENGAGEMENT’, 8: ‘INACTIVITY’ NULL for non voice issues | 0, 1, 2, 5, 7 | None | None | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | None | ||||
| desk_mode_string | varchar(191) | Decodes the desk_mode flag. Current possible values (Null, ‘VOICE’, ‘VOICE_DESK’, ‘VOICE_DESK_ENGAGEMENT’,‘VOICE_INACTIVITY’). NULL for non voice issues. | VOICE_DESK | None | None | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | None | ||||
| queue_id | integer | The ASAPP queue identifier which the issue was placed. | 20001 | None | None | 2021-04-08 00:00:00 | 2021-04-08 00:00:00 | None | ||||
| autopilot_timeout_msgs | integer | Number of autopilot timeout messages. | 2 | None | None | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | None | ||||
| exclusive_phrase_auto_complete_msgs | integer | Count of utterances where at least one phrase autocomplete was accepted/sent and no other augmentation was used. | None | None | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | None | |||||
| custom_click_to_insert_msgs | integer | Total count of custom click_to_insert messages. | None | None | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | None | |||||
| ms_auto_suggest_msgs | integer | Total count of multi-sentence auto-suggest messages. | None | None | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | None | |||||
| ms_auto_complete_msgs | integer | Total count of multi-sentence auto-complete messages. | None | None | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | None | |||||
| ms_auto_suggest_custom_msgs | integer | Total count of custom multi-sentence auto-suggest messages. | None | None | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | None | |||||
| ms_auto_complete_custom_msgs | integer | Total count of custom multi-sentence auto-complete messages. | None | None | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | None | |||||
| autopilot_form_msgs | bigint | Number of autopilot form messages. | 2 | None | None | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | None | ||||
| click_to_insert_global_msgs | integer | Number of click to insert messages. | 2 | None | None | 2023-02-15 00:00:00 | 2023-02-15 00:00:00 | None | ||||
| autopilot_greeting_msgs | bigint | Number of autopilot greeting messages. | 2 | None | None | 2023-02-15 00:00:00 | 2023-02-15 00:00:00 | None | ||||
| augmented_msgs | bigint | Number of augmented messages. | 2 | None | None | 2023-02-22 00:00:00 | 2023-02-22 00:00:00 | None | ||||
| autopilot_ending_msgs_ct | integer | Number of autopilot endings | 2 | None | None | 2024-04-19 00:00:00 | 2024-04-19 00:00:00 | None |
Table: rep_hierarchy
The rep_hierarchy table contains the rep and their direct reports and their manager. This is a daily snapshot of rep hierarchy information. This table may be empty and if empty, then consult rep_attributes.
Sync Time: 1d
Unique Condition: subordinate_rep_id, superior_rep_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| subordinate_agent_id | deprecated: 2019-09-25 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||||
| superior_agent_id | deprecated: 2019-09-25 | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||||
| reporting_relationship | character varying(1024) | Relationship between subordinate and superior reps, e.g. “superiors_superior” for skip-level reporting. | superior, superiors_superior | None | None | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | None | ||||
| subordinate_rep_id | bigint | ASAPP rep identifier that is the subordinate of the superior_rep_id. | 110001 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| superior_rep_id | bigint | Superior rep id that is the superior of the subordinate_rep_id. | 20001 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None |
Table: rep_utilized
The purpose of the rep_utilized table is to calculate metrics associated with a rep’s activity states and utilization time. This table will list out utilization time and cumulative minutes per state. In this table, the data is captured in 15 minute increments throughout the day. instance_ts is actually the 15-minute window in question, and is part of the primary key. It does not indicate the last time a relevant event happened as in other tables. Windows may be re-stated when information from a later window amends them, for example to account for additional utilized time.
Sync Time: 1h
Unique Condition: instance_ts, rep_id, desk_mode, max_slots
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The start of the 15-minute time window under observation. As an example, for a 15 minute interval an instance_ts of 12:30 implies activity from 12:30 to 12:45. | 2019-11-08 14:00:00 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| update_ts | Timestamp at which the last event for this record occurred - usually the last status end or conversation end that was active in this window deprecated: 2020-11-09 | 2019-06-10 14:24:00 | None | None | 2020-01-29 00:00:00 | 2020-01-29 00:00:00 | None | |||||
| export_ts | The end of the time window for which this record was exported. This is used for de-duplicating records. | 2019-06-10 14:30:00 | None | None | 2020-01-29 00:00:00 | 2020-01-29 00:00:00 | None | |||||
| company_id | bigint | The ASAPP identifier of the company or test data source. | 10001 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| company_subdivision | varchar(255) | Relates to the customer issue, not relevant to reps. Intentionally left blank. | ACMEsubcorp | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| company_segments | varchar(255) | Relates to the customer issue, not relevant to reps. Intentionally left blank. | marketing,promotions | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| rep_name | varchar(191) | The name of the rep. | John Doe | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| max_slots | integer | Maximum chat concurrency slots enabled for this rep. | 2 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_logged_in_min | bigint | Cumulative Logged In Time (min) — Total cumulative time (linear time x max slots) the rep logged into tthe agent desktop. | 120 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| lin_logged_in_min | bigint | Linear Logged In Time (min) — Total linear time rep logged into agent desktop. | 60 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_avail_min | bigint | Cumulative Available Time (min) — Total cumulative time (linear time x max slots) the rep logged into agent desktop while in the “Available” state. | 90 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| lin_avail_min | bigint | Linear Available Time (min) — Total linear time the rep logged into the agent desktop while in the “Available” state. | 45 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_busy_min | bigint | Cumulative Busy Time (min) — Total cumulative time (linear time x max slots) the rep logged into agent desktop while in a “Busy” state. | 30 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| lin_busy_min | bigint | Linear Busy Time (min) — Total linear time rep logged into agent desktop while in a “Busy” state. | 15 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_prebreak_min | bigint | Cumulative Busy Time - Pre-Break (min) — Total cumulative time (linear time x max slots) rep logged into agent desktop while in the Pre-Break vesion of the “Busy” state. | 10 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| lin_prebreak_min | bigint | Linear Busy Time - Pre-Break (min) — Total linear time the rep logged into Agent Desktop while in the Pre-Break vesion of Busy state | 5.6 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_ute_total_min | bigint | Cumulative Utilized Time (min) — Total cumulative time (linear time x active slots) the rep logged into agent desktop and utilized over all states. | 27.71 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| lin_ute_total_min | bigint | Linear Utilized Time (min) — Total linear time rep logged into agent desktop and utilized over all states. | 5.5 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_ute_avail_min | bigint | Cumulative Utilized Time While Available (min) — Total cumulative time (linear time x active slots) rep logged into agent desktop and utilized while in the “Available” state. | 11.5 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| lin_ute_avail_min | bigint | Linear Utilized Time While Available (min) — Total linear time rep logged into agent desktop and utilized while in the “Available” state. | 5.93 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_ute_busy_min | bigint | Cumulative Busy Time - While Chatting (min) — Total cumulative time (linear time x active slots) rep logged into agent desktop while in a Busy state and handling at least one assignment. | 7.38 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| lin_ute_busy_min | bigint | Linear Utilized Time While Busy (min) — Total linear time rep logged into agent desktop while in a Busy state and handling at least one assignment. | 3.44 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_ute_prebreak_min | bigint | Cumulative Utilized Time While Busy Pre-Break (min) — Cumulative time rep logged into agent desktop and utilized while in the “Pre-Break Busy” state. | 5.35 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| lin_ute_prebreak_min | bigint | Linear Utilized Time While Busy Pre-Break (min) — Linear time rep logged into agent desktop and utilized while in the “Pre-Break Busy” state. | 3.65 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| labor_min | bigint | Total linear time rep logged into agent desktop in the available state, plus cumulative time rep was handling issues in any “Busy” state. | 18.44 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| busy_clicks_ct | bigint | Busy Clicks — Number of times the rep moved from an active to a busy state. | 1 | None | None | 2019-05-10 00:00:00 | 2019-05-10 00:00:00 | None | ||||
| ute_ratio | Utilization ratio - cumulative utilized time divided by linear total potential labor time. | 1.71 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | |||||
| act_ratio | Active utilization ratio - cumulative utilized time in the available state divided by total labor time. | 1.67 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | |||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| desk_mode | varchar(191) | The mode of the desktop that the agent is logged into - whether CHAT or VOICE. | ‘CHAT’, ‘VOICE’ | None | None | 2019-12-10 00:00:00 | 2019-12-10 00:00:00 | None | ||||
| lin_utilization_level_over_min | bigint | Total linear time in minutes when rep has no assignment Total linear time in minute when rep’s assignments is greater than rep’s max slot | 120 | None | None | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| lin_utilization_level_full_min | bigint | Total linear time in minute when rep’s assignments is equal to rep’s max slot | 120 | None | None | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| lin_utilization_level_light_min | bigint | Total linear time in minute when rep’s assignments is less than rep’s max slot | 120 | None | None | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| workload_level_no_min | bigint | Total time in minute when rep has no active assignment | 120 | None | None | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| workload_level_over_min | bigint | Total time in minute when rep’s active assignment is greater than rep’s max slot | 120 | None | None | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| workload_level_full_min | bigint | Total time in minute when rep’s active assignment is equal to rep’s max slot | 120 | None | None | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| workload_level_light_min | bigint | Total time in minute when rep’s active assignment is less than rep’s max slot | 120 | None | None | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| flex_protect_min | bigint | Total time in minute when rep is flex protected | 120 | None | None | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | None | ||||
| cum_weighted_min | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | |||||||
| cum_weighted_seconds | bigint | Total effort_workload when a rep has active assignments | 10 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_ute_weighted_avail_unflexed_seconds | bigint | Total time weighted in seconds when a rep is available | 160 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None | ||||
| cum_weighted_inactive_seconds | bigint | Total effort_workload when a rep has no active assignments | 10 | None | None | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | None |
Table: sms_events
Exports for each 15 min window of SMS flow events
Sync Time: 1h
Unique Condition: company_id, sms_flow_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| sms_flow_id | character varying(65535) | The flow identifier. | 019bf9e4-a01a-4420-b419-459659a1b50e | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| external_session_id | character varying(65535) | The session identifier received from the client. | 772766038 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| message_sent_result | character varying(6) | The status of a SMS request received from the 3rd party SMS provider. | ‘Sent’ | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| message_sent_result_status_code | character varying(65535) | The failure reason received from 3rd party SMS provider. | 30001 (Queue Overflow), 30004 (Message Blocked) | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| message_character_count | integer | The SMS message’s character count. | 29 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| partner_triggered_ts | timestamp without time zone | The date and time in which a partner sends a SMS request to ASAPP. | 2018-03-03 12:23:52 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| provider_sent_ts | timestamp without time zone | The date and time in which ASAPP sends a SMS request from 3rd party SMS provider. | 2018-03-03 12:23:52 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| provider_status_ts | timestamp without time zone | The date and time in which the 3rd party SMS provider sends back the status of a SMS request. | 2018-03-03 12:23:52 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| company_id | bigint | The ASAPP identifier of the company or test data source. | 10001 | None | None | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-08 00:00:00 | 2020-03-23 00:00:00 | None |
Table: transfers
The purpose of the transfers table is to capture information associated with an issue transfer between reps. The data is captured per 15 minute window.
Sync Time: 1h
Unique Condition: company_id, issue_id, rep_id, timestamp_req
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||
| timestamp_req | timestamp without time zone | The date and time when the transfer was requested. | 2019-06-11T13:27:09.470000+00:00 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||
| timestamp_reply | timestamp without time zone | The date and time when the transfer request was received. | 2019-06-11T13:31:58.537000+00:00 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2018-08-04 00:00:00 | 2018-08-04 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-10-04 00:00:00 | 2018-10-04 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-10-04 00:00:00 | 2018-10-04 00:00:00 | None | ||||
| requested_agent_transfer | deprecated: 2019-09-25 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||||
| group_transfer_to | character varying(65535) | The group identifier where the issue was transferred. | 20001 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||
| group_transfer_to_name | character varying(191) | The group name where the issue was transferred. | acme-mobile-eng | None | None | 2018-08-04 00:00:00 | 2018-08-04 00:00:00 | None | ||||
| group_transfer_from | character varying(65535) | The group identifier which transferred the issue. | 87001 | None | None | 2018-08-04 00:00:00 | 2018-08-04 00:00:00 | None | ||||
| group_transfer_from_name | character varying(191) | The group name which transferred the issue. acme-residential-eng | None | None | 2018-08-04 00:00:00 | 2018-08-04 00:00:00 | None | |||||
| actual_agent_transfer | deprecated: 2019-09-25 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||||
| accepted | boolean | A boolean flag indicating whether the transfer was accepted. | true, false | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||
| is_auto_transfer | boolean | A boolean flag indicating whether this was an auto-transfer. | true, false | None | None | 2019-07-22 00:00:00 | 2019-07-22 00:00:00 | None | ||||
| exit_transfer_event_type | character varying(65535) | The event type which concluded the transfer. | TRANSFER_ACCEPTED, CONVERSATION_END | None | None | 2019-07-22 00:00:00 | 2019-07-22 00:00:00 | None | ||||
| transfer_button_clicks | bigint | The number of times a rep requested a transfer from transfer initiation to when the transfer was received. | 1 | None | None | 2019-08-22 00:00:00 | 2019-08-22 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| requested_rep_transfer | bigint | The rep which requested the transfer. | 1070001 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| actual_rep_transfer | bigint | The rep which received the transfer. | 250001 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| requested_group_transfer_id | bigint | The group identifier where the transfer was initially requested. | 123455 | None | None | 2019-12-13 00:00:00 | 2019-12-13 00:00:00 | None | ||||
| requested_group_transfer_name | character varying(191) | The group name where the transfer was initially requested. | support | None | None | 2019-12-13 00:00:00 | 2019-12-13 00:00:00 | None | ||||
| route_code_to | varchar(191) | IVR routing code indicating the customer contact reason to which the issue is being transferred into | 2323 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None | ||||
| route_code_from | varchar(191) | IVR routing code indicating the customer contact reason from the previous assignment | 2323 | None | None | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | None |
Table: utterances
The purpose of the utterances table is to list out each utterance and associated data which was captured during a conversation. This table will include data associated with ongoing conversations which are unended.
Sync Time: 1h
Unique Condition: created_ts, issue_id, sender_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | ||||
| created_ts | timestamp | The date and time which the message was sent. | 2019-12-17T17:11:41.626000+00:00 | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | ||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | ||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | ||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | ||||
| sequence_id | integer | deprecated: 2019-09-26 | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | |||||
| sender_id | bigint | The identifier of the person who sent the message. | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | |||||
| sender_type | character varying(191) | The type of sender. | customer, bot, rep, rep_note, rep_whisper | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | ||||
| utterance_type | character varying(65535) | The type of utterance sent. | autosuggest, autocomplete, script, freehand | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | ||||
| sent_to_agent | boolean | deprecated: 2019-09-25 | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | |||||
| utterance | character varying(65535) | The spoken text from a bot or human (i.e. customer, rep, expert). | ’Upgrade current device’, ‘Is there anything else we can help you with?‘ | None | None | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | ||||
| sent_to_rep | A boolean flag indicating if an utterance was sent from a customer to a rep. | true, false | None | None | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | None | |||||
| utterance_start_ts | timestamp without time zone | This timestamp marks the time when a person began speaking in the voice platform. In chat platforms or non-voice generated messages, this timestamp will be NULL. | NULL, 2019-10-18T18:45:00+00:00 | None | None | 2019-12-06 00:00:00 | 2019-12-06 00:00:00 | None | ||||
| utterance_end_ts | timestamp without time zone | This timestamp marks the time when a person finished speaking in the voice platform. In chat platforms or non-voice generated messages, this timestamp will be NULL. | NULL, 2019-10-18T18:45:00+00:00 | None | None | 2019-12-06 00:00:00 | 2019-12-06 00:00:00 | None | ||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | None | None | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | None | ||||
| event_uuid | varchar(36) | A UUID uniquely identifying each utterance record | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | None | None | 2020-10-23 00:00:00 | 2020-10-23 00:00:00 | None |
Table: voice_intents
The voice intents table includes fields that provide visibility to the customer’s contact reason for the call
Sync Time: 1h
Unique Condition: company_id, issue_id
| name | type | description | example | aggregates | primary | created_date | updated_date | ignore | pii | release_state | specific_use | feature_group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| instance_ts | timestamp | The time window of computed elements. This window is usually 15 minutes or 1 hour depending on the generation time. Times are rounded down to the nearest interval (for a time of 12:34 and an interval of 15m, this is 12:30). As an example, for a 15 minute interval an instance_ts of 12:30 implies events from 12:30 -> 12:45. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | None | None | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | None | ||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | None | None | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | None | ||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | None | None | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | None | ||||
| voice_intent_code | varchar(255) | Voice intent code with the highest score associated to the issue | PAYBILL | None | None | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | None | ||||
| voice_intent_name | varchar(255) | Voice intent name with the highest score associated to the issue | Payment history | None | None | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | None |
Last Updated November 24, 2024
Was this page helpful?