Inbound Report

Strategy 1 Query:

                SELECT 
                    vcl.phone_number as inbound_did,
                    vcl.campaign_id,
                    COALESCE(vc.campaign_name, vcl.campaign_id) as campaign_name,
                    COUNT(vcl.uniqueid) as calls_received,
                    COUNT(CASE WHEN vcl.status != 'DROP' AND vcl.length_in_sec > 0 THEN 1 END) as calls_answered,
                    COUNT(CASE WHEN vcl.status = 'DROP' OR vcl.length_in_sec = 0 THEN 1 END) as calls_dropped,
                    ROUND(AVG(CASE WHEN vcl.queue_seconds > 0 THEN vcl.queue_seconds ELSE 0 END), 2) as avg_wait_time,
                    MAX(vcl.queue_seconds) as max_wait_time,
                    MIN(CASE WHEN vcl.queue_seconds > 0 THEN vcl.queue_seconds ELSE NULL END) as min_wait_time,
                    COUNT(CASE WHEN vcl.queue_seconds <= 20 THEN 1 END) as answered_within_20,
                    COUNT(CASE WHEN vcl.queue_seconds <= 30 THEN 1 END) as answered_within_30,
                    ROUND(AVG(CASE WHEN vcl.length_in_sec > 0 THEN vcl.length_in_sec END), 2) as avg_talk_time,
                    SUM(vcl.length_in_sec) as total_talk_time,
                    COUNT(DISTINCT vcl.user) as agents_answered,
                    COUNT(CASE WHEN vcl.status = 'SALE' THEN 1 END) as sales_made,
                    ROUND(
                        CASE 
                            WHEN COUNT(vcl.uniqueid) > 0 
                            THEN (COUNT(CASE WHEN vcl.queue_seconds <= 20 THEN 1 END) * 100.0 / COUNT(vcl.uniqueid))
                            ELSE 0 
                        END, 2
                    ) as service_level_20,
                    ROUND(
                        CASE 
                            WHEN COUNT(vcl.uniqueid) > 0 
                            THEN (COUNT(CASE WHEN vcl.queue_seconds <= 30 THEN 1 END) * 100.0 / COUNT(vcl.uniqueid))
                            ELSE 0 
                        END, 2
                    ) as service_level_30,
                    ROUND(
                        CASE 
                            WHEN COUNT(vcl.uniqueid) > 0 
                            THEN (COUNT(CASE WHEN vcl.status != 'DROP' AND vcl.length_in_sec > 0 THEN 1 END) * 100.0 / COUNT(vcl.uniqueid))
                            ELSE 0 
                        END, 2
                    ) as answer_rate,
                    ROUND(
                        CASE 
                            WHEN COUNT(vcl.uniqueid) > 0 
                            THEN (COUNT(CASE WHEN vcl.status = 'DROP' OR vcl.length_in_sec = 0 THEN 1 END) * 100.0 / COUNT(vcl.uniqueid))
                            ELSE 0 
                        END, 2
                    ) as abandon_rate
                FROM vicidial_closer_log vcl
                LEFT JOIN vicidial_campaigns vc ON vcl.campaign_id = vc.campaign_id
                WHERE DATE(vcl.call_date) BETWEEN ? AND ?
                GROUP BY vcl.phone_number, vcl.campaign_id
                HAVING calls_received > 0
                ORDER BY calls_received DESC
                LIMIT 50
            

Has queue_seconds: Yes