Calls data

--select * from call_normalized_non_variant limit 100
--select distinct is_voicemail from call_normalized_non_variant

WITH pilot_locations AS (
    SELECT
        location_id,
        voice_ai_platform,
        ai_enablement_date
    FROM VALUES
        -- Uniti: Round 1 (5)
        (11273, 'Uniti', DATE '2026-03-25'),
        (11448, 'Uniti', DATE '2026-03-25'),
        (11334, 'Uniti', DATE '2026-03-25'),

        -- Uniti: Round 2 (10)
        (15373, 'Uniti', DATE '2026-06-09'),
        (11399, 'Uniti', DATE '2026-06-09'),
        (11418, 'Uniti', DATE '2026-06-09'),
        (17412, 'Uniti', DATE '2026-06-09'),
        (11300, 'Uniti', DATE '2026-06-09'),
        (11395, 'Uniti', DATE '2026-06-09'),
        (11317, 'Uniti', DATE '2026-06-09'),
        (11472, 'Uniti', DATE '2026-06-09'),
        (11376, 'Uniti', DATE '2026-06-09'),
        (11312, 'Uniti', DATE '2026-06-09'),
        (11428, 'Uniti', DATE '2026-06-09'),
        (11298, 'Uniti', DATE '2026-06-09'),

        -- Lumio: Round 1 (5)
        (11311, 'Lumio', DATE '2026-03-11'),
        (11346, 'Lumio', DATE '2026-03-11'),
        (11406, 'Lumio', DATE '2026-03-11'),
        (11325, 'Lumio', DATE '2026-03-11'),
        (11359, 'Lumio', DATE '2026-03-11'),

        -- Lumio: Round 2 (10)
        (11463, 'Lumio', DATE '2026-06-12'),
        (13668, 'Lumio', DATE '2026-06-12'),
        (17409, 'Lumio', DATE '2026-06-12'),
        (11424, 'Lumio', DATE '2026-06-12'),
        (11293, 'Lumio', DATE '2026-06-12'),
        (17425, 'Lumio', DATE '2026-06-12'),
        (11388, 'Lumio', DATE '2026-06-12'),
        (12440, 'Lumio', DATE '2026-06-12'),
        (11464, 'Lumio', DATE '2026-06-12'),
        (17430, 'Lumio', DATE '2026-06-12')
    AS t(location_id, voice_ai_platform, ai_enablement_date)
)

SELECT
    l.location_id,
    l.location_name,

    /* Platform is only active on/after enablement. */
    COALESCE(
        IFF(c.datestamp::DATE >= p.ai_enablement_date, p.voice_ai_platform, NULL),
        'Control'
    ) AS voice_ai_platform,

    /* A pilot location behaves as Control until its actual launch date. */
    CASE
        WHEN c.datestamp::DATE >= p.ai_enablement_date THEN 'Variant'
        ELSE 'Control'
    END AS test_group,
    CASE WHEN C.CALL_TYPE LIKE 'inbound%' THEN 'Inbound' WHEN C.CALL_TYPE LIKE 'outbound%' THEN 'Outbound' ELSE '' END AS call_type,
    c.datestamp::DATE AS call_date,
    COUNT(c.log_id) AS total_calls_count,
    SUM(C.CALL_DURATION) AS TOTAL_CALLS_DURATION,
    SUM(CASE WHEN IS_VOICEMAIL IS NOT NULL THEN 1 ELSE 0 END) AS VOICEMAIL_COUNT,
    COUNT(CASE WHEN C.CALL_DURATION >= 5 THEN C.LOG_ID END) AS CALLS_GREATER_THAN_5_SEC_COUNT,
    SUM(CASE WHEN C.CALL_DURATION >= 5 THEN C.CALL_DURATION END) AS CALLS_GREATER_THAN_5_SEC_DURATION,
    --COUNT(CASE WHEN C.CALL_DURATION >= 10 THEN C.LOG_ID END) AS CALLS_GREATER_THAN_10_SEC_COUNT,
    --SUM(CASE WHEN C.CALL_DURATION >= 10 THEN C.CALL_DURATION END) AS CALLS_GREATER_THAN_10_SEC_DURATION,

    /*
      Escalated AI calls:
      - Location must be enabled as of this specific call date.
      - The ad must map to the platform assigned to that facility.
    */
    SUM(
        CASE
            WHEN c.datestamp::DATE >= p.ai_enablement_date
             AND ads.ad_name ILIKE p.voice_ai_platform || '%'  
            THEN 1
            ELSE 0
        END
    ) AS escalated_call_count,
    SUM(
        CASE
            WHEN c.datestamp::DATE >= p.ai_enablement_date
             AND ads.ad_name ILIKE p.voice_ai_platform || '%'  
            THEN C.CALL_DURATION
            ELSE 0
        END
    ) AS escalated_call_DURATION,
    SUM(
        CASE
            WHEN c.datestamp::DATE >= p.ai_enablement_date
             AND ads.ad_name ILIKE p.voice_ai_platform || '%'  
             AND C.CALL_DURATION >= 5
            THEN 1
            ELSE 0
        END
    ) AS escalated_call_count_GREATER_THAN_5_SEC,
    SUM(
        CASE
            WHEN c.datestamp::DATE >= p.ai_enablement_date
             AND ads.ad_name ILIKE p.voice_ai_platform || '%' 
             AND C.CALL_DURATION >= 5
            THEN C.CALL_DURATION
            ELSE 0
        END
    ) AS escalated_call_DURATION_GREATER_THAN_5_SEC,
   /*  
  SUM(
        CASE
            WHEN c.datestamp::DATE >= p.ai_enablement_date
             AND ads.ad_name ILIKE p.voice_ai_platform || '%'  
             AND C.CALL_DURATION >= 10
            THEN 1
            ELSE 0
        END
    ) AS escalated_call_count_GREATER_THAN_10_SEC,

   SUM(
        CASE
            WHEN c.datestamp::DATE >= p.ai_enablement_date
             AND ads.ad_name ILIKE p.voice_ai_platform || '%' 
             AND C.CALL_DURATION >= 10
            THEN C.CALL_DURATION
            ELSE 0
        END
    ) AS escalated_call_DURATION_GREATER_THAN_10_SEC,
*/
    /*
      Non-AI calls at an enabled pilot location:
      - No ad attribution, or
      - An ad that is not attributed to either AI platform.
    */
    /*
    SUM(
        CASE
            WHEN c.datestamp::DATE >= p.ai_enablement_date
             AND (
                 ads.ad_name IS NULL
                 OR (
                     ads.ad_name NOT ILIKE 'Uniti%'
                     AND ads.ad_name NOT ILIKE 'Lumio%'
                 )
             )
            THEN 1
            ELSE 0
        END
    ) AS ai_call_count
    */
    
FROM call_normalized_non_variant c
JOIN locations l
    ON c.location_id = l.location_id
LEFT JOIN pilot_locations p
    ON c.location_id = p.location_id
LEFT JOIN trackingnumbers tn
    ON REPLACE(c.call_name, '+', '') = tn.call_number
LEFT JOIN ads
    ON tn.ad_id = ads.ad_id
WHERE c.datestamp::DATE >= DATE '2025-01-01'
GROUP BY 1, 2, 3, 4, 5, 6  --ALL
ORDER BY 6, 2;