Call Recording Archive

Objective

Create a system for storing and managing customer service call recordings. This enables compliance, quality assurance, training, and dispute resolution.

Step 1: Create Call Recordings Table

Create a table for storing call recordings.

CREATE TABLE call_recordings (
    id INTEGER PRIMARY KEY,
    call_id VARCHAR(50) UNIQUE NOT NULL,
    recording AUDIO(MP3),
    agent_id VARCHAR(50) NOT NULL,
    agent_name VARCHAR(100),
    customer_phone VARCHAR(20),
    customer_name VARCHAR(100),
    call_type VARCHAR(50),
    department VARCHAR(50),
    duration_seconds INTEGER,
    call_start TIMESTAMP,
    call_end TIMESTAMP,
    quality_score INTEGER,
    is_reviewed BOOLEAN DEFAULT FALSE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Call Recordings

Add sample call recording records.

INSERT INTO call_recordings (id, call_id, agent_id, agent_name, customer_phone, customer_name, call_type, department, duration_seconds, call_start, quality_score) VALUES
    (1, 'CALL-2024-0001', 'AGT-101', 'Alice Johnson', '555-0101', 'John Smith', 'support', 'Technical Support', 480, '2024-01-15 09:15:00', 85),
    (2, 'CALL-2024-0002', 'AGT-102', 'Bob Williams', '555-0102', 'Jane Doe', 'billing', 'Billing', 320, '2024-01-15 09:45:00', 92),
    (3, 'CALL-2024-0003', 'AGT-101', 'Alice Johnson', '555-0103', 'Mike Brown', 'complaint', 'Customer Service', 720, '2024-01-15 10:30:00', 78),
    (4, 'CALL-2024-0004', 'AGT-103', 'Carol Davis', '555-0104', 'Sarah Wilson', 'inquiry', 'Sales', 240, '2024-01-15 11:00:00', 95),
    (5, 'CALL-2024-0005', 'AGT-102', 'Bob Williams', '555-0105', 'Tom Anderson', 'support', 'Technical Support', 560, '2024-01-15 11:30:00', 88),
    (6, 'CALL-2024-0006', 'AGT-104', 'David Lee', '555-0106', 'Emma Davis', 'cancellation', 'Retention', 900, '2024-01-15 13:00:00', 72),
    (7, 'CALL-2024-0007', 'AGT-101', 'Alice Johnson', '555-0107', 'Chris Martin', 'support', 'Technical Support', 390, '2024-01-15 14:15:00', 91),
    (8, 'CALL-2024-0008', 'AGT-103', 'Carol Davis', '555-0108', 'Lisa Taylor', 'inquiry', 'Sales', 180, '2024-01-15 15:00:00', 97);

Step 3: Query Calls by Agent

Get all calls for a specific agent.

SELECT
    call_id,
    customer_name,
    call_type,
    duration_seconds / 60 as duration_minutes,
    call_start,
    quality_score
FROM call_recordings
WHERE agent_id = 'AGT-101'
ORDER BY call_start DESC;

Step 4: Agent Performance Statistics

Calculate agent performance metrics.

SELECT
    agent_id,
    agent_name,
    COUNT(*) as total_calls,
    SUM(duration_seconds) / 60 as total_minutes,
    AVG(duration_seconds) / 60 as avg_duration_minutes,
    AVG(quality_score) as avg_quality,
    MIN(quality_score) as min_quality,
    MAX(quality_score) as max_quality
FROM call_recordings
GROUP BY agent_id, agent_name
ORDER BY avg_quality DESC;

Step 5: Filter by Call Type

Find calls by type.

SELECT
    call_id,
    agent_name,
    customer_name,
    duration_seconds / 60 as duration_minutes,
    quality_score
FROM call_recordings
WHERE call_type = 'complaint'
ORDER BY call_start DESC;

Step 6: Calls Needing Review

Find calls with low quality scores.

SELECT
    call_id,
    agent_name,
    customer_name,
    call_type,
    quality_score,
    duration_seconds / 60 as duration_minutes
FROM call_recordings
WHERE quality_score < 80
  AND is_reviewed = FALSE
ORDER BY quality_score;

Step 7: Department Statistics

Analyze calls by department.

SELECT
    department,
    COUNT(*) as call_count,
    AVG(duration_seconds) / 60 as avg_duration,
    AVG(quality_score) as avg_quality
FROM call_recordings
GROUP BY department
ORDER BY call_count DESC;

Step 8: Daily Call Volume

Track call volume by date.

SELECT
    DATE(call_start) as call_date,
    COUNT(*) as total_calls,
    SUM(duration_seconds) / 3600 as total_hours,
    AVG(quality_score) as avg_quality
FROM call_recordings
GROUP BY DATE(call_start)
ORDER BY call_date DESC;

Step 9: Long Calls Report

Find exceptionally long calls.

SELECT
    call_id,
    agent_name,
    customer_name,
    call_type,
    duration_seconds / 60 as duration_minutes,
    quality_score,
    CASE
        WHEN duration_seconds > 600 THEN 'Very Long'
        WHEN duration_seconds > 300 THEN 'Long'
        ELSE 'Normal'
    END as duration_category
FROM call_recordings
WHERE duration_seconds > 300
ORDER BY duration_seconds DESC;

Step 10: Mark Calls as Reviewed

Update review status for calls.

UPDATE call_recordings
SET is_reviewed = TRUE,
    notes = 'Reviewed by supervisor on 2024-01-16'
WHERE call_id = 'CALL-2024-0003';

SELECT
    call_id,
    is_reviewed,
    notes
FROM call_recordings
WHERE call_id = 'CALL-2024-0003';

Step 11: Customer Call History

Find all calls from a specific customer.

SELECT
    call_id,
    agent_name,
    call_type,
    duration_seconds / 60 as duration_minutes,
    call_start,
    quality_score
FROM call_recordings
WHERE customer_phone = '555-0101'
   OR customer_name LIKE '%Smith%'
ORDER BY call_start DESC;

Step 12: Compliance Report

Generate compliance overview.

SELECT
    COUNT(*) as total_calls,
    COUNT(recording) as recorded_calls,
    COUNT(*) - COUNT(recording) as unrecorded_calls,
    SUM(CASE WHEN is_reviewed THEN 1 ELSE 0 END) as reviewed_calls,
    SUM(CASE WHEN NOT is_reviewed THEN 1 ELSE 0 END) as pending_review,
    AVG(quality_score) as overall_quality
FROM call_recordings;

Cleanup (Optional)

DROP TABLE IF EXISTS call_recordings;

Expected Outcomes

• Call recordings stored with metadata
• Agent performance tracked
• Quality scores enable QA
• Review workflow supported
• Compliance reporting available

Call Types

Key Concepts Learned

• Call recording storage
• Agent performance metrics
• Quality score tracking
• Review workflow management
• Compliance reporting queries