Create Custom Reports for HAQM Connect Cases

Introduction

HAQM Connect Cases allows your agents to track and manage customer issues that require multiple interactions, follow-up tasks, and teams in your contact center. As an organization, it becomes important to define performance metrics for resolving cases and identifying any bottlenecks.

HAQM Connect supports Cases events streams which provide you with near real-time updates when cases are created or modified within your HAQM Connect Cases domain. Organizations can generate custom reports based on these events to get deeper insights of their case resolution performance.

In this blog, you will learn how to develop a reporting and analytics solution using HAQM Athena and HAQM QuickSight.

Overview

Fig 1 – High-level architecture diagram

In this architecture, HAQM Connect publishes events near real-time to the case events streams, each time a case is created or modified. The events published to the stream include, Case reacted, Case modified and Related items (Comments, Calls, Chats, Tasks). HAQM EventBridge receives the HAQM Case event streams, tracks the case event data, and streams it to HAQM Kinesis Data Firehose. Kinesis Data Firehose collects these events for a defined interval of time, compresses these records to a parquet file, and stores this parquet file in the HAQM Simple Storage Service (S3) bucket.

AWS Glue Catalog has the table definitions for the Contact event stream. You can run queries in HAQM Athena for reporting, and HAQM QuickSight for visualization.

This solution uses a parameter named “Project Name” that defines the solution domain. You can deploy this solution multiple times with different project names to maintain isolation. Since it will be used to name fundamental components such as an S3 bucket, project name must be globally unique.

Prerequisites

To follow along with the solution presented in this blog post, you should be familiar with the following AWS services and features:

•  HAQM Connect
• HAQM EventBridge
• HAQM Simple Storage Service (S3)
• AWS CloudFormation
• HAQM Kinesis
• AWS Glue
• HAQM QuickSight
• HAQM Athena
• AWS Identity and Access Management (IAM)
• Set up case event streams
• An active AWS account with the permission to create and modify AWS IAM roles

Walkthrough

1. Sign in to the AWS Management Console.
2. Download the CloudFormation template from here onto your local machine
3. In your preferred Region, create a CloudFormation stack using the template file downloaded in step 2, by filling the Stack name and Project name.
4. Keep the defaults on the remaining options page and select Create Stack

Validation

In this step, generate data by interacting with HAQM Connect Cases. Within a few minutes, Contact Event case stream data will appear in the bucket named as your project name under “cases-relateditem-base” in “.parquet” files.

1. Navigate to HAQM Athena table, you should see “connect-cases” table. Click on three dots besides the connect_case table, then select Preview Table.
2. Athena displays the query results as seen in the following screenshot.

Visualize in HAQM QuickSight

HAQM QuickSight allows you to visualize the results of your HAQM Athena queries directly. Before connecting HAQM QuickSight to Athena, you must grant HAQM QuickSight access to Athena and its associated S3 bucket. For more information, see managing HAQM QuickSight permissions to AWS resources. After granting the access permissions, you create a new dataset in HAQM QuickSight based on the Athena table that was created.

Next create a new analysis in HAQM QuickSight.

1. Navigate to QuickSight and select Analyses.
2. Click on New Dataset, then select Athena and give the data source a name.
3. In the example below, the name chosen is ‘cases-reporting’. Afterwards, click on Create data source.
4. Choose the name of the database (same as project name in CFT) and “connect_cases_latest” view then click ‘Select’
5. Finally, click Visualize to complete the dataset creation.
6. In the next screen, you set up your analytics in the sheet.
7. Click on the fields of interest under the “Fields list” and the “Visual type” to display the visualization.
8. To get visuals similar to the following screenshots (Fig – 2), configure the Field list and Visual types as listed in the following table (Table – 1).
   

   Visual Name	Field List	Visual Types	Visual type icon
   Number of Cases	caseid (count)	KPI
   Number of open cases	status_open (sum)	KPI
   Number of closed cases	status_closed (sum)	KPI
   Average # of Hours from creation to close	creation_to_close_diff_hours (average)	KPI
   Average number of Touch points	countofcontactspercase (average)	KPI
   Average number of Tasks pre case	countoftaskspercase (average)	KPI
   Number of cases created per date	X axis – createdatetime Y axis – caseid (count)	Vertical bar chart
   Number of cases created per date with status	X axis – createdatetime Y axis – status(count) Group/Color – status	Vertical bar chart
   Number of cases by status	Group/Color – status	Pie chart

   Table-1Fig -2

9. To create a filter on CaseTemplate or CaseReason, follow the steps below
   1. Under the Parameters option, click the plus sign (+) to add a parameter
   2. Name the Parameter (e.g. CaseReason), select type (e.g. “String”) and select “single value” and click create
   3. In the next screen click on Control. Name the Control (e.g. Case Reason).
   4. Select style as “Drop-down” and select “Link to a data-set field” option.
   5. Select the data-set you created earlier, and select the field that will contain the data (case_reason). Click on Add
   6. Under the Filters option, click the plus sign (+) to add a filter. Select field to use with the filter (case_reason).
   7. Click on the newly created filter. Select filter type to be ‘Custom filter’. Check ‘Use parameters’. When asked to change the scope of the filter, select ‘Yes’.
   8. Select the parameter to be the parameter you created above. Click ‘Apply’ and ‘Close’.

To build additional customized visualizations within HAQM QuickSight, check this blog post for more details.

Clean up

In order to remove the resources created by the stack, perform the following:

1. Delete the CloudFormation template.
2. Delete the object and the S3 bucket created from the CloudFormation template. The Bucket name should contain the project name.
3. Delete the Glue database created from the CloudFormation template.
4. Delete HAQM QuickSight dashboard

Conclusion

In this blog, you learned about HAQM Connect Cases events, set up a pipeline to stream the cases’ events, built reports using Athena, and visualization using HAQM QuickSight. You can stream the cases event data in a similar fashion to other AWS services like HAQM CloudWatch or third-party applications. To visualize your cases data in Tableau and Power BI, follow Building AWS data lake visualizations with HAQM Athena and Tableau and Using the HAQM Athena Power BI connector blog, respectively.

If you are interested in learning about hands off solutions for your analytics needs, explore our accredited partner solutions in the AWS Marketplace. Partners like Joulica and Aceyus offer Contact Center metric solutions including cases reports.

For more analytics capabilities on HAQM Connect data sources, refer the following blogs in the HAQM Connect reporting blog series:

• Analyze HAQM Connect Contact Trace Record (CTR)
• Analyze HAQM Connect Contact Lens
• Analyze HAQM Connect Chat sentiments
• Analyze HAQM Connect Chatbot performance
• Analyze HAQM Connect Agent Event Stream (AES)
• Automating HAQM QuickSight dashboard creation for analyzing HAQM Connect data
• Analyze data for HAQM Connect Outbound Campaigns (Contact event Streams)
• Create Custom Reports for HAQM Connect Cases

Author Bio

	Mehmet Demir is a Senior Solutions Architect at HAQM Web Services (AWS) based in Toronto, Canada. He helps customers in building well-architected solutions that support business innovation.
	Ankur Taunk is a Senior Specialist Solutions Architect at AWS. He helps customer achieve their desired business outcomes in the Contact Center space leveraging HAQM Connect.
	Edgar Alza is a Senior Product Manager at HAQM Web Services (AWS). Edgar is passionate about building solutions that help organizations provide superb customer service. Outside of work, he loves to travel and play soccer.