Outbound Report

Main Query Debug:

        SELECT 
            vl.campaign_id,
            COALESCE(vc.campaign_name, vl.campaign_id) as campaign_name,
            COUNT(vl.uniqueid) as total_dials,
            COUNT(CASE WHEN vl.status IN ('SALE', 'A', 'B', 'C') THEN 1 END) as contacts,
            COUNT(CASE WHEN vl.status = 'DROP' THEN 1 END) as drops,
            COUNT(CASE WHEN vl.status = 'NA' THEN 1 END) as no_answers,
            COUNT(CASE WHEN vl.status = 'BUSY' THEN 1 END) as busy_signals,
            COUNT(CASE WHEN vl.status = 'AM' THEN 1 END) as answering_machines,
            COUNT(CASE WHEN vl.status = 'SALE' THEN 1 END) as sales,
            COUNT(CASE WHEN vl.status = 'CALLBK' THEN 1 END) as callback_set,
            COUNT(CASE WHEN vl.status = 'DNC' THEN 1 END) as dnc_requests,
            ROUND(AVG(CASE WHEN vl.length_in_sec > 0 THEN vl.length_in_sec END), 2) as avg_talk_time,
            SUM(vl.length_in_sec) as total_talk_time,
            COUNT(DISTINCT vl.user) as agents_dialing,
            COUNT(DISTINCT vl.phone_number) as unique_numbers,
            ROUND(
                CASE 
                    WHEN COUNT(vl.uniqueid) > 0 
                    THEN (COUNT(CASE WHEN vl.status IN ('SALE', 'A', 'B', 'C') THEN 1 END) * 100.0 / COUNT(vl.uniqueid))
                    ELSE 0 
                END, 2
            ) as contact_rate,
            ROUND(
                CASE 
                    WHEN COUNT(CASE WHEN vl.status IN ('SALE', 'A', 'B', 'C') THEN 1 END) > 0 
                    THEN (COUNT(CASE WHEN vl.status = 'SALE' THEN 1 END) * 100.0 / COUNT(CASE WHEN vl.status IN ('SALE', 'A', 'B', 'C') THEN 1 END))
                    ELSE 0 
                END, 2
            ) as conversion_rate,
            ROUND(
                CASE 
                    WHEN COUNT(vl.uniqueid) > 0 
                    THEN (COUNT(CASE WHEN vl.status = 'DROP' THEN 1 END) * 100.0 / COUNT(vl.uniqueid))
                    ELSE 0 
                END, 2
            ) as drop_rate,
            ROUND(
                CASE 
                    WHEN COUNT(DISTINCT vl.user) > 0 
                    THEN (COUNT(vl.uniqueid) * 1.0 / COUNT(DISTINCT vl.user))
                    ELSE 0 
                END, 2
            ) as dials_per_agent,
            MIN(vl.call_date) as first_call,
            MAX(vl.call_date) as last_call
        FROM vicidial_log vl
        LEFT JOIN vicidial_campaigns vc ON vl.campaign_id = vc.campaign_id
        WHERE DATE(vl.call_date) BETWEEN ? AND ?
        GROUP BY vl.campaign_id
        HAVING total_dials > 0
        ORDER BY total_dials DESC
        LIMIT 50
    

Parameters: 2026-06-14, 2026-07-14