Feed Schemas
ASAPP Messaging Feed Schemas
Feed Schemas
ASAPP Messaging Feed Schemas
The tables below provide detailed information regarding the schema for exported data files that we can make available to you for ASAPP Messaging.
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | no | ||||||
| rep_name | varchar(191) | Name of agent | John | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | no | ||||||
| status_description | varchar | Indicates status of the agent. | ONLINE | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | no | ||||||
| status_start_ts | datetime | Timestamp at which this agent entered that status. | 2018-06-10 14:23:00 | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | no | ||||||
| status_end_ts | datetime | Timestamp at which this agent exited that status. | 2018-06-10 14:23:00 | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | no | ||||||
| status_time_seconds | double | Time in seconds that the agents spent in that status. | 2353.23 | 2020-11-10 00:00:00 | 2020-11-10 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | no | ||||||
| company_marker | varchar(191) | The ASAPP company marker. | spear, aa | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | no | ||||||
| event_type | varchar(191) | agent journey event type on record | CUSTOMER_TIMEOUT, TEXT_MESSAGE | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | no | ||||||
| event_count | bigint | count of the agent journey event type on record | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | no | |||||||
| disconnected_count | bigint | number of times that a rep disconnected for less than 1 hour | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | no | |||||||
| disconnected_seconds | bigint | cumulative number of seconds that a rep disconnected for less than 1 hour | 2022-01-31 00:00:00 | 2022-01-31 00:00:00 | no |
Table: autopilot_flow
This table contains factual data about autopilot flow.
Sync Time: 1h
Unique Condition: company_marker, issue_id, form_start_ts
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| rep_assigned_ts | timestamp without time zone | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||||
| 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 | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | |||||||
| form_dismissed_event_ts | timestamp without time zone | Timestamp of recommended autopilot form being dismissed. | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | |||||||
| form_presented_event_ts | timestamp without time zone | Timestamp the autopilot form being presented to end user. | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | |||||||
| form_submitted_event_ts | timestamp without time zone | Timestamp the autopilot form being submitted by end user | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | |||||||
| flow_id | varchar(255) | An ASAPP identifier assigned to a particular flow executed during a customer event or request. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| flow_name | varchar(255) | The ASAPP text name for a given flow which was executed during a customer event or request. | FirstChatMessage, AccountNumberFlow | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| 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 | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | |||||||
| is_secure_form | boolean | Is this a secure form flow. | false | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| queue_id | integer | The ASAPP queue identifier which the issue was placed. | 210001 | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| asapp_mode | varchar(191) | Mode of the desktop that the rep is logged into (CHAT or VOICE). | CHAT, VOICE | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2022-03-09 00:00:00 | 2022-03-09 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_utterance_ts | varchar(255) | The timestamp of the first customer utterance for an issue. | 2018-09-05 19:58:06 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_utterance_text | varchar(255) | Time of the first customer message in the conversation. | ‘Pay my bill’, ‘Check service availability’ | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_intent_code | varchar(255) | Code name which are used for classifying customer queries in first interaction. | PAYBILL, COVERAGE | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_intent_code_alt | varchar(255) | Alternative second best code name which are used for classifying customer queries in first interaction. | PAYBILL, COVERAGE | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| final_intent_code | varchar(255) | The final code name classifying the customer’s query, based on the flow navigated; defaults to the first interaction code if no flow was followed. | PAYBILL, COVERAGE | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| intent_path | varchar(255) | A comma-separated list of all intent codes from the customer’s flow navigation. If no flow was navigated, this will match the first intent code. | OUTAGE,CANT_CONNECT | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| disambig_count | bigint | The number of times a disambiguation event was presented for an issue. | 2 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| ftd_visit | boolean | Indicates whether free-text disambiguation was used to help the customer present a clearer intent, based on the number of texts sent to AI. | true, false | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| faq_id | varchar(255) | The last FAQ identifier presented for an issue. | FORGOT_LOGIN_faq | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| final_action_destination | varchar(255) | The last deep-link URL clicked during the issue resolution process. | asapp-pil://acme/JSONataDeepLink | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| is_first_intent_correct | boolean | Indicates whether the initial intent associated with the chat was correct, based on feedback from the agent. | true, false | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| first_rep_id | varchar(191) | The first ASAPP rep/agent identifier found in a window of time. | 123008 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-11-07 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| first_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | 2018-11-07 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_utterance_ts | varchar(255) | Timestamp of the first customer message in the conversation. | 2018-09-05 19:58:06T00:01:16.203000+00:00 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_utterance_text | varchar(255) | First message from the customer. | I need to pay my bill. | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_intent_code | varchar(255) | Code name which are used for classifying customer queries in first interaction | PAYBILL | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_intent_code_alt | varchar(255) | alternative second best code name which are used for classifying customer queries in first interaction. | PAYBILL | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| final_intent_code | varchar(255) | The final code name classifying the customer’s query, based on the flow navigated; defaults to the first interaction code if no flow was followed. | PAYBILL | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| intent_path | varchar(255) | A comma-separated list of all intent codes from the customer’s flow navigation. If no flow was navigated, this will match the first intent code. | OUTAGE, CANT_CONNECT | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| disambig_count | bigint | The number of times a disambiguation event was presented for an issue. | 2 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| ftd_visit | boolean | Indicates whether free-text disambiguation was used to help the customer present a clearer intent, based on the number of texts sent to AI. | false, true | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| faq_id | varchar(255) | The last faq-id presented for an issue. | FORGOT_LOGIN_faq | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| final_action_destination | varchar(255) | The last deep-link URL clicked during the issue resolution process. | asapp-pil://acme-mobile/protection-plan-features | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| is_first_intent_correct | boolean | Indicates whether the initial intent associated with the chat was correct, based on feedback from the agent. | true, false | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| first_rep_id | varchar(191) | The first ASAPP rep/agent identifier found in a window of time. | 123008 | 2018-11-07 00:00:00 | 2018-11-07 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| first_utterance_ts | timestamp | Timestamp of the first customer message in the conversation. | 2018-09-05 19:58:06 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| first_utterance_text | varchar(255) | First message content from the customer. | ”Hello, please assist me” | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| issue_created_ts | timestamp | Timestamp of the “NEW_ISSUE” event for an issue. | 2018-09-05 19:58:06 | 2019-10-15 00:00:00 | 2019-10-15 00:00:00 | no | ||||||
| last_event_ts | timestamp | The timestamp of the last event for an issue. | 2018-09-05 19:58:06 | 2019-09-16 00:00:00 | 2019-09-16 00:00:00 | no | ||||||
| last_srs_event_ts | timestamp without time zone | Timestamp of the last bot assisted event. | 2018-09-05 19:58:06 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| conversation_end_ts | timestamp | Timestamp when the conversation ended. | 2018-09-05 19:58:06 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | 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 | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| session_type | character varying(255) | ASAPP session type. | asapp-uuid | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| session_event_type | character varying(255) | Basic type of the session event. | UPDATE, CREATE | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| internal_session_id | character varying(255) | Internal identifier for the ASAPP session. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| internal_session_type | character varying(255) | An ASAPP session type for internal use. | asapp-uuid | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| 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 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| internal_user_session_type | varchar(255) | The customer ASAPP session type. | customer | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| external_session_id | character varying(255) | Client-provided session identifier passed to the SDK during chat initialization. | 062906ff-3821-4b5d-9443-ed4fecbda129 | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| external_session_type | character varying(255) | Client-provided session type passed to the SDK during chat initialization. | visitID | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| external_user_id | varchar(255) | Customer identifier provided by the client, available if the customer is authenticated. | EECACBD227CCE91BAF5128DFF4FFDBEC | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| external_user_type | varchar(255) | The type of external user identifier. | acme_CUSTOMER_ACCOUNT_ID | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| external_issue_id | character varying(255) | Client-provided issue identifier passed to the SDK (currently unused). | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | |||||||
| external_channel | character varying(255) | Client-provided customer channel passed to the SDK (currently unused). | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | |||||||
| customer_id | bigint | ASAPP customer id | 1470001 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| escalated_to_chat | bigint | Flag indicating whether the issue was escalated to an agent. false, true | 1 | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| platform | varchar(255) | A value indicating which consumer platform was used. | ios, android, web | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| device_type | varchar(255) | The last device type used by the customer for an issue. | mobile, tablet, desktop, watch, unknown | 2019-06-17 00:00:00 | 2019-06-17 00:00:00 | no | ||||||
| first_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | no | ||||||
| last_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | no | ||||||
| external_agent_id | varchar(255) | deprecated: 2019-09-25 | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | no | ||||||
| assigned_to_rep_time | timestamp | Time when the issue was first assigned to a rep, if applicable. | 2018-09-05 19:58:06 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| disposition_event_type | varchar(255) | Event type indicating how the conversation ended. | resolved, unresolved, timeout | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| disposition_ts | timestamp | Timestamp when the rep exited the issue or conversation. | 2018-09-05 19:58:06 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| termination_event_type | varchar(255) | Event type indicating the reason for conversation termination. | customer, agent, autoend | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| disposition_notes | text | Notes added by the last rep after marking the chat as completed. | ”The customer wanted to pay his bill. We successfully processed his payment.” | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| ended_resolved | integer | 1 if the rep marked the conversation resolved, 0 otherwise. | 1, 0 | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| ended_unresolved | integer | 1 if the rep marked the conversation unresolved, 0 otherwise. | 0, 1 | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| ended_timeout | integer | 1 if the customer timed out or abandoned chat, 0 otherwise. | 0, 1 | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| ended_auto | integer | 1 if the rep did not disposition the issue and it was auto-ended. | 0, 1 | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| ended_other | integer | 1 if the customer or rep terminated the issue but the rep didn’t disposition the issue. | 0, 1 | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| app_version_asapp | varchar(255) | ASAPP API version used during customer event or request. | com.asapp.api_api:-2f1a053f70c57f94752e7616b66f56d7bf1d6675 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| app_version_client | varchar(255) | ASAPP SDK version used during customer event or request. | web-sdk-4.0.0 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| session_metadata | character varying(65535) | Additional metadata information about the session, provided by the client. | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | |||||||
| last_sequence_id | integer | Last sequence identifier associated with the issue. | 115 | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| issue_queue_id | varchar(255) | Queue identifier associated with the issue. | 20001 | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| issue_queue_name | varchar(255) | Queue name associated with the issue. | acme-wireless-english | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| csat_rating | double precision | Customer Satisfaction (CSAT) rating for the issue. | 400.0 | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| sentiment_valence | character varying(50) | Sentiment of the issue. | Neutral, Negative | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| deep_link_queue | character varying(65535) | Deeplink queued for the issue. | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | |||||||
| end_srs_selection | character varying(65535) | User selected button upon end_srs. | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | |||||||
| trigger_link | VARCHAR | deprecated: 2020-04-25 aliases: current_page_url | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | no | |||||||
| auth_state | varchar(3) | Flag indicating if the user is authenticated. | false, true | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| 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 | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| auth_source | character varying(65535) | Source of the first authentication event for an issue. | ivr-url | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| auth_external_user_type | character varying(65535) | External user type of the first authentication event for an issue. | ACME_CUSTOMER_ACCOUNT_ID | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| auth_external_user_id | character varying(65535) | User ID provided by the client for the first authentication event. | 9BE62CCD564D6982FF305DEBCEAABBB5 | 2019-05-15 00:00:00 | 2019-07-16 00:00:00 | no | ||||||
| is_review_required | boolean | Flag indicates whether an admin must review this issue. data type: boolean data type: boolean | true, false | 2019-07-24 00:00:00 | 2019-07-24 00:00:00 | no | ||||||
| mid_issue_auth_ts | timestamp without time zone | Time when the user authenticates during the middle of an issue, | 2020-01-11 08:13:26.094 | 2019-07-24 00:00:00 | 2019-07-24 00:00:00 | no | ||||||
| first_rep_id | varchar(191) | ASAPP provided identifier for the first rep involved with the issue. | 60001 | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | no | ||||||
| last_rep_id | varchar(191) | ASAPP provided identifier for the last rep involved with the issue. | 60001 | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | no | ||||||
| external_rep_id | varchar(255) | Client-provided identifier for the rep. | 0671018510 | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| first_voice_customer_state | varchar(255) | Initial state assigned to the customer when using voice. | IDENTIFIED | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | no | ||||||
| first_voice_customer_state_ts | timestamp | 2020-01-11 08:13:26.094 | 2018-09-05 19:58:06 | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | no | ||||||
| first_voice_identified_customer_state_ts | timestamp | Time when the customer was first assigned an IDENTIFIED state. | 2020-01-11 08:13:26.094 | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | no | ||||||
| first_voice_verified_customer_state_ts | timestamp | Time when the customer was first assigned an VERIFIED state. | 2020-01-11 08:13:26.094 | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | no | ||||||
| merged_ts | timestamp | Time when the issue was merged into another issue. data type: timestamp | 2020-01-11 08:13:26.094 | 2019-12-28 00:00:00 | 2019-12-28 00:00:00 | no | ||||||
| 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 | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | no | ||||||
| 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 | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | no | ||||||
| current_page_url | varchar(2000) | URL link (stripped of parameters) that triggered the start chat event. Only applicable for WEB platforms. aliases: trigger_link | https:www.acme.corp/billing/viewbill | 2020-04-24 00:00:00 | 2020-04-24 00:00:00 | no | ||||||
| raw_current_page_url | Full URL link (including parameters) that triggered the chat event. Only applicable for WEB platforms. aliases: raw_trigger_link | 2020-04-25 00:00:00 | 2020-04-25 00:00:00 | no | ||||||||
| language_code | VARCHAR(32) | Language code for the issue_id | English | 2022-01-04 00:00:00 | 2022-01-04 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| first_utterance_ts | timestamp | Timestamp of the first customer message in the conversation. | 2019-09-22T13:12:26.073000+00:00 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| first_utterance_text | varchar(65535) | First message content from the customer. | ”Hello, please assist me” | 2019-01-11 00:00:00 | 2022-06-08 00:00:00 | no | ||||||
| issue_created_ts | timestamp | Timestamp when the “NEW_ISSUE” event occurred. | 2019-11-21T19:11:01.748000+00:00 | 2019-10-15 13:12:26.073000+00:00 | 2019-10-15 13:12:26.073000+00:00 | no | ||||||
| last_event_ts | timestamp | Timestamp of the last event in the issue. | 2019-09-23T14:00:09.043000+00:00 | 2019-09-16 00:00:00 | 2019-09-16 00:00:00 | no | ||||||
| last_srs_event_ts | timestamp without time zone | Timestamp of the last bot assisted event. | 2019-09-22T13:12:26.131000+00:00 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| conversation_end_ts | timestamp | Timestamp when the conversation ended. | 2019-10-08T14:00:07.395000+00:00 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | 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 | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| session_type | character varying(255) | ASAPP session type. | asapp-uuid | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| session_event_type | character varying(255) | Basic type of the session event. | CREATE, UPDATE, DELETE | 2018-11-26 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| internal_session_id | character varying(255) | Internal identifier for the ASAPP session. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| internal_session_type | character varying(255) | An ASAPP session type for internal use. | asapp-uuid | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| 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_session_type field to determine which type the identifier represents. | 123004 | 2018-11-26 00:00:00 | 2018-12-06 00:00:00 | no | ||||||
| internal_user_session_type | varchar(255) | The customer ASAPP session type. | customer | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| external_session_id | character varying(255) | Client-provided session identifier passed to the SDK during chat initialization. | 062906ff-3821-4b5d-9443-ed4fecbda129 | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| external_session_type | character varying(255) | Client-provided session type passed to the SDK during chat initialization. | visitID | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| external_user_id | varchar(255) | Customer identifier provided by the client, available if the customer is authenticated. | MjU0ZTRiMDQyNDVlNTcyNWNlOTljNmI1NDc2NWQzNzdmNmJmZTFjZDgyY2IwMzc3MDkwZDI5YmQwZDlkODJhNA== | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| external_user_type | varchar(255) | The type of external user identifier. | acme_CUSTOMER_ACCOUNT_ID | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | ||||||
| external_issue_id | character varying(255) | Client-provided issue identifier passed to the SDK (currently unused). | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | |||||||
| external_channel | character varying(255) | Client-provided customer channel passed to the SDK (currently unused). | 2018-11-26 00:00:00 | 2020-10-24 00:00:00 | no | |||||||
| customer_id | bigint | An ASAPP customer identifier. | 1470001 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| escalated_to_chat | bigint | 1 if an issue escalated to live chat, 0 if not | 1 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| platform | varchar(255) | The consumer platform in use. | ios, android, web, voice | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| device_type | varchar(255) | The last device type used by the customer for an issue. | mobile, tablet, desktop, watch, unknown | 2019-06-17 00:00:00 | 2019-06-17 00:00:00 | no | ||||||
| first_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| last_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| external_agent_id | varchar(255) | deprecated: 2019-09-25 | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| assigned_to_rep_time | timestamp | Timestamp when the issue was first assigned to a rep, if applicable. | 2018-09-05 19:58:06T16:14:57.289000+00:00 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| disposition_event_type | varchar(255) | Event type indicating how the conversation ended. | resolved, unresolved, timeout | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| disposition_ts | timestamp | Timestamp when the rep exited the issue or conversation. | 2018-09-05 19:58:06T16:14:57.289000+00:00 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| termination_event_type | varchar(255) | Event type indicating the reason for conversation termination. | customer, agent, autoend | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| disposition_notes | text | Notes added by the last rep after marking the chat as completed. | ”The customer wanted to pay his bill. We successfully processed his payment.” | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| ended_resolved | integer | Indicator (1 or 0) for whether the rep marked the conversation as resolved. | 1, 0 | 2019-04-30 00:00:00 | 2019-05-01 00:00:00 | no | ||||||
| ended_unresolved | integer | Indicator (1 or 0) for whether the rep marked the conversation as unresolved. | 0, 1 | 2019-04-30 00:00:00 | 2019-05-01 00:00:00 | no | ||||||
| ended_timeout | integer | Indicator (1 or 0) for whether the customer abandoned or timed out of the chat. | 0, 1 | 2019-04-30 00:00:00 | 2019-04-30 00:00:00 | no | ||||||
| ended_auto | integer | Indicator (1 or 0) for whether the issue was auto-ended without rep disposition. | 0, 1 | 2019-04-30 00:00:00 | 2019-05-01 00:00:00 | no | ||||||
| ended_other | integer | Indicator (1 or 0) for whether the customer or rep terminated the issue without rep disposition. | 0, 1 | 2019-04-30 00:00:00 | 2019-05-01 00:00:00 | no | ||||||
| app_version_asapp | varchar(255) | ASAPP API version used during customer event or request. | com.asapp.api_api:-b393f2d920bb74ce5bbc4174ac5748acff6e8643 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| app_version_client | varchar(255) | ASAPP SDK version used during customer event or request. | web-sdk-4.0.2 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| session_metadata | character varying(65535) | Additional metadata information about the session, provided by the client. | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | |||||||
| last_sequence_id | integer | Last sequence identifier associated with the issue. | 25 | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| issue_queue_id | varchar(255) | Queue identifier associated with the issue. | 2001 | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| issue_queue_name | varchar(255) | Queue name associated with the issue. | acme-mobile-english | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| csat_rating | double precision | Customer Satisfaction (CSAT) rating for the issue. | 400.0 | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| sentiment_valence | character varying(50) | Sentiment of the issue. | Neutral, Negative | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | ||||||
| deep_link_queue | character varying(65535) | Deeplink queued for the issue. | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | |||||||
| end_srs_selection | character varying(65535) | User selected button option at the end of the session. | 2019-01-11 00:00:00 | 2019-01-11 00:00:00 | no | |||||||
| trigger_link | VARCHAR | deprecated: 2020-04-25 aliases: current_page_url | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | |||||||
| auth_state | varchar(3) | Flag indicating if the user is authenticated. | 0, 1 | 2018-11-26 00:00:00 | 2018-11-26 00:00:00 | no | ||||||
| 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 | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| auth_source | character varying(65535) | The source of the first authentication event for an issue. | ivr-url | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| auth_external_user_type | character varying(65535) | An external user type of the first authentication event for an issue. | ACME_CUSTOMER_ACCOUNT_ID | 2019-05-15 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| auth_external_user_id | character varying(65535) | External user ID provided by the client for the first authentication event. | 9BE62CCD564D6982FF305DEBCEAABBB5 | 2019-05-15 00:00:00 | 2019-07-16 00:00:00 | no | ||||||
| is_review_required | boolean | Flag indicates whether an admin must review this issue. data type: boolean | true, false | 2019-07-24 00:00:00 | 2019-07-24 00:00:00 | no | ||||||
| mid_issue_auth_ts | timestamp without time zone | Time when the user authenticates during the middle of an issue. | 2020-01-18T03:43:41.414000+00:00 | 2019-07-24 00:00:00 | 2019-07-24 00:00:00 | no | ||||||
| first_rep_id | varchar(191) | Identifier for the first rep involved with the issue. | 60001 | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | no | ||||||
| last_rep_id | varchar(191) | Identifier for the last rep involved with the issue. | 60001 | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | no | ||||||
| external_rep_id | varchar(255) | Client-provided identifier for the rep. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| first_voice_customer_state | varchar(255) | Initial state assigned to the customer when using voice. | IDENTIFIED, VERIFIED | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | no | ||||||
| first_voice_customer_state_ts | timestamp | Timestamp when the customer was first assigned a state. | 2020-01-18T03:43:41.414000+00:00 | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | no | ||||||
| first_voice_identified_customer_state_ts | timestamp | Time when the customer was first assigned an IDENTIFIED state. | 2020-01-18T03:43:41.414000+00:00 | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | no | ||||||
| first_voice_verified_customer_state_ts | timestamp | Time when the customer was first assigned an VERIFIED state. | 2020-01-18T03:43:41.414000+00:00 | 2019-11-21 00:00:00 | 2019-11-21 00:00:00 | no | ||||||
| merged_ts | timestamp | Time when the issue was merged into another issue. data type: timestamp | 2020-01-18T03:43:41.414000+00:00 | 2019-12-28 00:00:00 | 2019-12-28 00:00:00 | no | ||||||
| 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 | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | no | ||||||
| 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 | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | no | ||||||
| current_page_url | varchar(2000) | URL link (stripped of parameters) that triggered the start chat event. Only applicable for WEB platforms. aliases: trigger_link | https:www.acme.corp/billing/viewbill | 2020-04-25 00:00:00 | 2020-04-25 00:00:00 | no | ||||||
| raw_current_page_url | Full URL link (including parameters) that triggered the chat event. Only applicable for WEB platforms. aliases: raw_trigger_link | 2020-04-25 00:00:00 | 2020-04-25 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| first_utterance_ts | timestamp | Time of the first customer message in the conversation. | 2019-05-16T02:47:13+00:00 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-11-06 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| 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 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| device_type | varchar(255) | Last device type used by the customer for an issue. | mobile, tablet, desktop, watch, unknown | 2019-06-18 00:00:00 | 2019-06-18 00:00:00 | no | ||||||
| assisted | tinyint(1) | Flag indicates whether a rep was assigned and responded to the issue (1 if yes, 0 if no). | 0, 1 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| total_handle_time | double | Total time in seconds that reps spent handling the issue, from assignment to disposition. | 168.093 | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| total_lead_time | double | Total time in seconds the customer spent interacting during the conversation, from assignment to last utterance. | 163.222 | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| total_wrap_up_time | double | Total time in seconds spent by reps wrapping up the conversation, calculated as the difference between handle and lead time. | 4.871 | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| total_session_time | double | Total time the customer spent seeking resolution, including time in queue and up until the conversation end event. | 190.87900018692017 | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| customer_sent_msgs | double | The total number of messages sent by the customer, including typed and tapped messages | 1, 3, 5 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| agent_sent_msgs | deprecated: 2019-09-25 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| auto_generated_msgs | bigint(20) | The number of messages sent by the AI system. | 0,2 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| first_rep_response_count | bigint(20) | The number of first responses by reps, 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 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| total_seconds_to_first_rep_response | bigint(20) | Total time in seconds that passed before the rep responded to the customer. | 407.5679998397827 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| agent_response_count | deprecated: 2019-09-25 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| customer_response_count | bigint(20) | The total number of responses (excluding messages) sent by the customer. | 0, 4 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| total_rep_seconds_to_respond | double | Total time in seconds the rep took to respond to the customer. | 407.5679998397827 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| total_cust_seconds_to_respond | double | Total time in seconds the customer took to respond to the rep. | 65.87400007247925 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| time_in_queue | double | The cumulative time in seconds spent in queue, including all re-queues. | 78.30999994277954 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| auto_suggest_msgs | bigint(20) | Total time spent by the customer in the queue, including any re-queues. | 0, 1, 3 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| auto_complete_msgs | bigint(20) | The number of autocomplete messages sent by a rep. | 0, 1, 3 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| auto_wait_for_agent_msgs | bigint | deprecated: 2019-09-25 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | |||||||
| customer_wait_for_agent_msgs | bigint | deprecated: 2019-09-25 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | |||||||
| 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 | 2018-11-06 00:00:00 | 2019-07-26 00:00:00 | no | ||||||
| out_business_ct | bigint | The number of times that a customer received an out of business hours message. | 0, 2 | 2018-11-06 00:00:00 | 2019-04-23 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| rep_sent_msgs | bigint(20) | The number of messages a rep sent. | 0, 6, 7 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| 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 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| auto_wait_for_rep_msgs | bigint(20) | The number of times a user was asked to wait for a rep. | 0, 1, 2 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| customer_wait_for_rep_msgs | bigint(20) | The number of times a user asked to speak with a rep. | 0, 1 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| hold_ct | bigint | The number of times the customer was placed on hold. This applies to VOICE only. | 0, 1, 2 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| 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 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| first_utterance_ts | timestamp | Time of the first customer message in the conversation. | 2018-09-05 19:58:06 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| 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 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| device_type | varchar(255) | The last device type used by the customer. | mobile, tablet, desktop, watch, unknown | 2019-06-18 00:00:00 | 2019-06-18 00:00:00 | no | ||||||
| assisted | tinyint(1) | Flag indicates whether a rep was assigned and responded to the issue (1 if yes, 0 if no). | 0,1 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| total_handle_time | double | Total time in seconds that reps spent handling the issue, from assignment to disposition. | 718.968 | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| total_lead_time | double | Total time in seconds the customer spent interacting during the conversation, from assignment to last utterance. | 715.627 | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| total_wrap_up_time | double | Total time in seconds spent by reps wrapping up the conversation, calculated as the difference between handle and lead time. | 27.583 | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| total_session_time | double | Total time the customer spent seeking resolution, including time in queue and up until the conversation end event. | 1441.0329999923706 | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| customer_sent_msgs | double | The total number of messages sent by the customer, including typed and tapped messages | 2, 1 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| agent_sent_msgs | deprecated: 2019-09-25 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| auto_generated_msgs | bigint(20) | The number of messages sent by SRS. | 5, 3 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| first_rep_response_count | bigint(20) | The number of first responses by reps, 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 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| total_seconds_to_first_rep_response | bigint(20) | Total time in seconds that passed before the rep responded to the customer. | 4.291000127792358 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| agent_response_count | deprecated: 2019-09-25 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| customer_response_count | bigint(20) | The total number of responses (excluding messages) sent by the customer. | 3, 0, 8 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| total_rep_seconds_to_respond | double | Total time in seconds the rep took to respond to the customer. | 240.28499960899353 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| total_cust_seconds_to_respond | double | Total time in seconds the customer took to respond to the rep. | 227.27100014686584 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| time_in_queue | double | Total time spent by the customer in the queue, including any re-queues. | 71.74499988555908 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| auto_suggest_msgs | bigint(20) | The number of autosuggest messages sent by rep. | 0, 3, 4 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| auto_complete_msgs | bigint(20) | The number of autocomplete messages sent by rep. | 0, 1, 2 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| auto_wait_for_agent_msgs | bigint | deprecated: 2019-09-25 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | |||||||
| customer_wait_for_agent_msgs | bigint | deprecated: 2019-09-25 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | |||||||
| 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 | 2018-11-06 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| out_business_ct | bigint | The number of times that a customer received an out of business hours message. | 0, 1 | 2018-11-06 00:00:00 | 2019-04-23 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| rep_sent_msgs | bigint(20) | The number of messages a rep sent. | 0, 4, 7 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| rep_response_count | bigint(20) | The number of first responses by reps, 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, 20 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| auto_wait_for_rep_msgs | bigint(20) | The number of times a user was asked to wait for a rep. | 0, 3, 4 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| customer_wait_for_rep_msgs | bigint(20) | The number of times a user asked to speak with a rep. | 0, 1, 2 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| hold_ct | bigint | The number of times the customer was placed on hold. This field applies to VOICE. | 0, 1, 2 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| 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 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2019-11-01 00:00:00 | no |
Table: convos_summary_tags
The convos_summary_tags table contains information regarding all AI generated auto-summary tags populated by the system when a rep initiates the “end chat” disposition process.
Sync Time: 1h
Unique Condition: company_id, issue_id, summary_tag_presented
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| queue_id | integer | The identifier of the group to which the rep (who dispositioned the issue) belongs. | 20001 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| queue_name | varchar(255) | The name of the group to which the rep (who dispositioned the issue) belongs. | acme-mobile-english | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| 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 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| 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)‘ | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| summary_tag_selected_bool | boolean | Boolean field returns true if a rep selects the summary_tag_presented. | false, true | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| 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’ | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no |
Table: csid_containment
The csid_containment table tracks and organizes customer interactions by associating them with a unique session identifier (csid) with 30min window timeframe. It consolidates data related to customer sessions, including associated issue_ids, session durations, and indicators of containment success. Containment success measures whether an issue was resolved within a session without escalation. This table is critical for analyzing customer interaction patterns, evaluating the effectiveness of issue resolution processes, and identifying areas for improvement.
Sync Time: 1h
Unique Condition: csid, company_name
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| customer_id | bigint | The customer identifier on which this session is based, after merge if applicable. | 123008 | 2018-11-06 00:00:00 | 2018-11-07 00:00:00 | no | ||||||||
| external_customer_id | varchar(255) | The customer identifier as provided by the client. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| csid | varchar(255) | Unique identifier for a continuous period of activity for a given customer, starting at the specified timestamp. | ‘24790001_2018-09-24T22:17:41.341’ | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| csid_start_ts | timestamp without time zone | The start time of the customer’s session. | 2019-12-23T16:00:10.072000+00:00 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| csid_end_ts | timestamp without time zone | The end time of the active session. | 2019-12-23T16:00:10.072000+00:00 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| agents_involved | deprecated: 2019-09-25 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||||
| included_issues | character varying(65535) | Pipe-delimited list of issues involved in this period of customer activity. | ‘2044970001 | 2045000001 | 2045010001’ | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||
| is_contained | boolean | Flag indicating whether reps were involved with any issues during this csid. | true, false | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| event_count | bigint | The number of customer (only) events active during this csid. | 21 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| fgsrs_event_count | bigint | The number of FGSRS events during this csid. | 5 | 2019-08-30 00:00:00 | 2019-08-30 00:00:00 | no | ||||||||
| was_enqueued | boolean | Flag indicating if enqueued events existed for this session. | true, false | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| rep_msgs | bigint | Count of text messages sent by reps during this csid. | 6 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| messages_sent | bigint | Number of text messages typed or quick replies clicked by the customer during this csid. | 4 | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| has_customer_utterance | boolean | Flag indicating if the csid contains customer messages. | true, false | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| attempted_escalate | boolean | A boolean value indicating if the customer or flow tried (or succeeded) to reach a rep. | false, true | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| last_platform | VARCHAR(191) | Flag indicating if the customer or flow attempted or succeeded in reaching a rep. | ANDROID, WEB, IOS | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | ||||||||
| last_device_type | VARCHAR(191) | Last device type used by the customer | mobile, tablet, desktop, watch, unknown | 2019-06-18 00:00:00 | 2019-06-18 00:00:00 | no | ||||||||
| first_auth_source | character varying(65535) | First source of the authentication event for a csid. | ivr-url | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| last_auth_source | character varying(65535) | Last source of the authentication event for a csid. | ivr-url | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| distinct_auth_source_path | character varying(65535) | Comma-separated list of all distinct authentication event sources for the csid. | ivr-url, facebook | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| first_auth_external_user_type | character varying(65535) | The first external user type of the authentication event for a csid. | client_CUSTOMER_ACCOUNT_ID | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| last_auth_external_user_type | character varying(65535) | The last external user type of the authentication event for a csid. | client_CUSTOMER_ACCOUNT_ID | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| first_auth_external_user_id | character varying(65535) | Client-provided field for the first external user ID linked to an authentication event. | 64b0959a65a63dec32e1be04fe755be1 | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| last_auth_external_user_id | character varying(65535) | Client-provided field for the last external user ID linked to an authentication event. | 64b0959a65a63dec32e1be04fe755be1 | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| 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 | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| 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 | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| reps_involved | varchar(4096) | Pipe-delimited list of reps associated with any issues during this session. | ‘209000 | 2020001’ | 2018-11-06 00:00:00 | 2018-11-06 00:00:00 | no | |||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
Table: csid_containment_1d
The csid_containment table tracks and organizes customer interactions by associating them with a unique session identifier (csid) with 24 hours of window timeframe. It consolidates data related to customer sessions, including associated issue_ids, session durations, and indicators of containment success. Containment success measures whether an issue was resolved within a session without escalation. This table is critical for analyzing customer interaction patterns, evaluating the effectiveness of issue resolution processes, and identifying areas for improvement.
Sync Time: 1h
Unique Condition: csid, company_name
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| customer_id | bigint | The customer identifier on which this session is based, after merge if applicable. | 123008 | 2018-01-15 00:00:00 | 2018-11-07 00:00:00 | no | ||||||||
| external_customer_id | varchar(255) | The customer identifier as provided by the client. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| csid | varchar(255) | Unique identifier for a continuous period of activity for a given customer, starting at the specified timestamp. | ‘24790001_2018-09-24T22:17:41.341’ | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| csid_start_ts | timestamp without time zone | The start time of the customer’s session. | 2019-12-23T16:00:10.072000+00:00 | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| csid_end_ts | timestamp without time zone | The end time of the active session. | 2019-12-23T16:00:10.072000+00:00 | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| agents_involved | deprecated: 2019-09-25 | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||||
| included_issues | character varying(65535) | Pipe-delimited list of issues involved in this period of customer activity. | ‘2044970001 | 2045000001 | 2045010001’ | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||
| is_contained | boolean | Flag indicating whether reps were involved with any issues during this csid. | true, false | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| event_count | bigint | The number of customer (only) events active during this csid. | 21 | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| fgsrs_event_count | bigint | The number of FGSRS events during this csid. | 5 | 2019-08-30 00:00:00 | 2019-08-30 00:00:00 | no | ||||||||
| was_enqueued | boolean | Flag indicating if enqueued events existed for this session. | true, false | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| rep_msgs | bigint | Count of text messages sent by reps during this csid. | 6 | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| messages_sent | bigint | Number of text messages typed or quick replies clicked by the customer during this csid. | 4 | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| has_customer_utterance | boolean | Flag indicating if the csid contains customer messages. | true, false | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| attempted_escalate | boolean | A boolean value indicating if the customer or flow tried (or succeeded) to reach a rep. | false, true | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| last_platform | VARCHAR(191) | Flag indicating if the customer or flow attempted or succeeded in reaching a rep. | ANDROID, WEB, IOS | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | ||||||||
| last_device_type | VARCHAR(191) | Last device type used by the customer | mobile, tablet, desktop, watch, unknown | 2019-06-18 00:00:00 | 2019-06-18 00:00:00 | no | ||||||||
| first_auth_source | character varying(65535) | First source of the authentication event for a csid. | ivr-url | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| last_auth_source | character varying(65535) | Last source of the authentication event for a csid. | ivr-url | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| distinct_auth_source_path | character varying(65535) | Comma-separated list of all distinct authentication event sources for the csid. | ivr-url, facebook | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| first_auth_external_user_type | character varying(65535) | The first external user type of the authentication event for a csid. | client_CUSTOMER_ACCOUNT_ID | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| last_auth_external_user_type | character varying(65535) | The last external user type of the authentication event for a csid. | client_CUSTOMER_ACCOUNT_ID | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| first_auth_external_user_id | character varying(65535) | Client-provided field for the first external user ID linked to an authentication event. | 64b0959a65a63dec32e1be04fe755be1 | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| last_auth_external_user_id | character varying(65535) | Client-provided field for the last external user ID linked to an authentication event. | 64b0959a65a63dec32e1be04fe755be1 | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| 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 | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| 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 | 2019-05-15 00:00:00 | 2019-05-15 00:00:00 | no | ||||||||
| reps_involved | varchar(4096) | Pipe-delimited list of reps associated with any issues during this session. | ‘209000 | 2020001’ | 2018-01-15 00:00:00 | 2018-01-15 00:00:00 | no | |||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| last_agent_id | varchar(191) | deprecated: 2019-09-25 | 123008 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| question | character varying(65535) | Question presented to the user. | VOC Score, endSRS rating, What did the agent do well, or what could the agent have done better? (1000 character limit) | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| question_category | character varying(65535) | The question category type. | rating, comment, levelOfEffort | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| question_type | character varying(65535) | The type of question. | rating, scale, radio | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| answer | character varying(65535) | The customer’s answer to the question. | 0, 1, 17, yes | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| ordering | integer | The sequence or order of the question. | 0, 1, 3, 5 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| last_rep_id | varchar(191) | The last ASAPP rep/agent identifier found in a window of time. | 123008 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | no | ||||||
| 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 | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | no | ||||||
| feedback_type | character varying(65535) | The classification of feedback provided by the customer. | FEEDBACK_AGENT, etc | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | no | ||||||
| feedback_form_type | character varying(65535) | Indicates the type of feedback form completed by the customer. | ASAPP_CSAT, GBM | 2021-09-10 00:00:00 | 2021-09-10 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| event_ts | timestamp | The time at which this event was fired. | 2019-11-08 14:00:06.957000+00:00 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | The subdivision of the company. | ACMEsubcorp | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| company_segments | varchar(255) | The segments of the company. | marketing,promotions | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| rep_id | varchar(191) | deprecated: 2022-06-30 | 123008 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| referring_page_url | character varying(65535) | The URL of the page the user navigated from. | https://www.acme.com/wireless | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| event_id | character varying(256) | A unique identifier for the event within the customer parameter payload. | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | |||||||
| platform | varchar(255) | The platform the customer is using to interact with ASAPP. | 08679ded-38b7-11ea-9c44-debfe2011fef | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | ||||||
| session_id | varchar(128) | The websocket UUID associated with the current request’s session. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | ||||||
| auth_state | boolean | Flag indicating if the user is authenticated. | true, false | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | ||||||
| params | character varying(65535) | A string representation of the JSON parameters. | {"Key1":"Value1"; "Key2":"Value2"} | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| param_key | character varying(255) | A value of a specific key within the parameter JSON. | Key1 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| param_value | character varying(65535) | The value corresponding with the specific key in param_key. | Value1 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| current_page_url | varchar(2000) | The URL of the page where the customer initiated the ASAPP chat. | https://www.asapp.com | 2021-09-16 00:00:00 | 2021-09-16 00:00:00 | no |
Table: customer_params_hourly
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: 1h
Unique Condition: event_id, param_key
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| event_ts | timestamp | The time at which this event was fired. | 2019-11-08 14:00:06.957000+00:00 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | The subdivision of the company. | ACMEsubcorp | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| company_segments | varchar(255) | The segments of the company. | marketing,promotions | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| rep_id | varchar(191) | deprecated: 2022-06-30 | 123008 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| referring_page_url | character varying(65535) | The URL of the page the user navigated from. | https://www.acme.com/wireless | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| event_id | character varying(256) | A unique identifier for the event within the customer parameter payload. | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | |||||||
| platform | varchar(255) | The platform the customer is using to interact with ASAPP. | 08679ded-38b7-11ea-9c44-debfe2011fef | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | ||||||
| session_id | varchar(128) | The websocket UUID associated with the current request’s session. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | ||||||
| auth_state | boolean | Flag indicating if the user is authenticated. | true, false | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | ||||||
| params | character varying(65535) | A string representation of the JSON parameters. | {"Key1":"Value1"; "Key2":"Value2"} | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| param_key | character varying(255) | A value of a specific key within the parameter JSON. | Key1 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| param_value | character varying(65535) | The value corresponding with the specific key in param_key. | Value1 | 2019-01-25 00:00:00 | 2019-01-25 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| current_page_url | varchar(2000) | The URL of the page where the customer initiated the ASAPP chat. | https://www.asapp.com | 2021-09-16 00:00:00 | 2021-09-16 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2022-01-27 00:00:00 | 2022-01-27 00:00:00 | no | ||||||
| queue_key | bigint | Numeric primary key for dim queues | 100001 | 2022-01-27 00:00:00 | 2022-01-27 00:00:00 | no | ||||||
| queue_id | integer | The ASAPP queue identifier which the issue was placed. | 210001 | 2022-01-27 00:00:00 | 2022-01-27 00:00:00 | no | ||||||
| queue_name | varchar(255) | Name of the queue. | Voice | 2022-01-27 00:00:00 | 2022-01-27 00:00:00 | no |
Table: flow_completions
The purpose of this table is to list the flow success information, any negation data, and other associated metadata for all issues. This table provides insights into the success or failure of any issue. Flow Success refers to the successful completion of a predefined process or interaction flow without interruptions, errors, or escalations, as determined by specific business logic.
Sync Time: 1h
Unique Condition: company_id, issue_id, flow_name, flow_status_ts, success_event_details
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-11-14 00:00:00 | 2019-09-12 00:00:00 | no | no | |||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| platform | varchar(255) | The customer’s platform. | web, ios, android, applebiz, voice | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| external_user_id | varchar(255) | Client-provided identifier for customer, Available if the customer is authenticated. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| customer_session_id | character varying(65535) | The ASAPP application session identifier for this customer. | c5d7afcc-89b9-43cc-90e2-b869bb2be883 | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| success_rule_id | character varying(256) | The tag denoting whether the flow was successful within this issue. | LINK_RESOLVED, TOOLING_SUCCESS | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| success_event_details | character varying(65535) | Any additional metadata about this success rule. | asapp-pil://acme/grande-shop, EndSRSPositiveMessage | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| success_event_ts | timestamp without time zone | The time at which the flow success occurred. | 2019-12-03T01:43:17.079000+00:00 | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| negation_rule_id | character varying(256) | The tag denoting the last negation event that reverted a previous success. | TOOLING_NEGATION, NEG_QUESTION_NOT_ANSWERED | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| negation_event_ts | timestamp without time zone | The time at which this negation occurred. | 2019-12-03T01:49:19.875000+00:00 | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| is_flow_success_event | boolean | True if this event was not negated directly, false otherwise. | true, false | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| 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 | 2018-11-14 00:00:00 | 2018-11-14 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2019-11-01 00:00:00 | no | ||||||
| last_relevant_event_ts | Timestamp of the most recent relevant event (success or negation) detected for this issue, useful for deduplication. | 2020-01-02T19:13:27.698000+00:00 | 2019-12-10 00:00:00 | 2019-12-10 00:00:00 | no |
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 trhough a flow node by node.
Sync Time: 1h
Unique Condition: event_ts, issue_id, event_type
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| event_type | varchar(191) | The type of event within a given flow. | MESSAGE_DISPLAYED | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | no | |||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-08-14 00:00:00 | 2018-08-27 00:00:00 | no | 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 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| flow_id | varchar(255) | An ASAPP identifier assigned to a particular flow executed during a customer event or request. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| flow_name | varchar(255) | The ASAPP text name for a given flow which was executed during a customer event or request. | FirstChatMessage, AccountNumberFlow | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| event_name | character varying(65535) | The event name within a given flow. | FirstChatMessage, SuccessfulPaymentNode | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | no | |||||
| link_resolved_pil | character varying(65535) | An asapp internal URI for the link. | asapp-pil://acme/bill-history | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | no | |||||
| link_resolved_pdl | character varying(65535) | The resolved host deep link or web link. | https://www.acme.com/BillHistory | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | no | |||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| code | character varying(128) | The ASAPP internal code for a given intent. | ACCTNUM | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | no | no | |||||
| name | character varying(256) | The user-friendly name associated with an intent. | Get account number | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | no | no | |||||
| intent_type | character varying(128) | The hierarchical classification of this intent. | SYSTEM, LEAF, PARENT | 2018-07-26 00:00:00 | 2021-11-24 00:00:00 | no | no | |||||
| short_description | character varying(1024) | A short description for the intent code. | ‘Users asking to get their account number.’, ‘Television error codes.‘ | 2018-07-26 00:00:00 | 2019-02-12 00:00:00 | no | no | |||||
| flow_name | varchar(255) | The ASAPP flow code attached to this intent code. | AccountNumberFlow | 2018-12-13 00:00:00 | 2018-12-13 00:00:00 | no | 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 | 2018-12-13 00:00:00 | 2018-12-13 00:00:00 | no | no | |||||
| actions | character varying(4096) | Describes the type of action for the customer interface (e.g., “flow” for forms, “link” for URLs, or “text” for help content). An empty value indicates no specific action or automation. | flow, link, test, NULL | 2018-12-20 00:00:00 | 2018-12-20 00:00:00 | no | no | |||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2021-04-09 00:00:00 | no | ||||||
| deleted_ts | The date when this intent was removed. If blank or null, the intent is still active as of the export. An intent can be “undeleted” at a later date. | NULL, 2018-12-13 01:23:34 | 2021-11-23 00:00:00 | 2021-11-23 00:00:00 | no | 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 rate at which the same customer recontacts within a three day period. The issue_callback table is applicable only to specific clients.
Sync Time: 1h
Unique Condition: issue_id
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | ||||||
| issue_created_ts | timestamp | Timestamp when the issue ID is created. | 2018-09-05 19:58:06 | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | ||||||
| issue_disconnect_ts | timestamp without time zone | Timestamp when the issue ID is Disconnected. | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | |||||||
| issue_cutoff_ts | timestamp without time zone | The timestamp when the callback period expires for an issue. This is calculated as 3 days after the issue_disconnect_ts. | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | |||||||
| next_callback_issue_id | bigint | The ID of the next issue created by the same customer. This must occur between issue_disconnect_ts and issue_cutoff_ts. Null if no such issue exists. | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | |||||||
| next_callback_issue_created_ts | timestamp without time zone | Time when the next_callback_issue was created. | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | |||||||
| time_btwn_next_callback_issue_seconds | double precision | The duration in seconds between issue_disconnect_ts and next_callback_issue_created_ts | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | |||||||
| callback_prev_issue_id | bigint | The ID of any previous issue created by the same customer, provided it was disconnected within 3 days of the current issue’s create_ts. Null if no such issue exists. | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | |||||||
| callback_prev_issue_created_ts | timestamp without time zone | The timestamp when the callback_prev_issue was created. | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | |||||||
| callback_prev_issue_disconnect_ts | timestamp without time zone | The timestamp when the callback_prev_issue was disconnected. | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | |||||||
| time_btwn_callback_prev_issue_seconds | double precision | The duration in seconds between callback_prev_issue_disconnect_ts and issue_created_ts. | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no | |||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-14 00:00:00 | 2019-11-14 00:00:00 | no |
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: company_marker, issue_id
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| 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 | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| generative_agent_turns__turn_ct | int | Number of turns ( one cycle of interaction between Generative Agent and a user) | 1 | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| generative_agent_turns__turn_duration_ms_sum | bigint | Total duration in milliseconds between PROCESSING_START and PROCESSING_END across all turns. | 2 | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| generative_agent_turns__utterance_ct | int | Number of generative_agent utterances. | 2 | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| generative_agent_turns__contains_escalation | boolean | Indicates if any turn in the conversation resulted in an escalation to a human agent. | 1 | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| generative_agent_tasks__first_task_name | varchar(255) | Name of the first task initiated by the generative agent. | SomethingElse | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| generative_agent_tasks__last_task_name | varchar(255) | Name of the last task initiated by the generative agent. | SomethingElse | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| generative_agent_tasks__task_ct | int | Number of tasks entered by generative_agent. | 2 | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| generative_agent_tasks__configuration_id | varchar(255) | The configuration version responsible for the actions of the generative agent. | 4ea5b399-f969-49c6-8318-e2c39a98e817 | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | ||||||
| generative_agent_tasks__used_hila | Boolean representing if the conversation used a HILA escalation. True doesn’t guarantee that there was a HILA response in the conversation. | TRUE | 2024-11-08 00:00:00 | 2024-11-08 00:00:00 | no | genagent_tasks |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | ||||||
| issue_created_ts | timestamp | timestamp of the “NEW_ISSUE” event for an issue | 2018-09-05 19:58:06 | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | ||||||
| company_id | bigint | The ASAPP identifier of the company or test data source. | 10001 | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | ||||||
| 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 | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | |||||||
| treatment_type | varchar(64) | Indicates whether proactive messaging is configured to route the customer to an automated flow or a live agent. | QUEUE_PAUSED | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | ||||||
| rule_name | character varying(65535) | Name of the logical set of criteria met by the customer to trigger a proactive invitation or reactive button display. | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | |||||||
| is_new_conversation | boolean | Indicates whether the issue was created as a new conversation when the customer was not engaged in any ongoing or active issue. | 2019-11-15 00:00:00 | 2019-11-15 00:00:00 | no | |||||||
| is_new_user | boolean | Indicates if this is the first issue from the customer. | 2019-11-15 00:00:00 | 2019-11-15 00:00:00 | no | |||||||
| current_page_url | varchar(2000) | The URL of the page where the SDK was displayed. | https://www.asapp.com | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | ||||||
| referring_page_url | character varying(65535) | The URL of the page that directed the user to the current page. | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no | |||||||
| 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 | 2024-07-01 00:00:00 | 2021-06-01 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2024-07-01 00:00:00 | 2024-07-01 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2020-06-02 00:00:00 | 2020-06-02 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2020-06-02 00:00:00 | 2020-06-02 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2020-06-02 00:00:00 | 2020-06-02 00:00:00 | no | ||||||
| omni_source | character varying(191) | The source of the information. | ‘ABC’ | 2020-06-03 00:00:00 | 2020-06-03 00:00:00 | no | ||||||
| opaque_id | varchar(191) | deprecated: 2020-09-11 | ’urn:mbid:XXXXXX’ | 2020-06-03 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| 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’ | 2020-06-03 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| 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’ | 2020-06-03 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| first_utterance | character varying(191) | Captures the text of the first customer statement in an issue. | 2020-06-03 00:00:00 | 2020-06-03 00:00:00 | no | |||||||
| event_ts | timestamp | deprecated: 2020-09-11 | 2019-11-08 14:00:06.957000+00:00 | 2020-06-02 00:00:00 | 2020-06-02 00:00:00 | no | ||||||
| third_party_customer_id | character varying(65535) | An encrypted identifier which is permanently mapped to an ASAPP customer. | ‘urn:mbid:XXXXXX’ | 2020-07-23 00:00:00 | 2020-07-23 00:00:00 | no | ||||||
| external_context_1 | character varying(65535) | Provides traffic source or customer context from external platforms, including Apple Business Chat Group ID and Google Business Messaging Entry Point. | ‘credit_card_department’ | 2020-07-23 00:00:00 | 2020-07-23 00:00:00 | no | ||||||
| external_context_2 | character varying(65535) | Provides additional traffic source or customer context from external platforms, including Apple Business Chat Intent ID and Google Business Messaging Place ID. | ‘account_question’ | 2020-07-23 00:00:00 | 2020-07-23 00:00:00 | no | ||||||
| created_ts | timestamp | Timestamp at which the message was sent. | ‘2019-11-08T14:00:06.95700000:00’ | 2020-07-23 00:00:00 | 2020-07-23 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| enter_queue_ts | timestamp without time zone | Timestamp when the issue was added to the queue. | 2019-12-26T18:25:22.836000+00:00 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| exit_queue_ts | timestamp | Timestamp when the issue was removed from the queue. | 2019-12-26T18:25:28.552000+00:00 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| queue_id | integer | ASAPP queue identifier which the issue was placed. | 20001 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| queue_name | varchar(255) | Queue name which the issue was placed. | Acme Residential, Acme Wireless | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| abandoned | boolean | Flag indicating whether the issue was abandoned. | true, false | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| enqueue_time | double precision | Duration in seconds that the issue spent in the queue. | 5.716000080108643 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| exit_queue_eventtype | character varying(65535) | Reason the customer exited the queue. | CUSTOMER_TIMEDOUT, NEW_REP | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| enter_queue_eventtype | character varying(65535) | Reason the customer entered the queue. | TRANSFER_REQUESTED, SRS_HIER_AND_TREEWALK | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| enter_queue_eventflags | bigint | Event causing the issue to be enqueued. | (1=customer, 2=rep, 4=bot) | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| enter_queue_flow_name | character varying(65535) | Name of the flow which the issue was in before being enqueued. | LiveChatAgentsBusyFlow | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| enter_queue_message_name | character varying(65535) | Message name within the flow which the user was in before being enqueued. | someoneWillBeWithYou, shortWaitFormNode | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| exit_queue_eventflags | bigint | Event causing the issue to be deenqueued. | (1=customer, 2=rep, 4=bot) | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
Table: issue_sentiment
The issue_sentiment table captures sentiment analysis information related to customer issues. Each row represents an issue and its associated sentiment score or classification. This table helps track customer sentiment trends, assess the emotional tone of interactions, and support decision-making for issue resolution strategies.
Sync Time: 1d
Unique Condition: issue_id
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-07-26 00:00:00 | 2018-09-29 00:00:00 | no | ||||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | no | ||||||
| score | double precision | The sentiment score applied to this issue. | 0.5545974373817444, -1000.0 | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | no | ||||||
| status | character varying(65535) | Reason for the sentiment score, which may be NULL | CONVERSATION_TOO_SHORT | 2018-07-26 00:00:00 | 2018-07-26 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | no | ||||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | no | ||||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | 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. | ‘guid:2348001002-0032128785-2172846080-0001197432’ | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | no | ||||||
| issue_created_ts | timestamp | Timestamp this issue_id was created. | 2018-09-05 19:58:06 | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | no | ||||||
| first_issue_id | bigint | The first issue_id for this session. | 21352352 | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | no | ||||||
| first_issue_created_ts | timestamp | Timestamp when the NEW_ISSUE event occurred for the first issue_id associated with this session. | 2018-09-05 19:58:06 | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | no | ||||||
| last_issue_id | bigint | The last issue_id associated with this session. | 21352352 | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | no | ||||||
| last_issue_created_ts | timestamp | Timestamp when the NEW_ISSUE event occurred for the last issue_id associated with this session | 2018-09-05 19:58:06 | 2020-02-05 00:00:00 | 2020-02-05 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| prechat_survey_ts | timestamp without time zone | Timestamp when the pre-chat survey was completed to route the issue to an expert. | 2019-08-07 19:34:18.844 | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| 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 | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| queue_id | integer | The unique identifier for the queue to which the issue was routed. | 20001 | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| issue_type | character varying(65535) | Current type of the issue (question or escalation). | ESCALATION | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| initial_type | character varying(65535) | Original type of the issue when it was created. | QUESTION | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| subsidiary_name | character varying(65535) | Name of the company to which this issue is associated. | ACMEsubsid | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| channel_type | character varying(65535) | Indicates the channel (voice or chat) if the issue started as ESCALATION, or null otherwise. | CALL | 2019-08-12 00:00:00 | 2019-08-12 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| article_id | character varying(65535) | The knowledge base identifier for the article. | 5, 16580001 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| interaction | character varying(8) | An indicator of whether the article was viewed or attached to a chat. | ‘Viewed’, ‘Attached’ | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| is_favorited | boolean | Indicates whether the article is marked as a favorite. | TRUE, FALSE | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| event_ts | timestamp | The time of an given event. All times are in UTC. | 2019-11-08 14:00:06.957000+00:00 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| 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 | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| event_name | character varying(191) | A string that determines if the action comes from an Interaction event or a Recommendation event | ’INTERACTION’, ‘SUGGESTION’ | 2019-12-20 00:00:00 | 2019-12-20 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2020-03-30 00:00:00 | 2020-03-30 00:00:00 | no | ||||||
| rep_assigned_ts | timestamp without time zone | timestamp of the NEW_REP event | 2020-10-15 00:00:00 | 2020-10-15 00:00:00 | no | |||||||
| 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’ | 2020-10-15 00:00:00 | 2020-10-15 00:00:00 | no | ||||||
| discovery_type | character varying(256) | How article was presented/discovered. (recommendation, quick_access_kbr, favorite, search, filebrowser) | recommendation | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | no | ||||||
| position | integer | Position of article recommendation when multiple recommendations are presented. Default is 1 when a single recommendation is presented. | 1 | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | no | ||||||
| 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’ | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | no | ||||||
| article_name | Short description of the article. | 500 | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | no | |||||||
| is_paperclip_enabled | Flag which indicates whether the article is paper clipped (Bookmark). | TRUE | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | no | |||||||
| external_article_id | Identifier for external article id. | 4567 | 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | no |
Table: live_agent_opportunities
The live_agent_opportunities table tracks instances where automated processes, such as chatbots or virtual assistants, escalate a conversation or issue to a live agent. It offers insights into the effectiveness of automation, the reasons behind escalations, and key metrics for improving both customer experience and agent performance. The term “Opportunity” refers to the period from when the conversation is handed over to an agent until its closure.
Sync Time: 1h
Unique Condition: issue_id, customer_id, opportunity_ts
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| rep_id | varchar(191) | The identifier of the rep this opportunity was assigned to or null if it was never assigned. | 123008 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| opportunity_ts | timestamp | Timestamp of the opportunity event. | 2020-01-06 23:13:50.617 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| device_type | varchar(255) | Last device type used by the customer. | mobile, tablet, desktop, watch, unknown | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| first_opportunity | boolean | Indicator of whether this is the first opportunity for this issue. | true, false | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| triggered_when_busy | boolean | Indicator of whether the customer was asked if they wanted to wait for an agent. | true | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| triggered_outside_hours | boolean | Indicator of whether the customer was told they are outside of business hours. | false | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| queue_id | integer | Identifier of the agent group this opportunity will be routed to. | 2001 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| queue_name | varchar(255) | Name of the queue this opportunity will be routed to. | Residential | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| intent_code | character varying(128) | The most recent intent code used for routing this issue. | SALESFAQ, BILLINFO | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| flow_name | varchar(255) | The flow associated with the routing intent, if any. | ForceChatFlow | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| by_end_srs | boolean | Indicator of whether this opportunity occurred because of a negative end srs response. | true, false | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| by_api_error | boolean | Indicator of whether this opportunity occurred because of an error in partner API. | true, false | 2019-10-21 00:00:00 | 2019-10-21 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| exit_queue_ts | timestamp | Time at which the customer exited the queue. | 2020-01-06 23:13:50.617 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| abandoned_ts | TIMESTAMP | The datetime when the customer abandoned the queue. | 2020-01-06 23:13:50.617 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| assigned_ts | timestamp | Timestamp when the opportunity was assigned to a representative; null if it was never assigned. | 2020-01-03T18:54:45.140000+00:00 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| escalation_initiated_ts | timestamp | The lesser of enqueued and assigned time, null if never escalated. | 2020-01-06 23:13:50.617 | 2019-06-04 00:00:00 | 2019-06-04 00:00:00 | no | ||||||
| rep_first_response_ts | TIMESTAMP | The time when a rep first responded to the customer. | 2020-01-06 23:13:50.617 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| disposition_event_type | varchar(255) | Event type indicating how the conversation ended. | resolved, timedout | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| cust_utterance_count | bigint | Count of customer utterances from issue_assigned_ts to dispositioned_ts | 4 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| rep_utterance_count | bigint | Count of rep utterances from issue_assigned_ts to dispositioned_ts | 5 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| cust_response_ct | int | Total count of responses by customer. Max of one message following a rep message counted as a response. | 3 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| rep_response_ct | int | Total count of responses by agent. Max of one message following a customer message counted as a response. | 10 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| is_ghost_customer | boolean | True if the customer was assigned to a rep but never responded to the rep. | true, false | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| handle_time_seconds | double precision | Time in seconds spent an agent working on a particular assignment. Time between assignment and disposition event | 824.211 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| 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 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| is_transfer | boolean | Indicator whether this opportunity is due to a transfer. | true, false | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| is_reengagement | boolean | Indicator whether this opportunity is due to the user returning from a timeout. | true, false | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| is_conversation_initiation | boolean | Indicator of whether this opportunity is from a conversation initiation (i.e. not from transfer or reengagement). | true, false | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| from_queue_id | bigint | The identifier of the group from which the issue was transferred. | 30001 | 2019-12-18 00:00:00 | 2019-12-18 00:00:00 | no | ||||||
| from_queue_name | character varying(191) | The name of the group from which the issue was transferred. | service, General | 2019-12-18 00:00:00 | 2019-12-18 00:00:00 | no | ||||||
| from_rep_id | bigint | The identifier of the rep from which the issue was transferred. | 81001 | 2019-12-18 00:00:00 | 2019-12-18 00:00:00 | no | ||||||
| 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 | 2020-01-14 00:00:00 | 2020-01-14 00:00:00 | no | ||||||
| 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 | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | no | ||||||
| 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 | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | no | ||||||
| merged_from_issue_id | bigint | The issue id before the merge | 21352352 | 2020-06-30 00:00:00 | 2020-06-30 00:00:00 | no | ||||||
| merged_ts | timestamp | the time the merge occurred | 2019-11-08T14:00:06.957000+00:00 | 2020-06-30 00:00:00 | 2020-06-30 00:00:00 | no | ||||||
| exclusive_phrase_auto_complete_msgs | bigint | Count of utterances where at least one phrase autocomplete was accepted/sent and no other augmentation was used. | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | no | |||||||
| autopilot_ending_msgs_ct | integer | Number of autopilot endings | 2 | 2024-04-19 00:00:00 | 2024-04-19 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | no | ||||||
| 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 | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | no | ||||||
| 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 | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | no | ||||||
| wait_time_threshold_ts | timestamp without time zone | Timestamp at which the queue wait time threshold was reached. | 2018-06-10 14:22:58 | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | no | ||||||
| check_in_result | character varying(9) | The result of the check in message, either the customer ‘Accepted’ or was ‘Dequeued’. | ‘Dequeued’ | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | no | ||||||
| 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 | 2020-01-02 00:00:00 | 2020-04-24 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-03-23 00:00:00 | 2019-03-23 00:00:00 | no | ||||||
| wait_time_threshold_ct_distinct | bigint | Quantity of times the queue wait time threshold was reached before getting the check in message. | 2 | 2020-04-25 00:00:00 | 2020-04-25 00:00:00 | no | ||||||
| queue_id | integer | The ASAPP queue identifier which the issue was placed. | 20001 | 2020-06-11 00:00:00 | 2020-06-11 00:00:00 | no | ||||||
| queue_name | varchar(255) | The queue name which the issue was placed. | Acme Residential, Acme Wireless | 2020-06-11 00:00:00 | 2020-06-11 00:00:00 | no | ||||||
| opportunity_ts | timestamp | Timestamp of the opportunity event | 2023-01-02 19:58:06 | 2020-01-02 00:00:00 | 2020-01-02 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| final_intent_code | character varying(255) | The last intent code of the flow which the user navigated. | PAYBILL | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| escalated_to_chat | bigint | 1 if an issue escalated to live chat, 0 if not. | 1 | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| escalation_requested | integer | 1 if customer was asked to wait for an agent or if a customer asked to speak to an agent. | 1 | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| quick_reply_button_text | character varying(65535) | The text of the quick reply button. | ‘Billing’ | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| quick_reply_button_index | integer | The position of the quick reply button shown. | (1,2,3) | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| quick_reply_displayed_count | bigint | The number of times this button was shown. | 42 | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| quick_reply_selected_count | bigint | The number of times this button was selected. | 42 | 2019-02-12 00:00:00 | 2019-02-12 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| created_ts | timestamp | The timestamp of when record gets generated. | 2019-02-19T21:31:43+00:00 | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | no | ||||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | no | ||||||
| crm_agent_id | varchar(255) | deprecated: 2019-09-25 | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | no | ||||||
| name | varchar(255) | The rep name as imported from the CRM. | Smith, Anne | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | no | ||||||
| max_slot | smallint | The number of slots or concurrent conversations this rep can have at the same time. | 4 | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | no | ||||||
| disabled_time | timestamp without time zone | The time when this rep was removed from the ASAPP system. | 2019-02-27T12:56:34+00:00 | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | no | ||||||
| agent_status | deprecated: 2019-09-25 | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | no | ||||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2018-09-21 00:00:00 | 2018-09-21 00:00:00 | no | ||||||
| crm_rep_id | The rep identifer from the client system. | monica.rosa | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | no | |||||||
| rep_status | varchar(255) | The last known status of the rep at UTC midnight. | 80001 | 2019-09-26 00:00:00 | 2019-09-26 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||
| 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 | 2019-12-16 00:00:00 | 2019-12-16 00:00:00 | no | ||||||
| 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 | 2019-12-16 00:00:00 | 2019-12-16 00:00:00 | no | |||||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | The company subdivision relates to the customer issue and is not relevant to reps. Intentionally left blank. | ACMEsubcorp | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||
| company_segments | varchar(255) | The company segments field relates to the customer issue and is not relevant to reps. Intentionally left blank. | marketing,promotions | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||
| agent_name | deprecated: 2019-09-25 | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||||
| status_id | character varying(65535) | The ASAPP identifier for a given status. | OFFLINE, 1 | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||
| status_description | character varying(65535) | The human text name for a given status. | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | |||||||
| orig_status_description | character varying(191) | The text of the status before alteration for disconnects. | Available, Away, Coffee Break, Active | 2020-01-07 00:00:00 | 2020-01-07 00:00:00 | no | ||||||
| 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 | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||
| 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 | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| 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 | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| 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 | 2019-03-05 00:00:00 | 2019-03-05 00:00:00 | no | ||||||
| window_status_time | double precision | The length of time which the agent was inside this status in seconds. | 0.107, 900.0 | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||
| 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 | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||
| max_slots | integer | The number of issue slots or concurrency values which the rep set for themselves for this window. | 3, 2 | 2018-10-01 00:00:00 | 2018-10-01 00:00:00 | no | ||||||
| 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 | 2020-01-07 00:00:00 | 2020-01-07 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| rep_name | varchar(191) | The name of this rep. Jane Doe | John | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| desk_mode | varchar(191) | The mode of the desktop which the agent is logged into. Modes include CHAT or VOICE. | ‘CHAT’, ‘VOICE’ | 2019-12-10 00:00:00 | 2019-12-10 00:00:00 | no | ||||||
| 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 | 2024-05-29 00:00:00 | 2024-05-29 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | ||||||
| rep_assigned_ts | timestamp without time zone | The timestamp at which the issue was assigned to the rep. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| disposition_event | character varying(65535) | The event type associated with the disposition event | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| disposition_notes_txt | character varying(65535) | Disposition notes associated with the disposition event | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| disposition_notes_valid | boolean | Boolean value to indicate if the notes are different than blank or null. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| crm_offered_ts | timestamp without time zone | Timestamp of the last CRM offered event. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| crm_outcome_ts | timestamp without time zone | Timestamp of the last CRM outcome event. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| crm_is_success | boolean | Boolean value to indicate if the disposition event is successfully sent to partner CRM | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| crm_error_type | character varying(65535) | This field indicates the type of an error occured in the pipeline. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| crm_error_source | character varying(65535) | This field indicates where in the pipeline the event is failed to publish. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| presented_tags | character varying(65535) | Unique list of all summary tags presented to agent for this assignment. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| selected_tags | character varying(65535) | Unique list of all summary tags selected by agent for this assignment. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| notes_presented_tags | character varying(65535) | Unique list of the summary tags presented to agent at the OTF NOTES state for this assignment. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| notes_selected_tags | character varying(65535) | Unique list of the summary tags selected by agent at the OTF NOTES state for this assignment. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| assignment_end_presented_tags | character varying(65535) | Unique list of the summary tags presented to agent at the end of assignment state. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| assignment_end_selected_tags | character varying(65535) | Unique list of the summary tags selected by agent at the end of assignment state. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| presented_tags_ct_distinct | bigint | Distinct count of all summary tags presented to agent for this assignment. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| selected_tags_ct_distinct | bigint | Distinct count of all summary tags selected by agent for this assignment. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| notes_presented_tags_ct_distinct | bigint | Distinct count of the summary tags presented to agent at the OTF NOTES state for this assignment. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| notes_selected_tags_ct_distinct | bigint | Distinct count of the summary tags selected by agent at the OTF NOTES state for this assignment. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| assignment_end_presented_tags_ct_distinct | bigint | Distinct count of the summary tags presented to agent at the end of assignment state. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| assignment_end_selected_tags_ct_distinct | bigint | Distinct count of the summary tags selected by agent at the end of assignment state. | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | |||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2020-09-03 00:00:00 | 2020-09-03 00:00:00 | no | ||||||
| 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. | 2023-02-16 00:00:00 | 2023-02-16 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| created_ts | timestamp | The date this agent was created. | 2019-06-24T18:02:05+00:00 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| attribute_name | character varying(64) | The attribute key value. | role, companygroup, jobcode | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| attribute_value | character varying(1024) | The attribute value associated with the attribute_name. | manager, representative, lead | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| agent_attribute_id | deprecated: 2019-09-25 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||||
| external_agent_id | varchar(255) | deprecated: 2019-09-25 | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| rep_attribute_id | bigint | The ASAPP identifier for this attribute. | 1200001 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| external_rep_id | varchar(255) | Client-provided identifier for the rep. | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| customer_id | bigint | The ASAPP internal customer identifier. | 123008 | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| external_customer_id | varchar(255) | The customer identifier as provided by the client. | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | |||||||
| conversation_end_ts | timestamp | Timestamp when the conversation ended. | 2018-06-23 21:23:53 | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| auto_suggest_msgs | bigint | The number of autosuggest prompts used by the rep. | 3 | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| auto_complete_msgs | bigint | The number of autocompletion prompts used by the rep. | 2 | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| did_customer_timeout | boolean | Boolean value indicating whether the customer timed out. | false, true | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| is_rep_resolved | boolean | Boolean value indicating whether the rep marked this conversation resolved. | true, false | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| is_billable | boolean | Boolean value indicating whether the rep marked the conversation resolved after using autocomplete or autosuggest. | true, false | 2018-11-27 00:00:00 | 2018-11-27 00:00:00 | no | ||||||
| custom_auto_suggest_msgs | bigint | The number of custom autocompletion prompts used by the rep (is a subset of auto_suggest_msgs). | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| custom_auto_complete_msgs | bigint | The number of custom autosuggest prompts used by the rep (is a subset of auto_complete_msgs). | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| drawer_msgs | bigint | The number of custom drawer messages used by the rep. | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| kb_search_msgs | bigint | The number of messages used from knowledge base search. | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| kb_recommendation_msgs | bigint | The number of messages used from knowledge base recommendations. | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| rep_id | varchar(191) | Last rep_id that worked on this issue. | 123008 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| is_autopilot_timeout_msgs | Number of autopilot timeout messages. | 2 | 2020-06-11 00:00:00 | 2020-06-11 00:00:00 | no | |||||||
| phrase_auto_complete_presented_msgs | integer | Count of utterances where at least one phrase autocomplete was suggested/presented. | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | no | |||||||
| cume_phrase_auto_complete_presented | integer | Total number of phrase autocomplete suggestions per issue. | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | no | |||||||
| phrase_auto_complete_msgs | integer | Count of utterances where at least one phrase autocomplete was accepted/sent. | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | no | |||||||
| cume_phrase_auto_complete | integer | Total number of phrase autocompletes per issue. | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | no | |||||||
| exclusive_phrase_auto_complete_msgs | integer | Count of utterances where at least one phrase autocomplete was accepted/sent and no other augmentation was used. | 2020-06-24 00:00:00 | 2020-06-24 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| 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 | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| agent_first_response_ts | deprecated: 2019-09-25 | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||||
| dispositioned_ts | timestamp | The time when the issue left the rep’s screen. | 2019-10-31T18:46:39.869000+00:00 | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| 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 | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| disposition_event_type | varchar(255) | Event type indicating how the conversation ended. | rep, customer, batch (system/auto ended), batch | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| cust_utterance_count | bigint | The count of customer utterances from issue_assigned_ts to dispositioned_ts. | 5 | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| rep_utterance_count | bigint | The count of rep utterances from issue_assigned_ts to dispositioned_ts. | 5 | 2018-09-01 00:00:00 | 2018-09-01 00:00:00 | no | ||||||
| handle_time_seconds | double precision | Total time in seconds that reps spent handling the issue, from assignment to disposition. | 428.9 | 2019-03-19 00:00:00 | 2019-03-20 00:00:00 | no | ||||||
| lead_time_seconds | double precision | Total time in seconds the customer spent interacting during the conversation, from assignment to last utterance. | 320.05 | 2019-03-19 00:00:00 | 2019-03-20 00:00:00 | no | ||||||
| wrap_up_time_seconds | double precision | Total time in seconds spent by reps wrapping up the conversation, calculated as the difference between handle and lead time. | 3.614 | 2019-03-19 00:00:00 | 2019-03-20 00:00:00 | no | ||||||
| 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 | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| 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 | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| auto_suggest_msgs | bigint | The number of autosuggest prompts used by the rep (inclusive of custom_auto_suggest_msgs). | 5 | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | ||||||
| auto_complete_msgs | bigint | The number of autocompletion prompts used by the rep (inclusive of custom_auto_complete_msgs). | 5 | 2019-07-29 00:00:00 | 2019-07-29 00:00:00 | no | ||||||
| custom_auto_suggest_msgs | bigint | The number of custom autocompletion prompts used by the rep (is a subset of auto_suggest_msgs). | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| custom_auto_complete_msgs | bigint | The number of custom autosuggest prompts used by the rep (is a subset of auto_complete_msgs). | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| drawer_msgs | bigint | The number of custom drawer messages used by the rep. | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| kb_search_msgs | bigint | The number of messages used by the rep from the knowledge base searches. | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| kb_recommendation_msgs | bigint | The number of messages used by the rep from the knowledge base recommendations. | 2 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | no | ||||||
| is_ghost_customer | boolean | Boolean value indicating if the customer was assigned a rep but never responded. | true, false | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| first_response_seconds | bigint | The total time taken by the rep to send the first message, once the message was assigned. | 26.148 | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| cume_rep_response_seconds | bigint | The total time across the assignment for the rep to send response messages. | 53.243 | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| max_rep_response_seconds | double precision | The maximum time across the assignment for the rep to send a response message. | 77.965 | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| avg_rep_response_seconds | double precision | The average time across assignment for the rep to send response messages. | 22.359 | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| cume_cust_response_seconds | bigint | The total time across the assignment for the customer to send response messages. | 332.96 | 2019-05-17 00:00:00 | 2019-05-17 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| rep_first_response_ts | datetime | The time when a rep first responded to the customer. | 2019-10-31T18:38:03.996000+00:00 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| hold_ct | bigint | The total count that this rep was part of a hold call. This field is not applicable to chat. | 1 | 2019-11-19 00:00:00 | 2019-11-19 00:00:00 | no | ||||||
| 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 | 2019-11-19 00:00:00 | 2019-11-19 00:00:00 | no | |||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| client_mode | varchar(191) | The communication mode used by the customer for a given issue (CHAT or VOICE). | CHAT, VOICE | 2019-12-10 00:00:00 | 2019-12-10 00:00:00 | no | ||||||
| cume_cross_talk_seconds | numeric(38,5) | Total duration of time where both agent and customer were speaking. Only relevant for voice client mode. | 2019-12-28 00:00:00 | 2019-12-28 00:00:00 | no | |||||||
| 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 | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | no | ||||||
| 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 | 2020-02-19 00:00:00 | 2020-02-19 00:00:00 | no | ||||||
| queue_id | integer | The ASAPP queue identifier which the issue was placed. | 20001 | 2021-04-08 00:00:00 | 2021-04-08 00:00:00 | no | ||||||
| autopilot_timeout_msgs | integer | Number of autopilot timeout messages. | 2 | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | no | ||||||
| exclusive_phrase_auto_complete_msgs | integer | Count of utterances where at least one phrase autocomplete was accepted/sent and no other augmentation was used. | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | no | |||||||
| custom_click_to_insert_msgs | integer | Total count of custom click_to_insert messages. | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | no | |||||||
| ms_auto_suggest_msgs | integer | Total count of multi-sentence auto-suggest messages. | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | no | |||||||
| ms_auto_complete_msgs | integer | Total count of multi-sentence auto-complete messages. | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | no | |||||||
| ms_auto_suggest_custom_msgs | integer | Total count of custom multi-sentence auto-suggest messages. | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | no | |||||||
| ms_auto_complete_custom_msgs | integer | Total count of custom multi-sentence auto-complete messages. | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | no | |||||||
| autopilot_form_msgs | bigint | Number of autopilot form messages. | 2 | 2021-08-02 00:00:00 | 2021-08-02 00:00:00 | no | ||||||
| click_to_insert_global_msgs | integer | Number of click to insert messages. | 2 | 2023-02-15 00:00:00 | 2023-02-15 00:00:00 | no | ||||||
| autopilot_greeting_msgs | bigint | Number of autopilot greeting messages. | 2 | 2023-02-15 00:00:00 | 2023-02-15 00:00:00 | no | ||||||
| augmented_msgs | bigint | Number of augmented messages. | 2 | 2023-02-22 00:00:00 | 2023-02-22 00:00:00 | no | ||||||
| autopilot_ending_msgs_ct | integer | Number of autopilot endings | 2 | 2024-04-19 00:00:00 | 2024-04-19 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| subordinate_agent_id | deprecated: 2019-09-25 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||||
| superior_agent_id | deprecated: 2019-09-25 | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||||
| reporting_relationship | character varying(1024) | Relationship between subordinate and superior reps, e.g. “superiors_superior” for skip-level reporting. | superior, superiors_superior | 2018-08-14 00:00:00 | 2018-08-14 00:00:00 | no | ||||||
| subordinate_rep_id | bigint | ASAPP rep identifier that is the subordinate of the superior_rep_id. | 110001 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| superior_rep_id | bigint | Superior rep id that is the superior of the subordinate_rep_id. | 20001 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no |
Table: rep_utilized
The rep_utilized table tracks a rep’s activity and how much time they spend in each state. It shows utilization time and total minutes per state, recorded in 15-minute intervals throughout the day. The instance_ts field represents the 15-minute window and is part of the primary key. It doesn’t show the most recent event like in other tables. The data may be updated if later information changes it, such as adding more utilization time. Utilization refers to the rep’s efficiency.
Sync Time: 1h
Unique Condition: instance_ts, rep_id, desk_mode, max_slots
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2020-01-29 00:00:00 | 2020-01-29 00:00:00 | no | |||||||
| 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 | 2020-01-29 00:00:00 | 2020-01-29 00:00:00 | no | |||||||
| company_id | bigint | The ASAPP identifier of the company or test data source. | 10001 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | Relates to the customer issue, not relevant to reps. Intentionally left blank. | ACMEsubcorp | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| company_segments | varchar(255) | Relates to the customer issue, not relevant to reps. Intentionally left blank. | marketing,promotions | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| rep_name | varchar(191) | The name of the rep. | John Doe | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| max_slots | integer | Maximum chat concurrency slots enabled for this rep. | 2 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| lin_logged_in_min | bigint | Linear Logged In Time (min) — Total linear time rep logged into agent desktop. | 60 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| lin_avail_min | bigint | Linear Available Time (min) — Total linear time the rep logged into the agent desktop while in the “Available” state. | 45 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| lin_busy_min | bigint | Linear Busy Time (min) — Total linear time rep logged into agent desktop while in a “Busy” state. | 15 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| lin_ute_total_min | bigint | Linear Utilized Time (min) — Total linear time rep logged into agent desktop and utilized over all states. | 5.5 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| 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 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| busy_clicks_ct | bigint | Busy Clicks — Number of times the rep moved from an active to a busy state. | 1 | 2019-05-10 00:00:00 | 2019-05-10 00:00:00 | no | ||||||
| ute_ratio | Utilization ratio - cumulative utilized time divided by linear total potential labor time. | 1.71 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | |||||||
| act_ratio | Active utilization ratio - cumulative utilized time in the available state divided by total labor time. | 1.67 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | |||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| desk_mode | varchar(191) | The mode of the desktop that the agent is logged into - whether CHAT or VOICE. | ‘CHAT’, ‘VOICE’ | 2019-12-10 00:00:00 | 2019-12-10 00:00:00 | no | ||||||
| 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 | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| lin_utilization_level_full_min | bigint | Total linear time in minute when rep’s assignments is equal to rep’s max slot | 120 | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| lin_utilization_level_light_min | bigint | Total linear time in minute when rep’s assignments is less than rep’s max slot | 120 | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| workload_level_no_min | bigint | Total time in minute when rep has no active assignment | 120 | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| workload_level_over_min | bigint | Total time in minute when rep’s active assignment is greater than rep’s max slot | 120 | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| workload_level_full_min | bigint | Total time in minute when rep’s active assignment is equal to rep’s max slot | 120 | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| workload_level_light_min | bigint | Total time in minute when rep’s active assignment is less than rep’s max slot | 120 | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| flex_protect_min | bigint | Total time in minute when rep is flex protected | 120 | 2020-11-09 00:00:00 | 2020-11-09 00:00:00 | no | ||||||
| cum_weighted_min | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | |||||||||
| cum_weighted_seconds | bigint | Total effort_workload when a rep has active assignments | 10 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| cum_ute_weighted_avail_unflexed_seconds | bigint | Total time weighted in seconds when a rep is available | 160 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no | ||||||
| cum_weighted_inactive_seconds | bigint | Total effort_workload when a rep has no active assignments | 10 | 2019-03-11 00:00:00 | 2019-03-11 00:00:00 | no |
Table: sms_events
Exports for each 15 min window of SMS flow events
Sync Time: 1h
Unique Condition: company_id, sms_flow_id
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | Ignore | PII | release state | Specific Use | Feature Group |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| sms_flow_id | character varying(65535) | The flow identifier. | 019bf9e4-a01a-4420-b419-459659a1b50e | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| external_session_id | character varying(65535) | The session identifier received from the client. | 772766038 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| message_sent_result | character varying(6) | The status of a SMS request received from the 3rd party SMS provider. | ‘Sent’ | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| message_sent_result_status_code | character varying(65535) | The failure reason received from 3rd party SMS provider. | 30001 (Queue Overflow), 30004 (Message Blocked) | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| message_character_count | integer | The SMS message’s character count. | 29 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| 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 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| 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 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| 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 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| company_id | bigint | The ASAPP identifier of the company or test data source. | 10001 | 2019-11-08 00:00:00 | 2019-11-08 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-08 00:00:00 | 2020-03-23 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||
| timestamp_req | timestamp without time zone | The date and time when the transfer was requested. | 2019-06-11T13:27:09.470000+00:00 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||
| timestamp_reply | timestamp without time zone | The date and time when the transfer request was received. | 2019-06-11T13:31:58.537000+00:00 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||
| agent_id | bigint | deprecated: 2019-09-25 | 123008 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2018-08-04 00:00:00 | 2018-08-04 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-10-04 00:00:00 | 2018-10-04 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-10-04 00:00:00 | 2018-10-04 00:00:00 | no | ||||||
| requested_agent_transfer | deprecated: 2019-09-25 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||||
| group_transfer_to | character varying(65535) | The group identifier where the issue was transferred. | 20001 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||
| group_transfer_to_name | character varying(191) | The group name where the issue was transferred. | acme-mobile-eng | 2018-08-04 00:00:00 | 2018-08-04 00:00:00 | no | ||||||
| group_transfer_from | character varying(65535) | The group identifier which transferred the issue. | 87001 | 2018-08-04 00:00:00 | 2018-08-04 00:00:00 | no | ||||||
| group_transfer_from_name | character varying(191) | The group name which transferred the issue. acme-residential-eng | 2018-08-04 00:00:00 | 2018-08-04 00:00:00 | no | |||||||
| actual_agent_transfer | deprecated: 2019-09-25 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||||
| accepted | boolean | A boolean flag indicating whether the transfer was accepted. | true, false | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||
| is_auto_transfer | boolean | A boolean flag indicating whether this was an auto-transfer. | true, false | 2019-07-22 00:00:00 | 2019-07-22 00:00:00 | no | ||||||
| exit_transfer_event_type | character varying(65535) | The event type which concluded the transfer. | TRANSFER_ACCEPTED, CONVERSATION_END | 2019-07-22 00:00:00 | 2019-07-22 00:00:00 | no | ||||||
| transfer_button_clicks | bigint | The number of times a rep requested a transfer from transfer initiation to when the transfer was received. | 1 | 2019-08-22 00:00:00 | 2019-08-22 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| rep_id | varchar(191) | The ASAPP rep/agent identifier. | 123008 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| requested_rep_transfer | bigint | The rep which requested the transfer. | 1070001 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| actual_rep_transfer | bigint | The rep which received the transfer. | 250001 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| requested_group_transfer_id | bigint | The group identifier where the transfer was initially requested. | 123455 | 2019-12-13 00:00:00 | 2019-12-13 00:00:00 | no | ||||||
| requested_group_transfer_name | character varying(191) | The group name where the transfer was initially requested. | support | 2019-12-13 00:00:00 | 2019-12-13 00:00:00 | no | ||||||
| route_code_to | varchar(191) | IVR routing code indicating the customer contact reason to which the issue is being transferred into | 2323 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no | ||||||
| route_code_from | varchar(191) | IVR routing code indicating the customer contact reason from the previous assignment | 2323 | 2018-08-03 00:00:00 | 2018-08-03 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | ||||||
| created_ts | timestamp | The date and time which the message was sent. | 2019-12-17T17:11:41.626000+00:00 | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | ||||||
| conversation_id | bigint | deprecated: 2019-09-25 | 21352352 | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | ||||||
| company_subdivision | varchar(255) | String identifier for the company subdivision associated with the conversation. | ACMEsubcorp | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | ||||||
| company_segments | varchar(255) | String with comma separated segments for the company enclosed by square brackets. | marketing,promotions | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | ||||||
| sequence_id | integer | deprecated: 2019-09-26 | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | |||||||
| sender_id | bigint | The identifier of the person who sent the message. | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | |||||||
| sender_type | character varying(191) | The type of sender. | customer, bot, rep, rep_note, rep_whisper | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | ||||||
| utterance_type | character varying(65535) | The type of utterance sent. | autosuggest, autocomplete, script, freehand | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | ||||||
| sent_to_agent | boolean | deprecated: 2019-09-25 | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | |||||||
| utterance | character varying(65535) | Text sent from a bot or human (i.e. customer, rep, expert). | ’Upgrade current device’, ‘Is there anything else we can help you with?‘ | 2018-07-13 00:00:00 | 2018-07-13 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | ||||||
| sent_to_rep | A boolean flag indicating if an utterance was sent from a customer to a rep. | true, false | 2019-09-27 00:00:00 | 2019-09-27 00:00:00 | no | |||||||
| 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 | 2019-12-06 00:00:00 | 2019-12-06 00:00:00 | no | ||||||
| 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 | 2019-12-06 00:00:00 | 2019-12-06 00:00:00 | no | ||||||
| company_name | varchar(255) | Name of the company associated with the data. | acme | 2019-11-01 00:00:00 | 2024-05-24 00:00:00 | no | ||||||
| event_uuid | varchar(36) | A UUID uniquely identifying each utterance record | 347bdddb-d3a1-45fc-bbcd-dbd3a175fc1c | 2020-10-23 00:00:00 | 2020-10-23 00:00:00 | no |
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
| Column | Type | Description | Example | Aggregates | Notes | Date Added | Date Modified | 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 | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | no | ||||||
| issue_id | bigint | The ASAPP issue or conversation id. | 21352352 | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | no | ||||||
| company_id | bigint | DEPRECATED 2024-03-25 | 10001 | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | no | ||||||
| voice_intent_code | varchar(255) | Voice intent code with the highest score associated to the issue | PAYBILL | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | no | ||||||
| voice_intent_name | varchar(255) | Voice intent name with the highest score associated to the issue | Payment history | 2021-08-10 00:00:00 | 2021-08-10 00:00:00 | no |
Last Updated: 2025-01-15 15:30:58 UTC
Was this page helpful?