Dialing and Success Rate Summary Gold Query
Purpose
This query calculates and inserts campaign performance metrics for each day of each campaign into a table called dialing_success_rate_summary_gold. It is focused on calculating the total connected calls, total dialed contacts, total dialed attempts, total uploaded contacts, and also gives the total IVR-connected and agent-connected calls. It also computes the dial rate and success rate of a campaign.
Data Source Tables
campaign_scheduler
campaigns
voice_connector_activities
For more detail on campaigns and activities tables, please refer to: Reporting Database Schema
Query
CODE
-- for mysql
"dialing_success_rate_summary_gold": """
INSERT INTO dialing_success_rate_summary_gold (
`DATE`,
`Campaign_Name`,
campaign_id,
`Total_uploaded_contacts`,
`Total_dialed_contacts`,
`Total_dialed_attempts`,
`Total_connected_IVR`,
`Total_connected_Agent`,
`Dial_Rate`,
`Success_Rate`
)
SELECT
DATE,
`Campaign_Name`,
campaign_id,
SUM(`Total uploaded contacts`) AS `Total_uploaded_contacts`,
SUM(`Total dialed contacts`) AS `Total_dialed_contacts`,
SUM(`Total dialed attempts`) AS `Total_dialed_attempts`,
SUM(`Total connected - IVR`) AS `Total_connected_IVR`,
SUM(`Total connected - Agent`) AS `Total_connected_Agent`,
CASE WHEN IFNULL(SUM(`Total uploaded contacts`),0) = 0 THEN 0 ELSE
IFNULL(SUM(IFNULL(`Total dialed contacts`, 0)) / IFNULL(SUM(`Total uploaded contacts`),0), 0) * 100 END AS 'Dial_Rate',
CASE WHEN IFNULL(SUM(`Total dialed contacts`),0) = 0 THEN 0 ELSE
IFNULL(IFNULL(SUM(TOTALconnected),0) / IFNULL(SUM(`Total dialed contacts`),0), 0) * 100 END AS 'Success_Rate'
FROM (
SELECT
CAST(activity_timestamp AS DATE) AS DATE,
title AS `Campaign_Name`,
campaign_id,
COUNT(DISTINCT customer_id) AS `Total uploaded contacts`,
COUNT(DISTINCT vca_customer_id) AS `Total dialed contacts`,
COUNT(message_id) AS `Total dialed attempts`,
SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(scheduling_meta_data, '$.campaignType')) = 'IVR' AND reason_code = 'NORMAL_CLEARING' THEN 1 ELSE 0 END) AS 'Total connected - IVR',
SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(scheduling_meta_data, '$.campaignType')) = 'AGENT' AND reason_code = 'NORMAL_CLEARING' THEN 1 ELSE 0 END) AS 'Total connected - Agent',
SUM(CASE WHEN reason_code = 'NORMAL_CLEARING' THEN 1 ELSE 0 END) AS TOTALconnected
FROM (
SELECT
c.title,
c.number_of_contacts,
cs.customer_id,
vca.customer_id AS vca_customer_id,
cs.activity_data_id,
JSON_UNQUOTE(JSON_EXTRACT(cs.customer, '$.campaign')) AS campaign_id,
cs.activity_timestamp,
vca.message_id,
vca.channel_customer_identifier,
vca.reason_code,
vca.scheduling_meta_data
FROM campaign_scheduler cs
INNER JOIN campaigns c
ON JSON_UNQUOTE(JSON_EXTRACT(cs.customer, '$.campaign')) = c.id
LEFT JOIN voice_connector_activities vca
ON cs.activity_data_id = vca.message_id
AND CAST(cs.activity_timestamp AS DATE) = CAST(vca.activity_timestamp AS DATE)
WHERE cs.message_type = 'VOICE'
AND cs.activity_timestamp BETWEEN CONCAT(DATE('{start_time}'), ' 00:00:00')
AND CONCAT(DATE('{end_time}'), ' 23:59:59')
) AS A
WHERE activity_timestamp BETWEEN CONCAT(DATE('{start_time}'), ' 00:00:00')
AND CONCAT(DATE('{end_time}'), ' 23:59:59')
GROUP BY CAST(activity_timestamp AS DATE), title, campaign_id, customer_id
) AS B
GROUP BY DATE, `Campaign_Name`, campaign_id;
""",
-- for mssql
"dialing_success_rate_summary_gold_MSSQL": """
INSERT INTO dialing_success_rate_summary_gold (
DATE,
[Campaign_Name],
campaign_id,
[Total_uploaded_contacts],
[Total_dialed_contacts],
[Total_dialed_attempts],
[Total_connected_IVR],
[Total_connected_Agent],
[Dial_Rate],
[Success_Rate]
)
SELECT
DATE,
[Campaign_Name],
campaign_id,
SUM([Total uploaded contacts]) AS [Total_uploaded_contacts],
SUM([Total dialed contacts]) AS [Total_dialed_contacts],
SUM([Total dialed attempts]) AS [Total_dialed_attempts],
SUM([Total connected - IVR]) AS [Total_connected_IVR],
SUM([Total connected - Agent]) AS [Total_connected_Agent],
CASE WHEN NULLIF(SUM([Total uploaded contacts]),0) = 0 THEN 0 ELSE
ISNULL(SUM([Total dialed contacts])*1.0/NULLIF(SUM([Total uploaded contacts]),0),0)*100 END AS [Dial_Rate],
CASE WHEN NULLIF(SUM([Total dialed contacts]),0) = 0 THEN 0 ELSE
ISNULL(SUM(TOTALconnected)*1.0/NULLIF(SUM([Total dialed contacts]),0),0)*100 END AS [Success_Rate]
FROM (
SELECT
CAST(activity_timestamp AS DATE) AS DATE,
title AS [Campaign_Name],
campaign_id,
COUNT(DISTINCT customer_id) AS [Total uploaded contacts],
COUNT(DISTINCT vca_customer_id) AS [Total dialed contacts],
COUNT(message_id) AS [Total dialed attempts],
SUM(CASE WHEN JSON_VALUE(scheduling_meta_data,'$.campaignType') = 'IVR' AND reason_code = 'NORMAL_CLEARING' THEN 1 ELSE 0 END) AS [Total connected - IVR],
SUM(CASE WHEN JSON_VALUE(scheduling_meta_data,'$.campaignType') = 'AGENT' AND reason_code = 'NORMAL_CLEARING' THEN 1 ELSE 0 END) AS [Total connected - Agent],
SUM(CASE WHEN reason_code = 'NORMAL_CLEARING' THEN 1 ELSE 0 END) AS TOTALconnected
FROM (
SELECT
c.title,
c.number_of_contacts,
cs.customer_id,
vca.customer_id AS vca_customer_id,
cs.activity_data_id,
JSON_VALUE(cs.customer, '$.campaign') AS campaign_id,
cs.activity_timestamp,
vca.message_id,
vca.channel_customer_identifier,
vca.reason_code,
vca.scheduling_meta_data
FROM campaign_scheduler cs
INNER JOIN campaigns c ON JSON_VALUE(cs.customer, '$.campaign') = c.id
LEFT JOIN voice_connector_activities vca
ON cs.activity_data_id = vca.message_id
AND CAST(cs.activity_timestamp AS DATE) = CAST(vca.activity_timestamp AS DATE)
WHERE cs.message_type = 'VOICE'
AND cs.activity_timestamp BETWEEN DATETIMEFROMPARTS(YEAR('{start_time}'), MONTH('{start_time}'), DAY('{start_time}'), 0, 0, 0, 0)
AND DATETIMEFROMPARTS(YEAR('{end_time}'), MONTH('{end_time}'), DAY('{end_time}'), 23, 59, 59, 999)
) AS A
WHERE activity_timestamp BETWEEN DATETIMEFROMPARTS(YEAR('{start_time}'), MONTH('{start_time}'), DAY('{start_time}'), 0, 0, 0, 0)
AND DATETIMEFROMPARTS(YEAR('{end_time}'), MONTH('{end_time}'), DAY('{end_time}'), 23, 59, 59, 999)
GROUP BY CAST(activity_timestamp AS DATE), title, campaign_id, customer_id
) AS B
GROUP BY DATE, [Campaign_Name], campaign_id;
""",