Contact analytics data in the Connect Customer data lake

The following tables contain contact analytics data.

Conversational analytics

Table name: contact_lens_conversational_analytics

Description: Contains Contact Lens analytics data including sentiment scores, talk and non-talk time, interruptions, talk speed, and response times for voice and chat contacts.

Primary key: instance_id, contact_id

Partition key: disconnect_timestamp (daily)

Join keys:

Contact evaluation record

Table name: contact_evaluation_record

Description: Stores contact evaluation data at the form, section, and question level, including scores, answers, and generative AI automation results for quality management.

Primary key: evaluation_id, item_reference_id, instance_id

Partition key: initiation_timestamp (daily)

Join keys:

Sample queries for business outcome metrics

The following example query in Amazon Athena returns business outcome evaluation scores by metric:

SELECT
    evaluation_measurement_name,
    AVG(evaluation_measurement_score) AS avg_score,
    COUNT(*) AS evaluation_count
FROM "contact-evaluation-record-resource-link-table-name"
WHERE instance_id = 'your-instance-id'
    AND evaluation_measurement_scope = 'BUSINESS_OUTCOME'
    AND evaluation_submitted_timestamp >= TIMESTAMP 'start-date'
    AND evaluation_submitted_timestamp < TIMESTAMP 'end-date'
GROUP BY evaluation_measurement_name
ORDER BY evaluation_measurement_name;

The following example query in Amazon Athena joins business outcome scores with customer segments:

SELECT
    cp.lifetime_value_segment,
    cer.evaluation_measurement_name,
    AVG(cer.evaluation_measurement_score) AS avg_score,
    COUNT(*) AS evaluation_count
FROM "contact-evaluation-record-resource-link-table-name" cer
JOIN "customer-profiles-contact-event-resource-link-table-name" cp
    ON cer.instance_id = cp.instance_id
    AND cer.contact_id = cp.contact_id
WHERE cer.instance_id = 'your-instance-id'
    AND cer.evaluation_measurement_scope = 'BUSINESS_OUTCOME'
    AND cer.evaluation_submitted_timestamp >= TIMESTAMP 'start-date'
    AND cer.evaluation_submitted_timestamp < TIMESTAMP 'end-date'
GROUP BY cp.lifetime_value_segment, cer.evaluation_measurement_name
ORDER BY cp.lifetime_value_segment, cer.evaluation_measurement_name;

For information about creating evaluation forms with business outcome metrics that generate the scores stored in the evaluation_measurement_score column, see Create an evaluation form.

Evaluation review record

Table name: evaluation_review_record

Description: Stores evaluation review lifecycle data, capturing who requested a review of a contact evaluation, who reviewed it, and the outcome, for quality management auditing and reporting.

Primary key: evaluation_review_id, contact_id, instance_id

Partition key: creation_timestamp (daily)

Join keys:

The following table describes the columns in the evaluation_review_record table.

Sample queries for evaluation reviews

The following example query in Amazon Athena returns the number of evaluation reviews by outcome. A review without an outcome is still in progress:

SELECT
    COALESCE(outcome, 'IN_PROGRESS') AS review_status,
    COUNT(*) AS review_count
FROM "evaluation-review-record-resource-link-table-name"
WHERE instance_id = 'your-instance-id'
    AND creation_timestamp >= TIMESTAMP 'start-date'
    AND creation_timestamp < TIMESTAMP 'end-date'
GROUP BY COALESCE(outcome, 'IN_PROGRESS')
ORDER BY review_status;

The following example query in Amazon Athena returns the percentage of completed reviews with an APPROVED outcome for each evaluator who submitted the original evaluation:

SELECT
    evaluator_id,
    COUNT(*) AS completed_reviews,
    CAST(COUNT_IF(outcome = 'APPROVED') AS DOUBLE) / COUNT(*) * 100.0 AS approved_pct
FROM "evaluation-review-record-resource-link-table-name"
WHERE instance_id = 'your-instance-id'
    AND outcome IS NOT NULL
    AND creation_timestamp >= TIMESTAMP 'start-date'
    AND creation_timestamp < TIMESTAMP 'end-date'
GROUP BY evaluator_id
ORDER BY approved_pct DESC;

The following example query in Amazon Athena returns the average time, in hours, from the review request to the last update of a completed review:

SELECT
    AVG(date_diff('second', requested_timestamp, last_modified_timestamp)) / 3600.0
        AS avg_review_completion_hours
FROM "evaluation-review-record-resource-link-table-name"
WHERE instance_id = 'your-instance-id'
    AND outcome IS NOT NULL
    AND creation_timestamp >= TIMESTAMP 'start-date'
    AND creation_timestamp < TIMESTAMP 'end-date';

For information about how agents request reviews of their evaluations, see Request reviews of performance evaluations.