Agent Call Detail
Download SQL file: DBS-AgentCallDetailReportAgentCallDetailReportSnow.sql
The example below queries the BroadsoftCCAgentCallFact for agent details by call center agents for a specified month.
...
-- DBS agent call details...
SELECT TOP 1000
...
bud_broadsoftUserDisplayName...
, ...
acf_creationTimestamp...
, ...
acf_callReleasedTimestamp...
, ...
acf_callType...
, ...
acf_agentSkill...
, ...
acf_remoteNumber...
,
acf_numberCalled
...
, ...
acf_callStartedTimestamp...
, ...
acf_callTalkDuration...
, ...
acf_callHeldDuration...
, ...
acf_wrapUpDuration...
,
acf_redirectNumber
...
,
...
acf_redirectLocation...
FROM
...
BroadsoftCCAgentCallFact...
LEFT OUTER JOIN
...
BroadsoftUserDimension...
ON
...
acf_agentUserFk...
= ...
bud_id...
WHERE
...
acf_...
callStartedDateUTC >=
...
20220901 AND
...
acf_...
callStartedDateUTC <
...
20221001;
Queue Call Statistics
Download SQL file: DBS-QueueMetricsReportQueueMetricsReportSnow.sql
The example below queries the BroadsoftCCQueueCallFact for queue statistics by date and call status.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
-- DBS queue call counters by day & status SELECT [qcf_callCreationDateUTC] ["Date UTC]", COALESCE( [qcf_status], [qcf_deflectedCallReason] ) [Status], COUNT(*) ["Call Count]", SUM( [qcf_callOfferedCount] ) ["Offered Count]", SUM( [qcf_callBouncedCount] ) ["Bounced Count]" FROM [dbo].[BroadsoftCCQueueCallFact] WHERE [qcf_callCreationDateUTC] >= 2021080120220801 AND [qcf_callCreationDateUTC] < 2021090120220901 GROUP BY [qcf_callCreationDateUTC], COALESCE( [qcf_status], [qcf_deflectedCallReason] ) ORDER BY ["Date UTC]", [Status]; /* Date UTC Status Call Count Offered Count Bounced Count 20210826 CALLABANDONED 1284 225 141 20210826 CALLABANDONEDENTRANCE 52 20 0 20210826 CALLANSWERED 8757 10452 1705 20210826 CALLOVERFLOWEDBYTIME 8 5 2 20210826 CALLSTRANDED 8 1 1 20210826 CALLSTRANDEDUNAVAILABLE 589 51 51 20210826 CALLTRANSFERRED 17 4 1 20210826 FORCEDFORWARDAPPLIED 37 0 0 20210826 NIGHTSERVICEAPPLIED 407 0 0 20210827 CALLABANDONED 1404 210 132 Status Call Count Offered Count Bounced Count 20210826 CALLABANDONED 1284 225 141 20210826 CALLABANDONEDENTRANCE 52 20 0 20210826 CALLANSWERED 8757 10452 1705 20210826 CALLOVERFLOWEDBYTIME 8 5 2 20210826 CALLSTRANDED 8 1 1 20210826 CALLSTRANDEDUNAVAILABLE 589 51 51 20210826 CALLTRANSFERRED 17 4 1 20210826 FORCEDFORWARDAPPLIED 37 0 0 20210826 NIGHTSERVICEAPPLIED 407 0 0 20210827 CALLABANDONED 1404 210 132 */ |
The example below queries the BroadsoftCCQueueCallFact for queue statistics by date, hour-of-day and call status.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
-- DBS queue call counters by hourday & status SELECT [qcf_callCreationDateUTC] ["Date UTC]", [qcf_callCreationTimeUTC] / 10000 [Hour], COALESCE( [qcf_status], [qcf_deflectedCallReason] ) [Status], COUNT(*) ["Call Count]", SUM( [qcf_callOfferedCount] ) ["Offered Count]", SUM( [qcf_callBouncedCount] ) ["Bounced Count]" FROM [dbo].[BroadsoftCCQueueCallFact] WHERE [qcf_callCreationDateUTC] >= 2021080120220801 AND [qcf_callCreationDateUTC] < 2021080220220901 GROUP BY [qcf_callCreationDateUTC], [qcf_callCreationTimeUTC] / 10000, COALESCE( [qcf_status], [qcf_deflectedCallReason] ) ORDER BY ["Date UTC], [Hour]", [Status]; /* Date UTC Hour Status Call Count Offered Count Bounced Count 20210901 0 CALLANSWERED 37 40 3 20210901 0 FORCEDFORWARDAPPLIED 3 0 0 20210901 0 NIGHTSERVICEAPPLIED 186 0 0 20210901 1 CALLABANDONED 4 2 2 20210901 1 CALLANSWERED 24 27 5 20210901 1 FORCEDFORWARDAPPLIED 1 0 0 20210901 1 NIGHTSERVICEAPPLIED 110 0 0 20210901 2 CALLANSWERED 16 16 0 20210901 2 NIGHTSERVICEAPPLIED 60 0 0 20210901 3 NIGHTSERVICEAPPLIED 45 0 0 20210901 4 NIGHTSERVICEAPPLIED 16 0 0 20210901 5 NIGHTSERVICEAPPLIED 15 0 0 20210901 6 NIGHTSERVICEAPPLIED 3 0 0 */ Status Call Count Offered Count Bounced Count 20210826 CALLABANDONED 1284 225 141 20210826 CALLABANDONEDENTRANCE 52 20 0 20210826 CALLANSWERED 8757 10452 1705 20210826 CALLOVERFLOWEDBYTIME 8 5 2 20210826 CALLSTRANDED 8 1 1 20210826 CALLSTRANDEDUNAVAILABLE 589 51 51 20210826 CALLTRANSFERRED 17 4 1 20210826 FORCEDFORWARDAPPLIED 37 0 0 20210826 NIGHTSERVICEAPPLIED 407 0 0 20210827 CALLABANDONED 1404 210 132 */ -- DBS queue call counters by hour & status SELECT qcf_callCreationDateUTC "Date UTC", FLOOR(qcf_callCreationTimeUTC / 10000) "Hour", COALESCE( qcf_status, qcf_deflectedCallReason ) "Status", COUNT(*) "Call Count", SUM( qcf_callOfferedCount ) "Offered Count", SUM( qcf_callBouncedCount ) "Bounced Count" FROM BroadsoftCCQueueCallFact WHERE qcf_callCreationDateUTC >= 20230301 AND qcf_callCreationDateUTC < 20230302 GROUP BY qcf_callCreationDateUTC, FLOOR(qcf_callCreationTimeUTC / 10000), COALESCE( qcf_status, qcf_deflectedCallReason ) ORDER BY "Date UTC", "Hour", "Status"; -- CAST((qcf_callCreationTimeUTC / 10000) AS INTEGER) /* Date UTC Hour Status Call Count Offered Count Bounced Count 20210901 0 CALLANSWERED 37 40 3 20210901 0 FORCEDFORWARDAPPLIED 3 0 0 20210901 0 NIGHTSERVICEAPPLIED 186 0 0 20210901 1 CALLABANDONED 4 2 2 20210901 1 CALLANSWERED 24 27 5 20210901 1 FORCEDFORWARDAPPLIED 1 0 0 20210901 1 NIGHTSERVICEAPPLIED 110 0 0 20210901 2 CALLANSWERED 16 16 0 20210901 2 NIGHTSERVICEAPPLIED 60 0 0 20210901 3 NIGHTSERVICEAPPLIED 45 0 0 20210901 4 NIGHTSERVICEAPPLIED 16 0 0 20210901 5 NIGHTSERVICEAPPLIED 15 0 0 20210901 6 NIGHTSERVICEAPPLIED 3 0 0 */ |
Download SQL file: DBS-LocationUsingCaseStatementLocationUsingCaseStatementSnow.sql
The example below queries the BroadsoftCCQueueCallFact for queue call details.
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
-- DBS Show queue and agent locations for queued calls SELECT DISTINCT QL.lcd_location AS ["Queue Location]", CASE WHEN QL.lcd_location IS NOT NULL THEN CONCAT( QL.[lcd_city], ', ', QL.[lcd_stateProvince] ) ELSE NULL END AS ["Queue Address]", AL.lcd_location AS ["Agent Location]", CASE WHEN AL.lcd_location IS NOT NULL THEN CONCAT( AL.[lcd_city], ', ', AL.[lcd_stateProvince] ) ELSE NULL END AS ["Agent Address]", dt.dd_dateAsDate,ccd.bccd_name, ccd.bccd_userId, qcf.qcf_status, qcf.qcf_deflectedCallReason FROM dbo.FROM BroadsoftCCQueueCallFact qcf with (nolock) INNER JOIN dbo.DateDimension dt WITH (NOLOCK) ON dt.dd_date = qcf.qcf_callReleasedDateLocal leftleft outer join dbo.BroadsoftUserDimension ud with (nolock) on ud.bud_id = qcf.qcf_agentUserFk left left outer join dbo.BroadsoftCallCenterDimension ccd with (nolock) on ccd.bccd_id = qcf.qcf_callCenterFk left left outer join dbo.BroadsoftUserDimension QU with (nolock) on QU.bud_broadsoftUserId = ccd.bccd_userId left left outer join dbo.LocationDimension AL with (nolock) on ud.bud_locationId = AL.lcd_location left left outer join dbo.LocationDimension QL with (nolock) on QU.bud_locationId = QL.lcd_location WHERE qcf.qcf_callCreationDateLocal >= 2021100820230301 AND qcf.qcf_callCreationDateLocal < 20211009;20230302 /* Queue Location Queue Address Agent Location Agent Address dd_dateAsDate bccd_name bccd_userId qcf_status qcf_deflectedCallReason 0001000XXX EXTON, PA NULL NULL 2022-01-08 Alpha xxxxx cc-0001000XXX-Alphaxxxxx NULL NIGHTSERVICEAPPLIED 0001000XXX EXTON, PA NULL NULL 2022-01-08 yyy App Queue cc-0001000XXX-AppCC NULL NIGHTSERVICEAPPLIED 0001005437 KING OF PRUSSIA, PA NULL NULL 2022-01-08 Flex Support cc-0001005437-999 CALLABANDONEDENTRANCE NULL */ |