# Data type definitions for the Amazon Connect data lake
This topic details the content in the Amazon Connect data lake tables. Each table lists the column, type, and description of the content in the table.
There are two ways to access the analytics data lake and configure data to be shared:
+ [Option 1: Use the Amazon Connect console](access-datalake.md#option1-configure-data-to-be-shared)
+ [Option 2: Use CLI or CloudShell](access-datalake.md#option2-configure-data-to-be-shared)
If you are unable to access the scheduling tables by using Option 1, try using Option 2.
**Topics**
+ [Important things to know](#data-lake-important)
+ [Agent queue statistic record](#data-lake-agent-queue-statistic-record)
+ [Agent statistic record](#data-lake-agent-statistic-record)
+ [AI Agent](#data-lake-ai-agent)
+ [AI Agent Knowledge Base](#data-lake-ai-agent-knowledge-base)
+ [AI Prompt](#data-lake-ai-prompt)
+ [AI Session](#data-lake-ai-session)
+ [AI Tool](#data-lake-ai-tool)
+ [Connect test case execution results](#data-lake-connect-test-case-execution-results)
+ [Contact Lens conversational analytics](#data-lake-contact-lens-conversational-analytics)
+ [Contact evaluation record](#data-lake-contact-evaluation-record)
+ [Contact flow events](#data-lake-contact-flow-events)
+ [Contact record](#data-lake-contacts-record)
+ [Contact statistic record](#data-lake-contact-statistic-record)
+ [Agent event](#data-lake-agent-event)
+ [Bot analytics data](data-lake-botdata.md)
+ [Cases data](data-lake-cases-data.md)
+ [Configuration data](data-lake-configuration-data.md)
+ [Forecasting data](data-lake-forecasting-data.md)
+ [Outbound campaigns data](data-lake-outbound-campaigns-data.md)
+ [Resource tags](#data-lake-resource-tags)
+ [Scheduling data](data-lake-scheduling.md)
## Important things to know
+ The launch of new features results in additional to data fields and/or values to be added to the tables. When you develop applications that consume data lake data, we recommend that you build them to ignore the addition of new fields.
+ Amazon Connect delivers contact records at least once. Contact records may be delivered again for multiple reasons, such as new information arriving after initial delivery which may update the data in the record. For example, when you use the [update-contact-attributes](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/connect/update-contact-attributes.html) CLI command to update a contact record, Amazon Connect delivers a new contact record.
+ For information about data retention, see [Data retention in the Amazon Connect analytics data lake](data-lake-data-retention.md).
## Agent queue statistic record
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| aqsr\$1statistic\$1id | string | Unique ID for the AQSR record |
| instance\$1id | string | The identifier of the Amazon Connect instance. You can [find the instance ID](find-instance-arn.md) in the Amazon Resource Name (ARN) of the instance. |
| user\$1id | string | The identifier of the user account. |
| routing\$1profile\$1id | string | ID of routing profile. |
| agent\$1hierarchy\$1level\$11\$1id | string | ID of level 1 Hierarchy Group. |
| agent\$1hierarchy\$1level\$12\$1id | string | ID of level 2 Hierarchy Group. |
| agent\$1hierarchy\$1level\$13\$1id | string | ID of level 3 Hierarchy Group. |
| agent\$1hierarchy\$1level\$14\$1id | string | ID of level 4 Hierarchy Group. |
| agent\$1hierarchy\$1level\$15\$1id | string | ID of level 5 Hierarchy Group. |
| interval\$1start\$1time | Timestamp | Timestamp of the conversation when interval started. |
| interval\$1end\$1time | Timestamp | Timestamp of the conversation when interval ended. |
| published\$1date | Timestamp | |
| aws\$1account\$1id | string | The ID of the AWS account that owns the contact. |
| queue\$1id | string | The identifier of the queue. |
| channel | string | The method used to contact your contact center: VOICE, CHAT, TASK, EMAIL. |
| queue\$1type | string | QueueType specifies the classification of the queue, indicating its intended use and behavior. Valid values for QueueType are STANDARD, which is a general-purpose queue where contacts wait before they are routed to and accepted by agents, and AGENT, which are created automatically for every agent user you add Amazon Connect. |
| agent\$1non\$1response | bigint | The count of contacts routed to an agent but not answered by that agent, including contacts abandoned by the customer. |
| contacts\$1offered | bigint | |
| contacts\$1handled | bigint | The count of contacts that were connected to an agent. It doesn't matter how the contact got to the agent. It could be a customer calling your contact center, or an agent calling the customer. It could be a contact transferred from one agent to another. It could be a contact where the agent answered it, but then they weren't sure what to do and they transferred the contact away again. As long as the agent was connected to the contact, it increments Contacts handled. |
| handle\$1time | bigint | The average time, from start to finish, that a contact was connected with an agent (average handled time). It includes talk time, hold time, After Contact Work (ACW) time, custom status time, and agent pause duration (which applies only to tasks). AHT is calculated by averaging the amount of time between the contact being answered by an agent and the conversation ending. It applies to both inbound and outbound calls. |
| agent\$1incoming\$1connecting\$1time | bigint | The total time between when a contact is initiated by Amazon Connect reserving the agent for the contact, and the agent is connected. |
| agent\$1outbound\$1connecting\$1time | bigint | Total time between when an outbound contact is initiated by Amazon Connect reserving the agent for the contact, and the agent is connected. |
| agent\$1callback\$1connecting\$1time | bigint | The total time between when a callback contact is initiated by Amazon Connect reserving the agent for the contact, and the agent is connected. |
| agent\$1api\$1connecting\$1time | bigint | The total time between when a contact is initiated using an Amazon Connect API, and the agent is connected. |
| incoming\$1connecting\$1attempts | bigint | The number of attempts initiated by Amazon Connect reserving the agent for the contact for INBOUND initiation type contacts. |
| outbound\$1connecting\$1attempts | bigint | The number of attempts initiated by Amazon Connect reserving the agent for the contact for outbound initiation type contacts. |
| callback\$1connecting\$1attempts | bigint | The number of contacts where a callback was attempted, but the customer did not pick up. |
| api\$1connecting\$1attempts | bigint | Used for calculating - [Average agent incoming connecting time](metrics-definitions.md#average-agent-incoming-connecting-time). |
| data\$1lake\$1last\$1processed\$1Timestamp | Timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Agent statistic record
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| asr\$1statistic\$1id | string | Unique ID for the ASR record. |
| instance\$1id | string | The identifier of the Amazon Connect instance. You can [find the instance ID](https://docs.aws.amazon.com/connect/latest/adminguide/find-instance-arn.html) in the Amazon Resource Name (ARN) of the instance. |
| user\$1id | string | The identifier of the user account. |
| routing\$1profile\$1id | string | ID of routing profile. |
| agent\$1hierarchy\$1level\$11\$1id | string | ID of level 1 Hierarchy Group. |
| agent\$1hierarchy\$1level\$12\$1id | string | ID of level 2 Hierarchy Group. |
| agent\$1hierarchy\$1level\$13\$1id | string | ID of level 3 Hierarchy Group. |
| agent\$1hierarchy\$1level\$14\$1id | string | ID of level 4 Hierarchy Group. |
| agent\$1hierarchy\$1level\$15\$1id | string | ID of level 5 Hierarchy Group. |
| interval\$1start\$1time | Timestamp | Timestamp of the conversation when interval started. |
| interval\$1end\$1time | Timestamp | Timestamp of the conversation when interval ended. |
| published\$1date | Timestamp | |
| aws\$1account\$1id | string | The ID of the AWS account that owns the contact. |
| online\$1time | bigint | Total time that an agent spent with their CCP set to a status other than Offline. This includes any time spent in a custom status. When you create a historical metrics reports, this metric can't be grouped or filtered by queue, phone number, or channels. |
| error\$1time | bigint | For a specific agent, the total time contacts were in an error status. This metric can't be grouped or filtered by queue. |
| non\$1productive\$1time | bigint | Total time that agents spent in a [custom status](https://docs.aws.amazon.com/connect/latest/adminguide/agent-custom.html). That is, their CCP status is other than Available or Offline. This metric doesn't mean that the agent was spending their time unproductively. |
| agent\$1idle\$1time | bigint | After the agent sets their status in the CCP to Available, this is the amount of time they weren't handling contacts \$1 any time their contacts were in an Error state. Agent idle time doesn't include the amount of time from when Amazon Connect starts routing the contact to the agent, to when agent picks up or declines the contact. |
| agent\$1on\$1contact\$1time | bigint | Total time that an agent spent on one or multiple contacts, including [Customer hold time](metrics-definitions.md#customer-hold-time) and [After contact work time](metrics-definitions.md#after-contact-work-time). This does not include time spent on a contact while in a custom status or Offline status. (Custom status = the agent's CCP status is other than Available or Offline. For example, Training would be a custom status). |
| custom\$1state\$1time\$101 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$102 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$103 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$104 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$105 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$106 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$107 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$108 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$109 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$110 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$111 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$112 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$113 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$114 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$115 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$116 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$117 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$118 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$119 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$120 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$121 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$122 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$123 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$124 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$125 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$126 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$127 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$128 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$129 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$130 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$131 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$132 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$133 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$134 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$135 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$136 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$137 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$138 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$139 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$140 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$141 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$142 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$143 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$144 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$145 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$146 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$147 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$148 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$149 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| custom\$1state\$1time\$150 | bigint | Represents custom agent states defined by a customer. For example: Coffee\$1break. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## AI Agent
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1arn | string | The ARN of the Connect instance. |
| instance\$1id | string | The ID of the Connect instance. |
| contact\$1id | string | The ID of the contact. |
| ai\$1agent\$1id | string | The Id of the requested AI Agent. |
| ai\$1agent\$1version | string | The version of the requested AI Agent. |
| ai\$1agent\$1event\$1id | string | Id of the event. |
| aws\$1account\$1id | string | The ID of the AWS account where AI Assistant is used. |
| assistant\$1id | string | The ID of the AI Assistant. |
| ai\$1session\$1id | string | The ID of AI-Agent session. |
| creation\$1timestamp | bigint | The timestamp of the event is created in the data lake. |
| update\$1timestamp | bigint | The timestamp of the event is updated in the data lake. |
| ai\$1use\$1case | string | The use case of the AI agent. |
| ai\$1agent\$1type | string | The type of the requested AI Agent. |
| ai\$1agent\$1name | string | The name of the requested AI Agent. |
| ai\$1agent\$1arn | string | The Arn of the requested AI Agent. |
| invocation\$1success | bool | A boolean field which indicates whether the invocation of the AI agent has been successful or not. |
| invocation\$1latency\$1ms | float | The invocation latency of the AI Agent in the evaluated contact. |
| conversation\$1turns\$1in\$1response | bigint | The number of conversation turns responded by the requested AI Agent. |
## AI Agent Knowledge Base
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1arn | string | The ARN of the Connect instance. |
| aws\$1account\$1id | string | The identifer of the AWS account that owns Connect AI Assistant. |
| instance\$1id | string | The ID of the Connect instance. |
| contact\$1id | string | The ID of the specific contact . |
| knowledge\$1content\$1id | string | The ID of the referenced knowledge content. |
| ai\$1agent\$1type | string | The type of the requested AI Agent. |
| ai\$1agent\$1knowledge\$1base\$1event\$1id | string | The ID of the knowledge base reference event. |
| assistant\$1id | string | The ID of the Amazon Connect AI Assistant. |
| ai\$1session\$1id | string | The ID of AI Agent session. |
| creation\$1timestamp | string | The instant the data lake event was created. |
| update\$1timestamp | string | The instant the data lake event was last modified. |
| ai\$1agent\$1id | string | The ID of requested AI Agent. |
| ai\$1agent\$1name | string | The name of the requested AI Agent. |
| ai\$1agent\$1version | string | The version number of the requested AI Agent. |
| ai\$1agent\$1arn | string | The ARN of the requested AI Agent. |
| knowledge\$1base\$1id | string | The ID of the referenced knowledge base. |
| knowledge\$1base\$1name | string | The name of the referenced knowledge base. |
| knowledge\$1content\$1reference | string | The title of the referenced knowledge content. |
| data\$1lake\$1last\$1processed\$1timestamp | timestamp | The timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## AI Prompt
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| aws\$1account\$1id | string | The customer AWS account ID. |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| contact\$1id | string | The ID of the contact. |
| ai\$1prompt\$1id | string | The ID of the requested AI-prompt. |
| ai\$1prompt\$1version | string | The version of the requested AI-prompt. |
| ai\$1prompt\$1event\$1id | string | The ID of the event. |
| assistant\$1id | string | The identifier of the AI Assistant. |
| ai\$1session\$1id | string | The ID of AI-Agent session. |
| creation\$1timestamp | bigint | The timestamp when the event is created in the data lake. |
| update\$1timestamp | bigint | The timestamp when the event is updated in the data lake. |
| ai\$1agent\$1type | string | The type of the requested AI Agent. |
| ai\$1agent\$1name | string | The name of the requested AI Agent. |
| ai\$1agent\$1id | string | The Id of the requested AI Agent. |
| ai\$1agent\$1version | string | The version number of the requested AI Agent. |
| ai\$1agent\$1arn | string | The Arn of the requested AI Agent. |
| ai\$1prompt\$1type | string | The type of the invoked AI Prompt. |
| ai\$1prompt\$1name | string | The name of the invoked AI Prompt. |
| ai\$1prompt\$1arn | string | The arn of the invoked AI Prompt. |
| model\$1id | string | The name of the llm model associated to the AI Prompt. |
| invocation\$1success | boolean | A boolean field which indicates whether the invocation of the prompt has been successful or not. |
| invocation\$1latency\$1ms | float | The invocation latency of the AI Prompt in the evaluated contact. |
| input\$1token | bigint | The input token of the AI Prompt in the evaluated contact. |
| output\$1token | bigint | The output token of the AI Prompt in the evaluated contact. |
## AI Session
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| contact\$1id | string | The ID of the contact. |
| ai\$1session\$1id | string | The ID of the AI-Agent session. |
| aws\$1account\$1id | string | The customer AWS account ID. |
| assistant\$1id | string | The identifier of the Connect AI agents assistant. |
| creation\$1timestamp | bigint | The timestamp when the event is created in the data lake. |
| update\$1timestamp | bigint | The timestamp when the event is updated in the data lake. |
| proactive\$1intents\$1detected | bigint | The number of proactive intents (customer queries) detected during the AI session for an Agent Assistance use case. |
| proactive\$1intents\$1engaged | bigint | The number of proactive intents (customer queries) engaged in the AI session for an Agent Assistance use case. |
| proactive\$1intents\$1answered | bigint | The number of proactive intents (customer queries) answered in the AI session for an Agent Assistance use case. |
| ai\$1agent\$1invocation\$1count | bigint | The number of AI Agent invocations in the AI session. |
| ai\$1agent\$1invocation\$1success\$1count | bigint | The number of successful AI Agent invocations in the AI session. |
| is\$1handed\$1off | boolean | A boolean field which indicates whether the AI agent has handed off to the human agent during the AI session. |
| avg\$1conversation\$1turns\$1in\$1response | float | The average number of conversation turns in response of AI Agent invocation. |
## AI Tool
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1arn | string | The ARN of the Connect instance. |
| aws\$1account\$1id | string | The identifier of the AWS account that owns Connect AI Assistant. |
| instance\$1id | string | The ID of the Connect instance. |
| contact\$1id | string | The ID of the contact . |
| ai\$1agent\$1id | string | The ID of requested AI Agent. |
| ai\$1tool\$1id | string | The ID of requested AI tool. |
| ai\$1tool\$1event\$1id | string | The ID of the AI Tool invocation event. |
| assistant\$1id | string | The ID of the Amazon Connect AI Assistant. |
| ai\$1session\$1id | string | The ID of AI Agent session. |
| creation\$1timestamp | bigint | The instant the data lake event was created. |
| update\$1timestamp | bigint | The instant the data lake event was last modified. |
| ai\$1agent\$1type | string | The type of the requested AI Agent. |
| ai\$1agent\$1name | string | The name of the requested AI Agent. |
| ai\$1agent\$1version | string | The version number of the requested AI Agent. |
| ai\$1agent\$1arn | string | The ARN of the requested AI Agent. |
| ai\$1tool\$1type | string | The type of the invoked AI tool. |
| ai\$1tool\$1name | string | The name of the invoked AI tool. |
| ai\$1tool\$1arn | string | The ARN of the invoked AI tool. |
| invocation\$1success | boolean | A boolean field which indicates whether the invocation of the tool has been successful or not. |
| invocation\$1latency\$1ms | float | The invocation latency for AI tool calling. |
## Connect test case execution results
**Connect Test Case Execution Results**
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The identifier of the Amazon Connect instance |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| aws\$1account\$1id | string | The ID of the AWS account that owns the contact. |
| connect\$1test\$1case\$1resource\$1arn | string | Primary Key - The ARN of the Test Case. |
| connect\$1test\$1case\$1resource\$1name | string | The name of the Test Case. |
| connect\$1test\$1case\$1execution\$1start\$1timestamp | timestamp | Primary Key - The start time of simulated execution. |
| connect\$1test\$1case\$1execution\$1end\$1timestamp | timestamp | The end time of simulated execution. |
| connect\$1test\$1case\$1status | string | Current state of Test. Values: Scheduled, InProgress, Completed, Aborted, Failed |
| connect\$1test\$1case\$1result | string | The final result of Test execution. Values: Passed, Failed, Skipped, Aborted |
| connect\$1test\$1case\$1execution\$1id | string | Primary Key - The unique identifier for Test execution. |
| connect\$1test\$1case\$1failure\$1reasons | array(string) | The reasons for failed Test. |
| connect\$1test\$1case\$1type | string | The type of Test executed. Values: ExperienceTest, FlowTest, BotTest |
| connect\$1test\$1case\$1execution\$1method | string | The method of starting Test execution. Values: Manual, API, Scheduled, ContactRuleTriggered, FlowPublishTriggered |
| connect\$1test\$1case\$1execution\$1channel | string | The channel of simulated interaction. Values: Chat, Voice, Campaign, Task, Email |
| connect\$1test\$1case\$1execution\$1channel\$1subtype | string | The channel subtype of simulated interaction. Values: SMS, WhatsApp |
| connect\$1test\$1case\$1associated\$1initial\$1contact\$1id | string | Initial contact ID to start Test. |
| connect\$1test\$1case\$1associated\$1contact\$1ids | array(string) | List of contact IDs created as part of Test. |
| connect\$1test\$1case\$1initiating\$1flow\$1id | string | First resource ID Test Execution started with. |
| record\$1creation\$1timestamp | timestamp | The time of record creation |
## Contact Lens conversational analytics
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| aws\$1account\$1id | string | The ID of the AWS account that owns the contact. |
| version | string | Indicates real-time or post call/chat analysis. |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| contact\$1id | string | The ID of the contact being evaluated. |
| channel | string | The method used to contact your contact center: VOICE, CHAT. |
| language\$1locale | string | Language used to analyze contact - [AI features](supported-languages.md#supported-languages-contact-lens). |
| feature | string | Will always have the same value "contact\$1lens\$1conversational\$1analytics". |
| categories | array(string) | Array of categories assigned to the contact. |
| disconnect\$1timestamp | Timestamp | The contact disconnect Timestamp. |
| greeting\$1time\$1agent\$1ms | bigint | First response time of agents on chat, indicating how quickly they engage with customers after joining the chat. |
| non\$1talk\$1time\$1total\$1ms | bigint | Total non-talk time in a voice conversation. Non-talk time refers to the combined duration of hold time and periods of silence exceeding 3 seconds, during which neither the agent nor the customer is engaged in conversation. |
| talk\$1time\$1total\$1ms | bigint | Time that was spent talking during a voice contact across either the customer or the agent. |
| talk\$1time\$1agent\$1ms | bigint | Time that was spent talking during a voice contact by the agent. |
| talk\$1time\$1customer\$1ms | bigint | Time that was spent talking during a voice contact by the customer. |
| total\$1conversation\$1duration\$1ms | bigint | The total time from the start of the conversation until the last word spoken by either the agent or the customer. |
| talk\$1speed\$1agent\$1wpm | float | Words per minute spoken by the agent. |
| talk\$1speed\$1customer\$1wpm | float | Words per minute spoken by the customer. |
| interruptions\$1time\$1total\$1ms | bigint | Amount of time agent or customer were speaking at the same time. |
| interruptions\$1time\$1agent\$1ms | bigint | Amount of time the agent spoke while the customer was already speaking. |
| interruptions\$1time\$1customer\$1ms | bigint | Amount of time the customer spoke while the agent was already speaking. |
| interruptions\$1total\$1count | bigint | Count of times interruptions were detected during a conversation. |
| interruptions\$1agent\$1count | bigint | Count of time an agent interruption was detected during a conversation |
| interruptions\$1customer\$1count | bigint | Count of times a customer interruption was detected during a conversation |
| sentiment\$1overall\$1score\$1agent | float | A sentiment score is an analysis of text, and a rating of whether it includes mostly positive, negative, or neutral language. This is overall sentiment score for the agent during the call. The overall sentiment score is the average of the scores assigned during each portion of the call. |
| sentiment\$1overall\$1score\$1customer | float | A sentiment score is an analysis of text, and a rating of whether it includes mostly positive, negative, or neutral language. This is overall sentiment score for customer during the call. The overall sentiment score is the average of the scores assigned during each portion of the call. |
| sentiment\$1interaction\$1score\$1customer\$1with\$1agent | float | A sentiment score is an analysis of text, and a rating of whether it includes mostly positive, negative, or neutral language. This is sentiment score of customer with agent. |
| sentiment\$1interaction\$1score\$1customer\$1without\$1agent | float | A sentiment score is an analysis of text, and a rating of whether it includes mostly positive, negative, or neutral language. This is sentiment score of customer without the agent. |
| sentiment\$1end\$1score\$1agent | float | A sentiment score is an analysis of text, and a rating of whether it includes mostly positive, negative, or neutral language. This is sentiment score for agent at the end of the call. |
| sentiment\$1end\$1score\$1customer | float | A sentiment score is an analysis of text, and a rating of whether it includes mostly positive, negative, or neutral language. This is sentiment score for customer at the end of the call. |
| response\$1time\$1average\$1agent\$1ms | bigint | For chat, average time to send a response after the customers last message. |
| response\$1time\$1average\$1customer\$1ms | bigint | For chat, average time to send a response after the agents last message. |
| response\$1time\$1maximum\$1agent\$1ms | bigint | For chat, maximum time to send a response after the customers last message. |
| response\$1time\$1maximum\$1customer\$1ms | bigint | For chat, maximum time to send a response after the customers last message. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Contact evaluation record
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| aws\$1account\$1id | string | The ID of the AWS account that owns the contact. |
| instance\$1id | string | The identifier of the Amazon Connect instance. You can [find the instance ID](https://docs.aws.amazon.com/connect/latest/adminguide/find-instance-arn.html) in the Amazon Resource Name (ARN) of the instance. |
| evaluation\$1id | string | primary key, ID of the evaluation to disambiguate multiple evaluations done of the call with the same form (by different evaluators). |
| item\$1reference\$1id | string | primary key - may represent form/ section/ sub-section/ question depending on type. |
| item\$1type | string | Defines "Form/Section/sub-section/question or indicates a deleted record. |
| contact\$1id | string | The ID of the contact being evaluated. |
| evaluation\$1submitted\$1timestamp | Timestamp | Timestamp when contact was evaluated. |
| score | double | Score in percentage value for forms/sections/questions. |
| weighted\$1score | double | Score adding up to 100% of form, for example, 2 sections - one of 80, other out of 20. |
| automatic\$1fail | Boolean | Boolean to indicate if automatic fail was applied. |
| evaluator\$1id | string | user\$1ID of evaluator. |
| numeric\$1answer | double | Value for question where answer type is numeric. |
| answer\$1reference\$1id | string | for single select answer type. |
| to\$1delete | Boolean | Set to true if Form/Section/sub-section/question was deleted. |
| disconnect\$1timestamp | Timestamp | The contact disconnect Timestamp. |
| initiation\$1timestamp | Timestamp | The contact initiation Timestamp. |
| user\$1id | string | The user\$1id of person being evaluated. |
| queue\$1id | string | The queue\$1id of queue which contact was handled from. |
| channel | string | The method used to contact your contact center: VOICE, CHAT, TASK, EMAIL. |
| contact\$1aggregation\$1timestamp | Timestamp | Timestamp used for building aggregated agent, queue and weekly aggregation tables. |
| evaluated\$1contact\$1with\$1status | string | The connection status of the evaluated contact at the time of evaluation. Valid Values: disconnected \$1 notDisconnected. disconnected indicates the evaluated contact had ended (a disconnect timestamp exists). notDisconnected indicates the evaluated contact was still active at the time of evaluation (no disconnect timestamp). |
| evaluation\$1source | string | Indicates the origin of the evaluation process. This field indicates whether the evaluation was performed manually, with the assistance of automation or entirely automatically (without human review prior to submission). Assistance of automation encompasses pre-configured automation to answer a question (for example, auto-filling an answer based on a Contact Lens category) or asking AI for assistance while evaluating the contact. |
| resubmitted | Boolean | Indicates whether the evaluation has been resubmitted. This field helps quickly identify evaluations which were resubmitted to perform audits of the evaluation process. |
| evaluation\$1type | string | Helps distinguish between different types of evaluations, such as standard evaluations and calibration evaluations. This provides the ability to only include relevant types of evaluations while performing analysis, for example, only standard evaluations should be used to calculate the aggregated score of an agent. |
| calibration\$1session\$1id | string | Holds a unique identifier for a calibration session. This field is essential for identifying evaluations associated with a calibration session. |
| item\$1title | string | Column captures the title of the form item. This can be a form, section, subsection or question title depending on item\$1type. |
| form\$1version | string | Indicates the version number of the evaluation form used. This field helps identify different versions of the evaluation form for analysis and reporting. |
| acknowledgement\$1status | string | Acknowledgement status of the evaluation. Valid values: ACKNOWLEDGED\$1UNACKNOWLEDGED |
| acknowledger\$1id | string | user\$1id of the person who acknowledged the evaluation. |
| evaluation\$1acknowledged\$1timestamp | Timestamp | Timestamp when the evaluation was acknowledged. |
| acknowledger\$1comment | string | Comment left by the user who acknowledged the evaluation. |
| item\$1disabled | Boolean | The itemDisabled column indicates whether the item is in the disabled state at the time of submission from a condition defined in the evaluation form. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
| multi\$1select\$1answer\$1reference\$1ids | array(string) | Value for question where answer type is multi-select. |
| date\$1time\$1answer | Timestamp | Value for question where answer type is dateTime. |
| evaluated\$1participant\$1role | string | The role of the evaluated contact participant. |
| evaluated\$1participant\$1id | string | The ID of the evaluated contact participant. |
| is\$1sampled | Boolean | Whether the evaluation was created by a sampling job. |
| is\$1reviewed | Boolean | Indicates that the evaluation was reviewed. |
| automation\$1gen\$1ai\$1text\$1answer | string | The Gen AI-generated answer for an evaluation question where the answer type is text. |
| automation\$1gen\$1ai\$1answer\$1reference\$1id | string | The reference ID of the Gen AI-generated answer for an evaluation question where the answer type is single select. |
| automation\$1gen\$1ai\$1answer\$1justification | string | The justification provided by Gen AI for its automated evaluation answer. |
| is\$1automation\$1answer\$1accepted | Boolean | Indicates whether the Gen AI-generated answer was accepted and used as the final answer for the evaluation question. |
## Contact flow events
| **Column** | **Type** | **Description** | |
| --- | --- | --- | --- |
| instance\$1id | string | The identifier of the Amazon Connect instance. You can [find the instance ID](https://docs.aws.amazon.com/connect/latest/adminguide/find-instance-arn.html) in the Amazon Resource Name (ARN) of the instance. | |
| event\$1id | string | The ID of the contact as it interacts with the flow. | |
| aws\$1account\$1id | string | The ID of the AWS account that owns the contact. | |
| instance\$1arn | string | The ARN of the Amazon Connect instance. | |
| contact\$1id | string | The ID of the contact in the contact record. | |
| flow\$1resource\$1id | string | Flow Id | |
| module\$1resource\$1id | string | Module Id | |
| resource\$1version | string | Version of the contact flow used. | |
| resource\$1type | string | Can be flow or module. | |
| channel | string | The method used to contact your contact center: VOICE, CHAT, TASK, EMAIL. | |
| start\$1timestamp | Timestamp | Date and time of the start event in unix epoch, UTC | |
| end\$1timestamp | Timestamp | Date and time of the end event in unix epoch, UTC. | |
| next\$1flow\$1resource\$1id | string | Next contact flow resourceId. | |
| next\$1queue\$1resource\$1id | string | Next queue resourceId. | |
| next\$1resource\$1type | string | It can be flow or queue. | |
| flow\$1language\$1version | string | Flow language version. | |
| flow\$1outcome | string | This will contain the system defined and custom outcomes. | |
| sub\$1type | string | This field can be used to show channel subtype. For example, connect:Guide or connect:SMS or connect:Email. | |
| flow\$1type | string | Amazon Connect includes a set of nine flow types. For more information, see [Choose a flow type](create-contact-flow.md#contact-flow-types). | |
| initiation\$1method | string | Every contact in your Amazon Connect contact center is initiated by one of the following methods: Inbound, Outbound, Transfer, Callback, API, Queue Transfer, Disconnect. | |
| resource\$1published\$1timestamp | Timestamp | "Creation" or "revision" date of the flow itself. | |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. | |
## Contact record
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| aws\$1account\$1id | string | The ID of the AWS account that owns the contact. |
| contact\$1id | string | The ID of the contact in the contact record. |
| initial\$1contact\$1id | string | The unique identifier for the contact associated with the first interaction between the customer and your contact center. Use the initial contact ID to track contacts between flows. |
| previous\$1contact\$1id | string | The unique identifier for the contact before it was transferred. Use the previous contact ID to trace contacts between flows. |
| related\$1contact\$1id | string | Each time a contact is connected to an agent, a new contact record is created. The contact records for a contact are linked together through the contactId fields: related. |
| next\$1contact\$1id | string | Each time a contact is connected to an agent, a new contact record is created. The contact records for a contact are linked together through the contactId fields: initial, next, previous. |
| channel | string | The method used to contact your contact center: VOICE, CHAT, TASK, EMAIL. |
| initiation\$1method | string | How the contact was initiated. Valid values include: INBOUND, OUTBOUND, TRANSFER, CALLBACK, QUEUE\$1TRANSFER, EXTERNAL\$1OUTBOUND, MONITOR, DISCONNECT, and API . |
| initiation\$1timestamp | Timestamp | The contact initiation Timestamp. |
| connected\$1to\$1system\$1timestamp | Timestamp | The Timestamp marking the date and time the customer endpoint connected to Amazon Connect, in UTC time. For INBOUND, this matches InitiationTimestamp. For OUTBOUND, CALLBACK, and API, this is when the customer endpoint answers. |
| last\$1update\$1timestamp | Timestamp | The Timestamp, which shows the last time the data lake touched a record in the data lake. |
| scheduled\$1timestamp | Timestamp | The date and time when this contact was scheduled to trigger the flow to run, in UTC time. This is supported only for the task channel. |
| transfer\$1completed\$1timestamp | Timestamp | The Timestamp for the completion of the transfer. |
| disconnect\$1timestamp | Timestamp | The contact disconnect Timestamp. |
| disconnect\$1reason | string | The reason for disconnect of the call. |
| queue\$1duration\$1ms | bigint | The duration that a contact spent waiting in the queue in milliseconds. |
| queue\$1dequeue\$1timestamp | Timestamp | The Timestamp of contacts transferred out of the queue from another queue during a Customer queue flow. |
| queue\$1enqueue\$1timestamp | Timestamp | The Timestamp of contacts transferred into the queue from another queue during a Customer queue flow. |
| queue\$1name | string | The name of the queue. |
| queue\$1arn | string | ARN of the queue. |
| queue\$1id | string | ID of the queue. |
| agent\$1connection\$1attempts | bigint | The number of times Amazon Connect attempted to connect this contact with an agent. |
| agent\$1connected\$1to\$1agent\$1timestamp | Timestamp | The Timestamp the contact was connected to the agent. |
| agent\$1interaction\$1duration\$1ms | bigint | The total time that agents spent interacting with customers in milliseconds. |
| agent\$1customer\$1hold\$1duration\$1ms | bigint | Total time that agents and contact were on hold in milliseconds. |
| agent\$1number\$1of\$1holds | bigint | The count of contacts that were placed on hold by the agent |
| agent\$1longest\$1hold\$1duration\$1ms | bigint | The longest time, in whole seconds, that the customer was put on hold by the agent. |
| agent\$1after\$1contact\$1work\$1start\$1timestamp | Timestamp | The Timestamp marking the start of the AfterContactWork state. |
| agent\$1after\$1contact\$1work\$1end\$1timestamp | Timestamp | The Timestamp marking the end of the AfterContactWork state. |
| agent\$1after\$1contact\$1work\$1duration\$1ms | bigint | The total time that an agent spent doing ACW for a contact in milliseconds. In some businesses, also known as Call Wrap Up time. |
| attributes | map(string,string) | A contact attribute represents this data as a key-value pair. You might think of it as a field name together with the data entered into that field. |
| agent\$1username | string | The user name of the agent, as entered in their Amazon Connect user account. |
| agent\$1arn | string | The ARN of the agent, as created in their Amazon Connect user account. |
| agent\$1id | string | The ID of the agent, as created in their Amazon Connect user account. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| agent\$1hierarchy\$1groups\$1level\$11\$1name | string | The hierarchy name the agent is assigned to, for first level. |
| agent\$1hierarchy\$1groups\$1level\$11\$1arn | string | The hierarchy ARN the agent is assigned to, for first level. |
| agent\$1hierarchy\$1groups\$1level\$11\$1id | string | The hierarchy ID the agent is assigned to, for first level. |
| agent\$1hierarchy\$1groups\$1level\$12\$1name | string | The hierarchy name the agent is assigned to, for second level. |
| agent\$1hierarchy\$1groups\$1level\$12\$1arn | string | The hierarchy ARN the agent is assigned to, for second level. |
| agent\$1hierarchy\$1groups\$1level\$12\$1id | string | The hierarchy ID the agent is assigned to, for second level. |
| agent\$1hierarchy\$1groups\$1level\$13\$1name | string | The hierarchy name the agent is assigned to, for third level. |
| agent\$1hierarchy\$1groups\$1level\$13\$1arn | string | The hierarchy ARN the agent is assigned to, for third level. |
| agent\$1hierarchy\$1groups\$1level\$13\$1id | string | The hierarchy ID the agent is assigned to, for third level. |
| agent\$1hierarchy\$1groups\$1level\$14\$1name | string | The hierarchy name the agent is assigned to, for fourth level. |
| agent\$1hierarchy\$1groups\$1level\$14\$1arn | string | The hierarchy ARN the agent is assigned to, for fourth level. |
| agent\$1hierarchy\$1groups\$1level\$14\$1id | string | The hierarchy ID the agent is assigned to, for fourth level. |
| agent\$1hierarchy\$1groups\$1level\$15\$1name | string | The hierarchy name the agent is assigned to, for fifth level. |
| agent\$1hierarchy\$1groups\$1level\$15\$1arn | string | The hierarchy ARN the agent is assigned to, for fifth level. |
| agent\$1hierarchy\$1groups\$1level\$15\$1id | string | The hierarchy ID the agent is assigned to, for fifth level. |
| agent\$1routing\$1profile\$1name | string | The name of routing profile for the agent. |
| agent\$1routing\$1profile\$1arn | string | The ARN of routing profile for the agent. |
| agent\$1routing\$1profile\$1id | string | The ID of the routing profile for the agent. |
| agent\$1voice\$1enhancement\$1mode | string | The voice enhancement mode used by the agent. Valid values: VOICE\$1ISOLATION \$1 NOISE\$1SUPPRESSION \$1 NONE. A value of null indicates this mode has not yet been set for this user. |
| aws\$1contact\$1trace\$1record\$1format\$1version | string | The record format version. |
| campaign\$1Id | string | The ID associated with an outbound campaign to assist with tracking campaigns. |
| customer\$1endpoint\$1type | string | The type of the customer endpoint. Valid value is TELEPHONE\$1NUMBER. |
| customer\$1endpoint\$1address | string | The customer or external third party participant endpoint address. |
| transferred\$1endpoint\$1type | string | The customer or external third party participant transferred endpoint type. |
| transferred\$1endpoint\$1address | string | The customer or external third party participant transferred endpoint address. |
| system\$1endpoint\$1type | string | The type of the system endpoint. Valid value is TELEPHONE\$1NUMBER. |
| system\$1endpoint\$1address | string | The system endpoint type address. |
| recording\$1deletion\$1reason | string | If the recording was deleted, this is the reason entered for the deletion. |
| recording\$1location | string | The location, in Amazon S3, for the recording. |
| recording\$1status | string | The recording status. Valid values: AVAILABLE \$1 DELETED \$1 NULL. |
| recording\$1type | string | The recording type. Valid values: AUDIO. |
| answering\$1machine\$1detection\$1Status | string | The status of whether an answering machine was detected. |
| voice\$1id\$1result\$1authentication\$1result | string | The voice authentication information for the call. |
| voice\$1id\$1result\$1fraud\$1detection\$1watch\$1list\$1id | string | The fraud detection information for the call for watch list. |
| voice\$1id\$1result\$1speaker\$1id | string | The fraud detection result produced by Voice ID, processed against the current session state and streamed audio of the speaker. |
| voice\$1id\$1result\$1fraud\$1detection\$1result | string | The fraud detection result information for the call. |
| voice\$1id\$1result\$1fraud\$1detection\$1fraudster\$1Id | string | The fraud detection information for the call detecting a fraudster. |
| external\$1third\$1party\$1interaction\$1duration\$1ms | bigint | The interaction duration for external third party in milliseconds. |
| voice\$1id\$1result\$1authentication\$1minimum\$1speech\$1ms | bigint | The minimum authentication score required for a user to be authenticated. Values MIN 0 and MAX 100. |
| voice\$1id\$1result\$1authentication\$1score | bigint | The minimum authentication score required for a user to be authenticated. Values MIN 0 and MAX 100. |
| voice\$1id\$1result\$1authentication\$1score\$1threshold | bigint | The minimum authentication score required for a user to be authenticated. Values MIN 0 and MAX 100. |
| voice\$1id\$1result\$1fraud\$1detection\$1risk\$1score\$1known\$1fraudster | bigint | The detection of fraudsters in a watchlist score for Known Fraudster category. |
| voice\$1id\$1result\$1fraud\$1detection\$1risk\$1score\$1synthetic\$1speech | bigint | This score is presented as a combined risk score for Voice Spoofing. |
| voice\$1id\$1result\$1fraud\$1detection\$1risk\$1score\$1voice\$1spoofing | bigint | The fraud risk score based on Voice Spoofing, such as playback of audio from Text-to-Speech systems recorded audio. |
| voice\$1id\$1result\$1fraud\$1detection\$1score\$1threshold | bigint | The threshold for detection of fraudsters in a watchlist that was set in the flow for the contact. |
| agent\$1pause\$1duration\$1ms | bigint | Agent pause duration for a contact in seconds. |
| voice\$1id\$1result\$1speaker\$1enrolled | Boolean | Enrolled: The caller is enrolled in voice authentication. |
| voice\$1id\$1result\$1speaker\$1opted\$1out | Boolean | Opted out: The caller has opted out of voice authentication. |
| media\$1streams\$1items | array(struct(type:string)) | Information about the media stream used during the contact. Valid values: AUDIO, VIDEO, CHAT. |
| voice\$1id\$1result\$1fraud\$1detection\$1reasons\$1items | array(string) | Contains fraud types: Known Fraudster and Voice Spoofing. |
| tags\$1references\$1items | map(string,string) | Adds the specified tags to the specified resource. |
| contact\$1details | map(string,string) | The details for the contact between agent and caller. |
| contact\$1evaluations | map(string,struct(form\$1id:string,evaluation\$1arn:string,status:string, start\$1timestamp:timestamp,end\$1timestamp:timestamp,delete\$1timestamp:timestamp, export\$1location:string)) | The list with the fields and data in the evaluation form. |
| references | array(struct(name:string, type:string, value:string, status: string, arn: string)) | Contains links to other documents that are related to a contact. Type: URL \$1 ATTACHMENT \$1 NUMBER \$1 STRING \$1 DATE \$1 EMAIL\$1MESSAGE |
| additional\$1email\$1recipients | array(struct(to\$1list: array(struct(display\$1name: string, address: string)), cc\$1list: array(struct(display\$1name: string, address: string)), from\$1recipient: struct(display\$1name: string, address: string))) | Contains the entire list of email address and display names from the email contact. |
| agent\$1state\$1transitions | array(struct(state\$1start\$1timestamp:timestamp,state\$1end\$1timestamp:timestamp, state:string)) | Information about the state transitions of the agent. |
| recordings | array(struct(storage\$1type:string,location:string,media\$1stream\$1type:string, participant\$1type:string,fragment\$1start\$1number:string,fragment\$1stop\$1number:string, start\$1timestamp:timestamp,stop\$1timestamp:timestamp,status:string, deletion\$1reason:string)) | Information about a voice recording, chat transcript, or screen recording. |
| agent\$1device\$1platform\$1name | string | Name of the platform that the agent used for the call. |
| agent\$1device\$1platform\$1version | string | Version of the platform that the agent used for the call. |
| agent\$1device\$1operating\$1system | string | Operating system that the agent used for the call. |
| customer\$1device\$1platform\$1name | string | Name of the platform that the customer used for the call. |
| customer\$1device\$1platform\$1version | string | Version of the platform that the customer used for the call. |
| customer\$1device\$1operating\$1system | string | Operating system that the agent customer for the call. |
| disconnect\$1details\$1potential\$1disconnect\$1issue | string | Indicates the potential disconnection issues for a call. This field is not populated if the service does not detect potential issues. |
| last\$1resumed\$1timestamp | Timestamp | The date and time this contact was last resumed, in UTC time. |
| last\$1paused\$1timestamp | Timestamp | The date and time this contact was last paused, in UTC time. |
| customer\$1voice\$1activity\$1greeting\$1start\$1timestamp | Timestamp | The date and time that measures the beginning of the customer greeting from an outbound voice call, in UTC time. |
| customer\$1voice\$1activity\$1greeting\$1end\$1timestamp | Timestamp | The date and time that measures the end of the customer greeting from an outbound voice call, in UTC time. |
| total\$1pause\$1duration\$1ms | bigint | Total pause duration, including before and after the agent was connected. |
| total\$1pause\$1count | bigint | Total number of pauses including when the contact was not connected. |
| quality\$1metrics\$1agent\$1audio | struct | Information about the quality of the agent's media connection. This is a measure of how the agent sounded to the customer. |
| quality\$1metrics\$1customer\$1audio | struct | Information about the quality of the customer's media connection. This is a measure of how the customer sounded to the agent. |
| segment\$1attribute | map(string, string) | A set of system defined key-value pairs stored on individual contact segments using an attribute map. The attributes are standard Amazon Connect attributes and can be accessed in flows. Attribute keys can include only alphanumeric, -, and \$1 characters. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
| chat\$1contact\$1metrics\$1total\$1messages | bigint | The number of chat messages on the contact |
| chat\$1contact\$1metrics\$1conversation\$1close\$1time\$1ms | bigint | The time it took for a contact to end after the last customer message |
| chat\$1contact\$1metrics\$1conversation\$1turn\$1count | bigint | The number of conversation turns in a chat contact |
| chat\$1contact\$1metrics\$1agent\$1first\$1response\$1timestamp | Timestamp | The agent first response timestamp for a chat contact. |
| chat\$1contact\$1metrics\$1agent\$1first\$1response\$1time\$1ms | bigint | The time for an agent to respond after obtaining a chat contact. |
| chat\$1contact\$1metrics\$1total\$1bot\$1messages | bigint | The total number of bot and automated messages on a chat contact. |
| chat\$1contact\$1metrics\$1total\$1bot\$1message\$1length\$1in\$1chars | bigint | The total number of characters from bot and automated messages on a chat contact. |
| chat\$1contact\$1metrics\$1multi\$1party | Boolean | A flag indicating whether multiparty chat or supervisor barge were enabled on this contact |
| chat\$1agent\$1metrics\$1participant\$1id | string | The Agent's participant id. |
| chat\$1agent\$1metrics\$1participant\$1type | string | The Agent's participant type. |
| chat\$1agent\$1metrics\$1conversation\$1abandon | Boolean | A flag indicating whether the chat conversation was abandoned by an Agent. |
| chat\$1agent\$1metrics\$1messages\$1sent | bigint | The number of chat messages sent by Agent. |
| chat\$1agent\$1metrics\$1num\$1responses | bigint | The number of responses sent by Agent to Customer. |
| chat\$1agent\$1metrics\$1message\$1length\$1in\$1chars | bigint | The number of chat characters sent by Agent. |
| chat\$1agent\$1metrics\$1total\$1response\$1time\$1ms | bigint | The total chat response time by Agent. |
| chat\$1agent\$1metrics\$1max\$1response\$1time\$1ms | bigint | The maximum chat response time by Agent. |
| chat\$1agent\$1metrics\$1last\$1message\$1timestamp | Timestamp | The Timestamp of last chat message by Agent. |
| chat\$1customer\$1metrics\$1participant\$1id | string | The Customer's participant id. |
| chat\$1customer\$1metrics\$1participant\$1type | string | The Customer's participant type. |
| chat\$1customer\$1metrics\$1conversation\$1abandon | Boolean | A flag indicating whether the chat conversation was abandoned by an Customer |
| chat\$1customer\$1metrics\$1messages\$1sent | bigint | The Number of chat messages sent by Customer. |
| chat\$1customer\$1metrics\$1num\$1responses | bigint | The number of responses sent by Customer. |
| chat\$1customer\$1metrics\$1message\$1length\$1in\$1chars | bigint | The number of chat characters sent by Customer. |
| chat\$1customer\$1metrics\$1total\$1response\$1time\$1ms | bigint | The total chat response time by Customer. |
| chat\$1customer\$1metrics\$1max\$1response\$1time\$1ms | bigint | The maximum chat response time by Customer. |
| chat\$1customer\$1metrics\$1last\$1message\$1timestamp | Timestamp | The Timestamp of last chat message by Customer. |
| q\$1in\$1connect\$1session\$1arn | string | The ARN of Connect AI agents session. |
| ai\$1agents | array(struct) | The AI Agent information of the contact. Each object includes ai\$1use\$1case (string), ai\$1agent\$1version\$1id (string), and ai\$1agent\$1escalated (boolean). |
| customer\$1phone\$1number\$1prefix | string | The prefix of the customer phone number. |
## Contact statistic record
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| aws\$1account\$1id | string | The ID of the AWS account that owns the contact. |
| contact\$1id | string | The ID of the contact. |
| channel | string | The method used to contact your contact center: VOICE, CHAT, TASK, EMAIL. |
| queue\$1id | string | ID of the queue. |
| agent\$1id | string | ID of the agent. |
| initiation\$1method | string | Values for INITIATION\$1METHOD: INBOUND \$1 OUTBOUND \$1 TRANSFER \$1 QUEUE\$1TRANSFER \$1 CALLBACK \$1 API |
| disconnect\$1timestamp | Timestamp | The contact disconnect Timestamp. |
| enqueue\$1timestamp | Timestamp | The Timestamp of contacts transferred into the queue from another queue during a Customer queue flow. |
| contact\$1flow\$1time\$1ms | bigint | The time a contact spent in a contact flow. |
| abandon\$1time\$1ms | bigint | The time that contacts waited in the queue before being abandoned. |
| queue\$1time\$1ms | bigint | The time that a contact spent waiting in the queue. |
| queue\$1answer\$1time\$1ms | bigint | The time that contacts waited in the queue before being answered by an agent. |
| handle\$1time\$1ms | bigint | The time that an agent spent on contacts. Agent interaction \$1 Customer Hold \$1 ACW |
| customer\$1hold\$1time\$1ms | bigint | The time that customers spent on hold after being connected to an agent. |
| agent\$1interaction\$1time\$1ms | bigint | The time that customer spent to interact with agent. |
| agent\$1interaction\$1outbound\$1time\$1ms | bigint | The time that agents spent interacting with a customer during an outbound contact. |
| agent\$1interaction\$1and\$1hold\$1time\$1ms | bigint | The time that customer spent to interact with agent and put on hold. |
| after\$1contact\$1work\$1time\$1ms | bigint | The time that an agent spent doing ACW for a contact. |
| after\$1contact\$1work\$1outbound\$1time\$1ms | bigint | The time that agents spent doing After Contact Work (ACW) for an outbound contact. |
| is\$1connected | bigint | A flag indicating whether `connected_to_system_timestamp` is not null. `is_connected` is populated when `connected_to_system_timestamp` is not null and is set to the date and time the customer endpoint connected to Amazon Connect, in UTC time. For INBOUND, this matches InitiationTimestamp. For OUTBOUND, CALLBACK, and API, this is when the customer endpoint answers. Note that `is_handled` indicates whether the call was connected to an agent. |
| is\$1abandoned | bigint | A flag indicating whether a contact is abandoned. (Determined by not having been handled by an agent, not being transferred by a flow and not having a next contact.) |
| is\$1agent\$1hung\$1up\$1first | bigint | A flag indicating whether a contact is disconnected where the agent disconnected before the customer. |
| is\$1handled | bigint | A flag indicating whether the call was connected to an agent. |
| is\$1handled\$1incoming | bigint | A flag indicating whether a contact is an incoming contact that was handled by an agent, including inbound contacts and transferred contacts. |
| is\$1handled\$1outbound | bigint | A flag indicating whether a contact is an outbound contact that was handled by an agent. |
| is\$1callback\$1handled | bigint | A flag indicating whether a contact is a callback and handled by an agent. |
| is\$1api\$1handled | bigint | A flag indicating whether a contact is initiated using an Amazon Connect API operation and handled by an agent. |
| is\$1put\$1on\$1hold | bigint | A flag indicating whether a contact is put on hold. |
| is\$1hold\$1disconnect | bigint | A flag indicating whether a contact is disconnected while the customer was on hold. |
| is\$1hold\$1agent\$1disconnect | bigint | A flag indicating whether a contact is disconnected by the agent while the customer was on hold. |
| is\$1hold\$1customer\$1disconnect | bigint | A flag indicating whether a contact is disconnected by the customer while the customer was on hold. |
| is\$1incoming | bigint | A flag indicating whether a contact is an incoming contacts, including inbound contacts and transferred contacts. |
| is\$1callback\$1contact | bigint | A flag indicate whether a contact is callback. |
| is\$1api\$1contact | bigint | A flag indicate whether a contact is initiated using an Amazon Connect API operation. |
| is\$1queued | bigint | A flag indicate whether a contact is put in queue. |
| is\$1queued\$1and\$1handled | bigint | A flag indicate whether a contact is put in queue and handled by agent. |
| is\$1transferred\$1in | bigint | A flag indicate whether a contact is transferred in. |
| is\$1transferred\$1in\$1from\$1handled | bigint | A flag indicate whether a contact is transferred in from a contact handled by agent. |
| is\$1transferred\$1in\$1from\$1queued | bigint | A flag indicate whether a contact is transferred to the queue from another in a Transfer to queue contact flow. |
| is\$1transferred\$1out | bigint | A flag indicate whether a contact is transferred out. |
| is\$1transferred\$1out\$1from\$1handled | bigint | A flag indicate whether a contact is transferred in from a contact handled by agent. |
| is\$1transferred\$1out\$1from\$1queued | bigint | A flag indicate whether a contact is transferred from the queue to another queue in a Transfer to queue contact flow. |
| is\$1transferred\$1out\$1internal | bigint | A flag indicate whether a contact is transferred to an internal source. |
| is\$1transferred\$1out\$1external | bigint | A flag indicate whether a contact is transferred from the queue to an external source. |
| is\$1transferred\$1out\$1external\$1from\$1contact\$1flow | bigint | A flag indicate whether a contact is transferred to an external destination by contact flow. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Agent event
Table name: `agent_event`
Composite primary key: \$1instance\$1id, event\$1id\$1
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| aws\$1account\$1id | string | The ID of the AWS account that owns the agent event. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| agent\$1arn | string | The ARN of the agent. |
| event\$1id | string | The unique event identifier. |
| event\$1timestamp | Timestamp | The timestamp when the event occurred. |
| event\$1type | string | The type of agent event. |
| version | string | The version of the agent event stream in date format. |
| current\$1agent\$1status\$1arn | string | The ARN of the current agent status. |
| current\$1agent\$1status\$1name | string | The name of the current agent status. |
| current\$1agent\$1status\$1start\$1timestamp | Timestamp | The timestamp when the current agent status started. |
| current\$1agent\$1status\$1type | string | The type classification of the current agent status. |
| current\$1next\$1agent\$1status\$1arn | string | The ARN of the next agent status. |
| current\$1next\$1agent\$1status\$1name | string | The name of the next agent status. |
| current\$1next\$1agent\$1status\$1enqueued\$1timestamp | Timestamp | The timestamp when the next agent status was enqueued. |
| current\$1next\$1agent\$1status\$1type | string | The type classification of the next agent status. |
| current\$1configuration\$1username | string | The username of the current agent. |
| current\$1configuration\$1first\$1name | string | The first name of the current agent. |
| current\$1configuration\$1last\$1name | string | The last name of the current agent. |
| current\$1configuration\$1sip\$1address | string | The SIP address for the agent's communication endpoint. |
| current\$1configuration\$1auto\$1accept | boolean | Whether the agent auto-accepts incoming contacts. |
| current\$1configuration\$1language\$1code | string | The language code for the agent's interface. |
| current\$1configuration\$1proficiencies | array(struct) | The agent proficiency attributes (name, value, level). |
| current\$1routing\$1profile\$1arn | string | The ARN of the current routing profile. |
| current\$1routing\$1profile\$1name | string | The name of the current routing profile. |
| current\$1routing\$1profile\$1concurrency | array(struct) | The current routing profile concurrency settings. |
| current\$1routing\$1profile\$1default\$1outbound\$1queue\$1arn | string | The ARN of the default outbound queue. |
| current\$1routing\$1profile\$1default\$1outbound\$1queue\$1name | string | The name of the default outbound queue. |
| current\$1routing\$1profile\$1default\$1outbound\$1queue\$1channels | array(string) | The channels of the default outbound queue. |
| current\$1routing\$1profile\$1inbound\$1queues | array(struct) | The inbound queues for the routing profile. |
| current\$1agent\$1hierarchy\$1groups\$1level\$11\$1arn | string | The ARN of the agent hierarchy group level 1. |
| current\$1agent\$1hierarchy\$1groups\$1level\$11\$1name | string | The name of the agent hierarchy group level 1. |
| current\$1agent\$1hierarchy\$1groups\$1level\$12\$1arn | string | The ARN of the agent hierarchy group level 2. |
| current\$1agent\$1hierarchy\$1groups\$1level\$12\$1name | string | The name of the agent hierarchy group level 2. |
| current\$1agent\$1hierarchy\$1groups\$1level\$13\$1arn | string | The ARN of the agent hierarchy group level 3. |
| current\$1agent\$1hierarchy\$1groups\$1level\$13\$1name | string | The name of the agent hierarchy group level 3. |
| current\$1agent\$1hierarchy\$1groups\$1level\$14\$1arn | string | The ARN of the agent hierarchy group level 4. |
| current\$1agent\$1hierarchy\$1groups\$1level\$14\$1name | string | The name of the agent hierarchy group level 4. |
| current\$1agent\$1hierarchy\$1groups\$1level\$15\$1arn | string | The ARN of the agent hierarchy group level 5. |
| current\$1agent\$1hierarchy\$1groups\$1level\$15\$1name | string | The name of the agent hierarchy group level 5. |
| current\$1contacts | array(struct) | The current contacts associated with the agent. |
| previous\$1agent\$1status\$1arn | string | The ARN of the previous agent status. |
| previous\$1agent\$1status\$1name | string | The name of the previous agent status. |
| previous\$1agent\$1status\$1start\$1timestamp | Timestamp | The timestamp when the previous agent status started. |
| previous\$1agent\$1status\$1type | string | The type classification of the previous agent status. |
| previous\$1configuration\$1proficiencies | array(struct) | The previous agent proficiency attributes. |
| previous\$1routing\$1profile\$1arn | string | The ARN of the previous routing profile. |
| previous\$1routing\$1profile\$1name | string | The name of the previous routing profile. |
| previous\$1routing\$1profile\$1concurrency | array(struct) | The previous routing profile concurrency settings. |
| previous\$1routing\$1profile\$1default\$1outbound\$1queue\$1arn | string | The ARN of the previous default outbound queue. |
| previous\$1routing\$1profile\$1default\$1outbound\$1queue\$1name | string | The name of the previous default outbound queue. |
| previous\$1routing\$1profile\$1default\$1outbound\$1queue\$1channels | array(string) | The channels of the previous default outbound queue. |
| previous\$1routing\$1profile\$1inbound\$1queues | array(struct) | The previous inbound queues for the routing profile. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$11\$1arn | string | The ARN of the previous agent hierarchy group level 1. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$11\$1name | string | The name of the previous agent hierarchy group level 1. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$12\$1arn | string | The ARN of the previous agent hierarchy group level 2. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$12\$1name | string | The name of the previous agent hierarchy group level 2. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$13\$1arn | string | The ARN of the previous agent hierarchy group level 3. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$13\$1name | string | The name of the previous agent hierarchy group level 3. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$14\$1arn | string | The ARN of the previous agent hierarchy group level 4. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$14\$1name | string | The name of the previous agent hierarchy group level 4. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$15\$1arn | string | The ARN of the previous agent hierarchy group level 5. |
| previous\$1agent\$1hierarchy\$1groups\$1level\$15\$1name | string | The name of the previous agent hierarchy group level 5. |
| previous\$1contacts | array(struct) | The previous contacts associated with the agent. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
# Bot analytics data in the Amazon Connect analytics data lake
This topic details the content in the Amazon Connect data lake bot tables. The tables list the column, type, and description of the content.
There are two ways to access the analytics data lake and configure data to be shared:
+ [Option 1: Use the Amazon Connect console](access-datalake.md#option1-configure-data-to-be-shared)
+ [Option 2: Use CLI or CloudShell](access-datalake.md#option2-configure-data-to-be-shared)
If you are unable to access the scheduling tables by using Option 1, try using Option 2.
**Topics**
+ [Bot conversations](#data-lake-bot-conversations)
+ [Bot intents](#data-lake-bot-intents)
+ [Bot slots](#data-lake-bot-slots)
## Bot conversations
Table name: bot\$1conversations
Composite primary key: \$1instance\$1id, event\$1id\$1
| Column | Type | Description |
| --- | --- | --- |
| event\$1id | String | This is the primary key of the table. This will be a hash of originating request identifier and session identifier. |
| bot\$1originating\$1request\$1id | String | A unique identifier for a specific bot request. |
| bot\$1session\$1id | String | The identifier of the user session that is having the conversation. |
| aws\$1account\$1id | String | The identifier of the AWS account that owns the contact. |
| instance\$1arn | String | The ARN of the Amazon Connect instance. |
| instance\$1id | String | The identifier of the Amazon Connect instance. |
| invoking\$1resource\$1type | String | Can be flow or module. |
| flow\$1resource\$1id | String | Flow identifier. |
| module\$1resource\$1id | String | Module identifier. |
| invoking\$1resource\$1start\$1timestamp | Timestamp | Time at which flow started. |
| parent\$1flow\$1resource\$1id | String | Flow Id from which the module was invoked from. This field will only be populated for modules. |
| contact\$1id | String | The identifier of the contact. |
| flow\$1action\$1id | String | Identifier for action that was executed. An Action is a single step of a flow's run. |
| invoking\$1resource\$1published\$1timestamp | Timestamp | "Creation" or "revision" date of the flow itself. |
| flow\$1type | String | Amazon Connect includes a set of nine flow types. For more information, see [Choose a flow type](create-contact-flow.md#contact-flow-types). |
| channel | String | The method used to contact your contact center: VOICE, CHAT, TASK. |
| sub\$1type | String | This subtype for the contact. For example, connect:Guide or connect:SMS. |
| initiation\$1method | String | Indicates how the contact was initiated. |
| flow\$1language\$1version | String | Flow language version. |
| invoking\$1resource\$1version | String | Version of the contact flow used. |
| bot\$1id | String | The identifier of the bot. |
| bot\$1alias\$1id | String | The alias identifier of the bot that the session was held with. |
| bot\$1version | String | The version of the bot that the session was held with. |
| bot\$1locale | String | Language configuration of the bot. |
| bot\$1conversation\$1start\$1timestamp | Timestamp | The Timestamp marking the start of the conversation with the bot. |
| bot\$1conversation\$1end\$1timestamp | Timestamp | The Timestamp marking the end of the conversation with the bot. |
| bot\$1conversation\$1outcome | String | The final state of the conversation. Values: Success \$1 Failure \$1 Dropped |
| bot\$1number\$1of\$1conversation\$1turns | Number | The number of turns that the session took. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Bot intents
Table name: bot\$1intents
Composite primary key: \$1instance\$1id, event\$1id\$1
| Column | Type | Description |
| --- | --- | --- |
| event\$1id | String | This is the primary key of the table. This will be a hash of originating request identifier, session identifier, intent name, and intent level. |
| bot\$1originating\$1request\$1id | String | A unique identifier for a specific bot request. |
| bot\$1session\$1id | String | The identifier of the user session that is having the conversation. |
| account\$1id | String | The identifier of the AWS account that owns the contact. |
| instance\$1arn | String | The ARN of the Amazon Connect instance. |
| instance\$1id | String | The identifier of the Amazon Connect instance. |
| invoking\$1resource\$1type | String | Can be flow or module. |
| flow\$1resource\$1id | String | The flow identifier. |
| module\$1resource\$1id | String | The module identifier. |
| invoking\$1resource\$1start\$1timestamp | Timestamp | The time when the flow started. |
| parent\$1flow\$1resource\$1id | String | The flow identifier where the module was invoked. This field is only be populated for modules. |
| contact\$1id | String | The identifier of the contact. |
| action\$1id | String | The identifier for action that was executed. An Action is a single step of a flow's run. |
| invoking\$1resource\$1published\$1timestamp | Timestamp | The "creation" or "revision" date of the flow itself. |
| flow\$1type | String | The type of flow. Amazon Connect includes a set of nine flow types. For more information, see [Choose a flow type](create-contact-flow.md#contact-flow-types). |
| channel | String | The method used to contact your contact center: VOICE, CHAT, TASK. |
| sub\$1type | String | The subtype for the contact. For example, connect:Guide or connect:SMS. |
| initiation\$1method | String | How the contact was initiated. |
| flow\$1language\$1version | String | The flow language version. |
| invoking\$1resource\$1version | String | Version of the contact flow used. |
| bot\$1id | String | The identifier of the bot. |
| bot\$1alias\$1id | String | The alias identifier of the bot that the session was held. with |
| bot\$1version | String | The version of the bot that the session was held with. |
| bot\$1locale | String | The language configuration of the bot. |
| bot\$1conversation\$1start\$1timestamp | Timestamp | The Timestamp marking the start of the conversation with the bot. |
| bot\$1conversation\$1end\$1timestamp | Timestamp | The Timestamp marking the end of the conversation with the bot. |
| bot\$1intent\$1name | String | The name of the intent. |
| bot\$1intent\$1level | Number | The number of intents up to and including the requested path. |
| bot\$1intent\$1outcome | String | The end state of the intent. Value of Success, Failed, Switched, or Dropped. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Bot slots
Table name: bot\$1slots
Composite primary key: \$1instance\$1id, event\$1id\$1
| Column | Type | Description |
| --- | --- | --- |
| event\$1id | String | This is the primary key of the table. This will be a hash of originating request identifier, session identifier, intent name, intent level, slot name, and slot level. |
| bot\$1originating\$1request\$1id | String | A unique identifier for a specific bot request |
| bot\$1session\$1id | String | The identifier of the user session that is having the conversation. |
| account\$1id | String | The identifier of the AWS account that owns the contact. |
| instance\$1arn | String | The ARN of the Amazon Connect instance. |
| instance\$1id | String | The identifier of the Amazon Connect instance. |
| invoking\$1resource\$1type | String | Can be flow or module. |
| flow\$1resource\$1id | String | Flow identifier. |
| module\$1resource\$1id | String | Module identifier. |
| invoking\$1resource\$1start\$1timestamp | Timestamp | Time at which flow started. |
| parent\$1flow\$1resource\$1id | String | Flow identifier from which the module was invoked from. This field will only be populated for modules. |
| contact\$1id | String | The identifier of the contact. |
| action\$1id | String | Identifier for action that was executed. An Action is a single step of a flow's run. |
| invoking\$1resource\$1published\$1timestamp | Timestamp | "Creation" or "revision" date of the flow itself. |
| flow\$1type | String | Amazon Connect includes a set of nine flow types. For more information, see [Choose a flow type](create-contact-flow.md#contact-flow-types). |
| channel | String | The method used to contact your contact center: VOICE, CHAT, TASK. |
| sub\$1type | String | This subtype for the contact. For example, connect:Guide or connect:SMS. |
| initiation\$1method | String | Indicates how the contact was initiated. |
| flow\$1language\$1version | String | Flow language version. |
| invoking\$1resource\$1version | String | Version of the contact flow used. |
| bot\$1id | String | The identifier of the bot. |
| bot\$1alias\$1id | String | The alias identifier of the bot that the session was held with. |
| bot\$1version | String | The version of the bot that the session was held with. |
| bot\$1locale | String | Language configuration of the bot. |
| bot\$1conversation\$1start\$1timestamp | Timestamp | The Timestamp marking the start of the conversation with the bot. |
| bot\$1conversation\$1end\$1timestamp | Timestamp | The Timestamp marking the end of the conversation with the bot. |
| bot\$1intent\$1name | String | The name of the intent. |
| bot\$1intent\$1level | Number | The number of intents up to and including the requested path. |
| bot\$1slot\$1name | String | The name of the slot. |
| bot\$1slot\$1level | Number | The number of slots up to and including the requested path. |
| bot\$1slot\$1outcome | String | The end state of the slot. Values of Success, Failed, Dropped, or Retry. |
| bot\$1slot\$1retry\$1count | Number | The number of times the bot tried to elicit a response from the user for the slot. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
# Cases data in the Amazon Connect analytics data lake
This topic details the content in the Amazon Connect data lake cases tables. The tables list the column, type, and description of the content.
There are two ways to access the analytics data lake and configure data to be shared:
+ [Option 1: Use the Amazon Connect console](access-datalake.md#option1-configure-data-to-be-shared)
+ [Option 2: Use CLI or CloudShell](access-datalake.md#option2-configure-data-to-be-shared)
If you are unable to access the scheduling tables by using Option 1, try using Option 2.
**Topics**
+ [Case events](#data-lake-case-events)
+ [Case related item events](#data-lake-case-related-item-events)
## Case events
Table name: `case_events`
Composite primary key: \$1instance\$1id, event\$1id\$1
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| aws\$1account\$1id | string | The ID of the AWS account that owns the case. |
| event\$1id | string | The unique ID of the case event. |
| case\$1id | string | The ID of the case. |
| event\$1timestamp | Timestamp | The timestamp when the event occurred, in UTC. |
| changed\$1field\$1ids | array(string) | The list of field IDs that were modified in this event. |
| event\$1type | string | The type of event. Valid values: CASE.CREATED, CASE.UPDATED, CASE.DELETED. |
| performed\$1by\$1iam\$1principal | string | The IAM principal ARN of the entity that triggered the event. |
| performed\$1by\$1user\$1arn | string | The ARN of the user that performed the action. |
| performed\$1by\$1custom\$1entity | string | The custom entity that performed the action. |
| cases\$1domain\$1arn | string | The ARN of the Amazon Connect Cases domain. |
| template\$1id | string | The ID of the case template used to create the case. |
| template\$1name | string | The name of the case template. This value is empty if the template has been deleted. |
| last\$1updated\$1user | string | The last user who updated the case. This references the last\$1updated\$1user system field value. |
| reference\$1number | string | The human-readable reference number for the case. This references the reference\$1number system field value. |
| status | string | The status of the case. This references the status system field value. |
| assigned\$1user | string | The ARN of the user assigned to the case. This references the assigned\$1user system field value. |
| assigned\$1queue | string | The ARN of the queue assigned to the case. This references the assigned\$1queue system field value. |
| case\$1reason | string | The reason for opening the case. This references the case\$1reason system field value. |
| case\$1title | string | The title of the case. This references the title system field value. |
| case\$1summary | string | The summary of the case. This references the summary system field value. |
| customer\$1profile\$1arn | string | The ARN of the customer profile associated with the case. This references the customer\$1id system field value. |
| created\$1timestamp | Timestamp | The timestamp when the case was created, in UTC. This references the created\$1datetime system field value. |
| last\$1updated\$1timestamp | Timestamp | The timestamp when the case was last updated, in UTC. This references the last\$1updated\$1datetime system field value. |
| next\$1sla\$1breach\$1timestamp | Timestamp | The timestamp of the next SLA breach deadline, in UTC. This references the next\$1sla\$1breach\$1datetime system field value. |
| last\$1closed\$1timestamp | Timestamp | The timestamp when the case was last closed, in UTC. This references the last\$1closed\$1datetime system field value. |
| last\$1reopened\$1timestamp | Timestamp | The timestamp when the case was last reopened, in UTC. This references the last\$1reopened\$1datetime system field value. |
| custom\$1fields | array(struct) | An array of objects containing custom field data associated with the case. Each object includes the field id, field\$1name, and a type-specific value field (string\$1value, double\$1value, boolean\$1value, or timestamp\$1value). |
### Sample queries
The following example query in Athena creates a view that flattens custom fields from the case events table:
```
CREATE VIEW case_events_flattened AS
SELECT
ce.event_id,
ce.case_id,
ce.event_timestamp,
ce.event_type,
ce.instance_id,
-- ... other system fields
-- Flattened custom fields
cf.id AS custom_field_id,
cf.field_name AS custom_field_name,
CASE
WHEN cf.string_value IS NOT NULL THEN 'string'
WHEN cf.double_value IS NOT NULL THEN 'double'
WHEN cf.timestamp_value IS NOT NULL THEN 'timestamp'
WHEN cf.boolean_value IS NOT NULL THEN 'boolean'
ELSE 'unknown'
END AS custom_field_type,
cf.string_value AS custom_field_string_value,
cf.double_value AS custom_field_double_value,
-- Cast milliseconds to timestamp
CAST(from_unixtime(cf.timestamp_value / 1000.0) AS TIMESTAMP) AS custom_field_timestamp_value,
cf.boolean_value AS custom_field_boolean_value
FROM "case_events resource link table name" ce
CROSS JOIN UNNEST(ce.custom_fields) AS t(cf)
```
The following example creates a view that returns the latest state for each case:
```
CREATE OR REPLACE VIEW latest_case_state AS
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY case_id, instance_id
ORDER BY event_timestamp DESC
) AS rn
FROM "case_events resource link table name"
) t
WHERE rn = 1;
```
## Case related item events
Table name: `case_related_item_events`
Composite primary key: \$1instance\$1id, event\$1id\$1
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| aws\$1account\$1id | string | The ID of the AWS account that owns the related item. |
| event\$1id | string | The unique ID of the related item event. |
| related\$1item\$1id | string | The ID of the related item. |
| event\$1timestamp | Timestamp | The timestamp when the event occurred, in UTC. |
| event\$1type | string | The type of event. Valid values: CASE.RELATED\$1ITEM.CREATED, CASE.RELATED\$1ITEM.UPDATED, CASE.RELATED\$1ITEM.DELETED. |
| performed\$1by\$1iam\$1principal | string | The IAM principal ARN of the entity that triggered the event. |
| performed\$1by\$1user\$1arn | string | The ARN of the user who performed the action. |
| performed\$1by\$1custom\$1entity | string | The custom entity identifier that performed the action. |
| cases\$1domain\$1arn | string | The ARN of the Amazon Connect Cases domain. |
| associated\$1case\$1id | string | The ID of the case that this related item is associated with. |
| related\$1item\$1type | string | The type of related item. Valid values: comment, file, sla, connect, contact, custom. |
| created\$1timestamp | Timestamp | The timestamp when the related item was created, in UTC. |
| comment\$1body | string | The body text of the comment. |
| comment\$1content\$1type | string | The content type of the comment body. For example, Text/Plain. |
| related\$1case\$1id | string | The ID of the related case. |
| contact\$1channel | string | The communication channel of the contact. For example, VOICE, CHAT, TASK, EMAIL. |
| contact\$1id | string | The ID of the contact. |
| file\$1arn | string | The ARN of the file attachment. |
| sla\$1name | string | The name of the SLA. |
| sla\$1status | string | The current status of the SLA. For example, active, overdue, met, notmet. |
| sla\$1target\$1timestamp | Timestamp | The timestamp of the target deadline for the SLA, in UTC. |
| sla\$1type | string | The type of SLA metric being tracked. For example, CaseField. |
| sla\$1completion\$1timestamp | Timestamp | The timestamp when the SLA was completed, in UTC. |
| sla\$1target\$1field\$1id | string | The ID of the field that the SLA is targeting. |
| sla\$1target\$1field\$1values | array(string) | The target field values for the sla\$1target\$1field\$1id in order for the SLA to be completed. |
| custom\$1related\$1item\$1fields | array(struct) | An array of objects containing custom field data associated with the related item. Each object includes the field id, field\$1name, and a type-specific value field (string\$1value, double\$1value, boolean\$1value, or timestamp\$1value). |
# Configuration data in the Amazon Connect data lake
This topic details the content in the Amazon Connect data lake configuration tables. The tables list the column, type, and description of the content.
There are two ways to access the analytics data lake and configure data to be shared:
+ [Option 1: Use the Amazon Connect console](access-datalake.md#option1-configure-data-to-be-shared)
+ [Option 2: Use CLI or CloudShell](access-datalake.md#option2-configure-data-to-be-shared)
If you are unable to access the scheduling tables by using Option 1, try using Option 2.
**Topics**
+ [Agent Hierarchy Groups](#agent-hierarchy-groups)
+ [Routing profiles](#data-lake-routing-profiles)
+ [Users](#data-lake-users)
## Agent Hierarchy Groups
Table Name: agent\$1hierarchy\$1groups
Composite Primary Key: agent\$1hierarchy\$1group\$1id
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| aws\$1account\$1id | string | The ID of the AWS account that owns the contact. |
| agent\$1hierarchy\$1group\$1id | string | The identifier of the hierarchy group for the user. |
| agent\$1hierarchy\$1group\$1arn | string | The ARN of the hierarchy group. |
| agent\$1hierarchy\$1group\$1name | string | The name of the hierarchy group. |
| last\$1modified\$1region | string | The AWS Region where this resource was last modified. |
| last\$1modified\$1timestamp | timestamp | The Timestamp when this resource was last modified. |
| is\$1active | Boolean | Whether the agent hierarchy group exists or has been deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Routing profiles
Table Name: routing\$1profiles
Composite Primary Key: agent\$1routing\$1profile\$1id
| Column | Type | Description |
| --- | --- | --- |
| agent\$1routing\$1profile\$1id | string | The identifier of the routing profile. |
| agent\$1routing\$1profile\$1arn | string | The ARN of the routing profile. |
| routing\$1profile\$1name | string | The name of the routing profile. |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| agent\$1availability\$1timer | string | Whether agents with this routing profile will have their routing order calculated based on longest idle time or time since their last inbound contact. |
| default\$1outbound\$1queue\$1id | string | The default outbound queue for the routing profile. |
| routing\$1profile\$1description | string | Description of the routing profile. |
| last\$1modified\$1region | string | The AWS Region where this resource was last modified. |
| last\$1modified\$1timestamp | Timestamp | The Timestamp when this resource was last modified. |
| is\$1active | Boolean | Whether the agent exists or has been deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Users
Table Name: users
Composite Primary Key: user\$1id
| Column | Type | Description |
| --- | --- | --- |
| user\$1id | string | The identifier of the user account. |
| user\$1arn | string | The ARN of the user account. |
| directory\$1user\$1id | string | The identifier of the user account in the directory used for identity management. |
| agent\$1hierarchy\$1group\$1id | string | The identifier of the hierarchy group for the user. |
| agent\$1hierarchy\$1group\$1arn | string | The identifier of level 1 hierarchy group for the user. |
| agent\$1hierarchy\$1group\$1level\$11\$1id | string | The identifier of level 1 hierarchy group for the user. |
| agent\$1hierarchy\$1group\$1level\$12\$1id | string | The identifier of level 2 hierarchy group for the user. |
| agent\$1hierarchy\$1group\$1level\$13\$1id | string | The identifier of level 3 hierarchy group for the user. |
| agent\$1hierarchy\$1group\$1level\$14\$1id | string | The identifier of level 4 hierarchy group for the user. |
| agent\$1hierarchy\$1group\$1level\$15\$1id | string | The identifier of level 5 hierarchy group for the user. |
| agent\$1email | string | The user's email address. |
| agent\$1secondary\$1email | string | The user's secondary email address. |
| first\$1name | string | The first name of the agent. |
| last\$1name | string | The last name of the agent. |
| mobile | string | The user's mobile number. |
| agent\$1username | string | The user name of the agent, as entered in their Amazon Connect user account. |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| agent\$1routing\$1profile\$1id | string | The ID of the routing profile for the agent. |
| agent\$1routing\$1profile\$1arn | string | The ARN of the routing profile for the agent. |
| agent\$1security\$1profile\$1ids | array | The IDs of the security profiles for the user. |
| agent\$1security\$1profile\$1arns | array | The ARNs of the security profiles for the user. |
| last\$1modified\$1region | string | The AWS Region where this resource was last modified. |
| last\$1modified\$1timestamp | Timestamp | The Timestamp when this resource was last modified. |
| after\$1contact\$1work\$1time\$1limit | int | The After Call Work (ACW) timeout setting, in seconds. |
| auto\$1accept | Boolean | The Auto accept setting. |
| desk\$1phone\$1number | string | The phone number for the user's desk phone. |
| phone\$1type | string | The phone type. |
| is\$1active | Boolean | Whether the agent exists or has been deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
| agent\$1voice\$1enhancement\$1mode | string | The voice enhancement mode used by the agent. Valid values: VOICE\$1ISOLATION \$1 NOISE\$1SUPPRESSION \$1 NONE. A value of null indicates this mode has not yet been set for this user. |
# Forecasting data in the Amazon Connect analytics data lake
This topic details the content in the Amazon Connect data lake forecasting tables. Each table lists the column, type, and description of the content in the table.
There are two ways to access the analytics data lake and configure data to be shared:
+ [Option 1: Use the Amazon Connect console](access-datalake.md#option1-configure-data-to-be-shared)
+ [Option 2: Use CLI or CloudShell](access-datalake.md#option2-configure-data-to-be-shared)
If you are unable to access the scheduling tables by using Option 1, try using Option 2.
**Topics**
+ [Important things to know](#data-lake-forecasting-data-important)
+ [Forecast groups table](#data-lake-forecast-groups)
+ [Long-term forecasts table](#data-lake-longterm-forecasts)
+ [Short-term forecasts table](#data-lake-shortterm-forecasts)
+ [Intraday forecasts table](#data-lake-intraday-forecasts)
+ [Demand group table](#data-lake-demand-groups)
+ [Demand group definitions table](#data-lake-demand-group-definition)
## Important things to know
+ You can use the tables described in this topic to access published forecasts data in the data lake.
+ The Forecast groups table stores versioned records. A new version is created when forecast group details are changed, for example, adding or removing queues from the forecast group. You can get the latest record using the highest value of forecast\$1group\$1version.
+ You can join the Forecast groups table to the Long-term and Short-term forecasts tables by using the following columns: forecast\$1group\$1arn and forecast\$1group\$1version.
## Forecast groups table
Table name: forecast\$1groups
Composite primary key: \$1instance\$1id, forecast\$1group\$1arn, forecast\$1group\$1version\$1
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | String | The identifier of the Amazon Connect instance. |
| forecast\$1group\$1arn | String | The ARN of the forecast group. |
| forecast\$1group\$1version | Number | The version of the forecast group. A new version is created every time a change is made to a forecast group, for example, addition of new queues. |
| forecast\$1group\$1name | String | The name of the forecast group. |
| instance\$1arn | String | The ARN of the Amazon Connect instance. |
| is\$1deleted | Boolean | Whether the forecast group is deleted. |
| last\$1updated\$1timestamp | String | The epoch Timestamp in milliseconds when the last time the forecast group was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp for the last time the data lake processed the record. This can include transformation and backfill processes. This field cannot be used to determine reliably data freshness. |
## Long-term forecasts table
Table name: long\$1term\$1forecasts
Composite primary key: \$1instance\$1id, long\$1term\$1forcast\$1id\$1
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | String | The ID of the Amazon Connect instance |
| long\$1term\$1forcast\$1id | String | Unique Identifier of the forecast data row. Key is hash of multiple values: instanceId, forecastGroupId, forecastGroupVersion, forecastType, queueId, channel, forecastStarttime, creationTime. |
| forecast\$1group\$1arn | String | The ARN of the forecast group. |
| forecast\$1group\$1version | Number | The version of the forecast group. |
| interval | String | Time interval of the forecast data. For example, Daily for long term forecast data. |
| queue\$1id | String | The ID of the queue for the forecast. |
| channel | String | The channel of the forecast. For example, VOICE. |
| forecast\$1interval\$1start\$1time\$1ms | Timestamp | Epoch in milliseconds of the start time of the time interval for this data row. |
| creation\$1timestamp\$1ms | Timestamp | Epoch in milliseconds of when this forecast is first computed or published. |
| computed\$1timestamp\$1ms | Timestamp | Epoch in milliseconds of when this forecast is first computed. |
| published\$1timestamp\$1ms | Timestamp | Epoch in milliseconds of when this forecast is first published. |
| timezone | String | The timezone of the forecast, for example, UTC. |
| is\$1published | Boolean | Whether this forecast is published or not. |
| average\$1handle\$1time | Number | The average handle time metric value of the forecast data row. |
| contact\$1volume | Number | The contact volume metric value of the forecast data row. |
| average\$1handle\$1time\$1override | Number | The customer applied override value of the average handle time metric. |
| contact\$1volume\$1override | Number | The customer applied override value of the contact volume metric value. |
| instance\$1arn | String | The ARN of the Amazon Connect instance of the forecast. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp for the last time the data lake processed the record. This can include transformation and backfill processes. This field cannot be used to determine reliably data freshness. |
## Short-term forecasts table
Table name: short\$1term\$1forecasts
Composite primary key: \$1instance\$1id, short\$1term\$1forecast\$1id\$1
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | String | The ID of the Amazon Connect instance. |
| short\$1term\$1forecast\$1id | String | Unique Identifier of the forecast data row. Key is hash of multiple values: instanceId, forecastGroupId, forecastGroupVersion, forecastType, queueId, channel, forecastStarttime, creationTime. |
| forecast\$1group\$1arn | String | The ARN of the forecast group for the forecast data row. |
| forecast\$1group\$1version | Number | The version of the forecast group. |
| interval | String | Time interval of the forecast data row. For example, FIFTEEN\$1MINUTES for short term 15 minutes forecast data row. |
| queue\$1id | String | The ID of the queue for the forecast. |
| channel | String | The channel of this forecast, for example, VOICE. |
| forecast\$1interval\$1start\$1time\$1ms | Timestamp | Epoch in milliseconds of the start time of the time interval for this data row. |
| creation\$1timestamp\$1ms | Timestamp | Epoch in milliseconds of when this forecast is first computed or published. |
| computed\$1timestamp\$1ms | Timestamp | Epoch in milliseconds of when this forecast is first computed. |
| published\$1timestamp\$1ms | Timestamp | Epoch in milliseconds of when this forecast is first published. |
| is\$1published | Boolean | Whether this forecast is published or not. |
| average\$1handle\$1time | Number | The average handle time metric value of the forecast data row. |
| contact\$1volume | Number | The contact volume metric value of the forecast data row. |
| average\$1handle\$1time\$1override | Number | The customer applied override value of the average handle time metric. |
| contact\$1volume\$1override | Number | The customer applied override value of the contact volume metric value. |
| instance\$1arn | String | The ARN of the Amazon Connect instance of the forecast. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp for the last time the data lake processed the record. This can include transformation and backfill processes. This field cannot be used to determine reliably data freshness. |
## Intraday forecasts table
Table name: intraday\$1forecasts
Composite primary key: \$1instance\$1id, intraday\$1forecast\$1id\$1
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| intraday\$1forecast\$1id | string | Unique identifier of this intraday forecast data. |
| aws\$1account\$1id | string | The identifier of the AWS account that owns the Intraday Forecast. |
| instance\$1id | string | The identifier of the Amazon Connect instance. You can [find the instance ID](find-instance-arn.md) in the Amazon Resource Name (ARN) of the instance. |
| instance\$1arn | string | Instance ARN of the Amazon Connect instance. |
| channel | string | The method used to contact your contact center. |
| queue\$1arn | string | The Amazon Resource Name of the queue. |
| forecast\$1interval\$1start\$1time | Timestamp | Start Timestamp of the forecast interval. |
| creation\$1timestamp | Timestamp | When the forecast was computed in forecasting system. |
| average\$1handle\$1time | Double | Forecasted metric data: average handle time. |
| average\$1queue\$1answer\$1time | Double | Forecasted metric data: average queue answer time. |
| contact\$1volume | Double | Forecasted metric data: contact volume. |
| effective\$1agent\$1staffing | Double | Forecasted metric data: effective agent staffing. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Demand group table
Table name: `demand_group`
Composite Primary Key: `{instance_id, demand_group_arn, demand_group_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The identifier of the Amazon Connect instance. |
| demand\$1group\$1arn | string | The ARN of the demand group. |
| demand\$1group\$1version | Long | The version of the demand group. A new version is created every time a change is made to a demand group, for example, addition of new queues. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| demand\$1group\$1name | string | Name of the demand group. |
| foecast\$1group\$1arn | string | The ARN of the forecast group. |
| is\$1deleted | Boolean | Whether the demand group is deleted. |
| last\$1updated\$1timestamp | Timestamp | Timestamp when the demand group was last created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Demand group definitions table
Table name: `demand_group_definitions`
Composite Primary Key: `{instance_id, demand_group_definition_id}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The identifier of the Amazon Connect instance. |
| demand\$1group\$1definition\$1id | string | Unique Identifier for the demandGroup definition row. |
| demand\$1group\$1arn | string | The ARN of the demand group. |
| demand\$1group\$1version | Long | The version of the demand group. A new version is created every time a change is made to a demand group, for example, addition of new queues. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| queue\$1id | string | ID of the queue that is part of the demand group. |
| is\$1deleted | Boolean | Whether the demand group is deleted. |
| last\$1updated\$1timestamp | Timestamp | Timestamp when the demand group was last created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
# Outbound campaigns data in the Amazon Connect data lake
This topic details the content in the Amazon Connect data lake outbound campaign events table. The table lists the column, type, and description of the content.
There are two ways to access the analytics data lake and configure data to be shared:
+ [Option 1: Use the Amazon Connect console](access-datalake.md#option1-configure-data-to-be-shared)
+ [Option 2: Use CLI or CloudShell](access-datalake.md#option2-configure-data-to-be-shared)
If you are unable to access the scheduling tables by using Option 1, try using Option 2.
## Outbound campaign events
Table name: outbound\$1campaign\$1events
Composite primary key: `campaign_event_id`, `instanceId`
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| account\$1profile\$1id | String | Specifies the profile identifier for an account-based profile in Customer Profiles. |
| instance\$1id | String | The identifier of the Amazon Connect instance. |
| instance\$1arn | String | The ARN of the Amazon Connect instance. |
| aws\$1account\$1id | String | The identifier of the AWS account that owns the outbound campaign. |
| campaign\$1id | String | The identifier for the outbound campaign. |
| campaign\$1name | String | The name of the outbound campaign. |
| campaign\$1initiation\$1type | String | The campaign initiation method selected for the outbound campaign. For example: CUSTOMER\$1SEGMENT, CUSTOMER\$1EVENT. |
| campaign\$1execution\$1timestamp | Timestamp | The Timestamp indicating the start of each campaign execution. Multiple Timestamps can exist for campaigns that run repeatedly. |
| campaign\$1event\$1id | String | The unique identifier for each outbound campaign event. |
| campaign\$1event\$1type | String | The specific outbound campaign event type. *Campaign events:* [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/connect/latest/adminguide/data-lake-outbound-campaigns-data.html) *Email events:* [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/connect/latest/adminguide/data-lake-outbound-campaigns-data.html) *SMS events:* [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/connect/latest/adminguide/data-lake-outbound-campaigns-data.html) *Telephony events:* [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/connect/latest/adminguide/data-lake-outbound-campaigns-data.html) |
| campaign\$1event\$1timestamp | Timestamp | The Timestamp indicating when the outbound campaign event occurred. |
| delivery\$1attempt\$1id | String | The identifier for the outbound communication delivery attempt. |
| channel | String | The method used to contact your contact center. For example: VOICE, CHAT, EMAIL. This field may be blank for campaign events when this value is not applicable. |
| subtype | String | The outbound campaign delivery mode used to contact the campaign recipient. For example: connect:Email, connect:SMS, connect:Telephony. Note: Telephony includes both the Agent Assisted Voice, Automated Voice delivery modes. This field may be blank for campaign events when this value is not applicable. |
| profile\$1id | String | The unique identifier of an Amazon Connect Customer Profile. Note: This attribute is only available when you use segmentation capabilities offered with Amazon Connect Customer Profiles. |
| campaign\$1segment\$1arn | String | The ARN of a segment of users. Note: This attribute is only available when you use segmentation capabilities offered with Amazon Connect Customer Profiles. |
| campaign\$1url\$1link\$1click | String | The URL link clicked by the outbound campaign recipient. This attribute is applicable to email campaigns. |
| campaign\$1is\$1final\$1status | Boolean | Set to True if this is the final state of the message. There are intermediate message statuses and it can take up to 72 hours for the final state to be received. This attribute is applicable to SMS campaigns. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp |
| lambda\$1function\$1arn | String | The Lambda Function ARN invoked by Outbound Campaigns. |
| lambda\$1invocation\$1result | String | The result of the Lambda Function invocation attempt. Set to SUCCESS: Outbound Campaigns successfully invoked the Lambda function and received a response, including [function errors](https://docs.aws.amazon.com/lambda/latest/api/API_Invoke.html#API_Invoke_ResponseSyntax) and malformed responses. Set to ERROR: Outbound Campaigns failed to invoke the Lambda function or couldn't confirm successful invocation (e.g., timeout). |
| orchestration\$1event\$1context\$1subtype | String | Provides additional granularity for outbound campaign events indicating the specific reason a target was not reached. |
## Resource tags
Table name: `amazon_connect_resource_tags`
Composite Primary Key: `{instance_id, aws_account_id, resource_arn, record_creation_timestamp} `
| **Column** | **Type** | **Description** |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| aws\$1account\$1id | string | The ID of the AWS account that owns the resource. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| record\$1creation\$1timestamp | Timestamp | The timestamp when tags changed on the resource. |
| resource\$1arn | string | The ARN of the Amazon Connect resource. |
| tags | map(string,string) | The tags associated with the Amazon Connect resource. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
# Scheduling data in the Amazon Connect analytics data lake
This topic details the content in the Amazon Connect data lake scheduling tables. The tables list the column, type, and description of the content.
There are two ways to access the analytics data lake and configure data to be shared:
+ [Option 1: Use the Amazon Connect console](access-datalake.md#option1-configure-data-to-be-shared)
+ [Option 2: Use CLI or CloudShell](access-datalake.md#option2-configure-data-to-be-shared)
If you are unable to access the scheduling tables by using Option 1, try using Option 2.
**Topics**
+ [Staff scheduling profile](#data-lake-staff-scheduling-profile)
+ [Shift activities](#data-lake-shift-activities)
+ [Shift profiles](#data-lake-shift-profiles)
+ [Staffing groups](#data-lake-staffing-groups)
+ [Staffing groups - Forecast groups](#data-lake-staffing-groups-forecast-groups)
+ [Staffing groups - Supervisors](#data-lake-staffing-groups-supervisors)
+ [Staff shifts](#staff-shifts)
+ [Staff shift activities](#data-lake-staff-shift-activities)
+ [Staff timeoff balance changes](#data-lake-staff-timeoff-balance-changes)
+ [Staff timeoffs](#data-lake-staff-timeoffs)
+ [Staff timeoff intervals](#data-lake-staff-timeoff-intervals)
+ [Staff demand group](#data-lake-staff_demand_group)
+ [Staffing groups demand group](#data-lake-staffing-groups-demand-groups)
+ [Staff shift activity allocation](#data-lake-staff-shift-activity-allocation)
+ [Schedule metrics](#data-lake-schedule-metrics)
+ [Schedule goals](#data-lake-schedule-goals)
+ [Shift rotation patterns](#data-lake-shift-rotation-patterns)
+ [Shift rotation steps](#data-lake-shift-rotation-steps)
+ [Data schema](#data-lake-data-schema)
+ [Sample queries](#data-lake-sample-queries)
## Staff scheduling profile
Table Name: `staff_scheduling_profile`
Composite Primary Key: `{instance_id, agent_arn, staff_scheduling_profile_version} `
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| agent\$1arn | string | The ARN of the Agent. |
| staff\$1scheduling\$1profile\$1version | bigint | The Staff Scheduling Profile Version. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| staffing\$1group\$1arn | string | The ARN of the Staffing Group to which the Agent is assigned. |
| start\$1timestamp | Timestamp | StartTimestamp for the Agent configured in Staff Rules (schedules are generated only after this Timestamp). |
| end\$1timestamp | Timestamp | EndTimestamp for the Agent configured in Staff Rules (schedules are not generated beyond this Timestamp). |
| shift\$1profile\$1arn | string | The ARN of the Shift Profile assigned to the Agent in Staff Rules. Mutually exclusive with Shift Rotation Pattern. |
| shift\$1rotation\$1pattern\$1arn | string | The ARN of the Shift Rotation Pattern assigned to the Agent in Staff Rules. Mutually exclusive with Shift Profile. |
| shift\$1rotation\$1start\$1step\$1id | bigint | The step ID where the Agent begins in the assigned Shift Rotation Pattern. |
| timezone | string | Timezone configured for the Agent. |
| is\$1deleted | Boolean | Set to True if the Agent is deleted. Else set to False. |
| last\$1updated\$1timestamp | Timestamp | Timestamp when the Staff Scheduling Profile was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Shift activities
Table Name: `shift_activities`
Composite Primary Key: `{instance_id, shift_activity_arn, shift_activity_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| shift\$1activity\$1arn | string | The ARN of the Shift Activity. |
| shift\$1activity\$1version | bigint | The Shift Activity Version. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| shift\$1activity\$1name | string | Name of the Shift Activity. |
| type | string | Type of the Shift Activity. The possible values are: PRODUCTIVE, NON\$1PRODUCTIVE, and LEAVE. |
| sub\$1type | string | The sub-type of the Shift Activity. This is only valid for NON\$1PRODUCTIVE type activities. The possible values are: BREAK\$1OR\$1MEAL and NONE. |
| is\$1adherence\$1tracked | Boolean | Set to True if the Shift Activity is configured for Adherence tracking. Else set to False. |
| is\$1paid | Boolean | Set to True if the Shift Activity is configured as Paid. Else set to False. |
| is\$1deleted | Boolean | Set to True if the Shift Activity is deleted. Else set to False. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Shift Activity was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Shift profiles
Table Name: `shift_profiles`
Composite Primary Key: `{instance_id, shift_profile_arn, shift_profile_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| shift\$1profile\$1arn | string | The ARN of the Shift Profile. |
| shift\$1profile\$1version | bigint | The Shift Profile Version. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| shift\$1profile\$1name | string | The name of the Shift Profile. |
| is\$1deleted | Boolean | Set to True if the Shift Profile is deleted. Else set to False. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Shift Profile was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staffing groups
Table Name: `staffing_groups`
Composite Primary Key: `{instance_id, staffing_group_arn, staffing_group_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| staffing\$1group\$1arn | string | The ARN of the Staffing Group. |
| staffing\$1group\$1version | bigint | The Staffing Group Version. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| staffing\$1group\$1name | string | The name of the Staffing Group. |
| is\$1deleted | Boolean | Set to True if the Staffing Group is deleted. Else set to False. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Staffing Group was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staffing groups - Forecast groups
Table Name: `staffing_group_forecast_groups `
Composite Primary Key: `{instance_id, staffing_group_arn, staffing_group_version, forecast_group_arn}`
This table should be queried by joining with `staffing_groups` table on `staffing_group_arn` and `staffing_group_version`.
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| staffing\$1group\$1arn | string | The ARN of the Staffing Group. |
| staffing\$1group\$1version | bigint | The Staffing Group Version. |
| forecast\$1group\$1arn | string | The ARN of the Forecast Group associated to the Staffing Group. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| is\$1deleted | Boolean | Set to False when the StaffingGroup-ForecastGroup association is valid. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Staffing Group was created/updated. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staffing groups - Supervisors
Table Name: `staffing_group_supervisors`
Composite Primary Key: `{instance_id, staffing_group_arn, staffing_group_version, supervisor_arn} `
This table should be queried by joining with `staffing_groups` table on `staffing_group_arn` and `staffing_group_version`.
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| staffing\$1group\$1arn | string | The ARN of the Staffing Group. |
| staffing\$1group\$1version | bigint | The Staffing Group Version. |
| supervisor\$1arn | string | The Agent ARN of the Supervisor associated to the Staffing Group. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| is\$1deleted | Boolean | Set to False when the StaffingGroup-ForecastGroup association is valid. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Staffing Group was created/updated. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staff shifts
Table Name: `staff_shifts`
Composite Primary Key: `{instance_id, shift_id, shift_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| shift\$1id | string | The ID of the Shift. |
| shift\$1version | bigint | The Shift Version. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| agent\$1arn | string | The ARN of the Agent. |
| shift\$1start\$1timestamp | Timestamp | The Timestamp when the Shift Starts. |
| shift\$1end\$1timestamp | Timestamp | The Timestamp when the Shift Ends. |
| created\$1timestamp | Timestamp | The Timestamp when the Shift was Created. |
| is\$1deleted | Boolean | Set to True if the Shift is deleted. Else set to False. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Shift was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staff shift activities
Table Name: `staff_shift_activities`
Composite Primary Key: `{instance_id, shift_id, shift_version, activity_id}`
This table should be queried by joining with `staff_shifts` table on `shift_id` and `shift_version`.
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| shift\$1id | string | The ID of the Shift. |
| shift\$1version | bigint | The Shift Version. |
| activity\$1id | string | The ID of the Activity. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| activity\$1start\$1timestamp | Timestamp | The Timestamp when the activity starts. |
| activity\$1end\$1timestamp | Timestamp | The Timestamp when the activity ends. |
| shift\$1activity\$1arn | string | The ARN of the Shift Activity. If the shift\$1activity\$1arn is null, then it indicates 'Work' activity. |
| activity\$1status | string | Status of the Activity. This is set to INACTIVE if the activity overlaps with a timeoff. |
| is\$1overtime | Boolean | Set to True if the Activity is part of Overtime. Else set to False. |
| is\$1deleted | Boolean | Set to False when the Shift Activities are valid. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Shift was created/updated. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staff timeoff balance changes
Table Name: `staff_timeoff_balance_changes`
Composite Primary Key: `{instance_id, agent_arn, shift_activity_arn, timeoff_balance_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| account\$1id | string | The ID of the AWS account. |
| agent\$1arn | string | The ARN of the agent. |
| shift\$1activity\$1arn | string | The ARN of the Shift Activity this balance is allocated to. |
| timeoff\$1balance\$1version | bigint | The Time Off balance version, an incrementing number to denote order of changes. |
| balance\$1update\$1source | string | Source of the balance update. The possible values are TIME\$1OFF\$1BALANCE\$1UPLOAD, CONNECT\$1TIME\$1OFF\$1REQUEST, SCHEDULE\$1PUBLISH, CSV\$1TIME\$1OFF\$1BALANCE\$1DELETION, TIME\$1OFF\$1BALANCE\$1BACKFILL, SYSTEM\$1UPDATE |
| timeoff\$1id | string | The ID of the Time Off that caused this balance change, if one exists. |
| last\$1updated\$1by | string | The ARN of the agent who caused this balance change, if one exists. |
| balance\$1change\$1in\$1hours | double | Amount of Time Off balance updated through this change in hours. If this value is positive, this change is crediting Time Off balance. If this value is negative, this change is deducting Time Off balance. This value is undefined for any balance upload and deletion events. |
| remaining\$1balance\$1in\$1hours | double | Remaining Time Off balance hours after this change event. This value is undefined for any balance deletion event. |
| last\$1created\$1timestamp | Timestamp | The Timestamp when the Time Off balance change record was created. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staff timeoffs
Table Name: `staff_timeoffs`
Composite Primary Key: `{instance_id, timeoff_id, agent_arn, timeoff_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| timeoff\$1id | string | The ID of the Time Off. |
| agent\$1arn | string | The ARN of the Agent. |
| timeoff\$1version | bigint | The Time Off Version. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| timeoff\$1type | string | Type of Time Off. The possible values are: TIME\$1OFF and VOLUNTARY\$1TIME\$1OFF. |
| timeoff\$1start\$1timestamp | Timestamp | Timestamp when the Time Off starts. |
| timeoff\$1end\$1timestamp | Timestamp | Timestamp when the Time Off ends. |
| timeoff\$1status | string | Status of the Time Off. The possible values are: PENDING\$1CREATE, PENDING\$1UPDATE, PENDING\$1CANCEL, PENDING\$1ACCEPT, PENDING\$1APPROVE, PENDING\$1DECLINE, APPROVED, ACCEPTED, REJECTED, CANCELLED, WAITING\$1ACCEPT, and WAITING\$1APPROVE. The WAITING statuses indicate timeoff is waiting on User action. PENDING statuses indicate timeoff is waiting for system processing of a user action. |
| shift\$1activity\$1arn | string | The ARN of the Shift Activity used for the Timeoff. |
| effective\$1timeoff\$1hours | double | Total effective Time Off hours. Effective timeoff hours are calculated based on [timeoff deduction logic](https://docs.aws.amazon.com/connect/latest/adminguide/upload-timeoff-balance.html#how-system-calculates-time-off-deductions). This is only set for TIME\$1OFF type. |
| last\$1updated\$1timestamp | Timestamp | Timestamp when the Time Off was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staff timeoff intervals
Table Name: `staff_timeoff_intervals`
Composite Primary Key: \$1`instance_id, timeoff_id, timeoff_version, interval_id}`
This table should be queried by joining with `staff_timeoffs` table on `timeoff_id` and `timeoff_version`.
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| timeoff\$1id | string | The ID of the Time Off. |
| timeoff\$1version | bigint | The Time Off Version. |
| interval\$1id | string | The ID of the Time Off Interval. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| timeoff\$1interval\$1start\$1timestamp | Timestamp | Timestamp when the specific interval of Time Off starts. |
| timeoff\$1interval\$1end\$1timestamp | Timestamp | Timestamp when the specific interval of Time Off ends. |
| interval\$1effective\$1timeoff\$1hours | double | Effective Time Off hours for this specific interval of Time Off. Effective timeoff hours are calculated based on [timeoff deduction logic](https://docs.aws.amazon.com/connect/latest/adminguide/upload-timeoff-balance.html#how-system-calculates-time-off-deductions). |
| last\$1updated\$1timestamp | Timestamp | Timestamp when the Time Off was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staff demand group
Table name: `staff_demand_group`
Composite Primary Key: `{instance_id, agent_arn, demand_group_arn, staff_demand_group_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| agent\$1arn | string | The ARN of the agent. |
| demand\$1group\$1arn | string | The ARN of the demand group. |
| staff\$1demand\$1group\$1version | Long | Version for this agent to demand group association |
| priority | string | Priority of the demand group for this agent. Can be LOW, MEDIUM or HIGH |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| is\$1override | Boolean | Set to 'true' if this is Agent to Demand Group association is Agent level override. |
| is\$1deleted | Boolean | Set to true if agent to demand group association is deleted. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the agent to demand group association was created/updated. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staffing groups demand group
Table name: `staffing_group_demand_group`
Composite Primary Key: `{instance_id, staffing_group_arn, demand_group_arn, staffing_group_demand_group_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| staffing\$1group\$1arn | string | The ARN of the Staffing Group. |
| demand\$1group\$1arn | string | The ARN of the demand group. |
| staffing\$1group\$1demand\$1group\$1version | Long | Version for this Staffing Group to Demand Group association |
| priority | string | Priority of the Demand Group for this Staffing Group. Can be LOW, MEDIUM or HIGH |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| is\$1deleted | Boolean | Set to true if the staffing group to demand group association is deleted. |
| last\$1updated\$1timestamp | Timestamp | Timestamp when the staffing group to demand group association was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Staff shift activity allocation
Table name: `staff_shift_activity_allocations`
Composite Primary Key: `{instance_id, shift_id, shift_version, activity_id, demand_group_arn}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| shift\$1id | string | The ID of the shift. |
| shift\$1version | Long | The Shift Version. |
| activity\$1id | string | The ID of the activity. |
| demand\$1group\$1arn | string | The ARN of the demand group. |
| foecast\$1group\$1arn | string | The ARN of the forecast group. |
| allocation\$1percentage | double | Percentage allocation of the Activity to the Demand Group. |
| is\$1deleted | Boolean | Set to False when the StaffingGroup-ForecastGroupassociation is valid. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Staffing Group was created/updated. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Schedule metrics
Table Name: `schedule_metrics`
Composite Primary Key: `{instance_id, metric_id, interval_start_timestamp}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ARN of the Amazon Connect instance. |
| instance\$1arn | string | The ID of the Amazon Connect instance. |
| metric\$1id | string | Unique identifier for the metric value |
| aws\$1account\$1id | string | The ID of the AWS account. |
| entity\$1type | string | Denotes whether the metric is for a forecast group or demand group. |
| entity\$1arn | string | Arn of the forecast group or demand group |
| channel | string | Denotes the media channel like Voice, chat. If the row contains metrics that are not channel level, then it's populated as ALL |
| interval\$1start\$1timestamp | timestamp | Timestamp denoting the start of the interval |
| required\$1agent\$1count | float | Denotes the forecasted agents count |
| scheduled\$1agent\$1count | float | Denotes the schedule agents count |
| scheduled\$1occupancy | float | Denotes the occupancy percentage |
| scheduled\$1service\$1level\$1percentage | float | Denotes the schedule service level percentage |
| service\$1level\$1seconds | integer | Denotes the service level seconds |
| scheduled\$1average\$1speed\$1of\$1answer | float | Denotes the average speed of answer |
| is\$1deleted | boolean | Denotes whether the metric is deleted |
| last\$1updated\$1timestamp | timestamp | The Timestamp when the metric record was created. |
| data\$1lake\$1last\$1processed\$1timestamp | timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Schedule goals
Table Name: `schedule_goals`
Composite Primary Key: `{instance_id, goal_id}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ARN of the Amazon Connect instance. |
| instance\$1arn | string | The ID of the Amazon Connect instance. |
| goal\$1id | string | Unique identifier for the goal value |
| aws\$1account\$1id | string | The ID of the AWS account. |
| entity\$1type | string | Denotes whether the goal is for a forecast group or demand group. |
| entity\$1arn | string | Arn of the forecast group or demand group |
| channel | string | Denotes the media channel like Voice, chat. |
| start\$1date\$1timestamp | timestamp | Timestamp denoting start of the goal |
| end\$1date\$1timestamp | timestamp | Timestamp denoting end of the goal |
| goal\$1service\$1level\$1percentage | float | Denotes the goal service level percentage |
| goal\$1service\$1level\$1seconds | integer | Denotes the service level seconds |
| goal\$1average\$1speed\$1of\$1answer | float | Denotes the average speed of answer |
| is\$1deleted | boolean | Denotes whether the goal is deleted |
| last\$1updated\$1timestamp | timestamp | The Timestamp when the goals record was created. |
| data\$1lake\$1last\$1processed\$1timestamp | timestamp | Timestamp, which shows the last time the data lake processed the record. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Shift rotation patterns
Table Name: `shift_rotation_patterns`
Composite Primary Key: `{instance_id, shift_rotation_pattern_arn, shift_rotation_pattern_version}`
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| shift\$1rotation\$1pattern\$1arn | string | The ARN of the Shift Rotation Pattern. |
| shift\$1rotation\$1pattern\$1version | bigint | The Shift Rotation Pattern Version. |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| shift\$1rotation\$1pattern\$1name | string | The name of the Shift Rotation Pattern. |
| start\$1date | string | The start date of the Shift Rotation Pattern in yyyy-mm-dd format. |
| is\$1deleted | Boolean | Set to True if the Shift Rotation Pattern is deleted. Else set to False. |
| last\$1updated\$1by | string | The ARN of the user who created/updated/deleted the Shift Rotation Pattern. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Shift Rotation Pattern was created/updated/deleted. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Shift rotation steps
Table Name: `shift_rotation_steps`
Composite Primary Key: `{instance_id, shift_rotation_pattern_arn, shift_rotation_pattern_version, step_id}`
This table should be queried by joining with `shift_rotation_patterns` table on `shift_rotation_pattern_arn` and `shift_rotation_pattern_version`.
| Column | Type | Description |
| --- | --- | --- |
| instance\$1id | string | The ID of the Amazon Connect instance. |
| shift\$1rotation\$1pattern\$1arn | string | The ARN of the Shift Rotation Pattern. |
| shift\$1rotation\$1pattern\$1version | bigint | The Shift Rotation Pattern Version. |
| step\$1id | bigint | The ID of the step within the Shift Rotation Pattern. Steps are numbered sequentially (1, 2, 3, ... up to 52). |
| instance\$1arn | string | The ARN of the Amazon Connect instance. |
| shift\$1profile\$1arn | string | The ARN of the Shift Profile associated with the rotation step. |
| duration | bigint | The duration of the rotation step in weeks. |
| is\$1deleted | Boolean | Set to False when the Shift Rotation Step is valid. |
| last\$1updated\$1by | string | The ARN of the user who created/updated the Shift Rotation Pattern. |
| last\$1updated\$1timestamp | Timestamp | The Timestamp when the Shift Rotation Pattern was created/updated. |
| data\$1lake\$1last\$1processed\$1timestamp | Timestamp | The Timestamp, which shows the last time the record was touched by the data lake. This can include transformation and backfill. This field cannot be used to determine reliably data freshness. |
## Data schema
Following is an entity relationship diagram that shows the structure and relationships between scheduling tables in the Amazon Connect data lake.
Each table displays its primary keys and attributes with their data types. The diagram illustrates how these tables relate to each other through foreign key relationships, providing a comprehensive view of the scheduling data model.
![\[An entity relationship diagram that shows the structure and relationships between scheduling tables in the Amazon Connect analytics data lake.\]](http://docs.aws.amazon.com/connect/latest/adminguide/images/data-lake-scheduling-tables-overview-1.png)
## Sample queries
### 1. Query to get all the Scheduled Shift Activities of the Agents working on a specific Forecast Group
`SELECT * FROM agent_scheduled_shift_activities_view where forecast_group_name = 'AnyDepartmentForecastGroup'`
Complete the following steps to create `agent_scheduled_shift_activities_view` mentioned above.
**Step 1: Create a view to get supervisor names**
```
CREATE OR REPLACE VIEW "latest_supervisor_names_view" AS
SELECT
staffing_group_arn
, array_agg(supervisor_name ORDER BY supervisor_name ASC) supervisor_names
FROM
(
SELECT
s.staffing_group_arn
, CONCAT(u.first_name, ' ', u.last_name) supervisor_name
FROM
((
SELECT
staffing_group_arn
, supervisor_arn
FROM
(
SELECT
*
, RANK() OVER (PARTITION BY staffing_group_arn ORDER BY staffing_group_version DESC) recency
FROM
staffing_group_supervisors
WHERE (instance_id = 'YourAmazonConnectInstanceId')
) t
WHERE (recency = 1)
) s
INNER JOIN USERS u ON (s.supervisor_arn = u.user_arn))
)
GROUP BY staffing_group_arn
```
**Step 2: Create a view to get the staffing group and forecast group associated with an agent**
```
CREATE OR REPLACE VIEW "latest_agent_staffing_group_forecast_group_view" AS
WITH
latest_staff_scheduling_profile AS (
SELECT
agent_arn
, staffing_group_arn
, last_updated_timestamp
FROM
(
SELECT
*
, RANK() OVER (PARTITION BY agent_arn ORDER BY staff_scheduling_profile_version DESC) recency
FROM
staff_scheduling_profile
WHERE ((instance_id = 'YourAmazonConnectInstanceId') AND (is_deleted = false))
) t
WHERE (recency = 1)
)
, latest_staffing_groups AS (
SELECT
staffing_group_name
, staffing_group_arn
FROM
(
SELECT
*
, RANK() OVER (PARTITION BY staffing_group_arn ORDER BY staffing_group_version DESC) recency
FROM
staffing_groups
WHERE (instance_id = 'YourAmazonConnectInstanceId')
) t
WHERE (recency = 1)
)
, latest_forecast_groups AS (
SELECT
forecast_group_arn
, forecast_group_name
FROM
(
SELECT
*
, RANK() OVER (PARTITION BY forecast_group_arn ORDER BY forecast_group_version DESC) recency
FROM
forecast_groups
WHERE (instance_id = 'YourAmazonConnectInstanceId')
) t
WHERE (recency = 1)
)
, latest_staffing_group_forecast_groups AS (
SELECT
staffing_group_arn
, forecast_group_arn
FROM
(
SELECT
*
, RANK() OVER (PARTITION BY staffing_group_arn ORDER BY staffing_group_version DESC) recency
FROM
staffing_group_forecast_groups
WHERE (instance_id = 'YourAmazonConnectInstanceId')
) t
WHERE (recency = 1)
)
SELECT
ssp.agent_arn
, U.agent_username AS username
, U.agent_routing_profile_id AS routing_profile_id
, CONCAT(u.first_name, ' ', u.last_name) agent_name
, fg.forecast_group_arn
, fg.forecast_group_name
, sg.staffing_group_arn
, sg.staffing_group_name
FROM
latest_staff_scheduling_profile ssp
INNER JOIN latest_staffing_groups sg ON ssp.staffing_group_arn = sg.staffing_group_arn
INNER JOIN latest_staffing_group_forecast_groups sgfg ON ssp.staffing_group_arn = sgfg.staffing_group_arn
INNER JOIN latest_forecast_groups fg ON fg.forecast_group_arn = sgfg.forecast_group_arn
INNER JOIN USERS u ON ssp.agent_arn = u.user_arn
```
**Step 3: Get the latest Shift activities **
```
CREATE OR REPLACE VIEW "latest_shift_activities_view" AS
SELECT
shift_activity_arn
, shift_activity_name
, shift_activity_version
, type
, sub_type
, is_adherence_tracked
, is_paid
, last_updated_timestamp
FROM
(
SELECT
*
, RANK() OVER (PARTITION BY shift_activity_arn ORDER BY shift_activity_version DESC) recency
FROM
shift_activities
WHERE (instance_id = 'YourAmazonConnectInstanceId')
) t
WHERE (recency = 1)
```
**Step 4: Create a view to get the agent scheduled shift activities**
```
CREATE OR REPLACE VIEW "agent_scheduled_shift_activities_view" AS
WITH
latest_staff_shifts AS (
SELECT
agent_arn
, shift_id
, shift_version
, shift_start_timestamp
, shift_end_timestamp
, created_timestamp
, last_updated_timestamp
, data_lake_last_processed_timestamp
, recency
FROM
(
SELECT
RANK() OVER (PARTITION BY shift_id ORDER BY shift_version DESC) recency
, *
FROM
staff_shifts sa
WHERE (instance_id = 'YourAmazonConnectInstanceId')
) t
WHERE ((recency = 1) AND (is_deleted = false))
)
SELECT
asgfg.forecast_group_name
, array_join(sn.supervisor_names, ',') supervisor_names
, s.agent_arn
, u.first_name
, u.last_name
, asgfg.staffing_group_name
, ssa.activity_id
, (CASE WHEN (ssa.shift_activity_arn IS NULL) THEN COALESCE(sa.shift_activity_name, 'Work') ELSE sa.shift_activity_name END) shift_activity_name
, s.shift_start_timestamp
, s.shift_end_timestamp
, (CASE WHEN (ssa.shift_activity_arn IS NULL) THEN COALESCE(sa.type, 'PRODUCTIVE') ELSE sa.type END) type
, (CASE WHEN (ssa.shift_activity_arn IS NULL) THEN COALESCE(sa.is_paid, true) ELSE sa.is_paid END) is_paid
, ssa.activity_start_timestamp
, ssa.activity_end_timestamp
, ssa.last_updated_timestamp
, ssa.data_lake_last_processed_timestamp
, u.agent_username as username
, u.agent_routing_profile_id as routing_profile_id
FROM
staff_shift_activities ssa
INNER JOIN latest_staff_shifts s ON s.shift_id = ssa.shift_id AND s.shift_version = ssa.shift_version
INNER JOIN USERS u ON s.agent_arn = u.user_arn
INNER JOIN latest_agent_staffing_group_forecast_group_view asgfg ON s.agent_arn = asgfg.agent_arn
LEFT JOIN latest_shift_activities_view sa ON sa.shift_activity_arn = ssa.shift_activity_arn
INNER JOIN latest_supervisor_names_view sn ON sn.staffing_group_arn = asgfg.staffing_group_arn
WHERE (ssa.is_deleted = false) AND (COALESCE(ssa.activity_status, ' ') <> 'INACTIVE') AND (ssa.instance_id = 'YourAmazonConnectInstanceId')
```
### 2. Query to get all the time off requests of the Agents in a specific Forecast Group
` SELECT * FROM agent_timeoff_report_view where forecast_group_name = 'AnyDepartmentForecastGroup' `
Use the following query to create `agent_timeoff_report_view` mentioned above.
```
CREATE OR REPLACE VIEW "agent_timeoff_report_view" AS
WITH latest_staff_timeoffs AS (
SELECT t1.*,
CAST((t1.effective_timeoff_hours * 60) AS INT) total_effective_timeoff_minutes
FROM (
SELECT RANK() OVER (
PARTITION BY timeoff_id
ORDER BY timeoff_version DESC
) recency,
agent_arn,
timeoff_id,
shift_activity_arn,
timeoff_status,
timeoff_version,
effective_timeoff_hours,
timeoff_start_timestamp,
timeoff_end_timestamp,
last_updated_timestamp,
data_lake_last_processed_timestamp
FROM staff_timeoffs
WHERE (
instance_id = 'YourAmazonConnectInstanceId'
)
) t1
WHERE (recency = 1)
)
SELECT asgfg.forecast_group_name,
to.agent_arn,
asgfg.agent_name,
asgfg.staffing_group_name,
asgfg.username,
sa.shift_activity_name,
to.timeoff_start_timestamp,
to.timeoff_end_timestamp,
to.timeoff_status,
array_join(sn.supervisor_names, ',') AS supervisor_names,
sa.is_paid,
to.last_updated_timestamp,
to.data_lake_last_processed_timestamp,
u.agent_routing_profile_id AS routing_profile_id,
to.timeoff_id,
to.shift_activity_arn,
to.total_effective_timeoff_minutes
FROM latest_staff_timeoffs to
INNER JOIN latest_agent_staffing_group_forecast_group_view asgfg ON asgfg.agent_arn = to.agent_arn
INNER JOIN latest_shift_activities_view sa ON sa.shift_activity_arn = to.shift_activity_arn
INNER JOIN latest_supervisor_names_view sn ON sn.staffing_group_arn = asgfg.staffing_group_arn
INNER JOIN users u ON u.user_arn = to.agent_arn
```