mysql -u cron -p asteriskcdrdb
mysql -u cron -p asteriskcdrdb
mysql -u cron -p asteriskcdrdb
SELECT duration - billsec AS ring_seconds, billsec AS talk_seconds, disposition
FROM cdr
WHERE calldate >= '2026-03-19 00:00:00' AND disposition = 'ANSWERED'
LIMIT 20;
SELECT duration - billsec AS ring_seconds, billsec AS talk_seconds, disposition
FROM cdr
WHERE calldate >= '2026-03-19 00:00:00' AND disposition = 'ANSWERED'
LIMIT 20;
SELECT duration - billsec AS ring_seconds, billsec AS talk_seconds, disposition
FROM cdr
WHERE calldate >= '2026-03-19 00:00:00' AND disposition = 'ANSWERED'
LIMIT 20;
SIP/carrier1-00001a2b
SIP/vicitrunk02-00003c4d
PJSIP/outbound-0000ef01
SIP/carrier1-00001a2b
SIP/vicitrunk02-00003c4d
PJSIP/outbound-0000ef01
SIP/carrier1-00001a2b
SIP/vicitrunk02-00003c4d
PJSIP/outbound-0000ef01
SELECT accountcode AS campaign, COUNT(*) AS total_attempts, SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) AS answered, SUM(CASE WHEN disposition = 'NO ANSWER' THEN 1 ELSE 0 END) AS no_answer, SUM(CASE WHEN disposition = 'BUSY' THEN 1 ELSE 0 END) AS busy, SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) AS failed, ROUND(SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS connect_rate_pct
FROM cdr
WHERE calldate >= '2026-03-18 00:00:00' AND calldate < '2026-03-19 00:00:00'
GROUP BY accountcode
ORDER BY connect_rate_pct DESC;
SELECT accountcode AS campaign, COUNT(*) AS total_attempts, SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) AS answered, SUM(CASE WHEN disposition = 'NO ANSWER' THEN 1 ELSE 0 END) AS no_answer, SUM(CASE WHEN disposition = 'BUSY' THEN 1 ELSE 0 END) AS busy, SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) AS failed, ROUND(SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS connect_rate_pct
FROM cdr
WHERE calldate >= '2026-03-18 00:00:00' AND calldate < '2026-03-19 00:00:00'
GROUP BY accountcode
ORDER BY connect_rate_pct DESC;
SELECT accountcode AS campaign, COUNT(*) AS total_attempts, SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) AS answered, SUM(CASE WHEN disposition = 'NO ANSWER' THEN 1 ELSE 0 END) AS no_answer, SUM(CASE WHEN disposition = 'BUSY' THEN 1 ELSE 0 END) AS busy, SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) AS failed, ROUND(SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS connect_rate_pct
FROM cdr
WHERE calldate >= '2026-03-18 00:00:00' AND calldate < '2026-03-19 00:00:00'
GROUP BY accountcode
ORDER BY connect_rate_pct DESC;
SELECT accountcode AS campaign, COUNT(*) AS total_attempts, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) AS human_connects, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec BETWEEN 1 AND 14 THEN 1 ELSE 0 END) AS short_answers, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec = 0 THEN 1 ELSE 0 END) AS zero_sec_answers, ROUND( SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS human_connect_rate_pct
FROM cdr
WHERE calldate >= CURDATE()
GROUP BY accountcode
ORDER BY human_connect_rate_pct DESC;
SELECT accountcode AS campaign, COUNT(*) AS total_attempts, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) AS human_connects, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec BETWEEN 1 AND 14 THEN 1 ELSE 0 END) AS short_answers, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec = 0 THEN 1 ELSE 0 END) AS zero_sec_answers, ROUND( SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS human_connect_rate_pct
FROM cdr
WHERE calldate >= CURDATE()
GROUP BY accountcode
ORDER BY human_connect_rate_pct DESC;
SELECT accountcode AS campaign, COUNT(*) AS total_attempts, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) AS human_connects, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec BETWEEN 1 AND 14 THEN 1 ELSE 0 END) AS short_answers, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec = 0 THEN 1 ELSE 0 END) AS zero_sec_answers, ROUND( SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS human_connect_rate_pct
FROM cdr
WHERE calldate >= CURDATE()
GROUP BY accountcode
ORDER BY human_connect_rate_pct DESC;
SELECT calldate, dst AS number_dialed, channel, billsec, duration, duration - billsec AS ring_time
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'ANSWERED' AND billsec BETWEEN 1 AND 4
ORDER BY calldate DESC
LIMIT 100;
SELECT calldate, dst AS number_dialed, channel, billsec, duration, duration - billsec AS ring_time
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'ANSWERED' AND billsec BETWEEN 1 AND 4
ORDER BY calldate DESC
LIMIT 100;
SELECT calldate, dst AS number_dialed, channel, billsec, duration, duration - billsec AS ring_time
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'ANSWERED' AND billsec BETWEEN 1 AND 4
ORDER BY calldate DESC
LIMIT 100;
SELECT CASE WHEN billsec = 0 THEN '0 sec (no audio)' WHEN billsec BETWEEN 1 AND 3 THEN '1-3 sec (dead air/hangup)' WHEN billsec BETWEEN 4 AND 10 THEN '4-10 sec (quick reject)' WHEN billsec BETWEEN 11 AND 30 THEN '11-30 sec (short conversation)' WHEN billsec BETWEEN 31 AND 120 THEN '31-120 sec (pitch attempt)' WHEN billsec BETWEEN 121 AND 300 THEN '2-5 min (engaged call)' WHEN billsec > 300 THEN '5+ min (deep conversation)' END AS call_bucket, COUNT(*) AS call_count, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM cdr WHERE calldate >= CURDATE() AND disposition = 'ANSWERED'), 1) AS pct
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'ANSWERED'
GROUP BY call_bucket
ORDER BY MIN(billsec);
SELECT CASE WHEN billsec = 0 THEN '0 sec (no audio)' WHEN billsec BETWEEN 1 AND 3 THEN '1-3 sec (dead air/hangup)' WHEN billsec BETWEEN 4 AND 10 THEN '4-10 sec (quick reject)' WHEN billsec BETWEEN 11 AND 30 THEN '11-30 sec (short conversation)' WHEN billsec BETWEEN 31 AND 120 THEN '31-120 sec (pitch attempt)' WHEN billsec BETWEEN 121 AND 300 THEN '2-5 min (engaged call)' WHEN billsec > 300 THEN '5+ min (deep conversation)' END AS call_bucket, COUNT(*) AS call_count, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM cdr WHERE calldate >= CURDATE() AND disposition = 'ANSWERED'), 1) AS pct
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'ANSWERED'
GROUP BY call_bucket
ORDER BY MIN(billsec);
SELECT CASE WHEN billsec = 0 THEN '0 sec (no audio)' WHEN billsec BETWEEN 1 AND 3 THEN '1-3 sec (dead air/hangup)' WHEN billsec BETWEEN 4 AND 10 THEN '4-10 sec (quick reject)' WHEN billsec BETWEEN 11 AND 30 THEN '11-30 sec (short conversation)' WHEN billsec BETWEEN 31 AND 120 THEN '31-120 sec (pitch attempt)' WHEN billsec BETWEEN 121 AND 300 THEN '2-5 min (engaged call)' WHEN billsec > 300 THEN '5+ min (deep conversation)' END AS call_bucket, COUNT(*) AS call_count, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM cdr WHERE calldate >= CURDATE() AND disposition = 'ANSWERED'), 1) AS pct
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'ANSWERED'
GROUP BY call_bucket
ORDER BY MIN(billsec);
-- Calls that lasted under 2 seconds post-answer with no agent bridge
SELECT c.calldate, c.dst, c.channel, c.billsec, c.disposition
FROM asteriskcdrdb.cdr c
WHERE c.calldate >= CURDATE() AND c.disposition = 'ANSWERED' AND c.billsec <= 2 AND c.dstchannel = ''
ORDER BY c.calldate DESC;
-- Calls that lasted under 2 seconds post-answer with no agent bridge
SELECT c.calldate, c.dst, c.channel, c.billsec, c.disposition
FROM asteriskcdrdb.cdr c
WHERE c.calldate >= CURDATE() AND c.disposition = 'ANSWERED' AND c.billsec <= 2 AND c.dstchannel = ''
ORDER BY c.calldate DESC;
-- Calls that lasted under 2 seconds post-answer with no agent bridge
SELECT c.calldate, c.dst, c.channel, c.billsec, c.disposition
FROM asteriskcdrdb.cdr c
WHERE c.calldate >= CURDATE() AND c.disposition = 'ANSWERED' AND c.billsec <= 2 AND c.dstchannel = ''
ORDER BY c.calldate DESC;
SELECT HOUR(calldate) AS hour_of_day, DAYOFWEEK(calldate) AS day_of_week, COUNT(*) AS attempts, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) AS human_connects, ROUND( SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS connect_rate
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND accountcode = 'SALESCAMP'
GROUP BY HOUR(calldate), DAYOFWEEK(calldate)
ORDER BY day_of_week, hour_of_day;
SELECT HOUR(calldate) AS hour_of_day, DAYOFWEEK(calldate) AS day_of_week, COUNT(*) AS attempts, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) AS human_connects, ROUND( SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS connect_rate
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND accountcode = 'SALESCAMP'
GROUP BY HOUR(calldate), DAYOFWEEK(calldate)
ORDER BY day_of_week, hour_of_day;
SELECT HOUR(calldate) AS hour_of_day, DAYOFWEEK(calldate) AS day_of_week, COUNT(*) AS attempts, SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) AS human_connects, ROUND( SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS connect_rate
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND accountcode = 'SALESCAMP'
GROUP BY HOUR(calldate), DAYOFWEEK(calldate)
ORDER BY day_of_week, hour_of_day;
SELECT LEFT(dst, 3) AS area_code, HOUR(calldate) AS hour_of_day, COUNT(*) AS attempts, ROUND( SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS connect_rate
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 14 DAY) AND accountcode = 'SALESCAMP' AND LENGTH(dst) >= 10
GROUP BY LEFT(dst, 3), HOUR(calldate)
HAVING attempts >= 50
ORDER BY area_code, hour_of_day;
SELECT LEFT(dst, 3) AS area_code, HOUR(calldate) AS hour_of_day, COUNT(*) AS attempts, ROUND( SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS connect_rate
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 14 DAY) AND accountcode = 'SALESCAMP' AND LENGTH(dst) >= 10
GROUP BY LEFT(dst, 3), HOUR(calldate)
HAVING attempts >= 50
ORDER BY area_code, hour_of_day;
SELECT LEFT(dst, 3) AS area_code, HOUR(calldate) AS hour_of_day, COUNT(*) AS attempts, ROUND( SUM(CASE WHEN disposition = 'ANSWERED' AND billsec >= 15 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS connect_rate
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 14 DAY) AND accountcode = 'SALESCAMP' AND LENGTH(dst) >= 10
GROUP BY LEFT(dst, 3), HOUR(calldate)
HAVING attempts >= 50
ORDER BY area_code, hour_of_day;
SELECT SUBSTRING_INDEX(channel, '-', 1) AS carrier, COUNT(*) AS total_calls, SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) AS answered, SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) AS failed, ROUND(SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS answer_rate, ROUND(SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS fail_rate, ROUND(AVG(CASE WHEN disposition = 'ANSWERED' THEN billsec ELSE NULL END), 1) AS avg_talk_sec
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY SUBSTRING_INDEX(channel, '-', 1)
ORDER BY answer_rate DESC;
SELECT SUBSTRING_INDEX(channel, '-', 1) AS carrier, COUNT(*) AS total_calls, SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) AS answered, SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) AS failed, ROUND(SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS answer_rate, ROUND(SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS fail_rate, ROUND(AVG(CASE WHEN disposition = 'ANSWERED' THEN billsec ELSE NULL END), 1) AS avg_talk_sec
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY SUBSTRING_INDEX(channel, '-', 1)
ORDER BY answer_rate DESC;
SELECT SUBSTRING_INDEX(channel, '-', 1) AS carrier, COUNT(*) AS total_calls, SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) AS answered, SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) AS failed, ROUND(SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS answer_rate, ROUND(SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS fail_rate, ROUND(AVG(CASE WHEN disposition = 'ANSWERED' THEN billsec ELSE NULL END), 1) AS avg_talk_sec
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY SUBSTRING_INDEX(channel, '-', 1)
ORDER BY answer_rate DESC;
SELECT SUBSTRING_INDEX(channel, '-', 1) AS carrier, COUNT(*) AS answered_calls, ROUND(AVG(billsec), 1) AS avg_billsec, ROUND(STDDEV(billsec), 1) AS stddev_billsec, SUM(CASE WHEN billsec >= 60 THEN 1 ELSE 0 END) AS calls_over_60s, ROUND(SUM(CASE WHEN billsec >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_over_60s
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND disposition = 'ANSWERED' AND billsec > 0
GROUP BY SUBSTRING_INDEX(channel, '-', 1)
ORDER BY avg_billsec DESC;
SELECT SUBSTRING_INDEX(channel, '-', 1) AS carrier, COUNT(*) AS answered_calls, ROUND(AVG(billsec), 1) AS avg_billsec, ROUND(STDDEV(billsec), 1) AS stddev_billsec, SUM(CASE WHEN billsec >= 60 THEN 1 ELSE 0 END) AS calls_over_60s, ROUND(SUM(CASE WHEN billsec >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_over_60s
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND disposition = 'ANSWERED' AND billsec > 0
GROUP BY SUBSTRING_INDEX(channel, '-', 1)
ORDER BY avg_billsec DESC;
SELECT SUBSTRING_INDEX(channel, '-', 1) AS carrier, COUNT(*) AS answered_calls, ROUND(AVG(billsec), 1) AS avg_billsec, ROUND(STDDEV(billsec), 1) AS stddev_billsec, SUM(CASE WHEN billsec >= 60 THEN 1 ELSE 0 END) AS calls_over_60s, ROUND(SUM(CASE WHEN billsec >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_over_60s
FROM cdr
WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND disposition = 'ANSWERED' AND billsec > 0
GROUP BY SUBSTRING_INDEX(channel, '-', 1)
ORDER BY avg_billsec DESC;
SELECT SUBSTRING_INDEX(channel, '-', 1) AS carrier, lastdata, COUNT(*) AS occurrences
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'FAILED'
GROUP BY SUBSTRING_INDEX(channel, '-', 1), lastdata
ORDER BY occurrences DESC
LIMIT 30;
SELECT SUBSTRING_INDEX(channel, '-', 1) AS carrier, lastdata, COUNT(*) AS occurrences
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'FAILED'
GROUP BY SUBSTRING_INDEX(channel, '-', 1), lastdata
ORDER BY occurrences DESC
LIMIT 30;
SELECT SUBSTRING_INDEX(channel, '-', 1) AS carrier, lastdata, COUNT(*) AS occurrences
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'FAILED'
GROUP BY SUBSTRING_INDEX(channel, '-', 1), lastdata
ORDER BY occurrences DESC
LIMIT 30;
#!/bin/bash
# /opt/scripts/daily_cdr_report.sh
MYSQL_CMD="mysql -u cron -p'yourpass' asteriskcdrdb -N -B" echo "=== Daily CDR Report $(date +%Y-%m-%d) ==="
echo ""
echo "--- Connect Rate by Campaign ---"
$MYSQL_CMD -e "
SELECT accountcode, COUNT(*) AS attempts, ROUND(SUM(CASE WHEN disposition='ANSWERED' AND billsec>=15 THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS human_connect_pct
FROM cdr WHERE calldate >= CURDATE() - INTERVAL 1 DAY AND calldate < CURDATE()
GROUP BY accountcode ORDER BY human_connect_pct DESC;
" echo ""
echo "--- Carrier Fail Rates ---"
$MYSQL_CMD -e "
SELECT SUBSTRING_INDEX(channel,'-',1) AS carrier, COUNT(*) AS calls, ROUND(SUM(CASE WHEN disposition='FAILED' THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS fail_pct
FROM cdr WHERE calldate >= CURDATE() - INTERVAL 1 DAY AND calldate < CURDATE()
GROUP BY carrier ORDER BY fail_pct DESC;
"
#!/bin/bash
# /opt/scripts/daily_cdr_report.sh
MYSQL_CMD="mysql -u cron -p'yourpass' asteriskcdrdb -N -B" echo "=== Daily CDR Report $(date +%Y-%m-%d) ==="
echo ""
echo "--- Connect Rate by Campaign ---"
$MYSQL_CMD -e "
SELECT accountcode, COUNT(*) AS attempts, ROUND(SUM(CASE WHEN disposition='ANSWERED' AND billsec>=15 THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS human_connect_pct
FROM cdr WHERE calldate >= CURDATE() - INTERVAL 1 DAY AND calldate < CURDATE()
GROUP BY accountcode ORDER BY human_connect_pct DESC;
" echo ""
echo "--- Carrier Fail Rates ---"
$MYSQL_CMD -e "
SELECT SUBSTRING_INDEX(channel,'-',1) AS carrier, COUNT(*) AS calls, ROUND(SUM(CASE WHEN disposition='FAILED' THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS fail_pct
FROM cdr WHERE calldate >= CURDATE() - INTERVAL 1 DAY AND calldate < CURDATE()
GROUP BY carrier ORDER BY fail_pct DESC;
"
#!/bin/bash
# /opt/scripts/daily_cdr_report.sh
MYSQL_CMD="mysql -u cron -p'yourpass' asteriskcdrdb -N -B" echo "=== Daily CDR Report $(date +%Y-%m-%d) ==="
echo ""
echo "--- Connect Rate by Campaign ---"
$MYSQL_CMD -e "
SELECT accountcode, COUNT(*) AS attempts, ROUND(SUM(CASE WHEN disposition='ANSWERED' AND billsec>=15 THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS human_connect_pct
FROM cdr WHERE calldate >= CURDATE() - INTERVAL 1 DAY AND calldate < CURDATE()
GROUP BY accountcode ORDER BY human_connect_pct DESC;
" echo ""
echo "--- Carrier Fail Rates ---"
$MYSQL_CMD -e "
SELECT SUBSTRING_INDEX(channel,'-',1) AS carrier, COUNT(*) AS calls, ROUND(SUM(CASE WHEN disposition='FAILED' THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS fail_pct
FROM cdr WHERE calldate >= CURDATE() - INTERVAL 1 DAY AND calldate < CURDATE()
GROUP BY carrier ORDER BY fail_pct DESC;
" - ANSWERED — The remote side sent a 200 OK / answer signal. This includes human answers, voicemail pickups, IVR systems, and answering machines.
- NO ANSWER — Ring timeout expired with no answer. Your dial timeout in the dialplan controls how long Asterisk waits.
- BUSY — Remote side returned a 486 Busy Here or equivalent.
- FAILED — Call could not be placed. Trunk error, invalid number, network failure, or all circuits busy. - Dial — Normal outbound call
- Hangup — Call was explicitly hung up
- AGI — Call went through an AGI script (common for AMD)
- Playback — Audio was played (voicemail drop, message blasting)
- Park — Call was parked - AMD timing — If AMD takes too long to classify, the human hangs up during the "hello? hello?" phase.
- Agent bridge delay — The time between AMD classifying "HUMAN" and the call reaching an available agent. If all agents are busy, the caller hears silence and hangs up.
- Carrier latency — Some carriers introduce audio delay. The caller answers, says hello, hears nothing (because audio hasn't bridged), and hangs up. - B2C: Peak connect rates between 10 AM-12 PM and 4 PM-7 PM local time. Worst rates before 9 AM and after 8:30 PM.
- B2B: Best connect rates Tuesday-Thursday, 9 AM-11 AM and 1:30 PM-3:30 PM. Monday mornings and Friday afternoons are dead zones. - Daily: Connect rate by hour, carrier fail rate, short-call percentage
- Weekly: Carrier comparison, area code timing patterns, call duration distribution
- Monthly: Trend analysis — are connect rates improving or declining? Is a carrier degrading over time? - Automated carrier scoring — We continuously rank your trunks by human connect rate, not just raw answer rate, and route traffic accordingly
- Time-of-day optimization — We build custom dialing schedules based on your specific list demographics and CDR patterns
- Dead air elimination — We tune AMD sensitivity, agent bridge timing, and trunk selection to minimize the 1-4 second answered calls that burn through your list
- Carrier negotiation leverage — When CDR data proves a carrier is underperforming, we provide the data you need for rate negotiations or replacement decisions - VICIdial Real-Time Agent Dashboard Customization Guide — Visualize CDR-derived metrics in real-time wallboards
- VICIdial Auto-Dial Level Tuning by Campaign Type — Use connect rate data to inform dial level decisions
- VICIdial Voicemail Drop Configuration and Compliance Guide — AMD accuracy directly impacts CDR-based connect rate calculations
