Querying Amazon Connect post contact summaries from analytics JSON files

The above options should work, but you could also try running a Glue Crawler because it automatically discovers and catalogs the schema of semi-structured data like your nested JSON files, eliminating the need to manually define table structures for complex, hierarchical data. When the crawler runs, it scans your S3 bucket, analyzes the JSON structure of your files, and creates a unified table schema in the Glue Data Catalog that understands the nested relationships within your data - essentially creating a "map" that tells Athena how to interpret fields like ConversationCharacteristics.ContactSummary.PostContactSummary.Content and CustomerMetadata.ContactId. The crawler also automatically detects your folder partitioning structure (year/month/day) and creates partition metadata, which dramatically improves query performance and reduces costs when filtering by date. Once cataloged, your previously "invisible" JSON data becomes immediately queryable through standard SQL in Athena, allowing you to perform complex analytics like searching for keywords in contact summaries and joining with your existing Amazon Connect data lake tables using the ContactId as the common key. The high-level process is simply: create crawler → point it at your S3 bucket → run crawler to catalog schema → query data in Athena using SQL. Crawlers can be run on a schedule or used in different workflows.

Querying Amazon Connect Call Summaries from S3 JSON Files Using Athena

Overview

The below steps explains how to query Contact Summary data from JSON files stored in S3 using Amazon Athena, allowing integration with existing Amazon Connect data lake tables.

Table Creation

=> Create an external table in Athena to read the JSON files:

CREATE EXTERNAL TABLE call_summaries ( ConversationCharacteristics STRUCT< ContactSummary: STRUCT< PostContactSummary: STRUCT< Content: string > > >, CustomerMetadata STRUCT< ContactId: string, InputS3Uri: string, InstanceId: string > ) PARTITIONED BY ( year string, month string, day string ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://your-bucket/Analysis/Voice/' TBLPROPERTIES ('has_encrypted_data'='false');

Load Partitions

=> After table creation, load the partitions using the folllowing command:

MSCK REPAIR TABLE call_summaries;

Example Queries:

=> Basic Query for Content Search

Find contacts where "Wibble" is mentioned in the summary:

SELECT CustomerMetadata.ContactId, CustomerMetadata.InputS3Uri, ConversationCharacteristics.ContactSummary.PostContactSummary.Content FROM call_summaries WHERE LOWER(ConversationCharacteristics.ContactSummary.PostContactSummary.Content) LIKE '%wibble%';

=>Join with Contact Records

Combine summary data with contact_record table:

SELECT cs.CustomerMetadata.ContactId, cs.CustomerMetadata.InputS3Uri, cs.ConversationCharacteristics.ContactSummary.PostContactSummary.Content, cr.* FROM call_summaries cs JOIN contact_record cr ON cs.CustomerMetadata.ContactId = cr.contactid WHERE LOWER(cs.ConversationCharacteristics.ContactSummary.PostContactSummary.Content) LIKE '%wibble%';

I hope the above helps. Thank You

You can use Amazon Athena to query the JSON files containing Amazon Connect Contact Lens analytics data stored in your S3 bucket. Here's how to approach this:

1. Create an external table in Athena that maps to your JSON files in S3. The table should define the nested JSON structure including the fields you're interested in: ContactId, InputS3Uri, and the PostContactSummary Content.

2. When creating the table, you'll need to:

• Specify the JSON SerDe (serializer/deserializer)
• Define the schema to match your JSON structure with nested fields
• Point to the S3 location where your analysis files are stored
• Set up partitioning based on your folder structure (year/month/day)

3. Once the table is created, you can write SQL queries to extract and analyze the data. For example, to find contacts where "Wibble" was mentioned in the PostContactSummary:

SELECT 
CustomerMetadata.ContactId, 
CustomerMetadata.InputS3Uri,
ConversationCharacteristics.ContactSummary.PostContactSummary.Content
FROM your_table_name
WHERE LOWER(ConversationCharacteristics.ContactSummary.PostContactSummary.Content) LIKE '%wibble%'

4. You can join this data with other Amazon Connect data lake tables using the ContactId field:

SELECT 
a.CustomerMetadata.ContactId,
a.ConversationCharacteristics.ContactSummary.PostContactSummary.Content,
b.other_fields
FROM your_analysis_table a
JOIN contact_record b ON a.CustomerMetadata.ContactId = b.contactid
WHERE LOWER(a.ConversationCharacteristics.ContactSummary.PostContactSummary.Content) LIKE '%wibble%'

This approach allows you to query the nested JSON structure directly and extract the specific fields you need, then join them with other Connect data for comprehensive analysis.
Sources
Pull historical AWS Connect (CTR) Contract Trace Records | AWS re:Post
Query JSON data - Amazon Athena
Output file locations for files analyzed by Contact Lens conversational analytics - Amazon Connect