Campaign Summary Gold Query

Purpose

This query is extracts and insert hourly performance summaries of campaign calls into a table called campaign_summary_gold.
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
"campaign_summary_gold":"""
    INSERT INTO campaign_summary_gold (
    `DATE`,
    `Hour`,
    `Campaign_Name`, 
    campaign_id,
    `Total_Loaded`, 
    `Dialled_Unique`, 
    `Dialled_Attempts`,
    Connected,
    `No_Answer`,
    Busy,
    `Not_Connected`,
    Problem,
    `Dialer_Abandoned`,
    `Other`
)
SELECT  
    `DATE`,
    `Hour`,
    `Campaign_Name`, 
    campaign_id,
    SUM(`Total Loaded`) AS `Total_Loaded`, 
    SUM(`Dialled (Unique)`) AS `Dialled_Unique`, 
    SUM(`Dialled (Attempts)`) AS `Dialled_Attempts`,
    SUM(Connected) AS Connected,
    SUM(`No Answer`) AS `No_Answer`,
    SUM(Busy) AS Busy,
    SUM(`Not Connected`) AS `Not_Connected`,
    SUM(Problem) AS Problem,
    SUM(`Dialer Abandoned`) AS `Dialer_Abandoned`,
    SUM(`Other`) AS `Other`
FROM (
    SELECT 
        DATE(activity_timestamp) AS `DATE`,
        DATE_FORMAT(activity_timestamp, '%H:00') AS `Hour`,
        title AS `Campaign_Name`, 
        campaign_id, 
        count(distinct customer_id)  as `Total Loaded`, 
        COUNT(distinct vca_customer_id) AS `Dialled (Unique)`, 
        COUNT(message_id) AS `Dialled (Attempts)`,
        SUM(CASE WHEN reason_code = 'NORMAL_CLEARING' THEN 1 ELSE 0 END) AS Connected,
        SUM(CASE WHEN reason_code IN ('NO_USER_RESPONSE', 'NO_ANSWER', 'CALL_REJECTED') THEN 1 ELSE 0 END) AS `No Answer`,
        SUM(CASE WHEN reason_code = 'USER_BUSY' THEN 1 ELSE 0 END) AS Busy,
        SUM(CASE WHEN reason_code IN ('UNALLOCATED_NUMBER', 'ANSWER_MACHINE', 'VOICEMAIL_DETECTED', 'FAX', 'NO_RINGBACK', 'NETWORK_IVR', 'NETWORK_DISCONNECTED', 'SUBSCRIBER_ABSENT') THEN 1 ELSE 0 END) AS `Not Connected`,
        SUM(CASE WHEN reason_code IN ('SIT_TONE', 'NO_DIAL_TONE', 'SYSTEM_SHUTDOWN', 'NORMAL_TEMPORARY_FAILURE', 'RECOVERY_ON_TIMER_EXPIRE', 'SCRIPT_ERROR', 'INVALID_GATEWAY') THEN 1 ELSE 0 END) AS Problem,
        SUM(CASE WHEN reason_code IN ('ABANDONED_TO_IVR', 'SCRIPT_DEQUEUED', 'OTHER') THEN 1 ELSE 0 END) AS `Dialer Abandoned`,
        SUM(CASE WHEN reason_code NOT IN (
            'NORMAL_CLEARING', 
            'NO_USER_RESPONSE', 'NO_ANSWER', 'CALL_REJECTED', 
            'USER_BUSY', 
            'UNALLOCATED_NUMBER', 'ANSWER_MACHINE', 'VOICEMAIL_DETECTED', 'FAX', 'NO_RINGBACK', 'NETWORK_IVR', 'NETWORK_DISCONNECTED', 'SUBSCRIBER_ABSENT', 
            'SIT_TONE', 'NO_DIAL_TONE', 'SYSTEM_SHUTDOWN', 'NORMAL_TEMPORARY_FAILURE', 'RECOVERY_ON_TIMER_EXPIRE', 'SCRIPT_ERROR', 'INVALID_GATEWAY', 
            'ABANDONED_TO_IVR', 'SCRIPT_DEQUEUED', 'OTHER'
        ) THEN 1 ELSE 0 END) AS `Other`
    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, 
            vca.message_id, 
            cs.activity_timestamp,
            vca.channel_session_id, 
            vca.channel_customer_identifier, 
            vca.sender_id, 
            vca.reason_code
        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 DATE(cs.activity_timestamp) = DATE(vca.activity_timestamp)
        WHERE cs.message_type = 'VOICE' 
          AND cs.activity_timestamp BETWEEN DATE_FORMAT('{start_time}', '%Y-%m-%d %H:00:00') AND DATE_FORMAT('{end_time}', '%Y-%m-%d %H:59:59')
    ) AS A
    GROUP BY 
        DATE(activity_timestamp), 
        DATE_FORMAT(activity_timestamp, '%H:00'),
        title, 
        campaign_id, 
        customer_id
  ) AS B
  GROUP BY `DATE`, `Hour`, `Campaign_Name`, campaign_id;


    """,

-- for mssql
"campaign_summary_gold_MSSQL":"""
    INSERT INTO campaign_summary_gold (
    [DATE],
    Hour,
    [Campaign_Name], 
    campaign_id,
    [Total_Loaded], 
    [Dialled_Unique], 
    [Dialled_Attempts],
    Connected,
    [No_Answer],
    Busy,
    [Not_Connected],
    Problem,
    [Dialer_Abandoned],
    [Other]
)
SELECT  
    [DATE],
    Hour,
    [Campaign_Name], 
    campaign_id,
    SUM([Total Loaded]) AS [Total_Loaded], 
    SUM([Dialled (Unique)]) AS [Dialled_Unique], 
    SUM([Dialled (Attempts)]) AS [Dialled_Attempts],
    SUM(Connected) AS Connected,
    SUM([No Answer]) AS [No_Answer],
    SUM(Busy) AS Busy,
    SUM([Not Connected]) AS [Not_Connected],
    SUM(Problem) AS Problem,
    SUM([Dialer Abandoned]) AS [Dialer_Abandoned],
    SUM([Other]) AS Other
FROM (
    SELECT 
        CONVERT(DATE, activity_timestamp) AS [DATE],
        FORMAT(activity_timestamp, 'HH:00') AS Hour,
        title AS [Campaign_Name], 
        campaign_id, 
        COUNT(DISTINCT customer_id) AS [Total Loaded],
        COUNT(DISTINCT vca_customer_id) AS [Dialled (Unique)],
        COUNT(message_id) AS [Dialled (Attempts)],
        SUM(CASE WHEN reason_code = 'NORMAL_CLEARING' THEN 1 ELSE 0 END) AS Connected,
        SUM(CASE WHEN reason_code IN ('NO_USER_RESPONSE', 'NO_ANSWER', 'CALL_REJECTED') THEN 1 ELSE 0 END) AS [No Answer],
        SUM(CASE WHEN reason_code = 'USER_BUSY' THEN 1 ELSE 0 END) AS Busy,
        SUM(CASE WHEN reason_code IN ('UNALLOCATED_NUMBER', 'ANSWER_MACHINE', 'VOICEMAIL_DETECTED', 'FAX', 'NO_RINGBACK', 'NETWORK_IVR', 'NETWORK_DISCONNECTED', 'SUBSCRIBER_ABSENT') THEN 1 ELSE 0 END) AS [Not Connected],
        SUM(CASE WHEN reason_code IN ('SIT_TONE', 'NO_DIAL_TONE', 'SYSTEM_SHUTDOWN', 'NORMAL_TEMPORARY_FAILURE', 'RECOVERY_ON_TIMER_EXPIRE', 'SCRIPT_ERROR', 'INVALID_GATEWAY') THEN 1 ELSE 0 END) AS Problem,
        SUM(CASE WHEN reason_code IN ('ABANDONED_TO_IVR', 'SCRIPT_DEQUEUED', 'OTHER') THEN 1 ELSE 0 END) AS [Dialer Abandoned],
        SUM(CASE WHEN reason_code NOT IN (
            'NORMAL_CLEARING', 
            'NO_USER_RESPONSE', 'NO_ANSWER', 'CALL_REJECTED', 
            'USER_BUSY', 
            'UNALLOCATED_NUMBER', 'ANSWER_MACHINE', 'VOICEMAIL_DETECTED', 'FAX', 'NO_RINGBACK', 'NETWORK_IVR', 'NETWORK_DISCONNECTED', 'SUBSCRIBER_ABSENT', 
            'SIT_TONE', 'NO_DIAL_TONE', 'SYSTEM_SHUTDOWN', 'NORMAL_TEMPORARY_FAILURE', 'RECOVERY_ON_TIMER_EXPIRE', 'SCRIPT_ERROR', 'INVALID_GATEWAY', 
            'ABANDONED_TO_IVR', 'SCRIPT_DEQUEUED', 'OTHER'
        ) THEN 1 ELSE 0 END) AS [Other]
    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,
            vca.message_id,
            cs.activity_timestamp,
            vca.channel_session_id,
            vca.channel_customer_identifier,
            vca.sender_id,
            vca.reason_code
        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 FORMAT(LEFT('{start_time}', 23), 'yyyy-MM-dd HH:00:00') AND FORMAT(LEFT('{end_time}', 23), 'yyyy-MM-dd HH:59:59')
    ) AS A
    GROUP BY 
        CONVERT(DATE, activity_timestamp), 
        FORMAT(activity_timestamp, 'HH:00'),
        title, 
        campaign_id,
        customer_id
) AS B
GROUP BY [DATE], Hour, [Campaign_Name], campaign_id;

    """,