Hello thank you for reaching out.

I understand that you are able to retrieve the exact CTR (Contact Trace Records) data from Amazon Connect and that the number of CTRs in Databricks matches what is in Connect. However, when you try to calculate the "Contacts Handled Incoming" metric using your Databricks query, the numbers are slightly off from what is reported in the Amazon Connect historical metrics

I would like to answer why the the contacts handles incoming [1] metric results are slightly off. I would first like to explain the behavior of the terms in metrics.

=====Contacts handled incoming definition=====

It is the count of incoming contacts that were handled by an agent, including inbound contacts(INBOUND) and transferred contacts. (TRANSFER). Thus this metrics only have the data for those contacts which have either initiation method as INBOUND or TRANSFER. Incoming new chats are not included in this metric. Only transferred chats (both agent transfers and queue transfers) are included.

1. One possibility could be contacts routed to an agent but the agent does not answer which would show on reports as contacts missed or agent non-response. If an agent does not answer the contact should get routed to another agent, so if another agent is not currently available I would exprect the call to be requeued which may account for some small differences.
2. Another reason for this discrepancy could be due to the way Amazon Connect calculates this specific metric. The definition states that it includes "incoming contacts that were handled by an agent, including inbound contacts and transferred contacts." This means that the metric not only counts inbound contacts, but also contacts that were transferred to an agent.
3. Your current query of getting CTRs where the Agent data is not null and the initiation method is INBOUND or TRANSFER may be missing some edge cases. For example, there could be scenarios where a contact was initially routed to an agent, but the agent did not answer, and the contact was then transferred to another agent who did handle it. In this case, the contact would be counted in the "Contacts Handled Incoming" metric, but may not be captured by your current query.

Unfortunately, Amazon does not provide the exact queries they use to calculate the historical metrics. The metrics are likely calculated using a combination of the contact data, agent activity, and other factors within the Amazon Connect service.

To better align your Databricks calculations with the Amazon Connect metrics, I would suggest the following:

1. Review the historical metrics definitions in the Amazon Connect documentation more closely to understand any edge cases or nuances in how each metric is calculated.
2. Experiment with different Databricks queries that capture a wider range of contact scenarios, such as including contacts where the agent data is null (e.g., the contact was handled by an IVR or self-service flow) or where the contact was transferred multiple times or even contacts with a disconnectTimestamp.
3. Consider opening a support case [3] with AWS using the following link to get even more guidance specific to your environment historical metrics calculations.

Lastly, we undertstand the need to perform analytics on the contact center data for an extended period and build custom reports for different business needs. Since Amazon Connect is an open platform and offers APIs and mechanisms to integrate with third party reporting applications, you can stream Contact Trace Record (CTR) to Amazon Kinesis for extended retention and advanced analysis [4].

If you have further questions or need additional clarification, please don't hesitate to reach out.

References:-

[1] https://docs.aws.amazon.com/connect/latest/adminguide/historical-metrics-definitions.html#contacts-handled-incoming-historical [2] https://docs.aws.amazon.com/connect/latest/adminguide/historical-metrics-definitions.html#contacts-handled-incoming-historical [3] https://console.aws.amazon.com/support/home#/case/create [4] https://aws.amazon.com/blogs/contact-center/analyze-amazon-connect-contact-trace-record-with-amazon-athena-and-amazon-quicksight-part-1/