Real Time Call Center Reporting

Hospital call centers have various (real time and historical) reporting needs that are very helpful for call center managers. Let’s look into creating real time call center analytics / reports using various AWS capabilities.

Categorized as Technology Tagged

Hospital call centers have various (real time and historical) reporting needs that are very helpful for call center managers.

I recommend using Amazon Connect Contact center software to our healthcare customers because of its ease of setup, scaling capabilities and the ability to integrate into and leverage the huge AWS ecosystem.

In this particular post, let’s look into creating real time call center analytics / reports using various AWS capabilities.

I am assuming that you have already started using Amazon Connect Contact Center software.

First, make sure you have turned on Data streaming

Hospital Call Center Real Time Reporting – data streaming

If you do not have an Amazon kinesis stream setup (I am assuming that you do not), create a new AWS Kinesis stream.

Let’s start with the contact trace records first (CTR). Create a kinesis stream for it. 

Hospital Call Center Real Time Reporting – create Kinesis stream

Now, go back to your Amazon Connect Call Center console and associate this data stream to handle your call center CTR records.

It’s a good idea to start with 1 or more shards.

For my call processing needs, I chose 4 shards. 

Hospital Call Center Real Time Reporting – use Kinesis stream

Your next step would be to create an application to process this data.

You can see that there is a “producer” and a “consumer” section in the Amazon kinesis stream that you just created.

Now, your Amazon Connect hospital call center call records will be the “producers” of data as you will tie in this newly created Kinesis stream to the Amazon Connect hospital call center console pretty soon. 

On this screen, you will be associating Consumers to the Amazon Kinesis stream that you created just now.

This “consumer” will consume all the data that your Amazon Connect hospital call center Contact Trace Records (CTRs) generate.

Keep in mind that here, you do have the choice to bypass processing with an Amazon Kinesis Delivery stream to analyze your hospital call center data.

You can choose to associate an Amazon Kinesis Data Analytics application directly to analyze your hospital call center data instead (there are some pros and cons discussed in another post).

Let’s say that you did choose to use an Amazon Kinesis Data Analytics application instead of Amazon Kinesis Firehose Data.

Here, you will find an option to choose SQL or Amazon Fink. 

I doubt that your IT team has already developed the skills to manage reading data using Amazon Fink.

If they do not know Amazon Fink, they most certainly know SQL. Go ahead and choose that.

Hospital Call Center Real Time Reporting – create Kinesis Data Analytics

I advise clients to ALWAYS use tags as it allows to filter data and associate costs back to various departments for chargeback purposes.

Even if you are not asked to help with cost allocation at the time of creating this setup, it is always best to take care of tags, rather now than later on. 

Hospital Call Center Real Time Reporting – create Kinesis Data Analytics tags

On the next screen, all you have to do is to associate the data stream you had created, with this analytics application.

Hospital Call Center Real Time Reporting – connect to Kinesis Data Analytics

Should you want to do any preprocessing with AWS Lambda, this is where you would choose the Lambda function.

However, in this post, we are assuming that you have not created any Lambda functions to preprocess the call centre CTR records. 

One GREAT thing about using Amazon Kinesis Data Analytics for your hospital call center is that it allows you to do SCHEMA DISCOVERY.

This in itself is a big time saver – do not underestimate that.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics schema discovery

However, for Amazon Kinesis Data analytics to discover the schema, you need to wait for a bit while your call center agents make/receive calls.

This generates enough call center CTRs for Kinesis Data analytics to do a proper schema discovery.

This will start the discovery process with the beginning of NOW and show you a single column schema first.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics edit schema

If you really want to, you can go ahead with a one column schema, however, it is going to be painful to do data discovery later on.

I suggest that you click on the EDIT SCHEMA button and create a schema of your own.

For this, first look at the raw data, choose JSON as the format. 

Next, copy the output and head over to some json linter tool (e.g. jsonlint.com) to clean/format it a bit.

Now, you have enough data to create your own columns.

{
     "AWSAccountId": "90290668xxxx",
     "AWSContactTraceRecordFormatVersion": "2017-03-10",
     "Agent": {
         "ARN": "arn:aws:connect:us-east-1:90290668xxxx:instance/xxxea4c7-514d-43d1-bdb9-173e51b1e729/agent/xxx8523a-7207-45e5-a76f-935694a2faf4",
         "AfterContactWorkDuration": 0,
         "AfterContactWorkEndTimestamp": null,
         "AfterContactWorkStartTimestamp": null,
         "AgentInteractionDuration": 0,
         "ConnectedToAgentTimestamp": null,
         "CustomerHoldDuration": 0,
         "HierarchyGroups": null,
         "LongestHoldDuration": 0,
         "NumberOfHolds": 0,
         "RoutingProfile": {
             "ARN": "arn:aws:connect:us-east-1:90290668xxxx:instance/xxxea4c7-514d-43d1-bdb9-173e51b1e729/routing-profile/xxxe2ae3-6507-4e53-b5b3-c083ccbadd2a",
             "Name": "xxx Calling Routing Profile"
         },
         "Username": "nh-tanvi"
     },
     "AgentConnectionAttempts": 0,
     "Attributes": {},
     "Channel": "VOICE",
     "ConnectedToSystemTimestamp": null,
     "ContactDetails": {},
     "ContactId": "xxx52ed7-585d-4434-9405-e3ddf07b9528",
     "CustomerEndpoint": {
         "Address": "+1xxx5432200",
         "Type": "TELEPHONE_NUMBER"
     },
     "DisconnectReason": "CUSTOMER_DISCONNECT",
     "DisconnectTimestamp": "2021-02-16T19:02:27Z",
     "InitialContactId": null,
     "InitiationMethod": "OUTBOUND",
     "InitiationTimestamp": "2021-02-16T19:02:15Z",
     "InstanceARN": "arn:aws:connect:us-east-1:90290668xxxx:instance/xxxea4c7-514d-43d1-bdb9-173e51b1e729",
     "LastUpdateTimestamp": "2021-02-16T19:03:34Z",
     "MediaStreams": [{
         "Type": "AUDIO"
     }],
     "NextContactId": null,
     "PreviousContactId": null,
     "Queue": {
         "ARN": "arn:aws:connect:us-east-1:90290668xxxx:instance/xxxea4c7-514d-43d1-bdb9-173e51b1e729/queue/xxx57bef-0a79-4e33-ac23-80aa3eb02e9e",
         "DequeueTimestamp": null,
         "Duration": 0,
         "EnqueueTimestamp": null,
         "Name": "A Calling Q"
     },
     "Recording": null,
     "Recordings": null,
     "References": [],
     "SystemEndpoint": {
         "Address": "+1xxx9002523",
         "Type": "TELEPHONE_NUMBER"
     },
     "TransferCompletedTimestamp": null,
     "TransferredToEndpoint": null
 }
Hospital Call Center Real Time Reporting – Kinesis Data Analytics verify schema results

Once you have added all the columns flattened from the JSON output of your Amazon Connect CTR records, you can start using this as your “real time” query engine.

If you have no errors, you will see the following results.. 

Hospital Call Center Real Time Reporting – Kinesis Data Analytics verify schema results

And once your healthcare call center agents start making/receiving calls, you will see the data as well.

Of course, in your data, you might have symbols that are reference data OR you might have these phone numbers mapped to contact records in your medical CRM.

You can very easily connect your reference data as a CSV in S3 and then query the same in real time.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – connect reference data

Here, click on the Connect Reference data button. Before you can connect to a reference database / CSV in your S3, you need to upload your file to Amazon S3.

Once you upload the file, then you can finish this section

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – connect reference data

Even here, you can discover the schema like before

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – connect reference data

Once it finishes discovering the schema (in my case, it is the NPI database), you will see the outcome as below and can also edit the schema

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – verify reference data

I recommend that you edit the schema and give the column names something that makes sense for your data.

This would be something you would refer to while running the SQL queries.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – edit reference data schema

Be careful about having commas (or the delimiter of your choice) in columns of the CSV you have uploaded to S3.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – edit reference data schema

It’s always a good idea to double check your CSV data with running Query with S3 Select on the CSV first, verifying the data that is being shown as formatted and as Raw.. and then proceeding with using this file in your Amazon Kinesis Data Analytics reference data.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – verify with S3 Select

Now that you have set yourself up with reference data and to capture real time data in your real time hospital call center data analytics application using Amazon Kinesis Data analytics, you can proceed with querying this data.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – add reference data

From time to time, you might see the following, but this should not concern you.

This only means that no CTRs are available at that minute – that’s all.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – add reference data

For our example here, I created a continuous filter like below.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – add continuous filter

All it does are:

  1. Creates a stream first (CTR_DESTINATION_SQL_STREAM) that holds one of the columns we had created before (called DATA). This DATA column holds the entire CTR record for us. Ideally, you would select specific columns, but for brevity sake, we chose data. The CTR_DESTINATION_SQL_STREAM is where your CTR record output is recorded.
  2. Creates a PUMP that takes the data from the STREAM CTR_STREAM_PUMP and selects the column DATA (as mentioned above) from the stream you had created before- namely, SOURCE_SQL_STREAM_001.
  3. Allows you to continuously query the CTR_DESTINATION_SQL_STREAM table as a normal SQL query to get the real time analytics data you are seeking.

Wait for a few seconds and if your agents are making calls, data will start showing up like below

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – verify continuous filter results

New results are added every 2-10 seconds. 

At this point, you can also connect an in-application stream to a Kinesis stream, or to a Firehose delivery stream, to continuously deliver SQL results to AWS destinations.

This could be a Lambda function, another Firehose stream, Kinesis stream etc.

This way, you can write applications that read from any of these data sources to show you the desired charts.

Do keep in mind that the limit is three destinations for each application.

Let’s say that you want to store the outputs of your continuous queries in Amazon S3 as CSV files. 

For this, you would first create a bucket/destination to hold your CSV files.

Next, go ahead and create a new Kinesis Firehose stream.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – connect to firehose delivery stream

And for source, use direct PUT

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – connect to firehose delivery stream

For the next step, you can choose to transform the record, but we are not discussing that in this post.

Instead, disable transformation and proceed.

In the next step, choose the folder you had created in Amazon S3

I always recommend compressing the files you put in S3. In this case, you can choose Snappy (pretty good).

You can also choose to ignore compression on this next page.

Wait for a bit and the Amazon Firehose stream would be created and ready for you.

Next, go back to Amazon Kinesis and connect to the destination you just created (the Amazon Kinesis Data Firehouse), choose the in-application stream you had created in the steps before and proceed.

Hospital Call Center Real Time Reporting – Kinesis Data Analytics – connect to firehose delivery stream

You are now ready to go.

As the continuous filter runs and produces results (i.e. your call center agents are working), these results will show up in your Amazon S3 bucket you created before.

Once you have your results in the Amazon S3 bucket, you can query it in your web application using Amazon Athena as well (super cheap to use).