View for conversation voice overview data combining voice analytics with conversation summary data
CREATE VIEW vwconvvoiceoverviewdata AS (
SELECT cv.conversationid,
cv.sentimentscore,
cv.sentimenttrend,
cv.agentdurationpercentage,
cv.customerdurationpercentage,
cv.silencedurationpercentage,
cv.overtalkdurationpercentage,
cv.overtalkcount,
cv.ivrdurationpercentage,
cv.acddurationpercentage,
cv.otherdurationpercentage,
cs.conversationstartdate,
cs.conversationstartdateltc,
cs.conversationenddate,
cs.conversationenddateltc,
cs.ttalkcomplete,
cs.ani,
cs.dnis,
cs.firstmediatype,
cs.divisionid,
cs.firstqueueid,
cs.firstqueuename,
cs.lastqueueid,
cs.lastqueuename,
cs.firstagentid,
cs.firstagentname,
cs.firstagentdepartment AS firstagentdept,
cs.firstagentmanagerid,
cs.firstagentmanager AS firstagentmanagername,
cs.lastagentid,
cs.lastagentname,
cs.lastagentdepartment AS lastagentdept,
cs.lastagentmanagerid,
cs.lastagentmanager AS lastagentmanagername,
cs.firstwrapupcode,
cs.firstwrapname AS firstwrapupname,
cs.lastwrapupcode,
cs.lastwrapname AS lastwrapupname,
dd.name AS divisionname
FROM ((convvoiceoverviewdata cv
LEFT JOIN vwconvsummarydata cs ON (((cs.conversationid)::text = (cv.conversationid)::text)))
LEFT JOIN divisiondetails dd ON (((cs.divisionid)::text = (dd.id)::text)))
)
Name | Type | Default | Nullable | Children | Parents | Comment |
---|---|---|---|---|---|---|
conversationid | varchar(50) | true | Conversation GUID | |||
sentimentscore | numeric(20,2) | true | Voice sentiment analysis score | |||
sentimenttrend | numeric(20,2) | true | Voice sentiment trend analysis | |||
agentdurationpercentage | numeric(20,2) | true | Percentage of conversation duration spoken by agent | |||
customerdurationpercentage | numeric(20,2) | true | Percentage of conversation duration spoken by customer | |||
silencedurationpercentage | numeric(20,2) | true | Percentage of conversation duration in silence | |||
overtalkdurationpercentage | numeric(20,2) | true | Percentage of conversation duration with overtalk | |||
overtalkcount | integer | true | Number of overtalk instances in conversation | |||
ivrdurationpercentage | numeric(20,2) | true | Percentage of conversation duration in IVR | |||
acddurationpercentage | numeric(20,2) | true | Percentage of conversation duration in ACD | |||
otherdurationpercentage | numeric(20,2) | true | Percentage of conversation duration in other states | |||
conversationstartdate | timestamp without time zone | true | Conversation start date (UTC) | |||
conversationstartdateltc | timestamp without time zone | true | Conversation start date (local time) | |||
conversationenddate | timestamp without time zone | true | Conversation end date (UTC) | |||
conversationenddateltc | timestamp without time zone | true | Conversation end date (local time) | |||
ttalkcomplete | numeric(20,2) | true | Total talk time complete | |||
ani | varchar(400) | true | Automatic Number Identification (caller ID) | |||
dnis | varchar(400) | true | Dialed Number Identification Service | |||
firstmediatype | varchar(50) | true | First media type of conversation | |||
divisionid | varchar(50) | true | Division GUID | |||
firstqueueid | varchar(50) | true | First queue GUID | |||
firstqueuename | varchar(255) | true | First queue name | |||
lastqueueid | varchar(50) | true | Last queue GUID | |||
lastqueuename | varchar(255) | true | Last queue name | |||
firstagentid | varchar(50) | true | First agent GUID | |||
firstagentname | varchar(200) | true | First agent name | |||
firstagentdept | varchar(200) | true | First agent department | |||
firstagentmanagerid | varchar(50) | true | First agent manager GUID | |||
firstagentmanagername | varchar(200) | true | First agent manager name | |||
lastagentid | varchar(50) | true | Last agent GUID | |||
lastagentname | varchar(200) | true | Last agent name | |||
lastagentdept | varchar(200) | true | Last agent department | |||
lastagentmanagerid | varchar(50) | true | Last agent manager GUID | |||
lastagentmanagername | varchar(200) | true | Last agent manager name | |||
firstwrapupcode | varchar(255) | true | First wrap-up code GUID | |||
firstwrapupname | varchar(255) | true | First wrap-up code name | |||
lastwrapupcode | varchar(255) | true | Last wrap-up code GUID | |||
lastwrapupname | varchar(255) | true | Last wrap-up code name | |||
divisionname | varchar(50) | true | Division name |
Name | Columns | Comment | Type |
---|---|---|---|
public.convvoiceoverviewdata | 20 | Voice Analysis Overview Data | BASE TABLE |
public.vwconvsummarydata | 68 | See ConvSummaryData - Expands all the GUIDs with their lookups | VIEW |
public.divisiondetails | 4 | BASE TABLE |