Tools: ViciDial Lead Recycling & List Management Best Practices - Complete Guide
ViciDial Lead Recycling & List Management Best Practices
Prerequisites
Understanding Lead Lifecycle in ViciDial
Lead Status Codes and Their Meanings
The Recycling Decision Tree
Database Schema for Recycling Management
Key Tables
Core Recycling Tables
Configuring Lead Recycling Strategies
Method 1: Campaign-Level Automatic Recycling
Method 2: Status-Based Recycling Rules
Method 3: Custom Recycling with AGI Scripts
Implementing Intelligent List Management
List Segmentation Strategy
Automatic List Purging and Archive
Dynamic Callback Scheduling
Monitoring and Reporting Lead Recycling
Real-Time Recycling Dashboard Query
Recycling Effectiveness Metrics
Lead Aging Report
Configuring Recycling via CLI Commands
Bulk Recycle Operations
Check Recycling Queue Status
Force Immediate Recycle
Advanced: Time-Zone Aware Recycling
Best Practices and Optimization
Lead Fatigue Prevention
Quality Assurance
Database Maintenance
Troubleshooting Lead Recycling Issues
Issue: Leads Not Recycling
Issue: Recycled Leads Not Dialing
Issue: Excessive Recycling / Lead Fatigue
Issue: Recycling Causing Database Lock
Summary Master advanced lead recycling strategies, implement intelligent list management workflows, and optimize your ViciDial contact center performance with production-tested configurations and real-world examples. Before implementing the strategies in this tutorial, ensure you have: Verify your installation: Lead recycling is the process of requeuing contacts after specific outcomes, allowing multiple attempts with different agents, strategies, or time intervals. Understanding the lead lifecycle is foundational to effective recycling. ViciDial uses standardized status codes stored in the vicidial_log table. Key statuses relevant to recycling: Understanding the database structure is essential for custom recycling logic. vicidial_list — Contains lead master records: Key fields for recycling: vicidial_log — Call attempt history: vicidial_campaign — Campaign configuration: Critical recycling fields: Access the campaign configuration and enable recycling globally: In the database, verify campaign recycling configuration: Update recycling method: Create granular recycling behavior through status configuration in vicidial_closer_log: This table determines which statuses trigger recycling. Insert recycling rules: For advanced recycling logic, implement AGI (Asterisk Gateway Interface) scripts. Create a custom recycling script: Call this AGI from your dialplan: Separate leads into logical groups for targeted recycling: Prevent list bloat by archiving old, unrecyclable leads: Implement time-zone aware callbacks for optimal contact: Monitor active recycling in real-time: Measure recycling performance: Identify leads requiring urgency or removal: Recycle entire batches of leads using command-line operations: Verify pending recycled leads: Manually trigger recycle for specific leads: Handle multi-zone campaigns properly: Avoid over-calling contacts: Monitor recycling quality metrics: Regular optimization for recycling performance: Symptom: Status remains unchanged after expected recycle interval. Symptom: RECYCLE status leads remain in queue but don't get dialed. Symptom: Leads dialed repeatedly in short intervals, agents complaining about repetition. Symptom: Database queries slow, connection timeout errors, agents report lag. ViciDial lead recycling is a sophisticated system requiring careful planning and ongoing optimization. The key takeaways: Understand Status Flow: Different statuses recycle at different intervals; configure per vicidial_closer_log. Database-Driven Configuration: Recycling behavior lives in vicidial_list, vicidial_log, and vicidial_campaign tables—customize via SQL. Time-Zone Awareness: Use gmt_offset_now and last_local_call_time to avoid off-hours contact. Prevent Lead Fatigue: Set attempt limits, monitor call frequency, and archive over-contacted leads. Monitor Performance: Track recycle-to-sale conversion rates, queue depths, and recycling effectiveness. Automate Maintenance: Use cron jobs and stored procedures to archive old leads, optimize tables, and maintain database health. Test Before Deploying: Always back up your database and test recycling changes in a staging environment first. Document Custom Logic: If implementing AGI scripts or complex procedures, maintain clear documentation for future maintenance. Effective lead recycling balances outreach frequency with lead quality, agent productivity, and customer satisfaction. Use the SQL queries and scripts provided here as templates, adjusting parameters for your specific campaign requirements. Templates let you quickly answer FAQs or store snippets for re-use. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse
mysql -u root -p asterisk -e "SELECT VERSION();"
asterisk -v
grep "vicidial" /etc/asterisk/extensions-vicidial.conf | head -5
mysql -u root -p asterisk -e "SELECT VERSION();"
asterisk -v
grep "vicidial" /etc/asterisk/extensions-vicidial.conf | head -5
mysql -u root -p asterisk -e "SELECT VERSION();"
asterisk -v
grep "vicidial" /etc/asterisk/extensions-vicidial.conf | head -5
Lead Called ├─ Answer │ ├─ Sale → No recycle │ ├─ Not Interested → Recycle (optional) │ └─ Wrong Number → No recycle (flag DNC) ├─ No Answer │ ├─ Retry ≤ 3 → Recycle NOW │ ├─ Retry 4-5 → Recycle in 24h │ └─ Retry > 5 → Archive └─ Busy ├─ Retry ≤ 2 → Recycle in 30 min └─ Retry > 2 → Recycle in 2h
Lead Called ├─ Answer │ ├─ Sale → No recycle │ ├─ Not Interested → Recycle (optional) │ └─ Wrong Number → No recycle (flag DNC) ├─ No Answer │ ├─ Retry ≤ 3 → Recycle NOW │ ├─ Retry 4-5 → Recycle in 24h │ └─ Retry > 5 → Archive └─ Busy ├─ Retry ≤ 2 → Recycle in 30 min └─ Retry > 2 → Recycle in 2h
Lead Called ├─ Answer │ ├─ Sale → No recycle │ ├─ Not Interested → Recycle (optional) │ └─ Wrong Number → No recycle (flag DNC) ├─ No Answer │ ├─ Retry ≤ 3 → Recycle NOW │ ├─ Retry 4-5 → Recycle in 24h │ └─ Retry > 5 → Archive └─ Busy ├─ Retry ≤ 2 → Recycle in 30 min └─ Retry > 2 → Recycle in 2h
-- View current lead status and recycle information
SELECT vl.lead_id, vl.phone_number, vl.status, vl.call_date, vl.length_in_sec, vl.user, vl.campaign_id, COUNT(*) as attempt_count
FROM vicidial_log vl
WHERE vl.campaign_id = 'TESTCAMP' AND vl.phone_number = '5551234567'
GROUP BY vl.lead_id
ORDER BY vl.call_date DESC;
-- View current lead status and recycle information
SELECT vl.lead_id, vl.phone_number, vl.status, vl.call_date, vl.length_in_sec, vl.user, vl.campaign_id, COUNT(*) as attempt_count
FROM vicidial_log vl
WHERE vl.campaign_id = 'TESTCAMP' AND vl.phone_number = '5551234567'
GROUP BY vl.lead_id
ORDER BY vl.call_date DESC;
-- View current lead status and recycle information
SELECT vl.lead_id, vl.phone_number, vl.status, vl.call_date, vl.length_in_sec, vl.user, vl.campaign_id, COUNT(*) as attempt_count
FROM vicidial_log vl
WHERE vl.campaign_id = 'TESTCAMP' AND vl.phone_number = '5551234567'
GROUP BY vl.lead_id
ORDER BY vl.call_date DESC;
DESC vicidial_list;
DESC vicidial_list;
DESC vicidial_list;
DESC vicidial_log;
DESC vicidial_log;
DESC vicidial_log;
DESC vicidial_campaign;
DESC vicidial_campaign;
DESC vicidial_campaign;
# Log into ViciDial web interface
# Navigate to: Admin → Campaigns → [Your Campaign]
# Look for these settings:
# Log into ViciDial web interface
# Navigate to: Admin → Campaigns → [Your Campaign]
# Look for these settings:
# Log into ViciDial web interface
# Navigate to: Admin → Campaigns → [Your Campaign]
# Look for these settings:
SELECT campaign_id, lead_recycle_method, auto_dial_level, voicemail_ext, hopper_level
FROM vicidial_campaign
WHERE campaign_id = 'TESTCAMP';
SELECT campaign_id, lead_recycle_method, auto_dial_level, voicemail_ext, hopper_level
FROM vicidial_campaign
WHERE campaign_id = 'TESTCAMP';
SELECT campaign_id, lead_recycle_method, auto_dial_level, voicemail_ext, hopper_level
FROM vicidial_campaign
WHERE campaign_id = 'TESTCAMP';
UPDATE vicidial_campaign
SET lead_recycle_method = 1
WHERE campaign_id = 'TESTCAMP';
UPDATE vicidial_campaign
SET lead_recycle_method = 1
WHERE campaign_id = 'TESTCAMP';
UPDATE vicidial_campaign
SET lead_recycle_method = 1
WHERE campaign_id = 'TESTCAMP';
-- View current status recycling rules
SELECT campaign_id, status, recyclable, rank
FROM vicidial_closer_log
WHERE campaign_id = 'TESTCAMP'
ORDER BY status;
-- View current status recycling rules
SELECT campaign_id, status, recyclable, rank
FROM vicidial_closer_log
WHERE campaign_id = 'TESTCAMP'
ORDER BY status;
-- View current status recycling rules
SELECT campaign_id, status, recyclable, rank
FROM vicidial_closer_log
WHERE campaign_id = 'TESTCAMP'
ORDER BY status;
-- Recycle NOANSWER leads after 30 minutes
INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank)
VALUES ('TESTCAMP', 'NOANSWER', 'Y', 1800, 10)
ON DUPLICATE KEY UPDATE recyclable = 'Y', seconds_to_next_call = 1800; -- Recycle BUSY after 15 minutes
INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank)
VALUES ('TESTCAMP', 'BUSY', 'Y', 900, 20)
ON DUPLICATE KEY UPDATE recyclable = 'Y', seconds_to_next_call = 900; -- Do NOT recycle DNCPROSPECT
INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank)
VALUES ('TESTCAMP', 'DNCPROSPECT', 'N', 0, 5)
ON DUPLICATE KEY UPDATE recyclable = 'N', seconds_to_next_call = 0;
-- Recycle NOANSWER leads after 30 minutes
INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank)
VALUES ('TESTCAMP', 'NOANSWER', 'Y', 1800, 10)
ON DUPLICATE KEY UPDATE recyclable = 'Y', seconds_to_next_call = 1800; -- Recycle BUSY after 15 minutes
INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank)
VALUES ('TESTCAMP', 'BUSY', 'Y', 900, 20)
ON DUPLICATE KEY UPDATE recyclable = 'Y', seconds_to_next_call = 900; -- Do NOT recycle DNCPROSPECT
INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank)
VALUES ('TESTCAMP', 'DNCPROSPECT', 'N', 0, 5)
ON DUPLICATE KEY UPDATE recyclable = 'N', seconds_to_next_call = 0;
-- Recycle NOANSWER leads after 30 minutes
INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank)
VALUES ('TESTCAMP', 'NOANSWER', 'Y', 1800, 10)
ON DUPLICATE KEY UPDATE recyclable = 'Y', seconds_to_next_call = 1800; -- Recycle BUSY after 15 minutes
INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank)
VALUES ('TESTCAMP', 'BUSY', 'Y', 900, 20)
ON DUPLICATE KEY UPDATE recyclable = 'Y', seconds_to_next_call = 900; -- Do NOT recycle DNCPROSPECT
INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank)
VALUES ('TESTCAMP', 'DNCPROSPECT', 'N', 0, 5)
ON DUPLICATE KEY UPDATE recyclable = 'N', seconds_to_next_call = 0;
cat > /usr/share/astguiclient/agi-bin/recycle_custom.agi << 'EOF'
#!/usr/bin/perl
# Custom Lead Recycling AGI Script for ViciDial
# Place in: /usr/share/astguiclient/agi-bin/ use strict;
use warnings;
use Asterisk::AGI;
use DBI; my $agi = new Asterisk::AGI;
my $lead_id = $agi->get_variable("lead_id");
my $campaign_id = $agi->get_variable("campaign_id");
my $status = $agi->get_variable("call_status");
my $attempt_count = $agi->get_variable("attempt_count"); # Connect to database
my $dbh = DBI->connect( "DBI:mysql:asterisk:localhost", "asteriskuser", "DBPASSWORD", {AutoCommit => 1}
) or die "Cannot connect: $DBI::errstr"; # Recycling logic
my $recycle = 0;
my $recycle_seconds = 0; if ($status eq 'NOANSWER') { if ($attempt_count < 3) { $recycle = 1; $recycle_seconds = 1800; # 30 minutes } elsif ($attempt_count < 5) { $recycle = 1; $recycle_seconds = 86400; # 24 hours }
} elsif ($status eq 'BUSY') { if ($attempt_count < 4) { $recycle = 1; $recycle_seconds = 900; # 15 minutes }
} elsif ($status eq 'XFER' && $attempt_count < 2) { $recycle = 1; $recycle_seconds = 3600; # 1 hour
} # Update vicidial_list with recycle status
if ($recycle) { my $sth = $dbh->prepare( "UPDATE vicidial_list SET status = ?, last_local_call_time = DATE_ADD(NOW(), INTERVAL ? SECOND) WHERE lead_id = ?" ); $sth->execute('RECYCLE', $recycle_seconds, $lead_id); $agi->set_variable("recycled", 1);
} $dbh->disconnect();
exit(0);
EOF chmod +x /usr/share/astguiclient/agi-bin/recycle_custom.agi
cat > /usr/share/astguiclient/agi-bin/recycle_custom.agi << 'EOF'
#!/usr/bin/perl
# Custom Lead Recycling AGI Script for ViciDial
# Place in: /usr/share/astguiclient/agi-bin/ use strict;
use warnings;
use Asterisk::AGI;
use DBI; my $agi = new Asterisk::AGI;
my $lead_id = $agi->get_variable("lead_id");
my $campaign_id = $agi->get_variable("campaign_id");
my $status = $agi->get_variable("call_status");
my $attempt_count = $agi->get_variable("attempt_count"); # Connect to database
my $dbh = DBI->connect( "DBI:mysql:asterisk:localhost", "asteriskuser", "DBPASSWORD", {AutoCommit => 1}
) or die "Cannot connect: $DBI::errstr"; # Recycling logic
my $recycle = 0;
my $recycle_seconds = 0; if ($status eq 'NOANSWER') { if ($attempt_count < 3) { $recycle = 1; $recycle_seconds = 1800; # 30 minutes } elsif ($attempt_count < 5) { $recycle = 1; $recycle_seconds = 86400; # 24 hours }
} elsif ($status eq 'BUSY') { if ($attempt_count < 4) { $recycle = 1; $recycle_seconds = 900; # 15 minutes }
} elsif ($status eq 'XFER' && $attempt_count < 2) { $recycle = 1; $recycle_seconds = 3600; # 1 hour
} # Update vicidial_list with recycle status
if ($recycle) { my $sth = $dbh->prepare( "UPDATE vicidial_list SET status = ?, last_local_call_time = DATE_ADD(NOW(), INTERVAL ? SECOND) WHERE lead_id = ?" ); $sth->execute('RECYCLE', $recycle_seconds, $lead_id); $agi->set_variable("recycled", 1);
} $dbh->disconnect();
exit(0);
EOF chmod +x /usr/share/astguiclient/agi-bin/recycle_custom.agi
cat > /usr/share/astguiclient/agi-bin/recycle_custom.agi << 'EOF'
#!/usr/bin/perl
# Custom Lead Recycling AGI Script for ViciDial
# Place in: /usr/share/astguiclient/agi-bin/ use strict;
use warnings;
use Asterisk::AGI;
use DBI; my $agi = new Asterisk::AGI;
my $lead_id = $agi->get_variable("lead_id");
my $campaign_id = $agi->get_variable("campaign_id");
my $status = $agi->get_variable("call_status");
my $attempt_count = $agi->get_variable("attempt_count"); # Connect to database
my $dbh = DBI->connect( "DBI:mysql:asterisk:localhost", "asteriskuser", "DBPASSWORD", {AutoCommit => 1}
) or die "Cannot connect: $DBI::errstr"; # Recycling logic
my $recycle = 0;
my $recycle_seconds = 0; if ($status eq 'NOANSWER') { if ($attempt_count < 3) { $recycle = 1; $recycle_seconds = 1800; # 30 minutes } elsif ($attempt_count < 5) { $recycle = 1; $recycle_seconds = 86400; # 24 hours }
} elsif ($status eq 'BUSY') { if ($attempt_count < 4) { $recycle = 1; $recycle_seconds = 900; # 15 minutes }
} elsif ($status eq 'XFER' && $attempt_count < 2) { $recycle = 1; $recycle_seconds = 3600; # 1 hour
} # Update vicidial_list with recycle status
if ($recycle) { my $sth = $dbh->prepare( "UPDATE vicidial_list SET status = ?, last_local_call_time = DATE_ADD(NOW(), INTERVAL ? SECOND) WHERE lead_id = ?" ); $sth->execute('RECYCLE', $recycle_seconds, $lead_id); $agi->set_variable("recycled", 1);
} $dbh->disconnect();
exit(0);
EOF chmod +x /usr/share/astguiclient/agi-bin/recycle_custom.agi
; Add to /etc/asterisk/extensions-vicidial.conf
[vicidial-handler]
exten => s,1,AGI(agi-bin/recycle_custom.agi)
exten => s,n,Hangup()
; Add to /etc/asterisk/extensions-vicidial.conf
[vicidial-handler]
exten => s,1,AGI(agi-bin/recycle_custom.agi)
exten => s,n,Hangup()
; Add to /etc/asterisk/extensions-vicidial.conf
[vicidial-handler]
exten => s,1,AGI(agi-bin/recycle_custom.agi)
exten => s,n,Hangup()
-- Create a custom list segmentation view
CREATE OR REPLACE VIEW lead_segments AS
SELECT vl.lead_id, vl.phone_number, vl.list_id, vl.campaign_id, CASE WHEN COUNT(*) <= 1 THEN 'FRESH' WHEN COUNT(*) BETWEEN 2 AND 3 THEN 'WARM' WHEN COUNT(*) > 3 THEN 'HOT' END as segment, COUNT(*) as attempt_count, MAX(vlog.call_date) as last_attempt
FROM vicidial_list vl
LEFT JOIN vicidial_log vlog ON vl.lead_id = vlog.lead_id
GROUP BY vl.lead_id; -- Query segmented leads
SELECT * FROM lead_segments WHERE campaign_id = 'TESTCAMP' AND segment = 'FRESH';
-- Create a custom list segmentation view
CREATE OR REPLACE VIEW lead_segments AS
SELECT vl.lead_id, vl.phone_number, vl.list_id, vl.campaign_id, CASE WHEN COUNT(*) <= 1 THEN 'FRESH' WHEN COUNT(*) BETWEEN 2 AND 3 THEN 'WARM' WHEN COUNT(*) > 3 THEN 'HOT' END as segment, COUNT(*) as attempt_count, MAX(vlog.call_date) as last_attempt
FROM vicidial_list vl
LEFT JOIN vicidial_log vlog ON vl.lead_id = vlog.lead_id
GROUP BY vl.lead_id; -- Query segmented leads
SELECT * FROM lead_segments WHERE campaign_id = 'TESTCAMP' AND segment = 'FRESH';
-- Create a custom list segmentation view
CREATE OR REPLACE VIEW lead_segments AS
SELECT vl.lead_id, vl.phone_number, vl.list_id, vl.campaign_id, CASE WHEN COUNT(*) <= 1 THEN 'FRESH' WHEN COUNT(*) BETWEEN 2 AND 3 THEN 'WARM' WHEN COUNT(*) > 3 THEN 'HOT' END as segment, COUNT(*) as attempt_count, MAX(vlog.call_date) as last_attempt
FROM vicidial_list vl
LEFT JOIN vicidial_log vlog ON vl.lead_id = vlog.lead_id
GROUP BY vl.lead_id; -- Query segmented leads
SELECT * FROM lead_segments WHERE campaign_id = 'TESTCAMP' AND segment = 'FRESH';
-- Archive DEAD leads older than 60 days
CREATE PROCEDURE archive_old_leads()
BEGIN INSERT INTO vicidial_list_archive SELECT * FROM vicidial_list WHERE status IN ('DEAD', 'NOTCALLABLE', 'BADNUMBER') AND DATE_SUB(NOW(), INTERVAL 60 DAY) > created_date; DELETE FROM vicidial_list WHERE status IN ('DEAD', 'NOTCALLABLE', 'BADNUMBER') AND DATE_SUB(NOW(), INTERVAL 60 DAY) > created_date;
END; -- Execute once daily via cron
CALL archive_old_leads();
-- Archive DEAD leads older than 60 days
CREATE PROCEDURE archive_old_leads()
BEGIN INSERT INTO vicidial_list_archive SELECT * FROM vicidial_list WHERE status IN ('DEAD', 'NOTCALLABLE', 'BADNUMBER') AND DATE_SUB(NOW(), INTERVAL 60 DAY) > created_date; DELETE FROM vicidial_list WHERE status IN ('DEAD', 'NOTCALLABLE', 'BADNUMBER') AND DATE_SUB(NOW(), INTERVAL 60 DAY) > created_date;
END; -- Execute once daily via cron
CALL archive_old_leads();
-- Archive DEAD leads older than 60 days
CREATE PROCEDURE archive_old_leads()
BEGIN INSERT INTO vicidial_list_archive SELECT * FROM vicidial_list WHERE status IN ('DEAD', 'NOTCALLABLE', 'BADNUMBER') AND DATE_SUB(NOW(), INTERVAL 60 DAY) > created_date; DELETE FROM vicidial_list WHERE status IN ('DEAD', 'NOTCALLABLE', 'BADNUMBER') AND DATE_SUB(NOW(), INTERVAL 60 DAY) > created_date;
END; -- Execute once daily via cron
CALL archive_old_leads();
# Run daily at 2 AM
0 2 * * * mysql -u root -p'PASSWORD' asterisk -e "CALL archive_old_leads;"
# Run daily at 2 AM
0 2 * * * mysql -u root -p'PASSWORD' asterisk -e "CALL archive_old_leads;"
# Run daily at 2 AM
0 2 * * * mysql -u root -p'PASSWORD' asterisk -e "CALL archive_old_leads;"
-- Schedule callbacks during business hours (9 AM - 6 PM local time)
UPDATE vicidial_list vl
SET status = 'CALLBACK', last_local_call_time = DATE_ADD( NOW(), INTERVAL ( (9 - HOUR(CONVERT_TZ(NOW(), 'UTC', 'America/Chicago'))) + IF(HOUR(CONVERT_TZ(NOW(), 'UTC', 'America/Chicago')) >= 18, 24, 0) ) HOUR )
WHERE lead_id IN ( SELECT lead_id FROM vicidial_log WHERE status = 'CALLBACK' AND DATE_SUB(NOW(), INTERVAL 24 HOUR) < call_date
)
AND campaign_id = 'TESTCAMP';
-- Schedule callbacks during business hours (9 AM - 6 PM local time)
UPDATE vicidial_list vl
SET status = 'CALLBACK', last_local_call_time = DATE_ADD( NOW(), INTERVAL ( (9 - HOUR(CONVERT_TZ(NOW(), 'UTC', 'America/Chicago'))) + IF(HOUR(CONVERT_TZ(NOW(), 'UTC', 'America/Chicago')) >= 18, 24, 0) ) HOUR )
WHERE lead_id IN ( SELECT lead_id FROM vicidial_log WHERE status = 'CALLBACK' AND DATE_SUB(NOW(), INTERVAL 24 HOUR) < call_date
)
AND campaign_id = 'TESTCAMP';
-- Schedule callbacks during business hours (9 AM - 6 PM local time)
UPDATE vicidial_list vl
SET status = 'CALLBACK', last_local_call_time = DATE_ADD( NOW(), INTERVAL ( (9 - HOUR(CONVERT_TZ(NOW(), 'UTC', 'America/Chicago'))) + IF(HOUR(CONVERT_TZ(NOW(), 'UTC', 'America/Chicago')) >= 18, 24, 0) ) HOUR )
WHERE lead_id IN ( SELECT lead_id FROM vicidial_log WHERE status = 'CALLBACK' AND DATE_SUB(NOW(), INTERVAL 24 HOUR) < call_date
)
AND campaign_id = 'TESTCAMP';
SELECT COUNT(DISTINCT lead_id) as recyclable_leads, SUM(CASE WHEN status = 'RECYCLE' THEN 1 ELSE 0 END) as pending_recycle, SUM(CASE WHEN status = 'CALLBACK' THEN 1 ELSE 0 END) as scheduled_callbacks, campaign_id, MAX(last_local_call_time) as next_available
FROM vicidial_list
WHERE campaign_id = 'TESTCAMP' AND status IN ('RECYCLE', 'CALLBACK', 'BUSY', 'NOANSWER')
GROUP BY campaign_id;
SELECT COUNT(DISTINCT lead_id) as recyclable_leads, SUM(CASE WHEN status = 'RECYCLE' THEN 1 ELSE 0 END) as pending_recycle, SUM(CASE WHEN status = 'CALLBACK' THEN 1 ELSE 0 END) as scheduled_callbacks, campaign_id, MAX(last_local_call_time) as next_available
FROM vicidial_list
WHERE campaign_id = 'TESTCAMP' AND status IN ('RECYCLE', 'CALLBACK', 'BUSY', 'NOANSWER')
GROUP BY campaign_id;
SELECT COUNT(DISTINCT lead_id) as recyclable_leads, SUM(CASE WHEN status = 'RECYCLE' THEN 1 ELSE 0 END) as pending_recycle, SUM(CASE WHEN status = 'CALLBACK' THEN 1 ELSE 0 END) as scheduled_callbacks, campaign_id, MAX(last_local_call_time) as next_available
FROM vicidial_list
WHERE campaign_id = 'TESTCAMP' AND status IN ('RECYCLE', 'CALLBACK', 'BUSY', 'NOANSWER')
GROUP BY campaign_id;
-- Calculate recycle-to-sale conversion rate
SELECT vlog.campaign_id, COUNT(DISTINCT vlog.lead_id) as total_attempts, COUNT(DISTINCT CASE WHEN vlog.status = 'SALE' THEN vlog.lead_id END) as sales, ROUND( (COUNT(DISTINCT CASE WHEN vlog.status = 'SALE' THEN vlog.lead_id END) / COUNT(DISTINCT vlog.lead_id) * 100), 2 ) as conversion_rate_pct, AVG(CASE WHEN vlog.status = 'SALE' THEN vlog.length_in_sec ELSE NULL END) as avg_sale_duration
FROM vicidial_log vlog
WHERE vlog.campaign_id = 'TESTCAMP' AND vlog.call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY vlog.campaign_id;
-- Calculate recycle-to-sale conversion rate
SELECT vlog.campaign_id, COUNT(DISTINCT vlog.lead_id) as total_attempts, COUNT(DISTINCT CASE WHEN vlog.status = 'SALE' THEN vlog.lead_id END) as sales, ROUND( (COUNT(DISTINCT CASE WHEN vlog.status = 'SALE' THEN vlog.lead_id END) / COUNT(DISTINCT vlog.lead_id) * 100), 2 ) as conversion_rate_pct, AVG(CASE WHEN vlog.status = 'SALE' THEN vlog.length_in_sec ELSE NULL END) as avg_sale_duration
FROM vicidial_log vlog
WHERE vlog.campaign_id = 'TESTCAMP' AND vlog.call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY vlog.campaign_id;
-- Calculate recycle-to-sale conversion rate
SELECT vlog.campaign_id, COUNT(DISTINCT vlog.lead_id) as total_attempts, COUNT(DISTINCT CASE WHEN vlog.status = 'SALE' THEN vlog.lead_id END) as sales, ROUND( (COUNT(DISTINCT CASE WHEN vlog.status = 'SALE' THEN vlog.lead_id END) / COUNT(DISTINCT vlog.lead_id) * 100), 2 ) as conversion_rate_pct, AVG(CASE WHEN vlog.status = 'SALE' THEN vlog.length_in_sec ELSE NULL END) as avg_sale_duration
FROM vicidial_log vlog
WHERE vlog.campaign_id = 'TESTCAMP' AND vlog.call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY vlog.campaign_id;
-- List leads by age and status
SELECT vl.lead_id, vl.phone_number, vl.status, DATEDIFF(NOW(), vl.created_date) as days_in_system, COUNT(vlog.lead_id) as total_attempts, MAX(vlog.call_date) as last_attempt, DATEDIFF(NOW(), MAX(vlog.call_date)) as days_since_last_attempt
FROM vicidial_list vl
LEFT JOIN vicidial_log vlog ON vl.lead_id = vlog.lead_id
WHERE vl.campaign_id = 'TESTCAMP'
GROUP BY vl.lead_id
HAVING days_in_system > 90
ORDER BY last_attempt DESC;
-- List leads by age and status
SELECT vl.lead_id, vl.phone_number, vl.status, DATEDIFF(NOW(), vl.created_date) as days_in_system, COUNT(vlog.lead_id) as total_attempts, MAX(vlog.call_date) as last_attempt, DATEDIFF(NOW(), MAX(vlog.call_date)) as days_since_last_attempt
FROM vicidial_list vl
LEFT JOIN vicidial_log vlog ON vl.lead_id = vlog.lead_id
WHERE vl.campaign_id = 'TESTCAMP'
GROUP BY vl.lead_id
HAVING days_in_system > 90
ORDER BY last_attempt DESC;
-- List leads by age and status
SELECT vl.lead_id, vl.phone_number, vl.status, DATEDIFF(NOW(), vl.created_date) as days_in_system, COUNT(vlog.lead_id) as total_attempts, MAX(vlog.call_date) as last_attempt, DATEDIFF(NOW(), MAX(vlog.call_date)) as days_since_last_attempt
FROM vicidial_list vl
LEFT JOIN vicidial_log vlog ON vl.lead_id = vlog.lead_id
WHERE vl.campaign_id = 'TESTCAMP'
GROUP BY vl.lead_id
HAVING days_in_system > 90
ORDER BY last_attempt DESC;
# Recycle all NOANSWER leads from a specific campaign
mysql -u asteriskuser -p'PASSWORD' asterisk << EOF
UPDATE vicidial_list
SET status = 'RECYCLE', last_local_call_time = DATE_ADD(NOW(), INTERVAL 1800 SECOND)
WHERE campaign_id = 'TESTCAMP' AND status = 'NOANSWER' AND DATE_SUB(NOW(), INTERVAL 6 HOUR) > last_local_call_time;
EOF
# Recycle all NOANSWER leads from a specific campaign
mysql -u asteriskuser -p'PASSWORD' asterisk << EOF
UPDATE vicidial_list
SET status = 'RECYCLE', last_local_call_time = DATE_ADD(NOW(), INTERVAL 1800 SECOND)
WHERE campaign_id = 'TESTCAMP' AND status = 'NOANSWER' AND DATE_SUB(NOW(), INTERVAL 6 HOUR) > last_local_call_time;
EOF
# Recycle all NOANSWER leads from a specific campaign
mysql -u asteriskuser -p'PASSWORD' asterisk << EOF
UPDATE vicidial_list
SET status = 'RECYCLE', last_local_call_time = DATE_ADD(NOW(), INTERVAL 1800 SECOND)
WHERE campaign_id = 'TESTCAMP' AND status = 'NOANSWER' AND DATE_SUB(NOW(), INTERVAL 6 HOUR) > last_local_call_time;
EOF
# Connect to Asterisk CLI and check lead queue
asterisk -rx "agi show" | grep -i recycle # Monitor ViciDial dialer process
ps aux | grep -E "vici_|astguiclient" # Check for recycling script errors
tail -50 /var/log/asterisk/messages | grep -i recycle
# Connect to Asterisk CLI and check lead queue
asterisk -rx "agi show" | grep -i recycle # Monitor ViciDial dialer process
ps aux | grep -E "vici_|astguiclient" # Check for recycling script errors
tail -50 /var/log/asterisk/messages | grep -i recycle
# Connect to Asterisk CLI and check lead queue
asterisk -rx "agi show" | grep -i recycle # Monitor ViciDial dialer process
ps aux | grep -E "vici_|astguiclient" # Check for recycling script errors
tail -50 /var/log/asterisk/messages | grep -i recycle
# SSH to ViciDial server
# Run manual recycle operation
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_list SET status = 'RECYCLE', last_local_call_time = NOW() WHERE lead_id = 12345 AND campaign_id = 'TESTCAMP';" # Verify update
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT lead_id, phone_number, status, last_local_call_time FROM vicidial_list WHERE lead_id = 12345;"
# SSH to ViciDial server
# Run manual recycle operation
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_list SET status = 'RECYCLE', last_local_call_time = NOW() WHERE lead_id = 12345 AND campaign_id = 'TESTCAMP';" # Verify update
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT lead_id, phone_number, status, last_local_call_time FROM vicidial_list WHERE lead_id = 12345;"
# SSH to ViciDial server
# Run manual recycle operation
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_list SET status = 'RECYCLE', last_local_call_time = NOW() WHERE lead_id = 12345 AND campaign_id = 'TESTCAMP';" # Verify update
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT lead_id, phone_number, status, last_local_call_time FROM vicidial_list WHERE lead_id = 12345;"
-- Create time-zone aware recycling procedure
DELIMITER $$ CREATE PROCEDURE recycle_by_timezone( IN p_campaign_id VARCHAR(20)
)
BEGIN DECLARE done INT DEFAULT FALSE; DECLARE v_lead_id INT; DECLARE v_phone VARCHAR(20); DECLARE v_gmt_offset INT; DECLARE v_local_hour INT; DECLARE cur CURSOR FOR SELECT lead_id, phone_number, gmt_offset_now FROM vicidial_list WHERE campaign_id = p_campaign_id AND status IN ('NOANSWER', 'BUSY'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO v_lead_id, v_phone, v_gmt_offset; IF done THEN LEAVE read_loop; END IF; -- Calculate local hour for this lead's timezone SET v_local_hour = HOUR(DATE_ADD(NOW(), INTERVAL v_gmt_offset HOUR)); -- Only recycle during business hours (9 AM - 6 PM) IF v_local_hour >= 9 AND v_local_hour < 18 THEN UPDATE vicidial_list SET status = 'RECYCLE', last_local_call_time = NOW() WHERE lead_id = v_lead_id; END IF; END LOOP; CLOSE cur;
END$$ DELIMITER ; -- Execute
CALL recycle_by_timezone('TESTCAMP');
-- Create time-zone aware recycling procedure
DELIMITER $$ CREATE PROCEDURE recycle_by_timezone( IN p_campaign_id VARCHAR(20)
)
BEGIN DECLARE done INT DEFAULT FALSE; DECLARE v_lead_id INT; DECLARE v_phone VARCHAR(20); DECLARE v_gmt_offset INT; DECLARE v_local_hour INT; DECLARE cur CURSOR FOR SELECT lead_id, phone_number, gmt_offset_now FROM vicidial_list WHERE campaign_id = p_campaign_id AND status IN ('NOANSWER', 'BUSY'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO v_lead_id, v_phone, v_gmt_offset; IF done THEN LEAVE read_loop; END IF; -- Calculate local hour for this lead's timezone SET v_local_hour = HOUR(DATE_ADD(NOW(), INTERVAL v_gmt_offset HOUR)); -- Only recycle during business hours (9 AM - 6 PM) IF v_local_hour >= 9 AND v_local_hour < 18 THEN UPDATE vicidial_list SET status = 'RECYCLE', last_local_call_time = NOW() WHERE lead_id = v_lead_id; END IF; END LOOP; CLOSE cur;
END$$ DELIMITER ; -- Execute
CALL recycle_by_timezone('TESTCAMP');
-- Create time-zone aware recycling procedure
DELIMITER $$ CREATE PROCEDURE recycle_by_timezone( IN p_campaign_id VARCHAR(20)
)
BEGIN DECLARE done INT DEFAULT FALSE; DECLARE v_lead_id INT; DECLARE v_phone VARCHAR(20); DECLARE v_gmt_offset INT; DECLARE v_local_hour INT; DECLARE cur CURSOR FOR SELECT lead_id, phone_number, gmt_offset_now FROM vicidial_list WHERE campaign_id = p_campaign_id AND status IN ('NOANSWER', 'BUSY'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO v_lead_id, v_phone, v_gmt_offset; IF done THEN LEAVE read_loop; END IF; -- Calculate local hour for this lead's timezone SET v_local_hour = HOUR(DATE_ADD(NOW(), INTERVAL v_gmt_offset HOUR)); -- Only recycle during business hours (9 AM - 6 PM) IF v_local_hour >= 9 AND v_local_hour < 18 THEN UPDATE vicidial_list SET status = 'RECYCLE', last_local_call_time = NOW() WHERE lead_id = v_lead_id; END IF; END LOOP; CLOSE cur;
END$$ DELIMITER ; -- Execute
CALL recycle_by_timezone('TESTCAMP');
-- Set maximum attempts per lead
ALTER TABLE vicidial_list ADD COLUMN max_attempts INT DEFAULT 5; -- Prevent recycling beyond maximum
UPDATE vicidial_list
SET status = 'MAXED'
WHERE (SELECT COUNT(*) FROM vicidial_log WHERE lead_id = vicidial_list.lead_id) >= max_attempts
AND campaign_id = 'TESTCAMP';
-- Set maximum attempts per lead
ALTER TABLE vicidial_list ADD COLUMN max_attempts INT DEFAULT 5; -- Prevent recycling beyond maximum
UPDATE vicidial_list
SET status = 'MAXED'
WHERE (SELECT COUNT(*) FROM vicidial_log WHERE lead_id = vicidial_list.lead_id) >= max_attempts
AND campaign_id = 'TESTCAMP';
-- Set maximum attempts per lead
ALTER TABLE vicidial_list ADD COLUMN max_attempts INT DEFAULT 5; -- Prevent recycling beyond maximum
UPDATE vicidial_list
SET status = 'MAXED'
WHERE (SELECT COUNT(*) FROM vicidial_log WHERE lead_id = vicidial_list.lead_id) >= max_attempts
AND campaign_id = 'TESTCAMP';
#!/bin/bash
# Script: check_recycle_health.sh CAMPAIGN="TESTCAMP"
THRESHOLD=10 # Alert if recycle queue exceeds 10% TOTAL_LEADS=$(mysql -N -u asteriskuser -p'PASSWORD' asterisk \ -e "SELECT COUNT(*) FROM vicidial_list WHERE campaign_id='$CAMPAIGN';") RECYCLE_LEADS=$(mysql -N -u asteriskuser -p'PASSWORD' asterisk \ -e "SELECT COUNT(*) FROM vicidial_list WHERE campaign_id='$CAMPAIGN' AND status='RECYCLE';") PERCENTAGE=$((RECYCLE_LEADS * 100 / TOTAL_LEADS)) echo "Campaign: $CAMPAIGN"
echo "Total Leads: $TOTAL_LEADS"
echo "Recycled Leads: $RECYCLE_LEADS"
echo "Recycle Queue: $PERCENTAGE%" if [ $PERCENTAGE -gt $THRESHOLD ]; then echo "WARNING: Recycle queue exceeds $THRESHOLD% threshold!"
fi
#!/bin/bash
# Script: check_recycle_health.sh CAMPAIGN="TESTCAMP"
THRESHOLD=10 # Alert if recycle queue exceeds 10% TOTAL_LEADS=$(mysql -N -u asteriskuser -p'PASSWORD' asterisk \ -e "SELECT COUNT(*) FROM vicidial_list WHERE campaign_id='$CAMPAIGN';") RECYCLE_LEADS=$(mysql -N -u asteriskuser -p'PASSWORD' asterisk \ -e "SELECT COUNT(*) FROM vicidial_list WHERE campaign_id='$CAMPAIGN' AND status='RECYCLE';") PERCENTAGE=$((RECYCLE_LEADS * 100 / TOTAL_LEADS)) echo "Campaign: $CAMPAIGN"
echo "Total Leads: $TOTAL_LEADS"
echo "Recycled Leads: $RECYCLE_LEADS"
echo "Recycle Queue: $PERCENTAGE%" if [ $PERCENTAGE -gt $THRESHOLD ]; then echo "WARNING: Recycle queue exceeds $THRESHOLD% threshold!"
fi
#!/bin/bash
# Script: check_recycle_health.sh CAMPAIGN="TESTCAMP"
THRESHOLD=10 # Alert if recycle queue exceeds 10% TOTAL_LEADS=$(mysql -N -u asteriskuser -p'PASSWORD' asterisk \ -e "SELECT COUNT(*) FROM vicidial_list WHERE campaign_id='$CAMPAIGN';") RECYCLE_LEADS=$(mysql -N -u asteriskuser -p'PASSWORD' asterisk \ -e "SELECT COUNT(*) FROM vicidial_list WHERE campaign_id='$CAMPAIGN' AND status='RECYCLE';") PERCENTAGE=$((RECYCLE_LEADS * 100 / TOTAL_LEADS)) echo "Campaign: $CAMPAIGN"
echo "Total Leads: $TOTAL_LEADS"
echo "Recycled Leads: $RECYCLE_LEADS"
echo "Recycle Queue: $PERCENTAGE%" if [ $PERCENTAGE -gt $THRESHOLD ]; then echo "WARNING: Recycle queue exceeds $THRESHOLD% threshold!"
fi
0 * * * * /usr/local/bin/check_recycle_health.sh | mail -s "Recycle Health Alert" [email protected]
0 * * * * /usr/local/bin/check_recycle_health.sh | mail -s "Recycle Health Alert" [email protected]
0 * * * * /usr/local/bin/check_recycle_health.sh | mail -s "Recycle Health Alert" [email protected]
#!/bin/bash
# Add to crontab - Run weekly mysql -u asteriskuser -p'PASSWORD' asterisk << EOF
-- Optimize tables for query performance
OPTIMIZE TABLE vicidial_list;
OPTIMIZE TABLE vicidial_log;
OPTIMIZE TABLE vicidial_closer_log; -- Rebuild indexes
ANALYZE TABLE vicidial_list;
ANALYZE TABLE vicidial_log; -- Check for corrupted records
CHECK TABLE vicidial_list;
CHECK TABLE vicidial_log;
EOF
#!/bin/bash
# Add to crontab - Run weekly mysql -u asteriskuser -p'PASSWORD' asterisk << EOF
-- Optimize tables for query performance
OPTIMIZE TABLE vicidial_list;
OPTIMIZE TABLE vicidial_log;
OPTIMIZE TABLE vicidial_closer_log; -- Rebuild indexes
ANALYZE TABLE vicidial_list;
ANALYZE TABLE vicidial_log; -- Check for corrupted records
CHECK TABLE vicidial_list;
CHECK TABLE vicidial_log;
EOF
#!/bin/bash
# Add to crontab - Run weekly mysql -u asteriskuser -p'PASSWORD' asterisk << EOF
-- Optimize tables for query performance
OPTIMIZE TABLE vicidial_list;
OPTIMIZE TABLE vicidial_log;
OPTIMIZE TABLE vicidial_closer_log; -- Rebuild indexes
ANALYZE TABLE vicidial_list;
ANALYZE TABLE vicidial_log; -- Check for corrupted records
CHECK TABLE vicidial_list;
CHECK TABLE vicidial_log;
EOF
# 1. Check if campaign recycling is enabled
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT campaign_id, lead_recycle_method FROM vicidial_campaign WHERE campaign_id='TESTCAMP';" # Expected output: lead_recycle_method should be 1 or higher # 2. Verify vicidial_dialer process is running
ps aux | grep vicidial | grep -v grep # 3. Check for errors in logs
tail -100 /var/log/asterisk/messages | grep -i recycle
grep "ERROR\|recycle\|RECYCLE" /var/log/asterisk/messages | tail -20
# 1. Check if campaign recycling is enabled
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT campaign_id, lead_recycle_method FROM vicidial_campaign WHERE campaign_id='TESTCAMP';" # Expected output: lead_recycle_method should be 1 or higher # 2. Verify vicidial_dialer process is running
ps aux | grep vicidial | grep -v grep # 3. Check for errors in logs
tail -100 /var/log/asterisk/messages | grep -i recycle
grep "ERROR\|recycle\|RECYCLE" /var/log/asterisk/messages | tail -20
# 1. Check if campaign recycling is enabled
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT campaign_id, lead_recycle_method FROM vicidial_campaign WHERE campaign_id='TESTCAMP';" # Expected output: lead_recycle_method should be 1 or higher # 2. Verify vicidial_dialer process is running
ps aux | grep vicidial | grep -v grep # 3. Check for errors in logs
tail -100 /var/log/asterisk/messages | grep -i recycle
grep "ERROR\|recycle\|RECYCLE" /var/log/asterisk/messages | tail -20
# Enable recycling in campaign
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_campaign SET lead_recycle_method = 1 WHERE campaign_id = 'TESTCAMP';" # Restart vicidial_dialer service
sudo service vicidial_dialer restart # Monitor service startup
tail -f /var/log/asterisk/messages | grep vicidial
# Enable recycling in campaign
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_campaign SET lead_recycle_method = 1 WHERE campaign_id = 'TESTCAMP';" # Restart vicidial_dialer service
sudo service vicidial_dialer restart # Monitor service startup
tail -f /var/log/asterisk/messages | grep vicidial
# Enable recycling in campaign
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_campaign SET lead_recycle_method = 1 WHERE campaign_id = 'TESTCAMP';" # Restart vicidial_dialer service
sudo service vicidial_dialer restart # Monitor service startup
tail -f /var/log/asterisk/messages | grep vicidial
# Check hopper queue depth
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT COUNT(*) as hopper_count FROM vicidial_hopper WHERE campaign_id='TESTCAMP';" # View next leads to be dialed
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT lead_id, phone_number, status, last_local_call_time FROM vicidial_list WHERE campaign_id = 'TESTCAMP' AND status = 'RECYCLE' ORDER BY last_local_call_time LIMIT 10;" # Check if leads meet recycle time requirement
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT lead_id, phone_number, status, TIMESTAMPDIFF(SECOND, NOW(), last_local_call_time) as seconds_until_recycle FROM vicidial_list WHERE campaign_id = 'TESTCAMP' AND status = 'RECYCLE' LIMIT 5;"
# Check hopper queue depth
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT COUNT(*) as hopper_count FROM vicidial_hopper WHERE campaign_id='TESTCAMP';" # View next leads to be dialed
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT lead_id, phone_number, status, last_local_call_time FROM vicidial_list WHERE campaign_id = 'TESTCAMP' AND status = 'RECYCLE' ORDER BY last_local_call_time LIMIT 10;" # Check if leads meet recycle time requirement
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT lead_id, phone_number, status, TIMESTAMPDIFF(SECOND, NOW(), last_local_call_time) as seconds_until_recycle FROM vicidial_list WHERE campaign_id = 'TESTCAMP' AND status = 'RECYCLE' LIMIT 5;"
# Check hopper queue depth
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT COUNT(*) as hopper_count FROM vicidial_hopper WHERE campaign_id='TESTCAMP';" # View next leads to be dialed
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT lead_id, phone_number, status, last_local_call_time FROM vicidial_list WHERE campaign_id = 'TESTCAMP' AND status = 'RECYCLE' ORDER BY last_local_call_time LIMIT 10;" # Check if leads meet recycle time requirement
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT lead_id, phone_number, status, TIMESTAMPDIFF(SECOND, NOW(), last_local_call_time) as seconds_until_recycle FROM vicidial_list WHERE campaign_id = 'TESTCAMP' AND status = 'RECYCLE' LIMIT 5;"
# If leads show negative seconds_until_recycle, they're ready
# Check if hopper is full (capacity issue) mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT * FROM vicidial_campaign WHERE campaign_id = 'TESTCAMP';" | grep -E "hopper_level|lead_recycle" # If hopper is too small, update it
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_campaign SET hopper_level = 200 WHERE campaign_id = 'TESTCAMP';" # Force immediate hopper fill
asterisk -rx "vicidial hopper $CAMPAIGN"
# If leads show negative seconds_until_recycle, they're ready
# Check if hopper is full (capacity issue) mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT * FROM vicidial_campaign WHERE campaign_id = 'TESTCAMP';" | grep -E "hopper_level|lead_recycle" # If hopper is too small, update it
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_campaign SET hopper_level = 200 WHERE campaign_id = 'TESTCAMP';" # Force immediate hopper fill
asterisk -rx "vicidial hopper $CAMPAIGN"
# If leads show negative seconds_until_recycle, they're ready
# Check if hopper is full (capacity issue) mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT * FROM vicidial_campaign WHERE campaign_id = 'TESTCAMP';" | grep -E "hopper_level|lead_recycle" # If hopper is too small, update it
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_campaign SET hopper_level = 200 WHERE campaign_id = 'TESTCAMP';" # Force immediate hopper fill
asterisk -rx "vicidial hopper $CAMPAIGN"
# Identify heavily recycled leads
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT vl.lead_id, vl.phone_number, COUNT(*) as call_attempts, MIN(vlog.call_date) as first_call, MAX(vlog.call_date) as last_call, TIMESTAMPDIFF(DAY, MIN(vlog.call_date), MAX(vlog.call_date)) as days_span FROM vicidial_list vl INNER JOIN vicidial_log vlog ON vl.lead_id = vlog.lead_id WHERE vl.campaign_id = 'TESTCAMP' GROUP BY vl.lead_id HAVING call_attempts > 10 ORDER BY call_attempts DESC LIMIT 20;"
# Identify heavily recycled leads
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT vl.lead_id, vl.phone_number, COUNT(*) as call_attempts, MIN(vlog.call_date) as first_call, MAX(vlog.call_date) as last_call, TIMESTAMPDIFF(DAY, MIN(vlog.call_date), MAX(vlog.call_date)) as days_span FROM vicidial_list vl INNER JOIN vicidial_log vlog ON vl.lead_id = vlog.lead_id WHERE vl.campaign_id = 'TESTCAMP' GROUP BY vl.lead_id HAVING call_attempts > 10 ORDER BY call_attempts DESC LIMIT 20;"
# Identify heavily recycled leads
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"SELECT vl.lead_id, vl.phone_number, COUNT(*) as call_attempts, MIN(vlog.call_date) as first_call, MAX(vlog.call_date) as last_call, TIMESTAMPDIFF(DAY, MIN(vlog.call_date), MAX(vlog.call_date)) as days_span FROM vicidial_list vl INNER JOIN vicidial_log vlog ON vl.lead_id = vlog.lead_id WHERE vl.campaign_id = 'TESTCAMP' GROUP BY vl.lead_id HAVING call_attempts > 10 ORDER BY call_attempts DESC LIMIT 20;"
# Implement attempt limits in closer_log
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank) VALUES ('TESTCAMP', 'NOANSWER', 'Y', 3600, 10) ON DUPLICATE KEY UPDATE seconds_to_next_call = 3600;" # Archive over-attempted leads
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_list vl SET status = 'ARCHIVE' WHERE vl.campaign_id = 'TESTCAMP' AND (SELECT COUNT(*) FROM vicidial_log WHERE lead_id = vl.lead_id) > 8;"
# Implement attempt limits in closer_log
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank) VALUES ('TESTCAMP', 'NOANSWER', 'Y', 3600, 10) ON DUPLICATE KEY UPDATE seconds_to_next_call = 3600;" # Archive over-attempted leads
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_list vl SET status = 'ARCHIVE' WHERE vl.campaign_id = 'TESTCAMP' AND (SELECT COUNT(*) FROM vicidial_log WHERE lead_id = vl.lead_id) > 8;"
# Implement attempt limits in closer_log
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"INSERT INTO vicidial_closer_log (campaign_id, status, recyclable, seconds_to_next_call, rank) VALUES ('TESTCAMP', 'NOANSWER', 'Y', 3600, 10) ON DUPLICATE KEY UPDATE seconds_to_next_call = 3600;" # Archive over-attempted leads
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"UPDATE vicidial_list vl SET status = 'ARCHIVE' WHERE vl.campaign_id = 'TESTCAMP' AND (SELECT COUNT(*) FROM vicidial_log WHERE lead_id = vl.lead_id) > 8;"
# Check for long-running queries
mysql -u asteriskuser -p'PASSWORD' asterisk -e "SHOW PROCESSLIST;" | grep -i UPDATE # Monitor table locks
mysqladmin -u asteriskuser -p'PASSWORD' -i 2 processlist | grep -E "UPDATE|LOCK" # Check innodb status
mysql -u asteriskuser -p'PASSWORD' asterisk -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "LATEST DETECTED DEADLOCK"
# Check for long-running queries
mysql -u asteriskuser -p'PASSWORD' asterisk -e "SHOW PROCESSLIST;" | grep -i UPDATE # Monitor table locks
mysqladmin -u asteriskuser -p'PASSWORD' -i 2 processlist | grep -E "UPDATE|LOCK" # Check innodb status
mysql -u asteriskuser -p'PASSWORD' asterisk -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "LATEST DETECTED DEADLOCK"
# Check for long-running queries
mysql -u asteriskuser -p'PASSWORD' asterisk -e "SHOW PROCESSLIST;" | grep -i UPDATE # Monitor table locks
mysqladmin -u asteriskuser -p'PASSWORD' -i 2 processlist | grep -E "UPDATE|LOCK" # Check innodb status
mysql -u asteriskuser -p'PASSWORD' asterisk -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "LATEST DETECTED DEADLOCK"
# Implement batching in recycling procedures
mysql -u asteriskuser -p'PASSWORD' asterisk << EOF
-- Update in smaller chunks to reduce lock time
SET @batch_size = 100;
UPDATE vicidial_list vl
SET status = 'RECYCLE'
WHERE campaign_id = 'TESTCAMP' AND status = 'NOANSWER' AND lead_id IN ( SELECT lead_id FROM ( SELECT lead_id FROM vicidial_list vl2 WHERE campaign_id = 'TESTCAMP' AND status = 'NOANSWER' LIMIT @batch_size ) tmp );
EOF # Add index for faster recycling queries
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"ALTER TABLE vicidial_list ADD INDEX idx_recycle (campaign_id, status, last_local_call_time);"
# Implement batching in recycling procedures
mysql -u asteriskuser -p'PASSWORD' asterisk << EOF
-- Update in smaller chunks to reduce lock time
SET @batch_size = 100;
UPDATE vicidial_list vl
SET status = 'RECYCLE'
WHERE campaign_id = 'TESTCAMP' AND status = 'NOANSWER' AND lead_id IN ( SELECT lead_id FROM ( SELECT lead_id FROM vicidial_list vl2 WHERE campaign_id = 'TESTCAMP' AND status = 'NOANSWER' LIMIT @batch_size ) tmp );
EOF # Add index for faster recycling queries
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"ALTER TABLE vicidial_list ADD INDEX idx_recycle (campaign_id, status, last_local_call_time);"
# Implement batching in recycling procedures
mysql -u asteriskuser -p'PASSWORD' asterisk << EOF
-- Update in smaller chunks to reduce lock time
SET @batch_size = 100;
UPDATE vicidial_list vl
SET status = 'RECYCLE'
WHERE campaign_id = 'TESTCAMP' AND status = 'NOANSWER' AND lead_id IN ( SELECT lead_id FROM ( SELECT lead_id FROM vicidial_list vl2 WHERE campaign_id = 'TESTCAMP' AND status = 'NOANSWER' LIMIT @batch_size ) tmp );
EOF # Add index for faster recycling queries
mysql -u asteriskuser -p'PASSWORD' asterisk -e \
"ALTER TABLE vicidial_list ADD INDEX idx_recycle (campaign_id, status, last_local_call_time);" - ViciDial 2.14+ installed and running (2.14.1 or later recommended)
- Administrator access to the ViciDial web interface at /vicidial/admin.php
- SSH access to the ViciDial server with sudo privileges
- MySQL/MariaDB command-line access to the asterisk database
- At least one active campaign configured
- Basic understanding of ViciDial list structure and lead statuses
- Backup of your database before implementing changes - lead_id — Unique identifier
- phone_number — Contact number
- status — Current lead status
- list_id — Associated campaign list
- gmt_offset_now — Timezone for callback scheduling
- created_date — Lead entry date
- last_local_call_time — Last contact attempt timestamp - lead_id — Links to vicidial_list
- call_date — Attempt timestamp
- status — Outcome status
- length_in_sec — Duration
- user — Agent identifier
- campaign_id — Campaign code
- phone_number — Dialed number
- call_type — INBOUND/OUTBOUND - campaign_id — Unique identifier
- lead_recycle_method — Recycling strategy
- reuse_trunk_level — Trunk reuse behavior
- caller_id_method — Caller ID assignment - 0 — No recycling
- 1 — Standard recycling (uses vicidial_closer_log status settings)
- 2 — Advanced recycling (requires custom scripting) - Understand Status Flow: Different statuses recycle at different intervals; configure per vicidial_closer_log.
- Database-Driven Configuration: Recycling behavior lives in vicidial_list, vicidial_log, and vicidial_campaign tables—customize via SQL.
- Time-Zone Awareness: Use gmt_offset_now and last_local_call_time to avoid off-hours contact.
- Prevent Lead Fatigue: Set attempt limits, monitor call frequency, and archive over-contacted leads.
- Monitor Performance: Track recycle-to-sale conversion rates, queue depths, and recycling effectiveness.
- Automate Maintenance: Use cron jobs and stored procedures to archive old leads, optimize tables, and maintain database health.
- Test Before Deploying: Always back up your database and test recycling changes in a staging environment first.
- Document Custom Logic: If implementing AGI scripts or complex procedures, maintain clear documentation for future maintenance.
🏷️ Tags
More from Tools
Tools: Update: How to Buy 2014 Years old Gmail Accounts Through 2026
Tools: Best 8 Platforms to Buy Aged GitHub Accounts With
Tools: The Supply Chain Security Audit Nobody Told You to Run (Until It Was Too Late) - Expert Insights
