SLA for operational pipelines

Keep the list of members by the tool's user id, with team, role and start and end dates. The queue starts from this list and shows people without tasks at zero. Shared inboxes (the team's scheduling inbox, for example) appear apart from people. Never derive the team only from who showed up in the tasks.

Consider only active tasks. Started today (date in America/Sao_Paulo) is today's queue; started earlier is carried over; with the tool's expected end date less than or equal to now is overdue. Count distinct processes, not rows: a task with two assignees appears twice in the source. Separate main tasks from subtasks.

Walk through each day between start and end and add only the part inside working hours (for example, 9am to 6pm, or 8am to 6pm in customer service), skipping weekends, national, state and municipal holidays and company-wide vacations, with half days where they exist. Moving only the start and end into business hours and subtracting still counts the nights and the weekend in between.

Zendesk and other tools provide business time and calendar time for wait, first reply and resolution. Pick one per metric and use the same in the target, the percentage and the average.

Consecutive tasks from the same team form a pass, opened by one of that team's initiating tasks. When the case goes to another team and comes back, a new pass opens. Inside a pass, each change of assignee opens a sub-pass, to measure who did each part. Flag passes with a review step (double check). A team task without an initiating task before it is orphaned and stays out of the time.

Order messages by process (claim, rent guarantee, ticket). An event starts at the customer's first message after an operations reply, and consecutive customer messages belong to the same event. The event ends at the first operations message after the start. No reply is a status of its own, not zero. Who counts as operations comes from the sender's id or role (claims analyst, brokerage admin), not from the name.

Keep targets in a table: by process (for example, claims 24 business hours and other processes 48) and by ticket severity (for example, critical 60 minutes, high 4 hours, medium 8, low 16). Change a target with a start date, so the past is not reclassified.

A task closed with the result of sending the request back leaves the stage time and enters the rework rate of the team that got it back. A reopened ticket counts as rework for the analyst who closed it. Show rework by analyst and by severity over the last 30 days.

Stage time has a long tail. Show median and 90th percentile per week, as well as the average. A stage's time considers only cases that went through it; a case that did not go through it does not count as zero.

Remove from the support queue internal development subtasks without a company, test cards and duplicates. Tickets without a company or partner filled in stay in the queue but become a metric of their own (% without company), because they hide volume per customer. When the partner comes in the field name as "[code] name", extract the code to join with the registry.