About Redshift Database Access
Critcal Notice!!
The Redshift Database Access only operates on Prebuilt Reports. Since Prebuilt Reports have been discontinued, the use of the Redshift Database Access will be for legacy reporting only.
Purchasing Redshift Database Access
The Redshift Database Access is a chargeable item and must be granted by your account manager. Please reach out to your account manager to purchase and activate the Redshift Database Access.
Not for Engage Omni
The Redshift Database Access is not suitable for Engage Omni. If you are a new Engage Omni user, please use the Historical reports and dashboards instead for your reporting purposes.
When accessing the Redshift Historical Database you will be provided with a username, password, schema name and JDBC connection URL. The credentials will only enable a user to read/query from the database. There are also limitations as to the number of connections (5) and time that a query can run (5 minutes) before they are refused or terminated respectfully.
With the aforementioned credentials you can create a connection to the historical database with your choice of client. This client will need to support either a Postgres or preferably the Redshift JDBC driver.
The Redshift JDBC driver and generic connection information can be found here.
The AWS tutorial and help pages for Redshift can be found here.
Once connected the user will have read permissions to all schemas on the database. However, you will only be able to query your provided schema. Underneath each schema is a Redshift View. These views mirror all of the tables in the production schema and contain all of the same columns.
The following query is useful as it will provide you with a list of views that your user has access to.
SELECT
table_schema as schema_name,
table_name as view_name
FROM information_schema.views
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY schema_name, view_name;
Views (Tables)
You can also view this through each client GUI. The _XXXXXXXX schema names are visible and from there you can list the views under each schema. The available views are listed below.
- agent_block_monitor
- agent_login_gates
- agent_logins
- agent_states
- call_events
- call_sessions
- calls
- dnc_list
- internal_chats
- international_calls
- reverse_match_results
- scripting_studio_result
- survey_response_details (Legacy Surveys - no longer supported)
- survey_responses (Legacy Surveys - no longer supported)
- visual_ivr_results
- whitelist_entries
From the list of available views, there are 4 main tables that are generally of most interest when creating KPIs.
- calls
- call_sessions
- agent_logins
- agent_states
Tables
A brief description of the four core tables are below.
CALLS
By far the largest table with the most information. It is also the largest table in terms of data stored and number of columns, which as of this documentation is 165. This creates a challenge at times when querying because you must be very specific about the information that you want to return or the performance of your query will suffer.
This Calls table contains a unique record per call. In most scenarios this will mean one record per uii. A uii is a unique 30 character call identifier with the first 14 digits representing the time that the call initiated on the Engage Voice platform. An example uii might look like this.
UII - 202001010011550131070000000400
We also use a shortened version of the uii which is titled the uii_table_key. The uii_table_key is 28 characters in length. This allowed us to change the data type from a varchar to a bigint which enabled slightly faster query performance while searching for specific uiis. Using the example uii above the uii_table_key would be…
UII_TABLE_KEY - 2001010011550131070000000400
The only difference being the first two characters have been removed.
On the calls table and any other table related to call data, we use the uii_table_key as a distribution key. This is because the uii_table_key has a very high cardinality. We also create a compound sort key with uii_table_key and account_id. This keeps the data organized chronologically and makes for faster querying. A table with the sort and distribution keys is located at the bottom of this document.
The Calls table contains information on any product ID the call was associated with. These columns are gate_id, campaign_id, visual_ivr_id, trac_id, cloud_destination_id, etc. The products all have a product name column, that may make them easier to identify. An example is “campaign_name” or “gate_name”. The majority of calls will be related to either Gates (now titled as Queues in the application), Campaigns, or IVRs. It can be the case that a call contains more than one product, if that is the case all products related to that call will populate the respective column with a product_id and product name. These can be used to group calls by products, search for calls on a specific product, etc. Lastly, the calls table also contains basic information as of the final call state and agent information who may have handled the call. This includes agent_id, agent_username, agent_login_id, and agent_disposition.
CALL_SESSIONS
The call sessions table is similar to the calls table however each record is broken down by session_id. Most calls will have more than one session_id, one for each leg of the call. Records with a session_id of 1 can be thought of as the entire encapsulated call. All subsequent session_ids are an individual leg of the call. For example, an agent may dial out to a lead, then once connected the agent may determine that another agent might be better able to handle that call and then transfer to an inbound Queue (gate_id). The call would have three sessions, one for the entire call, one for the outbound leg to the lead, and one on the transfer to the Queue. The call_sessions table also uses the uii_table_key and account_id distribution and sort keys.
AGENT_LOGINS
The agent_logins table gives a summed up version of specific agent login sessions. A login session is the time period between when an agent logs into the platform and when they logout. This table contains statistics about the specific state duration for each agent session, as well as metadata related to each agent like username, agent_id, agent_group_id, agent_name, agent_team, etc. Each agent login session is given an agent_login_id which is a unique identifier that can be filtered by to determine the number of calls that an agent fielded in one login session. There are several timestamps on this table including login_dts, and logout_dts. The agent_logins table is distributed evenly across all nodes in the Redshift cluster so there is not a distribution key, however there is a compound sort key that utilizes the login_dts, and account_id columns.
AGENT_STATES
The agent_states table is a detailed breakdown of agent state information. There are only 13 columns in the table. All records are sorted by account_id and agent_login_id with agent_login_id being the distribution key for the table. This table contains state_label which is the custom label assigned to a base working state if any. It also contains the base_state and the duration in seconds for how long the agent was in that particular state. It also contains the uii_table_key for any call that may have been tied to that state.
There are also two columns on each table that are titled etl_job_timestamp and etl_job_guid. These are internal columns used to track the progress of data loading into the Redshift cluster and can be ignored.
Sample Queries
We highly advise against writing any SELECT * query especially against the CALLS table. It is not performant and may cause us to cancel any long running query.
GENERIC CALLS SELECT
SELECT
uii_table_key,
account_id,
call_timestamp,
enqueue_time,
dequeue_time,
dequeue_attempts,
dial_type,
call_type,
agent_notes,
gate_id,
gate_name,
is_presented,
is_accepted,
agent_disposition,
campaign_id,
campaign_name,
visual_ivr_id,
visual_ivr_name,
lead_id,
list_id,
pass_number,
agent_login_id,
agent_id
FROM _xxxxxxxx.calls
WHERE account_id = xxxxxxxx AND uii_table_key BETWEEN 2001010011550131070000000400 AND 2001020011550131070000000400;
GENERIC AGENT LOGINS
SELECT
account_id,
agent_id,
agent_login_id,
username,
last_name,
fist_name,
email,
login_dts,
logout_dts,
login_time,
available_time,
engaged_time,
lunch_time,
work_time,
train_time,
rna_time,
pending_disp_time,
agent_phone,
team
FROM _xxxxxxxx.agent_logins
WHERE account_id = xxxxxxxx AND login_dts BETWEEN ‘2020-01-01 00:00:00’ AND ‘2020-01-02 00:00:00’;
Sort and Distribution Keys
The table below contains a list of distribution and sort keys for each table. If the distkey column is true, then that column is the distribution key for the table. For the sort key column, the number corresponds to the position of the column in the compound sort. We encourage you to use both the distribution key and sort keys when writing queries. To do this include them in your WHERE clause and include them on any joins.
Table | Column | Data Type | Distribution Key | Sort Key |
---|---|---|---|---|
agent_block_monitor | agent_login_id | bigint | false | 2 |
agent_login_gates | agent_login_id | bigint | false | 2 |
agent_states | agent_login_id | bigint | true | 1 |
internal_chats | log_id | bigint | true | 0 |
agent_block_monitor | account_id | integer | false | 1 |
agent_login_gates | account_id | integer | false | 1 |
agent_logins | account_id | integer | false | 2 |
agent_states | account_id | integer | false | 2 |
call_events | account_id | integer | false | 1 |
call_sessions | account_id | integer | false | 2 |
calls | account_id | integer | false | 2 |
chats | account_id | integer | false | 1 |
dnc_list | account_id | integer | false | 1 |
internal_chats | account_id | integer | false | 2 |
international_calls | account_id | integer | false | 2 |
reverse_match_results | account_id | integer | false | 2 |
scripting_studio_result | account_id | integer | false | 2 |
survey_response_details | account_id | integer | false | 2 |
survey_response_details | response_id | integer | true | 1 |
survey_responses | account_id | integer | false | 2 |
visual_ivr_results | account_id | integer | false | 2 |
whitelist_entries | account_id | integer | false | 1 |
call_events | uii_table_key | numeric(28,0) | true | 2 |
call_sessions | uii_table_key | numeric(28,0) | true | 1 |
calls | uii_table_key | numeric(28,0) | true | 1 |
chats | uii_table_key | numeric(28,0) | true | 2 |
international_calls | uii_table_key | numeric(28,0) | true | 1 |
reverse_match_results | uii_table_key | numeric(28,0) | true | 1 |
scripting_studio_result | uii_table_key | numeric(28,0) | true | 1 |
survey_responses | uii_table_key | numeric(28,0) | true | 1 |
visual_ivr_results | uii_table_key | numeric(28,0) | true | 1 |
agent_logins | login_dts | timestamp without time zone | false | 1 |
dnc_list | added_date | timestamp without time zone | false | 2 |
internal_chats | message_dts | timestamp without time zone | false | 1 |
whitelist_entries | added_dts | timestamp without time zone | false | 2 |
Table Descriptions
Below are tables containing the columns their position in the tables they belong to. They also list their data type, max length, and whether that column is nullable or not.
CALLS
Position | Column | Data Type | Max Length | Nullable |
---|---|---|---|---|
1 | uii_table_key | numeric | 28 | NO |
2 | uii | character | 30 | NO |
3 | account_id | integer | 32 | NO |
4 | account_name | character varying | 126 | YES |
5 | call_timestamp | timestamp without time zone | YES | |
6 | call_date | date | YES | |
7 | visual_ivr_id | integer | 32 | YES |
8 | trac_id | integer | 32 | YES |
9 | cloud_profile_id | integer | 32 | YES |
10 | campaign_id | integer | 32 | YES |
11 | campaign_name | character varying | 150 | YES |
12 | campaign_desc | character varying | 255 | YES |
13 | gate_id | integer | 32 | YES |
14 | gate_name | character varying | 100 | YES |
15 | gate_desc | character varying | 200 | YES |
16 | source_type | character | 20 | YES |
17 | lead_id | integer | 32 | YES |
18 | outbound_disposition | character | 25 | YES |
19 | source_app_id | character varying | 20 | YES |
20 | ani | character varying | 20 | YES |
21 | dnis | character varying | 20 | YES |
22 | originating_dnis | character varying | 20 | YES |
23 | aux_phone | character varying | 30 | YES |
24 | enqueue_time | timestamp without time zone | YES | |
25 | dequeue_time | timestamp without time zone | YES | |
26 | call_state | character | 20 | YES |
27 | priority_queue_event | character varying | 255 | YES |
28 | skill_override | character varying | 255 | YES |
29 | next_dequeue_time | timestamp without time zone | YES | |
30 | dequeue_attempts | integer | 32 | YES |
31 | curr_dequeue_event_id | integer | 32 | YES |
32 | next_session_id | integer | 32 | YES |
33 | recording_deleted | integer | 32 | YES |
34 | opt_out | boolean | YES | |
35 | sla_qualified | boolean | YES | |
36 | sla_passed | boolean | YES | |
37 | is_short_call | boolean | YES | |
38 | is_long_call | boolean | YES | |
39 | is_short_abandon | boolean | YES | |
40 | dial_type | integer | 32 | YES |
41 | call_type | character | 20 | YES |
42 | agent_notes | character varying | 65535 | YES |
43 | pci_compliant | boolean | YES | |
44 | recording_url | character varying | 555 | YES |
45 | call_result | character | 20 | YES |
46 | is_presented | boolean | YES | |
47 | agent_id | integer | 32 | YES |
48 | agent_first_name | character varying | 160 | YES |
49 | agent_last_name | character varying | 160 | YES |
50 | agent_username | character varying | 260 | YES |
51 | agent_externid | character varying | 255 | YES |
52 | is_agent_disposition_complete | boolean | YES | |
53 | gate_group_id | integer | 32 | YES |
54 | group_name | character varying | 100 | YES |
55 | trac_description | character varying | 255 | YES |
56 | trac_rule_destination | character varying | 255 | YES |
57 | callback_requested | boolean | YES | |
58 | is_callback | boolean | YES | |
59 | callback_uii | character varying | 30 | YES |
60 | callback_result | character varying | 255 | YES |
61 | profile_name | character varying | 255 | YES |
62 | profile_desc | character varying | 255 | YES |
63 | cloud_destination_id | integer | 32 | YES |
64 | destination_name | character varying | 255 | YES |
65 | destination_desc | character varying | 255 | YES |
66 | media_isci | character varying | 255 | YES |
67 | media_network | character varying | 255 | YES |
68 | media_market | character varying | 255 | YES |
69 | media_code | character varying | 255 | YES |
70 | media_format | character varying | 255 | YES |
71 | media_version | character varying | 255 | YES |
72 | media_length | character varying | 255 | YES |
73 | sla_time | integer | 32 | YES |
74 | etl_job_timestamp | timestamp without time zone | YES | |
75 | etl_job_guid | character varying | 256 | YES |
76 | latitude | numeric | 10 | YES |
77 | longitude | numeric | 10 | YES |
78 | ani_country_code | character | 5 | YES |
79 | ani_state | character | 5 | YES |
80 | agent_disposition | character varying | 180 | YES |
81 | outbound_externid | character varying | 180 | YES |
82 | main_account_name | character varying | 125 | YES |
83 | main_account_id | integer | 32 | YES |
84 | ani_is_cell | smallint | 16 | YES |
85 | trac_open_rule | boolean | YES | |
86 | trac_locater_description | character varying | 255 | YES |
87 | trac_location_description | character varying | 255 | YES |
88 | ani_city | character varying | 264 | YES |
89 | trac_dest_description | character varying | 255 | YES |
90 | dial_group_id | integer | 32 | YES |
91 | dial_group_name | character varying | 125 | YES |
92 | dial_group_desc | character varying | 125 | YES |
93 | billing_code | character varying | 256 | YES |
94 | dnis_description | character varying | 256 | YES |
95 | caller_id | character varying | 20 | YES |
96 | live_answer_message | character varying | 256 | YES |
97 | mach_answer_message | character varying | 256 | YES |
98 | list_id | bigint | 64 | YES |
99 | pass_number | integer | 32 | YES |
100 | is_complete | smallint | 16 | YES |
101 | uploaded_list_description | character varying | 400 | YES |
102 | reserved_agent_id | integer | 32 | YES |
103 | lead_state | character varying | 50 | YES |
104 | next_dial_time | timestamp without time zone | YES | |
105 | lead_timezone | character varying | 5 | YES |
106 | title | character varying | 100 | YES |
107 | mid_name | character varying | 100 | YES |
108 | suffix | character varying | 100 | YES |
109 | city | character varying | 50 | YES |
110 | state | character varying | 50 | YES |
111 | zip | character varying | 50 | YES |
112 | gate_keeper | character varying | 325 | YES |
113 | character varying | 325 | YES | |
114 | aux_data1 | character varying | 325 | YES |
115 | aux_data2 | character varying | 325 | YES |
116 | aux_data3 | character varying | 325 | YES |
117 | aux_data4 | character varying | 325 | YES |
118 | aux_data5 | character varying | 325 | YES |
119 | aux_external_url | character varying | 325 | YES |
120 | aux_greeting | character varying | 65535 | YES |
121 | loaded_dts | timestamp without time zone | YES | |
122 | list_desc | character varying | 400 | YES |
123 | reserved_agent_firstname | character varying | 100 | YES |
124 | reserved_agent_lastname | character varying | 100 | YES |
125 | reserved_agent_username | character varying | 200 | YES |
126 | address1 | character varying | 325 | YES |
127 | address2 | character varying | 325 | YES |
128 | first_name | character varying | 200 | YES |
129 | last_name | character varying | 200 | YES |
130 | is_contact | boolean | YES | |
131 | is_success | boolean | YES | |
132 | is_xfer | boolean | YES | |
133 | xfer_dest | character varying | 50 | YES |
134 | dial_mode | character varying | 30 | YES |
135 | country_id | character | 10 | YES |
136 | country_name | character varying | 75 | YES |
137 | country_code | character | 10 | YES |
138 | list_state | character varying | 265 | YES |
139 | int_cost | numeric | 14 | YES |
140 | international_prefix | character varying | 30 | YES |
141 | international_destination | character varying | 256 | YES |
142 | speed_to_lead_first_pass | integer | 32 | YES |
143 | speed_to_lead_agent_conn | integer | 32 | YES |
144 | external_agent_id | character varying | 284 | YES |
145 | agent_login_id | bigint | 64 | YES |
146 | session_id | smallint | 16 | YES |
147 | dial_duration | integer | 32 | YES |
148 | is_abandoned | boolean | YES | |
149 | is_accepted | boolean | YES | |
150 | is_deflected | boolean | YES | |
151 | is_manual_no_connect | boolean | YES | |
152 | exclude_abandon | smallint | 16 | YES |
153 | agent_group_id | integer | 32 | YES |
154 | agent_group_name | character varying | 255 | YES |
155 | script_group_id | integer | 32 | YES |
156 | script_group_name | character varying | 255 | YES |
157 | track_group_id | integer | 32 | YES |
158 | track_group_name | character varying | 255 | YES |
159 | visual_ivr_group_id | integer | 32 | YES |
160 | visual_ivr_group_name | character varying | 255 | YES |
161 | cloud_group_id | integer | 32 | YES |
162 | cloud_group_name | character varying | 255 | YES |
163 | lead_extra_data | character varying | 65535 | YES |
164 | pass_disposition | character varying | 30 | YES |
165 | outbound_disposition_tracker | character varying | 30 | YES |
CALL SESSIONS
Position | Column | Data Type | Max Length | Nullable |
---|---|---|---|---|
1 | uii_table_key | numeric | 28 | NO |
2 | account_id | integer | 32 | NO |
3 | uii | character | 30 | NO |
4 | the_date | timestamp without time zone | YES | |
5 | session_id | smallint | 16 | NO |
6 | session_type | character varying | 20 | YES |
7 | recording_url | character varying | 255 | YES |
8 | is_monitoring | integer | 32 | YES |
9 | on_hold | integer | 32 | YES |
10 | call_dts | timestamp without time zone | YES | |
11 | duration | integer | 32 | YES |
12 | term_party | character varying | 140 | YES |
13 | term_reason | character varying | 130 | YES |
14 | agent_login_id | bigint | 64 | YES |
15 | cloud_destination_id | integer | 32 | YES |
16 | is_dequeue_agent | smallint | 16 | YES |
17 | transfer_flag | integer | 32 | YES |
18 | skill_profile_id | integer | 32 | YES |
19 | dial_dts | timestamp without time zone | YES | |
20 | dial_duration | integer | 32 | YES |
21 | dial_disposition | character varying | 50 | YES |
22 | carrier | character varying | 50 | YES |
23 | wrap_time | integer | 32 | YES |
24 | gate_id | integer | 32 | YES |
25 | gate_name | character varying | 150 | YES |
26 | agent_id | integer | 32 | YES |
27 | first_name | character varying | 150 | YES |
28 | last_name | character varying | 150 | YES |
29 | username | character varying | 270 | YES |
30 | team | character varying | 150 | YES |
31 | dial_result | character varying | 20 | YES |
32 | source_type | character | 10 | YES |
33 | source_id | bigint | 64 | YES |
34 | source_name | character varying | 255 | YES |
35 | etl_job_timestamp | timestamp without time zone | YES | |
36 | etl_job_guid | character | 38 | YES |
37 | agent_notes | character varying | 65535 | YES |
38 | agent_disposition | character varying | 150 | YES |
39 | phone | character varying | 90 | YES |
40 | cloud_destination_name | character varying | 312 | YES |
41 | cloud_destination_desc | character varying | 312 | YES |
42 | wait_time | integer | 32 | YES |
AGENT LOGINS
Position | Column | Data Type | Max Length | Nullable |
---|---|---|---|---|
1 | account_id | integer | 32 | NO |
2 | agent_login_id | bigint | 64 | NO |
3 | agent_id | integer | 32 | YES |
4 | parent_agent_id | integer | 32 | YES |
5 | skill_profile_id | integer | 32 | YES |
6 | login_dial_group_id | integer | 32 | YES |
7 | monitor_agent_id | integer | 32 | YES |
8 | agent_team | character varying | 65 | YES |
9 | first_name | character varying | 65 | YES |
10 | last_name | character varying | 65 | YES |
11 | username | character varying | 65 | YES |
12 | character varying | 150 | YES | |
13 | team | character varying | 65 | YES |
14 | agent_name | character varying | 65 | YES |
15 | external_agent_id | character varying | 65 | YES |
16 | agent_phone | character varying | 255 | YES |
17 | agent_state | character varying | 32 | YES |
18 | agent_aux_state | character varying | 50 | YES |
19 | agent_login_type | character varying | 16 | YES |
20 | state_dts | timestamp without time zone | YES | |
21 | login_dts | timestamp without time zone | NO | |
22 | logout_dts | timestamp without time zone | YES | |
23 | login_time | bigint | 64 | YES |
24 | calls_handled | integer | 32 | YES |
25 | preview_dials | integer | 32 | YES |
26 | manual_dials | integer | 32 | YES |
27 | rna_count | integer | 32 | YES |
28 | reserved_uii | character varying | 30 | YES |
29 | reserved_dts | timestamp without time zone | YES | |
30 | last_call_dts | timestamp without time zone | YES | |
31 | next_call_time | timestamp without time zone | YES | |
32 | pending_disposition | smallint | 16 | YES |
33 | pending_disp_time | integer | 32 | YES |
34 | ip_map_key | character varying | 32 | YES |
35 | current_chat_count | integer | 32 | YES |
36 | last_archive_state_dts | timestamp without time zone | YES | |
37 | agent_rank | integer | 32 | YES |
38 | available_time | integer | 32 | YES |
39 | ring_time | integer | 32 | YES |
40 | engaged_time | integer | 32 | YES |
41 | hold_time | integer | 32 | YES |
42 | break_time | integer | 32 | YES |
43 | away_time | integer | 32 | YES |
44 | lunch_time | integer | 32 | YES |
45 | train_time | integer | 32 | YES |
46 | rna_time | integer | 32 | YES |
47 | work_time | integer | 32 | YES |
48 | dial_group_id | integer | 32 | YES |
49 | dial_group_name | character varying | 100 | YES |
50 | off_hook_time | numeric | 20 | YES |
51 | etl_job_timestamp | timestamp without time zone | YES | |
52 | etl_job_guid | character varying | 36 | YES |
53 | rounded_off_hook_time | numeric | 20 | YES |
54 | account_name | character varying | 65 | YES |
55 | location | character varying | 48 | YES |
56 | main_account_name | character varying | 65 | YES |
57 | main_account_id | character varying | 8 | YES |
58 | agent_group_id | integer | 32 | YES |
59 | agent_group_name | character varying | 256 | YES |
60 | chat_engaged_time | integer | 32 | YES |
61 | chat_rna_time | integer | 32 | YES |
62 | chat_available_time | integer | 32 | YES |
63 | chat_presented_time | integer | 32 | YES |
AGENT STATES
Position | Column | Data Type | Max Length | Nullable |
---|---|---|---|---|
1 | account_id | integer | 32 | NO |
2 | agent_login_id | bigint | 64 | NO |
3 | state_dts | timestamp without time zone | YES | |
4 | base_state | character varying | 24 | YES |
5 | state_label | character varying | 50 | YES |
6 | duration | integer | 32 | YES |
7 | etl_job_timestamp | timestamp without time zone | YES | |
8 | etl_job_guid | character varying | 36 | YES |
9 | pending_disp | boolean | YES | |
10 | uii | character | 30 | YES |
11 | uii_table_key | numeric | 28 | YES |
12 | chat_base_state | character varying | 50 | YES |
13 | chat_duration | integer | 32 | YES |