Your VICIdial system generates a CDR (Call Detail Record) for every single call attempt. A 50-agent outbound operation dialing aggressively produces tens of thousands of records per day. Most call center operators treat the CDR as a logging artifact -- something for billing disputes and compliance audits. That's leaving money on the table.
The CDR is the most granular data source you have for understanding what happens between the moment a call leaves your system and the moment it connects (or doesn't). When you query it properly, you find carriers that silently fail, time windows where connect rates spike, and call patterns that separate real human answers from dead air.
Where the Data Lives
CDR data sits in the cdr table within the asteriskcdrdb database -- separate from VICIdial's vicidial_log table in the asterisk database. The CDR table has Asterisk-level detail that VICIdial's application layer doesn't surface.
Connect to it:
mysql -u cron -p asteriskcdrdb
The columns that matter most:
-
calldate-- when the call was initiated -
disposition-- ANSWERED, NO ANSWER, BUSY, or FAILED -
billsec-- seconds after answer (the real talk time) -
duration-- total call duration including ring time -
channel-- reveals which carrier trunk handled the call (e.g.,SIP/carrier1-00001a2b) -
accountcode-- usually the campaign ID -
lastapp-- the last Asterisk application that handled the call -
lastdata-- arguments to that application
The delta between duration and billsec gives you ring time: duration - billsec = ring_seconds.
Raw Connect Rate vs Real Connect Rate
A raw ANSWERED count overstates actual human connections because voicemail pickups, carrier IVRs, and answering machines all count as "answered." Filter by billsec to isolate likely human conversations:
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;
The 15-second threshold is a reasonable starting point. Lower to 10 for B2B campaigns where introductions are shorter. Raise to 20 for consumer campaigns with longer scripts. Calibrate by reviewing a sample of calls right at the boundary and checking whether they were real conversations.
Calls with billsec between 1-3 seconds marked ANSWERED are almost certainly voicemail greetings, IVRs, or machines that AMD didn't catch. Calls with billsec of 0 and ANSWERED disposition are false positives -- the carrier sent an answer signal but the call never actually connected.
Dead Air Detection
Dead air calls are where the system connected but nobody spoke -- AMD took too long, no agents were available for the bridge, or carrier latency left the caller hearing silence. They show up as ANSWERED calls with very low billsec:
SELECT calldate, dst AS number_dialed, channel, billsec,
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;
If more than 20% of your ANSWERED calls fall in the 0-3 second bucket, you have a systemic connection quality problem. Investigate AMD timing (too long to classify), agent bridge delay (all agents busy so caller hears silence), and carrier latency (audio not bridged in time).
Call Length Distribution
Break answered calls into buckets to see the shape of your operation:
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
FROM cdr
WHERE calldate >= CURDATE() AND disposition = 'ANSWERED'
GROUP BY call_bucket
ORDER BY MIN(billsec);
A healthy outbound campaign has the majority of answered calls in the short-conversation through deep-conversation range. If the 0-3 second bucket dominates, something is broken upstream.
Time-of-Day Analysis
Connect rates vary dramatically by hour. Query hourly rates over a 7-day window:
SELECT
HOUR(calldate) AS hour_of_day,
DAYOFWEEK(calldate) AS day_of_week,
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 7 DAY)
AND accountcode = 'SALESCAMP'
GROUP BY HOUR(calldate), DAYOFWEEK(calldate)
ORDER BY day_of_week, hour_of_day;
Typical B2C patterns: peak between 10 AM-12 PM and 4 PM-7 PM local time. Dead before 9 AM and after 8:30 PM. B2B: Tuesday-Thursday, 9 AM-11 AM and 1:30 PM-3:30 PM. Monday mornings and Friday afternoons are empty.
Go deeper with per-area-code analysis. Different regions answer at different times. With enough data (50+ attempts per area code per hour), you can build campaign-specific call schedules that prioritize area codes during their peak answer windows.
Carrier Performance Comparison
If you run multiple SIP trunks (and at 25+ agents, you should for redundancy and A/B testing), the CDR tells you which carriers actually deliver.
SELECT
SUBSTRING_INDEX(channel, '-', 1) AS carrier,
COUNT(*) AS total_calls,
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;
Don't just compare raw answer rates. Compare post-connect duration. A carrier with 20% answer rate but 70% of those calls lasting over 60 seconds is delivering real conversations. One with 25% answer rate but only 30% over 60 seconds is mostly delivering answering machines. The first carrier is more valuable even though its headline number looks worse.
Carrier Failure Analysis
Watch SIP response codes when disposition is FAILED. A spike in 403 (Forbidden) means the carrier may have flagged your numbers. A spike in 503 (Service Unavailable) means you're overrunning their capacity. Both need immediate attention:
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;
Building a Routine
Daily: connect rate by hour, carrier fail rate, short-call percentage. Automate with a cron job that dumps results to a file or Slack channel.
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? These slow drifts are invisible day-to-day but obvious in monthly comparisons.
The data is already there in your CDR table, accumulating thousands of records per day. Most shops never look at it. The ones that do consistently find 5-15% connect rate improvements sitting in the data waiting to be discovered.
ViciStack runs continuous CDR analysis for every client -- automated carrier scoring by human connect rate (not raw answer rate), time-of-day optimization, dead air elimination, and carrier negotiation data. Included at flat-rate pricing, $150/agent/month.
Originally published at https://vicistack.com/blog/vicidial-asterisk-cdr-analysis/
Top comments (0)