Campaign Call Details Gold Query

Purpose

This query extracts and inserts individual call details into the campaign_call_details_gold table for non-successful voice calls only (i.e., where reason_code ≠ 'NORMAL_CLEARING').

Data Source Tables

campaign_scheduler
campaigns
voice_connector_activities

For more detail of campaigns and activities tables, please refer to: Reporting Database Schema

Query:

CODE

-- for mysql

"campaign_call_details_gold":"""
    INSERT INTO campaign_call_details_gold (
    campaign_name,
    datetime,
    number_called,
    call_result
    )
   SELECT 
    c.title AS campaign_name,
    vca.activity_timestamp AS datetime,
    vca.channel_customer_identifier AS number_called,
    vca.reason_code AS call_result
   FROM campaign_scheduler cs  
  INNER JOIN campaigns c  
    ON JSON_UNQUOTE(JSON_EXTRACT(cs.customer, '$.campaign')) = c.id 
  INNER 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 vca.reason_code <> 'NORMAL_CLEARING' 
  AND vca.activity_timestamp BETWEEN '{start_time}' AND '{end_time}';

    """,

  -- For mssql
  "campaign_call_details_gold_MSSQL":"""
    INSERT INTO campaign_call_details_gold (
    campaign_name, 
    datetime, 
    number_called, 
    call_result
)
SELECT 
    c.title AS campaign_name, 
    vca.activity_timestamp AS datetime, 
    vca.channel_customer_identifier AS number_called, 
    vca.reason_code AS call_result
FROM campaign_scheduler cs  
INNER JOIN campaigns c  
    ON JSON_VALUE(cs.customer, '$.campaign') = c.id 
INNER 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 vca.reason_code <> 'NORMAL_CLEARING' 
    AND vca.activity_timestamp BETWEEN LEFT('{start_time}', 23) AND LEFT('{end_time}', 23);


    """,