Campaign Summary Report

Main Query Debug:

        SELECT 
            vl.campaign_id,
            COALESCE(vc.campaign_name, vl.campaign_id) as campaign_name,
            COALESCE(vc.campaign_description, 'No description') as campaign_description,
            COUNT(vl.uniqueid) as total_calls,
            COUNT(CASE WHEN vl.status IN ('SALE', 'A', 'B', 'C') THEN 1 END) as contacts,
            COUNT(CASE WHEN vl.status = 'SALE' THEN 1 END) as sales_made,
            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 = 'CALLBK' THEN 1 END) as callbacks_set,
            ROUND(AVG(CASE WHEN vl.length_in_sec > 0 THEN vl.length_in_sec END), 2) as avg_call_time,
            SUM(vl.length_in_sec) as total_talk_time,
            COUNT(DISTINCT vl.user) as agents_used,
            COUNT(DISTINCT vl.phone_number) as unique_leads,
            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_percentage,
            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(DISTINCT vl.user) > 0 
                    THEN (COUNT(vl.uniqueid) * 1.0 / COUNT(DISTINCT vl.user))
                    ELSE 0 
                END, 2
            ) as calls_per_agent,
            MIN(vl.call_date) as first_call_date,
            MAX(vl.call_date) as last_call_date
        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_calls > 0
        ORDER BY total_calls DESC
    

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