CREATE VIEW vwoutboundattemptdatabuild AS (
WITH seg AS NOT MATERIALIZED (
SELECT di.conversationid,
di.conversationstartdate,
di.sessionid,
di.purpose,
di.mediatype,
di.sessiondirection,
di.segmenttype,
di.segmentstartdate,
di.segmentenddate,
di.disconnectiontype,
di.errorcode,
di.sipresponsecodes,
di.q850responsecodes,
di.wrapupcode,
di.callbackscheduledtime,
di.outboundcampaignid,
di.outboundcontactid,
di.outboundcontactlistid,
di.dispositionname,
di.userid,
di.queueid,
di.noutboundconnected,
di.ttalkcomplete,
di.theldcomplete,
di.tacw,
di.thandle,
di.tacd
FROM detailedinteractiondata di
WHERE ((di.outboundcampaignid IS NOT NULL) AND (di.conversationid IS NOT NULL) AND (di.sessionid IS NOT NULL))
), events AS (
SELECT s.conversationid,
s.conversationstartdate,
s.sessionid,
s.purpose,
s.mediatype,
s.sessiondirection,
s.segmenttype,
s.segmentstartdate,
s.segmentenddate,
s.disconnectiontype,
s.errorcode,
s.sipresponsecodes,
s.q850responsecodes,
s.wrapupcode,
s.callbackscheduledtime,
s.outboundcampaignid,
s.outboundcontactid,
s.outboundcontactlistid,
s.dispositionname,
s.userid,
s.queueid,
s.noutboundconnected,
s.ttalkcomplete,
s.theldcomplete,
s.tacw,
s.thandle,
s.tacd,
s.segmentstartdate AS event_ts,
0 AS is_boundary
FROM seg s
UNION ALL
SELECT s.conversationid,
s.conversationstartdate,
s.sessionid,
s.purpose,
s.mediatype,
s.sessiondirection,
s.segmenttype,
s.segmentstartdate,
s.segmentenddate,
s.disconnectiontype,
s.errorcode,
s.sipresponsecodes,
s.q850responsecodes,
s.wrapupcode,
s.callbackscheduledtime,
s.outboundcampaignid,
s.outboundcontactid,
s.outboundcontactlistid,
s.dispositionname,
s.userid,
s.queueid,
s.noutboundconnected,
s.ttalkcomplete,
s.theldcomplete,
s.tacw,
s.thandle,
s.tacd,
s.segmentenddate AS event_ts,
1 AS is_boundary
FROM seg s
WHERE (((s.purpose)::text = 'customer'::text) AND ((s.mediatype)::text = 'callback'::text) AND ((s.segmenttype)::text = 'scheduled'::text) AND (s.segmentenddate IS NOT NULL))
), grouped AS (
SELECT e.conversationid,
e.conversationstartdate,
e.sessionid,
e.purpose,
e.mediatype,
e.sessiondirection,
e.segmenttype,
e.segmentstartdate,
e.segmentenddate,
e.disconnectiontype,
e.errorcode,
e.sipresponsecodes,
e.q850responsecodes,
e.wrapupcode,
e.callbackscheduledtime,
e.outboundcampaignid,
e.outboundcontactid,
e.outboundcontactlistid,
e.dispositionname,
e.userid,
e.queueid,
e.noutboundconnected,
e.ttalkcomplete,
e.theldcomplete,
e.tacw,
e.thandle,
e.tacd,
e.event_ts,
e.is_boundary,
sum(e.is_boundary) OVER (PARTITION BY e.conversationid, e.conversationstartdate ORDER BY e.event_ts, e.is_boundary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS attempt_group
FROM events e
), sess AS (
SELECT grouped.conversationid,
grouped.conversationstartdate,
grouped.attempt_group,
grouped.sessionid,
max((grouped.purpose)::text) AS purpose,
max((grouped.mediatype)::text) AS mediatype,
max((grouped.sessiondirection)::text) AS sessiondirection,
min(grouped.segmentstartdate) AS sess_start,
min(
CASE
WHEN ((grouped.segmenttype)::text <> 'scheduled'::text) THEN grouped.segmentstartdate
ELSE NULL::timestamp without time zone
END) AS span_start,
max(
CASE
WHEN ((grouped.segmenttype)::text <> 'scheduled'::text) THEN grouped.segmentenddate
ELSE NULL::timestamp without time zone
END) AS span_end,
sum(
CASE
WHEN (((grouped.segmenttype)::text <> 'scheduled'::text) AND (grouped.segmentenddate IS NULL)) THEN 1
ELSE 0
END) AS open_segments,
max(
CASE
WHEN ((grouped.segmenttype)::text = 'interact'::text) THEN 1
ELSE 0
END) AS answered,
max(
CASE
WHEN ((grouped.segmenttype)::text <> 'alert'::text) THEN 1
ELSE 0
END) AS engaged,
max(
CASE
WHEN ((grouped.segmenttype)::text = 'scheduled'::text) THEN 1
ELSE 0
END) AS scheduled,
max(
CASE
WHEN ((grouped.segmenttype)::text = 'scheduled'::text) THEN grouped.callbackscheduledtime
ELSE NULL::timestamp without time zone
END) AS callbackscheduledtime,
max((grouped.outboundcampaignid)::text) AS campaignid,
max((grouped.outboundcontactid)::text) AS contactid,
max((grouped.outboundcontactlistid)::text) AS contactlistid,
max((grouped.queueid)::text) AS queueid,
max((
CASE
WHEN ((grouped.disconnectiontype)::text <> 'none'::text) THEN grouped.disconnectiontype
ELSE NULL::character varying
END)::text) AS disconnectiontype,
max((grouped.errorcode)::text) AS errorcode,
max(grouped.sipresponsecodes) AS sipresponsecodes,
max(grouped.q850responsecodes) AS q850responsecodes,
max((grouped.dispositionname)::text) AS dispositionname,
max(
CASE
WHEN ((grouped.segmenttype)::text = 'wrapup'::text) THEN NULLIF((grouped.wrapupcode)::text, ''::text)
ELSE NULL::text
END) AS wrapupcode,
max(
CASE
WHEN (((grouped.segmenttype)::text = 'wrapup'::text) AND (NULLIF((grouped.wrapupcode)::text, ''::text) IS NOT NULL)) THEN grouped.segmentstartdate
ELSE NULL::timestamp without time zone
END) AS wrapup_time,
max((grouped.userid)::text) AS userid,
sum(COALESCE(grouped.noutboundconnected, 0)) AS noutboundconnected,
sum(grouped.ttalkcomplete) AS ttalkcomplete,
sum(grouped.theldcomplete) AS theldcomplete,
sum(grouped.tacw) AS tacw,
sum(grouped.thandle) AS thandle,
sum(grouped.tacd) AS tacd,
sum(
CASE
WHEN ((grouped.segmenttype)::text = ANY ((ARRAY['contacting'::character varying, 'dialing'::character varying])::text[])) THEN (EXTRACT(epoch FROM (grouped.segmentenddate - grouped.segmentstartdate)) * (1000)::numeric)
ELSE NULL::numeric
END) AS dial_ms
FROM grouped
WHERE (grouped.is_boundary = 0)
GROUP BY grouped.conversationid, grouped.conversationstartdate, grouped.attempt_group, grouped.sessionid
), flagged AS (
SELECT s.conversationid,
s.conversationstartdate,
s.attempt_group,
s.sessionid,
s.purpose,
s.mediatype,
s.sessiondirection,
s.sess_start,
s.span_start,
s.span_end,
s.open_segments,
s.answered,
s.engaged,
s.scheduled,
s.callbackscheduledtime,
s.campaignid,
s.contactid,
s.contactlistid,
s.queueid,
s.disconnectiontype,
s.errorcode,
s.sipresponsecodes,
s.q850responsecodes,
s.dispositionname,
s.wrapupcode,
s.wrapup_time,
s.userid,
s.noutboundconnected,
s.ttalkcomplete,
s.theldcomplete,
s.tacw,
s.thandle,
s.tacd,
s.dial_ms,
CASE
WHEN ((s.purpose = 'customer'::text) AND (s.mediatype <> 'callback'::text)) THEN 1
ELSE 0
END AS is_dial,
CASE
WHEN ((s.purpose = 'agent'::text) AND (s.engaged = 1)) THEN 1
ELSE 0
END AS is_agent,
CASE
WHEN ((s.purpose = 'agent'::text) AND (s.wrapupcode IS NOT NULL)) THEN 1
ELSE 0
END AS has_wrapup
FROM sess s
), ranked AS (
SELECT f.conversationid,
f.conversationstartdate,
f.attempt_group,
f.sessionid,
f.purpose,
f.mediatype,
f.sessiondirection,
f.sess_start,
f.span_start,
f.span_end,
f.open_segments,
f.answered,
f.engaged,
f.scheduled,
f.callbackscheduledtime,
f.campaignid,
f.contactid,
f.contactlistid,
f.queueid,
f.disconnectiontype,
f.errorcode,
f.sipresponsecodes,
f.q850responsecodes,
f.dispositionname,
f.wrapupcode,
f.wrapup_time,
f.userid,
f.noutboundconnected,
f.ttalkcomplete,
f.theldcomplete,
f.tacw,
f.thandle,
f.tacd,
f.dial_ms,
f.is_dial,
f.is_agent,
f.has_wrapup,
row_number() OVER (PARTITION BY f.conversationid, f.conversationstartdate, f.attempt_group, f.is_dial ORDER BY f.answered DESC, f.sess_start DESC, f.sessionid) AS dial_rank,
row_number() OVER (PARTITION BY f.conversationid, f.conversationstartdate, f.attempt_group, f.is_agent ORDER BY f.sess_start, f.sessionid) AS agent_rank,
row_number() OVER (PARTITION BY f.conversationid, f.conversationstartdate, f.attempt_group, f.has_wrapup ORDER BY
CASE
WHEN (f.mediatype <> 'callback'::text) THEN 0
ELSE 1
END, f.wrapup_time DESC, f.sessionid) AS wrap_rank
FROM flagged f
), attempt AS (
SELECT ranked.conversationid,
ranked.conversationstartdate,
ranked.attempt_group,
min(ranked.span_start) AS attempt_start,
CASE
WHEN (sum(ranked.open_segments) = (0)::numeric) THEN max(ranked.span_end)
ELSE NULL::timestamp without time zone
END AS attempt_end,
max(ranked.campaignid) AS campaignid,
max(ranked.contactid) AS contactid,
max(ranked.contactlistid) AS contactlistid,
sum(ranked.is_dial) AS dial_count,
max(
CASE
WHEN (ranked.is_dial = 1) THEN ranked.answered
ELSE NULL::integer
END) AS answered,
sum(
CASE
WHEN (ranked.is_dial = 1) THEN ranked.dial_ms
ELSE NULL::numeric
END) AS dial_ms,
max(
CASE
WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.mediatype
ELSE NULL::text
END) AS mediatype,
max(
CASE
WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.sessiondirection
ELSE NULL::text
END) AS sessiondirection,
COALESCE(max(
CASE
WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.queueid
ELSE NULL::text
END), max(ranked.queueid)) AS queueid,
max(
CASE
WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.disconnectiontype
ELSE NULL::text
END) AS disconnectiontype,
max(
CASE
WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.errorcode
ELSE NULL::text
END) AS errorcode,
max(
CASE
WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.sipresponsecodes
ELSE NULL::text
END) AS sipresponsecodes,
max(
CASE
WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.q850responsecodes
ELSE NULL::text
END) AS q850responsecodes,
max(
CASE
WHEN (ranked.purpose = 'outbound'::text) THEN ranked.dispositionname
ELSE NULL::text
END) AS dispositionname,
max(
CASE
WHEN ((ranked.purpose = 'customer'::text) AND (ranked.mediatype = 'callback'::text)) THEN ranked.scheduled
ELSE 0
END) AS scheduled_callback,
max(
CASE
WHEN ((ranked.purpose = 'customer'::text) AND (ranked.mediatype = 'callback'::text)) THEN ranked.callbackscheduledtime
ELSE NULL::timestamp without time zone
END) AS callbackscheduledtime,
sum(ranked.noutboundconnected) AS connected,
sum(
CASE
WHEN ((ranked.purpose = 'agent'::text) AND (ranked.mediatype <> 'callback'::text)) THEN ranked.ttalkcomplete
ELSE NULL::numeric
END) AS ttalkcomplete,
sum(
CASE
WHEN ((ranked.purpose = 'agent'::text) AND (ranked.mediatype <> 'callback'::text)) THEN ranked.theldcomplete
ELSE NULL::numeric
END) AS theldcomplete,
sum(
CASE
WHEN (ranked.purpose = 'agent'::text) THEN ranked.tacw
ELSE NULL::numeric
END) AS tacw,
sum(
CASE
WHEN (ranked.purpose = 'agent'::text) THEN ranked.thandle
ELSE NULL::numeric
END) AS thandle,
sum(
CASE
WHEN (ranked.purpose = 'acd'::text) THEN ranked.tacd
ELSE NULL::numeric
END) AS tacd,
max(
CASE
WHEN ((ranked.is_agent = 1) AND (ranked.agent_rank = 1)) THEN ranked.userid
ELSE NULL::text
END) AS userid,
max(
CASE
WHEN ((ranked.has_wrapup = 1) AND (ranked.wrap_rank = 1)) THEN ranked.wrapupcode
ELSE NULL::text
END) AS wrapupcode
FROM ranked
GROUP BY ranked.conversationid, ranked.conversationstartdate, ranked.attempt_group
HAVING (sum(ranked.is_dial) > 0)
), classified AS (
SELECT a.conversationid,
a.conversationstartdate,
a.attempt_group,
a.attempt_start,
a.attempt_end,
a.campaignid,
a.contactid,
a.contactlistid,
a.dial_count,
a.answered,
a.dial_ms,
a.mediatype,
a.sessiondirection,
a.queueid,
a.disconnectiontype,
a.errorcode,
a.sipresponsecodes,
a.q850responsecodes,
a.dispositionname,
a.scheduled_callback,
a.callbackscheduledtime,
a.connected,
a.ttalkcomplete,
a.theldcomplete,
a.tacw,
a.thandle,
a.tacd,
a.userid,
a.wrapupcode,
dense_rank() OVER (PARTITION BY a.conversationid, a.conversationstartdate ORDER BY a.attempt_group) AS attempt_seq,
((a.connected > (0)::numeric) OR ((a.dispositionname IS NULL) AND (a.answered = 1))) AS reached,
((','::text || replace(replace(replace(COALESCE(a.sipresponsecodes, ''::text), '['::text, ''::text), ']'::text, ''::text), ' '::text, ''::text)) || ','::text) AS sip_list,
((','::text || replace(replace(replace(COALESCE(a.q850responsecodes, ''::text), '['::text, ''::text), ']'::text, ''::text), ' '::text, ''::text)) || ','::text) AS q850_list,
CASE
WHEN (a.dispositionname IS NULL) THEN NULL::integer
WHEN (a.dispositionname ~~ 'disposition.classification.uncallable.%'::text) THEN 1
WHEN (a.dispositionname = 'disposition.classification.callable.sit'::text) THEN 1
WHEN ((a.dispositionname = 'disposition.classification.callable.disconnect'::text) AND (a.answered = 0)) THEN 1
ELSE 0
END AS disposition_failed
FROM attempt a
), resolved AS (
SELECT c_1.conversationid,
c_1.conversationstartdate,
c_1.attempt_group,
c_1.attempt_start,
c_1.attempt_end,
c_1.campaignid,
c_1.contactid,
c_1.contactlistid,
c_1.dial_count,
c_1.answered,
c_1.dial_ms,
c_1.mediatype,
c_1.sessiondirection,
c_1.queueid,
c_1.disconnectiontype,
c_1.errorcode,
c_1.sipresponsecodes,
c_1.q850responsecodes,
c_1.dispositionname,
c_1.scheduled_callback,
c_1.callbackscheduledtime,
c_1.connected,
c_1.ttalkcomplete,
c_1.theldcomplete,
c_1.tacw,
c_1.thandle,
c_1.tacd,
c_1.userid,
c_1.wrapupcode,
c_1.attempt_seq,
c_1.reached,
c_1.sip_list,
c_1.q850_list,
c_1.disposition_failed,
CASE
WHEN (c_1.dispositionname IS NOT NULL) THEN NULL::text
WHEN c_1.reached THEN 'answered'::text
WHEN ((c_1.sip_list ~~ '%,486,%'::text) OR (c_1.sip_list ~~ '%,600,%'::text)) THEN 'busy'::text
WHEN ((c_1.sip_list ~~ '%,408,%'::text) OR (c_1.sip_list ~~ '%,480,%'::text) OR (c_1.sip_list ~~ '%,487,%'::text)) THEN 'no_answer'::text
WHEN (c_1.sip_list <> ',,'::text) THEN 'failed'::text
WHEN (c_1.q850_list ~~ '%,17,%'::text) THEN 'busy'::text
WHEN ((c_1.q850_list ~~ '%,16,%'::text) OR (c_1.q850_list ~~ '%,18,%'::text) OR (c_1.q850_list ~~ '%,19,%'::text) OR (c_1.q850_list ~~ '%,31,%'::text) OR (c_1.q850_list ~~ '%,102,%'::text)) THEN 'no_answer'::text
WHEN (c_1.q850_list <> ',,'::text) THEN 'failed'::text
WHEN ((c_1.errorcode IS NOT NULL) OR (c_1.disconnectiontype = 'error'::text)) THEN 'failed'::text
WHEN (c_1.disconnectiontype = 'peer'::text) THEN 'no_answer'::text
ELSE NULL::text
END AS dial_result
FROM classified c_1
)
SELECT (((r.conversationid)::text || '|'::text) || r.attempt_seq) AS attempt_id,
r.conversationid AS conversation_id,
r.contactid AS contact_id,
NULL::character varying(255) AS contact_name,
r.campaignid AS campaign_id,
COALESCE(c.name, mc.name) AS campaign_name,
r.contactlistid AS contact_list_id,
cl.name AS contact_list_name,
(r.attempt_start)::timestamp(3) without time zone AS attempt_start_ts,
(r.attempt_end)::timestamp(3) without time zone AS attempt_end_ts,
(r.attempt_start)::date AS attempt_date,
r.userid AS agent_id,
ud.name AS agent_name,
ud.managerid AS manager_id,
ud.managername AS manager_name,
tm.teamid AS team_id,
td.name AS team_name,
r.queueid AS queue_id,
qd.name AS queue_name,
r.mediatype AS media_type,
r.sessiondirection AS direction,
r.wrapupcode AS wrap_up_code,
wd.name AS wrap_up_name,
CASE
WHEN (r.reached AND (r.wrapupcode IS NOT NULL) AND wd.isrightpartycontact) THEN 'Right Party Contact'::text
WHEN r.reached THEN 'Connected'::text
ELSE 'Not Connected'::text
END AS connect_status,
r.reached AS contact_reached,
CASE
WHEN (r.wrapupcode IS NOT NULL) THEN wd.isrightpartycontact
WHEN (NOT r.reached) THEN false
ELSE NULL::boolean
END AS right_party_contact,
CASE
WHEN (r.dispositionname IS NOT NULL) THEN (r.dispositionname ~~ '%.callable.machine'::text)
ELSE NULL::boolean
END AS answering_machine,
CASE
WHEN (r.dispositionname IS NOT NULL) THEN (r.dispositionname ~~ '%.callable.noanswer'::text)
WHEN (r.dial_result IS NOT NULL) THEN (r.dial_result = 'no_answer'::text)
ELSE NULL::boolean
END AS no_answer,
CASE
WHEN (r.dispositionname IS NOT NULL) THEN (r.dispositionname ~~ '%.callable.busy'::text)
WHEN (r.dial_result IS NOT NULL) THEN (r.dial_result = 'busy'::text)
ELSE NULL::boolean
END AS busy,
CASE
WHEN (r.dispositionname IS NOT NULL) THEN (r.disposition_failed = 1)
WHEN (r.dial_result IS NOT NULL) THEN (r.dial_result = 'failed'::text)
ELSE NULL::boolean
END AS failed,
(r.scheduled_callback = 1) AS callback_required,
(r.callbackscheduledtime)::timestamp(3) without time zone AS callback_scheduled_ts,
r.disconnectiontype AS disconnect_type,
NULLIF(TRIM(BOTH FROM ((COALESCE((r.errorcode || ' '::text), ''::text) || COALESCE((('SIP '::text || r.sipresponsecodes) || ' '::text), ''::text)) || COALESCE(('Q.850 '::text || r.q850responsecodes), ''::text))), ''::text) AS disconnect_reason,
r.dial_count,
(round(r.tacd))::integer AS queue_time_seconds,
(round((r.dial_ms / 1000.0)))::integer AS dial_time_seconds,
(round(EXTRACT(epoch FROM (r.attempt_end - r.attempt_start))))::integer AS duration_seconds,
(round(r.ttalkcomplete))::integer AS talk_time_seconds,
(round(r.theldcomplete))::integer AS hold_time_seconds,
(round(r.tacw))::integer AS wrap_up_duration_seconds,
(round(r.thandle))::integer AS handle_time_seconds,
'GenesysCloud'::character varying(50) AS source_system,
((now() AT TIME ZONE 'utc'::text))::timestamp(3) without time zone AS load_ts,
r.conversationstartdate
FROM ((((((((resolved r
LEFT JOIN vwuserdetail ud ON (((ud.id)::text = r.userid)))
LEFT JOIN ( SELECT teammemberdata.userid,
min((teammemberdata.teamid)::text) AS teamid
FROM teammemberdata
GROUP BY teammemberdata.userid) tm ON (((tm.userid)::text = r.userid)))
LEFT JOIN teamdetails td ON (((td.id)::text = tm.teamid)))
LEFT JOIN odcampaigndetails c ON (((c.id)::text = r.campaignid)))
LEFT JOIN odmessagingcampaigndetails mc ON (((mc.id)::text = r.campaignid)))
LEFT JOIN odcontactlistdetails cl ON (((cl.id)::text = r.contactlistid)))
LEFT JOIN queuedetails qd ON (((qd.id)::text = r.queueid)))
LEFT JOIN wrapupdetails wd ON (((wd.id)::text = r.wrapupcode)))
)