Scheduling data in the Connect Customer analytics data lake

This topic details the content in the Connect Customer 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:

If you are unable to access the scheduling tables by using Option 1, try using Option 2.

Staff scheduling profile

Table name: staff_scheduling_profile

Description: Contains agent scheduling configuration including staffing group assignment, shift profile or rotation pattern assignment, and scheduling validity window.

Primary key: instance_id, agent_arn, staff_scheduling_profile_version

Join keys:

Shift activities

Table name: shift_activities

Description: Defines the types of activities that can be assigned within shifts, including activity type (productive, non-productive, leave), adherence tracking, and paid status.

Primary key: instance_id, shift_activity_arn, shift_activity_version

Join keys:

Shift profiles

Table name: shift_profiles

Description: Defines shift profile templates that can be assigned to agents, specifying the shift pattern for schedule generation.

Primary key: instance_id, shift_profile_arn, shift_profile_version

Join keys:

Staffing groups

Table name: staffing_groups

Description: Defines staffing groups that organize agents for scheduling purposes, serving as the primary organizational unit for workforce management.

Primary key: instance_id, staffing_group_arn, staffing_group_version

Join keys:

Staffing groups - Forecast groups

Table name: staffing_group_forecast_groups

Description: Maps the association between staffing groups and forecast groups, linking workforce capacity to demand forecasting.

Primary key: instance_id, staffing_group_arn, staffing_group_version, forecast_group_arn

Join keys:

Staffing groups - Supervisors

Table name: staffing_group_supervisors

Description: Maps supervisor associations to staffing groups, tracking which supervisors are responsible for each staffing group.

Primary key: instance_id, staffing_group_arn, staffing_group_version, supervisor_arn

Join keys:

Staff shifts

Table name: staff_shifts

Description: Contains individual shift instances assigned to agents, including shift start and end times and creation metadata.

Primary key: instance_id, shift_id, shift_version

Join keys:

Staff shift activities

Table name: staff_shift_activities

Description: Contains individual activity blocks within a shift, including activity start and end times, overtime flags, and activity status.

Primary key: instance_id, shift_id, shift_version, activity_id

Join keys:

Staff timeoff balance changes

Table name: staff_timeoff_balance_changes

Description: Tracks time-off balance changes for agents, recording credits and deductions from various sources including uploads, requests, and schedule publishing.

Primary key: instance_id, agent_arn, shift_activity_arn, timeoff_balance_version

Partition key: last_created_timestamp (daily)

Join keys:

Staff timeoffs

Table name: staff_timeoffs

Description: Records time-off requests and approvals for agents, including status tracking through the approval workflow and effective hours.

Primary key: instance_id, timeoff_id, agent_arn, timeoff_version

Join keys:

Staff timeoff intervals

Table name: staff_timeoff_intervals

Description: Contains the individual time intervals within a time-off request, breaking multi-day or multi-period time-off into discrete intervals with effective hours per interval.

Primary key: instance_id, timeoff_id, timeoff_version, interval_id

Join keys:

Staff demand group

Table name: staff_demand_group

Description: Maps agents to demand groups with priority assignments, supporting both staffing group-level defaults and agent-level overrides for demand allocation.

Primary key: instance_id, agent_arn, demand_group_arn, staff_demand_group_version

Join keys:

Staffing groups demand group

Table name: staffing_group_demand_group

Description: Maps the association between staffing groups and demand groups with priority, linking organizational units to demand allocation targets.

Primary key: instance_id, staffing_group_arn, demand_group_arn, staffing_group_demand_group_version

Join keys:

Staff shift activity allocation

Table name: staff_shift_activity_allocations

Description: Records the percentage allocation of shift activities to demand groups, enabling proportional assignment of agent capacity across multiple demand groups within a single activity.

Primary key: instance_id, shift_id, shift_version, activity_id, demand_group_arn

Join keys:

Schedule metrics

Table name: schedule_metrics

Description: Contains schedule performance metrics by interval, including required versus scheduled agent counts, occupancy, service level percentage, and average speed of answer.

Primary key: instance_id, metric_id, interval_start_timestamp

Join keys:

Schedule goals

Table name: schedule_goals

Description: Stores service level goals for scheduling, including target service level percentage, service level seconds, and average speed of answer for forecast or demand groups.

Primary key: instance_id, goal_id

Join keys:

Shift rotation patterns

Table name: shift_rotation_patterns

Description: Defines shift rotation pattern configurations that cycle agents through different shift profiles over time, with a configurable start date.

Primary key: instance_id, shift_rotation_pattern_arn, shift_rotation_pattern_version

Join keys:

Shift rotation steps

Table name: shift_rotation_steps

Description: Defines the individual steps within a shift rotation pattern, each associating a shift profile with a duration in weeks (up to 52 steps per rotation).

Primary key: instance_id, shift_rotation_pattern_arn, shift_rotation_pattern_version, step_id

Join keys:

Data schema

Following is an entity relationship diagram that shows the structure and relationships between scheduling tables in the Connect Customer 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.

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